Over the last few weeks I have been teaching second year students a number of digital tools. This week it was Google maps and I thought I would share the process I went through to create a map/mash up example to show them.

So here is the map.

View Larger Map

Not too interesting I know. It’s a map of post office closures in areas of the UK shown on a website called public servant daily last October. I just did a search around for the list based on a quick think about a geographically relevant story and this was the first site that popped up with data that looked copy and pasteable.

What I figured I needed was a list of postcodes that I could some how convert in to a feed that Google maps would understand. Now you can point the spreadsheet directly at a map via Google maps spreadsheet importer thingy but I didn’t want to do a lot of hand converting post codes in to lat/long information.  There are some tools that will do it, like this one, but they all seem to need some kind of preformatting. So I needed something that would do that ‘more’ automatically. That’s where yahoo pipes came in.

A quick search around pipes found me several examples of postcode conversion pipes to play with.

Setting up a spreadsheet

So to start I cut and paste the data in to a Google spreadsheet. If you look at the sheet you can see it is actually a bit of a mess in places but it’s raw data. Truth be told I went through excel to do some column splitting and then combining to get the postcode out of the address.

Next step was to publish the spreadsheet so I could use the data. You can publish in a number of formats but the pipes examples I had seen used CSV (comma separated values) and that seemed easier than trying to deliver the RSS feed.

publish your spreadsheet as a csv file

The publish option gives you a URL for the CSV feed. By setting the publish options to automatically update I knew it would be the most up to date.

Yahoo pipes

The postcode to lat/long pipe

Now that I had the feed I needed to get it in to pipes. So the first block is the Fetch CSV (found in Sources) option. This essentially gets the csv feed published from Google docs and spits it out in a list based on the columns in the sheet.

Then a loop block (found in operators) takes each item in the spreadsheet and runs a Location builder operation. To combine the two, you add the loop operator and then drop the Location Builder in to the space.

Drop the location builder pipe on the loop

The location builder “converts a description of a place into geographical data.” You can see that is set to use column 4 as the location- that’s the postcode – so we can generate the lat and long information that google needs to plot the points on a map.

The output of that loop are packaged up in the item.loop.locationbuilder results. So the last thing to do is make sure that the results are in a format that Google maps can understand. As far as I understand it Google plays with the Geocode RSS format and fitting with that convention seemed to be the norm on other pipes. So the next box takes each element and renames it in to something more Geocode friendly.

I’ve used column 1, the post office name and column 5, the post office address (or a truncated version) as a description. The lat and long results from the location builder are then renamed to match the requirement for geocode RSS.

The result of that is then plugged in to the pipe output.

Get a feed

When you save the pipe you can then run it and it spits out a fairly plain list. You can subscribe to the list of results in a number of ways including the RSS with all the geocode content. If you right-click over the link you can copy the url.

If you take that link over to Google maps and paste the RSS url in to the maps search box. Hey presto! the results are plotted.

You can then click the Link to this page option and gather the link data to embed on your site. Result!

Conclusion

This may be old hat to some. New to others. What pleased me was how easy this was made by the way pipes allows you to share and edit other peoples work. It feeds my approach of ‘oh, that’s good you could use that to do this..” hacking.

I’m also thinking that the new Google forms thing will make interesting addition to this mix. All it needs is a bit more data and more flexible pipe to make for richer content and things could get very mapalicious. Hope there is something there to play with.

Raw material

• The original page of data
  
• The Google spreadsheet
• The Yahoo Pipe
• The google map