Search code examples
hibernateh2spatial-query

hibernate-spatial: can't find function


I'm having a problem while using H2 and GeoDB (in-memory, junit).

Also, using Hibernate 5 (latest versions for each package, including hibernate-spatial) and Spring 4.

Persisting and querying by id entities works just fine. Geometry types are recognized without problems.

Problems arise when I try to query the DB with geospatial functions, and Hibernate fails saying that he can't find the function:

[ERROR] 2015-12-16 11:16:15,000: org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions:129: Function "ST_CONTAINS" not found; SQL statement:
select geoentity0_.id as id1_0_, geoentity0_.location as location2_0_, geoentity0_.name as name3_0_ from GEO_ENTITY geoentity0_ where ST_Contains(geoentity0_.location, ?)=1 [90022-190]

Looks like a probem of dialect.Here is what I'm using (in persistence.xml):

<property name="hibernate.dialect" value="org.hibernate.spatial.dialect.h2geodb.GeoDBDialect" />

These are the deps I'm using:

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>1.4.190</version>
    <scope>test</scope>
</dependency>
<dependency>
          <groupId>org.opengeo</groupId>
          <artifactId>geodb</artifactId>
          <version>0.7</version>
          <scope>test</scope>  
</dependency>

What am I doing wrong and how can I fix?

EDIT: Adding h2gis

I've tried adding h2gis to my deps but didn't change the error. I've also tried to substitute geodb with h2gis, with the same result.

<dependency>
    <groupId>org.orbisgis</groupId>
    <artifactId>h2spatial-ext</artifactId>
    <version>1.2.3</version>
</dependency>

Solution

  • Found the solution and posting here to leave track.

    This solution works for h2 + geodb, but I suppose that a similar solution can be found also for h2gis, with a bit of patience.

    What I was missing was a SQL file executed at schema export named import.sql located in the classpath root that created the functions in the H2 layer.

    The content of this file is:

    CREATE ALIAS AddGeometryColumn for "geodb.GeoDB.AddGeometryColumn"
    CREATE ALIAS CreateSpatialIndex for "geodb.GeoDB.CreateSpatialIndex"
    CREATE ALIAS DropGeometryColumn for "geodb.GeoDB.DropGeometryColumn"
    CREATE ALIAS DropGeometryColumns for "geodb.GeoDB.DropGeometryColumns"
    CREATE ALIAS DropSpatialIndex for "geodb.GeoDB.DropSpatialIndex"
    CREATE ALIAS EnvelopeAsText for "geodb.GeoDB.EnvelopeAsText"
    CREATE ALIAS GeometryType for "geodb.GeoDB.GeometryType"
    CREATE ALIAS ST_Area FOR "geodb.GeoDB.ST_Area"
    CREATE ALIAS ST_AsEWKB FOR "geodb.GeoDB.ST_AsEWKB"
    CREATE ALIAS ST_AsEWKT FOR "geodb.GeoDB.ST_AsEWKT"
    CREATE ALIAS ST_AsHexEWKB FOR "geodb.GeoDB.ST_AsHexEWKB"
    CREATE ALIAS ST_AsText FOR "geodb.GeoDB.ST_AsText"
    CREATE ALIAS ST_BBOX FOR "geodb.GeoDB.ST_BBox"
    CREATE ALIAS ST_Buffer FOR "geodb.GeoDB.ST_Buffer"
    CREATE ALIAS ST_Centroid FOR "geodb.GeoDB.ST_Centroid"
    CREATE ALIAS ST_Crosses FOR "geodb.GeoDB.ST_Crosses"
    CREATE ALIAS ST_Contains FOR "geodb.GeoDB.ST_Contains"
    CREATE ALIAS ST_DWithin FOR "geodb.GeoDB.ST_DWithin"
    CREATE ALIAS ST_Disjoint FOR "geodb.GeoDB.ST_Disjoint"
    CREATE ALIAS ST_Distance FOR "geodb.GeoDB.ST_Distance"
    CREATE ALIAS ST_Envelope FOR "geodb.GeoDB.ST_Envelope"
    CREATE ALIAS ST_Equals FOR "geodb.GeoDB.ST_Equals"
    CREATE ALIAS ST_GeoHash FOR "geodb.GeoDB.ST_GeoHash"
    CREATE ALIAS ST_GeomFromEWKB FOR "geodb.GeoDB.ST_GeomFromEWKB"
    CREATE ALIAS ST_GeomFromEWKT FOR "geodb.GeoDB.ST_GeomFromEWKT"
    CREATE ALIAS ST_GeomFromText FOR "geodb.GeoDB.ST_GeomFromText"
    CREATE ALIAS ST_GeomFromWKB FOR "geodb.GeoDB.ST_GeomFromWKB"
    CREATE ALIAS ST_Intersects FOR "geodb.GeoDB.ST_Intersects"
    CREATE ALIAS ST_IsEmpty FOR "geodb.GeoDB.ST_IsEmpty"
    CREATE ALIAS ST_IsSimple FOR "geodb.GeoDB.ST_IsSimple"
    CREATE ALIAS ST_IsValid FOR "geodb.GeoDB.ST_IsValid"
    CREATE ALIAS ST_MakePoint FOR "geodb.GeoDB.ST_MakePoint"
    CREATE ALIAS ST_MakeBox2D FOR "geodb.GeoDB.ST_MakeBox2D"
    CREATE ALIAS ST_Overlaps FOR "geodb.GeoDB.ST_Overlaps"
    CREATE ALIAS ST_SRID FOR "geodb.GeoDB.ST_SRID"
    CREATE ALIAS ST_SetSRID FOR "geodb.GeoDB.ST_SetSRID"
    CREATE ALIAS ST_Simplify FOR "geodb.GeoDB.ST_Simplify"
    CREATE ALIAS ST_Touches FOR "geodb.GeoDB.ST_Touches"
    CREATE ALIAS ST_Within FOR "geodb.GeoDB.ST_Within"
    CREATE ALIAS Version FOR "geodb.GeoDB.Version"