VBA Function to calculate distance from 2 addresses using MapQuest API or other interfaces

Hi all,

I'm looking for help with VBA functions (MS Access or Excel) to calculate distance between 2 addresses but NOT using Google Maps API. They have certain restrictions and requirements that would make the app difficult.

I've explored MapQuest API, and while I get the calling part, I'm just not that familiar with XML to understand how to retrieve the distance.

Any help would be greatly, GREATLY appreciated.
LVL 5
eantarAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Can you show what you've tried so far with the Mapquest API? Getting to those seems to require joining the community, download/install etc, and you might get better and quicker answers if you can show us the functions you're trying to use.
0
hnasrCommented:
Try to recreate the issue in a sample database, and upload.
0
aikimarkCommented:
You could roll-your-own calculation.

Example:
1. make a call to Texas A&M geocoding web service
https://geoservices.tamu.edu/Services/Geocode/WebService/GeocoderWebServiceHttpNonParsed_V04_01.aspx?streetAddress=27%20meadhall%20court&city=durham&state=nc&zip=27713&apikey=demo&format=XML&census=false¬Store=false&version=4.01

2. parse the XML results to get the Latitude and Longitude elements.
<WebServiceGeocodeResult version="4.01">
<QueryMetadata>
<TransactionId>d539af81-347e-45e0-8375-3f7080a54026</TransactionId>
<Version>4.01</Version>
<QueryStatusCodeValue>200</QueryStatusCodeValue>
<FeatureMatchingResultType>Success</FeatureMatchingResultType>
<FeatureMatchingResultCount>1</FeatureMatchingResultCount>
<TimeTaken>0.0156006</TimeTaken>
<ExceptionOccured>False</ExceptionOccured>
<Exception/>
<ErrorMessage/>
</QueryMetadata>
<InputAddress>
<StreetAddress>27 MEADHALL CT durham NC 27713</StreetAddress>
<City>durham</City>
<State>NC</State>
<Zip>27713</Zip>
</InputAddress>
<OutputGeocodes>
<OutputGeocode>
<Latitude>35.9189053154933</Latitude>
<Longitude>-78.8949771056968</Longitude>
<NAACCRGISCoordinateQualityCode>03</NAACCRGISCoordinateQualityCode>
<NAACCRGISCoordinateQualityType>StreetSegmentInterpolation</NAACCRGISCoordinateQualityType>
<MatchScore>100</MatchScore>
<MatchType>Exact</MatchType>
<FeatureMatchingResultType>Success</FeatureMatchingResultType>
<FeatureMatchingResultCount>1</FeatureMatchingResultCount>
<FeatureMatchingGeographyType>StreetSegment</FeatureMatchingGeographyType>
<RegionSize>4556.12917160499</RegionSize>
<RegionSizeUnits>Meters</RegionSizeUnits>
<MatchedLocationType>LOCATION_TYPE_STREET_ADDRESS</MatchedLocationType>
<ExceptionOccured>False</ExceptionOccured>
<Exception/>
<ErrorMessage/>
</OutputGeocode>
</OutputGeocodes>
</WebServiceGeocodeResult>

Open in new window


3. Calculate the distance between the two lat/lon values with the standard formula (it involves trig functions and the circumference of the Earth)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

eantarAuthor Commented:
Hi all,

Thanks for responding.

Here is the code I'm using:

Sub GetDistance_MapQuest2(ByVal startFrom As String, ByVal endAt As String, ByRef refMiles As Double, ByRef refHours As Double)


Dim url         As String
Dim resp        As String

Dim req         As MSXML2.XMLHTTP
Dim xdoc        As MSXML2.DOMDocument

url = "http://www.open.mapquestapi.com/directions/v2/route?key=lwCv2rEDHoCHE2VnLG71hKJg0b6a5EA5&drivingStyle=2&callback=renderAdvancedNarrative&ambiguities=ignore&avoidTimedConditions=false&doReverseGeocode=true&outFormat=xml&routeType=fastest&timeType=1&enhancedNarrative=false&shapeFormat=raw&generalize=0&locale=en_US&unit=m"
url = url & "&from=" & Replace(Trim(startFrom), " ", "%20")
url = url & "&to=" & Replace(Trim(endAt), " ", "%20")

Set req = New MSXML2.XMLHTTP
req.Open "GET", url, False
req.send

resp = req.responseText

Set xdoc = req.responseXML

refMiles = CSng(xdoc.selectSingleNode("response / route / distance").Text)
refHours = CLng(xdoc.selectSingleNode("response / route / time").Text) / 60 / 60

End Sub

Sub FindMilesTest()
Dim strStartFrom As String, strEndAt As String, refMiles As Double, refHours As Double

strStartFrom = "1788 East 10th Street, Brooklyn, NY"
strEndAt = "1803 Riverside Drive, New York, NY"

GetDistance_MapQuest2 strStartFrom, strEndAt, refMiles, refHours

Debug.Print refMiles


End Sub

Open in new window


I know the key is legitimate as I received it yesterday when I signed up with the MapQuest Developer Network. And when I tested it yesterday I got past the send, but it just seemed to have problems reading the xml.

BUT TODAY I'm getting a "system cannot locate the resource specified". (BTW, I was told that once obtained the key would work in a distributed application, so you should be able to try this.)

Any help OR suggestions would be greatly, GREATLY appreciated.

Best,
E
0
aikimarkCommented:
The URL was wrong.  Removed the "Open." part.  Please try the following:
Option Explicit

Sub GetDistance_MapQuest2(ByVal startFrom As String, ByVal endAt As String, ByRef refMiles As Double, ByRef refHours As Double)
    
    
    Dim url         As String
    Dim resp        As String
    
    Dim req         As Object       'MSXML2.XMLHTTP
    Dim xdoc        As Object       'MSXML2.DOMDocument
    Set req = CreateObject("MSXML2.XMLHTTP")
    Set xdoc = CreateObject("MSXML2.DOMDocument")
    url = "http://www.mapquestapi.com/directions/v2/route?key=lwCv2rEDHoCHE2VnLG71hKJg0b6a5EA5&drivingStyle=2&callback=renderAdvancedNarrative&ambiguities=ignore&avoidTimedConditions=false&doReverseGeocode=true&outFormat=xml&routeType=fastest&timeType=1&enhancedNarrative=false&shapeFormat=raw&generalize=0&locale=en_US&unit=m"
    url = url & "&from=" & Replace(Trim(startFrom), " ", "%20")
    url = url & "&to=" & Replace(Trim(endAt), " ", "%20")
    
    'Set req = New MSXML2.XMLHTTP
    req.Open "GET", url, False
    req.send
    
    resp = req.responseText
    
    'Set xdoc = req.responseXML
    xdoc.loadxml (resp)
    refMiles = CSng(xdoc.selectSingleNode("response / route / distance").Text)
    refHours = CLng(xdoc.selectSingleNode("response / route / time").Text) / 60 / 60
    Debug.Print "Miles: " & refMiles, "Hours: " & refHours
End Sub

Sub FindMilesTest()
    Dim strStartFrom As String, strEndAt As String, refMiles As Double, refHours As Double
    
    strStartFrom = "1788 East 10th Street, Brooklyn, NY"
    strEndAt = "1803 Riverside Drive, New York, NY"
    
    GetDistance_MapQuest2 strStartFrom, strEndAt, refMiles, refHours
    
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
aikimarkCommented:
I'm a proponent of late binding.  So, I used CreateObject() rather than adding references to the project
0
hnasrCommented:
My previous comment was not intended for this thread.
0
eantarAuthor Commented:
Wow! Works great. I was beginning to disrepair. Thank you. I've been working Access for quite a while, but never had the need to work with API's or XML. May I ask you to explain the XML part. I know it's complicated, but when the docs on an AP say that the Route response field is:

distance: Returns the calculated distance of the route :  <distance>25.150</distance>

for their xml example, is there a way to translate that into how I can get that using vba?

Hope the question makes sense. In any case, thanks so much.
0
eantarAuthor Commented:
Also, thanks for the geocoding suggestion. I need that as well. But I need actual travel time for my client so a radius calculation wouldn't work. Anyone other suggestions are welcome.
0
aikimarkCommented:
@eantar

Since this is a closed question, please open a new question for your additional question parts.  Please include a link to this question in your new question, then return to this thread and post a link to your new question.
0
eantarAuthor Commented:
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.