Spring Boot app with embedded H2GIS - initialization throws SQL syntax error

Question

I'm trying to set up an in-memory H2 database with H2GIS extension in a Spring Boot application. My build.gradle and application.properties are provided below. According to the H2GIS documentation and numerous examples, the extension must be initialized as the following:

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

In my case the first command is executed successfully, but the second one gives an error:

Syntax error in SQL statement "CREATE DOMAIN IF NOT EXISTS POINT AS GEOMETRY(1[*]) CHECK (ST_GEOMETRYTYPECODE(VALUE) = 1);"; SQL statement:
CREATE DOMAIN IF NOT EXISTS POINT AS GEOMETRY(1) CHECK (ST_GeometryTypeCode(VALUE) = 1); [42000-200] 42000/42000 (Help)

This happens, when executing the method registerGeometryType of the class org.h2gis.functions.factory.H2GISFunctions. For some reason, an SQL statement composed in that method cannot be executed.

So far I have tried to initialize the extension by putting SQL commands to src/main/resources/data.sql, and by running them manually in h2-console. I also tried to use different versions of H2GIS: 1.4.0 and 1.5.0. All attempts give me the same effect.

When I tried to run a standalone H2GIS as shown in H2GIS quickstart guide, it worked fine.

In order to minimize the scope of the problem, I created a minimal Spring Boot app from scratch. Here is my build.gradle:

plugins {
    id 'org.springframework.boot' version '2.2.5.RELEASE'
    id 'io.spring.dependency-management' version '1.0.9.RELEASE'
    id 'java'
}

group = 'com.example'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = '1.8'

repositories {
    mavenCentral()
}

dependencies {
    compile group: 'org.hibernate', name: 'hibernate-spatial', version: '5.4.10.Final'
    compile group: 'org.orbisgis', name: 'h2gis', version: '1.5.0'
    runtimeOnly 'com.h2database:h2'

    implementation 'org.springframework.boot:spring-boot-starter-web'
    implementation 'org.springframework.boot:spring-boot-starter-data-jdbc'
    implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
    testImplementation('org.springframework.boot:spring-boot-starter-test') {
        exclude group: 'org.junit.vintage', module: 'junit-vintage-engine'
    }
}

test {
    useJUnitPlatform()
}

my application.properties:

spring.application.name=demo-h2gis
server.port=8080

spring.datasource.url=jdbc:h2:mem:demoh2gis
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=

spring.jpa.database-platform=org.hibernate.spatial.dialect.h2geodb.GeoDBDialect
spring.jpa.hibernate.ddl-auto=create-drop

spring.h2.console.enabled=true

spring.main.allow-bean-definition-overriding=true

...and the application itself:

@SpringBootApplication
@EnableAutoConfiguration
public class DemoH2gisApplication {
    public static void main(String[] args) {
        SpringApplication.run(DemoH2gisApplication.class, args);
    }
}

What can be the problem?

Falsehood

Answer 1

Releases of H2GIS can only be used with some exact release of H2 database. For H2GIS 1.5.0 an outdated and unsupported H2 1.4.197 is needed, but you're using the latest published H2 1.4.200 and H2GIS 1.5.0 is not compatible with it by many aspects. You need to specify the version 1.4.197 of H2 in your build.gradle.

Anesthetist

Thanks, this helped perfectly! However there is a weird thing: H2GIS 1.5.0 brings H2 1.4.200 as a transitive dependency. So I had to exclude H2 from H2GIS dependency and specify explicitly H2 version 1.4.197. – Falsehood

Is there a mapping of these versions on the website somewhere? – Deposal

Answer 2

In addition to Evgenij's answer, for Spring Boot 2.2.x, it was enough to override the H2 version in pom.xml.

<properties>
    <!-- Need to use an earlier version of H2 when using H2GIS -->
    <h2.version>1.4.197</h2.version>
</properties>

H2GIS was then initialised correctly with the following dependency, property and schema initialisation file.

<dependency>
    <groupId>org.orbisgis</groupId>
    <artifactId>h2gis-functions</artifactId>
    <version>1.3.2</version>
    <scope>runtime</scope>
</dependency>

application-default.properties (or whatever properties file you use):

# Initialise H2 with H2GIS for spatial support – see schema-h2.sql also
spring.datasource.platform=h2
spring.jpa.properties.hibernate.dialect= org.hibernate.spatial.dialect.h2geodb.GeoDBDialect

Resource file schema-h2.sql

-- Needed to add H2GIS support for spatial data types
CREATE ALIAS IF NOT EXISTS H2GIS_SPATIAL FOR "org.h2gis.functions.factory.H2GISFunctions.load";
CALL H2GIS_SPATIAL();

Olsson

Don't forget hibernate-spatial dependency: https://mcmap.net/q/665941/-unable-to-resolve-name-org-hibernate-spatial-dialect-postgis-postgisdialect-as-strategy-org-hibernate-dialect-dialect – Monoculture

Any update for springboot 2.6? – Ulm

Springboot 2.2.x is so old now the initalizer doesnt even offer 2.2 – Ulm

@AlGrant I'm posting your solution for Spring Boot 2.6 here as well: https://mcmap.net/q/1482984/-h2gis-data-in-springboot-2-6 – Manchineel