Fusion tables and maps: Post office map revisited

A few years ago I wrote a post about mapping post office closures using google maps and yahoo pipes.  I used that combination because of an issue with google maps and post codes. I needed to convert the posts codes in to a lat, long format – yahoo pipes did that job.

I noted that @patrickolszo mentioned that post on twitter today and I realised it was a bit long in the tooth and, of course, things have moved on considerably.  So here is how I’d do that now using googledocs and google fusion tables. 

I’ll use the data from the original map which came from publicservice.co.uk which is not current but at least it keeps the link across the posts!

Importing the data

In the previous example I noted that I did a fair bit of manipulation on the information splitting the text up to make columns I could use.

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.

Now I guess you could explore tools like Google Refine to help with that kind of thing. But I’ve shared a version of the spreadsheet on Google docs which you will need to save in to your own google docs account. Once you have the spreadsheet, you can import it in to a fusion table. In google docs

  • Click Create new and select Table
  • Click Google Spreadsheets from the Import new table options
  • Find your saved version of the post office spreadsheet and click Select

A version of the spreadsheet will be loaded. At this point you can make changes to the column headers – I changed Address three to complete address for example. The last screen prompts you to add contextual information. It’s really worth doing this.

Always add context to your data when given the chance

When its loaded in the data is not that different from the spreadsheet. Fusion tables splits it across a number of pages, 100 rows at a time but that’s all that’s really obvious. I’m not going to go in to the functionality etc. of fusion tables here though. I’ll just do the basics to get us to the map.

Mapping the data

The first thing we need to do is tell fusion tables what data we are going to map each row.

  • Select Edit > Modify columns
  • Select the Post Code column and change the Type to Location
  • Select File > Geocode and select Post Code from the drop down
  • Click Geocode
Changing the post code column type to location

Fusion tables will then generate location data based on the post codes in the table. Much easier than the Yahoo pipes solution! You can also try geocoding more generic information. You could, for example, set the Address two column type to location as well and geocode that. But if you have more than one post office in the same town that might cause problems.

Generating the map

No you have the location information

  • Select Visualize > Map

Fusion tables will take the data and put it on the map. You’ll need to zoom in and move around to see the points in detail. Clicking on a point will open a standard speech bubble with more data. But overall it’s looking pretty good. Well, almost. One of our post offices seems to have moved from Sevenoakes to Northern France!

The basic map but with a problem

Checking the data, it seems that the post code is TN13 IHZ when it should be TN13 1HZ. It isn’t the only one but I’ll just correct that postcode for now :

  • Click Visualize > Table to switch back to table view
  • Click the Address two header
  • Select Sort Desc
  • Find Sevenoaks and change the appropriate Post Code Entry
  • Click Visualize > Map to go to the map view
The updated map
Editing the map
Changing the data on the map is simply a case of updating the table. But you can also edit the content of the bubble using the Configure info window link and even the marker by using the Configure styles link.
One nice trick here is to add an extra column to your spreadsheet and add a custom icon to use on your map. In the example below I added a column to my spreadsheet called logo. I added ‘post_office’ to each entry. When I import that in to Fusion tables I can use the Configure styles link to choose the logo column as the icon.
Using a custom icon on the map
Sharing the map.
When you’re happy with the map you can share it with others in a similar way to other google documents.
  • Click on the share button in the top-right of the page
  • Change the settings to suit
The share settings in fusion tables

Once that is done you’re good to go. Here’s an embedded version of the map.

Note: The embed works using an iframe so if you’re using WordPress.com, sorry, you’re out of luck when it comes to adding the map to a post.


The process of mapping is obviously a lot simpler with something like Fusion tables and I think that underlines just how far we’ve moved. That’s not just in terms of the underlying technology which, when you think about it, is pretty amazing. It also shows just how far and how quickly data, geocoded or otherwise, has become mainstream.

Anyway, it’s a simple little example and there is a tonne more that you can do with fusion tables but I hope it’s a useful insight.

Cool google and wikipedia mashups

A map of population centers generated using wikipedia

I do like a nice mashup and they are not just the preserve of techies as an ever increasing range of tools means the humble journo can mash with the best of them.

In that spirit I wanted to share a great post by Tony Hurst where he explains how you can  ‘Data scrape’ Wikipedia with Google Spreadsheets to get a map like the one above.

[W]e have scraped some data from a wikipedia page into a Google spreadsheet using the =importHTML formula, published a handful of rows from the table as CSV, consumed the CSV in a Yahoo pipe and created a geocoded KML feed from it, and then displayed it in a YahooGoogle map.

As Tony says ‘Kewel :-)’

It may sound arcane but don’t be put off by the seemingly techy.  Tony provides a reallu usable tutorial and the key thing is to experiment with data that’s relevent to you.

Go on, release the inner geek and have a play

Turning dog poo in to stories

I’ve been spending a lot of time doing prep for teaching and training that I’m doing at the moment. So expect the slow appearance of a backlog of posts on video and other related issues. But I thought I would share something that has been in my radar for a few days.

I’ve been doing a lot of talking (shocking for me I know) about using the web for research – journalism toolbox stuff. And one of the things I have been stressing is that the web will very rarely just ‘give’ you a story. It will give you lots of data and information but the story is in the way you, as the journalist, put the things together. The phrase that I heard last week that best summed that up journalists are sense makers. Phil Trippenbach has a nice post on this so I won’t labour the point.

But whilst I was browsing for resources and examples to show my students and delegates I came across a site that made me wonder if I had to re-think that position – fixmystreet.co.uk

Fixmystreet.co.uk is a site by the fantastic Mysociety group who specialize in socially aware, achingly web2.0 sites. Top stuff on a number of levels and their other sites are worth a visit. Anyway, here is how they describe fixmystreet:

A site where people can report, view, or discuss local problems like graffiti, fly tipping, broken paving slabs, or street lighting.

Nearly 25,000 problems have now been reported across the UK, with our users following up many thousands with updates, news and notifications that problems have been fixed

Here’s an example of an ongoing problem with litter.

Fixmystreet.co.uk - tracking local problems
Fixmystreet.co.uk - tracking local problems

You can also sign up for an RSS feed or email alerts for a location. Told you it was brilliant.

So I’m showing my students the site today as we discussed ways that you can get a handle on a patch. They enjoyed it, not least because it offers, what must be, the most accurate geographic mapping of poo that I have yet to see on the web. With pictures! Anything scatological is a hit with students it seem.

I made the point that it shouldn’t replace physically getting out on the patch but it could provide some insight and a conversation opener when wandering around. But it wouldn’t throw up a story. Then we came across this entry.

Is this just about dog poo?
Is this just about dog poo?

Take a look and ask yourself if there is a story in that or not and if it’s a story about dog poo.

Locating the meaning

In terms of the way you would work a beat to get a story to pitch to an editor this site serves up a hell of a lot in just a few lines of comment. Perhaps it’s the fact that the story is located that adds the context you need. Maybe it did take my eyes on the story to make the connection. But one thing is for sure, fixmystreet proves that locally focused geo-mashups work.

So if the embryonic geotagging of your content or the occasional attempts at mapping this kind of thing have fallen of the radar or been dismissed as gimmicks, maybe it’s worth looking again.

Take more of a healthy interest in your audiences poo.

UPDATE:Because I am that plugged in at the moment I didn’t see this great interview with one of My society’s developers Francis Irving on Journalism.co.uk/ (thanks the Alex Lockward for the nudge)

Times online and google maps

Times online map

I got a very nice mention on the Times website today. The Times’ Communities editor Tom Whitwell (thanks Tom) has added to a survey they did for the upcoming budget with a “now legally-required Google maps mashup

The map was built using Yahoo pipes and google maps, based on a post of mine with a little google forms magic built in. (Later: Just to clarify, that’s about the sum of my advice here. My boss just asked me when I started working with the Times!)

I mentioned in my post that, combined with Google forms, turning surveys in to ‘geotagged’ surveys is pretty darn easy.

As Journalism.co.uk reports

“The Times has a long history of commissioning opinion polls,” wrote Tom Whitwell, Communities Editor, Times Online, about the origin of the survey.

“These are scientifically rigorous, using a carefully selected panel of maybe 1,000 people. At Times Online, we can do things very differently. We can throw out questions to our readers and capture their mood quickly, cheaply and easily.

He admits the online poll isn’t that rigorous but it offers a cheap and easy way to add depth to your content.

Great stuff and it gives me a great example to show the students.