vba 使用 Google Earth 进行地理编码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1721338/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Geocoding using Google Earth
提问by chiccodoro
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.
我正在 Excel 工作簿中编写 VBA。我可以假设 Google Earth 已安装并注册为 COM 服务器。因此，我一直在寻找一种使用 Google Earth COM API 来实现这一目标的方法，但是我没有找到任何东西。
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.
我在网络上找到的大多数点击都是针对网络程序员的，并且是关于使用“Google 地理编码 API”（即 JavaScript），我认为这对我的情况不可行。
(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.)
（更新：这是针对数十个/数百个地址的批处理作业。正如 rjmunro 指出的那样，它是私有的，因此违反了 Google 的使用条款，所以我不得不寻找不同的服务。）
回答by rjmunro
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 examplesof using VBA to access geocoding apis on the web, both with Google, and with other providers (Bing, Yahoo, etc.).
为什么要使用 Google 地球？电脑不在线是无法进行地理编码的，如果是在线的话，也可以直接使用web apis。有很多使用 VBA 访问网络上的地理编码 API的示例，包括 Google 和其他提供商（Bing、Yahoo 等）。
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 questionin Google's FAQ.
请注意，如果这是针对私人应用程序，而不是任何人都可以下载的应用程序，则可能违反 Google 服务条款，您可能需要使用其他提供商。在 Google 的常见问题解答中查看此问题。
回答by Alain
I've actually done this in VBA, I'll give you my solution:
我实际上已经在 VBA 中完成了这个，我会给你我的解决方案：
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.
我使用谷歌地球 API（参考：地球 1.0 类型库）我首先获取地址信息（来自单元格 Tools.Range(rngGeocoderAddress)）。我为该点构建 KML 数据并将其发送到谷歌地球（可能没有必要），然后我对地址信息执行搜索。这将触发谷歌开始缩放到结果位置。
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)
在一个循环中，我定期监视 google earth 以查看它是否仍在缩放到新位置的过程中。一旦它停止移动，我知道它已经放大到搜索结果，我可以使用 GetPointOnTerrainFromScreenCoords(0,0) 捕获 Lat Long
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:
以下是一些屏幕截图，显示了我如何使其与 UI 配合使用：
Image 1Enter an Address
图 1输入地址
Image 2Click Geocode, code waits for zooming to stop
图2点击Geocode，代码等待缩放停止
Image 3Code records the the final lat/long, user clicks record if they are satisfied.
Image 3Code 记录最终的纬度/经度，用户点击记录，如果他们满意。
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)
我尝试循环代码以编程方式对许多位置进行地理编码。如果您将 google earth 的缩放速度配置为相当快（但不是即时的），则效果最佳
回答by smirkingman
I developed this Excel addinto do what your request, you may find it useful.
我开发了这个 Excel 插件来满足您的要求，您可能会发现它很有用。
It is, of course, up to you to respect Google's TOS.
当然，是否尊重 Google 的 TOS 取决于您。
(The addin enforces Google rate limits, as specified in their documentation).
（插件强制执行 Google 速率限制，如其文档中所述）。
