Rows and Columns

Geocoding takes a regular address and transforms it into a lattitude/longitude location on the earth. It is a very useful activity if you have a database full of addresses and you want to start applying some basic geo-spatial functionality (such as calculating distances) or start doing some more in depth spatial application development.
To be able to geocode an address, you need to have access to a dataset which can effectively find the address you want on a map and return to you the co-ordinates. There are a few online services which can do this, one of which is Google. Google provides a web services api to the Google maps data.

Once you apply for and receive your Google Maps API key, geocoding is as simple as placing a URL in your browser, such as the following,which returns the lat/lon coordinates for the Sybase iAnywhere building in Waterloo, Ontario, Canada:
http://maps.google.com/maps/geo?q=445+Wes+Graham+Way,+Waterloo,+ONTARIO&output=json&oe=utf8&sensor=true_or_false&key=mymapsapikey

So, now I want to apply this to a bunch of addresses in my SQL Anywhere database. Using the web services support, this is easy. I simply create a web service to call the Maps geocoding service as follows:

CREATE PROCEDURE GoogleGeoCode( address varchar(1024) )
URL 'http://maps.google.com/maps/geo?q=!address&output=xml&oe=utf8&sensor=true_or_false&key=myapikey'
TYPE 'HTTP'

Note that we use “!address” in the URL to indicate we want to use the address argument.
Of course, we can fancy up the procedure call to automatically pull the address from a database table, format the address properly for the URL (eg. replace spaces in the address), and push the results into another table, but this gives you the basic idea.
The output argument of the web service call allows you to get results in JSON, XML(KML) or CSV format. our service returns csv to make the example simple. In a later post, we will examine the JSON and XML outputs to deconstruct them and view/understand a more complete response from the webservice call.

To use the procedure, we call it from a SQL statement. Because the returned value from the webservice call is a standard html response, we have to use some special syntax to access our result value:

SELECT "value" 
FROM GoogleGeoCode( '445+Wes+Graham+Way,+Ontario' ) 
         WITH (Attribute LONG VARCHAR, Value LONG VARCHAR) 
WHERE Attribute = 'body';

This returns us the result
“200,8,43.4791432,-80.5522735″
The first number is our return code (success), the second is a measure of accuracy. 8 indicates address level accuracy, which is what we were after. The last 2 are our lat/lon coordinates. We can use SQL Anywhere’s OPENSTRING clause to automatically format these results for us, like in the following example:

--create a variable to store the result of the webservice call
CREATE VARIABLE geocode_result long varchar;
--call the web service
SELECT "value" INTO geocode_result 
FROM GoogleGeoCode( '445+Wes+Graham+Way,+Ontario' ) 
         WITH (Attribute LONG VARCHAR, Value LONG VARCHAR) 
WHERE Attribute = 'body';
--Turn the resulting csv string into a row in a table
SELECT * 
FROM OPENSTRING( VALUE geocode_result ) 
         WITH (return_code integer, accuracy integer, lat double, lon double) 
         OPTION( DELIMITED BY ',' ) AS location

The result of the above batch is a table like this: