GeoCode addresses with Google Maps API
Quick start
- Download the Google Maps workbook (button above)
- Amend Columns A,B,C in the VenueMaster workbook to your data
- Hit one of the buttons on that page to geocode (with Google, Bing, yahoo , create a KML file or plot the addresses on a map using Google, Yahoo, Bing or OVI.
- Read how it works below. You will normally not need to do any coding – just possible tweak a parameter sheet. For example here is all that is needed to Plot addresses on a google map or a Bing Map, where the argument is the name of a parameter sheet.
Public Sub bingMarkingVizVenues() bingMarkingExample cVizAppVenues End Sub Public Sub googleMarkingVizVenues() googleMarkingExample cVizAppVenues End Sub
How to interact with Google MAPS API
Parameter Layout
Google Maps API parameters
Any additional column names present in your sheet that match any of the Column Names which have a component definition will be populated according to the rules given below – more details on that later.
geomap Response
Once deserialized, these are the component parts of a response to a request to geomap Google Headquarters. It should be straightforward to relate this to the parameters above. Note the special treatment though for ‘state’ and ‘fullkey’. Since various countries have different levels of administrative areas you can specify a mapping between country code and level, and fullkey means that the full key can be relied on to rely on the required result.
Main Code
- Create datasets of the Customer master Sheet, the decoding rules parameter block and the Fields parameter block.
- Check that the ID and Input address column exists.
- For each row in the Customer Master, send a Google API request with the unstructured input address
- 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
- 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)
- 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