Complete Excel Address Data with Yahoo Placemaker Maps API


Geocode Excel Data with Yahoo Placemaker get it now

If you haven't read about Yahoo rules on doing this, please do.

Since we have done this in Google Maps, and in Bing Maps, I thought it would be worthwhile to see how it's done in Yahoo Placemaker (Yahoo maps is deprecated and has been superseded by OVI maps .. which I guess I might try next) .  


GeoCoding with Yahoo Placemaker - vs - Google Maps -vs- Bing Maps

I have already looked at geocoding with Bing Maps -vs- Google Maps and Google Maps is a clear winner. Yahoo Placemaker is pretty good at first glance even though it  claims not to be a geocoder.  Note that I am just using the basic REST URI query in all these examples - since the whole point to access directly from Excel. Comparing capabilities under Javascript would be a whole different ballgame.
  •  Yahoo require you to get a Key to use Placemaker which they call and Application ID. In this downloadable example, I am using my key. If you use this code for anything, please get your own and follow the Yahoo rules on usage. Google do not require you to get a key, but limit your usage to a small number of queries daily.
  • Yahoo was as good as or better than Google in dealing with ambiguity. 
  • The Yahoo response is clean and does not contain a bunch of unnecessary waffle.
  • The Yahoo response is even simpler than the Bing one, and easier to deal with and decode. It is so similar to Bing that I was able to use the same modules to deal with both, so implementing Placeholder was about a 30 minute job, mainly to find out what the field names were. 
  • Yahoo provides less detail than both Google and Bing, but most of what you would need is there. It does not provide a 'cleaned up, full address' field though, which the other 2 do. On the other hand, this can easily be constructed from other fields.
  • Its confusing that Yahoo Placeholder is not actually Yahoo Maps. Yahoo Maps is actually OVI maps, which I haven't tried .. yet. 

The implementation

Essentially this is exactly the same kind of implementation as for the Google Version, so please take a look at that first and we can concentrate on the differences. 

The parameter sheet

The jSon response from Yahoo is a different structure to Google, but similar to Bing. It can easily be accommodated in the same structure as we used for the Google version. Here it is with a couple of extra columns specific to Yahoo contrasted against the Bing Section of our parameter sheet. Note that Bing does not provide a country code like US, DE etc - seems like a big omission actually - Google (although complicated to find), and  Yahoo both do.  


Note that the response from both Bing and Yahoo are simpler, so we can use the 'fullkey' parameter to identify the field within that response. 


The Yahoo Placemaker Key

As mentioned you need a developer app ID to use Yahoo. In the downloadable example I am using my key. You need to get your own by registering at the Yahoo developer center. Once you have it you can enter it in the parameter sheet, replacing the one that is in the parameter sheet.


The Response

An example call is generated as below

http://where.yahooapis.com/geocode?location=1600%20Amphitheatre%20 Pkwy%2C%20Mountain%20View%2C%20CA%2094043%2C%20USA &flags=J&appid=xxxxxxxxxx

and this the response - a lot briefer and more to the point than Bing or Google.

{"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}]}}

This response gets decoded into a cJobject and this data is used in populating the sheet.



The code

This is very similar to Complete Excel Address Data with Bing Maps API, and calls many of the same modules which I won't bother repeating here.  We are doing the same thing - namely geoCoding and filling in named fields such as country etc as specified, and according to the rules in a parameter sheet.  This is the yahooMappingExample module in the downloadable GoogleMapping.xlsm file.


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", _
                                       Range("debug!a2")
                            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



In fact since this was so close to the Complete Excel Address Data with Bing Maps API example, I generalized a few more procedures that could be used by both. These can be found in the Publics module in the downloadable GoogleMapping.xlsm file.

Option Explicit
Public Const cFieldID = "ID"
Public Const cFieldAddress = "Address"
Public Const cFieldValue = "Column Name"
Public Const cParamSheet = "Parameters"
Public Const cParamFields = "Fields"
Public Const cParamRules = "Column Name"
Public Const cTransactions = "Transactions"
Public Const cMaster = "Master"
Public Const cCopyFields = "Clone from Master"
Public Const cJoin = "Join"
Public Const cTab = "Tabs"
Public Const cName = "Name"
Public Const cSheet = "Worksheet"
Public Const cMeasure = "Measures"
Public Const cBingParameters = "Bing"
Public Const cYahooParameters = "Yahoo"
Public iDebug As Long
Public Function dSetsSetup() As cDataSets

    Dim dSets As cDataSets

    ' populate the customer master
    Set dSets = New cDataSets
    With dSets
        .create
        ' get the names of all the sheets
        .init wholeSheet(cParamSheet), , cName, True, cName
        ' create the parameter page deserialization data set
        .init wholeSheet(cParamSheet), , cParamRules, True, cParamRules
        .init wholeSheet(.DataSet(cName).Cell(cMaster, cSheet).toString), , cMaster, , , True

        ' create the parameter page data set
        .init wholeSheet(cParamSheet), , cParamFields, True, cParamFields
        .init wholeSheet(cParamSheet), , cBingParameters, True, cBingParameters
        .init wholeSheet(cParamSheet), , cYahooParameters, True, cYahooParameters
        With .DataSet(cParamFields)
        ' check that the required fields are present in the input data
          If Not dSets.DataSet(cMaster).HeadingRow.Validate(True, _
                .Cell(cFieldID, cFieldValue).toString, _
                .Cell(cFieldAddress, cFieldValue).toString) Then
            Exit Function
          End If
        End With
    End With
    Set dSetsSetup = dSets
End Function
Public Sub fullkeySuitableJob(job As cJobject, dr As cDataRow, _
                                spComponent As String, spSpecial As String, _
                                   Optional rDebug As Range = Nothing)
                                   
    'given a row, find the most appropriate object to populate it with
    Dim dc As cCell, pc As cCell, jo As cJobject, sName As String

    For Each dc In dr.Columns
        ' is this an interesting column ?
        sName = dr.Parent.Headings(dc.Column).toString
        With dr.Parent.Parent.DataSet(cParamRules)
            Set pc = .Cell(sName, 1)
            If Not pc Is Nothing Then
                ' it is a cell that needs filling in
                Set jo = fullkeyMappingFind(job, _
                    LCase(.Cell(sName, spComponent).toString), _
                    LCase(.Cell(sName, spSpecial).toString), _
                    rDebug)
                With dc
                    If jo Is Nothing Then
                        .Value = Empty
                    Else
                        .Value = jo.Value
                    End If
                    .Commit
                End With
            End If
        End With
    Next dc
    
End Sub

Public Function fullkeyMappingFind(job As cJobject, _
            sComponent As String, sSpecial As String, _
            Optional rDebug As Range = Nothing) As cJobject
                    
    ' given a column name, what's the best fit in the structure response from geocoding
    Dim sValue As String, jo As cJobject, jResult As cJobject, sKey As String
    Dim st As String
    
    ' these are the parameters we will work with
    sKey = LCase(job.fullKey)
    sValue = LCase(job.toString)

    ' if you need a list this will show all seen api response values
    If Not rDebug Is Nothing Then
        rDebug.Offset(iDebug, 0).Value = sKey
        rDebug.Offset(iDebug, 1).Value = job.Value
        iDebug = iDebug + 1
    End If

    If sSpecial = "fullkey" Then
        ' fullkey needs no further matching
        If sKey = sComponent Then
            Set fullkeyMappingFind = job
            Exit Function
        End If
    Else
        MsgBox ("Only full key implemented for this provider")
    End If

    ' recurse for children
    If job.hasChildren Then
        For Each jo In job.Children
            Set jResult = fullkeyMappingFind(jo, sComponent, sSpecial, rDebug)
            If Not jResult Is Nothing Then
                Set fullkeyMappingFind = jResult
                Exit Function
            End If
        Next jo
    End If

End Function


Summary

Complete Excel Address Data with Yahoo Placemaker Maps API was a breeze to implement, partly because the work had already been done with Complete Excel Address Data with Bing Maps API and Complete Excel Address Data with Google Mapping API. Nevertheless, the simple jSon response to the REST query was refreshingly clutter free, with only a couple of minor things missing.  Although I am focusing on Google Maps API for the projects in this section, I may return to this at a later date.  I look forward to your questions and comments at our forum.

You want to learn Google Apps Script?
Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly. 
If you prefer Video style learning I also have two courses available. also published by O'Reilly.
 Google Apps Script for Developers and Google Apps Script for Beginners.



Comments