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(
   'jdbc:oracle:thin:@myserver:1521:MYINSTANCE',
   '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"
  createDbHandle()
        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() {
      super.setUp()
  }

  protected void tearDown() {
      super.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.