Complete Excel Address Data with Yahoo Placemaker Maps API

•  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

The parameter sheet

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

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

{“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 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.