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.

Creating dynamic charts using Google docs

I recently posted about how to add a quick form using Google docs. Kasper Sorenson left a comment asking if it was possible to update an image dynamically

Sometime ago, I wanted to create a dynamic chart based on values that were being entered into a database (in this case we used Google Spreadsheets). Basically if I was to export the chart as a static image, it would be outdated after an hour.

The chart created in the spreadsheet can be embedded instead of exported as an image so in principle you could update it. But I thought I would give it a try.

So, here is a form made using Google docs forms.

That drives a spreadsheet that drives an image:

Update: Well, I hope there is an image there but there seems to be this weird thing were you can’t see the image if you aren’t logged in to Google docs. Odd!. It seems you need to be quite specific about how you share the sheet before you then share the image. But you can also add a chart as a gadget and this seems less temperamental. Down side is that this is also a little unpredictable for updating. Not ideal but still, it’s free.

Add your data to the form and refresh the page and you should see the graph change. Okay the page refresh is not ideal but hey, it’s free.

How does it work

Selecting the whole column means you pick up new entries
Selecting the whole column means you pick up new entries

It works by picking the whole of the results columns the graph rather than specific data cells. With very little info in it it looks pretty sparse but as the results come in it will change. So not a perfect result but not bad.

When to use it
It might be useful if you were tracking fuel prices over time. Add the data and map the timestamp against the price. Add a postcode field the form and you could also output it as a map.