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
Sorted.
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.

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.

11 thoughts on “Fusion tables and maps: Post office map revisited”

    1. Yep, no custom icons yet but you can use custom images in the bubble content. Just put a url to the image in a column and pick that in the content you add to the bubble.

      You need to make any fusion table ‘public’ for people to see it and the map should update as you update the data.

  1. Thank you I tried your first post with Yahoo pipes and the technology seems to have changed so that it did not geocode generating instead a list saying NULL. Thanks for your own spreadsheet that way I know its Yahoo pipes not me. I have really learned a lot from these two post so now I have to catch up on the blog up to February 2012. By that time I might have a website up. Thanks again here from freezing France, snowed in, semi-hibernating, so working remotely is really ideal.

Leave a Reply