Tuesday, 7 September 2010

Google fusion tables

In the past I’ve created a few data visualisations using Silverlight, but though for smaller datasets a HTML solution would be preferable. Now with the help of Google fusion tables I can create simple datasets with custom info windows without the need to write any code.

In the example above we’ve created a Google Map showing expenditure of Primary Care Trusts(PCT) across the England, when you click on a PCT it will display a custom tooltip.


To create this visualisation without any code I carried out the following 6 steps:

Step 1 – Grab the data

For this example we’ve taken data shared by the Guardian Data Blog as a Google Spreadsheet, this is downloaded as an excel spreadsheet, which I removed rows such as the spreadsheet title and “regional total” rows.

Step 2 – Add geo hinting

To help Google geo-code the data I took the PCT name e.g. “Barnsley PCT” and region “Yorkshire and the Humber” to create a location column e.g. “Barnsley,Yorkshire and the Humber,England”

Without the hinting Google will assume the location is in the USA.

Step 3 – Upload to Google

Now with the geocoding hints in place we can upload the data, label the columns (important for step 5) and tell Google which column to use for location.

Step 4 – Geocode data

To geocode the data simply select the “visualize” menu bar and select “map” this will geo-code the locations to lat/long points.

Step 5 – Configure info window and styles

Now we have a map with lots of small dots for each PCT, choose “Configure styles” to change your pin style, and “Configure info window” to personalise the info window. For my demo I chose the custom template based on a mixture of HTML and meta data for field place holders.


Step 6 – Share and embed

Finally click on the “Share” button and select Visibility options Unlisted or public to share the data. Once the data is public we can use “Get Embeddable link” to get the HTML to embed in your website / blog

   1: <iframe width="500px" height="300px" scrolling="no"  
   2: src="http://tables.googlelabs.com/embedviz?viz=MAP&q=select+col0%2Ccol1%2Ccol2%2Ccol3%2Ccol4%2Ccol5%2Ccol6%2Ccol7+from+247676+&h=false&lat=51.78483389373529&lng=-1.197509765625&z=8&t=1&l=col3">
   4: </iframe>

Note: We can also export the data to KML, which could then be used in your preferred mapping solution, be it Bing Maps for Silverlight, Google Earth etc..