geocoding addresses directly in LibreOffice Calc

In a recent post I’ve described a way to geocode in a Google Spreadsheet quite easily. Now let’s move a little bit away from this crazy web 2.0 stuff and get a little bit more desktop orientated: Let us geocode addresses directly in LibreOffice (and OpenOffice probably as well). We will do this using the googlemaps API. If you are really into open stuff we show OSM geocoding as well.

Using the Google API

Just to clear things up: I am using the little outdated Open Office 4.0.3 for Mac. But lets get our hands dirty: open up Calc and type in one cell your address. Now we will create a so called Macro or lets name it a user defined function:

Delete now everything in the editor and paste the following code:

    Function GetGeoData(sSearch as String) as String
       If Len(sSearch) = 0 Then Exit Function 'we dont need empty cells ;-)
       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

The code itself was developed by Jörg Napp and as I have no idea of VBA or OpenOffice Basic I was playing around a little bit so it was working on my system as it wasn’t in Jörg’s version. If you have pasted it, it should look like this after saving it:

As the function has the name GetGeoData just type in a new cell “=GetGeoData(A1)” and off you go with your new coordinates. As the structure is quite simply you can chnage it according to your needs.
But please read the “Google Maps/Google Earth APIs Terms of Service” whether you are allowed to use this service. Additionally note that you can use this service for 2,500 requests per day and most important :”The Geocoding API may only be used in conjunction with a Google map; geocoding results without displaying them on a map is prohibited.”

Using the OpenStreetMap “nominatim”

The usage of the OpenStreetMap geocoding API is a little bit different as the resulting stream of xml is a little different to the google API result. You can paste the next lines in the same “module” we’ve used before:

Function GetGeoDataOSM(sSearch as String) as String
       If Len(sSearch) = 0 Then Exit Function
       URL = "http://nominatim.openstreetmap.org/search?format=xml&q=" 
       URL = URL & sSearch
       oSimpleFileAccess = createUnoService( "com.sun.star.ucb.SimpleFileAccess" )
       On Error GoTo ErrorResponse
       oInputStream = oSimpleFileAccess.openFileRead(URL)
       oTextStream = createUnoService("com.sun.star.io.TextInputStream")
       oTextStream.InputStream = oInputStream
       aDelimiters = Array(ASC(">"),ASC("<"),ASC(" "),ASC("="))
       sLastString = ""
       Do While NOT oTextStream.isEOF
          sThisString = oTextStream.readString(aDelimiters,True)
          Select Case sLastString
             Case "lat":
                sLat = sThisString
                Case "lon":
                sLon = sThisString
          End Select
          sLastString = sThisString
       Loop
       GetGeoDataOSM = " Longitude: " & sLon & " Latitude: " &sLat 
       oInputStream.closeInput()
       Exit Function
       ErrorResponse:
       GetGeoDataOSM = "no values found!!!"
    End Function

Please look at the user license for the nominatim service prior usage
Now enjoy your map creation process:

Please check OSM results as they seem a little buggy: “Rathausmarkt 1, 20095 Hamburg” has no result but “Rathausmarkt 1 Hamburg 20095” will result in great coordinates…

Is there a more simplified way to parse the xml documents from both of the APIs?