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.

Data for Journey Planning



I have a 17 mile commute to work that passes through a minimum of 4 local authority areas. Wolverhampton, Walsall, Sandwell and Birmingham. In the last few months junction 9 of the M6 has been closed so I have been travelling to work along alternative routes and it’s really come to my attention just how many road works are taking place. How disruptive it is. And how hard it is to find out where these works are taking place without digging through the depths of each individual boroughs websites.

So when another road by closed that affected my commute and was still closed well after the advertised reopening date I started asking around on twitter if anyone knew when the road was likely to be accessible.

One of the people who replied happens to be a travel correspondent for  a local radio station and he pointed me the direction of roadworks.org.


Road Works Mapped

Roadworks.org is a website that aggregates and maps ongoing road works. They collect data from the street works systems of local authorities that have signed up to the service, and provide live updates for anyone looking to use it.

The map is customisable to show different  types of incidents, restrictions and road works…

Roadworks.org customise


It is searchable down to street level, and individual incidents can be expanded to show more details:



But even better (for me at least) when combined with Googles Traffic Data which is one of the custom options you can see in real time how traffic is flowing around those works and plan your journey to suit.

Road works and Traffic data



And finally it’s all in one place, no more having to trawl through multiple sources to try and find a route to work!

Police Crime Data for Improving the Perceptions of Safety in Communities


Birmingham Crime MapAt a recent Central Birmingham Social Media Surgery I met Maria Hughes from Birmingham LGBT. Birmingham LGBT work to support and sustain a vibrant, diverse lesbian, gay, bisexual and trans community in Birmigham.

Maria and I were discussing how open data could be best used for the LGBT Centre on Holloway Circus near Birmingham City Centre. We looked at a couple of things they were working on but then she mentioned how  perceptions of safety of the area could potentially put people off visiting the centre, particularly at night.

So with that in mind we turned to look at the Crime stats for the area.

Every police force in England Wales and Northern Ireland release crime figures for their policing areas which are then published and are available to download on www.police.uk. 

We started by searching by area, in this instance Birmingham, and then narrowed down to specifics from there. The police.uk maps let you customise the areas you want figures for by drawing around the area on the map. For this excersize we just looked at the month of December.

Customised Police Crime map

And you can then zoom in which then separates the crimes out further, Which means it’s little more exact about where incidents took place. And you can hover your mouse over the numbers to see a breakdown on what type of crime.Download Police Crime Data

There is also more detailed analysis available with graphs and charts to show crime trends over the year and what the outcome of the reports were etc. If you click on the image above  it will take you to the detailed page for the area we’ve highlighted and the image below shows just one type graph they produce.

Types of crime

If you download the data not only do you get an overview of the numbers and types of crimes, but even more detail of where the crime took place that could provide context of the area, as we we can presume crimes “in and on a Nightclub” are more likely to be related to the Nightclub and drinking, than to residents of the area.

We also used the map to highlight other areas of the city and compare crime figures side by side. For instance highlighting an area of a similar size around New Street Station showed 92 crimes for the same period of varying types.

By looking at crime data like this Maria is now able to talk to the centre users, and next time someone may suggest that they are wary of using the centre at night she can show them actual data of what the area is really like, compare it to other similar areas, but also give a little context to the crimes which have occurred. Which hopefully goes some way to improving the perceptions of safety in that area.

Created under the Open Government License

Created under the Open Government License




Using Open Data to Evidence Funding Application


depivation mapper

Last week while working with BOSF as I was showing the open data communities deprivation map when suddenly Emma had a light bulb moment.

They’ve been applying to different funding pots but struggling at times to evidence that they are eligible for it. They needed to show that they serve a deprived LSOA area, and they had no idea what that is.

A LSOA is a “lower layer super output area” – or a geographic area of 400 to 1200 households, with a population of 1000  to 3000.  Deprivation in these areas is measured on multiple factors, and these are combined by the Office of National Statistics to give the Multiple Indices of Deprivation.

The ONS rank areas from 1 (really deprived) to 33,000+ (least deprived) and these are then grouped into into deciles –  so a decile areas 1 would be in the worst 10% for deprivation – 10 being the to top 10% for deprivation.

They have then mapped this data on  http://opendatacommunities.org/showcase/deprivation so that you can search by postcode and see at a glance if you live/work/volunteer in an area of deprivation OR if your membership comes from areas of deprivation.

This one website has now made BOSF and Emma’s life much easier as now not only does she know what a LSOA is – she hasn’t got to worry about working out the areas and if they fall into the eligibility criteria as it’s all mapped in front of her!


Created under the Open Government License

Created under the Open Government License

Using free tools to survey community groups and then perhaps combining that with open data. An example from BOSF, part 2


BOSF website

A few weeks ago we were working with Birmingham Open Spaces Forum to look at how they were collecting data and introduced them to Google Forms. Recently we returned to see how they were getting on and what we could help them with next.
As a group they were looking to collect information on their members income, How much they’d raised and where it had come from. Since we saw them 10 of their groups have filled in their questionnaire, so we began by looking at how they could display this.

We started by creating a separate sheet in google docs so that we could calculate the totals without disturbing the data that had already been collected – or confusing any future responses. They had columns for the different places they thought groups would be collection income from. Grants, fundraising, donations, etc. To be able to turn this into graphs we had to rearranges this so the titles were in rows – with the total amounts next to it. Like this:

BOSF Questionairre Spreadsheet

BOSF Spreadsheet


Once we were there we could use Google’s built in ability to create charts to display the data. We selected the cells with the data we wanted to display  and clicked on the create chart button .

Insert Graph Google Speadsheets


This opened up a screen that allowed us to select the type of chart we wanted to use, and edit the labels and titles.

Google Spreadsheet Charts

Once created you can place your graphic on to your spreadsheet, Save it to a separate tab in your spreadsheet, or save and download the image.

BOSF opted to go with the bar chart:

— BOSF (@BhamOpenSpaces) March 6, 2015


They are going to use this graphic to prompt more of their member groups to respond to the questionnaire, because if just 10 groups could have bought over £176,000 into the city – what have they done as a collective?

Combining this with Open Data

Once we’d finished helping BOSF display their own data we turned our brains towards how they could use open data to support their work.

For me the most obvious thing to begin with was Multiple Indicies of Deprivation . If BOSF have already evidenced they have bought £176k into the city, what areas was that money being spent in. Were the responding groups serving areas of deprivation?

What if they looked at the different domains of the deprivation map, are they bringing money into, and supporting areas of the city with poor Health for instance?

depivation mapper

The other thing we considered looking at was the police crime data. What records do the police hold on crimes in and around  the parks and open space the groups serve, What does this say about the effectiveness of friends groups? Is there more crime because there are more people around to report it? Or less because there are more people that care about the area?

They’re going to have a look at some of these things once their data collection is complete.

Created under the Open Government License

Created under the Open Government License

Data Visualisation Tools for Community Groups


Data Visualisation

Whether you are collecting your own information or downloading open data sets to use,   often what you end up with is a spreadsheet full of numbers.  Unless you know what you are looking for these can be hard to decipher.

This is where data visualisation tools come in.  You can use them can to turn those numbers in your spreadsheet into maps, graphs and charts It will help you display your work and highlight findings – literally visualise what the numbers mean.

A quick Google shows that there are lots of tools out there to do this but knowing where to start is daunting especially when you first start out. I know, as I’m just starting out myself and some of it looks like a foreign language.

I’m going to try and share some of the available tools as I discover them and look what they work best for on this blog. Starting today with the most obvious for me  –  Google’s data tools.

If you already have a Google email address you already have access to everything I’m going talk about below and for free.  They are included as part of the Google tools, either as a default option in google drive, or via an extension.

Google Speadsheets Charts

Google spreadsheets is very much like excel where you can create charts from columns and rows of data, You can  create bar charts, pie chart, scatter charts, line graphs and more. simply by highlighting the data you want to use and clicking on the “Create Chart” button and then running through the options.

All charts are customisable but can also be downloaded as images so you can use them outside of the spreadsheet too – to embed in a blog post or put in a report etc.

Google Spreadsheet Charts


Google Charts

Google Charts is a developers app for displaying visualations in a website. I’m not going to pretend I understand everything in the website as I’m no coder, but even I can tell it’s a great tool for displaying interactive data visualisation on your website…if you’re that way inclined, as well as creating images like those available in Spreadsheeds.

I’ve been looking around and it appears this a good place to the look at using charts in your website if you understand the code.

Google Fusion Tables

Google Fusion Tables is an experimental app from Google, It’s not included in the drive as standard so you will have to install the app from the chrome web store. I know I have barely scratched the surface of what fusion tables can do but I’ve played and it’s a brilliant tool for mapping data.

It seemed really tricky at first and there is still plenty I need to get my head around, but at it’s simplest  if you have a spreadsheet that contains location data you can use fusion tables to put that data onto a Google Map. This map will then also have all the capabilities of zooming etc. you’d expect from Google and you can embed the maps into your website / blog posts.

In addition to a single map you can also layer maps to compare data – I’ve yet to figure this out how to do this in tables, but this afternoon I came across a tool someone has created to make it a copy and paste job. This tool also makes embedding the layered fusion table maps easy, (and single maps too ) you can just take the generated HTML and copy and paste onto your site/blog.

If using you’re using wordpress you need to do this in HTML view and then NOT return to visual view before publishing as WordPress strips out the code – If I can I’ll write a follow up post on how to do this directly from tables as it also does something funky to the formatting that I’ve got to try and figure out. The map above is from the layers tool mentioned above.

But if you’re still not sure about embedding from fusion tables or using a tool as it seems complicated you could still use the maps for yourself or once you’ve got the map on screen you could use a programme such as Skitch to capture images of your screen to share easier.

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


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:



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


Marcus Belben giving feedback and the Kings Heath and Moseley Open Data Surgery


How easy is it to use open data – Surprisingly so, as Marcus Belben tells us after the Open Data Surgery in Kings Heath last night.

Created under the Open Government License

Created under the Open Government License

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.


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.

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 map tab on the left side click on the Change feature styles….

To create the Decile map we used Buckets.

We knew the decile data was 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.

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

Open Data Policy for Voluntary Service Councils.


Manchester Community Central  – the voluntary service council for the city – releases some of it’s data as open data.

They define it as

  • Open data are data that can be freely used, reused and redistributed by anyone

  • It is subject only, at most, to the requirement to attribute and sharealike

  • Open data are the building blocks of open knowledge

  • Open knowledge is what open data becomes when it’s useful, usable and used

and add that their main aims are


  • That our data are useful. We wish to publish data that bring value to those building a stronger community and voluntary sector.

  • That our data are meaningful. We wish to publish data that add to the ongoing dialogue and discussion about the sector.

  • That our data are accessible. We wish to publish data in an open and accessible way, and provide regular narrative and understanding to the stakeholders

So far they have provided open data on the grants they give and some information about groups in the city.  The grants data is very illuminating, showing who received the grant, what for and how much.

left hand side of the MACC grants spreadsheet

left hand side of the MACC grants spreadsheet

The image below of from one of the the spreadsheets on groups – showing how many member groups serve different parts of the city. I’ve added a very brief explanation of the GSS code and the GNS code. These help you when adding the information to a map.

what is the difference betyween GSS and ONS geographical data

MACC open data spreadsheet on groups.


The data comes in spreadsheet files which are  .ods format.  This is an open format  and can be opened with microsoft excel or open source software like open office (which is free, open source software and works much like microsoft office).   If you use an Apple Mac computer you’ll need to download an install open office to read the file.

MACC have  written a policy on open data (opens as a pdf)  – which is useful and practical for any vol sector organisation planning to follow in their footsteps.

Created under the Open Government License

Created under the Open Government License


Deciphering Data from the Department of Transport About Accidents in your Neighbourhood


Maze Starts Here
I’m currently attempting to write a post about how data can be used for a community campaign. I’m looking at what data is available to someone who who would like to campaign for a pedestrian crossing outside a school.

Thinking practically – where would I start if I were really going to do this –  I turned to the Department of Transport. They publish road accident data,  road safety spend, population details, road traffic data and more.

They’ve handily created a map of the road traffic accidents from their data on collisions, but I wanted to see if I could find out how that related to the area when bringing other factors into play. For instance 3 accidents in a year is a lot when traffic is generally low  – but 3 accidents in a year in a very high traffic low population area area might not be such a huge problem when considered in context.

I’ve just tried downloading their road traffic dataset, the description they give says it contains “Road traffic estimates broken up by local highways authority, vehicle type and total in miles broken up by year.” Fairly self explanatory, or so I thought.


Opening the excel file I quickly realise I need to do more research.

I understand that the numbers in the columns marked with vehicle type is the number of vehicles in millions that have travelled on roads in a specific location but in what location? What does ons_new_code mean? What do the S12000033 etc numbers represent?

I googled to try and find out, I quickly established that the ONS codes are codes from the Office of National Statistics that relate to place but that’s about as far as I could get without downloading other folders of documents to find what I needed from their site and I wasn’t sure which one…

So I turned back to the DfT site, and tried another download – this time looking at their data on population “Residential population data broken up by local highways authority and year.”

Now interestingly this data includes the ONS code and a local authority name so I can a least get an idea of where the codes related to…


So by looking up the local authority name on the population spreadsheet to find the ons code I was able to look up the road traffic data on the other…Or was I? I tried to test out my theory.

If the road traffic data is reported for the same 7 year period as the population data, (which it appeared to be) sorting the ONS_new_code column alphabetically on both spread sheets I should be able to compare the two side by side.

But that’s where my theory falls down. Just by looking at the number of rows on the two spread sheets you can see that the data isn’t easily comparable. There is 1170 rows of data on the population spread sheet and 1435 on the road Traffic Spreadsheet and it’s showing ONS codes that don’t exist on the other.

So it’s back to google I go to try and understand which of the ONS documents I need to decipher what the codes in the DfT data mean so I can ensure I’m looking at the correct information for the area I’m researching.

(Are you still with me?)

I’ve spent over an hour on this already – all the data I need is in front me, if only I could understand it!

Don’t worry – I won’t give up, but I just wanted to share this for anyone else embroiled in making the best use of open data to campaign around traffic, crossings, roads safety and any other term I can think of to encourage you to find this post through search.

Created under the Open Government License

Created under the Open Government License