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.

Mapping people from Areas of Deprivation using Google Fusion tables


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 began by looking at the deprivation areas.

Starting with the data they had them create a spreadsheet to list it all with a clear column for each type of data they wished to record and but making sure that location data was clearly defined ideally using a postcode.

So they have separate columns for: “Name” “Address” “PostCode” “Status*” etc

You can use excel or another spreadsheet programs such as numbers or OOS but as Fusion Tables are a Google tool it is easier to just create it straight in Google Spreadsheets. If you’re combining data from different lists make sure your data and labeling are consistent. You want to make sure that your labels are accurate for all of the information you are using.

Once we had a list of all the people we wanted to map, with their details in one place we started to work out if they fell into an area of deprivation or not.

We created a column on their spreadsheet for “Decile” and visited http://opendatacommunities.org/showcase/deprivation to find where their staff lived.

We looked up the post codes. This this showed what decile the area falls in (1 to 10) as well as the overall ranking, but we were only interested in the deciles in this instance, Noting this in the decile column using figures not words 1, 2, 3 etc, not one, two, three…..

Sometimes where the output areas are quite small or a street falls across two output areas it wasn’t very clear where the location we were looking for was, so in these instances we looked up the full address on Google Maps and compared it to the Open Data Communities Map to find the exact location.

Once completed, we saved the data.
Now we could start to build the maps using Google Fusion Tables.

How to start using google fusion tables.

You need to be logged in with you gmail account (although if you’re not it should prompt you to log in)  Click on the link below


Click Create a table.

You will then need to upload the file you’ve been working from or choose the spreadsheet from Google Drive.

As you import the spreadsheet you will be given some options – the most important one to consider is the “Allow export” check box, as this is essentially your privacy option, If you are using sensitive or private data you want to make sure this is unchecked!


This will Once uploaded the program will turn your spreadsheet to a table. If it has automatically detected that there is location data by highlighting it yellow and it will show a map tab at the top of the page. If not you will need to tell the table one of the columns contains location data by changing the column type

Fusion Tables table


Click on the map tab – as it loads it will try to automatically map. Where there is more than one type of location column (ie city and postcode) you will need to tell the map which column to pull the data from. so click cancel and select from the drop down.

Location Column Selector


Once geocoded the points on the map will have a card that displays each row of data from your spreadsheet (table) you can click on the points to open a card to show the information for that location as seen in the (redacted) screenshot below.

Mapped_data points

If any points appear where you least expect them (Australia for instance) click on the point and check the data, you can edit it in the table on the rows tab to correct.

Once happy – if you want to hide information that appears in those cards publicly click on Change info window in the left panelChange Info Window Fusion Tables

This will open a box with list of content un-check those you don’t want to include and the click Save.

Updating content list



Now we have the information in the map we can start to play with it some more. There are different style types you can use to display the data. Fixed, Column, Buckets and Gradient.

On the Feature map tab on the left side click on the Change feature styles….

Where to find buckets in google fusion maps

To create the Decile map we used Buckets.

We knew the decile data was a numerical range so by selecting the Decile column and creating the ranges we wanted to display 1 – 2, 3 – 4 and 5 – 10 with different icons to represent each range we can show the staff that work for the company that live in areas of deprivation.

How to use buckets in google fusion tables to separate out groups

In the map below we can see the Purple markers represent Gateway People that live in the most deprived areas of Birmingham, falling in decile 1 and 2. The yellow Markers are people in areas that are in the 3rd and 4th decile, and Yellow show people in areas 5 and above.

Decile mapped data


Finally to remember what the map we are looking at represents we renamed the tab the map was in to “deprivation map”.

Rename Fusion Table Tab


So now we can see that Gateway can demonstrate that the majority of the people that work from them do indeed come from some of the communities with the highest levels of deprivation.
But not all of these were Gateway employees…. Some of these were volunteers and apprentices so now we wanted to separate out the list by status – but that comes in the next blog post.

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>