Comments (2)
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()
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)'))
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.
from h2gis.
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.
from h2gis.
Related Issues (20)
- SHPWrite with empty geom HOT 1
- Unknown data type: "POINT" HOT 2
- Release H2GIS 2.1.0 on github HOT 1
- Import shapefile error with .prj HOT 1
- JTS OverlayNG HOT 1
- Add ST_Project HOT 2
- ST_OSMDownloader HOT 1
- Add ESRI proj definitions to SPATIAL_REF_SYS table HOT 1
- Support options on FILE_TABLE HOT 2
- Data conversion error MULTIPOLYGON HOT 7
- Dependency org.postgresql:postgresql, leading to CVE problem
- ST_Tessellate empty geometry
- Error transformation from SRID 2100 to 4326 HOT 2
- Update H2GIS with H2 2.2.220 HOT 2
- Check createDDL for DECFLOAT
- Update to H2 2.2.222 HOT 4
- st_union with GROUP BY doesn't work HOT 1
- TopoJson driver
- GeoJSON driver read regression HOT 2
- H2GISFunctions assumes that PUBLIC is the active schema HOT 5
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from h2gis.