Mollusca Code

Calling stored procedures from Grails is not that hard to achieve. One needs to wade through the HQL documentation though, which I find slightly counter-intuitive. I’d rather see stuff like that on the grails page.

So, how do we call a stored procedure then?

What stored procedure?

Here’s the situation:

• The events from the last post are in the database now.
• The database has been spatially enabled according to the PostGIS plan.

We now want to find the closest events, to a certain point on the earth. Let’s assume we have successfully geocoded the point, so we have its coordinates in lat;long form.

The basic function that we want to call in plain SQL is:

select * from event e
  order by distance_sphere(
  Geometry('POINT(lat, long)'),e.location_id)

The distance_sphere() function calculates the distance of two points on the earth given in lat;long form. However the function takes two argumenty of type geometry, so we have to create one first.

Calling it from grails

To call this function from grails and get a nice an clean Collection of objects back (we don’t want to mess with ResultSet, do we?) we have to grab a hold of the current hibernate session. This is pretty easy: We just have it injected by grails:

Add the following line to the EventController class:

def sessionFactory

The sessionFactory object is now defined inside of this controller and will have its value injected by grails. Assuming that the function is called by some form using POST, the code for our controller function boils down to this:

def searchByLocation = {
  if (request.method == "POST"){
     def lat = params['lat']
     def long = params['long']

     def session = sessionFactory.currentSession

     def sql = "select * from " +
     "event e order by distance_sphere(setsrid" +
     "(geometry('POINT(${lat} ${long})'), 0)," +
     "e.location_id) limit 20;" 

     def results = session.createSQLQuery(sql).
       addEntity(Event.class).list()
  }
}

The setSRID() function from PostGIS has to be called, because by default grails in combination with hibernatespatial stores the points with the SRID value set to 0. This is no problem, as long as all coordinates you save are in the same reference system and you do not have to do any coordinate transformation. Otherwise you would have to handle that as well.

What’s nice about this method of calling the stored procedure through hibernate is that by calling addEntity() we can set the returned type and calling list() we get a nice and clean java collection.

That’s it so far…

Important note (This is new)

As pointed out by Matt, this code is vulnerable to SQL-Injection type attacks, if used directly like that in a setting where this is of any use to the attacker.

Please, with sugar on top, always sanitize form input data, before parsing it into a database query. In this case, this would have meant more code, but I should have added a note to the code. Mea culpa. To fix this simply make sure that the entered text looks exactly like float data and nothing else.