Tuesday, February 14, 2017

A working combination of grails 3, geoDB & hibernate spatial.

Ever since the move to grails 3 - dealing with plugins (for me) has become a pain. Almost all the documentation out there is for the now legacy 2.X branches. Makes me consider just using basic servlets like jetty or tomcat embedded. Anyway!

Hibernate spatial is helpful ORM when working with the Java Topology Suite - and since hibernate 5 is now worked into the core project.

To setup your grails project to use hibernate spatial - and for grails to successfully select the type of the JTS Geometry derviatives when defined in a domain class - you'll need to make sure all your hibernate versions stack up right to avoid no class data found or no class def found, or java.lang.NoSuchMethodError OR cannot resolve name errrors - all symptoms of mismatch java versions and/or class/interface definitions.

in your build gradle file:
(I happen to be using the latest grails 3.2.6)

* Make sure you are using the hibernate5 grails plugin - atleast version 6.0.0
* Make sure the version of hibernate-core & hibernate-ehcache & hibernate-spatial matches - atleast version 5.1.0 - but crucially - not greater than 5.2.0 - 5.2.0+ is currently incompatible with grails.
* Make sure you are using geoDB version 0.7 (for local testing) - but not version 0.8 (incompatible from my testing)

* In your bootstrap, make sure to upgrade the h2 database to contain goeDB features:

 try {
            def sql = new Sql(dataSource)
            sql.executeUpdate('CREATE ALIAS InitGeoDB for "geodb.GeoDB.InitGeoDB"')
            sql.executeUpdate('CALL InitGeoDB()')
        } catch (java.sql.SQLException e) {
            log.debug('', e)
        }

And thats it. After changing the plugins, make sure to do a gradle clean, and you should be able to correctly map classes like:

import com.vividsolutions.jts.geom.Geometry
import com.vividsolutions.jts.geom.GeometryFactory

class Feature {

    UUID id

    double lat
    double lon

    Geometry geometry

    def beforeInsert( ) {
        if ( lat!=null && lon!=null && geometry == null) {
            geometry = (new GeometryFactory()).createPoint( new Coordinate(lon, lat ))
        }
    }

    static mapping = {
        id(generator: "uuid2", type: "uuid-binary") // H2 & Postgres
    }

}

4 comments:

  1. I feel your pain on Grails 3 plugins. Have you had any success with a Grails 3 + Hibernate Geospatial + Postgres combination? There is a scarce amount of documentation for using Grails 3 with geospatial databases and all the plugins seem to be for Grails 2.

    ReplyDelete
    Replies
    1. Yeah, ive had some success with POSTGIS and grails. I used geoDB for testing locally, and then hooked up to Postgres for production. I havent tested criteria or grails dynamic finders, but used SQL directly. e.g

      def db = new Sql(dataSource)
      def query = "SELECT geometry as geo FROM feature "+
      " WHERE st_intersects("+
      "ST_GeomFromText('POLYGON((" +
      "$ulLong $ulLat" + ", $lrLon $ulLat" + ", $lrLon $lrLat" + ", $ulLong $lrLat" + ", $ulLong $ulLat" +//5 points - must repeat 1st again to close shape
      "))',4326)" + //input coordinates are lat/lon 4326
      ",feature.geometry" + //osm coordinates are not
      ")"

      db.rows(query)

      The standard postgis driver seems to work with postgis commands.

      Delete
  2. This comment has been removed by the author.

    ReplyDelete
  3. ¡I have the same issue!, have you had a combination with MySQL?

    ReplyDelete