Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Question about H2GIS 2.0.0 (Function "st_contains" not found) #1308

Closed
ips219 opened this issue May 29, 2022 · 2 comments
Closed

Question about H2GIS 2.0.0 (Function "st_contains" not found) #1308

ips219 opened this issue May 29, 2022 · 2 comments

Comments

@ips219
Copy link

ips219 commented May 29, 2022

Hi...

I am trying to include h2gis in my project but I am not able to search with spatial functions from Java code using JPA criteria query.
The project is based in:

  • Spring Boot 2.7
  • Hibernate 5.6.9
  • H2 2.1.212
  • H2GIS 2.0.0

I was able to setup the database and install the extension by executing the required SQL statements (I use liquibase)

  - changeSet:
      id: master_ddl_00021
      context: TENANT_MANAGER
      comment: Enable h2gis extension 
      changes:
        -  sql:  
            dbms:  'h2'  
            sql:  CREATE ALIAS IF NOT EXISTS H2GIS_SPATIAL FOR "org.h2gis.functions.factory.H2GISFunctions.load"
        -  sql:  
            dbms:  'h2'  
            sql:  CALL H2GIS_SPATIAL()

h2-geometry

Also I had no issues for the creation of the required column (Also with liquibase)

  - changeSet:
      id: master_ddl_00023
      context: TENANT
      comment: Additional fields for geometric information
      changes:
        -  sql:  
            dbms:  'h2'  
            sql:  ALTER TABLE "${schemaName}".asset_data ADD data_geometry GEOMETRY;

In Java I am using JPA for storing the geometry column without any issues (By using org.locationtech.jts.geom.Geometry and Hibernate)

  @Column(name = "data_geometry")
  private Geometry dataGeometry;

With these settings I am able to persist the information and read it back without any issues...

But the problems comes when I try to search with criteria query... I am using JTSSpatialPredicates for searching which performs the translation to SQL code, basically it receives a GeoJson area which is then translated to a Geometry JTS object to build the crieteria Predicate and then Hibernate-Spatial translates it to SQL Code...

          Feature feature = (Feature) GeoJSONFactory.create(fieldValue.toString());
          GeoJSONReader reader = new GeoJSONReader();
          Geometry region = null;
          if (feature.getProperties().containsKey("radius")) {
            Double radius = Double.valueOf(feature.getProperties().get("radius").toString());
            Geometry point = reader.read(feature.getGeometry());
            GeometricShapeFactory shapeFactory = new GeometricShapeFactory();
            shapeFactory.setCentre(point.getCoordinate());
            shapeFactory.setSize(radius * 2);
            shapeFactory.setNumPoints(32);
            region = shapeFactory.createCircle();
          } else {
            region = reader.read(feature.getGeometry());
          }
          return JTSSpatialPredicates.contains(cb, cb.literal(region), fieldPath.as(Geometry.class));

From what I can see in the generated query, it seems it is building the query correctly
ST_Contains(?, cast(assetdatae5_."data_value" as GEOMETRY))=?))

But it throws a JDBC Exception as if the st_contains function is not recognized by the driver...

Function "st_contains" not found; SQL statement:
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:632) ~[h2-2.1.212.jar:2.1.212]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:477) ~[h2-2.1.212.jar:2.1.212]
	at org.h2.message.DbException.get(DbException.java:223) ~[h2-2.1.212.jar:2.1.212]
	at org.h2.message.DbException.get(DbException.java:199) ~[h2-2.1.212.jar:2.1.212]
	at org.h2.command.Parser.readFunction(Parser.java:3894) ~[h2-2.1.212.jar:2.1.212]

If I execute SQL code in the H2 Web Console it works correctly...
(SELECT * FROM "schema-name".asset_data where st_contains(data_geometry, 'POINT (-4.7464109 41.6252934)'))
h2-query

From what I understood, h2gis 2.0.0 version should register the functions automatically in the driver, or I am wrong?
Is it necessary to perform any additional setting or include any other dependency?
I tried including h2gis-functions (which it seems is not available in 2.0.0 version) but I got same results..

Thanks for the Help.

Best regards

@ips219
Copy link
Author

ips219 commented May 29, 2022

Hi...

I am trying to include h2gis in my project but I am not able to search with spatial functions from Java code using JPA criteria query. The project is based in:

  • Spring Boot 2.7
  • Hibernate 5.6.9
  • H2 2.1.212
  • H2GIS 2.0.0

I was able to setup the database and install the extension by executing the required SQL statements (I use liquibase)

  - changeSet:
      id: master_ddl_00021
      context: TENANT_MANAGER
      comment: Enable h2gis extension 
      changes:
        -  sql:  
            dbms:  'h2'  
            sql:  CREATE ALIAS IF NOT EXISTS H2GIS_SPATIAL FOR "org.h2gis.functions.factory.H2GISFunctions.load"
        -  sql:  
            dbms:  'h2'  
            sql:  CALL H2GIS_SPATIAL()

h2-geometry

Also I had no issues for the creation of the required column (Also with liquibase)

  - changeSet:
      id: master_ddl_00023
      context: TENANT
      comment: Additional fields for geometric information
      changes:
        -  sql:  
            dbms:  'h2'  
            sql:  ALTER TABLE "${schemaName}".asset_data ADD data_geometry GEOMETRY;

In Java I am using JPA for storing the geometry column without any issues (By using org.locationtech.jts.geom.Geometry and Hibernate)

  @Column(name = "data_geometry")
  private Geometry dataGeometry;

With these settings I am able to persist the information and read it back without any issues...

But the problems comes when I try to search with criteria query... I am using JTSSpatialPredicates for searching which performs the translation to SQL code, basically it receives a GeoJson area which is then translated to a Geometry JTS object to build the crieteria Predicate and then Hibernate-Spatial translates it to SQL Code...

          Feature feature = (Feature) GeoJSONFactory.create(fieldValue.toString());
          GeoJSONReader reader = new GeoJSONReader();
          Geometry region = null;
          if (feature.getProperties().containsKey("radius")) {
            Double radius = Double.valueOf(feature.getProperties().get("radius").toString());
            Geometry point = reader.read(feature.getGeometry());
            GeometricShapeFactory shapeFactory = new GeometricShapeFactory();
            shapeFactory.setCentre(point.getCoordinate());
            shapeFactory.setSize(radius * 2);
            shapeFactory.setNumPoints(32);
            region = shapeFactory.createCircle();
          } else {
            region = reader.read(feature.getGeometry());
          }
          return JTSSpatialPredicates.contains(cb, cb.literal(region), fieldPath.as(Geometry.class));

From what I can see in the generated query, it seems it is building the query correctly ST_Contains(?, cast(assetdatae5_."data_value" as GEOMETRY))=?))

But it throws a JDBC Exception as if the st_contains function is not recognized by the driver...

Function "st_contains" not found; SQL statement:
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:632) ~[h2-2.1.212.jar:2.1.212]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:477) ~[h2-2.1.212.jar:2.1.212]
	at org.h2.message.DbException.get(DbException.java:223) ~[h2-2.1.212.jar:2.1.212]
	at org.h2.message.DbException.get(DbException.java:199) ~[h2-2.1.212.jar:2.1.212]
	at org.h2.command.Parser.readFunction(Parser.java:3894) ~[h2-2.1.212.jar:2.1.212]

If I execute SQL code in the H2 Web Console it works correctly... (SELECT * FROM "schema-name".asset_data where st_contains(data_geometry, 'POINT (-4.7464109 41.6252934)')) h2-query

From what I understood, h2gis 2.0.0 version should register the functions automatically in the driver, or I am wrong? Is it necessary to perform any additional setting or include any other dependency? I tried including h2gis-functions (which it seems is not available in 2.0.0 version) but I got same results..

Thanks for the Help.

Best regards

For the records...

So it seems the problem is related with the "schema" and the way the spatial functions are loaded. In my application database is not deployed in the "public" H2 default schema.

Initialization method(org.h2gis.functions.factory.H2GISFunctions.load) deploy the spatial_ref_sys table fixed in the "public" schema, and it also create the "alias" to h2gis functions on the schema it's been executed...

In my case spatial_ref_sys and functions has been initialized in the "public" schema, but hibernate is executing the query setting up the specific schema name that I am using and consequently st_contains alias does not exists on it (only exists in public).

I've tried to execute the initialization method (org.h2gis.functions.factory.H2GISFunctions.load) in the specific schema by executing set schema = "schema-name" before the h2gis initialization, but it doesn't work, as it expects to find the spatial_ref_sys table on the same schema and the initialization methods deploy it fixed in the public schema.

A workaround that worked for me consists

First deploy H2GIS in the "public" schema in order to populate spatial_ref_sys table and the alias functions.

CREATE ALIAS IF NOT EXISTS H2GIS_SPATIAL FOR "org.h2gis.functions.factory.H2GISFunctions.load"
CALL H2GIS_SPATIAL()

Then switch to the specific schema and copy the spatial_ref_sys table and execute again the initalization

SET SCHEMA = "${schemaName}"
CREATE TABLE SPATIAL_REF_SYS  AS SELECT * FROM "public".SPATIAL_REF_SYS
CREATE ALIAS IF NOT EXISTS H2GIS_SPATIAL FOR "org.h2gis.functions.factory.H2GISFunctions.load"
CALL H2GIS_SPATIAL()

By doing in this way, initialization does not fail and function alias area created also in my application schema, and in consequence hibernate works as expected.

@ips219 ips219 closed this as completed May 29, 2022
@katzyn
Copy link

katzyn commented May 31, 2022

H2 has a SCHEMA_SEARCH_PATH setting. It can be used to specify a schema with these functions to allow their usage in your current schema too.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants