Maps, spreadsheets, yahoo pipes and post offices

NOTE: I’ve updated this post to show how to do the same thing using Fusion tables rather than pipes 

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

Google doc publishNot 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.

 

Publsish the csv

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

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.

 

Loop and location

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

RSS from pipesWhen 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

23 thoughts on “Maps, spreadsheets, yahoo pipes and post offices”

  1. I don’t believe it Andy.This was going to be my idea for the digital for the Digital Newsroom presentation.Really I had already started working on this yesterday afternoon.

    I hope that I can still use it and not get accused of plagarism.

    That’s two things now and you have moved the predentation to my wife’s birthday and she is not very happy either

  2. Paul,

    Thanks, neat solution. My Regex is nowhere near up to hacking around like that but now i have an example I can work through. Cheers

  3. Thanks for a fantastic tutorial. I just used this to scan the results from this survey I’ve been running on Times Online using Google Spreadsheets – 1,000 results so far. http://business.timesonline.co.uk/tol/business/money/article3484700.ece

    Thing I love/hate about Google apps is that once you’ve got one thing working, you start yearning for the next bit of functionality. I want colour-coded tiny icons (or even postcode-shaped polygons), and I want to be able to do them as easily as I can do this.

    Oh well, time to read the API properly and learn javascript.

  4. Tom

    Glad it was of some use. Thanks for an example I can show the students to prove that I don’t just make this stuff up. :)

  5. I’ve followed the steps and the link works (very interesting and useful, thanks) but when others try to load the page using the google maps link, none of the data shows – any ideas?

  6. Hi andy, this is great but unfortunately I’ve been frustrated at the last hurdle – I can generate a pipe which spits out my list of data with the geo: lat and geo: lon associated with a title. I get the RSS feed URL but then when I paste that into the google maps search box it tells me it’s not a valid KML or KMZ file. Any thoughts? thanks in advance….

  7. Hi guys,

    how do i change the spreadsheet and my pipe to create different icons or colors?

    i would like to have informations in my kml-file, so that gmaps will show automaticly different icons.

    hope you can help me.

    Alex

  8. It’s serious http://fatydariqycu.de.tl young teen bbs forum not such a nice looking girl..but i reckon she could scrub up well with a minor makeover..fabulous body. great set of tits and awesome areola.nice frontal riding sequence. cum to body would of been better than shooting the sofa!! lol.great clip.

  9. Another year http://esygipyrot.de.tl gay models naked sasha grey is such a loser she gets on tyra banks and cries about how life is hard because she’s in porn shut the fuck up if you don’t like doing porn then quit don’t get on tv and act like we should feel sorry for you

Leave a Reply