从地址获取 GPS 坐标的代码 (VB/VB.Net/VBA/VBScript)

Code to Get GPS Coordinates from Address (VB/VB.Net/VBA/VBScript)

提问by HK1

I believe the Google API allows you to get coordinates for a given address. However, most (or should I say all) of the examples I've found aren't for vb. Usually it's some javascript example that just leaves me confused.

我相信 Google API 允许您获取给定地址的坐标。但是，我发现的大多数（或者我应该说所有）示例都不适用于 vb。通常是一些 javascript 示例让我感到困惑。

Here's some code I have that uses a geocoding service. This works great. It's just that I want to query Google Maps directly myself.

这是我使用地理编码服务的一些代码。这很好用。只是我想自己直接查询谷歌地图。

Public Function fgGetLatAndLongUsingAddress(sAddress As String) As String

    'This function works best with a complete address including the zip code
    Dim sResponseText As String, sReturn As String

    sReturn = "none"

    Dim objHttp As Object, sQuery As String
    sQuery = "http://rpc.geocoder.us/service/csv?address=" & Replace(sAddress, " ", "+")
    Set objHttp = CreateObject("Msxml2.ServerXMLHTTP")
    objHttp.Open "GET", sQuery, False
    objHttp.send
    sResponseText = objHttp.ResponseText
    gsLastLatLongResponseText = sResponseText
    Set objHttp = Nothing


    If Len(sResponseText) > 0 Then
        If InStr(sResponseText, "Bad Request") > 0 Then
            'Do Nothing
        ElseIf InStr(sResponseText, "couldn't find this address") > 0 Then
            'Do Nothing
        Else
            If InStr(sResponseText, vbCrLf) > 0 Then
                'We got more than one result
            End If
            If InStr(sResponseText, ",") > 0 Then
                Dim aryInfo() As String
                aryInfo = Split(sResponseText, ",")
                sReturn = aryInfo(0) & "," & aryInfo(1)
            End If
        End If
    End If


    fgGetLatAndLongUsingAddress = sReturn

End Function

回答by barrowc

This should do the job. You need to add a reference to the MSXML6 library (Microsoft XML, v6.0) via Tools > References in Excel

这应该可以完成工作。您需要通过 Excel 中的工具 > 引用添加对 MSXML6 库（Microsoft XML，v6.0）的引用

Option Explicit

Function getGoogleMapsGeocode(sAddr As String) As String

Dim xhrRequest As XMLHTTP60
Dim sQuery As String
Dim domResponse As DOMDocument60
Dim ixnStatus As IXMLDOMNode
Dim ixnLat As IXMLDOMNode
Dim ixnLng As IXMLDOMNode


' Use the empty string to indicate failure
getGoogleMapsGeocode = ""

Set xhrRequest = New XMLHTTP60
sQuery = "http://maps.googleapis.com/maps/api/geocode/xml?sensor=false&address="
sQuery = sQuery & Replace(sAddr, " ", "+")
xhrRequest.Open "GET", sQuery, False
xhrRequest.send

Set domResponse = New DOMDocument60
domResponse.loadXML xhrRequest.responseText
Set ixnStatus = domResponse.selectSingleNode("//status")

If (ixnStatus.Text <> "OK") Then
    Exit Function
End If

Set ixnLat = domResponse.selectSingleNode("/GeocodeResponse/result/geometry/location/lat")
Set ixnLng = domResponse.selectSingleNode("/GeocodeResponse/result/geometry/location/lng")

getGoogleMapsGeocode = ixnLat.Text & ", " & ixnLng.Text

End Function

The only real differences to your example are:

与您的示例唯一真正的区别是：

• changing the URL and parameters of the query to use the Google API
• treating the response as an XML document and using XPathto extract the required results

• 更改查询的 URL 和参数以使用 Google API
• 将响应视为 XML 文档并使用XPath提取所需的结果

Obviously there's no error handling whatsoever in my code but it should give you an idea of what you need to use the API. You definitely want to consult the API documentationyourself to find out about rate limiting etc

显然，我的代码中没有任何错误处理，但它应该让您了解使用 API 所需的内容。您肯定想自己查阅API 文档以了解有关速率限制等的信息

回答by HK1

Thanks for that code! It works great. Thanks for the heads up on the rate limits. Here's what Google has to say:

谢谢你的代码！它工作得很好。感谢您注意速率限制。这是谷歌不得不说的：

Use of the Google Geocoding API is subject to a query limit of 2,500 geolocation requests per day. (User of Google Maps API Premier may perform up to 100,000 requests per day.) This limit is enforced to prevent abuse and/or repurposing of the Geocoding API, and this limit may be changed in the future without notice. Additionally, we enforce a request rate limit to prevent abuse of the service. If you exceed the 24-hour limit or otherwise abuse the service, the Geocoding API may stop working for you temporarily. If you continue to exceed this limit, your access to the Geocoding API may be blocked. Note: the Geocoding API may only be used in conjunction with a Google map; geocoding results without displaying them on a map is prohibited. For complete details on allowed usage, consult the Maps API Terms of Service License Restrictions.

使用 Google Geocoding API 的查询限制为每天 2,500 个地理定位请求。（Google Maps API Premier 的用户每天最多可以执行 100,000 个请求。）强制执行此限制是为了防止滥用和/或重新利用 Geocoding API，并且此限制将来可能会更改，恕不另行通知。此外，我们强制执行请求速率限制以防止滥用服务。如果您超过 24 小时限制或以其他方式滥用服务，地理编码 API 可能会暂时停止为您工作。如果您继续超过此限制，您对地理编码 API 的访问可能会被阻止。注意：Geocoding API 只能与谷歌地图结合使用；禁止地理编码结果而不在地图上显示。有关允许使用的完整详细信息，请参阅 Maps API 服务许可限制条款。