calculate the driving distance between 2 zip codes

[jdraw]

@shadow9449
It worked for me @30-Aug-2018.
It is possible that Google may change some parameters/code, but I posted an example with lat/lon in recent post.

What is the issue you are experiencing?

 

[shadow9449]

Hi Jdraw

It has been working until recently. I took a screenshot of an example and straight from the demo app that was posted mid-September. Please see attached.

Thank you

 

[jdraw]

shadow9449,
I'm not sure what is happening. I downloaded the database from post #65 and ran it several times with the parameters you used. It gave me error 601. This is an error I concocted and raise if the API response has no distance info. I stepped through the code and it worked as expected. I then reran the code with the parameters you used and it worked, a few times in a row?

The basic issue is that object variable ohttp is Nothing after the call to and response from Google.
See attached for the working sample.

It could be a timing issue with the getting/receiving of data from the server? I haven't seen this issue when testing. I wrote the code few years back. I'll look as I get time.

If you, or any reader, identifies the issue and solution, please reply in this thread.

 

[jdraw]

I have done some additional testing and looked at the response from the call to the Distance API.
<DistanceMatrixResponse>
<status>OVER_QUERY_LIMIT</status>
<error_message>
Keyless access to Google Maps Platform is deprecated. Please use an API key with all your API calls to avoid service interruption. For further details please refer to http://g.co/dev/maps-no-account
</error_message>
</DistanceMatrixResponse>

I found this at one of the links Google provided:

Keyless calls to any of the following APIs will return an error: Maps Static API (including Static Street View), Directions API, Distance Matrix API, Geocoding API, Geolocation API, Places API, Roads API, and Time Zone API.


Bottom line is that you can not make a call to the Distance Matrix API (which I use)
without a Google Key.

Here is a link to further info from Google --User Guide for Google Maps

***********************************************************
See this for info specific to limits and costs with Distance Matrix calls
***********************************************************

Further Update:
I have added a revised database in 2007 format accdb in this new zip file.
I have found that Google says that since July 2018 you need an account and a key in order to call certain APIs. This routine calls the DistanceMatrix API and does not use a key. Therefore sometimes it returns the proper info, but other times it sends a message that you must have an account an use a key. I have added code to check the return from the API. If it sends the must use a key message, I capture it and display a message re Google and their related link. If it does not send that message, it processes the call as normal/previous. If you get the error message, you can try the call again--I have found it still works sometime. If you really need it to work, then get an account and key and adjust the function call to include your key. And NOTE, all calls with a key and account are subject to Billing--Read the Google materials re Accounts and Billing.

Good luck and remember Google has changed their policy regarding Google Maps and other API activities.

 

[shadow9449]

Jdraw:

Thanks for the research. It seems that Colin/Ridders/Isladogs has done some work using the Google Maps API using a key and the method using the key is inexpensive or doesn't cost at all.

Would you happen to know if using the method with the key is the same as the previous method, but you just add the key after the call?

Meaning: The old API was called (I think this is from your original demo app)

    sURL = "http://maps.googleapis.com/maps/api/distancematrix/xml?origins="
    sURL = sURL & URLEncode(sOrigin) & "&destinations=" & URLEncode(sDestination)
    sURL = sURL & "&units=" & sUnits & "&sensor=false"

So, do I just add to that "&key=" followed by the key?

Thanks again.

 

[isladogs]

Hi
After the triple name check, thought I'd better reply....

I'm amazed that it still works at times with no API key. I think it only allows the first call from an IP address within a certain time period

All Google API keys are free but you now have to supply credit card information when you obtain the key. Make sure you enable the distance matrix API for that key. You will get $200 of 'free' usage per month for that API and will only be charged if the number of API calls exceeding that limit

As well as adding the API key, if you read the API documentation here, you will see this sentence:

The Google Maps API previously required that you include the sensor parameter to indicate whether your application used a sensor to determine the user's location. This parameter is no longer required.

Also, the distance matrix url now uses https instead of http

So change the code to:

 Dim strKey As String
    strKey = "Your APIKey code"
    
    'new code
    sURL = "https://maps.googleapis.com/maps/api/distancematrix/xml?origins="
    sURL = sURL & URLEncode(sOrigin) & "&destinations=" & URLEncode(sDestination)
    sURL = sURL & "&units=" & sUnits
    sURL = sURL & "&key=" & strKey

Tested & it works for me on all 3 forms in Jack's utility.
You can also use JSON output instead of xml but further code changes would be required if so.

HTH

 

[jdraw]

shadow9449, Colin

Thanks Colin for responding and clarifying.

I didn't want to get a key and account and insert same in the sample routines because I have no idea who or how often people would be using them.

However, any reader/user can take a copy of the database in post #65 (mdb format) or the latest database in post #84 (accdb with new msg) and, after creating a Google account, obtaining a key (and associating that with a credit card), adjust the calling code as Colin/Isladogs has shown in post #86, and use it for their own purposes.

There is a link to Google's limits and costs related to the Distance Matrix in post #84.

 

[isladogs]

shadow9449, Colin

Thanks Colin for responding and clarifying.

I didn't want to get a key and account and insert same in the sample routines because I have no idea who or how often people would be using them.

Very wise.
Have a look at my GetLocation app to see how I distribute this type of app without an API key

 

[shadow9449]

Good news!

I followed the info from Jdraw and Colin and it's working consistently. I tested about 5 or 6 different addresses and it worked each time.

Many thanks to both of you!

 

[isladogs]

Excellent. Glad to have helped.
I do recommend reading the Google API documentation. It's very clear and detailed

 

[jdraw]

Thanks for the update. Glad it is working as expected.

 

[klumzie]

Hi everyone, I have got an API key and wanted to know how to place it into the code to get this working again. any ideas or does somebody have an alternative method of getting the distance between 2 postcodes in ms access.

Thanks in advance

 

[isladogs]

You need to use your API key.
You could have a look at the code in my Get Location app -see post 88 or follow the Google maps documentation.
Another user has posted similar code recently in this post https://www.access-programmers.co.uk/forums/showpost.php?p=1603594&postcount=57
That also uses an api key.

HTH

 

[klumzie]

Hi, Thanks for the speedy reply, I am trying to adjust the code i have and am not able to get it working. i am quite new to vba and am struggling to get it working.

i have added


sURL = "https://maps.googleapis.com/maps/api/distancematrix/xml?key="My API KEY"&origins=" & _


and now i get an error on

oHttp.Open "GET", sURL, False

Please see full code below


Public Function GetDistanceBetweenTwoZips(sOrigin As String _
, sDestination As String _
, Optional sUnits As String = "M") As String
Dim oHttp As Object
Dim sURL As String, sHTML As String
Dim sDistance As String
Dim sDuration As String
Dim lTopicstart As Long, lTopicend As Long
On Error GoTo GetDistanceBetweenTwoZips_Error

'sOrigin = "77074"
'sDestination = "24112"

If sUnits = "K" Then
sUnits = "metric" 'kilometres is an option
Else
sUnits = "imperial" 'miles is the default
End If

'GMap webservice with XML output
sURL = "https://maps.googleapis.com/maps/api/distancematrix/xml?key="My API KEY"&origins=" & _
sURL = sURL & URLEncode(sOrigin) & "&destinations=" & URLEncode(sDestination)
sURL = sURL & "&units=" & sUnits & "&sensor=false"

'sURL = sURL & "&units=imperial&sensor=false" 'original code

'Debug.Print sURL
' Create an XMLHTTP object and add some error trapping
On Error Resume Next
Set oHttp = CreateObject("MSXML2.XMLHTTP")
'Set oHttp = CreateObject("MSXML.XMLHTTPRequest")
On Error GoTo 0
If oHttp Is Nothing Then
MsgBox "For some reason I wasn?t able to make a MSXML2.XMLHTTP object"""
GoTo GetDistanceBetweenTwoZips_Error
End If

'Open the URL in browser object
oHttp.Open "GET", sURL, False
'Debug.Print sURL
oHttp.send
sHTML = oHttp.responseText
Set oHttp = Nothing

' Formatted Distance
'Debug.Print sHTML
' Parse the returned xml to get the Distance
' actually need the string between <text> and </text>

lTopicstart = InStr(sHTML, "<distance>")
lTopicend = InStr(sHTML, "</distance>")
If lTopicstart = 0 And lTopicend = 0 Then
On Error Resume Next
Err.Raise 601, , "JED---No Distance returned by GoogleMap - perhaps invalid zipcode" 'added because some coords don't return Distance
GoTo GetDistanceBetweenTwoZips_Error
Else
sDistance = Trim(Mid(sHTML, lTopicstart + 10, lTopicend - lltopicstart - 10))
'Debug.Print Len(sDistance) & "[" & sDistance & "]"
lTopicstart = InStr(sDistance, "<text>")
lTopicend = InStr(sDistance, "</text>")
sDistance = Mid(sDistance, lTopicstart + 6, lTopicend - lTopicstart - 6)
Debug.Print "1--------- sDistance " & sDistance
'GetDistanceBetweenTwoZips = sDistance
End If
'
'Parse the xml to get the Duration (Time)
lTopicstart = InStr(sHTML, "<duration>")
lTopicend = InStr(sHTML, "</duration>")
If lTopicstart = 0 And lTopicend = 0 Then
On Error Resume Next
Err.Raise 602, , "JED---No Duration returned by GoogleMap" 'added because some coords don't return Duration
GoTo GetDistanceBetweenTwoZips_Error
Else
sDuration = Trim(Mid(sHTML, lTopicstart + 10, lTopicend - lltopicstart - 10))
'Debug.Print Len(sDuration) & "[" & sDuration & "]"
lTopicstart = InStr(sDuration, "<text>")
lTopicend = InStr(sDuration, "</text>")
sDuration = Mid(sDuration, lTopicstart + 6, lTopicend - lTopicstart - 6)
Debug.Print "2------ sDuration " & sDuration

End If

'Create the final output of the function
GetDistanceBetweenTwoZips = sDistance & "|" & sDuration

'Debug.Print GetDistanceBetweenTwoZips
'===========
On Error GoTo 0
Exit Function

GetDistanceBetweenTwoZips_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure GetDistanceBetweenTwoZips of Module GoogleMapsEtc"

End Function

If there is anyway to fix this code and get it working again i would be much appreicative as my whole date base has been developed rounf this code..

Thank you so much for your help.......

 

[isladogs]

Hopefully you have actually used your real API key rather than string in your code and not wrapped it in ""

Enable the line Debug.Print sURL & run your code
Copy the long string from the Immediate window into a browser.
Does it work?
If not, then you need to fix the sURL code

If it does work then the fault is in the rest of your code ...but I don't have time to look now

Not sure why you have included the lines err.Raise 601/602. Disable both?
As you are using someone else's code, it may be best to check with the author of that code

 

[martinbaker]

What would the Macro code look like to calculate distance between my classmates' current zip code in an Address table and the zip code of our high school and record that number in a data field in the same table? thank you very much.

 

[jdraw]

Martin, You have joined a post that is 2+ yrs old.
There is a database attached as a zip in post #84 that has code to do what you are asking- generally. But it will require changes related to a Google account and key.

As you can see in the posts since #84
Google has changed their policy regarding Google Maps and other API activities.
See post #86 by Colin/Isladogs for specifics.

 

[martinbaker]

Is Google the only search engine for geo location services? Is there any that do not charge for providing this information, such as Mapquest? My classmates list is about 150 at this point and will only decrease over time. After a one time pass through the table, any future changes to zip codes will be few and far between. Also, I probably will never need time to travel data, just distance.

 

[isladogs]

You can use others such as Bing but most charge for large scale use.
As I indicated in post #86, Google's charging policy means you may be able to do what you need for free.
MapQuest is open source and I believe free but I found their documentation much less clear and didn't investigate it thoroughly

 

[jdraw]

I found this Melissa tool free, online with a google search.