MySQL GIS/Geometry with Grails

Setting up grails to use GIS geometry is pretty simple (I will do a plugin sometime). So while I wait for 1.1 beta2 to download I thought I'd quickly describe the bits you need and the steps.

I assume you already know what GIS capabilities exist in MySql. If not consult the MySql website.

The OGC Open Geopatial Consortium publish a standard Geometry model amongst a plethora of documents which describes geometry elements and coordinate transformations for storing and manipulating geospatial data.

To access this from a Grails project you need a few things:

• A recent version of MySql (4.1 or above) with appropriate driver
• The JTS Topology Suite - an open (LGPL) library of geometry primitives and operations see http://www.vividsolutions.com/jts/main.htm
• A set of hibernate mapping UserType classes from http://www.hibernatespatial.org

In your DataSource you need something like this

dialect = org.hibernatespatial.mysql.MySQLSpatialDialect

and then you are free to write domain classes something like this:

import com.vividsolutions.jts.geom.Polygon
import org.hibernatespatial.GeometryUserType
public class MyPoly {
    String name
    Polygon poly
    static mapping = {
        poly type: GeometryUserType
    }
}

Scaffolding won't understand it but then it would be a big step to expect that (automatic SVG scaffolding anyone?).

You can also do querying using WKT geometry (well known text). This example from a service:

import com.vividsolutions.jts.geom.*
import org.hibernate.type.*
import org.hibernate.Query
import org.hibernatespatial.GeometryUserType

class GeometryService {
    def sessionFactory
    boolean transactional = false
    // performs query which takes polygon as single position 0 argument
    def doWithPolygon(String queryString, Polygon polygon) {
        def session = sessionFactory.currentSession
        Query query = session.createQuery(queryString)
        Type geometryType = new CustomType(GeometryUserType.class, null)
        query.setParameter(0, polygon, geometryType)
        return query.list()
    }
}

Note that I had to drop down into the raw hibernate query created with session in order to set the parameter for the query type to a UserType. By default Grails uses the query.setParameter(0, polygon) .. form.

The above would permit a query such as

def pointsWithin = geometryService.doWithPolygon("from MyPoint mp where within(mp.point, ?) = true", myPoly.poly)

Where MyPoint was a previously devided object like this:

public class MyPoint {
    String name
    Point point
    static mapping = {
        point type: GeometryUserType
    }
    String toString() {
        return "$name (${point.x}, ${point.y}) "
    }
}