Google Maps geocoding API in OpenOffice Calc VBA

Question

For my project, I need to geocode a set of locations for which I would like to know the GPS coordinates.

The amount of locations it too big to to it by hand, but not too much so that I will not have problems with Google's limitations of the use of the Geocoding API.

The most convenient way to do this for me would be to use OpenOffice Calc.

I found a VBA code that does just what I need:

Function GetGeoData(sSearch as String) as String
   If Len(sSearch) = 0 Then Exit Function 'we dont need empty cells <img draggable="false" class="emoji" alt="😉" src="http://s.w.org/images/core/emoji/72x72/1f609.png">
   URL = "http://maps.googleapis.com/maps/api/geocode/xml?sensor=true&address="  'we will use the google maps api
   URL = URL & sSearch          'create the searchstring
   oSimpleFileAccess = createUnoService( "com.sun.star.ucb.SimpleFileAccess" ) 'this is the Sefvice in getting the data from the web
   On Error GoTo ErrorResponse
   oInputStream = oSimpleFileAccess.openFileRead(URL) 'use the URL
   oTextStream = createUnoService("com.sun.star.io.TextInputStream") 'get the data from the web
   oTextStream.InputStream = oInputStream 'this is the data
   aDelimiters = Array(ASC(">"),ASC("<")) 'as the stream is segmented with ">" and "<"
   sLastString = ""
   Do While NOT oTextStream.isEOF 'go through the google output
      sThisString = oTextStream.readString(aDelimiters,True) 
      Select Case sLastString 'now search for the entries
         Case "lat": 'latitudes
            sLat = sThisString  
            Case "lng": 'longitude
            sLon = sThisString
      End Select
      sLastString = sThisString
   Loop
   GetGeoData =  " Longitude: " & sLon & " Latitude: " &sLat 'this is our output in  the new cell
   oInputStream.closeInput()
   Exit Function
   ErrorResponse:
   GetGeoData = "no values found!!!"
End Function

However, while it is fine for exact addresses, there is a problem when it comes to settlements that Google knows as polygons. In this case, the code only keeps the last set of coordinates it found in the xml info, but this corresponds to the north-east corner of the polygon. I would be happy to have the center of the polygon, which corresponds to the first set of coordinates in the xml document generated by Google maps.

• Can anyone explain to me how I can select a specific node in the xml file based on this code?
• Another solution would be to keep only the first set of coordinates.

Answer 1

At first: Never take XML as text string only. XML has a meaningful data structure which needs to be parsed. Fortunately the Openoffice API provides a XML parser already. com.sun.star.xml.dom.DocumentBuilder https://www.openoffice.org/api/docs/common/ref/com/sun/star/xml/dom/DocumentBuilder.html

To your question: Each result has a geometry with a location. The lat, lng in that location will be either the approximate lat, lng or the geometric center. The other lat, lng are viewport or bounds ones.

Example Berlin, Germany:

  <geometry>
   <location>
    <lat>52.5200066</lat>
    <lng>13.4049540</lng>
   </location>
   <location_type>APPROXIMATE</location_type>
   <viewport>
    <southwest>
     <lat>52.3396296</lat>
     <lng>13.0891553</lng>
    </southwest>
    <northeast>
     <lat>52.6754542</lat>
     <lng>13.7611176</lng>
    </northeast>
   </viewport>
   <bounds>
    <southwest>
     <lat>52.3396296</lat>
     <lng>13.0891553</lng>
    </southwest>
    <northeast>
     <lat>52.6754542</lat>
     <lng>13.7611176</lng>
    </northeast>
   </bounds>
  </geometry>

So only the lat, lng from the location are needed.

But there are other issues too. What if there are more than one results? Berlin, for example, is not only the capital of Germany.

So my example function returns all results:

Function GetGeoData(sSearch as String) as String

 sResult = ""

 if len(sSearch) > 0 and sSearch <> "0" then

  sURI = "http://maps.googleapis.com/maps/api/geocode/xml?sensor=true&address="
  sURI = sURI & sSearch
  oDocumentBuilder = createUnoService("com.sun.star.xml.dom.DocumentBuilder")
  oDOMDocument = oDocumentBuilder.parseURI(sURI)

  oResults = oDOMDocument.getElementsByTagName("result")

  for i = 0 to oResults.length -1
   oResult = oResults.item(i)
   oformattedAddress = oResult.getElementsByTagName("formatted_address").item(0)
   sformattedAddress = oformattedAddress.getFirstChild().nodeValue

   oGeometry = oResult.getElementsByTagName("geometry").item(0)
   oLocation = oGeometry.getElementsByTagName("location").item(0)
   oLat = oLocation.getElementsByTagName("lat").item(0)
   sLat = oLat.getFirstChild().nodeValue
   oLng = oLocation.getElementsByTagName("lng").item(0)
   sLng = oLng.getFirstChild().nodeValue

   if i = 0 then 
    sResult = sResult & sformattedAddress & ": Lat:" & sLat & " Lng:" & sLng
   else
    sResult = sResult & "; " & sformattedAddress & ": Lat:" & sLat & " Lng:" & sLng    
   end if   

  next

 end if

 GetGeoData = sResult

End Function