Results 1 to 3 of 3
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    La Jolla, CA
    Thanked 76 Times in 71 Posts

    Google Maps distance between 2 points

    The trig formula calculates the distance between 2 points (longitude and latitude) "as the crow flies."
    I remember a discussion about how Google Maps calculates the driving distance between 2 points (I may have even started one of them) but can't find any reference to it when I search here. I believe there's a macro/Google Maps API, but also can't find a clear statement of the macro in my searching the web.

    Anyone have this macro? TIA

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Newcazzle, UK
    Thanked 643 Times in 611 Posts

    ..I've used this to get street address data from Australia, if it helps:
    Public Function GetCoordinates(Address As String) As String
    'This function returns the latitude and longitude of a given address using the Google Geocoding API.
    'The function uses the "simplest" form of Google Geocoding API (sending only the address parameter),
    'so, optional parameters such as bounds, key, language, region and components are NOT used.
    'In case of multiple results (for example two cities sharing the same name), the function
    'returns the FIRST OCCURRENCE, so be careful in the input address (tip: use the city name and the
    'postal code if they are available).
    'NOTE: As Google points out, the use of the Google Geocoding API is subject to a limit of 2500
    'requests per day, so be careful not to exceed this limit.
    'For more info check:
    'In order to use this function you must enable the XML, v3.0 library from VBA editor:
    'Go to Tools -> References -> check the Microsoft XML, v3.0.
    'Declaring the necessary variables. Using 30 at the first two variables because it
    'corresponds to the "Microsoft XML, v3.0" library in VBA (msxml3.dll).
    Dim Request         As New XMLHTTP30
    Dim Results         As New DOMDocument30
    Dim statusNode      As IXMLDOMNode
    Dim LatitudeNode, aaaa   As IXMLDOMNode
    Dim LongitudeNode   As IXMLDOMNode
    Dim badNode
    Dim userBeanList As MSXML2.IXMLDOMNodeList
    Dim userbean As MSXML2.IXMLDOMNode
    Dim beanChild As MSXML2.IXMLDOMNode
    On Error GoTo errorHandler
    'Create the request based on Google Geocoding API. Parameters (from Google page):
    '- Address: The address that you want to geocode.
    '- Sensor: Indicates whether your application used a sensor to determine the user's location.
    'This parameter is no longer required.
    Request.Open "GET", "" _
    & "&address=" & Address & "+vic+au&sensor=false", False
    'Send the request to the Google server.
    'Read the results from the request.
    Results.LoadXML Request.responseText
    'Get the status node value.
    Set statusNode = Results.SelectSingleNode("//status")
    Dim street_number, Route, locality, administrative, postal_code, subpremise, subpremiseNode
    subpremiseNode = "sad"
    'Based on the status node result, proceed accordingly.
    Select Case UCase(statusNode.text)
    Case "OK"   'The API request was successful. At least one geocode was returned.
    'Set userBeanList = Results.SelectSingleNode("//result/formatted_address")
    Set userBeanList = Results.SelectNodes("//result/address_component")
    For Each userbean In userBeanList
    For Each beanChild In userbean.ChildNodes
    If (beanChild.text = "subpremise") Then
    Set subpremiseNode = beanChild.PreviousSibling
    End If
    If (beanChild.text = "street_number") Then
    Set street_number = beanChild.PreviousSibling
    End If
    If (beanChild.text = "route") Then
    Set Route = beanChild.PreviousSibling
    End If
    If (beanChild.text = "locality") Then
    Set locality = beanChild.PreviousSibling
    End If
    If (beanChild.text = "administrative_area_level_1") Then
    Set administrative = beanChild.PreviousSibling
    End If
    If (beanChild.text = "postal_code") Then
    Set postal_code = beanChild.PreviousSibling
    End If
    Next beanChild
    Next userbean
    GetCoordinates = street_number.text & "," & Route.text & "," & locality.text & "," & administrative.text & "," & postal_code.text
    If (subpremiseNode.text <> "") Then
        GetCoordinates = subpremiseNode.text & "/" & street_number.text & "," & Route.text & "," & locality.text & "," & administrative.text & "," & postal_code.text
    End If
    Case "ZERO_RESULTS"   'The geocode was successful but returned no results.
    GetCoordinates = "The address probably not exists"
    Case "OVER_QUERY_LIMIT" 'The requestor has exceeded the limit of 2500 request/day.
    GetCoordinates = "Requestor has exceeded the server limit"
    Case "REQUEST_DENIED"   'The API did not complete the request.
    GetCoordinates = "Server denied the request"
    Case "INVALID_REQUEST"  'The API request is empty or is malformed.
    GetCoordinates = "Request was empty or malformed"
    Case "UNKNOWN_ERROR"    'Indicates that the request could not be processed due to a server error.
    GetCoordinates = "Unknown error"
    Case Else   'Just in case...
    GetCoordinates = "Error"
    End Select
    'In case of error, release the objects.
    Set statusNode = Nothing
    Set LatitudeNode = Nothing
    Set LongitudeNode = Nothing
    Set Results = Nothing
    Set Request = Nothing
    End Function

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    La Jolla, CA
    Thanked 76 Times in 71 Posts
    I have the long and lat for several addresses, but need the driving distance (not as the crow files -- which I can compute using the trig formula).

    Also, Zeddy, if it matters should I ever use your code, I've used v6.0 for the XML

    Zeddy: from the site reference in your code, I was able to find a routine I can use to find the miles. I'm doing some testing, but so far it seems to match the results from Maps.Google.Com.

    Last edited by kweaver; 2016-05-19 at 16:14.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts