Grrr… How to create a Fusion Table


Earlier this week, I came across a Guardian article about where schools were on strike – it included a Google map that had each Local Authority highlighted to show what percentage of schools are on strike.  Now, I have made my own map in Google before, and carefully drawn around each territory to show where we have School Representatives, but it is painstaking, and definitely not as professional as the Guardian one.  It made me wonder how they managed to create it.  If you want to skip to the end, then here is what I made!

The answer was in Google Fusion Tables – and after a couple of minutes of tinkering, I was hooked.  The possibilities here are amazing. But also quite a bit of learning to do – and as with everything new, lots went wrong.  Here is how I managed to create my Fusion Table showing all of the different areas of the UK, and where Scholastic have school representatives:

First.

Golden rule: don’t build your data table in the Fusion Table itself – it is a very long winded and convoluted way.  Either build it in Excel or Google Spreadsheets, as you can import them when you are finished.

Get yourself some location data.  Now it depends which type of map you want to show. If I had wanted to show points in the map, then a place name, or postcode would have done and I could have skipped happily onto the next step. But I wanted to show each area as a layer on top of the map.  This is a lot more complicated.

I did plenty of Google searching to find the right data.  In the end, I found some from Nearby.Org, which isn’t perfect, but I can refine over time. To draw polygons over the right data you need a string of coordinates that show the edge of the area you want to cover.  Now, most coordinates come looking like this:

54.91567645	-1.743341174
54.91925706	-1.724644054
54.91056256	-1.718651474

Unfortunately, the lovely way that Google works, you need to reverse these codes, so they end up looking more like this:
-1.74334117369273,54.9156764522577
-1.72464405391863,54.9192570581909
-1.7186514736122,54.9105625612723

Second

Once you have all of the location data (and in my case I have 164 different lines in my database, each with up to 4000 coordinates in) – you can start to build up your spreadsheet.  I wanted to show the Rep name, Area name, phone number, email, Area number (more on this later) and finally the location.  Here is an example of mine.

All of the columns are self-explanatory, apart from location.  In the location column, you need to put in the KML data for the areas you want to show.  This can be done with the following code:

<Polygon> <outerBoundaryIs><coordinates> -2.51998453948394,51.6881171980821
-2.51981095720608,51.6881179666459  </coordinates> </outerBoundaryIs> </Polygon>

Now to do this for all areas would be very time consuming, had it not been for the wonderful concatenate function in Excel.  By using this, it saved me hours of coding work.

Once you have this in your table, you can then import it into Fusion Tables, using the import function.

Third

Once you have imported it, you can click on ‘visualise’ which allows you to show the data you have in your table as a map.  As long as you have included the KML data here, you should have no problems.

All of the areas are red to start with, and you then have to work out how to change each area.  If you click on Configure Map Styles, then on Polygons, fill colour, buckets, you can then set the colour to be what you want.  Now as I had numbered my areas, I was able to set the number of ‘buckets’ to 100, and then I could say that bucket 1, should be green, 2 blue etc. etc.

Once you have done this, you have pretty much finished, you can grab the embed code and put that into a website or blog post.  There is, however much more you can do. You can merge tables to compare and contrast different sorts of data.  The visualisations are much more than maps, you can do timelines, heat maps, charts, storylines…the list is longer, and the possibilities pretty much endless.

Have you used Fusion Tables before? What for, and how have you found them?  I can’t wait to get stuck into more complex, but funkier data!
Advertisements

One thought on “Grrr… How to create a Fusion Table

  1. Hello,

    Great blog. Would it be possible to get a copy of your location data please.

    I am stuggling to get what I need.

    Thanks,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s