Geocoding Part II: Geocoding Data in a Google Docs Spreadsheet

February 19, 2013 in Geocoding, HowTo

This tutorial follows on from the previous Introduction to Geocoding.

A very common need is to geocode data in a Google Spreadsheet (for example, in creating TimeMaps with the Timeliner project). There are several options here:

  1. By hand – use a Geocoding service (see the previous post) and then copy and paste by hand.
  2. Use the ImportXML (or ImportCSV) formulae to grab data from a geocoding service – great but with limitations on the number of rows you can code at one time (~50).
  3. Use a Google App Script – the most powerful but requires installation of an App Script in your spreadsheet.

In this tutorial I’m going to cover the latter two automated options and specifically focus on option 2.

Using Formulas

All of the following is illustrated live in this google spreadsheet.

We start with a formula like the following:

=ImportXML("http://open.mapquestapi.com/nominatim/v1/search.php?format=xml&q=London", "//place[1]/@lat")

This formula uses the ImportXML function to look up XML data from the Mapquest Nominatim geocoding service (see the previous tutorial for more about geocoding services). The first argument to ImportXML is the URL to fetch (in this case the results from querying the geocoding service) and the second part is an XPath expression to select data from that returned XML. In this case, the XPath looks up the first place object in the results: place[1] and then gets the lat (latitude) attribute. To understand this more clearly, here’s the XML returned by that XML query:

In reality we want both latitude and longitude, so let’s change it to:

=ImportXML("http://open.mapquestapi.com/nominatim/v1/search?format=xml&q=London", "//place[1]/@lat | //place[1]/@lon")

This uses an “or” || expression in XPath and the result will now be an array of results that Google Docs will put in 2 cells (one below another). You can see this in Column C of the example spreadsheet.

What happens if we wanted the data in just one cell, with the two values separated by commas, for example? We could use the JOIN function:

=JOIN(",", ImportXML("http://open.mapquestapi.com/nominatim/v1/search?format=xml&q=London", "//place[1]/@lat | //place[1]/@lon"))

Lastly, we’d like to geocode based on a place name in an another cell in the spreadsheet. To do this we just need to add the place name to our API request to MapQuest’s Nominatim service using the CONCATENATE function (this example assues the value is in cell A2):

=ImportXML(CONCATENATE("http://open.mapquestapi.com/nominatim/v1/search?format=xml&q=", A2), "//place[1]/@lat")
...
=JOIN(",", ImportXML(CONCATENATE("http://open.mapquestapi.com/nominatim/v1/search?format=xml&q=",A2), "//place[1]/@lat | //place[1]/@lon"))

App Script

If you want an even more powerful approach you can use a Google App Script. In particular, Development Seed’s MapBox team have prepared a great ready-made Google AppScript that will do geocoding for you.

Find the script plus instructions online here and further details in this blog post.

flattr this!

  • Pingback: Geocoding Using the Google Maps Geocoder via OpenRefine « OUseful.Info, the blog…

  • http://www.sukey.org/ Samuel Carlisle

    Great geocoding howto, thanks very much for writing this up. I noticed that your example spreadsheet https://docs.google.com/spreadsheet/ccc?key=0AqR8dXc6Ji4JdHBhY25yQkpHWF9NcEt1d3hrU0JWcUE#gid=0 importXML functions were returning: “error: The xPath query did not return any data.” I guessed that perhaps the mapquest API url endpoints might have changed so I checked their API docs: http://open.mapquestapi.com/nominatim/#search_basic and I found that by changing “http://open.mapquestapi.com/search” to “http://open.mapquestapi.com/nominatim/v1/search.php” the importXML functions returned data again. I made a copy of your spreadsheet and applied the changes mentioned to show it working that you can see here: https://docs.google.com/spreadsheet/ccc?key=0AtPlyIZQV2PIdGFfdVJ4NzA3bVROd2JNcWhjSzNOelE&usp=sharing

    • Michael Bauer

      Thanks, we’ve already fixed this in the recipe, however forgot to update it here…

  • Abhiram Chintangal

    Thanks! How do i make it work with an entire spreadsheet?

    • Michael Bauer

      You should be able to copy and paste the formula down the spreadsheet. Or simply “drag” it out: Try doubleclicking on the big – blue square (bottom right of the cell) – this should expand the formula to all the cells.

  • http://www.creconsult.net/ Randolph J. Taylor MBA, CCIM,

    Mapquest is poor at geocoding, missing quite a few. You can get a 90-day (1000 records) total with Cicero. Burning up about 750 geocoding a customer database for a client and will need to do more…isn’t there a Google Maps API I would imagine would be equal to or better than Cicero and most anything is better than Mapquest. Thanks for the script though, saved me a LOT of time.