CODEWITHSUNDEEP
javahibernatespring-booth2
NikS
NikS

Reputation: 127

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

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?

Upvotes: 6

Views: 3411

Answers (2)

Scott
Scott

Reputation: 425

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();

Upvotes: 3

Evgenij Ryazanov
Evgenij Ryazanov

Reputation: 8188

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.

Upvotes: 9

Related Questions