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.

Gaël Marziou · Accepted Answer · 2021-05-26 06:17:16Z

1

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.

answered May 26, 2021 at 6:17

Gaël Marziou

16.3k4 gold badges40 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kingofswords Over a year ago

Indeed, we might have to use docker for this, but my hope is to keep H2 and find some spatial-query-capable dialect that works with it the same way it works with our prod db. But if your experience was this painful too and if problems kept arising, then this path might be a dead end. I've already run into other oddities in terms of mismatches between H2 and our real prod database, so maybe the convenience of H2 isn't worth it.

kingofswords · Accepted Answer · 2021-12-04 16:38:46Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related