London => [geocoding] => {latitude: -0.81, longitude: 51.745}
Geocoding is a common need when working with data as you may only have human-readable locations (e.g. “London” or a zip code like “12245”) but for a computer to display the data on a map or query it requires one to have actual numerical geographical coordinates.
Geocoding Data in a Google Docs Spreadsheet
A very common need is to geoc
Geocoding Data in a Google Docs Spreadsheet
Original Geo Code Google Spreadsheet Example
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). 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?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:>/p>
To get both latitude and longitude, change the XML query to:
The code in the XML query above uses an "or" || in XPath, and the result will now be an array of results that Google Docs will put in two cells (horizontally stacked (one below another)); this can be seen in Column C of the example spreadsheet.
To return the data in just one cell, with the two values separated by commas, we use the JOIN function:
=JOIN(",", ImportXML("http://open.mapquestapi.com/nominatim/v1/search?format=xml&q=London", "//place[1]/@lat | //place[1]/@lon"))
In order to geocode based on a place name in another cell in the spreadsheet, add the place name to our API requests in MapQuest's Nominatim service using CONCATENATE function; the example below assures 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"))
"IF (IsBlank( " to avoid wasteful calls: =IF(isBlank(A2), "", transpose(ImportXML(CONCATENATE("http://open.mapquestapi.com/no...",A2), "//place[1]/@lat | //place[1]/@lon")))
I wanted the values in two different cells (as with the 2nd formula given) but side-by-side. So different columns for lat and lon. To do that I just put "transpose(" function around the whole thing. http://stackoverflow.com/a/197...