Jhipster-generated Spring Boot + MySql project config for spatial queries

Question

My goal is to add the capability for geospatial queries to my jhipster-generated Spring Boot + MySql project, but I have failed to properly configure my H2 database for queries performed by my tests and by my dev database for local deployments of the app. Since we have a strict CI/CD pipeline, this means I have not been able to test in prod yet, but I suspect I'd run into the same error there too. The error I get when performing a spatial query in a test or dev environment: org.h2.jdbc.JdbcSQLSyntaxErrorException: Function "WITHIN" not found;.

There are a number of posts and guides addressing this issue, but they have not resolved the problem for me. I have followed the tutorial here, the helpful documentation here, and have tried the solutions/suggestions in post 1, post 2, post 3, post 4, and several others. I also compared my code to this example project. But I am still unable to get past this error.

Relevant config... pom.xml:

...
<java.version>1.8</java.version>
<spring-boot.version>2.1.6.RELEASE</spring-boot.version>
<spring.version>5.1.8.RELEASE</spring.version>
<hibernate.version>5.3.10.Final</hibernate.version>
<h2.version>1.4.199</h2.version>
<jts.version>1.13</jts.version>
...
    <repositories>
        <repository>
            <id>OSGEO GeoTools repo</id>
            <url>http://download.osgeo.org/webdav/geotools</url>
        </repository>
        <repository>
            <id>Hibernate Spatial repo</id>
            <url>http://www.hibernatespatial.org/repository</url>
        </repository>
    </repositories>
...
<dependencies>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-spatial</artifactId>
        </dependency>
        <dependency>
            <groupId>com.vividsolutions</groupId>
            <artifactId>jts</artifactId>
            <version>${jts.version}</version>
        </dependency>
</dependencies>

My main application.yml:

spring:
  jpa:
    open-in-view: false
    properties:
      hibernate.jdbc.time_zone: UTC
    hibernate:
      dialect: org.hibernate.spatial.dialect.mysql.MySQL56SpatialDialect
      ddl-auto: none

My application-dev.yml for my dev environment:

spring:
  h2:
    console:
      enabled: false
  jpa:
    database-platform: org.hibernate.spatial.dialect.h2geodb.GeoDBDialect
    database: H2
    show-sql: true
    hibernate:
      dialect: org.hibernate.spatial.dialect.h2geodb.GeoDBDialect

My application-prod.yml for prod:

spring:
  jpa:
    database-platform: org.hibernate.spatial.dialect.mysql.MySQL56SpatialDialect
    database: MYSQL
    show-sql: false

My test/application.yml:

spring:
  jpa:
    database-platform: org.hibernate.spatial.dialect.h2geodb.GeoDBDialect
    database: H2
    open-in-view: false
    show-sql: false
    hibernate:
      dialect: org.hibernate.spatial.dialect.h2geodb.GeoDBDialect
      ddl-auto: none

Relevant code in service layer:

    @Override
    @Transactional(readOnly = true)
    public Page<MyObject> findAllWithinDistanceOfLocation(Float distance, Point location, Pageable pageable) {
        log.debug("Request to get all MyObject within a distance centered on location");
        GeometricShapeFactory shapeFactory = new GeometricShapeFactory();
        shapeFactory.setNumPoints(32); // 32 = number of points to define circle. Default is 100. Higher the number, the more accurately drawn the circle
        shapeFactory.setCentre(location.getCoordinate());
        shapeFactory.setSize(distance * 2);
        Geometry areaOfInterest = shapeFactory.createCircle();
        return myObjectRepository.findAllWithinCircle(areaOfInterest, pageable);
    }

Relevant code in repository:

@Query("select e from MyObjectTable e where within(e.location, :areaOfInterest) = true")
    Page<MyObject> findAllWithinCircle(@Param("areaOfInterest") Geometry areaOfInterest, Pageable pageable);

Relevant code in database config bean:

/**
     * Open the TCP port for the H2 database, so it is available remotely.
     *
     * @return the H2 database TCP server.
     * @throws SQLException if the server failed to start.
     */
    @Bean(initMethod = "start", destroyMethod = "stop")
    @Profile(JHipsterConstants.SPRING_PROFILE_DEVELOPMENT)
    public Object h2TCPServer() throws SQLException {
        String port = getValidPortForH2();
        log.debug("H2 database is available on port {}", port);
        return H2ConfigurationHelper.createServer(port);
    }

    private String getValidPortForH2() {
        int port = Integer.parseInt(env.getProperty("server.port"));
        if (port < 10000) {
            port = 10000 + port;
        } else {
            if (port < 63536) {
                port = port + 2000;
            } else {
                port = port - 2000;
            }
        }
        return String.valueOf(port);
    }

I've tried different values for the properties above, trying to do so in a principled way based on documentation and other projects, but I can't seem to get this working properly. I suspect I am missing an h2 initial configuration command that creates an alias for WITHIN but still have not been able to grok it and get this working.

Note: I've included and excluded the pom file's above section to no effect.

Answer 1

For those who want to know how we resolved this...

The problem: We had a Heroku CI/CD pipeline that did not support test containers, as stated here: https://devcenter.heroku.com/articles/heroku-ci#docker-deploys

To quote the documentation: "Currently, it is not possible to use Heroku CI to test container builds."

Compounding this problem was that H2 support for spatial queries was too problematic and gave different results than a native MySql db and posed a myriad of dialect-related problems outlined in the original post.

The not-ideal but workable solution: Was a combination of a development process "workaround" combined with some standard testing practices.

First, we created a test-containers profile that would run geospatial integration tests when ./mvnw verify was executed with that test-containers profile. The Heroku CI/CD pipeline did not run the geospatial integration tests, but we made it part of our "definition of done" to run those tests locally.

To make this less bad and error-prone, we did the typical unit testing strategy: mock the repositories that employ geospatial queries and exercise business logic in the unit tests. These ran in the CI/CD pipeline.

The next step will be to migrate the CI/CD pipeline to one that supports containers. But in the meantime, the above approach gave us enough overlapping coverage to feel confident to promote the geospatial-based features to prod. After several months of being stress tested with feature enhancements and extensions, so far things seem to have worked well from a product point-of-view.

Answer 2

I went through this path for spatial Postgresql and then it was painful: CI did not catch bugs until we decided to give up H2.

I would recommend that you use same database in dev and prod using docker and testcontainers, JHipster supports this but it's easy to do by yourself too.