Find distance between two cities in Excel using Google Maps API

Question

I would like a table of the following form:

Point A         Point B           Mileage
Los Angeles     Miami             292100
Palo Alto       San Francisco     90

I was hoping to use Google Maps or some other geo api to generate mileage based on input cities dynamically. Any ideas on how to do this?

UPDATE It looks like I could use the getDistance() function in GDirections. I could write JavaScript without too much difficulty to do this, but how would I incorporate that into Excel?

I could put the JS in an html file that takes a query string and returns the distance of the route. Then, I could set Excel up to use that connection. Or is that an excessive amount of work? And hasn't something like this been done before?

Answer 1

So, you can use Google's API to get a latitude and longitude for each point (maybe in your case, you just want any point in those cities). Then you can use the Halversine distance formula:

http://en.wikipedia.org/wiki/Haversine%5Fformula

For VBA, I have this function in my library that I got from somewhere. On first glance, it looks right and it yields the distance in kilometers (just convert it to miles for your purpose).

Public Function HaversineDistance(ByVal Lat1 As Double, _
                            ByVal Lat2 As Double, _
                            ByVal Long1 As Double, _
                            ByVal Long2 As Double) As Double

    Const R As Integer = 6371   'earth radius in km

    Dim DeltaLat As Double, DeltaLong As Double
    Dim a As Double, c As Double
    Dim Pi As Double

    On Error GoTo ErrorExit

    Pi = 4 * Atn(1)

    'convert Lat1, Lat2, Long1, Long2 from decimal degrees into radians
    Lat1 = Lat1 * Pi / 180
    Lat2 = Lat2 * Pi / 180
    Long1 = Long1 * Pi / 180
    Long2 = Long2 * Pi / 180

    'calculate change in Latitude and Longitude
    DeltaLat = Abs(Lat2 - Lat1)
    DeltaLong = Abs(Long2 - Long1)

    a = ((Sin(DeltaLat / 2)) ^ 2) + (Cos(Lat1) * Cos(Lat2) * ((Sin(DeltaLong / 2)) ^ 2))
    'c = 2 * Application.WorksheetFunction.Atan2(Sqr(a), Sqr(1 - a))                     'expressed as radians
    c = 2 * Application.WorksheetFunction.Atan((Sqr(1 - a)) / (Sqr(a)))
    HaversineDistance = R * c
ErrorExit:
End Function

(Note to other commenters, I'm not interested in hearing about how such accuracy is unnecessary so please don't bother tell me!)

Answer 2

Google currently only provide legal access to driving distances through the client side APIs (Javascript and Flash). Even if they ever do get round to implementing Issue 235: Get driving directions via HTTP , it would still be against Google's Terms (para 10.12) to use the data for purposes other than display on a Google Map.