Content

Data visualisation with Google Fusion Tables

by on 12th May 2014

Fusion Tables is a free data visualisation web app provided by Google which you can use to gather, visualise and share data tables. It’s been around since about 2009, and although it’s perhaps not as widely used as other applications like Google Docs, it is still a really powerful tool that can leverage some great results. The sky is definitely the limit when it comes to the kind of things you can produce!

Day by day I am always looking to find things that can make pieces of content more interesting, more interactive, and more useful. Whether that is working with a client to produce a new story with an edge, or trying to find ways to make my own personal blog more engaging and making my readers want to come back for more.

I thought it would be a good idea to produce a small guide on how you could go about working Google Fusion Tables into your content to really give it an edge, and to finally show you one of the ways I’m using Fusion Tables for my personal site.

I primarily use Fusion Tables to produce interactive maps. I am able to:

  • Upload data sets
  • Manipulate my data set and create various types of map (from a pushpin style, to an intensity map)
  • I can then embed my creations onto my website, combine it with different types of media and share with the world!

Making a chloropleth/intensity map

Intensity maps can be really handy, and are a really visual way of seeing differences in certain groups of data.

When using fusion tables you have the ability to work with different types of Geodata. One of those types of geodata is called KML – Keyhole Markup Language. KML is an XML-based language that allows you to manipulate the display of geographical data.

For this example, I will be using 2 files. The first one is a KML file using geographical data on US states that I got from Google Fusion Tables. I will also be using an excel spreadsheet I put together which contains data on the number of births in the USA by state in 2010. I got the data from kff.org.

To begin with I import my KML file into Google Fusion Tables.

KML file import

I give it a relevant name, and then edit some of the data so that I have a fusion table like the one below containing the 50 US states and some dependent territories:

Table preview
 
  • Code – Represents the abbreviation of each US state
  • Name – The name of each state in full
  • Geometry – The piece of KML that represents the geographical location of each of these states.

The next step is to import my excel spreadsheet which contains information on the number of births in each of these 54 locations.

no of births

I then merged my KML file with my excel file:

merge`

To do so, I open up my fusion table with the KML data(table called US STATES BOUNDARIES), and select “merge” from the drop down menu.

table select

I then select my table with the information on birth rates from excel (table called US BIRTH RATE)

I choose the “Code” column to use for matching data across the two tables

matching data
matching data

I also deselect the second instance of name when choosing to merge columns as I don’t want two instances of the same thing.

Giving me a table that looks like this:

finished table

You will also notice that there is a tab called “geometry” that sits along the top of the table.

map of geometry

When clicked it displays what is shown above. You can also see that when a particular state is clicked an info window pops up that displays the country code, name of the state, and the number of births.

The first thing I am going to do is to change what the info window displays when clicked. To do this, go to Tools > Change Map > Change Info Window

info window

You have 2 choices here, automatic or custom. The Automatic option shows the data that we have from all of our columns. You can select or deselect the box next to each column to decide whether or not it will display in the info window.

For this example click on the custom tab, so that we can add some style to our info window:

  • To fetch the data from each column you need to put the column name in curly braces i.e {Code}
  • Inline CSS is supported in the info window template.

<div class="googft-info-window style" style="font-family:Verdana; width: 100px; height: 100px;">

<h3 style="color:grey"> <center>{name}</h3></center>

<h3 style="color:grey"> <center>{Number of Births}</h3></center>

</div>

The code above is what I used to style my info window. I applied a simple style, giving it a fixed height and width and applied a font family. I also gave my columns a header styling – it now looks like this:

info window styling

Next, the styling that has been applied to our map needs to be changed.

Go to Tools > Change Map > Change Feature Styles

Then, In the pop up window go to Polygons > Fill Colour > Buckets

Select the number of buckets you want to divide the data up into, and select the column of data that you will be using to fill the buckets (in our case it would be number of births), and then hit “use this range”

You can then divide the range up across the buckets, and select colours for each of the ranges:

buckets
map legend

You can also apply a legend to your map by going to Tools > Change Map > Change Feature Styles > Legend – make sure the “show polygon fill legend” box is ticked.

Now we have our map we obviously want to show it off!

To publish a map go to Tools > Publish, firstly make sure your map is set to public, and then grab the HTML code to embed into a website. You can have a play around with mine below!

Using Fusion Tables on My Personal Blog

Outside of work one of my hobbies is writing and maintaining my personal blog. I run a predominantly beauty focused blog over at sophee.co.uk. There are loads of beauty blogs out there, and sometimes you can feel a bit lost in a big sea of bloggers. I wanted to try and produce something that would set me apart from others and make my blog stand out a bit more, whilst still keeping the content themed around beauty, fashion and so on.

I originally delved into the world of Fusion Tables to produce a “Beauty Hotspots” guide for my blog, and it was once I got playing around that I realised that not only could I use the application for my blog, I could also use it to create some really interesting graphical representations of data that could be tied into my day to day work.

My hotspots guide is made up of one table:

hotspots guide

I began by populating my table with data. I gathered a list of department stores, beauty boutiques, fashion stores and more all located in central London.

I paired each of the shops with an image of the brand, and then went on to gather the address for each business, and the contact telephone number. I also made sure that I placed each shop in a category. I.e “beauty”, “fashion”, “shopping centre” and so on.

Finally I gave each shop an “icon”(which will be explained later), and also wrote a comment if I had chosen it as one of my “Top 10 stores”.

Geocoding Location Data

In order to place my shops on a map, their locations had to be geocoded. As you can see I have a column in the table named “Address Concat” which contains the address combined with a postcode.
*side note: when putting together my chloropleth map I did not need to geocode locations as KML is already mappable.

To geocode my location data I made sure my address column had a location type – Click on the address columns arrow > change > type >location

add map

I then selected the red box from the tabs along the top of my fusion table and hit “add map”

geocoding

My points began to be geocoded, and when finished the map looked like this:

finished geocoding

My next step was to sort out my info windows. As I have already gone into detail about how to set them up I won’t repeat myself, however this is the code I used:

<div class="googft-info-window"
style="font-family:Verdana; width: 250px; height: 320px; overflow-y: auto;">

<h2 style="color:grey"><center>{Shop}</h2></center>
<center><img src='{View}' height='150' width='150' style='vertical-align: top'/></center><br>
<center>Phone: {Phone}</center><br>
<center>Address: {Address Concat}</center><br>
<center>{Comment}</center>
</div>

As you can see I applied a font family to the window and gave it a fixed height and width, in addition to this I applied the overflow-y property to overflow the window’s content area (e.g. If the window contained a comment, it would need to be possible to scroll to read it)

I also gave the image that is displayed for each brand a fixed height and width – the rest is fairly self-explanatory.

The next step was to give each point on the map a custom marker – this is where the “icon” column comes in to play.

I had to allocate a marker to each point on the map, using the Map marker or icon names fusion table from Google.

For example – All of the department stores were given the marker called “shopping”.

Once I had given each point a marker in my fusion table I headed over to the map itself: Tools > Change Map > Change Feature Styles > Points > Marker Icon > Column > and then selected the column that contained all of my icon names. – i.e “icon” – and Voila! I published my map and embedded it on my site.

If you use fusion tables yourself or have any questions I’d love to know! – drop me a line on Twitter @sopheewebb

Join the Inner Circle

Stay one step ahead of the competition by staying with us. Join the Inner Circle to receive a monthly update of Organic Digital marketing resources, blogs and industry news.

Get insights straight to your inbox

Stay one step ahead of the competition with our monthly Inner Circle email full of resources, industry developments and opinions from around the web.