我目前正在处理一份数据清单,其中一个数据列是房源的地址。我计划在Google地图上标出每个房源的位置,但我希望避免手动复制粘贴地址以获取经度和纬度参数。我的Excel表格(或Google文档电子表格)包含大约500个地址。
因此,我希望能够自动完成这个过程,有没有办法做到?
我目前正在处理一份数据清单,其中一个数据列是房源的地址。我计划在Google地图上标出每个房源的位置,但我希望避免手动复制粘贴地址以获取经度和纬度参数。我的Excel表格(或Google文档电子表格)包含大约500个地址。
因此,我希望能够自动完成这个过程,有没有办法做到?
感谢这篇优秀的文章 http://policeanalyst.com/using-the-google-geocoding-api-in-excel/
Function GoogleGeocode(address As String) As String
Dim strAddress As String
Dim strQuery As String
Dim strLatitude As String
Dim strLongitude As String
strAddress = URLEncode(address)
'Assemble the query string
strQuery = "http://maps.googleapis.com/maps/api/geocode/xml?"
strQuery = strQuery & "address=" & strAddress
strQuery = strQuery & "&sensor=false"
'define XML and HTTP components
Dim googleResult As New MSXML2.DOMDocument
Dim googleService As New MSXML2.XMLHTTP
Dim oNodes As MSXML2.IXMLDOMNodeList
Dim oNode As MSXML2.IXMLDOMNode
'create HTTP request to query URL - make sure to have
'that last "False" there for synchronous operation
googleService.Open "GET", strQuery, False
googleService.send
googleResult.LoadXML (googleService.responseText)
Set oNodes = googleResult.getElementsByTagName("geometry")
If oNodes.Length = 1 Then
For Each oNode In oNodes
strLatitude = oNode.ChildNodes(0).ChildNodes(0).Text
strLongitude = oNode.ChildNodes(0).ChildNodes(1).Text
GoogleGeocode = strLatitude & "," & strLongitude
Next oNode
Else
GoogleGeocode = "Not Found (try again, you may have done too many too fast)"
End If
End Function
Public Function URLEncode(StringVal As String, Optional SpaceAsPlus As Boolean = False) As String
Dim StringLen As Long: StringLen = Len(StringVal)
If StringLen>0 Then
ReDim result(StringLen) As String
Dim i As Long, CharCode As Integer
Dim Char As String, Space As String
If SpaceAsPlus Then Space = "+" Else Space = "%20"
For i = 1 To StringLen
Char = Mid$(StringVal, i, 1)
CharCode = Asc(Char)
Select Case CharCode
Case 97 To 122, 65 To 90, 48 To 57, 45, 46, 95, 126
result(i) = Char
Case 32
result(i) = Space
Case 0 To 15
result(i) = "%0" & Hex(CharCode)
Case Else
result(i) = "%" & Hex(CharCode)
End Select
Next i
URLEncode = Join(result, "")
End If
End Function
function getLatLong(adress) {
try{
if(adress=="")return("");
var geo = Maps.newGeocoder().geocode(adress);
if(geo.status=="OK"){
var lng = geo.results[0].geometry.viewport.southwest.lng;
var lat = geo.results[0].geometry.viewport.southwest.lat;
return([lat,lng]);
}
else{
return("error");
}
}
catch(err){
return(err);
}
}
SmartyStreets提供美国和国际地址地理编码服务。使用起来非常简单-将列表粘贴到其网页工具中,它将在几秒钟内返回纬度和经度。还有其他类似的服务可以返回相似的经纬度数据;我最熟悉SmartyStreets,因为我在那里工作。
另外要记住,将地址粘贴到Google Maps中并不一定会给您准确的信息,因为Google首先不会验证地址;而SmartyStreets会,这确保您获取的是真实位置的数据,而不是Google的猜测。
您可能还想查看其他解决方案:AddressDoctor, Loqate或其他“国际地址地理编码”提供商(快速搜索谷歌将找到一些很好的选项)。