Jpa query for both postgis and h2gis

Question

I have queries in JpaRepository which use Postgis functions like ST_MakeEnvelope,ST_DWithin etc. That's for production code.

I also want test these queries for H2gis, but these functions wont work then.

How can I bridge this gap with hibernate spatial?

My query is as:

@Query(value = "SELECT * FROM Feature f WHERE geometry && ST_MakeEnvelope(:west, :south, :east, :north, :srid)", nativeQuery = true)

How to make it work with h2 gis too?

So I created this query:

@Query(value = "SELECT * FROM Feature f where dwithin(f.geometry, :centre, :range)", nativeQuery = true)

But this throws error:

Caused by: org.h2.jdbc.JdbcSQLException: Function "DWITHIN" not found;

I have following dependencies:

        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <version>1.4.197</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.orbisgis</groupId>
            <artifactId>h2gis-functions</artifactId>
            <version>1.3.2</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-spatial</artifactId>
            <version>5.4.17.Final</version>
        </dependency>

Answer 1

Update the H2GIS latest version. In the latest version this issue is fixed.

<dependency>
   <groupId>org.orbisgis</groupId>
   <artifactId>h2gis</artifactId>
   <version>1.5.0</version>
</dependency>

You need to initialize the H2GIS extension

From doc: To initialize the H2GIS extension apply the SQL syntax:

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

Then use like

@Query(value = "SELECT * FROM Feature f where ST_DWithin(f.geometry, :centre, :range)", nativeQuery = true)