A site about how community groups and charities can make the most of data and open data to do something useful. Focused on Birmingham, relevant everywhere.

Creating maps from spreadsheets using Google Fusion Tables (Part 2)

0 Comments

Gateway Status Map

Gateway Family Services are a community Interest Company in Birmingham that work with people that need help from communities across Birmingham, Communities that fall in areas of deprivation. However as well as providing support to people in need in these areas they also actively employ staff and volunteers from them too.

But how can they demonstrate this?

Gateway wanted to be able to map 2 types of data, Where their employees come from and if they fall in an area of deprivation and then also what the employment status of those people were, whether they were Staff, Volunteer, Interpreter of Apprentice.

We worked with them to create these maps using Google Fusion Tables and this is how we did it:

We blogged previously about mapping the deprivation areas,  Now we’re looking at the employment status.

Because we had already created a table with their data we didn’t need to do that again and we just needed to look at the different feature styles to work out what to do next.

Google fusion tables lets you map your data using different filters. One of which is by column…

We had a status column in the table, but we couldn’t just select it as there was no way for the map to differentiate between them, so we had to tell the table what to do before it would appear on the map.

To do this we had to create a separate table to and merge it with this one we were currently working on.

We created a new spreadsheet that looked like this:

Spreadsheet

 

Column one giving the Category, Column 2 title “Icon Name” giving the descriptor of the marker as we wanted it to appear on the map. (You can find the descriptions by looking at the options on the Map Features Style on your existing table).

Fusion Table Marker Style

 

Using the steps in the previous post we created a new table just for this data.

Then going back to the original data table we had to merge these two tables together.

To do this go File > Merge > and select the table you’ve created with your icon colours in…

 

Merging Fusion Tables

Now you need to tell it what data it needs to merge… So in this instance the option on the left was the status column as this what I wanted to map by and the right was the category column as this was where I’d put the different status on the second spreadsheet..

Merging data in Fusion Tables
and then click next….

This will actually create an entirely new table to work from, which I hadn’t appreciated at first, so the previous deprivation map I had created wasn’t imported with it.  This was easy enough to recreate as I was using the same data – but if you are planning on creating lots of maps in different tabs from the same table, it is worth noting that it would be better to know what you’re going to do and merge your tables before you start.

Now I’d got my tables merged and recreated the deprivation map I could get on with creating the status map

Click the + symbol and select Add map,  once again select the Post Code for the location option.

Create New Map Fusion Tables

Now we can use the merged data to get the map to show the markers by status.

Click on Change feature styles and select the column tab.

Here you want to check the “Use icon specified in column” option and select Icon Name from the drop down options and Save.

Column Options Fusion Tables

 

This will create a map showing the points in the marker colours we chose when creating the second spreadsheet – so in this instance:

Red – Employee
Green – Volunteer
Blue – Interpreter
Yellow – Apprentice:

Gateway Status Map

 

And just like with the deprivation map you can click on markers to expand an information box to give more context to the point you are looking at

Status Cards Fusion Tables

 

and rename the map tab so it was easy to see what data we were seeing displayed on the maps.

Renamed Map

Both of these maps will now be used with their board to give proof to their claims that not only do they work with people in areas of need – they also actively employ from those areas too. 

Created under the Open Government License

Created under the Open Government License

 

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>