Calculating Distance Between Two Post Codes Using Google Maps Distance Matrix API

Any handy code snippets you wish to share with the community
Post Reply
User avatar
robinwilson16
Site Admin
Posts: 185
Joined: Sun Oct 16, 2016 6:46 pm
Forename: Robin
Surname: Wilson
College Name: FEA
Position: MIS Consultant
LinkedIn Profile: https://www.linkedin.com/in/robinwilson16

Calculating Distance Between Two Post Codes Using Google Maps Distance Matrix API

Post by robinwilson16 » Sun Mar 12, 2017 11:25 pm

I previously posted a topic about how to calculate the distance between two post codes (as the crow flies) here:
viewtopic.php?f=19&t=31

I have since received some questions as to whether it is possible to achieve this whilst taking road systems, speed limits and traffic into account and I believe I have found a way by following the instructions posted here:
http://analystcave.com/excel-calculate- ... addresses/

The page above contains an Excel VBA script you can use and using this I was able to create a list of from post codes in column A and the to post codes in column B and was then able to calculate the distance and estimated time in columns C and D by copying my formulas down the sheet:
DistanceGoogleAPI.PNG
DistanceGoogleAPI.PNG (18.72 KiB) Viewed 554 times
Code in column C is:

Code: Select all

=GetDistance(A2, B2)/1000
Code in column D is:

Code: Select all

=GetDuration(A2, B2)/60
To get this to work you need to open the VBA editor by clicking on the Visual Basic button on the Developer tab (it can be turned on in Options) or just press Alt + F11 on the keyboard.

Create a new Module and paste these functions into the module (as they are public functions they will work from any tab of your spreadsheet).

Calculates the distance between two addresses/post codes (in metres):

Code: Select all

'Calculate Google Maps distance between two addresses
Public Function GetDistance(start As String, dest As String)
    Dim firstVal As String, secondVal As String, lastVal As String
    firstVal = "http://maps.googleapis.com/maps/api/distancematrix/json?origins="
    secondVal = "&destinations="
    lastVal = "&mode=car&language=pl&sensor=false"
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    URL = firstVal & Replace(start, " ", "+") & secondVal & Replace(dest, " ", "+") & lastVal
    objHTTP.Open "GET", URL, False
    objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    objHTTP.send ("")
    If InStr(objHTTP.responseText, """distance"" : {") = 0 Then GoTo ErrorHandl
    Set regex = CreateObject("VBScript.RegExp"): regex.Pattern = """value"".*?([0-9]+)": regex.Global = False
    Set matches = regex.Execute(objHTTP.responseText)
    tmpVal = Replace(matches(0).SubMatches(0), ".", Application.International(xlListSeparator))
    GetDistance = CDbl(tmpVal)
    Exit Function
ErrorHandl:
    GetDistance = -1
End Function
Calculate the time it will take to travel between the two addresses/post codes (in seconds):

Code: Select all

Public Function GetDuration(start As String, dest As String)
    Dim firstVal As String, secondVal As String, lastVal As String
    firstVal = "http://maps.googleapis.com/maps/api/distancematrix/json?origins="
    secondVal = "&destinations="
    lastVal = "&mode=car&language=en&sensor=false"
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    URL = firstVal & Replace(start, " ", "+") & secondVal & Replace(dest, " ", "+") & lastVal
    objHTTP.Open "GET", URL, False
    objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    objHTTP.send ("")
    If InStr(objHTTP.responseText, """duration"" : {") = 0 Then GoTo ErrorHandl
    Set regex = CreateObject("VBScript.RegExp"): regex.Pattern = "duration(?:.|\n)*?""value"".*?([0-9]+)": regex.Global = False
    Set matches = regex.Execute(objHTTP.responseText)
    tmpVal = Replace(matches(0).SubMatches(0), ".", Application.International(xlListSeparator))
    GetDuration = CDbl(tmpVal)
    Exit Function
ErrorHandl:
    GetDuration = -1
End Function
Lastly this code does not use the Google API and is similar to the SQL I provided in the other example I posted but this is written in VBA:

Code: Select all

Public Function GetDistanceCoord(ByVal lat1 As Double, ByVal lon1 As Double, ByVal lat2 As Double, ByVal lon2 As Double, ByVal unit As String) As Double
    Dim theta As Double: theta = lon1 - lon2
    Dim dist As Double: dist = Math.Sin(deg2rad(lat1)) * Math.Sin(deg2rad(lat2)) + Math.Cos(deg2rad(lat1)) * Math.Cos(deg2rad(lat2)) * Math.Cos(deg2rad(theta))
    dist = WorksheetFunction.Acos(dist)
    dist = rad2deg(dist)
    dist = dist * 60 * 1.1515
    If unit = "K" Then
        dist = dist * 1.609344
    ElseIf unit = "N" Then
        dist = dist * 0.8684
    End If
    GetDistanceCoord= dist
End Function
 
Function deg2rad(ByVal deg As Double) As Double
    deg2rad = (deg * WorksheetFunction.Pi / 180#)
End Function
 
Function rad2deg(ByVal rad As Double) As Double
    rad2deg = rad / WorksheetFunction.Pi * 180#
End Function

Post Reply

Who is online

Users browsing this forum: No registered users and 0 guests