I'm trying to clean geocode data using Google's Geocode API. Sometimes I can get the right result and sometimes my code comes back with zero results or invalid request. I'm using the following code:
Function GetCoordinates(Address As String) As String
Dim Request As New XMLHTTP30
Dim Results As New DOMDocument30
Dim StatusNode As IXMLDOMNode
Dim LatitudeNode As IXMLDOMNode
Dim LongitudeNode As IXMLDOMNode
On Error GoTo errorHandler
Request.Open "GET", "https://maps.googleapis.com/maps/api/geocode/xml?&address=" & Address & "&sensor=false&key=KEYGOESHERE", False
'Send the request to the Google server.
Request.send
'Read the results from the request.
Results.LoadXML Request.responseText
'Get the status node value.
Set StatusNode = Results.SelectSingleNode("//status")
'Based on the status node result, proceed accordingly.
Select Case UCase(StatusNode.Text)
Case "OK" 'The API request was successful. At least one geocode was returned.
'Get the latitdue and longitude node values of the first geocode.
Set LatitudeNode = Results.SelectSingleNode("//result//geometry//location//lat")
Set LongitudeNode = Results.SelectSingleNode("//result//geometry//location//lng")
'Return the coordinates as string (latitude, longitude).
GetCoordinates = LatitudeNode.Text & "; " & LongitudeNode.Text
Case "ZERO_RESULTS" 'The geocode was successful but returned no results.
GetCoordinates = "Address not found"
Case "OVER_QUERY_LIMIT" 'The requestor has exceeded the limit of 2500 request/day.
GetCoordinates = "Exceeded the server limit"
Case "REQUEST_DENIED" 'The API did not complete the request.
GetCoordinates = "Request denied"
Case "INVALID_REQUEST" 'The API request is empty or is malformed.
GetCoordinates = "Invalid request"
Case "UNKNOWN_ERROR" 'Indicates that the request could not be processed due to a server error.
GetCoordinates = "Unknown error"
Case Else 'Just in case...
GetCoordinates = "Error"
End Select
End Function
Sometimes the code returns the correct latitude and longitude and sometimes it comes back as "address not found" or "invalid request". For example the three following addresses:
- BEOGRADSKA 133, VRČIN, Serbien--> Works fine
- BEM JOŽEFA 48, TEMERIN, Serbien--> Invalid request
- LIJAČKA 12, BEOGRAD, Serbien--> Address not found
As far as I can tell the XMLs do not differ from each other and when I manually take my code and paste it into a web browser, it comes back with no errors.
The xml code looks like this:
<?xml version="1.0"?>
<GeocodeResponse>
<status>OK</status>
<result>
<type>route</type>
<formatted_address>Lijačka, Beograd, Serbien</formatted_address>
<address_component>
<long_name>Lijačka</long_name>
<short_name>Lijačka</short_name>
<type>route</type>
</address_component>
<address_component>
<long_name>Beograd</long_name>
<short_name>BG</short_name>
<type>locality</type>
<type>political</type>
</address_component>
<address_component>
<long_name>Grad Beograd</long_name>
<short_name>Grad Beograd</short_name>
<type>administrative_area_level_2</type>
<type>political</type>
</address_component>
<address_component>
<long_name>Serbien</long_name>
<short_name>RS</short_name>
<type>country</type>
<type>political</type>
</address_component>
<geometry>
<location>
<lat>44.7803076</lat>
<lng>20.4817074</lng>
</location>
<location_type>GEOMETRIC_CENTER</location_type>
</geometry>
</result>
</GeocodeResponse>
Any hints would be greatly appreciated!!