Some tips for charts in Fusion Tables info windows

I've recently been working with some mortgage lending data from banks in Great Britain to produce a new mortgage lending maps site. Once again, I've used Google Fusion Tables to map the data because it's relatively quick and easy - further information can be found here. What's not so easy is getting the info windows to do exactly what you want, particularly when you want to include charts of the type shown below. In this post, I explain a little more about how you can get the info window to display such a chart, what can go wrong when trying to do so and what the code underlying code looks like.

The chart shown above is what you'll see when you click on any polygon in my mortgage lending map website. It takes the data from the underlying Fusion Table and provides a unique info window and chart for each postcode sector - in the case of the above it is for the Cardiff postcode sector of CF24 4, which as you can see had more than £117 million of outstanding mortgage debt owed by 839 households at the end of June 2013. The default info windows created in Fusion Table maps simply contain a number of default data records from columns from the underlying table. With this data, I was keen to show a visual comparison of lending mix in each area, and I wanted to do this using a horizontal bar chart. I'd done info window charts before (e.g. in my Deprivation in Scotland website) but these were line graphs showing change over time.

There is some general help on putting charts in info windows from Google, and this is a very good place to start, and you can also find a lot of explanation for what the different bits of chart code mean in Google's Charts Gallery, or in the Chart Feature List, but to help anyone who might be trying something similar to what I've produced, I thought I would provide an annotated code example. I also want to provide some general troubleshooting advice. Here's what the code looks like inside the Fusion Tables interface:

And here is a Word document with comments added explaining what each bit of code does. You'll notice that it's a bit messy but it produces a very nice looking chart. One thing that I noticed about all this is that if you want the numerical data to appear in the info window with comma separators - as above, for the £117 million figure - then it appears to disappear from the chart. This is what happened to me anyway. My advice would be to keep the number format for your data as 'none' in the Fusion Table field options. My other advice would be to remember to save a good version of your code but most of all to experiment with different things and then share the results.

I hope someone finds this useful! I know I would have before I got into this. 

Deprivation in your area - search and zoom

After making a new website when the Scottish Index of Multiple Deprivation 2012 was released, I've been looking at some of my old English IMD web pages and tools and so have decided - in anticipation of the next update to the IMD - to add a 'search and zoom' tool so that anyone who is interested can find out what deprivation is like in their area - or any other area of England you are interested in. Like my previous versions of this kind of thing it's based on Google Maps but in the version seen in the screenshot below you can simply enter a place, neighbourhood, postcode or address (or even an organisation, like Centre for Cities) into the search box and it will go straight there after clicking 'search'. Click the image or the link below to use the tool.

IMD Search and Zoom

As usual with these kinds of maps, if you click on an area the pop-up will give you more information. The next update of the IMD was to have been in 2013 but I'm not sure if that is still going to happen. However, I've updated this tool just in case so that I can add in the next update whenever it becomes available. The Scottish version of this tool is on this page, but I have not yet done the same for Wales or Northern Ireland.

Google Fusion Tables Info Windows

A short post today related to info windows for Google Fusion Tables maps. In case you didn't know, an info window is what pops up when you click on an area of a Google map - as shown below. I've blogged about this in the past and today am sharing some more tips in relation to how to get a Google Street View snapshot of an area to appear in the info window. I just added this in to my new SIMD website.

This page tells you how to add a Street View snapshot and there isn't really much to it. You just need to make sure you have an API console key (baffled? see this for how to get one) which is just a key code that you need to make it work.  All you need to get one is a Google account. The other thing you'll need is two columns in your table specifying a latitude and longitude. I do this in ArcGIS before importing the data into Fusion Tables. Once you've done this you can add in the Street View code to the Fusion Tables info window layout (via Tools, Change info window layout... as below) and then add in the code via the Custom tab.

I've added in an Employment page to my new SIMD site where this is all put into practice - I have an info window chart, some data and a Google Street View snapshot in it, plus some text. Here's the code that makes it work - I'm certainly not a coding expert so apologies if it is a bit messy. If you're into this kind of thing, I hope you find it useful. 

You can change the dimensions from 400x400 to whatever you like. The curly brackets specify which column the lat/long comes from so you will need to make sure that this matches the name of your lat/long columns.

The only downside to this is that the image it displays is fairly random and not always representative of the area people click on. Sometimes, no image is available.

SIMD2012 - An Interactive Website

With the release of the latest version of the Scottish Index of Multiple Deprivation I thought I would take the time to put together an interactive mapping website so that people who are interested in exploring spatial patterns of deprivation could easily interact with the data. The official Scottish Government interactive mapping site has some nice features but I find it a bit cumbersome and the map interface is too small for my liking so that's why I've produced my own version, based on Google Fusion Tables.

Putting this together has prompted me to develop some additional mapping tools using Fusion Tables code and these can be accessed via the 'Tools' tab on the new website. The 'search and zoom' allows you to enter a place you want to look for and when you hit 'Search' the map immediately pans and zooms to that location.   The other tool I've created simply lets you turn the SIMD map layer on and off, which is quite a useful feature.

I've just looked at the relative ranks of places within Scotland in this site. For details of absolute change you can see the employment and income domain data available from the new SIMD 2012 website.

Data visualisation at datavisualization.ch

With so many different maps, infographics and other types of visualisations appearing on the web each day, it can be difficult for those interested in the visual display of quantitative information to keep up with the tools of the trade. Thankfully, the people at Datavisualization.ch in Switzerland have put together a selection of the most powerful and useful tools currently available (see image below). 

The datavisualization.ch examples page

Click on any of the images and you'll see the flipcard format in action, whereby the image flips round and tells you where you can find out more about that particular image, tool and further technical information.

They included an example from Google Fusion Tables that I developed for a newspaper here in the UK but more interestingly they have highlighted a whole range of tools that are either quite new and unknown (e.g. CartoDB) or more for technical experts (e.g. data.js). However, there are tools here that anyone can use - even those with no technical knowledge (e.g. Google Chart Tools). Thanks to Alex Ghita for pointing this out to me.

So, definitely worth a look. I should also add that this is quite similar in nature to the examples provided in a joint CLG/OCSI project a couple of years ago which produced the DataViz pages - in relation to improving data visualisation for the public sector.

Mapping Methods

I've done a lot of mapping on this blog in recent months. Much of this has been about deprivation and my attempts to make more widely available maps on deprivation for different parts of the UK. For this, I've often used Google's Fusion Tables. The most recent work I've done with this data using Fusion Tables is to update the Welsh Deprivation work to include the most recent release of the Welsh Index of Multiple Deprivation from 2011. There's a screenshot below which links to a full page Google map. If you click on an area the pop-up will tell you all about it in relation to the WIMD data. This post isn't about the data but the most deprived area is in West Rhyl and the least deprived in Cardiff (Llandaff area).

But this post is about methods, so more on that before I go... The steps below relate to any kind of data I've mapped using Fusion Tables (warning: technical content!).

1. First of all I usually have to join attribute data to spatial data. I do this in ArcGIS but it works well in MapInfo too. If you're a MapInfo user and want to follow the steps below, use Universal Translator in MapInfo to convert the file to a Shapefile first.

2. I don't like overly detailed boundaries because of the large file sizes and often this exceeds the Fusion Tables file limit. So, I simplify the boundaries. For this, I use mapshaper, a great online tool. You can also use other GIS methods.

3. Then I use something called shpescape. This is a really great tool because it allows you just to zip your shapefile (i.e. the shp, shx, dbf and prj files) and then upload directly to Fusion Tables without having to convert to KML as an intermediate step.

4. Once there, all you need to do is go to Visualize / Map and then go to work customising things. This includes map colours and what appears in the Info Window pop up when you click an area. I've blogged on the Info Window code bit before.

5. If you want others to see it you must make it public. Just click 'Share' in the top right of the Fusion Table screen.

That's about it. The Info Window code bit takes a while to figure out but you can do so much with it.

Energy Consumption in London

Gas price rises have been in the news quite a lot in recent months, and with good reason. Clearly, it's a big issue in many ways so I've been trying to explore more about it from a data point of view. This is where the Department of Energy and Climate Change come in, because they now provide quite localised energy consumption data for the whole of Great Britain. There's quite a lot of it and it can be a bit messy and difficult to understand but if you want to find out more the full explanatory pdf is here. From my point of view, I was interested to see how it looked spatially, so I took the domestic gas consumption data for London in 2008 and 2009 and mapped it at the small area level. I've done a full screen map which you can see by clicking on this link but you can also see it embedded below, though it works best full screen. Click an area on the map to see consumption data for that area and a key which explains the colour coding. 

Click here to see the full screen version

Finally, a simple dot map of where most domestic gas is used in London. This reflects a number of things, not least the size of properties, but perhaps also things like poverty and deprivation... The final thing to say is that the highest gas consumption areas consume about 41,000 kWh per year and the lowest areas less than 10,000. The London average is about 15,000 kWh.

Info Windows for Google Maps (from Fusion Tables)

A slightly boring technical post today because from time to time people get in touch to ask technical questions. So, I thought I'd just share a bit of my (limited) knowledge on controlling what appears in a google map info window when you click a map layer using Fusion Tables. Most of this probably applies to non-Fusion Table maps as well. Here's what I'm talking about...

Link directly to this page here.

As you can see from the screenshot above, this info window has a chart (drawn from the Fusion Table which is mapped) an image (a map key) and some text, which also pulls out unique data for each area on the map when you click on it. Since Fusion Tables are relatively new, there isn't an awful lot of information out there on how to do this (at least not info that simpletons like me can understand) so I spent quite a bit of time experimenting with the code behind the info windows. There is some useful information on this page and the pages it links to but when you have your own dataset it can be a bit difficult to grasp.

Not much else to say except that I've copied an image below which shows the code and the info window side by side. If you click on the image it should take you to a PowerPoint file which you can then use to copy the code if you wish. I'm certainly no expert but someone may find this useful!

IMD 2004, 2007, 2010 - Change Over Time...

This is the last deprivation-related post in a while. I promise. I've been experimenting with English IMD data from 2004, 2007 and 2010 and have finally found a moment to do to it what I did to the Scottish IMD data, where it is all online and clicking an area produces a chart showing change in rank through time. In this new example, I've also added in the change in rank between 2004 and 2010, as you can see below. The rank change doesn't tell you if an area became more deprived in absolute terms, only that it is less or more deprived in relation to the other 32,481 LSOAs in England.

You can see in the example above a case of a quite large change in rank. This is for an LSOA in London. The maps are quite basic because I don't have time right now to build a more sophisticated site but the colour scheme is based on quintiles, with red most deprived, yellow next and blues less deprived (the darker the blue, the less deprived), as below.

For the moment, I don't have any plans to extend this analysis to Wales or Northern Ireland, but you never know...

Google Fusion Tables

A short post today about the wonderful Google Fusion Tables. It may sound baffling, but Fusion Tables are basically a service (provided by Google, obviously) that allows you to manage and map large databases 'in the cloud', as they say. In plain speak, you can upload large tables of data and display them and map them and share them. For someone like me who is into mapping data, this is great because all you need to do is get your data into kml format. This can be done with normal GIS software like ArcGIS but the best way I have found is to use shpescape.



The example above is just something I created a couple of years ago in ArcGIS and then recently uploaded to Fusion Tables. It's a 'tile finder' for Ordnance Survey data so you can figure out which 20km tile you need to get based on which area you are interested in. This is not the point here though - I'm just showing how you can get data into google map format very easily.

If you work with lots of data on a regular basis - and particularly any mappable data - you should take a look at Fusion Tables...

Scottish Index of Multiple Deprivation 2009 - Google Map

Recently I've been experimenting with google fusion tables and the mapping and database opportunities they present. One dataset I used for this was the new English Indices of Deprivation for 2010 - something the Guardian picked up. This was only really a quick experiment (literally done over an hour or so) but I was impressed with the capabilities.

Since, I'm from Scotland I also did a Scottish version - click the image below to go to the test site. Again, this is only experimental but I find it useful for exploring the data in an easy to use manner.

Technical note: this also led me to discover the amazing shpescape aka Shape to Fusion for converting shapefiles to fusion format - code which was written by Josh Livni at google. Enough nerdspeak for now.