H2GISFunctions assumes that PUBLIC is the active schema

[stijn-vranckx]

The following code fails:

public static void main(String[] args) throws LiquibaseException, SQLException { JdbcDataSource dataSource = new JdbcDataSource(); dataSource.setPassword("password"); dataSource.setUser("sa"); dataSource.setURL("jdbc:h2:mem:testdb"); Connection connection = dataSource.getConnection(); connection.prepareStatement("CREATE SCHEMA OTHER").execute(); connection.prepareStatement("SET SCHEMA OTHER").execute(); H2GISFunctions.load(connection); }

This because a number of insert statements are formatted in the following way;

INSERT INTO SPATIAL_REF_SYS (SRID,AUTH_NAME,AUTH_SRID,SRTEXT,PROJ4TEXT) VALUES (3819,'EPSG',3819,'GEOGCS["HD1909",DATUM["Hungarian_Datum_1909",SPHEROID["Bessel 1841",6377397.155,299.1528128,AUTHORITY["EPSG","7004"]],TOWGS84[595.48,121.69,515.35,4.115,-2.9383,0.853,-3.408],AUTHORITY["EPSG","1024"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","3819"]]','+proj=longlat +ellps=bessel +towgs84=595.48,121.69,515.35,4.115,-2.9383,0.853,-3.408 +no_defs');

Changing this to

INSERT INTO PUBLIC.SPATIAL_REF_SYS (SRID,AUTH_NAME,AUTH_SRID,SRTEXT,PROJ4TEXT) VALUES (3819,'EPSG',3819,'GEOGCS["HD1909",DATUM["Hungarian_Datum_1909",SPHEROID["Bessel 1841",6377397.155,299.1528128,AUTHORITY["EPSG","7004"]],TOWGS84[595.48,121.69,515.35,4.115,-2.9383,0.853,-3.408],AUTHORITY["EPSG","1024"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","3819"]]','+proj=longlat +ellps=bessel +towgs84=595.48,121.69,515.35,4.115,-2.9383,0.853,-3.408 +no_defs');

Would solve the problem. There are already some insert statements using the schema prefix.

[stijn-vranckx]

I can submit a patch if needed. The change seems trivial.

[ebocher]

It seems to me an issue on H2 database side @katzyn ?

[katzyn]

@ebocher What do you mean? Every database object, excluding users and roles, has a schema and H2GIS should take it into account.

H2GIS should probably have an own schema for functions and other objects and this schema must be specified in schema search path.

[ebocher]

@katzyn Oups... I'm just seeing your message now I thought the default schema was the PUBLIC one and therefore INSERT INTO SPATIAL_REF_SYS was equivalent to INSERT INTO PUBLIC.SPATIAL_REF_SYS

thanks @stijn-vranckx for the issue. You are welcome to submit a PR.

[ebocher]

Fixed