•  
  • Fusion Tables and KML files: a primer

    Media Hack data journalism, new media tools Fusion Tables and KML files: a primer

    data journalism new media tools

    Fusion Tables and KML files: a primer

    Posted By Alastair Otter

    This is a simple guide to creating an interactive map using Google’s Fusion Tables. I’m doing this guide because it took me absolute ages to get my head around this and once I did I realised how unbelievably simple the process is. So it’s time to share.

    Naturally Fusion Tables can be as complicated as you want to make them but this example, a mapping of murder ratios in South Africa by province, is a relatively simple place to start.

    For this exercise you’ll need two files: a set of statistics and a KML file. The first file is a list of murders per 100,000 residents of each province in South Africa, which was pulled from the recent crime statistics released by the South African police. The second file is a KML file which contains the borders of all of the nine provinces of South Africa. You can download the sample files at the link above.

    Upload files

    The first step is to upload the KML file as a Fusion Table. Open Google Drive and click onĀ “Create” and then on “Fusion Table”. On the next screen you need to select “From this computer” to select the KML file you saved previously. Locate the file and click “Next”. This should bring up a screen like this:

    1

    Don’t worry about what it looks like. Click “Next” again, give the file a name and then click “Finish” which uploads the KML file as a Fusion Table. You should see something like this:

    2

    The yellow highlighting is Fusion Tables’ way of letting you know it thinks it can geocode those items. But in this case you don’t need to do anything like that because the important information we need is in the KML block to the right. That’s the bit that contains the shape information.

    To check if everything is on track click on the tab that says “Map of geometry”. That should show you a map of South Africa with each province marked and filled in with the default colours. Something like this:

    3

    The next step is to create another Fusion Table, this time with the statistics in it. As with the KML file, click on “Create” and then “Fusion Table” and upload the spreadsheet with the murder ratios into Fusion Tables. Locate the downloaded CSV file on your drive and upload it. The result should look something like this:

    4

    Again, the yellow highlighting suggest those cells could be geocoded but we don’t need to do that because we’re going to get the province information from the KML file.

    Merging statistics and maps

    Now that we have both the statistics file and the KML file upload to Google Drive we can put them together.

    Open the window with the Fusion Table with the spreadsheet data. Then click on “File” and then on “Merge”. That opens a window to find your KML file. You should be able to find it by looking for “SA Provinces”:

    Click on this to open it. This opens a window which gives you the opportunity to pick the two columns in your two files that will be used for the merge:

    5

    The column on the left is the province column in your spreadsheet file. The column on the right lists the columns from your KML file. The idea is to get the two to match. They probably won’t match line for line but so long as there are nine provinces on the left and nine on the right you should be fine. If you pick “ID” for the right-hand column your input box should look like:

    6

    Click “Next” and select the columns you want to merge. For simplicity select all of the columns for now. Click “Merge” and then “View Table” to see your new, combined table.

    Finally, click on the “Map of geometry” tab to see your map. It looks like the previous one but if you click on a province an information window pops up with the various murder ratios for each year.

    This map is still a little rough but if you mess around with the map styles and the window layout (both under tools) you can customise the map in all sorts of ways. Once you’re happy you can use the Tools->Publish menu to embed your map onto your site, something like this:

     

    Want to know more? Take a look at an example of the newsletter.
    We won't spam you or sell your details on to anyone else.

    Tagged ,

    Written by Alastair Otter

    Data visualisation & design, journalist, hacker.

    http://mediahack.co.za/alastair

    One thought on “Fusion Tables and KML files: a primer

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    The business of journalism is changing rapidly. Media Hack tracks these changes and delivers news, tips and insight directly to your inbox, every week.
    CLOSE [ X ]