Geocoding using Google Earth
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 开发者_StackOverflow社区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.)
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.
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).
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)
精彩评论