GeoCoding
The purpose of this application is, given addresses in various states of accuracy,
to complete each data column in an Excel table with data (city, state, co-ordinates,
anything else we can find) derived from the response to a RESTful geoCode query
to a mapping API provider. The co-ordinates will later be used to plot these
locations on a map, and the derived data will be used to roll up to various summary
levels that will be visualized as a layer on those maps. The application will
be parameter driven so that it can be implemented in any workbook without having
to do any (or much) coding. This article won't go into all of the coding,
but you can download the whole thing from the Excel Ramblings Website.
The APIs
This project actually started out with the intention of implementing this in Google
Maps, but I thought I might as well implement it for Yahoo and Bing as well to
see how they compared. I would have done Ovi Maps as well, but I couldn;t find
the REST Url. If anyone knows it let me know on this forum or contact me here and I will implement that too. Note that you must read the Terms and conditions
of each of the providers. The URLs are in the each of the example modules in
the downloadable example. In general these APIs are for plotting locations on maps, not for bulk 'address
cleaning'. In this application we are simply preparing the data for plotting
on maps. Note also that the comparisons I will be making is based simply on the
response to the requests on the http: interface (we are after all writing an
application that is to work directly from Excel), and not on the capabilities
available if you were to use the full API using javaScript or similar. What follows
then is my first impression, probably biased, and certainly unscientific of how
these API's compare for geoCoding data straight from Excel.
The input Data
Google Maps Response
Very successful at dealing with the various levels of detail and innaccuracy in the
input addresses. It had some problems with the sketchy address in Argentina (funny
because it's the address of their Buenos Aries office from their web site), and
it totally failed to pick up the street in Egypt, even though it was in the
original address. However decoding the Google response was tricky, since depending
on the country and various other factors, it returns the state (for example)
in different fields. More of that later when we look at the parameter set up.
Yahoo Placemaker Response
Did almost as well as Google and in a couple of cases slightly better. Strangely
enough, Yahoo does not provide a state abbreviation nor a 'cleaned up address'
(not a big deal, you can construct that from the various components I guess).
On the other hand, a very straightforward response, clutter free and simple to
decode.
Bing Maps Response
Absolutely pathetic. In 5 cases of the 12 cases, it could not even provide a response.
Very oddly, for the Egypt address, it provides a response (which it anyway gets
wrong) only 1 time out of two. It also does not provide a country short code
in its standard response. The response also has a whole bunch of copyright, marketing
and other unneccesary fluff that is right now be flying around cluttering up
the internet every time it is used. Finally it doesn't deal well with messy
(non printable such as line feed) input., whereas the others correctly, just
treat it as white space.
Getting a Key
You'll find details on how to get a key for each of the services in comments
in the downloadable modules. In the case of Google, you dont need a key any more, but it does restrict you to
a small number of queries daily (I dont know how it knows it's actually you-
I assume there is a cookie somewhere). The other 2 need you to register for a
key, which you then include in your query URL. In the case of Yahoo getting a
key is pretty easy - you just go to the developer center, answer a couple of
questions, and it gives you a nice short key right there. Bing is pretty straightforward
also - in fact it's so straightforward it has a video containing an 11 page
powerpoint presentation telling you how to do it. Once you've done all that
you'll eventually create an account and search around and find your 64 character!
key that you append to each query you make. In the example application you'll
find that the keys can be specified on the parameter sheet. Please get your own
key if you are going to implement anything from the example.
The parameter sheet
One of the design criteria for this application is to be able to fill in a sheet
with minimal coding, with everything being parameter driven. In the workbook
you download, googlemapping.xlsm, you wil find a sheet called 'Parameters'.
This is used to describe how to populate each column of your workbook from each
of the different providers, but before we go there, let's look at the basic
reponse format.
Using Yahoo as an example, the request (you need to replace appid=xxxxxxxx with your own key)
http://where.yahooapis.com/geocode?location=1600%20Amphitheatre%20 Pkwy%2C%20Mountain%20View%2C%20CA%2094043%2C%20USA
&flags=J&appid=xxxxxxxxxx
Generates this jSon response
{"ResultSet":{"version":"1.0","Error":0,"ErrorMessage":"No
error", "Locale":"us_US", "Quality":87, "Found":1,
"Results": [{"quality":87, "latitude":"37.423232",
"longitude":"-122.085569","offsetlat":"37.423109",
"offsetlon":"-122.085598", "radius":500,"name":"",
"line1":"1600 Amphitheatre Pky", "line2":"Mountain
View, CA 94043-1351","line3":"", "line4":"United
States", "house":"1600","street":"Amphitheatre
Pky","xstreet":"","unittype":"", "unit":"",
"postal":"94043-1351","neighborhood": "",
"city":"Mountain View", "county":"Santa Clara
County","state": "California", "country":"United
States","countrycode":"US","statecode":"CA",
"countycode":"","uzip":"94043", "hash":"92F5774D0FC9C1AA",
"woeid":12797130,"woetype":11}]}}
The responses from Bing are a little more complicated, and from Google much more
so. If you have seen any of my previous articles on this site or visited my web
site Excel Ramblings you will have seen reference to how to populate Excel from jSon, and how to abstract data in Excel. We will use the classes that were covered in those articles to deal with parameter
sheets, decode jSon and populate our workbook. The Yahoo response decodes to
a cJobject that looks like this, and we use those key names to map the response to column names
in the parameter sheet. (There is a tab for each API in the downloadable workbook
that lists the names of each key returned by the api)
Here are the parameters for Yahoo
Using this method, we can match Excel column names to any jSon field and populate
an Excel table from the response. I mentioned that the Google response is very
much more complex, and it therefore needs special treatement since it returns
different values depending on the country. That is dealt with by parameters as
follows. For more information take a look at this more detailed comparison.
Some code
Most of the code for the classes here have been covered either in other articles
on this site or on Excel Ramblings and of course all code is included in the googleMapping downloadable example.
Here is the code for the main module of the Yahoo example.
Option Explicit
' NOTE: before using this please read the Yahoo terms and conditions
' http://info.yahoo.com/legal/us/yahoo/api/api-2140.html
' http://info.yahoo.com/legal/us/yahoo/applicationplatform/applicationplatform-3959.html
' this example is purely for educational purposes and is a step in creating a
yahoo map
' YOU NEED TO GET YOUR OWN Yahoo KEY TO BUILD AN APPLICATION WITH THIS CODE
Const cYahooApiUrl = "http://where.yahooapis.com/geocode?location="
Public Sub yahooMappingExample()
Dim dSets As cDataSets, dr As cDataRow, sInput As String
Dim jo As cJobject, cb As cBrowser, sReq As String, sWire As String
iDebug = 1
Set dSets = dSetsSetup
If dSets Is Nothing Then Exit Sub
' now we have the data file and know that it contains an ID column and an
address column
' now geocode the addresses - one by one (the final version will do it as
a batch)
Set cb = New cBrowser
With dSets
For Each dr In .DataSet(cMaster).Rows
sInput = rxReplace("nonprintable", _
dr.Cell(.DataSet(cParamFields).Cell _
(cFieldAddress, cFieldValue).toString).toString, " ")
sReq = cYahooApiUrl & URLEncode(sInput) & _
"&flags=J&appid=" & _
dSets.DataSet(cYahooParameters).Cell("Key", "Value").toString
' get mapping result
sWire = cb.httpGET(sReq)
Set jo = New cJobject
With jo.deSerialize(sWire)
If .isValid And Not .ChildExists("ResultSet") Is Nothing
Then
' now we have a cjobject of every field in the response
With .Child("ResultSet")
If .Child("Error").Value = 0 Then
' all is good go and find necessary fields
If Not .Child("Results").hasChildren Then
MsgBox ("No results for " & ":url"
& sReq)
Else
fullkeySuitableJob .Child("Results"), _
dr, "yahoo component", "yahoo
special"
End If
Else
MsgBox ("Unable to geomap - status " &
.Child("ErrorMessage").toString & ":url" & sReq)
End If
End With
Else
MsgBox ("Badly formed jSon response received to " &
sReq)
End If
End With
Set jo = Nothing
Next dr
End With
Set dSets = Nothing
Set cb = Nothing
End Sub
Summary
Although this article is focused on Mapping, hopefully you can see that this same
technique can be applied to mapping any data that can be retrieved from a REST
request directly out of Excel. This opens up many web services to very simple,
parameter driven population.
As far as the comparison of the geoCoding capability of the mapping APIs, I would
go for Yahoo for simplicity, Google for richness and accuracy, and I wouldn't
bother with Bing at all. As always I look forward to you comments, questions,
feedback and requests on this site or at Excel ramblings