[FIXED] H2 Gis: Function "WITHIN" not found

Issue

I have an integration test running h2, where I try to use jpa criteria to make spatial searches.

The test initializes h2 gis as required in the documentation:

@DataJpaTest
@Sql(scripts = "/sql/h2_gis_initialization.sql")
class LocalizationRepositoryTest {

Where the sql file contains the following line:

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

When running the test:

@Test
void it_can_search_within_geometry() {
    final Polygon polygon = new GeometryFactory().createPolygon();
    List<Localization> results = repository.findAll(
            (root, query, builder) -> JTSSpatialPredicates.within(builder, root.get(Localization_.geometry),
                    polygon));

    Assertions.assertEquals(0, results.size());
}

The test fail with the following error:

org.springframework.orm.jpa.JpaSystemException: could not prepare statement; nested exception is
org.hibernate.exception.GenericJDBCException: could not prepare
statement     at
my.api.LocalizationRepositoryTest.it_can_search_within_geometry(LocalizationRepositoryTest.java:66)
Caused by: org.hibernate.exception.GenericJDBCException: could not
prepare statement     at
my.api.LocalizationRepositoryTest.it_can_search_within_geometry(LocalizationRepositoryTest.java:66)
Caused by: org.h2.jdbc.JdbcSQLException:  Function "WITHIN" not found;
SQL statement: select localizati0_.id as id1_48_,
localizati0_.spatial_geometry as spatial_2_48_ from localization_t
localizati0_ where within(localizati0_.spatial_geometry, ?)=?
[90022-197]   at
my.api.LocalizationRepositoryTest.it_can_search_within_geometry(LocalizationRepositoryTest.java:66)

For some reason, the criteria tries to make use of a non-existing spatial function. Why is that? How can I make my test work?

Solution

I just found this other similar question: How to configure spring-boot project to work with inmemory spatial database for tests?

It appears that I was just missing the dialect configuration for jpa for tests:

Answered By - Heschoon