STATS19 Mapping – a quick guide

Inspired by the breadth of data exposed by Cyclestreets own collision map and keen to find a way to make more use of the freely available STATS19 data, our coordinator Alex Ingram has built a custom map of collision data. Here he explains how to use it. You can find the map on Google Fusion Tables here. Whilst this piece refers to data in London we have also produced similar fusion tables for STATS19 UK Killed and Seriously Injured only 2012 – 1987 and STATS 19 Hammersmith only 2012-1982.
This is part of our attempts to support the use of maps for Space4Cycling.

STATS 19 Map from Google Fusion Tables

STATS19 is a large and complex data set covering all recorded collisions on the roads of the UK. Though essentially a compilation of paper filled in forms the data is hierarchical and much of it is only understandable if correctly expanded. What I’ve done is necessarily a simplification driven largely by the data we’re seeking to use. We are interested in accidents that saw injuries and deaths caused to cyclists, so that we can try to learn from them. I have been able to use PostGIS (a series of geographical extensions on top of PostgreSQL a database server) to rapidly classify the STATS19 data, so that after a bit of cleanup and import I could accurately place all 7.5 million records into Westminster constituencies, Borough councils and Council wards. Along with this, I have taken a simplified count of vehicles involved and details of the behaviour of the first cyclist in any collision (usually there is only one) to present a simplified single table version of the data suitable for Google Fusion Tables.

Technical note for open source purists – the choice of google fusion tables is essentially driven by need to get this done quickly, I am interested in pursuing this further with a fuller data set on a more open and powerful platform, provided that is affordable.

Querying the data set

The whole point of having STATS19 in this form is to query it by particular attributes. Chief among these is to filter by borough, and secondly ward. Here’s a quick guide to what you can do.

It’s best to explore the data in the map view. To access it load the map then click on the ‘Collision Map’ tab if the map isn’t showing.

Click filter and a list of fields will appear. You can then choose how you want to filter the map. Scroll down and select the boroughname filter.

Screen Shot 2014-01-20 at 18.22.35

In the borough name filter you can then choose the borough(s) you want to look at

Screen Shot 2014-01-20 at 18.22.58

Now, you may need to reposition the map to see your area. To do this use the zoom control on the side of the map window to see the markers then recenter the map by clicking and dragging the map. Then you can zoom in on the area you need.

Screen Shot 2014-01-20 at 19.39.50

Now, add a ward filter again going Filter then scrolling for wardname.

Screen Shot 2014-01-20 at 19.49.06

You can always move around the filters you have by clicking on three dots about the grey title bar.  The stacked lines ☰ give you some filter options, the single line _ collapses the filter details and the X removes the filter.

Screen Shot 2014-01-20 at 18.23.40

You can always see the filters you are using in the line beside the blue Filter button.

Screen Shot 2014-01-20 at 18.23.15

Additionally as you’ve probably spotted each filter also shows a simple count of features for each possible value

Screen Shot 2014-01-20 at 18.23.51

You can apply any other filter you wish in any combination. However, you can’t select to see one type or another on the same map (i.e. accidents that involved buses AND HGVs), but that’s about the only main limitation.

Understanding the Pop-up Window

Screen Shot 2014-01-20 at 18.24.26

For each point on the map an info window appears if you click on it.

This will show you a simple summary of the collision using the fields extracted from STATS19. To this I’ve added a link to Cyclestreets fuller listing of STATS19 for the years they cover along with three 120 degree street view images to give an idea of the location. Scroll the pop-up window to see all of these. If you’d rather just jump into streetview then drag over the street view man stood by the zoom control (he’s bright orange).

Screen Shot 2014-01-20 at 19.48.11

Data Field Guide

Here’s a quick run down of all the fields.

DFT_index, id_in_year
Two fields for the DfT used field to identify each accident, the second is just to help link to cyclestreets in the boxes on the map.
longitude, latitude
Every accident has a location, those prior to 2005 have sometimes been converted from an Easting and Northing using PostGIS
collision_severity, number_of_vehicles, number_of_casualties
year, date, time
Some split apart date fields, note that year is a string to make the interface a bit nicer on google fusion tables!
road1, road2, road_type, speed_limit
These are the basic details of the roads involved, road1 usually being the road the first vehicle was on, road2 being a meeting road. Note that all of these are as they were when the accident happened so things like the old route of the A40 persist
junction_detail, junction_control
This gives a rough idea of the junction design at the time of the collision
westminstername, boroughname, wardname
These are our specially worked out details of Westminster constituency, Borough and ward within borough.
cycle, bus, tram, hgv, goods, van, taxi, car, p2w, horse, other
A simplified summary of the vehicles involved in each collision. Note that HGVs are goods of 7.5 tonnes mgw and over, goods is anything unknown, over 3.5 tonnes or between 3.5t and 7.5t whereas van is any van or goods 3.5t and under (this is to simplify some years of varied classification). Refuse lorries and some things you might consider like an HGV are classed as other, as are tractors and minibuses
Unlike the other vehicle counts, articulated_vehicles is a count of all vehicles which were articulated.
cyclist_fatal, cyclist_serious, cyclist_slight
Fields that count the number of cyclists in each category of severity per collision
pedestrian_fatal, pedestrian_serious, pedestrian_slight, horse_fatal, horse_serious, horse_slight, vehicle_fatal, vehicle_serious, vehicle_slight
Fields that do the same for pedestrians, horse riders and anyone on any form of vehicle
cycle_lane, cycle_manoeuvre, cycle_hit_object, point_of_impact
A quick rundown of the lane, manoeuvre, object hit and point of impact for each cyclist. Object hit helpfully indicates car doors.
Though often not completed this can indicate commuters, those cycling for work and school run or commute journeys
gender, cyclist_age
gender and age range of the cyclist
Boringly we actually have to specify the icon to get the right colour

Final Notes

I’ve had quite a bit of fun trying to work this all out, but don’t want anyone else to have to reinvent the wheel! Groups outside London who are also keen on reviewing this data can upload data I have retrieved into Google Fusion Tables and have their own maps. Drop us a line below.

It would be great to have a single map for all collisions for all time for all of the UK but 7.5 million points might overwhelm any quickly designed solution! You can download the full Excel file for London here (xlsx) and we’ll post a full data set in csv on here in time, though it will need importing into a database of some quality to be usable, so we’ll post the how-to on that as well.

Technical note for feature hungry users – I’ve had a quick experiment at overlaying ward boundaries but it’s beyond time available at present. Anyone else is naturally welcome to have a go!

Leave a Reply

Your email address will not be published. Required fields are marked *

The Hammersmith and Fulham branch of the London Cycling Campaign