Mapping Google Spreadsheets

There are several methods to use free map services for visualizing a list of point-features. I found this wizard for instance at the gmaps samples. It uses a published Google Spreadsheets document and puts the listed features on a map. This method is a clever way because you can use Google Spreadsheets to hold, manage and edit your data and don’t have to go through the map publishing process over and over again when you update your data.

The problem with that wizard is, that you have to know the coordinates already. So it won’t help if there is just a list of addresses without coordinate information. You must geocode (assign coordinates to each address) your items before you can put them on a map with this method. And if the map should be shown somewhere else, you’ll need a Google Maps API key, which is tied to exactly one url-string.

Luckily Yahoo! invented the Pipes: I put a quick Pipe together which allows you to geocode addresses stored in a Google Spreadsheet.

Here is how it works:

  1. Enter a list of addresses in Google Spreadsheet. Here, for instance, is a list of shops in Vienna where you can grab a free copy of biber.
  2. The column “Name” identifies the name of my features and the column “Address” holds the address to geocode. If you want to use another structure, you should clone the pipe and adjust the Regex-module to match your needs.
  3. Publish your Google Spreadsheet as Atom or RSS feed (click the link “More publishing options” in the “Publish” section), e.g. the biber feed
  4. Enter the feed url into the “Google Spreadsheet feed url” field and hit “run pipe”
  5. A Yahoo! Map showing all your (successfully geocoded) addresses should be produced

Alternatively you can take the GeoRSS feed or KML-file from the Pipe and display it in Google Earth or put it on a Google Map (and embed it into a blog post).


bigger map

Yahoo! Pipes are simple, yet powerful, and I think it should be possible to modify the Pipe in order to return a table containing coordinates. There is already a JSON output by default. Regarding the geocoding limit, I’m not sure which number applies for Yahoo! Pipes.

  • http://nona.net/ Alex

    :-O

    nice work ….

  • http://spanring.eu/ Christian

    Almost faster than starting up ArcMap ;-)

  • http://rolfm.wordpress.com rolf

    have you had a look at http://www.popfly.ms already?

  • http://spanring.eu/ Christian

    Not yet, but it’s in my bookmarks now. I guess one can do the same thing there. They even have more built-in 3rd party blocks/modules…

  • http://danmoldovan.blogspot.com/ Dan N. Moldovan

    Interesting Yahoo Pipe, keep working!

  • http://blog.bruno.locaweb.com.br/2008/01/14/links-for-2008-01-14/ rascunho » Blog Archive » links for 2008-01-14

    [...] mapping-google-spreadsheets (tags: spanring.eu 2008 mes0 dia14 at_tecp google google_spreadsheets geocoding google_maps blog_post) [...]

  • jon

    Great pipe. Is it possible to create a user input value to return a specific postcode or radius thereof?

  • http://spanring.eu/ Christian

    Well, I guess by adding webservices like geonames.org you could extend the geo-functionality of the pipe.

  • Josh

    I am curious how one could export the latitudes and longitudes back into a spreadsheet. I need that data for a project I am working on where I am trying to geocode a whole lot of addresses. Any suggestions?

  • http://spanring.eu/ Christian

    a while ago I used XSLT to transform kml into csv:

    http://spanring.eu/blog/2005/11/25/kml2csv/

  • vsv

    awesome work man!!! I was also wondering if its possible to upload data to map from a spreadsheet than a data feed, if so what changes need to be done in pipes. Also can we display more information(that may be available in the spreadsheet on a location) than just the address itself when we click on the marker icon on the map.

  • http://spanring.eu/ Christian

    There is a CSV module available in Pipes, so I bet it should be possible to find a solution using a table instead of a feed.

  • D

    I tried this and followed your steps but I am using addresses in this format:

    29021 CONSUELO PL, MISSION VIEJO, CA 92692

    I am not a developer – but before I ask my guys to do it I would like to know what needs to be edited in the Pipe to get this to work.

    Thanks,

    D

  • Christian

    That pipes uses the Yahoo! Maps geocoder, so as long as your address can be found in Yahoo! Maps you should be fine. I tried your address and it seems to work.

  • D

    Thanks for the quick reply! I didn’t bother reading the link about the geocoding limit at 5,000/day. My list was 5,100 records long. I cut it down to 50 records and it worked great!

    This is really cool and it will become very useful once we figure out how to get past the limit.

blog comments powered by Disqus