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:
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:
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.
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
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.