Tuesday, August 12, 2014

Groovy | Consuming Google Geocoding API

Large number of newly born online businesses have need for some sort of geo location based logic, whatever it's displaying 'products near consumer's location, match-making profiles in visitors neighborhood or simply advertising restaurants, stores and services near user. For each of this operations server side code needs to determine location of user, or location of some other entity (product store, advertised person / business). While people (and post office) work great with addresses, computers don't, and as such there is need to convert physical address to numbers, that is into latitude / longitude pair.

If you are interested in how geo-coding algorithms work, you can read more details here. However, if you just want a tool (service) that works, there is lots of options, I've listed couple of them below, but note that this surely is not final list - intention of this post is to demonstrate consuming Google's geocoding API from Groovy / Grails. You can find full list of Geocoding services here - http://en.wikipedia.org/wiki/List_of_geocoding_systems 

  1. Use Geonames.org database and setup your own database and service. Database can be downloaded from http://www.geonames.org/export/, and implement your own lookup - and geo-code logic. Pros of this approach is price - there is no charges, as everything is hosted by yourself (hosting charges apply, ofcourse). On the other side, implementing such thing is not trivial project, and I would use this approach if you are big as Facebook or any other massive online application,  and have millions of geo-coding request every hour.
  2. Use Geonames free API  - details of API can be found at http://www.geonames.org/export/ws-overview.html. Note that that are service limits that apply, and you're running commercial business you should consider singing up for premium webservices - more info  here - http://www.geonames.org/commercial-webservices.html
  3. My preferred way is to consume Google's web services, and it's Geocoding API. API specification can be found here . For example, geocoding street of Dositejeva, near Belgrade's national theater, you would parse response for following REST call

    https://maps.googleapis.com/maps/api/geocode/xml?address=Dositejeva,%20Belgrade,%20Serbia

    You'll see many pieces of data in example above, but what was specifically important for me is extracting:
    1. Latitude  / longitude pair
    2. State for given address
    3. Country for given address
         Latitude / longitude can be found at result/geometry/location XML node, that is in result.geometry.location property if you are requesting JSON as response format. In addition, location is split by 'address components'. To find address state, you should search for address component with type 'administrative_area_level_1', and for country, simple search for address component with type 'country' is enough. 

Below is examples in groovy programming language on how to get latitude / longitude pairs, state and country for given physical address

Listing 1 : Geocode physical address in lat/lng pair in groovy


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
import java.util.logging.Logger

final String GEOCODING_API_HOST = 'https://maps.googleapis.com'

def getGoolgeApiKey = {
    //place your API key kere if you have one
    return ''
}


@Grab(group='org.codehaus.groovy.modules.http-builder', module='http-builder', version='0.7' )
def getLatLng =  { String query ->
        def rval = null
        try{
            new groovyx.net.http.HTTPBuilder(GEOCODING_API_HOST).get([path: '/maps/api/geocode/json', query: [key : getGoolgeApiKey(), address: query, sensor: false]]) { resp, json ->
                rval = json
            }
        }catch(org.apache.http.client.ClientProtocolException ex){
           println ("Protocol error while connecting to  ${GEOCODING_API_HOST}:" + ex.message)
        }catch(IOException ex){
           println ("I/O  error while connecting to  ${GEOCODING_API_HOST}:" + ex.message)
        }catch(URISyntaxException ex){
           println ("URI mallformed: ${GEOCODING_API_HOST}")
        }

        
        return [lat: rval.results.geometry.location[0].lat, lng:rval.results.geometry.location[0].lng]
}
    
println getLatLng('100 John St, New York, NY, US')

Listing 2 : Extract state information from physical address



 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
import java.util.logging.Logger

final String GEOCODING_API_HOST = 'https://maps.googleapis.com'

def getGoolgeApiKey = {
    //place your API key kere if you have one
    return ''
}


@Grab(group='org.codehaus.groovy.modules.http-builder', module='http-builder', version='0.7' )
def getCountryAndState =  { String query ->
        def rval = null
        try{
            new groovyx.net.http.HTTPBuilder(GEOCODING_API_HOST).get([path: '/maps/api/geocode/json', query: [key : getGoolgeApiKey(), address: query, sensor: false]]) { resp, json ->
                rval = json
            }
        }catch(org.apache.http.client.ClientProtocolException ex){
           println ("Protocol error while connecting to  ${GEOCODING_API_HOST}:" + ex.message)
        }catch(IOException ex){
           println ("I/O  error while connecting to  ${GEOCODING_API_HOST}:" + ex.message)
        }catch(URISyntaxException ex){
           println ("URI mallformed: ${GEOCODING_API_HOST}")
        }
        
        def result = ['state':'',country : '']
        
        rval.results?.address_components[0].each {
            if (it.types.contains('administrative_area_level_1')) {
                result['state'] = it.long_name
            }
            if (it.types.contains('country')) {
                result['country'] = it.long_name
            }
        }
           
        return result
}
    
println "State / Country for '1018 maddison ave' : " +  getCountryAndState('1018 maddison ave')

Note: Above examples were compiled and tested against Groovy version 2.1.9

You can as well checkout full Geocoding utility class used in Grails application here - http://pastebin.com/hK6veMe1


Monday, August 11, 2014

Hibernate | 'unexpected AST node' - Suming boolean column in HQL

Today I've encountered this error developing Grails application that executes HQL (Hibernate Query Language) code:

unexpected AST node

HQL Query is supposed to pull all patients in  database that are marked as active, but have all of their treatments marked as inactive:


FROM PatientTreatment PT 
JOIN PT.patient P 
GROUP BY P.id  
HAVING SUM(case when PT.isActive then 1 else 0 end) = 0 
AND P.isActive=true 

However, after initial run - i got error mentioned above 'unexpected AST node' - which  implies there is something wrong with query syntax (unexpected abstract tree node).  It was obvious that problem is within sum() function, and after couple of minutes of research I've realized that HQL can't automatically consider 'PT.isActive' as expression, but rather this property's value MUST be explicitly given. So, HQL that does work is listed below:


FROM PatientTreatment PT 
JOIN PT.patient P 
GROUP BY P.id  
HAVING SUM(case when PT.isActive = true then 1 else 0 end) = 0 
AND P.isActive=true 


Hope this saves someone's time.

Thursday, August 7, 2014

Groovy | Generating map of U.S states and cities




I couldn't find anywhere on the web JSON file for download that has U.S state two-letter abbreviations as keys, and simple array of city names as values. I needed this JSON file to stick into AngularJS application, removing the need to pull data from the server.

What I did is - I've primarily found  list of state abbreviations, and secondary U.S. Small business administrations API for listing all counties and cities within one state - which resulted in Groovy script generating wanted JSON file.

State list is taken from  http://adamkinney.com/blog/2012/04/25/list-of-us-states-in-javascript-object-notation/, with some states removed (ones that SBA API did not recognize, like Palau)

Groovy script follows:


// list taken from http://adamkinney.com/blog/2012/04/25/list-of-us-states-in-javascript-object-notation/, and 
// transformed into Groovy notation
def states = [
    [ name: 'ALABAMA', abbreviation: 'AL'],
    [ name: 'ALASKA', abbreviation: 'AK'],
    [ name: 'ARIZONA', abbreviation: 'AZ'],
    [ name: 'ARKANSAS', abbreviation: 'AR'],
    [ name: 'CALIFORNIA', abbreviation: 'CA'],
    [ name: 'COLORADO', abbreviation: 'CO'],
    [ name: 'CONNECTICUT', abbreviation: 'CT'],
    [ name: 'DELAWARE', abbreviation: 'DE'],
    [ name: 'DISTRICT OF COLUMBIA', abbreviation: 'DC'],
    [ name: 'FLORIDA', abbreviation: 'FL'],
    [ name: 'GEORGIA', abbreviation: 'GA'],
    [ name: 'GUAM', abbreviation: 'GU'],
    [ name: 'HAWAII', abbreviation: 'HI'],
    [ name: 'IDAHO', abbreviation: 'ID'],
    [ name: 'ILLINOIS', abbreviation: 'IL'],
    [ name: 'INDIANA', abbreviation: 'IN'],
    [ name: 'IOWA', abbreviation: 'IA'],
    [ name: 'KANSAS', abbreviation: 'KS'],
    [ name: 'KENTUCKY', abbreviation: 'KY'],
    [ name: 'LOUISIANA', abbreviation: 'LA'],
    [ name: 'MAINE', abbreviation: 'ME'],
    [ name: 'MARYLAND', abbreviation: 'MD'],
    [ name: 'MASSACHUSETTS', abbreviation: 'MA'],
    [ name: 'MICHIGAN', abbreviation: 'MI'],
    [ name: 'MINNESOTA', abbreviation: 'MN'],
    [ name: 'MISSISSIPPI', abbreviation: 'MS'],
    [ name: 'MISSOURI', abbreviation: 'MO'],
    [ name: 'MONTANA', abbreviation: 'MT'],
    [ name: 'NEBRASKA', abbreviation: 'NE'],
    [ name: 'NEVADA', abbreviation: 'NV'],
    [ name: 'NEW HAMPSHIRE', abbreviation: 'NH'],
    [ name: 'NEW JERSEY', abbreviation: 'NJ'],
    [ name: 'NEW MEXICO', abbreviation: 'NM'],
    [ name: 'NEW YORK', abbreviation: 'NY'],
    [ name: 'NORTH CAROLINA', abbreviation: 'NC'],
    [ name: 'NORTH DAKOTA', abbreviation: 'ND'],
    [ name: 'OHIO', abbreviation: 'OH'],
    [ name: 'OKLAHOMA', abbreviation: 'OK'],
    [ name: 'OREGON', abbreviation: 'OR'],
    [ name: 'PENNSYLVANIA', abbreviation: 'PA'],
    [ name: 'PUERTO RICO', abbreviation: 'PR'],
    [ name: 'RHODE ISLAND', abbreviation: 'RI'],
    [ name: 'SOUTH CAROLINA', abbreviation: 'SC'],
    [ name: 'SOUTH DAKOTA', abbreviation: 'SD'],
    [ name: 'TENNESSEE', abbreviation: 'TN'],
    [ name: 'TEXAS', abbreviation: 'TX'],
    [ name: 'UTAH', abbreviation: 'UT'],
    [ name: 'VERMONT', abbreviation: 'VT'],
    [ name: 'VIRGIN ISLANDS', abbreviation: 'VI'],
    [ name: 'VIRGINIA', abbreviation: 'VA'],
    [ name: 'WASHINGTON', abbreviation: 'WA'],
    [ name: 'WEST VIRGINIA', abbreviation: 'WV'],
    [ name: 'WISCONSIN', abbreviation: 'WI'],
    [ name: 'WYOMING', abbreviation: 'WY' ]
],

//this our map that will be seriazlied to JSON
allData = [:]

//iterate through all of the states
states.each { it ->
    def twoLetterCode = it.abbreviation,
         //grab the data from the api
        citiesJson =  new URL("http://api.sba.gov/geodata/primary_city_links_for_state_of/${twoLetterCode}.json").text,
        citiesMap = new groovy.json.JsonSlurper().parseText(citiesJson)
   
    allData[twoLetterCode] = []
        
     //iterate through all cities and add them to result set   
    citiesMap.each { city ->
        allData[twoLetterCode].push(city.name)
    }
    
    //sort alphabetically asceding (alphabetical sort is default for String object in Groovy)
    allData[twoLetterCode].sort()
}
//serialize Groovy map to JSON
def serializedData = new groovy.json.JsonBuilder(allData).toPrettyString()
//and write to file
new File('/tmp/usCitiesData.json') << serializedData

return true

You can see generated file here - http://pastebin.com/GSpzczfi