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.
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.
In the borough name filter you can then choose the borough(s) you want to look at
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.
Now, add a ward filter again going Filter then scrolling for wardname.
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.
You can always see the filters you are using in the line beside the blue Filter button.
Additionally as you’ve probably spotted each filter also shows a simple count of features for each possible value
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
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).
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
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!