Fusion tables and maps: Post office map revisited

Reading Time:

[Tweet](https://twitter.com/share)
A few years ago I wrote a post about *[mapping post office closures using google maps and yahoo pipes](http://www.andydickinson.net/2008/02/29/maps-spreadsheets-yahoo-pipes-and-post-offices/)*.  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.

![](https://i1.wp.com/www.andydickinson.net/wp-content/uploads/2011/09/tables2.jpg?resize=500%2C332 "tables2")
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
![](https://i1.wp.com/www.andydickinson.net/wp-content/uploads/2011/09/Columns-500x341.jpg?resize=500%2C341 "Columns")
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!

![](https://i1.wp.com/www.andydickinson.net/wp-content/uploads/2011/09/map1.jpg?resize=381%2C413 "map1")
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
Sorted.
![](https://i2.wp.com/www.andydickinson.net/wp-content/uploads/2011/09/map3-500x275.jpg?resize=500%2C275 "map3")
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 C*onfigure 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](http://www.google.com/support/fusiontables/bin/answer.py?answer=185991). 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.
![](https://i0.wp.com/www.andydickinson.net/wp-content/uploads/2011/09/logo-500x305.jpg?resize=500%2C305 "logo")
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
![](https://i2.wp.com/www.andydickinson.net/wp-content/uploads/2011/09/share.jpg?resize=500%2C371 "share")
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.

Conclusions.

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.

Daily mail student media awards?

[Tweet](https://twitter.com/share)Yeah, wouldn’t happen. But should it? The always interesting Wannabehacks posted yesterday stating that The industry isn’t doing enough...

Do we need a journalism merit badge?

[Tweet](https://twitter.com/share)  So Ivan Lewis has suggested that journalists who ‘break the rules’ should be struck off. A move which, as FleetStreetBlues blogs:...

andydickinson.net   Never miss a story from andydickinson.net, get updates in your inbox.