2

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&#x10D;ka, Beograd, Serbien</formatted_address>
    <address_component>
      <long_name>Lija&#x10D;ka</long_name>
      <short_name>Lija&#x10D;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!!

Parfait
  • 104,375
  • 17
  • 94
  • 125
VBANewbie
  • 21
  • 1
  • You need to `URLencode` the `Address` before calling `Request.Open`. – Florent B. Oct 02 '17 at 14:59
  • Thanks, do you mean e.g. Replace(Address, " ", "+")? – VBANewbie Oct 03 '17 at 07:10
  • Ah, That's definitely an inprovement! I did it with the function URLencode directly in excel... Now I just need to write a few if functions to get the addresses that weren't found. – VBANewbie Oct 03 '17 at 07:30
  • No, URL encoding and replacing `" "` with `"+"` are not the same thing. Use an *acutal* URL encoding function. (Also, do not casually use `//` in your XPath.) – Tomalak Oct 04 '17 at 20:09
  • Excel 2013+ has a URL-encoding function built in (not sure about the other applications) but here is a thread with a few pure VBA choices. You want one with UTF-8 support. https://stackoverflow.com/questions/218181/how-can-i-url-encode-a-string-in-excel-vba – Tomalak Oct 04 '17 at 20:20

0 Answers0