Road distance calculation using latitudes and longitudes in excel/google sheets

I'm looking to get road distances between 2 addresses. I have the postcode and the latitude and longitudes of each address.

I have it laid out in a grid (see image attached), as there are 500 addresses that I need the distances between and can't figure out a better way to do it.

The issue I have is I can't use the Haversine formula, as it's for mileage claims so needs to be road distances.

I am a noob regarding coding, and have been slowly teaching myself, but I need a way that it will look up each set and fill the relevant/corresponding box.

I'm aware of the google API stuff, but not really sure how it works, or how I'd go about implementing something like that into my scripts.

2 Answers
2

The Google Distance Matrix API provides travel, distance and time for a matrix of origins and destinations. The API returns information based on the recommended route between start and end points, as calculated by the Google Maps API, and consists of rows containing duration and distance values for each pair of points.

Google Maps' set of APIs used to allow several free calls per day but they recently changed their billing structure, so you'll need to set up a billing account. Here are the new prices for the Distance Matrix API (unless you've managed to get a free trial). Basically $5 to $10 USD for every 1,000 calls to the API.

There are several specific examples here of how to call the API via a web browser or programmatically.

This will generates links to each trip's driving directions page in Google Maps (which includes distance) and will speed up the process (if you have at least basic knowledge of Excel).

Enter your sets of coordinates into an Excel worksheet, two coordinates sets per row in cells A to D, starting in row 2, in the order Origin Latitude, Origin Longitude, Destination Latitude, Destination Longitude. See image below.

A

D

Origin Latitude, Origin Longitude, Destination Latitude, Destination Longitude

Paste this formula into cell E2:
=HYPERLINK("https://www.google.ca/maps/dir/'" & A2 & "," &B2&"'/'" & C2 & "," &D2&"'/@"&AVERAGE(A2,C2)&","&AVERAGE(B2,D2) &",12z/")

E2

=HYPERLINK("https://www.google.ca/maps/dir/'" & A2 & "," &B2&"'/'" & C2 & "," &D2&"'/@"&AVERAGE(A2,C2)&","&AVERAGE(B2,D2) &",12z/")

'Fill down', or copy/paste the formula, so it's in all the rows with coordinates.

Click each link to load Google Maps for that trip, and manually make note of the driving distance for each coordinate set.

(Note that each trip may have multiple options depending on which route you travelled.)

_{Click images to enlarge.}

_{Result in browser:}

With a decent Internet connection, I figure each one to take 10-15 seconds, so you're looking at around an hour total.

(I suggest breaking it up instead of trying to do them all at one. Better yet, pay a kid, or get multiple children and make a game out of it...) 😊

Function using Google Maps API

I remembered a function I had stashed away that appears to still work for free... but based on the new pricing guidelines, I wouldn't be surprised if it only worked for a few calls a day (for "testing purposes").

This one take the origin & destination locations in two cells (instead of four), but is more flexible with how you enter the locations. Example:

In Excel, paste this code into a new (standard) module. _{(Here's how.)}

Public Function GetDistance(start As String, dest As String)
'Returns Google Maps driving distance between two points in kilometres
Dim url As String, html As String, regEx As Object, matches As Object
url = "http://maps.googleapis.com/maps/api/distancematrix/json?" & _
"origins=" & Replace(start, " ", "+") & "&destinations=" & _
Replace(dest, " ", "+")
With CreateObject("MSXML2.XMLHTTP")
.Open "GET", url, False
.Send: html = StrConv(.responseBody, vbUnicode)
Set regEx = CreateObject("VBScript.RegExp")
regEx.Pattern = """value"".*?([0-9]+)": regEx.Global = False
Set matches = regEx.Execute(html)
If matches.Count > 0 Then
GetDistance = CDbl(Replace(matches(0).SubMatches(0), ".", _
Application.International(xlListSeparator))) / 1000
Else: GetDistance = -1: End If 'there was a problem.
End With
End Function


With the example in the above image, you'd enter in cell C2:

C2

=GetDistance(A2,B2)


...to get the driving distance in kilometres.

Haversine Method

For the sake of completeness, I'll include the Haversine Method which calculates a "straight line" distance mathematically (with no API calls).

This is not driving distance. In my test of a relatively straight distance between towns, this method was different by about 15%, however, this is suitable (and faster) for other "non-driving related" purposes.

Public Function Distance(lat1 As Double, lon1 As Double, _
lat2 As Double, lon2 As Double) As Double
'Excel: Returns kilometres distance in a straight line Haversine)
On Error GoTo dErr
Dim dist As Double, theta As Double: theta = lon1 - lon2
dist = Math.Sin(deg2rad(lat1)) * Math.Sin(deg2rad(lat2)) + _
Math.Cos(deg2rad(lat1)) * Math.Cos(deg2rad(lat2)) * _
Math.Cos(deg2rad(theta))
dist = rad2deg(WorksheetFunction.Acos(dist))
Distance = dist * 60 * 1.1515 * 1.609344
Exit Function
dErr:
Distance2 = -1
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


Access is slightly different due to the lack of Excel's math functions, so I might as well include it:

Const pi = 3.14159265358979
Public Function Distance(lat1 As Double, lon1 As Double, lat2 As Double, lon2 As Double) As Double
'MS Access: Returns kilometres distance in a straight line Haversine)
Dim dist As Double, theta As Double: theta = lon1 - lon2
dist = Sin(deg2rad(lat1)) * Sin(deg2rad(lat2)) + Cos(deg2rad(lat1)) * Cos(deg2rad(lat2)) * Cos(deg2rad(theta))
Distance = rad2deg(ACos(dist)) * 60 * 1.1515 * 1.609344
End Function
Function ACos(N As Double) As Double: ACos = pi / 2 - ASin(N): End Function
Function deg2rad(ByVal deg As Double) As Double: deg2rad = (deg * pi / 180#): End Function
Function rad2deg(ByVal rad As Double) As Double: rad2deg = rad / pi * 180#: End Function
Public Function ASin(N As Double) As Double: ASin = 2 * Atn(N / (1 + Sqr(1 - (N * N)))): End Function


^{Spherical triangle solved by the Law of Haversines}

I would do it with arcgis:
You plot the two points
You should have some road shapefile that contains the road you want to calculate the distance the you clip seeing the points, then you measure that clipped polilyne

Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.