Find distances between locations in bulk

Use google Maps API to find distances between locations

Problem Statement

I've come across a situation where a business client of mine needed to find the distance between a source location to multiple destinations.

There were hundreds of destinations, and so this is no easy task to do manually.

As I did this some time back, I've forgotten the original sources from which I learnt the method. I apologize to them for not citing the references.

Solution

I snooped around online for a way to find the distances, and after getting a fair idea of how google maps works, I was able to put together this macro.

First, create a properly formatted Excel sheet like so:

The first three columns are details of the origin, while the next three columns are details of the destinations.

You can add or remove the number of columns depending on the precision you require.

The seventh column is for displaying the calculated distances.

Open up the VBA editor through 'Alt-F11' and add a new module.

Paste the following code:

Sub getDistances()

Dim xhrRequest As XMLHTTP60

Dim domDoc As DOMDocument60

Dim ixnlDistanceNodes As IXMLDOMNodeList

Dim ixnNode As IXMLDOMNode

Dim lOutputRow As Long

Dim dist As Double

dist = 0

Dim LastRow As Integer

Dim i As Integer

LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

For i = 2 To LastRow Step 1

    ' Read the data from the website

    Set xhrRequest = New XMLHTTP60

    xhrRequest.Open "GET", "http://maps.googleapis.com/maps/api/directions/xml?origin=" & Range("A" & i).Value & "+" & Range("B" & i).Value & "+" & Range("C" & i).Value & "&destination=" & Range("D" & i).Value & "+" & Range("E" & i).Value & "+" & Range("F" & i).Value & "&sensor=false", False

    xhrRequest.send

    

    ' Copy the results into a format we can manipulate with XPath

    Set domDoc = New DOMDocument60

    domDoc.LoadXML xhrRequest.responseText

    

    ' The important bit: select every node called "value" which is the child of a node called "distance" which is

    ' in turn the child of a node called "step"

    Set ixnlDistanceNodes = domDoc.SelectNodes("//step/distance/value")

    

    ' Basic stuff to output the distances

    lOutputRow = 1

    

        For Each ixnNode In ixnlDistanceNodes

            dist = dist + Val(ixnNode.Text)

        Next ixnNode

    

    

    Cells(i, 7).Value = dist / 1000

    

    Set ixnNode = Nothing

    Set ixnlDistanceNodes = Nothing

    Set domDoc = Nothing

    Set xhrRequest = Nothing

    dist = 0

   

   'Give enough time for the data to come back

    Application.Wait Now + TimeSerial(0, 0, 1)

    

Next i

End Sub

Aaaaaand... we're done!

Run the "getDistances" macro, sit back and relax while the distances are being populated.

The End.