Mashing up Force.com APIs with ql.io (Part 2)

Last time, I described how we passed a call to the Force.com REST API through ql.io, a ‘declarative, data-retrieval and aggregation gateway for quickly consuming HTTP APIs’ created by eBay’s platform engineering group. In this blog entry, I’ll explain how we used ql.io to mash up the Force.com REST API with Google’s Geocoding API.

We wanted to create a web service that, given an account name, returned its billing address in the usual form (for example, 1 Market St, San Francisco, CA 94105, USA) and as latitude/longitude coordinates. The ql.io docs provided a table definition for the Google Geocoding API, to which we added a test query:

create table google.geocode
  on select get from "http://maps.googleapis.com/maps/api/geocode/json?address={address}&sensor=true"
  resultset 'results'

select * from google.geocode where address = '1 Market St, San Francisco, CA 94105, USA'

We plugged this into the ql.io console (click here to try it yourself), and got a whole bunch of information:

[
  {
    ...LOTS OF JSON...
    "formatted_address": "1 Market Street, San Francisco, CA 94105, USA",
    "geometry": {
      "location": {
        "lat": 37.7940782,
        "lng": -122.3951331
      },
      "location_type": "ROOFTOP",
      ...MORE JSON...
    }
    ...YET MORE JSON...
  }
]

We extracted just the data we needed by being more specific in our query:

select geometry.location from google.geocode where address = '1 Market St, San Francisco, CA 94105, USA'

Yielding

[
  {
    "lat": 37.7940782,
    "lng": -122.3951331
  }
]

Now this worked just great for 1 Market St, SF, but we noticed that some addresses gave us more than we wanted:

select geometry.location from google.geocode
  where address = '345 Shoreline Park, Mountain View, CA 94043, USA'
[
  {
    "lat": 37.431498,
    "lng": -122.087148
  },
  {
    "lat": 37.3993163,
    "lng": -122.0809349
  },
  {
    "lat": 37.4327542,
    "lng": -122.0880901
  },
  {
    "lat": 37.3941695,
    "lng": -122.0840259
  }
]

Looking at the full output from the select * query on this address (omitted here, but visible in the ql.io console by clicking this link), we saw that there are multiple locations that match this address. In this case, it’s because this is a fictitious address on a demo account, but we wanted to handle the possibility that Google would return multiple coordinates for any given account address.

ql.io provides a ‘monkey patch‘ mechanism that allowed us to do just this. Running the ql.io server locally, we defined the following patch in a file, geocode.js, in the tables directory:

// Args: An object with uri, params, statement, response status,
// response headers, and body
exports['patch response'] = function(args) {
  var body = args.body;

  // Truncate Google GeoCode results to first entry
  if (body.results.length > 1) {
    body.results.length = 1;
  }

  return body;
}

So, if Google returns more than one set of coordinates, we simply take the first. Now we modified our table definition to use the patch, and saved it as google.geocode.ql, also in the tables directory:

create table google.geocode
  on select get from "http://maps.googleapis.com/maps/api/geocode/json?address={address}&sensor=true"
  using patch 'geocode.js'
  resultset 'results'

Now the same query returns a single result:

select geometry.location from google.geocode
  where address = '345 Shoreline Park, Mountain View, CA 94043, USA'
[
  {
    "lat": 37.431498,
    "lng": -122.087148
  }
]

Next we turned our attention to getting the relevant account. Based on the earlier work to retrieve all accounts, we quickly developed a table definition to return the relevant fields from a single account, given its name. We saved this as force.account.ql in our tables directory:

create table force.account
  on select get from "https://{instance_url}/services/data/v27.0/query?q=SELECT%20Name%2CBillingAddress__c%20FROM%20Account%20WHERE%20Name%20%3D%20%27{name}%27"
  using headers 'Authorization' = '{authorization}'
  resultset 'QueryResult.records'

That query parameter looks pretty complex, but if we run it through a URL decoder, all becomes clear:

SELECT Name,BillingAddress__c FROM Account WHERE Name = '{name}'

Now we set up an ad-hoc query in our console by defining authorization and instance_url, and providing name in the where clause:

instance_url = 'na9.salesforce.com'
authorization = 'Bearer MY_ACCESS_TOKEN'

select * from force.account where name = 'GenePoint'
{
  "type": "Account",
  "url": "/services/data/v27.0/sobjects/Account/001E0000002Jv2bIAC",
  "Name": "GenePoint",
  "BillingAddress__c": "345 Shoreline Park, Mountain View, CA 94043, USA"
}

Now we needed to mash up those two services. This is really where ql.io comes into its own – it allows SQL-like joins of two tables. In our case, a little documentation reading and experimentation led to:

select a.Name as name, a.BillingAddress__c as address, g.geometry.location as latlng
  from force.account as a, google.geocode as g
  where a.name = "GenePoint" and g.address = a.BillingAddress__c
[
  {
    "name": "GenePoint",
    "address": "345 Shoreline Park, Mountain View, CA 94043, USA",
    "latlng": {
      "lat": 37.431498,
      "lng": -122.087148
    }
  }
]

The final step was to define a route to expose the query as a web service – we saved this as account.ql in our server’s routes directory:

return select a.Name as name, a.BillingAddress__c as address, g.geometry.location as latlng
  from force.account as a, google.geocode as g
  where a.name = "{name}" and g.address = a.BillingAddress__c
  via route '/account?name={name}' using method get;

Now we could query the server using curl from the command line, passing instance_url and authorization as HTTP headers, as before:

$ curl -H 'instance_url: na9.salesforce.com' -H 'Authorization: Bearer MY_ACCESS_TOKEN' http://localhost:3000/account?name=GenePoint

With the result (formatted for clarity):

[
  {
    "name":"GenePoint",
    "address":"345 Shoreline Park, Mountain View, CA 94043, USA",
    "latlng":{
      "lat":37.431498,
      "lng":-122.087148
    }
  }
]

A successful mashup! In the third and final part of this series, we’ll explore user defined functions, and see how they allow us to augment the functionality of an existing web service, filtering Chatter posts on the body field. Watch this space!

tagged , , Bookmark the permalink. Trackbacks are closed, but you can post a comment.