My most recent post was a map of recreational cycling in the UK.
The map was created using Google Fusion Tables, a experimental app on their Drive platform. Here’s how:
1. Clean the Data
Google fusion tables needs to have all its column headings in the a specified row (usually the top) and then it treats all other cells as data points. This means that we have to remove all the pesky information (highlighted in the image) that appears above and below the data. But remember, always keep a copy of this info, either by saving the unaltered original separably or cutting and pasting the tidbits into another worksheet.
2. Upload your Data into Fusion Tables
Next, select ‘create’ on your Drive and click on’ Fusion Tables’. What follows is a fairly straight forward process where you will be asked to select your data from your computer (or from any Google spreadsheets you already own) and then specify which row your heading titles are in. Click ‘create’ and you should be left with something which looks like this:Now don’t be alarmed by the yellow highlighted columns. This is just Fusion Tables guessing your location data. In this case regions and local authorities. However, Fusion Tables struggles with accurately mapping UK regions so we need to do something a bit extra to help it map it correctly.
3. Get Appropriate Location Data
For this we need to head to the ONS Geoportal Site, more specifically their download resources and find the appropriate shape files. The resouces catalogue has a pretty exhaustive list to choose from. If stuck I recommend just googling one of your locations and you should be able to find out quickly which type of data you need. Now in this example I want to map local authority districts so we just need to download the file highlighted below.You more than welcome to go for the ‘Full Extent’ version but the ‘Generalised Clipped’ are more then detailed enough. A zip file will be downloaded containing a whole host of files that you’re computer cannot open. Don’t worry, leave the zip folder as is.
4. Creating a KML File
The zip folder and its contents are pretty useless for us in their current form and will need to be converted into another type of location file, a .kml. Its incredibly easy to do this thanks to a tool developed by mapsdata.co.uk. Head to their online file converter page and simply drag your zip file into the little box (pictured below).
Wait a while and a .kml file will be automatically downloaded to your computer. Now repeat step 2, except this time uploading the .kml file instead of a spreadsheet and you should be left with this:Now I’ve highlighted the third column as these sort of numbers (the LA codes) appear in our cycling data and correspond to the local authorities. The ‘geometry’ column contains all the geodata that maps the boundaries.
5. Merging the Tables
Now to get that geometry data together with our cycling data we need to merge our two tables. On the non-location data, go to File -> Merge, you will then be prompted to select which fusion tables to merge, select your location table. You will then have to match up the right columns, in this case it is the ‘LA Code’ in our cycling data and ‘LAD13CD’ in our geometry table.Finally, you will be prompted to choose which columns which which to keep from your two tables in your merge. Try to get rid of columns you know you won’t need, when in doubt, just keep it in.
6. Colour Coding
Now at this point your map probably looks like this:
Whilst a good sign that the KMl works and has been translated into accurate borders. At the moment, there is no representation of the data. To go about changing this first click on ‘change feature styles’ on the left toolbar. On the window that pops up head to polygons and select ‘Fill Color’, next select the ‘buckets’ tab. Now select which variable in your data you wish to represent on your map, in my case I choose; the percentage of people who cycled for recreational reasons at least once a month. After this we need to select how many colours we want to use, aim for as few colours as possible but enough to demonstrate differences between regions. Experiment a little. Fusion tables can select data boundaries between colour buckets for you or you can determine them yourself, again experiment and see what works best but try and keep the groups similar sizes.
The automatic colours fusion tables assigns you are usually not very good for showing increases and decreases in your range. Fortunately, colorbrewer is a fantastic resource which you can use to get a set of colours to help represent your data.Simply choose the number of data classes you need and what colours you think would represent your data well. Then copy and paste the hex codes into the fusion tables colour pallet. Click save.
Now that you map looks something like this:
You may notice that when you click each zone that you get an info code which probably is very cluttered. To de-clutter, select ‘change info window’ in the aforementioned left toolbar. For a straightforward cleanup, simple use the automatic tab and deselct that parts of your dataset you do not want to appear on the info card. If you have a decent grasp of HTML, the custom code is useful for adding some of your own language around the data. Hopefully once you’ve cleaned up you’ll get nice, straightforward cards like this:
Click the tab of your map and bring the cursor down to ‘Publish’, click that bugger.
Congratulations! You’ve just done your first fusion table map!