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.

A open data surgery in Trafford


Last week the fab Jamie Whyte ran an open data surgery in Trafford…

These are the people he helped and how…

Trafford Centre for Independent Living (CIL)

Trafford CIL provide a support service to the 4,500 or so carers in Trafford. They are preparing to carry out a comprehensive survey of carers in Trafford, and wanted to know if we would want the responses. We spoke about the sorts of data that it would be useful to collect, such as postcode (for geocoding). We also spoke about what we might be able to do with the data, in terms of mapping, etc. The CIL did have concerns that we might take their data, and then sell it (something that we would not do). The outcome of this conversation was that the CIL will send us the questionnaire before they distribute it, to make sure nothing useful is missed, and we will take it from there.

Lesbian, Gay, Bisexual and Trans Foundation (LGBTF)

We spoke about the fact that the LGBTF have LOTS of data, but it tends to be at a national level. We had a good long chat about how we might turn this national data into local data, probably by starting off using the same proportions that are reported nationally to get an idea of expected numbers. We then spoke about how we might carry out specific projects, in conjunction with the LGBTF, to identify local communities. Andrew from the LGBTF shared this (very good) document with me, GM Building Health Partnerships and we agreed that we should get together outside of the surgery, to discuss how we might work together.


Luxson are a company who have recently moved into The Fuse, in Partington. They wanted to get an understanding of the Partington area, so that they could better help the community. We spoke about the area profiles that we are developing, the very first part of which can be seen here. Lydia from Luxson agreed to be part of the test group as we develop the profiles.

First Asian Support Trust (FAST)

FAST offers support and advice to Asian communities, with a particular focus on the Old Trafford area. They had an idea for a project that would support Asian women to get jobs, and wanted to understand the ethnic make-up of Old Trafford. We spoke about the census and how there is a detailed breakdown of ethnicities and languages, and that that might be of use to the group. We also spoke about the potential of using the school census as a more up-to-date way of gauging ethnic composition of different areas. We said we’d send Linda from FAST the Census data, and take it from there.

Trafford Hard of Hearing group (THHG)

We spoke about the service that THHG provide – helping people who are hard of hearing to access lip-reading training, advice on using hearing aids, etc. They were interested in understanding where the groups potential clients would be – eg how they can tap into referral data from audiology departments of hospitals. We spoke about personally identifiable data, and how we wouldn’t be able to pass that on. We did mention that we might be able to aggregate location data to, for example Ward. We also explained the demographic data that we have available, and that we can tell the group where there are higher proportions of over 60s, an area of focus for THHG.

Community Allotment, Trafford Compass

We had an open chat about mental health and how the data that we can access and release can be used to help other services understand and target mental health support.

Art With a Heart

Great discussion with Karen about the place of Art in society. From a data point of view, we helped convert hours of volunteering into a cash equivalent, using minimum wage, plus 20%. We also spoke about which secondary schools offer arts subjects at GCSE and A-Level, and we agreed to look into this. We also said that we should be able to categorise and release Trafford’s voluntary sector grants and Trafford Housing Trust’s Community Panel funding, to show proportionate giving to art/culture projects.

Trafford Housing Trust / Friends of Coppice Library

One of the pieces of work that we had carried out was a profile of the area around Coppice Library, to support the new friends Group, and BlueSci. Dan from Trafford Housing Trust asked for this piece of work, which we’ll send. We also spoke very briefly about how the report was quite high level, and that we could tailor the data to his specific needs.

Collaborative Women

Collaborative Women are an organisation working with vulnerable women. They couldn’t stay, but they did give me this…

Note with open data requests on


Helping David Owen find the data he needs to make unattached playing fields in Birmingham more useful and more used.l


Earlier today Steph Clarke and I had a chat with David Owen of Friends of Spring Lane in Birmingham.

Sprin Lane playing fields in Erdington

Spring Lane is what is know as an “unattached playing field” (not attached to a specific school – although there for the sporting use of primary schools in Birmingham).  Spring lane once had numerous maintained pitches – including the two pitches you might be able to see on google earth.  It also had three changing rooms.  David and the other in the friends are hoping to revive the site for organised sport.    This is , briefly, what David said he is hoping to achieve…

He’d approached us because of the work we’ve being doin g to introduce data and open data skills to community groups through the social media surgeries.


So what was he after?  Information on

Health outcomes in an around this and other playing fields in Erdington. Perry Barr and Hodge Hill.

Crime statistics

Unemployment statistics

Training and skills information.

We pointed him in the direction of work we’ve already done with other groups helping them find what they need, or a starting point for what they need.  Here are some links we shared with him

This explains what Local super output areas are and how you might use them:

and this on using LG inform plus and its natural neighbourhoods to get broad brush data on neighbourhoods you can define yourself – including population and some health information.
which you can create your own here:
And look at other LSOA data around the Indices of Deprivation, making it easier for him to compare neighbourhoods and communities
Police data (maps):
Birmingham Data Factory:
Doorda – multiple maps – inc food places:
Freedom of information requests in public!
We also pointed him at the current Birmignham City Council open data consultation, in case there was something specific he’s looking for.
He was very enthused with the help that was available and the data there – and has some decent spreadsheet skills of his own.  So we were pleased to see that the approaches taken with other groups and blogged about here make sense for him.


How can community groups use open data for Big Local and Our Place


This is a practical run through of  something we picked up through a  question Becky Picklin asked us.  She works as the small groups officers for Dudley CVS and wondered if open data might help their support for Big Local in East Coseley

We first had a look at possibilities in the surgery at Stourbridge on 23rd February where Becky said we helped her

to find sources of data: doodad, LG inform. We had a hands on go at forming a natural neighbourhood (within the parameters of super output areas!) and hopefully that will provide useful data for that neigbourhood and community groups which work with people in that neighbourhood. The more you drill down, the more you get asked about what kinds of data you’ve got, so it;s important to stay focused and keep it simple.

We met again a few week later to do more than tinker. We set up a ‘natural neighbourhood to cover East Coseley.  A  natural neighbourhood is one tool where you can define your own neighbourhood on a map using super output area” and then colelct government data about that neighbourhood.

This is a quick video- scroll down this blog post for how we created the natural neighbourhood.  You can do the same for yours.


Natural neighbourhoods is part of a suite of open data tools being pulled together by the Local Government Association called LGInform plus.  It is quite focussed on local government but useful to all sorts of people.

Becky created this map – the dark green area is the neighbourhood she’s interested in and the data relates to just those areas) which presented her with these statistical report(s) for the neighbourhood she’s working in.  Basic facts about East Coseley (DRAFT) Big Local (DRAFT) and  Mapping health and healthcare provision in Dudley, with a focus on East Coseley (DRAFT) Big Local (DRAFT)  with data from



East Coseley Big Local in natural Neighbourhoods

East Coseley Big Local in natural Neighbourhoods

If these are useful for you here’s how to do it for where you live/work

First create got to  http://neighbourhoods.esd.org.uk/  and account on LgInformplus – you probably don’t want to become a subscriber (that cost money and has extra advantages, is aimed at local authorities)  but you do need to create a login. It only take a few minutes.



You’ll find a page with a drop down menu on the left – choose the local authority area you live – or rather want to make the map of (we found that you can’t work across local authority boundaries with this tool –  tell us in the comments please if we’re wrong, because it would be fab to be able to do that).  If you don’t know what local authority area you’re in use this postcode tool

Becky chose Dudley


and created a type of map – in this case she chose to call it Big Local.  ( we weren’t clear why you need to create a type of map – but you do (it can help group things)


You get a friendly congratulations….


Next you need to create you neighbourhood –

this is why you’re using the tool.  You click on “super output areas”  (these are the smallest area of your  community that the government keeps a range of statistics for, usually just a few streets) to create a neighbourhood that’s as close to the one you care about as possible.

Here Becky is trying to match the the map that exists on the Local trust website.

It only take a few minutes… building up the map but rember to save as you go along.


and you’ll have to compromise ….


Once you’ve established the neighbourhood and saved it you need to Publish It…  On the right hand side near the bottom is the publish this button.Natural_neighbourhoods_13

So where’s my data !

It won’t appear immediately – come back in24 hours and you should get reports that show


What will I get – here are the reports from East Coseley….

click here to download this one.

Have a go and let us know if it works for you.

Open Government License. Basic facts about Dudley

How using better data and being better informed can get you more of what you need


It matters when what you do changes something – this was a tweet from last month…

Yes that’s not a guarantee of change, but it’s evidence of a big step towards it.

Lisa Trickett is Marcus Belben’s local councillor.

Earlier this month Steph Clarke spend some time with Marcus  at the social media surgery we hold for Moseley and Kings Heath.  This is what Marcus said they covered:

Using data from doorda, crashmap, police.uk and road-collisions dft to find out and represent info on road safety and crime for a community group. Steph very helpful and explained clearly the sources available and how we could use in future.

Among other things he’s interested in, Marcus puts a lot of energy into make local streets safer, partly by trying to slow down traffic and also by encouraging more people to use the streets. Marcus is already pretty savvy with social media and has himself helped others at the surgeries and uses it to campaign for improvement:


He blogged about what they looked at together:

We have been documenting probems ourself (see our journeys to school, 1 (Arthur’s), 2 (Me, Jago and Freya), & 3(from 2013)) but data freely available online has really made the difference to prove the need for change.  Thanks to Podnosh and their Kings Heath and Moseley social media surgery, we’ve been able to access all the information we need for this, and pretty much anything else.

Marcus plans to use the information to also be better informed when talking to local police and council officers.  So one open data social media surgery has provided one group with enough extra info to hopefully influence their local council to slow down traffic on a road they consider dangerous.

Is that enough?  Of course it is, for now.

The whole point of the open data work we’re doing with the community groups who come to the social media surgeries is to help them achieve more. Those achievements can be big, but in reality they tend to be small, incremental progress.  That’s a good thing, because small improvements not only make the places we live in better, they are often easier to accomplish, so they can lead to lots of small ways we can build better relationships with public servants, politicians and other active citizens.

Consistency and Collaboration for Data Collection around Niche areas of Commuunity



I wrote previously about standardisation of data collection – being consistent on how data is collected, but now I’ve come across an interesting issue, not HOW  but WHAT data is collected.

Working with Birmingham LGBT we were trying to find data that could help them to target their support, they were particularly looking for older members of the LGBT community as these are the people that are often most in need of support.

We started looking around to see if we could find any information that gave any hint as to where to start looking – and we quickly ran into a problem that they had experienced time and time again. While age based data is widely available, data on sexual orientation, or sexual identity is much harder to find.


Because it is not uncommon for LGBT groups to encounter this problem they have started collaborating to bring useful data together on their Evidence Exchange

Any research that groups have done, or that they have found that they think is useful for others  is uploaded and the stats assigned to different topics and subtopics making it easier to search.

Groups collaborating in this way are making it easier for the LGBT community to find the facts and figures they need to evidence funding bids and to support their work. But while collaboration is a good thing if there was a consistent way data was collected then there would be no need for them to duplicate work that has already been done as the answers they need would be available with everything else.


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 Applications


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.