GeoCoding directly from Excel: Comparing Bing, Yahoo and Google Maps.

This article actually started out as a different one. Namely how to create an Excel application that would populate an Excel table with GeoCoded data (Country, State, Street, postal code, co-ordinates etc), given potentially incomplete addresses. It would be parameter driven, and could be implemented in any workbook without coding. You will be able to read about that too, and to download the finished application, but along the way I thought I might as well compare the main Mapping APIs too.

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

By bruce mcpherson   Popularity  (4031 Views)