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.

It may help to give an example of the `sSearch` parameter for a "settlement that Google knows as polygons." Also the tag (and title) should be `openoffice-basic`, rather than `vba` which is only for MS Office. To avoid cross-posting, I suggest removing it from gis.stackexchange.com, since you really only need help parsing the XML file properly. — Jim K, Jan 15 '16 at 01:47

Axel Richter · Accepted Answer · 2016-01-15T14:32:29.860

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

score 0 · Answer 2 · answered Jan 15 '16 at 01:26

It sounds like you are looking for something more powerful than just matching "<" and ">" in tags. This often happens when working with XML, and there are many specialized libraries to accomplish this task.

Parsing XML can be accomplished in OpenOffice Basic using the com.sun.star.xml.sax.Parser interface. See https://wiki.openoffice.org/wiki/XML_and_Filter for details.

Alternatively, many languages have XML parsing libraries. Java and Python have XML parsing libraries and can also work with OpenOffice. The library I personally use most with OpenOffice is xml.dom.minidom.

Google Maps geocoding API in OpenOffice Calc VBA

2 Answers2