Thursday, January 6, 2011

Groovy/Grails and Oracle Spatial (Part 1)

Below I document a relatively lightweight approach to perform a distance based search in Oracle Spatial using Groovy and Grails. First some background, we need to calculate reporting metrics for an ocean observing system, one of those metrics is to find all XBT drops within a specified distance of high priority tracklines.

The ultimate goal is to create a web page where a user can specify a trackline and a distance to generate a report on the number of XBT drops along that transect. To begin I'll create a groovy service class to begin testing out my Oracle Spatial Query.

First we specify a database connection (see def serviceDbHandle). You'll also see a createDbHandle method which first checks to see if the db handle has already been made (which is the case when running in grails) otherwise it creates the connection (such as when we are running a unit test of the service in groovy).

Next, our serviceMethod accepts a parameter for sensor type, transect identifier, and a distance. In this example, we are looking for all XBT observations (points) that fall within 2KM of transect AX07. Lastly, we capture are query results as a list of domain objects representing the observations that meet our criteria.

SearchByDistance Service:
//requires Oracle jar in classpath
package osmcreporter

import groovy.sql.Sql

class SearchByDistanceService {

static transactional = false

def serviceDbHandle = null

def setDbHandle(dataSource) {
serviceDbHandle = new Sql(dataSource)

//This method allows us to test from Groovy and Grails
//Lazy init the servicDbHandle
def createDbHandle () {
if (serviceDbHandle) return serviceDbHandle
serviceDbHandle = Sql.newInstance(
'myuser', 'mypasswd', 'oracle.jdbc.driver.OracleDriver')
return serviceDbHandle

def serviceMethod(sensor_type, transect_id, dist) {

println "\nBegin dsrs.serviceMethod()"
def DailySummaryObservations = []
def distance = "distance=$dist"
def sql = """select ds.platform_id as "platformId", ds.platform_code as "platformCode", ds.parameter_standard_name as "paramStandardName", ds.observation_value as "obsValue", ds.observation_depth as "obsDepth", ds.sensor_type as "sensorType", ds.observation_date as "obsDate", ds.observation_location.sdo_point.y as "obsLat", ds.observation_location.sdo_point.x as "obsLon", ds.platform_type_name as "platformTypeName" from OSMCV4.DS_XBT_2010 ds, OSMCV4.XBT_TRAJECTORY_TSQL xbt where sensor_type = ${sensor_type} AND xbt.line = $transect_id and SDO_WITHIN_DISTANCE(ds.observation_location, xbt.shape, '$distance') = 'TRUE'"""
serviceDbHandle.eachRow(sql) {
DailySummaryObs dso = new DailySummaryObs(it.toRowResult())
DailySummaryObservations << dso
println "End dsrs.serviceMethod()"
return DailySummaryObservations


Domain class representing the observations:
package osmcreporter

class DailySummaryObs {

Long platformId
String platformCode
String orgName
String countryName
String platformTypeName
Date obsDate
String paramStandardName
String paramUnits
String sensorCode
String sensorType
Integer sensorObsCount
Integer dailyObsCount
Double obsDepth
Double obsValue
Double obsLat
Double obsLon


Now we'll set up a test class to view our results.
package osmcreporter

import grails.test.*

class SearchByDistanceServiceTests extends GrailsUnitTestCase {
protected void setUp() {

protected void tearDown() {

void testSomething() {
def dsrs = new SearchByDistanceService()
def List dsos = dsrs.serviceMethod('XBT','AX07','200000')
assert dsos.size() >= 0

And run the test (provide a program argument - test/unit/osmcreporter/SearchByDistanceServiceTests.groovy):

That's it! We have a working Oracle Spatial query running as a service in groovy whose results populate our domain object that holds the observational data. We also have a unit test case for the service. In our next post, we'll build out the rest of our grails architecture. Namely a GSP page to accept user input, a controller to handle the user's submission, and a barebones GSP page to display the results.

No comments: