• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Use google maps api for lat/long, embed into Haversine?

chelston

New Member
Hi All! I'm pretty new to VBA so this is probably embarrassingly simple, but how do I use google maps/api to obtain lat/long of zip codes, then plug that into a haversine formula for approximate "as the crow flies" distances?

OR

Is there any way to change the mode to "flight" or some version of a straight line calculation in line 6 of the following? (I have zip codes and need approximate flight distances.)

'Calculate Google Maps distance between two addresses
>>> use code - tags <<<
Code:
Public Function GetDistance(start As String, dest As String)
    Dim firstVal As String, secondVal As String, lastVal As String
    firstVal = "https://maps.googleapis.com/maps/api/distancematrix/json?origins="
    secondVal = "&destinations="
    lastVal = "&mode=car&language=pl&sensor=false&key=AIzaSyD8EtmS82qghp2RcmCIFgjxtsot-5xeWu8"
    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
 
Last edited by a moderator:
You'd need a table mapping ZIP codes to lat and long (you can't calculate that part, you have to look it up). You can calculate the distance between two points, given lat and long, by just using the old X^2 + Y^2 = Z^2, but that's accurate only on a flat surface; I don't know enough spherical trig to tell you how to do it on the surface of a globe. Maybe the regular two-dimensional formula will be close enough.

I'm looking at your code but I don't follow part of it. Are you saying Google maps will tell you lat and long if you hand it a ZIP code?
 

chelston

New Member
BobBridges, thanks for the feedback! I was under the impression that I could use a google maps geocoding API to obtain lat/long of a city/state/zip code, then somehow plug that into a haversine formula.

(The code is one that I found online which would work for my project, except that it provides driving distances.)
 
Hm, sounds like you know a lot more about this than I do. I wouldn't be surprised to learn that latitude and longitude can be extracted from Google Maps, but I've never had occasion to do it.

But now that you've got me thinking about it, I did a little googling and came up with maybe something simpler: At https://public.opendatasoft.com/explore/dataset/us-zip-code-latitude-and-longitude/table/ I see a plain-text table showing lat and long for each ZIP code. And a cousin URL, https://public.opendatasoft.com/explore/dataset/us-zip-code-latitude-and-longitude/export/, apparently has files you can download; get one of those, once, and your program can give immediate response for any pair of ZIP codes instead of having to query Google Maps and wait. Not quite what you asked, but I'm kind of hoping you'll think it's even better.
 

Chihiro

Excel Ninja
@chelston

Any travel mode won't do you good. You'll need to use Google's or some other means to first obtain Lat/Long.
That function you posted, is specifically for distancematrix api and not for geocoding api.


https://developers.google.com/maps/documentation/geocoding/intro

For haversine, you can use formula method or use vba (UDF). See below thread for details. Post#3 has link to UDF I posted.
Post#5 by bosco_yip shows formula method.
https://chandoo.org/forum/threads/airport-to-airport-distance.40001/#post-239338

NOTE: Google API, unless you are on paid tier, will put limit on number of queries per second / day. For this reason, if the speed isn't critical and list is quite large, you may want to pilot IE instead of using MSXML2. Below is example that I created long time ago (3 to 4 years). Things may have changed since.

Code:
Function GetCurrentURL()
Dim vUrl As String
Set SWs = New SHDocVw.ShellWindows

For Each vIE In SWs
    If TypeName(vIE.document) = "HTMLDocument" Then
        vUrl = vIE.document.Url
        Exit For
    End If
Next
GetCurrentURL = vUrl
End Function

Sub LatLong()
Dim cel As Range
Dim sStr As String, ltlng As String
Dim iMax As Long, i As Long: i = 0
Dim IE As InternetExplorer

iMax = 5000

For Each cel In Sheet1.Range("Table1[Street]").SpecialCells(xlCellTypeVisible).Cells
    If Len(cel.Offset(, 2).Value) = 0 Then
        sStr = cel.Value & ", " & cel.Offset(, -1).Value
        Set IE = New InternetExplorer
        IE.Visible = True
        IE.navigate "www.google.ca/maps?q=" & sStr
        While IE.readyState <> 4 Or IE.Busy: DoEvents: Wend
        Application.Wait (Now + TimeValue("0:00:05"))
        ltlng = Split(Split(GetCurrentURL(), "@")(1), ",17z")(0)
        cel.Offset(, 2) = CDbl(Split(ltlng, ",")(0))
        cel.Offset(, 3) = CDbl(Split(ltlng, ",")(1))
        IE.Quit
        Set IE = Nothing
        i = i + 1
    If i = iMax Then Exit For
    End If
    Application.StatusBar = "Progress: " & i & " of " & iMax & " " & Format(i / iMax, "Percent")
Next
Application.StatusBar = False
End Sub
Table set up:
69615
 
Top