Complete Excel Address Data with Bing Maps API


GeoCode Excel Data with Bing Maps get it now

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

Since we have done this in Google Maps, I thought it would be worthwhile to see how it's done in Bing Maps.  This all uses cDataSet to abstract data and make all this a trivial exercise.

Quick start

  1. Download the  Google Maps workbook
  2. Amend Columns A,B,C in the VenueMaster workbook to your data
  3. 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.
  4. 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

Bing maps - vs - Google Maps

These restful apis work in pretty much the same way, Here are the main differences.
  •  Microsoft require you to get a Key to use Bing. In this downloadable example, I am using my key. If you use this code for anything, please get your own and follow the Microsoft rules on usage. Google do not require you to get a key, but limit your usage to a small number of queries daily.
  • Bing is less able to deal with amibuity. In the set of 10 random addresses, Bing was unable to identify  half of them. Google got them all. 
  • There is a whole bunch of unnecessary stuff about copyright, and other junk that is returned from each query to Bing. What a waste of space and traffic. 
  • Bing provides less detail than Google, but the results are a lot easier to deal with. 
  • Bing is fussy about input - for example I needed to clean the input addresses to get rid of non printable characters and so on. Google doesn't care about that. 
  • Bing Maps has a stupid name. 

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 Bing is a different structure. However 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 Bing.  


Note that the response from Bing is simpler, so we use simply the 'fullkey' to identify the field within that response. 

The Bing Key

As mentioned you need a developer key to use Bing. In the downloadable example I am using my key. You need to get your own by registering at the Bing Portal. 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://dev.virtualearth.net/REST/v1/Locations/1600 %20Amphitheatre%20Pkwy%2C%20Mountain %20View %2C%20CA%2094043%2C%20USA?output=json&key=
AmVoAsOUH9QHTL4-Zc7qF7MjU8tm7zR9rDdXsA5QRsgudEwRJmz_a_NkGMmTUn3I

and this the response

{"authenticationResultCode":"ValidCredentials", "brandLogoUri":"http:\/\/dev.virtualearth.net\/Branding\/logo_powered_by.png", "copyright":"Copyright © 2011 Microsoft and its suppliers. All rights reserved. This API cannot be accessed and the content and any results may not be used, reproduced or transmitted in any manner without express written permission from Microsoft Corporation.", "resourceSets":[{"estimatedTotal":1, "resources":[{"__type": "Location:http:\/\/schemas.microsoft.com\/search\/local\/ws\/rest\/v1",  "bbox":[37.418107282429325, -122.09062169320978, 37.425832717570678 ,-122.07765230679021], "name": "1600 Amphitheatre Pkwy,  Mountain View, CA 94043-1351","point":  {"type":"Point","coordinates": [37.42197,-122.084137]},"address":{"addressLine":"1600 Amphitheatre Pkwy", "adminDistrict": "CA", "adminDistrict2": "Santa Clara Co.","countryRegion": "United States","formattedAddress": "1600 Amphitheatre Pkwy,  Mountain View, CA 94043-1351", "locality":"Mountain View","postalCode":"94043-1351"}, "confidence":"High","entityType": "Address"}]}] , "statusCode":200, "statusDescription":"OK", "traceId":"a7f50cdaac934537b0e507f7c2f7bd62|LTSM001158 |02.00.126.3000|LTSMSNVM001475,  LTSMSNVM001457,  LTSMSNVM001461"}

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


The code

This is very similar to the Google Maps version, and calls many of the same modules which I won't bother repeating here.  We are doing the same thing - namely geoMapping and filling in named fields such as country etc as specified, and according to the rules in a parameter sheet. 


Option Explicit
' NOTE: before using this please read the Bing terms and conditions
' http://www.microsoft.com/maps/product/licensing.aspx
' and read about the restful api here
' http://msdn.microsoft.com/en-us/library/ff701713.aspx
' this example is purely for educational purposes and is a step in creating a bing map
' YOU NEED TO GET YOUR OWN Bing KEY TO BUILD AN APPLICATION WITH THIS CODE
Const cBingApiUrl = "http://dev.virtualearth.net/REST/v1/Locations/"

Dim iDebug As Long
' this is all about parameter names

Public Sub bingMappingExample()
    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 = cBingApiUrl & URLEncode(sInput) & _
             "?output=json&key=" & _
            dSets.DataSet(cBingParameters).Cell("Key", "Value").toString
            ' get mapping result
            sWire = cb.httpGET(sReq)
            Set jo = New cJobject
            With jo.deSerialize(sWire)
                If .isValid And Not .ChildExists("authenticationResultCode") Is Nothing Then
                ' now we have a cjobject of every field in the response
                    If .Child("authenticationResultCode").toString = "ValidCredentials" Then
                        ' all is good go and find necessary fields
                        If .Child("resourcesets.1.estimatedtotal").Value < 1 Then
                            MsgBox ("No results for " & ":url" & sReq)
                        Else
                            bingSuitableJob .Child("resourceSets.1.resources"), _
                                    dr, Range("debug!a2")
                        End If
                    Else
                        MsgBox ("Unable to geomap - status " & .Child("authenticationResultCode").toString & ":url" & sReq)
                    End If
                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

Private Sub bingSuitableJob(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

    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 = bingMappingFind(job, _
                    LCase(.Cell(sName, "bing component").toString), _
                    LCase(.Cell(sName, "bing special").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

Private Function bingMappingFind(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 bingMappingFind = job
            Exit Function
        End If
    Else
        MsgBox ("Only full key implemented for bing")
    End If

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

End Function




Summary

This Bing implementation was simply to contrast with the Google version. Although a little harder, I will stick to Google for Mapping. For questions and comments please use our forum. You can Download the example - googleMapping.xlsm

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