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
Using a custom icon on the map
- 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.