11. October 2013
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?