Sunday, November 16, 2008

Dynamic Geo-Tagged Maps

Here's a cool thing you can do for free. On the left is a map of our enrolled students, tagged with credit hours. I made a similar one showing students who didn't return. The comparison shows a fairly obvious geographic concentration of attrition. More on that later. This post is about how to create one of these maps.

I found a cool demo on reddit on sending data through yahoo.pipes to google maps. It all sounded very complicated, but I thought of an interesting application one afternoon between committee meetings and tried it out. About six hours later at home I had it debugged and working. Part of the problem was the demo didn't work for me, and I had to rewrite bits. Here's how to do it.

First, you'll need an account with Yahoo. Then sign in on their überkuhl pipes constructor site This utility will let you take data from one source and "pipe" it to another, with all kinds of options for filtering along the way. In this case what we want to to is take some data of interest and geo-tag it.

First create a cgi application (or get a friendly web programmer to do so) that takes some data of interest and produces a delimited text file. Here's a sample of mine:
Aiken,SC|2 ( 71 credits avg)
Alpharetta,GA|2 ( 45 credits avg)
Altoona,PA|2 (149 credits avg)
Amelia court house,VA|1 ( 40 credits avg)
Anniston,AL|3 (156 credits avg)
The format is (city, state|detail information). You could include street addresses if you want. For the detail information I used number of students and average credit hours, but you could use GPA or anything you like. I used the pipe character "|" as the delimiter because the addresses have commas in them. The first row doesn't really need to be there--it just names the columns. If you don't know how to write CGI applications based on live data, you can create a static text file and slap it on the web somewhere, and that will work too. So you could take data from a spreadsheet and save it as a delimited text file, put in on your web space and proceed to the next step.

Second create your pipe to look like the one below. You'll need to sort through the voluminous menu of widgets to find the ones you want, but this is what it should look like.

The URL at the top points to your CGI application or the text file you created. Notice that I have it set to ignore my header row, and provided it manually in the widget. This is a product of messing around debugging the thing :-). Note that the way I'm accomplishing the geo-tagging is a bit different from the way it was done on the original demo above. I couldn't get that one to work properly.

Third run your pipe. You should get a kind of ugly looking map like the one below.

We can improve this by sending the data to google maps. Find the options menu top right of the map, and use it to select the map format KML (pictured on the left) Don't click on it, but rather right-click to copy the URL this points to.

This URL is now a long string that will request KML information from yahoo pipes, which will then grab it from your file/cgi, geo-tag it, and add the XML markup necessary for google maps to understand it. Very cool.

Finally open up and paste the URL you just saved into the "Search Maps" box. If everything works, you should get something that resembles the map at the top of this post. On the top right of the map is a "Link" hyperlink which you can right-click on and capture a hyperlink to this map. This you can put in a web page or email to someone, so they can pull the map up too.

Over the weekend I created a little application that starts with a web form to collect contact information (name, address, email, etc), stores it in a database, and then redirects the user to a map with all the existing directory information displayed. I'm using it as a directory for family and friends. It's much better than just a list of names and contact information because of the visual nature of actually seeing where everyone lives. When I have time I'll create a generic version anyone can use. In the meantime, if anyone wants the perl code for it, leave a comment here.


  1. If you can find (or publish) data in an HTML table, you can use function in Google spreadsheets to "screenscrape" the data from the table into a spreadsheet.

    Once it's there, you can use the spreadsheet charting tools to display the data in the original HTML table, or take it out of the spreadsheet and into a yahoo pipe:

    The process is described here:
    Data Scraping Wikipedia with Google Spreadsheets

    You can also use Google spreadsheets as front end to XML web services (no programming required :-) - for example, Viewing Campaign Finance Data In a Google Spreadsheet via the New York Times Campaign Data API or Calling Amazon Associates/Ecommerce Web Services from a Google Spreadsheet

  2. Great point! You can get data onto the web that way quickly without having access to an in-house web-server.