Complete Excel Address Data with Google Mapping API

Public Sub bingMarkingVizVenues()
    bingMarkingExample cVizAppVenues
End Sub
Public Sub googleMarkingVizVenues()
    googleMarkingExample cVizAppVenues
End Sub

Given an incomplete address, let’s look at how to ask Google Maps to clean it up, decode the response and organize it into country, state and other components.  In order to see what this is about, download and try the example.

Google provides a response from this URL , http://maps.googleapis.com/maps/api/geocode/json?address=xx, where ‘xx’ is the address.  In previous articles,  we already know how to deal with a jSon response from Google and how to populate Excel with the response.  The subject of this article is to decode the response. As you will see it is not an ‘exact science’, so the decoding process is highly configurable so I can tweak it over time and find out more about exactly how it works.  Remember that this is the first stage in plotting summarized data on a map, and as per the Google Terms and Conditions, you should not use this as a way to just clean up address data.

In this example we are going to populate a ‘customer master’ which has an unstructured address, with Google’s interpretation of its components.

The cDataSet capability makes getting parameters easy. The parameter sheet has 2 blocks. One that describes the key input fields, and another that describes the treatment of the API response.

Two mandatory fields are required, an ID and the address to be decomposed. The first parameter block describes the column names you have chosen to give them.

Here are the steps

1. Create datasets of the Customer master Sheet, the decoding rules parameter block and the Fields parameter block.
2. Check that the ID and Input address column exists.
3. For each row in the Customer Master,  send a  Google API request with the unstructured input address
4. Deserialize the response, and assuming status is “OK”, go off and decode each column according to the parameter rules.

Option Explicit
' NOTE: before using this please read the Google terms and conditions
' http://code.google.com/apis/maps/terms.html#section_10_12
' this example is purely for educational purposes and is a step in creating a google map
Const cApiUrl = "http://maps.googleapis.com/maps/api/geocode/json?address="

Dim iDebug As Long
' this is all about parameter names


Public Sub googleMappingExample()
    Dim dSets As cDataSets
    Dim ds As cDataSet, dr As cDataRow, sWire As String, dFields As cDataSet
    Dim jo As cJobject, cb As cBrowser, sReq As String, dRules As cDataSet
    ' populate the customer master
    Set dSets = New cDataSets
    iDebug = 0
    With dSets
        .create
        Set ds = .init(wholeSheet(cCustMaster), , cDataMaster, , , True)
        ' create the parameter page deserialization data set
        Set dRules = .init(wholeSheet(cParamSheet), , cParamRules, True, cParamRules)
        ' create the parameter page data set
        Set dFields = .init(wholeSheet(cParamSheet), , cParamFields, True, cParamFields)

        With dFields
        ' check that the required fields are present in the input data
          If Not ds.HeadingRow.Validate(True, _
                .Cell(cFieldID, cFieldValue).toString, _
                .Cell(cFieldAddress, cFieldValue).toString) Then
            Exit Sub
          End If
        End With
    End With
    ' 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
    For Each dr In ds.Rows
        sReq = cApiUrl & URLEncode( _
            dr.Cell(dFields.Cell(cFieldAddress, cFieldValue).toString).toString) & _
            "&sensor=false"
        ' get mapping result
        sWire = cb.httpGET(sReq)
        Set jo = New cJobject
        With jo.deSerialize(sWire)
            If .isValid Then
            ' now we have a cjobject of every field in the response
                If .Child("status").toString = "OK" Then
                    ' all is good go and find necessary fields
                    findSuitableJob .Child("results"), dr, Range("debug!a2")
                Else
                    MsgBox ("Unable to geomap - status " & .Child("status").toString & ":url" & sReq)
                End If
            Else
                MsgBox ("Badly formed jSon response received to " & sReq)
            End If
        End With
        
        Set jo = Nothing
    Next dr

    Set dSets = Nothing
    Set cb = Nothing
End Sub

Finding the appropriate values and populating

1. Find the Country this address applies to in order to deal with any special country-level issues, and from that encode the state (if it’s needed)
2. For each cell that is required to be copied over, find the most appropriate match in the API response. Note this needs a recursive approach since we need to get deep enough into the structure to match the depth of the parameter component.

Private Sub findSuitableJob(job As cJobject, dr As cDataRow, _
                                   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, _
            sCountry As String, stateLevel As String
    
    ' US is different processing than other countries so first get what country we are doing
    Set jo = mappingFind(job, "address_components", "country", "short_name", "", "")
    sCountry = "Caprica"
    If jo Is Nothing Then
        MsgBox ("couldnt find country - assume non-US")
    Else
        sCountry = jo.toString
    End If
    With dr.Parent.Parent.DataSet(cParamRules)
        stateLevel = getStateLevel(LCase(Trim(sCountry)), LCase(Trim(.Cell("state", "rules").toString)))
    End With
    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 = mappingFind(job, _
                    LCase(.Cell(sName, "component").toString), _
                    LCase(.Cell(sName, "type").toString), _
                    LCase(.Cell(sName, "variation").toString), _
                    LCase(.Cell(sName, "special").toString), _
                    stateLevel, 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

Private Function mappingFind(job As cJobject, _
            sComponent As String, sType As String, sVariation As String, sSpecial As String, _
            stateLevel 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 mappingFind = job
            Exit Function
        End If
    Else
        ' looking for something like
        ' _deserialization.results.1.address_components.7.types.1
        If sKey Like "*" & sComponent & ".*.types.*" Then
            ' special processing for 'state'
            st = sType
            If "state" = sSpecial And isState(sValue) Then st = st & stateLevel
            If sValue = st Then
                ' got a type match, get the reqquested variation
                ' for example _deserialization.results.1.address_components.7.long_name
                Set jResult = job.Parent.Parent.ChildExists(sVariation)
                If jResult Is Nothing Then
                    MsgBox ("Variation " & sVariation & " doesnt exist " & " for " & job.Parent.Parent.fullKey)
                Else
                    Set mappingFind = jResult
                    Exit Function
                End If
            End If
        End If
    End If

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

End Function
Private Function isState(st As String) As Boolean
    isState = (LCase(st) Like "administrative_area_level_*")
End Function
Private Function getStateLevel(sc As String, sr As String) As String
    ' this will be a format like US;CN;BE=1,default=2
    Dim a As Variant, I As Long, deflt As String, b As Variant
    a = Split(sr, ",")
    For I = LBound(a) To UBound(a)
        b = Split(a(I), "=")
        If UBound(b) - LBound(b) <> 1 Then
            MsgBox ("Invalid state rule " & sc)
            Exit Function
        Else
            If b(LBound(b)) = "default" Then
                getStateLevel = b(UBound(b))
            ElseIf b(LBound(b)) = sc Then
                getStateLevel = b(UBound(b))
                Exit Function
            End If
        End If
    Next I
        
End Function

Summary