Spatial analysis with Bing Maps & Excel

After months of BI work in the office I finally got the opportunity to take a break and get out of the office to see the field sales end of the business.

Additionally, in the last couple of months I’ve been learning about the world of spatial analysis after reading the great blog from Alastair Aitchison and stumbling across the mapping features of SSRS.

So for the 1st topic of 2012 I give you an off-topic subject : Spatial Analysis with Bing Maps & Excel.

To cut a long story short I had a requirement to calculate the distance between point A and point n…

Point A is the current location of a sales person.    Points n… are sales leads.

I wanted to give our road warriors the ability to view the latest sales leads that are nearest to them.

At first I used Pythagoras’ theorem using the latitude and longitude of each point. This worked okay however, I felt there was more I could do. I really wanted to provide drive time and real road distances. Of course, to build my own mapping app was out of the question. Instead, I went to Bing Maps and found a great API that allows me to use the Bing Maps web services.

You can read about them here.

In order to use the Bing Maps web service you need to set up an account and obtain a Bing Maps Key.

In order to provide a proof of concept I created an Excel file to consume the web service and process its results.

The web service is called using the URL style below:

http://dev.virtualearth.net/REST/V1/Routes/Driving?o=xml&wp.0=london&wp.1=leeds&avoid=minimizeTolls&key=BingMapsKey

We use o to tell the service the format of data we want returned. We have XML or JSON.   I opted for XML as I know Excel can process XML very easily.

The wp parameters are used to provide way points.  The web service then geocodes the address and calculates the distance & drive time along with directions (which we’ll ignore in this post).

There are several parameters we can use to customise the results.  You can read about them here

Okay, enough of the theory.  You can download the model here or if you would rather build the solution for yourself follow the steps below.

1. Create some VBA classes and modules according to the screen below:

VBA classes

2. In order to process XML in VBA we need to add a reference to some existing code. You should already have the required code library installed. You just need to select it. Look for Microsoft XML v6.0.

XML Reference v6.0

3.  Paste the code below into the module AppGlobals.

Public Const BASEURL As String = "http://dev.virtualearth.net/REST/V1/Routes/Driving?o=xml&wp.0="
Public Const BINGKEY = "Place your Bing Maps Key here"

Be sure to input your Bing Maps Key where indicated.

4. Paste the code below into the class BingLocation

Dim ServiceResults As New MSXML2.DOMDocument

Public Function SetURL(Origin As String, Destination As String, Country As String) As String

Dim pOrigin
Dim pDestination
Dim pCountry

pOrigin = Origin & "&wp.1="
pDestination = Destination & "&avoid=minimizeTolls&key="

'assemble query string

Query = BASEURL + pOrigin + pDestination + BINGKEY

SetURL = Query

End Function

Public Function callService(qry As String) As TwoPointsData

Dim DistanceService As New MSXML2.XMLHTTP

'Call the service
DistanceService.Open "GET", qry, False
DistanceService.send

'Parse the result
ServiceResults.LoadXML (DistanceService.responseText)

Dim result As New TwoPointsData
result.SetData ServiceResults
Set callService = result

End Function

Private Sub OpenCnn()

End Sub

Private Sub CloseCnn()

End Sub

Private Sub Class_Initialize()

End Sub

Private Sub Class_Terminate()

End Sub

5. The TwoPointsData class is the class used to pull out the data we want from the XML returned by the web service. Paste the code below into the empty class.

Public DistanceMetres As Long
Public DriveTimeSeconds As String

Public Sub SetData(x As MSXML2.DOMDocument)

DistanceMetres = x.SelectSingleNode("//TravelDistance").Text
DriveTimeSeconds = x.SelectSingleNode("//TravelDuration").Text

End Sub

6. Save the code and return to the Excel worksheet.

Set up a table and command button as per the screen below

Excel Worksheet

7. Add some code behind the command button as below :

Private Sub BingMaps_Click()

Dim a As Integer
Dim url As String
Dim distance As Integer
Dim seconds As Integer
Dim g As BingLocation
Dim res As TwoPointsData

'clear data
Range("D6:E28").ClearContents

For a = 6 To 28

Set g = New BingLocation

url = g.SetURL(Cells(a, 2), Cells(a, 3), "UK")
Set res = g.callService(url)

Cells(a, 4) = res.DistanceMetres
Cells(a, 5) = res.DriveTimeSeconds

Set g = Nothing

Next a

End Sub

This is all there is too it.

About Lee Hawthorn

Data Professional
This entry was posted in Spatial Analysis and tagged , , . Bookmark the permalink.

12 Responses to Spatial analysis with Bing Maps & Excel

  1. Steven says:

    Hi Lee,

    Thanks for your demonstration, I am experiencing an issue where the Excel will show me an error “Object variable or With block variable not set”

    I have already entered in my BIng Map Key

    The strange thing is, the Excel spreadsheet worked fine before, but somehow after a while the aforementioned problem shows up.

    Is it possible if you can test the spreadsheet again to see if it still works

    • Lee Hawthorn says:

      Hi Steven,

      I see this problem when there is a reference missing in the vba code window. If you view the code window and check your refs you should see a MISSING label next to one or more libraries. Uncheck these objects or source the missing files. I’m away from a machine for the next few days but will look into this on my return.

      Good luck.

      Lee

  2. Steven says:

    Hi Lee,

    I have figured out it is the issue with my Bing Map key, so it is fine now.

    I noticed that the Map Distance returned is always rounded off to the nearest kilometer, is it possible to get the the accuracy to 1 decimal point?

    Thanks
    Steven

    • Kevin says:

      Hi Steven,

      Just change the “Public DistanceMetres As Long” to “Public DistanceMetres As Double”, then round it to decimal point

  3. Dylan says:

    Hi Lee,

    I am fairly new to VBA and coding. I tried to download the file, enter my Bing Map key (obtained through the Bing Maps website), saved and returned to worksheet. I don’t see anything there. The distance results are numbered inputs, not formula results. Not sure how the worksheet works. Could you provide a little guidance to a novice?

    Also it looks like you are using UK postal codes here. Is it possible to run this worksheet with US Zip codes?

    Thanks,
    Dylan

  4. Lee Hawthorn says:

    Hi Dylan,

    It should work with US zips. You’ll need to amend the country flag in the code. Search for :

    url = g.SetURL(Cells(a, 2), Cells(a, 3), “UK”)

    Change to

    url = g.SetURL(Cells(a, 2), Cells(a, 3), “US”)

    I assume you have macro’s enabled too?

    • Walter says:

      Hi, how is the country flag tu use it in Argentina?
      Thanks a lot.
      Can i pass to directions ?
      I Need calculate km. for two or more dirección like
      Country , Street, Number.
      Thanks

      • Lee Hawthorn says:

        The example code uses postal/zip codes. It doesn’t support Country/Street/Number. Feel free to take the code and develop it. It shouldn’t be too hard.

        Best

  5. Varghese says:

    Hi,

    Thank you for the code and I downloaded the excel workbook

    I am getting a Run time error “Object variable or With block variable not set” while running the command button and it is highlighting the “Set res = g.callService(url) ” while debugging the code . Could you please help me with this?

    • Lee Hawthorn says:

      I see this problem when there is a reference missing in the vba code window. If you view the code window and check your refs you should see a MISSING label next to one or more libraries. Uncheck these objects or source the missing files.

  6. Matthew Walters says:

    Lee is there anyway to return a larger range of data ..more cells ? I always get a block variable not set error when increasing the range.

    • Lee Hawthorn says:

      I recall there is a limit to how many data points can be returned from the service. As long as you stay within this limit you just need to change the cell references in the code. Look carefully and you should be able to do it.

Leave a Reply