clairemiller.net

Is one of your journalism resolutions this year to put in more Freedom of Information requests but not really sure where to start?

David Higgerson’s blog has a FOI Friday feature, collecting together stories from local and regional newspapers that have  been based on responses to FOI requests.

The list below collects together the topics of the requests featured, grouped by authority the information was requested from.

There are nearly 500 ideas below so it should keep you (and FOI officers across the country) busy for months to come.

Edited: Several people who work on the public authority side of FOI have pointed out that some of the information in the ideas listed below will already be available elsewhere (usually websites, accounts etc). FOI Man has a good guide on how to send a responsible and effective FOI.

Also the list is set up from the point of view of how Welsh public bodies are organised, unitary authorities and single-level health boards, this is very different from England where you seem to insist on making everything twice as complicated, so check which layer of bureaucracy is responsible for whichever area you want information about before you send your request off.

1. Councils

Councillors and meetings

• Cost of tea and biscuits at meetings
• Meeting attendance records for councillors
• Councillor allowances and expenses
• Council cash spent on councillors’ meals
• Council tax reminders for councillors
• Councillor’s first class travel
• Training days for councillors
• Council spending on political advisers

Council Staff

• Cost of union officials, who spend most of their time on union work
• Staff who live outside the council area
• Gagging orders on staff
• Amount charged to staff for making private calls
• Jobs get cut – but the agency spending goes on
• Relocation costs for staff
• Discrimination payouts
• Staff breaking computer use/social networking policies
• Top salaries at councils
• Money paid to officers to cover their commutes
• Accidents in council buildings
• Number of council officers with powers to enter homes
• The cost of suspended staff
• Bonuses paid to staff
• Assaults on council staff
• Redundancies – compulsory, cost, older or younger workers been hit
• Cost of finding new top officers
• The number of club class flights, luxury hotels and trips abroad made by public servants on the taxpayer
• Cost of recruitment
• Hotel stays by council staff
• Calls to speaking clocks/premium lines
• Websites visited by staff
• Council workers who took redundancy – and then got new jobs at the same council – cost of redundancy
• Council spending on mobile phones – the number of phones and the size of the bills and comparisons between councils
• Council workers overpaid due to payroll errors.
• Number of days council workers take off sick and how much it costs – including stress
• Cars for top council executives paid for by the council
• Amount paid (day or hourly rate) to temporary staff at councils.

Finance

• Spending on council credit cards
• Cost of council IT equipment
• Cost of buying stationary
• Compensation payments by the council – amount and reason for payout, including the lowest
• Do local councils support local business by paying bills quickly
• Cost of strikes
• Money spent on consultants
• Whether councils have a policy to buy local
• Cost of cancelled projects
• How much council tax is outstanding and how is it being chased, how much is written off?
• Cost of council refurbishments
• Money spent publicising cuts (or how council will cope with them)
• Cuts in grants to charities/community groups
• Council investments in dodgy companies (arms dealers etc.)
• Impact of cuts on departments – number of staff, size of budget
• Council spending on football clubs
• Council spending on temporary staff
• Council debts (rent, council tax) collected from the estates of dead people
• Councils getting liability orders and using bailiffs – locations of orders
• Council car mileage claim and rates that are higher than the 45p HMRC recommendation
• Amount councils make from selling on electoral roll data
• Land sold off by councils

Social Services

• How many children have been rescued from child sex rings
• Forced adoptions
• Children put in care because they are obese
• Elderly abuse
• Complaints against social workers
• The cost of closing care homes
• Cost of social workers
• Problem families relocated to the area (apparently covered under Multi Agency Public Protection Arrangements)
• Opening and closing times of youth clubs – kids back on streets by 7pm?
• Children in care sent to other council areas
• Number of children going missing from care – including ages

Highways and Vehicles

• Complaints about cabbies
• Hidden criminal offences of taxi drivers
• Crashes involving council vehicles and what the repairs cost
• Street lights being switched off
• How many potholes have been filled in, how many have been reported and cost of fixing them and paying compensation
• Fixed penalty notices for littering, dog mess
• Private gritting jobs
• Cars seized by the council
• Taxi vehicle checks

Parking

• Complaints about traffic wardens
• Overturned parking tickets
• Unpaid parking fines
• Ratio of parking spaces to parking permits or parking permits issued by road
• Fines for using disabled parking spaces
• Parking tickets – and how many issued incorrectly
• Cost of resident permits
• How much councils make off parking fines in top ticketed streets
• Top earning car parks
• Car park use falling in the recession?
• Parking fines for council vehicles
• Parking meters that don’t give change
• Attacks on traffic wardens

Planning and Housing

• Officer’s advice on planning decisions
• List of approved and proposed phone masts
• Empty houses – can also ask how many get second home relief and how much council tax is being lost
• Housing built on back gardens or several houses on plot of one house – and council attitudes
• Number of people complaining about landlords and letting agents – reason for complaints, and number of prosecutions and fines
• Big claims for housing benefit
• Numbers and locations of student homes (versus number of properties registered for council tax)
• Affordable Homes – where and what are they
• How much section 106 money councils have received and how much hasn’t been spent
• Homeless people winning appeals against councils for refusing support
• Cost of housing benefit cheats

Waste

• Streets with the most flytipping incidents
• Cost charged to people to buy a new wheelie bin
• Fines for leaving the bins out
• The number of bins private contractors for a council fail to collect each month
• Cost of replacing wheely bins that are lost or stolen

Schools

• Pest control in schools
• Illegal parking outside schools
• Schools which have dispensation from holding Christian assemblies
• Is home schooling on the rise
• CCTV cameras in schools
• The cost of crime in schools
• Estimated cost of repair backlog at schools
• Racism incidents reported in schools
• School bus cuts
• Different languages spoken in schools – list of the languages

Pupils

• Compensation claims by children for incidents in schools…and on school trips – numbers and payouts
• Rowdy kids on school buses
• School pupils caught with porn on school computers
• Largest number of violent incidents carried out by one pupil
• Special needs tribunals – to appeal not being statemented
• Racist schoolchildren
• The reasons for exclusion from school
• Children losing school places because parents lie on applications
• Cost of taxis for schoolchildren
• Education costs for traveller children
• Truancy facts and figures – days lost, prosecutions, fines.
• Number of pupils citing bullying as a reason when they look to switch schools
• Children missing education – children who don’t turn up to school and whose families can’t be contacted
• School place refusals
• School clothing grant postcode lottery

Teachers and Staff

• Disciplinary action against teachers
• Teachers’ sick days
• The cost of temporary teachers
• People in the classroom without a CRB check
• Attacks on teachers
• Failing teachers allowed to keep on teaching after being disciplined
• Spending for ‘off site’ training for teachers
• Pay rises at colleges and schools
• Cover supervisors taking lessons – should only do it for a short while using work from teachers
• How much do headteachers earn
• Teachers being made redundant

Libraries, Leisure Centres, Sport and Recreation

• What goes missing from libraries?
• People banned from libraries and leisure centres – and why they’re banned
• Antisocial behaviour reported in libraries
• Footfall at libraries
• Library fines – broken down by library
• Spending on library books, and other things found in libraries
• Items not being taken out of libraries – list of items and the last time they were borrowed
• Safety fears at a football ground – council safety advisory group which gives permission for clubs to hold matches
• House seats at council venues
• Spending on sport – by different sports
• The wait for an allotment
• Visits to council run sports centres

Other

• Number of lost and abandoned dogs found by councils – numbers reclaimed by owners, number put down.
• Food hygiene inspection details – poorly rated premises
• Cost of a plaque for ashes scattering
• Data protection breaches
• Amount of food thrown away by councils
• Numbers of stray horses and cost of dealing with them
• Neighbourhood Renewal Assessments – information on the state of areas
• Cost of hiring venues for council events
• Noise complaints and which areas see the most complaints
• Spending on private detectives
• Cost of burial and cremation
• Available burial plots
• Noise complaints – by ward/street and reasons
• Pest control call outs
• Smoking fines – how many?
• Complaints about noisy animals
• Body unclaimed in a mortuary for 10 years
• CCTV camera numbers
• Value of art
• Use of Regulation of Investigatory Powers Act powers on staff or the public
• Complaints about council services
• Footfall surveys of shopping centres
• Broken CCTV cameras
• Spending on Christmas lights
• Paupers funerals

2. Police

Crime – General

• Number of dangerous dogs seized, number of attacks reported and the number of dogs destroyed
• Levels of shoplifting
• Metal thefts
• Petrol station drive-offs
• Police called to incidents at schools, colleges and universities, e.g. burglaries – and what was stolen, hate crimes
• Crimes in hospitals, train stations, leisure centres, roads with the highest property values, the Welsh Assembly (you get the idea)
• Number of burglaries that lead to convictions
• Crimes committed in public buildings
• Hate crimes against disabled people
• Thefts from churches
• Scale of domestic violence – at a police patrol level and number of prosecutions/convictions
• Burglary hotspots
• Ride and run crimes involving people getting a cab ride and running off at the end without paying.
• Crimes involving Facebook, Twitter, eBay
• How often kidnaps were reported, and what the motives were, and how long people were held for
• Theft of numberplates
• Crimes committed by under 10s
• Number of crimes undetected
• Petrol thefts
• Railway cable thefts
• Reports of sheep worrying – and whether animals were attacked or killed
• Crimes commited by foreigners

Criminals

• OAP criminals
• Prolific and other priority offenders and the number of crimes they’re responsible for

Weapons

• Number of guns which police had taken off the streets
• Number of gun licences and who holds them
• Children carrying offensive weapons and what weapons
• Items seized as weapons by the police
• Location of knife attacks

Roads and public transport

• Highest speed clocked by speed cameras and where
• Number of images taken by ANPR cameras
• People using mobile phones while driving
• Tip off fees for letting recovery firms know to collect vehicles after crashes
• When are speed cameras turned off
• Top-earning speed cameras and those which catch hardly anyone
• The number of people caught ignoring traffic signals and nipping across a dangerous railway level crossing
• Fined for riding a bike on the pavement
• Attacks on speed cameras
• Number of incidents of taking vehicles without owner’s consent – and age of youngest criminal
• Top 10 streets with the most accidents.
• Nationality of drink drivers
• Speed tickets being overturned…
• …and don’t forget traffic light cameras
• Bus lane fines
• Crime on buses
• Attacks on public transport which aren’t resolved
• Speed camera detection rates

Drink and Drugs

• Teenage drug dealers or young people caught with drugs
• People prosecuted for breaching the smoking ban
• Women committing violent crimes while drunk
• Fixed penalty notices issued to drinkers – age breakdown
• Drink cited in the most crimes?
• Cannabis factory finds
• Call outs to pubs and clubs
• Locations of drug warrant executions – warrants issued under the Police & Criminal Evidence Act, 1984 (PACE) or the Misuse of Drugs Act 1971 – whether properties have been raided more than once.
• Number of times booze had been seized off people in the streets and for a number of other actions police can take to tackle the issue.
• Youngest drink driver
• Number of people stopped for drug possession – youngest, reason for stops

Sex Offences

• Sex offenders on the run and how many may have fled abroad
• Arrests for ‘extreme porn’
• Rape complaints and the number of convictions, also the number not investigated
• Use of Sara’s Law
• Sex offences involving the under 16s
• Where police have found couples involved in ‘indecent acts’
• The number of child exploitation cases reported to police
• Sex attacks carried out by relatives

Investigations

• Cold cases under investigation – and how many have been solved in recent years
• Cost of individual investigations – both solved and unsolved
• Top 10 most expensive police investigations
• Cost of big police investigations – especially failed ones

Sanctions

• Cautions used to deal with serious crimes…
• …or worse serious crimes “screened out” so not investigated
• Number of cases dealt with using restorative justice – and cases involving children
• How many crimes are solved through other offence been taken into consideration, and the percentage of the total solved cases
• Numbers of post-charge cautions being given out

Policing

• Taser use
• Stop and search on children – with ages
• Number of Osman warnings dished out by the local police – warnings issued after police have received specific intelligence that individuals were at serious, and imminent, risk of being killed.
• Where is crime most likely to be solved – detection rates by ward
• Stuff found on police raids
• Reports of ghosts, werewolves, witches, aliens, zombies and the like
• Fugitives on the run and what crimes are they wanted for, and how long they’ve been on the run for
• Number of stop and searches carried out
• Spending on forensics
• Number of crimes solved from information gained through force’s Facebook or Twitter accounts
• The cost of policing the EDL (or similar) protests
• Use of CS spray
• How busy are the armed police?
• Big Cats
• Amount of money paid to police informers
• Number of negative warrants carried out by police (where nothing is found) and cost of compensation as a result
• Bomb alerts – number and response
• Number of children having DNA swabs taken by the police – broken down by age
• Number of children held in police cells – ages

Organisational

• Filling up police cars with the wrong type of fuel
• Accidents involving police cars and cost of dealing with them
• Cost of security for police buildings
• How much forces hope to make by selling off police buildings
• Spending on bottled water
• What police helicopters are used for
• Cost of fuel for police vehicles
• Police charging organisations for policing events

Officers and Staff

• Money handed out to police officers in bonuses
• Claims of racial abuse made against police
• Disciplinary proceedings against police officers for breaching rules on social networking sites
• Coppers under investigation and why – information about disciplinary action
• Complaints against the police – and how many upheld
• How much different ranks earn
• Police officers taking second jobs
• Crimes committed by police officers
• Credit card expenses for senior officers
• Complaints against PCSOs (compared to police officers)
• Gifts to the police
• Missing laptops and mobile phones and data losses on the police force
• How many officers had retired from the force with pensions only to return in civilian roles
• Assaults on police officers
• Things stolen from the police
• Coppers on desk duty through illness
• Luxury cars for top brass
• Police jobs that are vacant
• Officers disciplined for using police computers to run searches on neighbours, family and the local area
• Forces forcing officers to retire after 30 years
• Overseas travel bills for police officers
• Police spending on hotels – number of nights per stay, number of people staying (lets you work out likely room rate).
• Emergency service personnel taking time off because of stress
• Historic data on police numbers
• Cost to police of defending claims (racism, sexism, harassment) from employees

3. Ministry of Justice

Prisons

• Assaults by prisoners on other prisoners
• Classes prisoners can take
• Stuff confiscated from cells, especially mobile phones
• Compensation paid to prisoners and why
• People returned to prison for breaking licences, what offences they committed and how quickly they reoffended
• Items smuggled into prisons
• DVDs prisoners are allowed to watch
• Complaints made by prisoners
• Menus served in prison

Courts and sentencing

• How many people have been spared jail by being given a curfew order instead, and how many go on to break the order
• Money recovered using Proceeds of Crime Act powers
• Number of parents prosecuted for not sending their children to school
• Items seized at courts

4. Fire

• Call outs due to false automatic fire alarms and the cost of attending
• Disciplinary action against fire officers
• Fire checks and enforcement notices issued to schools
• Chip pan/chimney fires – the number and the cost of tackling them
• Animal rescues
• Fire call outs where no smoke alarm has been fitted
• Hoax 999 calls
• Emergency service personnel taking time off because of stress
• Fire crew response times
• Compensation claims by fire service staff
• Number of times firemen have been called out to help paramedics move fat people.

5. Universities

• What have students been disciplined for
• How much has been paid in hardship payments and what for
• Reports on the state of buildings
• How accurate are university slogans – e.g. Plymouth University’s claim of being “the enterprising university”, only two ex-students set up businesses with university help
• The ways in which students have been caught cheating
• How much the university wine/art/plant collection is worth
• How many students demanded a refund for poor teaching
• How much universities make from hiring out facilities
• Where students come from – particularly those at top unis
• Donations from dodgy sources – dictators and the like
• How much are universities spending on hospitality
• Expenses for university bosses
• Library fines
• Social network policies and how many people have been disciplined for breaking them
• Student appeals against their exam and coursework grades
• Bomb hoaxes at universities
• Complaints about student behaviour

6. Health Boards

Care

• Number and seniority of doctors on duty on weekdays and at weekends – are hospitals understaffed at weekends
• Levels of bed blocking (delayed transfers of care), how many and how many bed days lost, and the longest case
• How many safety alerts are issued by the NHS National Patient Safety Agency have not been implemented
• Serious untoward incidents
• Injuries sustained in accidents in hospitals, patients and staff
• Number of hospital beds being axed, and where
• Deaths due to superbugs
• The number of children treated for eating disorders, and their ages
• ‘Red lists’ – lists of drugs which are approved for use but which are restricted due to their cost
• Interventions Not Normally Undertaken list – the things the PCT won’t normally pay for patients to have
• Number of cases of ‘surgical infection’ in patients who underwent major surgery and the number of emergency readmissions of patients following stays in hospitals
• Number of available hospital beds and instances of overcrowding
• Cancelled hospital appointments, as a percentage of all appointments
• Incentive schemes to encourage doctors not to prescribe antibiotics
• Number of people held at secure hospitals who have escaped and the numbers who have just left mental health units

Running Hospitals

• The number of hospital meals binned every day and the cost of doing so
• How much is being spent on temps and the hourly rates for agency staff – very expensive as a salary
• Haunted hospital wards
• Items handed into lost property at hospitals
• Responses to annual staff surveys – particularly questions along the line of : “Am I able to deliver the patient care I aspire to”
• Sick days taken by hospital staff
• Reasons why hospitals call pest control
• Spending on art in hospitals
• Hospitals with poor food hygiene ratings, why those who got 0 or 1 got a poor report
• Stuff reported stolen in hospitals
• Hospital inspection reports
• Civil law suits against hospitals – particular departments with high number of cases
• Hospitals letting injury-claim lawyers advertise in the hospital
• Amount raised by health boards in hospital parking

GPs

• Extra payments for doctors to do small operations in GP surgeries
• GP surgeries where the premises are below expected standards
• Amount of money paid to GPs for signing documents which allow funerals to be arranged
• Premium rate numbers to ring the doctors

Surgery

• The number of surgical instruments returned to NHS hospitals dirty or broken after being sent to be sterilised and whether operations had to be cancelled as a result
• How many operations go wrong, which ones and how
• Operations cancelled due to bed shortages
• Money spent on plastic surgery and which operations
• Number of tonsil removal operations
• Foreign objects removed from patients – how many and what
• Objects left in patients

Contraception and Maternity

• Babies born to drug dependent mothers, who were transferred to a drug treatment programme to wean them off their addiction
• Number of times morning after pill has been given out to teenagers
• Number of times maternity units have had to be closed because they are full.
• High-dependency cots for newborns being kept out of action due to staff shortages
• Number of complaints about maternity units
• Number of mums who give birth when they have addictions – what they’re addicted to.

Accident and Emergency

• A&E use by postcode
• How many times people have been diverted from A&E because it is too busy
• People arriving in ambulances who have to wait to be seen
• Number of people attending with dog attack injuries
• Number of gun injuries – information on types of guns

Health Boards/PCTs

• Money spent on public relations and marketing
• Top earning bosses
• How successful have healthy living projects been in getting people to live healthily
• Luxury cars for top brass
• Spending on licences to play music
• Public health funerals (like pauper’s funerals but paid for by hospitals)
• Why staff have been suspended
• Costs of private finance arrangements for building and maintaining hospitals
• Cost of translation services
• Attacks on staff – details of incidents
• Cost of priests and chaplains
• Money spent on taxis for patients
• Spending by health boards on getting people treated privately
• Amount owed to health boards by overseas patients not entitled to free treatment – breakdown of where they are from and how much they owe, how much written off
• Number and cost of exit packages at health boards

Obesity

• Number of children being treated for weight-linked issues, and their ages
• Number of gastric band and similar procedures carried out
• Spending on beds and equipment for oversized patients

Drink and drugs

• Hospital admissions due to drink, age breakdown
• People admitted to hospital for drug overdoses – numbers for different types of drugs, youngest
• Drugs found in overdose cases – which is most common

7. Ambulance

• How many 999 calls were attended by emergency care assistants only rather than with a fully-qualified paramedic – the split is supposed to be one of each per ambulance
• Ambulance crashes
• Police taking injured and sick people to hospital because of ambulance shortages
• Details of investigations carried out under the Ambulance Service’s ‘Adverse Incident Reporting System.
• How many alcohol-related incidents involving under-aged drinkers
• Number of incidents where ambulances waited for more than 20 minutes and how long for – average for each hospital and longest turnarounds
• Thefts from ambulances
• Out of date drugs in ambulances
• Emergency service personnel taking time off because of stress
• Number of times ambulances are delayed due to technical reasons – such as having faulty sat navs – also do ambulance services liaise with council etc. about road closures
• Hoax 999 calls

8. Ministry of Defence

• The number of staff disciplined for ‘leaking’ things from the Ministry of Defence on Twitter and Facebook
• Compensation claims from the MoD – other than suffered in combat, compared to compensation for combat injuries
• Breaches of security at armed forces bases

9. Health and Safety Executive

• Incidents logged to the Health and Safety Executive that happened at schools and details of whether the pupil or staff victim taken to hospital for treatment
• The number of health and safety improvement notices issued by the Health and Safety Executive to a local company

10. Bus Companies/Network Rail/Department of Transport

• Numbers of passengers using staff buses and cost of providing services
• Number of train services that ran with fewer carriages than stipulated in the franchise – Department of Transport
• Number of trains that run on time
• Correspondence between train operators and Government about finances
• Bus crashes
• Passenger numbers on trains at peak times – most overcrowded trains

11. UK Border Agency

• How many checks done for illegal workers and the size of fines issued as a result

12. Royal Mail

• Attacks on postmen by animals
• Number of letters destroyed each year
• Postcode area breakdown of complaints and the types of complaints, plus details of the numbers of posties investigated – in context of the number of rounds in each postcode
• Attacks on postmen

13. Environment Agency

• The number of rivers which fail to meet European standards for pollution and biodiversity

14. DVLA

• How many driving tests had to be cancelled due to bad weather, and the knock on effect it has on people trying to book in for a new tests
• The number of points the person with the highest number of points on their licence has in any given area and the number of drivers in any given area with more than 12 points on their licence
• The number of people taking their test who required an interpreter to get through their driving tests.
• Pass rates for men and women, different test centres and the person with the most failed attempts.

15. DWP

• Employment Support Allowance acceptances by area
• Appeal success rate for ESA by area

16. Charities Commission

• Number of registered charities in the red

17. HMRC

• The number of winding up orders issued by Her Majesty’s Revenue and Customs

18. Criminal Records Bureau

• Crimes flagged on CRB checks on people applying for teaching positions

19. Government

• Government/council spending on Facebook ads

My poor blog has been looking rather neglected. I haven’t really posted much for most of 2012, I’m hoping to change that a bit in 2013.

The second half of the year rather got away from me and I didn’t get as much done as I wanted to. I’m hoping to change that this year.

First thing that needs to be finished is the book I’m working about how  to get started with data journalism. It’s nearly finished, just a couple of stubborn chapters left to write (there is also the proofreading to do but it seems best not to think about that).

Other than that, I feel like I need to kick-start my learning when it comes to data journalism, there’s some new techniques I want to master, and I wanted to get Open Wales up and running again…and I have a start-up to launch at some point. So plenty to do.

Even though I failed to do much in the way of blogging, it appears people kept visiting. These were the most visited posts in the past year:

Putting it on a map – Google, BatchGeo and Fusion Tables

Huge list of data journalism resources

Putting it on a map – Longitudes and Latitudes

Combining Google Fusion Maps

How to make a heat map

Have completely failed to add a tutorial for making heat maps using Google Fusion Tables. To make this work, you have to first collect up some shapefiles – you can download them from open data sites, often as a KML, which can be directly uploaded or as shapefiles, which can be converted using Shape to FT.

The Middle Super Output Areas used below are available for download from the ONS. Alternatively download this Google Fusion Table and then upload the downloaded file to your own Google account.

To make a Fusion Table heat map, you will need a table containing your shapefiles and one containing your data.

For this example, I’m using the claimant count data, which gives an indication of the number of people claiming jobseeker’s allowance at low levels.

This can be downloaded from the  NOMIS site – clicking on advanced query brings up a tool that allows you to select various Department for Work and Pensions Statistics for different geographic areas.

The options are on the left-hand side of the screen, the easiest thing to do is work down the list selecting the variables you want.

For this example, I’ve selected claimants aged 24 and under in the Middle Super Output Areas in London.

Once you are happy with your selections, you can download the data as an Excel spreadsheet.

The spreadsheet needs some cleaning before it can be used. Firstly in order for it to be uploaded to Google Fusion Tables, the headers need to be in the first row.

Also, so the data can be combined with other data sets, the codes need to be in their own separate column.

This can be done by creating a new column to the left and adding the formula: =LEFT(B2,9), copying the first nine characters (the code part) into the new column.

Copy and paste the column, using Paste Special > paste values only, in the same place to get rid of the formula.

You can also tidy up the column with the name of the area in, to remove the part with the code, by adding a new column to the left, using =REPLACE(C2,1,12,“”).

Again copying and pasting values only will allow you to get rid of the formula, so you can delete the column with the codes and areas in.

While this data gives me the number of claimants looking for work, I want the proportion of young people are claiming jobseeker’s allowance for each area.

When making heat maps it is best to compare areas on a proportional scale, such as percentage or the number per 10,000 head of population, as different areas have different populations.

The population numbers for output areas can be downloaded from the ONS.

You can copy the figures for people aged under 24 into your original spreadsheet with the figures for claimants.

In order to make sure the claimants and the populations correspond to the same areas, sort the code columns for both. You can then check that they are lined up by comparing the code columns using =A2=E2

Add the columns for the number of people aged 15 to 19 and 20 to 24 to get a total population to compare claimants.

You can then work out the percentage of young people claiming jobseeker’s allowance in each area.

Save the file as a CSV and then upload to Google Fusion Tables.

Once the file has being uploaded, you can then merge it with the table containing the shapefile data using the codes to match up areas.

The new table will have a column containing the geometric information for each area – it will show up as a column with KML in italics for each matched area.

If you click on map, you will see the areas mapped out in red, which is the default.

In order to change the colours on the map, you need to select Tools > Change map styles.

You then need to select Polygons > Fill Colour and from there you have the option of buckets, which colour different areas different colours based on the value in the column selected, or gradient, which does the same, but colours areas based on a scale rather than distinct values.

It also helps to know how big a range of values you have – so from looking at the spreadsheet and sorting the percentage column from smallest to largest, I know the values go from 0% to 16.7%.

For this one, I have picked buckets and split it into four buckets

I have picked blue and orange for the colour scheme as people who are colour blind can still read it. Colorbrewer is a useful site for picking colour schemes for your heat map.

When you click save, the map should show up in the new colour scheme pretty instantly.

As you can see it is fairly dominated by the dark blue because a lot of the values are under 5%. One way to avoid this is to split the data into quartiles (or quintiles or deciles etc) so that there is a roughly equal number of areas of each colour – or you could pick out the bottom 10% of values and the top 10% and then split the remaining ones equally.

Doing this gives a better indicator of what areas are at the extremes and which are in the middle, rather than by splitting the values equally and possibly ending up with almost all the values grouped together and a few outliers.

This version of the map has the bottom 10% in dark blue, next 40% in light blue, next 40% in light orange and top 10% in dark orange

When you click on an area in the map, an info bubble comes up giving you information about that area.

To change what the info bubble says, click Tools > Change info window layout.

There are two options, a simple one where you can tick which columns you want to include data from.

Or one where you can combine data from the table with custom HTML. With this you can click on the information on the left to add in the data from a column for that particular area.

The benefit to this is you can create info bubbles which display the information about the data in sentences, making it easier to read and understand.

I’m slowly catching up with reading and blogging again – seriously, stop paying attention for five minutes and you end up buried in interesting things.

Among the things I’ve finally caught up on is the MEN’s excellent Crime down your street series looking at the crime map figures for the whole of 2011 in Manchester (I’d been looking out for it since Paul Gallagher mentioned he was working on the figures).

What the series is great for illustrating is the way in which data journalism can be used to generate a group of connected stories off the back of a single data source, which can be planned for the paper over a number of days (potentially a good way to get people to read the website/buy the paper over several days, plus offering some guaranteed copy

The articles themselves are really well put together, with maps, tables and case studies (I hate finding case studies, but yes, they do help bring a story to life), all of which is easier to produce if you’re not trying to do it right on deadline (this is totally the reason why I hate finding case studies).

It requires a commitment to forward planning and putting time aside to work on such stories – not always the easiest, given both the amount of time it takes to put together some pieces of data journalism (‘the longest I ever spent cleaning data’ is a future game of one-upmanship waiting to happen) and the in-the-moment demands of daily newspapers.

But the pay off is good – I’m not sure how the stories appeared in the paper but they look like the provided several spreads, but the online engagement is very high, the number of comments suggests they were amongst the most commented of the day (I can’t spot any numbers for Twitter/Facebook shares)

I tend to think one of the best arguments for data journalism in the newsroom is it generates stories – as this series demonstrates one piece of work can generate several good strong off-diary stories, the kind of thing people would much rather read over a rehashed press release, making it well worth the effort.

In the first part of basic mapping, everything went on the map nice and easily, in real life this pretty much never happens (x10 for every hour nearer to deadline you get).

The most consistent way to get things in the right place on a map is to add in columns based on longitude and latitude (it’s also pretty essential if you’re planning to use Tableau to visualise it all).

You will probably also need to do this for data that doesn’t come in proper address formats, which is pretty much every FOI ever.

In my case, the worst one ever was the parking tickets, which involved geocoding about 120,000 parking ticket locations.

Recently I’ve been playing with data about the location of bookmakers in Wales, and have managed to pull addresses for about 400 from yell.com and other service listings.

Get the data here: Bookies-Locations-csv

Mapping them on to a Google map means you hit a slight problem with things not been located to Wales.

With so many obviously in the wrong place, and too many to check if they’re less obviously in the wrong place (like South Wales locations in North Wales), a better mapping method is needed.

The first task is to pull as many postcodes out of addresses as possible.

In the column next to your address column you’ll need to enter the formula =RIGHT(B2, 8), where B2 is the cell in the address column. The eight is the number of characters you want it to copy from the end of the string in the address column (eight will cover a seven character postcode).

Copy and paste down entire column.

As a lot of the addresses don’t have postcodes, you’ll need to get rid of those. Sort the list A-Z and then get rid of the space in front of six character postcodes by swapping the eight in the formula to a seven, then delete everything that’s not a postcode.

Re-sort the list so you’re left with the postcodes at the top. Now to find locations for them.

The best place I’ve found for locating postcodes is the batch geocoding tool at www.doogal.co.uk. Just copy and paste the column of postcodes into the left-hand box and press geocode to get a nice list of corresponding longitudes and latitudes.

Copy and paste these into your spreadsheet (in OpenOffice I can paste them straight in noting the text is comma separated, however with Excel I need to go via Word to turn the text into a table, via the convert text to table tool).

Check the postcodes match up in the two columns (it does sometimes miss some) then delete one of the postcode columns.

That cover’s about a third of the addresses, what about the rest.

The other tool I use a lot is the GPS Visualizer’s Geocoder, where you add addresses and it returns locations using Google or Yahoo.

First I’m going to add Wales, UK to the empty cells in the postcode column to try and cut down on the number of points ending up in the USA.

Next copy and paste the cells you want to geocode from those two columns into the box on the page.

You now have two options from the source box, Yahoo, which is quicker but I find makes more mistakes, or Google, which is easier to check for accuracy but more of a faff.

As you can see, the Yahoo version comes out in a nice copy and pasteable format, see box, but you’d need to check each point on the map to see where it’s actually put a location and whether it’s right…or if it’s somewhere on the Isle of Wight.

Google results don’t output as a nice text file. Once they’re done geocoding (takes a little while) you’ll need to click the Create GPX file button to get the output.

Copy and paste the contents of the box into a separate sheet in your spreadsheet. What you’ve got is a line with the latitude and longitude in, a name – which is the address you were looking for and a description, and a description (desc) – which is what Google found for you.

Where the name and description match, copy and past the latitude and longitude into the cell in your main spreadsheet next to the matching address (you can use ctrl to highlight several cells and speed things up a bit).

When you’re down you’ll have a nice list of location information with  few addresses still to find.

Lets get rid of the extraneous information, just find (ctrl and f) the <wpt lat=” etc. parts and replace with nothing. Make sure to leave a space or find and replace one in between the latitude and the longitude.

Let’s get them split into two columns.

In Excel, highlight the cells you want to split, go to Data, Text to Columns, select Delimited in Step 1, in Step 2 pick the other checkbox and add a space in the box and hit finish (the location defaults to the current and neighbouring cells).

Those addresses still without locations – have to be done by hand, I’m afraid, Hopefully there’s not too many.

Back to doogal.co.uk and the find a postcode page, then it’s just a case of copying and pasting in the address to the box, hitting find and then copying the resulting latitude and longitude into the spread sheet – you may need to Google any really obscure addresses.

And that’s pretty much it, eventually you’ll make it to the end of your list of addresses and they will all have corresponding longitudes and latitudes, then just upload them to Google Fusion tables, select longitude and latitude as the location column and ta da:

Having posted recently about ways to combine Google Fusion maps or add basic postcode search (I’m going to revisit this one soon) I kind of forgot to write a post covering the basics.

I’m pretty convinced that people mainly care about how a story is relevant to them and where they live so being able to break information down to small areas and put it on a map is really handy.

Having spent some (actually, quite a lot of) time trying to put some maps together, these are the things I’ve found work quite well depending on what info you have and what you want to do with it.

Simple maps

Let’s grab some data to get started, I’m going for dentists in Cardiff and Vale (because it’s useful and fairly easy to find)

Go here and then copy and paste the table into a spreadsheet programme, and perform a bit of tidying – you’ll need to delete the ticks and area headings and add a some column headers.

Much better.

(Can’t be bothered to organise the data, just grab the above .xls file: Dentists)

Most basic way of mapping things is to create a basic Google map and start adding points.

First log in to your Google account and open maps. Then click on My Places and Create Map.

You can now give your map a title and description before you start adding the points.

There are two ways to add points:

-          Zoom to where you want to add a mark, then click on the add placemark button, then drop the marker where you want it.

-          Or if you’re not quite sure where the marker should go, put the location in the top bar to search for it first, then add the marker as above.

Add in your pin name and description, fiddle about with the styles and you’re on your way to a lovely map.

Click the link (looks like a small chain) icon to get the embed code (make sure the map is set to public first).

As you will quickly realise this is not very efficient, fine if you’re working with a small amount of data but not for anything large scale, we need something better.

Making maps with lots of points

The easiest way is just to feed the data into a website that does all the work for you. One example in BatchGeo.

Handily the site is really easy to follow, with plenty of step by step instructions.

Go to the website and copy and paste your data table into the box.

Click Validate and Set Options to set the location column and style the info box (what I have failed to do here is set a title column which will generate the pin’s title, I’d recommend doing that). You can also chose to group pins in categories.

Once you’re happy with the options, just click Make Google Map and it will do all the work.

Once done it will tell if it’s managed to geocode all the pins and give you the opportunity to drag any that are in the wrong place to the right spot.

Once you’re done, the site will email you with your embed code. You can also download the kml file (geographic file) for the map by scrolling to the bottom of the page.

Fusion Tables

Unfortunately, I can’t embed BatchGeo maps in the work website and importing the kml into Google Maps hits the display limits (you can only have so many points on a page).

Mapping solution: Google Fusion Tables

Firstly save your data table as a csv (especially if it’s big) or xls file.

Then open up Google, sign in and go to Documents. Click the create link on the right and pick Table and follow the instructions to upload your file.

When uploading you need to have just the first row as column headings, so make sure you shrink them down first.

Once your table is all loaded up, you can map it. First you need to make sure you’re using the address to find location, go to Edit and Modify Columns and check that the address column has the type location.

Once you’ve checked that, you can go to Visualise, then Map to see the points on a map. It will probably take a few seconds to visualise.

If you’re lucky everything will be in the right place (or at least appear to be).

You can also customise the markers. Before uploading (yes, should have said this earlier) you need to add a column named Marker (or something similarly self-explanatory) and fill it in with the names of markers, which can be found here (click on one to get its name).

I’ve decided to highlight the dentists still taking NHS patients in a different colour marker to make them stand out. Re-upload your spreadsheet with marker column.

Switch back to the map view. Click Configure styles on the top row and you get the option to customise the markers, The first tab gives you the option to change all the markers to the same thing.

The second lets you use the data in one of the columns to customise the markers. Select this one and chose the Marker column from the drop down menu and click save.

The third tab lets you set different markers for groups of numbers, e.g. items with a value of 1 to 5 are red, those with values 6-10, green.

Last thing to do is customise the info window you see when you click on a marker.

Click Configue info window on the top line. The first tab will lets you select which items to display in a simple column title: data format.

The custom tab allows you to write html using data points, just write your code and where you want the relevant item of data to appear, click the name on the left.

For this map, I think we’ll keep it simple and just get rid of the marker column, swap the telephone number to under address and make it italic. Save, then click Share in the top right corner to make public and shareable, and your map is done.

Since my minor success with combining Google Fusion maps, I’ve been trying out a few more things.

Firstly postcode search.

I really want to crack this one (it ties into my idea that most newspaper readers are most interested in what’s happening in their area) but it is proving difficult.

However, I think I’ve made my first steps, with this:

The basic code is from a tutorial here.

It’s pretty simple to follow, the Map Maker tool gives you the basic javascript and then the tutorial gives you the code to add to get a basic postcode search.

However, it’s not the best way of populating a map with markers. The best way around it, that I found is to use the & formula in a spreadsheet (where you paste together different cells into one) to add new information to the standard map marker code.

e.g. newpoints[&0&] = new Array(&51.546666&, &-3.586987&, icon0, ‘&Ynysawdre Comprehensive School&‘, ‘<b>&Ynysawdre Comprehensive School&</b><br><i>&Bridgend&</i>’);

where the black sections are all contained in different cells. Then it was just a case of copy and pasting into the right place.

I’m going to keep working on this one, I want the classic enter postcode, pull off a database type.

Secondly, drop-down menus.

Because the Guardian datablog one’s are great. So I decided to see if I could have a look at the code and work out how they work.

Turns out I can.

Best suggestion here is to view source and have a look at the code, I was commenting as I went so I could see what changes affected what.

Two things I noticed, I couldn’t get it to display lots of polygons, plus I managed to miss that the info window will be the same for both maps (I’m clearly using the women’s table).

I have also found out how to add javascript to WordPress posts, which was no mean feat as well, and I could only manage to add one.