Transport for London STATS19 Extract

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, I built a custom map of UK STATS19 collision data. Using data provided by TfL I have now used their extract to provide an early access to 2014 data ahead of DfT release in early July and to use TfL’s own definitions of which roads and borough or TLRN.

This map provides data for all of London but only for collisions involving cycles or pedestrians between 2005 and 2014



STATS19 is a large and complex data set covering all recorded collisions on the roads of the UK, even when reduced to only London.

Technical note for open source purists – the choice of google fusion tables is essentially a stop gap, and this should either go into one of the larger tools or an interesting javascript execution shortly-ish.

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. 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 ‘TfL STATS19 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 Borough filter.

Screen Shot 2014-06-18 at 15.35.57


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

Screen Shot 2014-06-18 at 15.36.06


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

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-06-18 at 15.56.15

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

Screen Shot 2014-06-18 at 15.56.28

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.

To make filtering easier there are a sequence of fields I’ve called flags. These are either given a value of t (true) or f (false) and enable you to filter by the parties involved in a collision. Note that it isn’t a given that the casualties in such a collision are always the most vulnerable party.

Screen Shot 2014-06-18 at 15.38.36

Understanding the Pop-up Window


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

Screen Shot 2014-06-18 at 15.54.53

This will show you a simple summary of the collision using the fields extracted from STATS19. Sadly I’ve had to remove google street view from the pop-ups whilst I handle an update to the StreetView API.

Data Field Guide

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

TfL’s unique identifier for each collision
The borough where the collision occured
collision_severity, number_of_casualties_in_col, number_of_vehicles_in_col
The basic details for each collision. The collision severity is that of the worse casualty but it may also involve casualties at lower severities if there are multiple.
collision_date, year, month, day_of_week, time
Some split apart date fields, day_of_week is by ISO standard (so 1 is Monday etc.)
highway indicates if it was on the TLRN (TfL controlled) or a borough road or even a Highways Agency road.
road_1, road_2, 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, ped_crossing_decoded
This gives a rough idea of the junction design at the time of the collision
light_conditions_banded, weather, road_surface
Some further detail on the conditions on the road at the time
special_conditions, c_w_hazard
A couple of fields often blank, that may show factors involved in a collision
longitude, latitude
Every accident has a location, these been converted from an Easting and Northing using PostGIS
Boringly we actually have to specify the icon to get the right colour
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, vehicle_fatal, vehicle_serious, vehicle_slight
Fields that do the same for pedestrians and anyone on/in any form of vehicle
ped, cycle, p2w, car, hire, bus, lgv, mgv, hgv, ogv, tram, 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. ogv is for goods vehicles of unspecified weight.
ped_flag, cycle_flag, p2w_flag, car_flag, hire_flag, bus_flag, lgv_flag, mgv_flag, hgv_flag, ogv_flag, tram_flag, other_flag
A set of simple flags, these are either given a value of t (true) or f (false) and indicate the presence of this group in a collision.

One thought on “Transport for London STATS19 Extract

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.