Geocoding using Google Earth

Question

I would like to get a pair of coordinates (longitude, latitude) from a postal address.

I am coding VBA in an Excel workbook. I can assume that Google Earth is installed and registered as COM server. Thus I have been looking for a way to use the Google Earth COM API to achieve this, however I have not managed to find anything.

Most of the hits I found on the web are for web programmers and are about using the "Google geocoding API" which is JavaScript, which I think is not feasible for my case.

(Update: This was to be done as a batch job for dozens/hundreds of addresses. As rjmunro pointed out, it's private and thus against the Google terms of use, so I had to look for a different service.)

Answer 1

Why do you want to use Google Earth? It can't geocode if the computer is not online, and if it is online, you may as well use the web apis directly. There are lots of examples of using VBA to access geocoding apis on the web, both with Google, and with other providers (Bing, Yahoo, etc.).

Note that if this is for a private application, rather than one that anyone can download, it may be against the Google terms of service, and you may need to use a different provider. See this question in Google's FAQ.

Answer 2

I developed this Excel addin to do what your request, you may find it useful.

It is, of course, up to you to respect Google's TOS.

(The addin enforces Google rate limits, as specified in their documentation).

Answer 3

I've actually done this in VBA, I'll give you my solution:

I use the google earth API (Reference: Earth 1.0 Type Library) I start by taking the address information (from cell Tools.Range(rngGeocoderAddress)). I construct KML data for the point and send it to google earth (probably not necessary), I then execute a search on the address information. This will trigger google to start zooming the to resulting location.

In a loop, I periodically monitor google earth to see whether it is still in the process of zooming to a new location. Once it has stopped moving, I know that it has zoomed to the search result, and I can capture the Lat Long using GetPointOnTerrainFromScreenCoords(0,0)

The end result is I have used google earth to geocode the address.

Public Sub LookUpAddress_Click()
    Dim GEI As ApplicationGE
    Dim PointOnTerrain As PointOnTerrainGE
    Dim Search As SearchControllerGE
    Dim KMLData As String
    Dim row As Long

    'Get the row of the location selected to look up'
    With ddGeocoderID()
        If .listCount <= 1 Then Exit Sub
        row = .ListIndex + 1
    End With

    Set GEI = GEInit()
    If GEI Is Nothing Then Exit Sub

    KMLData = "<?xml version=""1.0"" encoding=""UTF-8""?>" & _
            "<kml xmlns=""http://www.opengis.net/kml/2.2"">" & _
            "<Placemark>" & _
                "<name>" & ddGeocoderID().List(ddGeocoderID().ListIndex) & "</name>" & _
                "<visibility>1</visibility>" & _
                "<open>1</open>" & _
                "<description>" & "<![CDATA[" & Tools.Range(rngGeocoderDescription) & "]]></description>" & _
                "<address>" & Tools.Range(rngGeocoderAddress) & "</address>" & _
            "</Placemark>" & _
            "</kml>"
    GEI.LoadKmlData KMLData

    Set Search = GEI.SearchController()
    Call Search.Search(Tools.Range(rngGeocoderAddress))
    Dim resul As Variant
    Set resul = Search.GetResults()
    Dim lat As Double, lon As Double, prevlat As Double, prevlon As Double, checkChange As Double
    Dim steady As Boolean
    steady = False: checkChange = -1
    lat = 0: lon = 0: prevlat = -1: prevlon = -1
    While Not steady
        Set PointOnTerrain = GEI.GetPointOnTerrainFromScreenCoords(0, 0)
        lat = PointOnTerrain.Latitude
        lon = PointOnTerrain.Longitude
        lblGeoedLat().Caption = sigFigs(lat, 8)
        lblGeoedLong().Caption = sigFigs(lon, 8)
        DoEvents
        If (checkChange = 100) Then
            If (lat = prevlat And lon = prevlon) Then steady = True
            prevlat = lat: prevlon = lon
            checkChange = -1
        End If
        checkChange = checkChange + 1
        Sleep 10
    Wend
End Sub

Here's some screen-shots showing how I made it work with a UI:

Image 1 Enter an Address

Image 2 Click Geocode, code waits for zooming to stop

Image 3 Code records the the final lat/long, user clicks record if they are satisfied.

I've tried looping the code to geocode many locations programatically. It works best if you configure google earth's Zoom speed to be as quite fast (but not instantaneous)