ROAD SAFETY DATA UK

June 7, 2022

Overview

The strategic road network (SRN) is a vital national asset, supporting economic growth, regional development, and employment opportunities across England and the rest of the UK. It connects families, communities and businesses, enriching the lives of many citizens. Billions of miles are traveled on the SRN each year. The vast majority of these are safe and reliable journeys.

Every death or serious injury on our roads is a tragedy. Improving safety on the roads reduces physical, mental and emotional harm to individuals. A safer network also improves journey time reliability, providing economic benefits.

The road safety data provide details about the circumstances of personal injury road accidents in GB from 1979, the types of vehicles involved and the consequential casualties. The statistics relate only to personal injury accidents on public roads that are reported to the police, and subsequently recorded, using the STATS19 accident reporting form.

STATS19 is a code designating the protocol which outlines information to be collected whenever an injury crash is reported to the Police. This code is also frequently used to refer to Britain’s official Road Accident Statistics, which are derived from Police STATS19 returns and compiled by the Department for Transport.

STATS19 information is of great value to road safety practitioners, but cannot be made public in its entirety for reasons of personal confidentiality. However, information derived from analysis of STATS19 data forms the basis for the annual publication of Road Casualties Great Britain, and is also a key component of the data analysis.

Predicting traffic accidents is a crucial problem to improving transportation and public safety as well as safe routing. The problem is also challenging due to the rareness of accidents in space and time and spatial heterogeneity of the environment (e.g., urban vs. rural)

Our system consists of three parts in which we first cluster Casualty incidents in an interactive data map to highlight some hotspots and then narratively dive into accident attributes to uncover potentially related factors about the Accident.

  1. Accident
  2. Vehicle
  3. Casualty

Insights from the data: By this we could explore and analyze accidents on various combination of features such as

  • Analyze by collision factors (location, junctions, weather, lighting conditions)
  • Analyse by casualty factors (class, severity, sex & type)
  • Analyze by time factors (time of day, day of week)
  • Analyze by road factors (type, class, surface condition, speed limit)
  • Analyze by vehicle involvement (vehicle type, towing, maneuver)
  • Cross-compare vehicle, casualty and collision factors.

Accident Data

In Road Safety, an Accident is an incident occurring on the public highway which involved at least one vehicle. Any accident which resulted in human death or personal injury (an injury crash), and which has been notified to the Police.

Road traffic accidents (RTA) is a big issue to our society due to it being among the main causes of traffic congestion, human death, health problems, environmental pollution, and economic losses. Facing these fatal and unexpected traffic accidents, understanding what happened and discovering factors that relate to them and then making alarms in advance play critical roles for possibly effective traffic management and reduction of accidents.

Because an injury crash may result in more than one casualty, the total numbers of crashes and casualties cannot be expected to be the same. Great care should be taken to distinguish between numbers of incidents which occurred (crashes) and the number of people injured as a result (casualties).

The severity of a crash is defined as the highest severity of injury suffered by any resultant casualty. Consequently, it is meaningful to refer to a slight casualty which was suffered as a result of a serious crash, but not vice versa.

Data linkage and it’s description

The RSD_ACCIDENT_DETAILS contains the information on accidents. It contains all kinds of various attributes about the whole accident.

The Accident_Index is a unique value for each accident. The accident_index combines the accident_year and accident_ref_no to form a unique ID. It can be used to join Vehicle and Casualty tables.

The severity of an Accident is defined as the highest severity of injury suffered by any resultant casualty. Consequently, it is meaningful to refer to a slight casualty which was suffered as a result of a serious crash, but not vice versa.

Accident Severity can be shown as a separate table based on , Vehicles or Casualties. It should not be confused with Casualty Severity, which is shown only in reports based on Casualties.

The Longitude and Latitude data is based on WGS 1984. All the Accident attributes have lookup tables related to them which refers to the meaning of each field.

SQLDBM data modeling and visualization

Accident Look up tables

  1. ACCIDENT_ADJUSTMENT
  2. ACCIDENT_CARRIAGEWAY_HAZARDS
  3. ACCIDENT_DAY_OF_WEEK
  4. ACCIDENT_DID_POLICE_OFFICER_ATTEND_SCENE_OF_ACCIDENT
  5. ACCIDENT_FIRST_ROAD_CLASS
  6. ACCIDENT_FIRST_ROAD_NUMBER
  7. ACCIDENT_JUNCTION_CONTROL
  8. ACCIDENT_JUNCTION_DETAIL
  9. ACCIDENT_LIGHT_CONDITIONS
  10. ACCIDENT_LOCAL_AUTHORITY_DISTRICT
  11. ACCIDENT_LOCAL_AUTHORITY_HIGHWAY
  12. ACCIDENT_LOCAL_AUTHORITY_ONS_DISTRICT
  13. ACCIDENT_PEDESTRIAN_CROSSING_HUMAN_CONTROL
  14. ACCIDENT_PEDESTRIAN_CROSSING_PHYSICAL_FACILITIES
  15. ACCIDENT_POLICE_FORCE
  16. ACCIDENT_ROAD_SURFACE_CONDITIONS
  17. ACCIDENT_ROAD_TYPE
  18. ACCIDENT_SECOND_ROAD_CLASS
  19. ACCIDENT_SECOND_ROAD_NUMBER
  20. ACCIDENT_SEVERITY
  21. ACCIDENT_SPECIAL_CONDITIONS_AT_SITE
  22. ACCIDENT_SPEED_LIMIT
  23. ACCIDENT_TRUNK_ROAD_FLAG
  24. ACCIDENT_URBAN_OR_RURAL_AREA
  25. ACCIDENT_WEATHER_CONDITIONS

 

RSD_ACCIDENT_DETAILS

COLUMN_NAMEDATA_TYPEDESCRIPTION
ACCIDENT_SEVERITYNUMBERREFERS TO THE SEVERITY CLASS OF ACCIDENT
FIRST_ROAD_CLASSNUMBERREFERS TO THE FIRST ROAD CLASS ON WHICH ACCIDENT TOOK PLACE
LOCAL_AUTHORITY_DISTRICTNUMBERLOOKS UP THE LOCAL AUTHORITY DISTRICT AT WHICH ACCIDENT TOOK PLACE
NUMBER_OF_CASUALTIESNUMBERNUMBER OF CASUALTIES INVOLVED IN ACCIDENT
SECOND_ROAD_CLASSNUMBERREFERS TO THE SECOND ROAD CLASS ON WHICH ACCIDENT TOOK PLACE
JUNCTION_CONTROLNUMBERREFERS TO THE JUNCTION CONTROL AT WHICH ACCIDENT TOOK PLACE
POLICE_FORCENUMBERLOOKS UP THE POLICE FORCE AT WHICH ACCIDENT TOOK PLACE
ACCIDENT_TIMETIMESTAMP_NTZTIME AT WHICH ACCIDENT TOOK PLACE
URBAN_OR_RURAL_AREANUMBERREFERS TO THE TYPE OF AREA WHERE THE ACCIDENT TOOK PLACE
ACCIDENT_DATETIMESTAMP_NTZDATE ON WHICH ACCIDENT TOOK PLACE
ACCIDENT_YEARNUMBERYEAR ON WHICH ACCIDENT TOOK PLACE
DAY_OF_WEEKNUMBERDAY OF WEEK ON WHICH ACCIDENT TOOK PLACE
LIGHT_CONDITIONSNUMBERLIGHT CONDITIONS OF THE SCENE WHILE ACCIDENT TOOK PLACE
ROAD_SURFACE_CONDITIONSNUMBERROAD SURFACE CONDITIONS OF THE SCENE WHILE ACCIDENT TOOK PLACE
SPEED_LIMITNUMBERSPEED LIMIT OF ROAD ON WHICH ACCIDENT TOOK PLACE
VALIDATION_STATUSTEXTTELLS IF THE DATA IS VALIDATED BY SRN
NUMBER_OF_VEHICLESNUMBERNUMBER OF VEHICLE INVOLVED IN THE ACCIDENT
PEDESTRIAN_CROSSING_HUMAN_CONTROLNUMBERCATEGORY OF PEDESTRIAN CROSSING HUMAN CONTROL
WEATHER_CONDITIONSNUMBERWEATHER CONDITIONS WHILE ACCIDENT TOOK PLACE
SECOND_ROAD_NUMBERTEXTREFERS TO THE SECOND ROAD NUMBER ON WHICH ACCIDENT TOOK PLACE
LOCAL_AUTHORITY_HIGHWAYTEXTLOOKS UP THE LOCAL AUTHORITY HIGHWAY AT WHICH ACCIDENT TOOK PLACE
JUNCTION_DETAILNUMBERDESCRIBES THE JUNCTION AT WHICH ACCIDENT OCCURRED
ACCIDENT_REFERENCETEXTUNIQUE VALUE FOR EACH ACCIDENT. THE ACCIDENT_INDEX COMBINES THE ACCIDENT_YEAR AND ACCIDENT_REF_NO TO FORM A UNIQUE ID. IT CAN BE USED TO JOIN TO VEHICLE AND CASUALTY
LATITUDETEXTLATITUDE OF LOCATION AT WHICH ACCIDENT TOOK PLACE
SPECIAL_CONDITIONS_AT_SITENUMBERDESCRIBES THE SPECIAL CONDITIONS IF ANY AT WHICH ACCIDENT OCCURRED
LOCATION_NORTHING_OSGRTEXTLOCATION NORTHING ON ORDNANCE SURVEY NATIONAL GRID
PEDESTRIAN_CROSSING_PHYSICAL_FACILITIESTEXTDESCRIBES THE PEDESTRIAN CROSSING PHYSICAL FACILITIES AT WHICH ACCIDENT THE ACCIDENT OCCURED
TRUNK_ROAD_FLAGNUMBERCHECKS IF THE ACCIDENT OCCURRED IS ON TRUNK ROAD
CARRIAGEWAY_HAZARDSNUMBERDETAILS ON THE CARRIAGEWAY HAZARDS CAUSED BY ACCIDENT
DID_POLICE_OFFICER_ATTEND_SCENE_OF_ACCIDENTNUMBERCHECKS IF THE POLICE OFFICER ATTEND SCENE OF ACCIDENT
FIRST_ROAD_NUMBERNUMBERREFERS TO THE FIRST ROAD NUMBER ON WHICH ACCIDENT TOOK PLACE
LOCATION_EASTING_OSGRTEXTLOCATION EASTING ON ORDNANCE SURVEY NATIONAL GRID
LONGITUDETEXTLONGITUDE OF LOCATION AT WHICH ACCIDENT TOOK PLACE
LSOA_OF_ACCIDENT_LOCATIONTEXTONS LOCATION OF ENGLAND AND WALES ONLY.
ACCIDENT_INDEXTEXTUNIQUE VALUE FOR EACH ACCIDENT
LOCAL_AUTHORITY_ONS_DISTRICTTEXTLOOKS UP THE LOCAL AUTHORITY DISTRICT AT WHICH ACCIDENT TOOK PLACE(ONS DATA )
ROAD_TYPENUMBERREFERS TO THE ROAD TYPE ON WHICH ACCIDENT TOOK PLACE

Sample SQL Query on Accident data

Usage

Example 1

How many total accidents happened in the year 2015 at a Roundabout

Example 2

Count total number of severity for each category in the year 2019

Vehicle Accident Data

The term vehicle refers to any means of transport involved in a crash, whether powered by engine, pedal or animal.

In STATS19 reporting, any record relating to an attended vehicle includes the Vehicle Type, and details of the driver or rider who was in control of it at the time of the crash. It is also related to details of any passenger in that vehicle who was a casualty of the crash, and any pedestrian who became a casualty as a result of collision with it.

It doesn’t include roller skates, prams, children riding toys on the footpath and other similar items. The term does cover all other means of transport, including motorcycles, pedal cycles, mopeds, ridden horses, animal drawn carts, self propelled invalid carriages, tractors, trams, quad bikes, tanks, and street barrows.

A driver is a person who was in control of a vehicle at the moment when it became involved in a crash. In STATS19 data, vehicles include those which are pedal or animal powered. Consequently this term generally includes riders of pedal cycles and horses, as well as motorcycle riders.

Because information about drivers and riders is stored as part of a vehicle record, reports which relate to them typically use the Vehicles Measure. Drivers who suffered an injury as a result of a crash are also separately recorded as casualties, distinguished from other casualties by the Driver casualty class.

People who are in, on, or in the act of alighting from a vehicle at the time of a crash, but are not in control of it, are considered to be passengers rather than drivers. A pedestrian using roller skates or similar, and children in prams or riding toys on the footpath, are also not considered to be drivers.

STATS19 returns include information about drivers in the vehicle records associated with each crash. This includes Gender, Age and Postcode data. The driver postcode recorded by STATS19 is used to derive the location where drivers reside, as shown in the Driver Home dimension.

The RSD_VEHICLES_DETAILS contains the information on all the vehicles involved in the accident. It contains all kinds of various attributes linking from the vehicle’s direction to details about the vehicle.The ACCIDENT_INDEX is further divided into various VEHICLE_REFERENCE numbers to identify each vehicle involved in the accident uniquely. This VEHICLE_REFERENCE is appended with the accident index to form the VEHICLE_ACCIDENT_INDEX which serves as the primary key for this table. All the vehicle attributes have lookup tables related to them which refers to the meaning of each field.

Data linkage and it’s description

SQLDBM data modeling and visualization

Vehicle data Look up tables

  1. VEHICLE_AGE_BAND_OF_DRIVER
  2. VEHICLE_AGE_OF_DRIVER
  3. VEHICLE_DRIVER_HOME_AREA_TYPE
  4. VEHICLE_DRIVER_IMD_DECILE
  5. VEHICLE_ENGINE_CAPACITY_CC
  6. VEHICLE_FIRST_POINT_OF_IMPACT
  7. VEHICLE_GENERIC_MAKE_MODEL
  8. VEHICLE_HIT_OBJECT_IN_CARRIAGEWAY
  9. VEHICLE_HIT_OBJECT_OFF_CARRIAGEWAY
  10. VEHICLE_JOURNEY_PURPOSE_OF_DRIVER
  11. VEHICLE_JUNCTION_LOCATION
  12. VEHICLE_PROPULSION_CODE
  13. VEHICLE_SEX_OF_DRIVER
  14. VEHICLE_SKIDDING_AND_OVERTURNING
  15. VEHICLE_TOWING_AND_ARTICULATION
  16. VEHICLE_VEHICLE_DIRECTION_FROM
  17. VEHICLE_VEHICLE_DIRECTION_TO
  18. VEHICLE_VEHICLE_LEAVING_CARRIAGEWAY
  19. VEHICLE_VEHICLE_LEFT_HAND_DRIVE
  20. VEHICLE_VEHICLE_LOCATION_RESTRICTED_LANE
  21. VEHICLE_VEHICLE_MANOEUVRE
  22. VEHICLE_VEHICLE_TYPE

RSD_VEHICLE_DETAILS

COLUMN_NAMEDATA_TYPEDESCRIPTION
AGE_OF_DRIVERNUMBERAGE OF THE DRIVER IN NUMBER
JOURNEY_PURPOSE_OF_DRIVERNUMBERJOURNEY PURPOSE OF DRIVER
VEHICLE_REFERENCENUMBERREFERENCE FOR VEHICLE UNDER AN ACCIDENT
AGE_BAND_OF_DRIVERNUMBERFEATURE ENGINEERED VARIABLE TO FIND THE AGE CATEGORY OF DRIVER
TOWING_AND_ARTICULATIONNUMBERDESCRIBES IF THE VEHICLE CARRAIAGE / TRAILER ETC
VALIDATION_STATUSTEXTTELLS IF THE DATA IS VALIDATED BY SRN
VEHICLE_LEAVING_CARRIAGEWAYNUMBERHOW AND WHERE ERRANT VEHICLES TRAVEL AFTER LEAVING THE CARRIAGEWAY
VEHICLE_LOCATION_RESTRICTED_LANENUMBERVEHICLE LOCATION IN RESTRICTED LANE
VEHICLE_DIRECTION_FROMNUMBERREFERS TO THE DIRECTION FROM WHICH THE VEHICLE IS FROM
GENERIC_MAKE_MODELTEXTMAKE MODEL OF THE VEHICLE
JUNCTION_LOCATIONNUMBERREFERS ABOUT WHICH LOCATION CORRESPONDING TO THE JUNCTION
SKIDDING_AND_OVERTURNINGNUMBERREFERS TO THE SKIDDING AND OVERTURNING CHARACTERISTICS OF VEHICLE
VEHICLE_LEFT_HAND_DRIVENUMBERREFERS OF THE VEHICLE IS LEFT HAND DRIVE
VEHICLE_TYPENUMBERSPECIFIES THE NATURE OF EACH VEHICLE INVOLVED IN A CRASH.
PROPULSION_CODENUMBERFUEL ON WHICH VEHICLE OPERATES
VEHICLE_DIRECTION_TONUMBERREFERS THE DIRECTION TO VEHICLE WAS MOVING
AGE_OF_VEHICLENUMBERAGE OF THE ACCIDENT VEHICLE
DRIVER_HOME_AREA_TYPENUMBERREFERS TO THE TYPE OF HOME ARES FROM WHICH THE DRIVER IS FROM
HIT_OBJECT_IN_CARRIAGEWAYNUMBERREFERS TO THE HIT OBJECT IN ON A CARRIAGEWAY
SEX_OF_DRIVERNUMBERSEX OF THE DRIVER
ACCIDENT_INDEXTEXTUNIQUE VALUE FOR EACH ACCIDENT
ENGINE_CAPACITY_CCNUMBERENGINE CAPACITY OF THE VEHICLE IN LITERS
VEHICLE_ACCIDENT_INDEXTEXTUNIQUE REFERENCE FOR A VEHICLE UNDER A ACCIDENT_INDEX
FIRST_POINT_OF_IMPACTNUMBERFIRST POINT OF IMPACT ON THE VEHICLE
HIT_OBJECT_OFF_CARRIAGEWAYNUMBERREFERS TO THE HIT OBJECT OFF IN A CARRIAGEWAY
DRIVER_IMD_DECILENUMBERAN INDEX OF MULTIPLE DEPRIVATION (IMD) IS USED TO IDENTIFY HOW DEPRIVED AN AREA IS. IT USES A RANGE OF ECONOMIC, SOCIAL AND HOUSING DATA TO CREATE A SINGLE DEPRIVATION SCORE FOR EACH SMALL AREA OF THE COUNTRY.
VEHICLE_MANOEUVRENUMBERINDICATES THE ACTIONS TAKEN BY A VEHICLE IMMEDIATELY BEFORE IT BECAME INVOLVED IN A CRASH.

Sample SQL Queries

Usage

Example 1

What are the vehicle types and number of accidents happened for each category

Casualty Accident Data

Changes to the ways in which collisions are recorded by some police forces has increased the number of serious injuries identified. This makes interpreting recent trends less certain. The recording of fatalities is unaffected. The number of fatalities in 2019 was 210, two fifths lower than the 2005-2009 baseline. However, since 2012, the overall trend in fatalities has been fairly flat, ranging between 210 and 250 per year, with the highest in this period being in 2018. Apart from the difference in reporting for the most recent years, there are wider factors affecting the number of casualties on the SRN. People are traveling further, there are more vehicles on the road and a wider mix of vehicle types. Road casualty figures can vary from year to year because of things like a single collision/multiple casualty incident or external factors such as the weather. There are many factors that affect safety on our network, including vehicle safety and improvements to our roads. We are committed to reducing all categories of casualties on the network. This will require a targeted approach with investments informed by evidence.

Reporting of road casualty data

CRASH is the DfT’s collision and reporting and sharing system. It allows police officers to capture and upload collision data from the roadside in real time. Since it was introduced gradually in 2012, there has been an increase in recorded serious casualties in Great Britain. This system has introduced changes in how the severity of an incident is recorded and provides a more consistent basis to classify and report the level of injury severity.

However, the change has meant that in some instances injuries previously classified as slight are now classified as serious. By 2019, the system was being used by 21 of the 38 police forces which cover the SRN. These represent approximately 55% of the network. In addition, there were similar severity reporting changes for the Metropolitan Police who adopted the case overview preparation application (COPA) system.

However, the number of collisions on the SRN are low compared to those on local authority or Transport for London roads. The DfT commissioned the Office for National Statistics (ONS), to estimate adjustment factors for historic KSI data. This enables the production of consistent numbers over a time period which are independent of the reporting method being used. The work is complete and the methodology paper Estimating and adjusting for changes in the method of severity reporting for road accidents and casualty data: final report was published in July 2019.

It is complemented by the Annex: Update to severity adjustment methodology which was published in September 2019. The DfT is inviting users to adopt the methodology and to provide feedback on it and the way in which the statistics are being used, including any challenges faced. The model is likely to be updated annually and as experienced in 2019, there may be a resulting uplift in the adjusted serious injuries. This is at least in part due to new forces joining CRASH in 2019 and not having a full year of CRASH data.

 

Data linkage and it’s description

The RSD_CASUALITY_DETAILS contains the information on accidents. It contains all kinds of various attributes about the Casualty.Note should be taken care that One accident might have more than one casualty and this could be identified uniquely by looking into the CASUALTY_REFERENCE under the same ACCIDENT_INDEX.

This CASUALTY_REFERENCE is appended with the ACCIDENT_INDEX to form the CASUALTY_ACCIDENT_INDEX which serves as the primary key for this table. All the vehicle attributes have lookup tables related to them which refers to the meaning of each field.

Since STATS19 permits to contain an estimated age, the age information may not always be exactly accurate.

  • An value of ninety-nine reported in STATS19 indicates that age data for an individual is missing or unknown, and is not used for persons aged 99 years
  • An age of ninety-eight years reported in STATS19 refers to any person aged 98 years or older at the time of the crash
  • An value of zero reported in STATS19 refers to a person aged less than 12 months old, and is not used for unknown ages
  • Children unborn at the time of a crash are not included

The levels of the Age hierarchy are defined as follows:

  • Child refers to any person aged under 16
  • Child_Preschool refers to any child aged 4 years or under
  • Child_School refers to any child aged between 5 and 15 years (inclusive)
  • The upper age band within this level covers a six year age range, including children aged between 10 and 15 years (inclusive)
  • Adult refers to any person aged 16 or over
  • Adult_Young refers to any adult aged between 16 and 24 years (inclusive)
  • The lower age band within this level covers a four year age range, including young adults aged between 16 and 19 years (inclusive)
  • Adult_Mid refers to any adult aged between 25 and 64 years (inclusive)
  • Adult_Senior refers to any adult aged 65 years or over
  • The upper age band within this level includes all persons aged 85 years or over
  • The upper age band at the level below this includes all persons aged 90 years or over

SQLDBM data modeling and visualization

Casualty data Look up tables

  1. CASUALTY_ADJUSTMENT
  2. CASUALTY_AGE_BAND_OF_CASUALTY
  3. CASUALTY_AGE_OF_CASUALTY
  4. CASUALTY_BUS_OR_COACH_PASSENGER
  5. CASUALTY_CAR_PASSENGER
  6. CASUALTY_CASUALTY_CLASS
  7. CASUALTY_CASUALTY_HOME_AREA_TYPE
  8. CASUALTY_CASUALTY_IMD_DECILE
  9. CASUALTY_CASUALTY_SEVERITY
  10. CASUALTY_CASUALTY_TYPE
  11. CASUALTY_PEDESTRIAN_LOCATION
  12. CASUALTY_PEDESTRIAN_MOVEMENT
  13. CASUALTY_PEDESTRIAN_ROAD_MAINTENANCE_WORKER
  14. CASUALTY_SEX_OF_CASUALTY

 

RSD_CASUALTY_DETAILS

COLUMN_NAMEDATA_TYPEDESCRIPTION
SEX_OF_CASUALTYNUMBERREFERS TO THE SEX OF CASUALTY
VEHICLE_ACCIDENT_INDEXTEXTUNIQUE REFERENCE FOR A VEHICLE UNDER A ACCIDENT_INDEX
BUS_OR_COACH_PASSENGERNUMBERREFERS IF THE CASUALTY IS A BUS OR COACH PASSENGER
CASUALTY_IMD_DECILENUMBERDEPRIVATION SCORES OF INDIVIDUAL AREAS INTO ONE OF TEN GROUPS OF EQUAL FREQUENCY
AGE_OF_CASUALTYNUMBERREFERS TO THE AGE OF CASUALTY
PEDESTRIAN_ROAD_MAINTENANCE_WORKERNUMBERREFERS IF THE CASUALTY BELONGS TO THE CATEGORY IF THE PEDESTRIAN IS A ROAD MAINTENANCE WORKER
CAR_PASSENGERNUMBERREFERS IF THE CASUALTY IS A CAR PASSENGER
CASUALTY_CLASSNUMBERREFERS TO THE CLASS OF CASUALTY SUCH AS PEDESTRIAN OR DRIVER ETC
CASUALTY_HOME_AREA_TYPENUMBERREFERS TO THE TYPE OF HOME ARES FROM WHICH THE CASUALTY IS FROM
CASUALTY_REFERENCENUMBERREFERS TO THE CASUALTY UNDER A ACCIDENT
VALIDATION_STATUSTEXTTELLS IF THE DATA IS VALIDATED BY SRN
CASUALTY_SEVERITYNUMBERREFERS TO THE SEVERITY CLASS OF CASUALTY
AGE_BAND_OF_CASUALTYNUMBERREFERS TO THE AGE BAND OF CASUALTY
PEDESTRIAN_LOCATIONNUMBERPEDESTRIAN LOCATION WHILE THE ACCIDENT TOOK PLACE
VEHICLE_REFERENCENUMBERREFERS TO THE VEHICLE UNDER A ACCIDENT
ACCIDENT_INDEXTEXTUNIQUE VALUE FOR EACH ACCIDENT
CASUALTY_ACCIDENT_INDEXTEXTUNIQUE REFERENCE FOR A CASUALTY UNDER A ACCIDENT_INDEX
CASUALTY_TYPENUMBERREFERS TO WHICH CASUALTY TYPE CATEGORY THE CASUALTY IS ASSOCIATED
PEDESTRIAN_MOVEMENTNUMBERPEDESTRIAN MOVEMENT WHILE THE ACCIDENT TOOK PLACE

 

Sample SQL Queries

Usage

Example 1

What are the casualty class and number of accidents happened for each class

 

ACCIDENT AND CASUALTY ADJUSTMENT DATA

We have record-level severity adjustment data on the road safety data.gov website alongside the 2019 annual report to facilitate severity adjustment analyses. At both casualty and accident level, these look-ups contain the model probabilities for them being serious or slight under injury-based reporting systems (IBRS) since 2004, i.e. as if all forces were using injury-based reporting systems. The look ups contain adjustments for all non-fatal casualties and accidents. Fatal casualties and accidents are excluded as they are not given an adjustment probability. Accident_adjustment and casualty_adjustment can be linked back to the main accident and casualty datasets respectively and aggregated to produce adjusted totals. The accident values can be derived from the casualty look-up.

2004 adjustments were calculated using variables collected from an older STATS19 specification. There are differences in the way variables were recorded and categorized in 2004. Therefore, 2004 adjustment figures are indicative, and it is recommended to use adjustment figures from 2005 onwards only.

The accident and casualty adjustment probabilities provided can be linked to the available yearly data extracts using the “Accident_Index” variable (as well as “Vehicle_Reference” and “Casualty_Reference” for the casualty dataset). To get the adjusted slight or serious totals for an aggregate, the values in the appropriate column need to be summed (“Adjusted_Serious” or “Adjusted_Slight”). A flag for whether the casualty or accident was originally recorded on an injury based reporting system (“Injury_Based”) has also been included.

Data linkage and it’s description

SQLDBM data modeling and visualization

Adjustment data Look up tables

  1. ACCIDENT_ADJUSTMENT
  2. CASUALTY_ADJUSTMENT

 

ACCIDENT ADJUSTMENT_DETAILS

COLUMN_NAMEDATA_TYPEDESCRIPTION
ADJUSTED_SERIOUSFLOATADJUSTED SERIOUSNESS PROBABILITY OF THE ACCIDENT
ACCIDENT_INDEXTEXTUNIQUE VALUE FOR EACH ACCIDENT.
ADJUSTED_SLIGHTFLOATADJUSTED SLIGHTNESS PROBABILITY OF THE ACCIDENT
INJURY_BASEDNUMBERA FLAG FOR WHETHER THE CASUALTY OR ACCIDENT WAS ORIGINALLY RECORDED ON AN INJURY BASED REPORTING SYSTEM

CASUALTY ADJUSTMENT_DETAILS

COLUMN_NAMEDATA_TYPEDESCRIPTION
VEHICLE_REFERENCENUMBERREFERS TO THE VEHICLE UNDER A ACCIDENT
ADJUSTED_SLIGHTFLOATADJUSTED SLIGHTNESS PROBABILITY OF THE CAUSALITY
CASUALTY_ACCIDENT_INDEXTEXTUNIQUE REFERENCE FOR A CASUALTY UNDER A ACCIDENT_INDEX
INJURY_BASEDNUMBERA FLAG FOR WHETHER THE CASUALTY OR ACCIDENT WAS ORIGINALLY RECORDED ON AN INJURY BASED REPORTING SYSTEM
ACCIDENT_INDEXTEXTUNIQUE VALUE FOR EACH ACCIDENT.
ADJUSTED_SERIOUSFLOATADJUSTED SERIOUSNESS PROBABILITY OF THE CAUSALITY
CASUALTY_REFERENCENUMBERREFERS TO THE CASUALTY UNDER A ACCIDENT
VEHICLE_ACCIDENT_INDEXTEXTUNIQUE REFERENCE FOR A VEHICLE UNDER A ACCIDENT_INDEX

Sample SQL Queries

Usage

Example 1(Accident Adjustment)

What is the total number of serious accidents happened in 2020 with a seriousness above 0.8 ?

Example 2(Casualty Adjustment)

What is the total number of casualties with minimal injuries below 0.3 slightness in 2019 ?

Road Traffic Statistics

What traffic data is available?

All the datasets have been produced using the methods described in the guidance notes. We have the following categories of data

Road level Annual Average Daily Flow (AADF) estimates

  • AADF Data by direction
  • AADF Data

Raw manual counts data collected by our trained enumerators

  • Raw count data

Summary road traffic estimates

  • Local authority level traffic estimates
  • Regional traffic estimates by vehicle type
  • Regional traffic estimates by road type

Major roads model geography

  • Major road network – shape file format

Contents of Datasets

Estimated Annual average daily flows (AADFs) by direction

An AADF is the average over a full year of the number of vehicles passing a point in the road network each day. The file has the same structure to the major roads AADF file with the additional column ‘Direction_of_travel’. Different directions can be summed up to give a total combined flow. However, for methodological reasons, the AADFs for different count points should not be added together.

The “AADF data by direction” file contains the following variables

COLUMN_NAMEDATA_TYPEDESCRIPTION
ALL_HGVSNUMBERAADF FOR ALL HGVS.
COUNT_POINT_IDTEXTA UNIQUE REFERENCE FOR THE ROAD LINK THAT LINKS THE AADFS TO THE ROAD NETWORK
HGVS_3_OR_4_ARTICULATED_AXLENUMBERAADF FOR THREE OR FOUR-ARTICULATED AXLE HGVS
HGVS_3_RIGID_AXLENUMBERAADF FOR THREE-RIGID AXLE HGVS.
HGVS_5_ARTICULATED_AXLENUMBERAADF FOR FIVE-ARTICULATED AXLE HGVS.
LINK_LENGTH_KMFLOATTOTAL LENGTH OF THE NETWORK ROAD LINK FOR THAT CP (IN KILOMETERS).
LGVSNUMBERAADF FOR LGVS.
ALL_MOTOR_VEHICLESNUMBERAADF FOR ALL MOTOR VEHICLES
DIRECTION_OF_TRAVELTEXTDIRECTION OF TRAVEL.
REGION_IDNUMBERWEBSITE REGION IDENTIFIER
LONGITUDEFLOATLONGITUDE OF THE CP LOCATION.
START_JUNCTION_ROAD_NAMETEXTTHE ROAD NAME OF THE START JUNCTION OF THE LINK
EASTINGNUMBEREASTING COORDINATES OF THE CP LOCATION.
PEDAL_CYCLESNUMBERAADF FOR PEDAL CYCLES.
ROAD_TYPETEXTWHETHER THE ROAD IS A ‘MAJOR’ OR ‘MINOR’ ROAD.
IDTEXTPRIMAR KEY
ESTIMATION_METHODTEXTTHE METHOD USED TO ESTIMATE THE AADF, FOR EACH CP AND YEAR.
HGVS_2_RIGID_AXLENUMBERAADF FOR TWO-RIGID AXLE HGVS.
LINK_LENGTH_MILESFLOATTOTAL LENGTH OF THE NETWORK ROAD LINK FOR THAT CP (IN MILES).
ROAD_NAMETEXTTHIS IS THE ROAD NAME (FOR INSTANCE M25 OR A3).
CARS_AND_TAXISNUMBERAADF FOR CARS AND TAXIS.
HGVS_4_OR_MORE_RIGID_AXLENUMBERAADF FOR FOUR OR MORE RIGID AXLE HGVS.
LATITUDEFLOATLATITUDE OF THE CP LOCATION.
NORTHINGNUMBERNORTHING COORDINATES OF THE CP LOCATION.
TWO_WHEELED_MOTOR_VEHICLESNUMBERAADF FOR TWO-WHEELED MOTOR VEHICLES.
HGVS_6_ARTICULATED_AXLENUMBERAADF FOR SIX-ARTICULATED AXLE HGVS.
SEQUENCENUMBER 
BUSES_AND_COACHESNUMBERAADF FOR BUSES AND COACHES
ESTIMATION_METHOD_DETAILEDTEXTTHE DETAILED METHOD USED TO ESTIMATE THE AADF
LOCAL_AUTHORITY_IDNUMBERWEBSITE LOCAL AUTHORITY IDENTIFIER.
YEARNUMBERAADFS ARE SHOWN FOR EACH YEAR FROM 2000 ONWARDS.
END_JUNCTION_ROAD_NAMETEXTTHE ROAD NAME OF THE END JUNCTION OF THE LINK
ROAD_CATEGORYTEXTTHE CLASSIFICATION OF THE ROAD TYPE.
RAMPNUMBERRAMP IS PRESENT OR NOT

Estimated Annual average daily flows (AADFs)

An AADF is the average over a full year of the number of vehicles passing a point in the road network each day. For methodological reasons, the AADFs for different count points should not be added together.

The ‘AADF data’ file contains the following variables

COLUMN_NAMEDATA_TYPEDESCRIPTION
LOCAL_AUTHORITY_IDNUMBERWEBSITE LOCAL AUTHORITY IDENTIFIER.
YEARNUMBERAADFS ARE SHOWN FOR EACH YEAR FROM 2000 ONWARDS.
ROAD_NAMETEXTTHIS IS THE ROAD NAME (FOR INSTANCE M25 OR A3).
ALL_HGVSNUMBERAADF FOR ALL HGVS.
EASTINGNUMBEREASTING COORDINATES OF THE CP LOCATION.
HGVS_3_OR_4_ARTICULATED_AXLENUMBERAADF FOR THREE OR FOUR-ARTICULATED AXLE HGVS.
LINK_LENGTH_MILESFLOATTOTAL LENGTH OF THE NETWORK ROAD LINK FOR THAT CP (IN MILES).
LONGITUDEFLOATLONGITUDE OF THE CP LOCATION.
START_JUNCTION_ROAD_NAMETEXTTHE ROAD NAME OF THE START JUNCTION OF THE LINK
TWO_WHEELED_MOTOR_VEHICLESNUMBERAADF FOR TWO-WHEELED MOTOR VEHICLES.
CARS_AND_TAXISNUMBERAADF FOR CARS AND TAXIS.
END_JUNCTION_ROAD_NAMETEXTTHE ROAD NAME OF THE END JUNCTION OF THE LINK
ESTIMATION_METHODTEXTTHE METHOD USED TO ESTIMATE THE AADF, FOR EACH CP AND YEAR.
HGVS_5_ARTICULATED_AXLENUMBERAADF FOR FIVE-ARTICULATED AXLE HGVS.
NORTHINGNUMBERNORTHING COORDINATES OF THE CP LOCATION.
PEDAL_CYCLESNUMBERAADF FOR PEDAL CYCLES.
RAMPNUMBERRAMP IS PRESENT OR NOT
ROAD_CATEGORYTEXTTHE CLASSIFICATION OF THE ROAD TYPE.
SEQUENCENUMBER 
COUNT_POINT_IDNUMBERA UNIQUE REFERENCE FOR THE ROAD LINK THAT LINKS THE AADFS TO THE ROAD NETWORK.
ESTIMATION_METHOD_DETAILEDTEXTTHE DETAILED METHOD USED TO ESTIMATE THE AADF.
HGVS_2_RIGID_AXLENUMBERAADF FOR TWO-RIGID AXLE HGVS.
HGVS_4_OR_MORE_RIGID_AXLENUMBERAADF FOR FOUR OR MORE RIGID AXLE HGVS.
LATITUDEFLOATLATITUDE OF THE CP LOCATION.
REGION_IDNUMBERWEBSITE REGION IDENTIFIER
ROAD_TYPETEXTWHETHER THE ROAD IS A ‘MAJOR’ OR ‘MINOR’ ROAD.
HGVS_3_RIGID_AXLENUMBERAADF FOR THREE-RIGID AXLE HGVS
HGVS_6_ARTICULATED_AXLENUMBERAADF FOR SIX-ARTICULATED AXLE HGVS.
IDTEXTPRIMAR KEY
LINK_LENGTH_KMFLOATTOTAL LENGTH OF THE NETWORK ROAD LINK FOR THAT CP (IN KILOMETERS).
BUSES_AND_COACHESNUMBERAADF FOR BUSES AND COACHES
ALL_MOTOR_VEHICLESNUMBERAADF FOR ALL MOTOR VEHICLES.
LGVSNUMBERAADF FOR LGVS.

Raw manual counts data

Raw manual counts dataset is the actual data collected by trained enumerators to feed into road traffic estimates.

The ‘Raw_Count’ file contains the following variables

COLUMN_NAMEDATA_TYPEDESCRIPTION
BUSES_AND_COACHESNUMBERCOUNTS FOR BUSES AND COACHES
HGVS_3_OR_4_ARTICULATED_AXLENUMBERCOUNTS FOR THREE OR FOUR-ARTICULATED AXLE HGVS.
YEARNUMBERCOUNTS ARE SHOWN FOR EACH YEAR FROM 2000 ONWARDS.
LONGITUDEFLOATLONGITUDE OF THE CP LOCATION.
COUNT_DATETIMESTAMP_NTZTHE DATE WHEN THE ACTUAL COUNT TOOK PLACE.
HGVS_6_ARTICULATED_AXLENUMBERCOUNTS FOR SIX-ARTICULATED AXLE HGVS.
ROAD_TYPETEXTWHETHER THE ROAD IS A ‘MAJOR’ OR ‘MINOR’ ROAD.
HOURNUMBERTHE TIME WHEN THE COUNTS IN QUESTIONS TOOK PLACE WHERE 7 REPRESENTS BETWEEN 7AM AND 8AM, AND 17 REPRESENTS BETWEEN 5PM AND 6PM.
RAMPNUMBERRAMP IS PRESENT OR NOT
HGVS_2_RIGID_AXLENUMBERCOUNTS FOR TWO-RIGID AXLE HGVS.
LGVSNUMBERCOUNTS FOR LGVS
LINK_LENGTH_KMFLOATTOTAL LENGTH OF THE NETWORK ROAD LINK FOR THAT CP (IN KILOMETERS).
LINK_LENGTH_MILESFLOATTOTAL LENGTH OF THE NETWORK ROAD LINK FOR THAT CP (IN MILES).
PEDAL_CYCLESNUMBERCOUNTS FOR PEDAL CYCLES.
SEQUENCENUMBER 
END_JUNCTION_ROAD_NAMETEXTTHE ROAD NAME OF THE END JUNCTION OF THE LINK
HGVS_3_RIGID_AXLENUMBERCOUNTS FOR THREE-RIGID AXLE HGVS.
REGION_IDNUMBERWEBSITE REGION IDENTIFIER.
ROAD_NAMETEXTTHIS IS THE ROAD NAME (FOR INSTANCE M25 OR A3).
COUNT_POINT_IDNUMBERA UNIQUE REFERENCE FOR THE ROAD LINK THAT LINKS THE AADFS TO THE ROAD NETWORK.
DIRECTION_OF_TRAVELTEXTDIRECTION OF TRAVEL.
START_JUNCTION_ROAD_NAMETEXTTHE ROAD NAME OF THE START JUNCTION OF THE LINK
EASTINGNUMBEREASTING COORDINATES OF THE CP LOCATION.
HGVS_4_OR_MORE_RIGID_AXLENUMBERCOUNTS FOR FOUR OR MORE RIGID AXLE HGVS.
LOCAL_AUTHORITY_IDNUMBERWEBSITE LOCAL AUTHORITY IDENTIFIER.
ALL_HGVSNUMBERCOUNTS FOR ALL HGVS.
ALL_MOTOR_VEHICLESNUMBERCOUNTS FOR ALL MOTOR VEHICLES.
LATITUDEFLOATLATITUDE OF THE CP LOCATION.
TWO_WHEELED_MOTOR_VEHICLESNUMBERCOUNTS FOR TWO-WHEELED MOTOR VEHICLES.
ROAD_CATEGORYTEXTTHE CLASSIFICATION OF THE ROAD TYPE .
CARS_AND_TAXISNUMBERCOUNTS FOR CARS AND TAXIS.
HGVS_5_ARTICULATED_AXLENUMBERCOUNTS FOR FIVE-ARTICULATED AXLE HGVS.
NORTHINGNUMBERNORTHING COORDINATES OF THE CP LOCATION.
IDNUMBERPRIMAR KEY

Local authority road traffic estimates

Traffic estimates provide the summary statistics on the distance traveled by vehicles on Great Britain’s roads.

The ‘local authority traffic’ file contains the following variables:

COLUMN_NAMEDATA_TYPEDESCRIPTION
ALL_MOTOR_VEHICLESFLOATANNUAL ROAD TRAFFIC ESTIMATE FOR ALL MOTOR VEHICLES IN THE GIVEN LOCAL AUTHORITY
LINK_LENGTH_MILESFLOATTOTAL LENGTH OF THE NETWORK ROAD LINK FOR THAT LOCAL AUTHORITY (IN MILES).
LOCAL_AUTHORITY_NAMETEXTTHE NAME OF THE LOCAL AUTHORITY.
LINK_LENGTH_KMFLOATTOTAL LENGTH OF THE ROAD NETWORK ROAD LINK FOR THAT LOCAL AUTHORITY (IN KILOMETERS).
REGION_IDNUMBERWEBSITE REGION IDENTIFIER
CARS_AND_TAXISFLOATANNUAL ROAD TRAFFIC ESTIMATE FOR CARS AND TAXIS IN THE GIVEN REGION AND ROAD CATEGORY.
IDNUMBERIPRIMAR KEY
LOCAL_AUTHORITY_IDNUMBERWEBSITE LOCAL AUTHORITY IDENTIFIER.
ONS_CODETEXTTHE OFFICE FOR NATIONAL STATISTICS CODE IDENTIFIER FOR THE LOCAL AUTHORITY.
YEARNUMBERTRAFFIC ESTIMATES ARE SHOWN FOR EACH YEAR FROM 1993 ONWARDS.

Regional traffic estimates by vehicle type

Traffic estimates provides the summary statistics on the distance traveled by vehicles on Great Britain’s roads

COLUMN_NAMEDATA_TYPEDESCRIPTION
BUSES_AND_COACHESFLOATANNUAL ROAD TRAFFIC ESTIMATE FOR BUSES AND COACHES IN THE GIVEN REGION AND ROAD CATEGORY.
IDNUMBERPRIMAR KEY
TOTAL_LINK_LENGTH_KMFLOATTOTAL LENGTH OF THE ROAD NETWORK ROAD LINK FOR THAT REGION (IN KILOMETERS).
YEARNUMBERTRAFFIC ESTIMATES ARE SHOWN FOR EACH YEAR FROM 1993 ONWARDS.
ALL_MOTOR_VEHICLESNUMBERANNUAL ROAD TRAFFIC ESTIMATE FOR ALL MOTOR VEHICLES IN THE GIVEN REGION AND ROAD CATEGORY.
ONS_CODETEXTTHE OFFICE FOR NATIONAL STATISTICS CODE IDENTIFIER FOR THE REGION.
REGION_IDNUMBERWEBSITE REGION IDENTIFIER
LGVSNUMBERANNUAL ROAD TRAFFIC ESTIMATE FOR LGVS IN THE GIVEN REGION AND ROAD CATEGORY.
TWO_WHEELED_MOTOR_VEHICLESFLOATANNUAL ROAD TRAFFIC ESTIMATE FOR TWO-WHEELED MOTOR VEHICLES IN THE GIVEN REGION AND ROAD CATEGORY.
TOTAL_LINK_LENGTH_MILESFLOATTOTAL LENGTH OF THE NETWORK ROAD LINK FOR THAT REGION (IN MILES).
ALL_HGVSFLOATANNUAL ROAD TRAFFIC ESTIMATE FOR ALL HGVS IN THE GIVEN REGION AND ROAD CATEGORY.
CARS_AND_TAXISNUMBERANNUAL ROAD TRAFFIC ESTIMATE FOR CARS AND TAXIS IN THE GIVEN REGION AND ROAD CATEGORY.
PEDAL_CYCLESFLOATANNUAL ROAD TRAFFIC ESTIMATE FOR PEDAL CYCLES IN THE GIVEN REGION AND ROAD CATEGORY.

Regional traffic estimates by road type

Traffic estimates provides the summary statistics on the distance traveled by vehicles on Great Britain’s roads

COLUMN_NAMEDATA_TYPEDESCRIPTION
REGION_IDNUMBERWEBSITE REGION IDENTIFIER
TOTAL_LINK_LENGTH_MILESFLOATTOTAL LENGTH OF THE NETWORK ROAD LINK FOR THAT REGION (IN MILES).
YEARNUMBERTRAFFIC ESTIMATES ARE SHOWN FOR EACH YEAR FROM 1993 ONWARDS.
ROAD_CATEGORY_IDNUMBERTHE CLASSIFICATION OF THE ROAD TYPE (SEE DATA DEFINITIONS FOR THE FULL LIST).
TOTAL_LINK_LENGTH_KMFLOATTOTAL LENGTH OF THE ROAD NETWORK ROAD LINK FOR THAT REGION (IN KILOMETERS).
ALL_MOTOR_VEHICLESFLOATANNUAL ROAD TRAFFIC ESTIMATE FOR ALL MOTOR VEHICLES IN THE GIVEN REGION AND ROAD CATEGORY
IDNUMBERPRIMAR KEY
ONS_CODETEXTTHE OFFICE FOR NATIONAL STATISTICS CODE IDENTIFIER FOR THE REGION.

Road types

CategoryCategory Description
PMM or Class A Principal Motorway
PAClass A Principal road
TMM or Class A Trunk Motorway
TAClass A Trunk road
MMinor road
MBClass B road
MCUClass C road or Unclassified road

Direction of flow

CategoryCategory Description
NNorth
SSouth
EEast
WWest
CCombined (flows separated by the direction of travel unavailable)

Types of vehicle

CategoryCategory Description
All_MVAll Motor Vehicles
2WMVTwo-wheeled motor vehicles (e.g. motorcycles etc)
CarCars and Taxis
LGVLight Goods Vans
HGVHeavy Goods Vehicle total
HGVR22-rigid axle Heavy Goods Vehicle
HGVR33-rigid axle Heavy Goods Vehicle
HGVR44 or more rigid axle Heavy Goods Vehicle
HGVA33 and 4-articulated axle Heavy Goods Vehicle
HGVA55-articulated axle Heavy Goods Vehicle
HGVA66 or more articulated axle Heavy Goods Vehicle
PCPedal Cycles

Appendix

Traffic figures at the regional and national level are robust, and are reported as National Statistics. However, DfT’s traffic estimates for individual road links and small areas are less robust, as they are not always based on up-to-date counts made at these locations. Where other more up-to-date sources of traffic data are available (e.g. from local highways authorities), this may provide a more accurate estimate of traffic at these locations.