Google Fusion tables: how to make (accurate) ward maps

Regular readers (ahem) may remember I wrote a post about how to draw your own Fusion table maps freehand. I remember thinking at the time that there must be a better way of doing this. It turned out there was, thanks to Paul Bradshaw and Scraperwiki.

I followed Paul’s walkthrough with Bristol as my guinea pig until I hit a snag uploading the CSV file to the Fusion table. The Fusion table didn’t automatically draw a map using the polygon ward data contained in the CSV file.

There is no map option here. Image: Rob Grant
There is no map option here. Image: Rob Grant

The workaround for this problem is to head back to MapIt, hosted by MySociety. Type in a Bristol post code, locate Bristol City Council and click on “covered by or overlapping with this area”

bristol kml 5
Image: Rob Grant

You get a list of all the wards of Bristol. Download each one as a KML file.

bristol kml 6
Image: Rob Grant

Repeat this step for all 35 wards in Bristol (yes, this is tedious).

Next, open a new Fusion table and import from this computer. Upload each KML file to the Fusion table (again, slightly tedious, but at least you can see the map taking shape if you get bored).

When it’s done, you will have a perfect ward map of your town:

Bristol kml7
Image: Rob Grant

I hear that section of the Bristol Channel contains a lot of floating voters…

Anyway, now I have a decent ward map of Bristol the possibilities for data journalism have just significantly expanded. Here’s one I made earlier: a map of the percentage of Bristol residents on a means-tested benefit.

bristol kml 8
Image: Rob Grant

We can see that the affluent areas around Stoke Bishop and Clifton have fewer people on means-tested benefits than Lawrence Hill and Hartcliffe.

The original data is here.

This took about an hour to do. It’s much easier than my previous post, which you may now discard from your bookmarks!


How to draw your own Fusion table maps

Google Fusion tables are an excellent way to map data. They give you a means to create intensity maps showing any statistics you care to mention that vary regionally, such as crime, unemployment or age. Google Maps recognises a type of file called a KML file. To Google KML files are just sets of coordinates that make up a shape, but to us they can mark out London from the rest of the UK, wards in Bristol or the entire world’s borders.

Simon Rogers (a City visiting lecturer) has a handy bank of KML files, but if you can’t find the perfect one that suits your needs the answer might be to create it yourself. This post builds on Dane Watkins’s post here. For this example, we’re going to create a KML file of wards in Bristol (you’ll need a Google account for this). Continue reading “How to draw your own Fusion table maps”

Google Fusion tables – putting data together

I was lucky enough to spend two weeks at the Guardian Datablog over Christmas, where I got to grips with Google Fusion tables.

The beauty of Fusion tables are that you can add multiple spreadsheets together to make a single table, which you can then download as a spreadsheet. This is particularly useful for compiling data that is released over time such as, say, detail on spending that is released monthly by councils or government departments. Continue reading “Google Fusion tables – putting data together”