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_NAME DATA_TYPE DESCRIPTION
ACCIDENT_SEVERITY NUMBER REFERS TO THE SEVERITY CLASS OF ACCIDENT
FIRST_ROAD_CLASS NUMBER REFERS TO THE FIRST ROAD CLASS ON WHICH ACCIDENT TOOK PLACE
LOCAL_AUTHORITY_DISTRICT NUMBER LOOKS UP THE LOCAL AUTHORITY DISTRICT AT WHICH ACCIDENT TOOK PLACE
NUMBER_OF_CASUALTIES NUMBER NUMBER OF CASUALTIES INVOLVED IN ACCIDENT
SECOND_ROAD_CLASS NUMBER REFERS TO THE SECOND ROAD CLASS ON WHICH ACCIDENT TOOK PLACE
JUNCTION_CONTROL NUMBER REFERS TO THE JUNCTION CONTROL AT WHICH ACCIDENT TOOK PLACE
POLICE_FORCE NUMBER LOOKS UP THE POLICE FORCE AT WHICH ACCIDENT TOOK PLACE
ACCIDENT_TIME TIMESTAMP_NTZ TIME AT WHICH ACCIDENT TOOK PLACE
URBAN_OR_RURAL_AREA NUMBER REFERS TO THE TYPE OF AREA WHERE THE ACCIDENT TOOK PLACE
ACCIDENT_DATE TIMESTAMP_NTZ DATE ON WHICH ACCIDENT TOOK PLACE
ACCIDENT_YEAR NUMBER YEAR ON WHICH ACCIDENT TOOK PLACE
DAY_OF_WEEK NUMBER DAY OF WEEK ON WHICH ACCIDENT TOOK PLACE
LIGHT_CONDITIONS NUMBER LIGHT CONDITIONS OF THE SCENE WHILE ACCIDENT TOOK PLACE
ROAD_SURFACE_CONDITIONS NUMBER ROAD SURFACE CONDITIONS OF THE SCENE WHILE ACCIDENT TOOK PLACE
SPEED_LIMIT NUMBER SPEED LIMIT OF ROAD ON WHICH ACCIDENT TOOK PLACE
VALIDATION_STATUS TEXT TELLS IF THE DATA IS VALIDATED BY SRN
NUMBER_OF_VEHICLES NUMBER NUMBER OF VEHICLE INVOLVED IN THE ACCIDENT
PEDESTRIAN_CROSSING_HUMAN_CONTROL NUMBER CATEGORY OF PEDESTRIAN CROSSING HUMAN CONTROL
WEATHER_CONDITIONS NUMBER WEATHER CONDITIONS WHILE ACCIDENT TOOK PLACE
SECOND_ROAD_NUMBER TEXT REFERS TO THE SECOND ROAD NUMBER ON WHICH ACCIDENT TOOK PLACE
LOCAL_AUTHORITY_HIGHWAY TEXT LOOKS UP THE LOCAL AUTHORITY HIGHWAY AT WHICH ACCIDENT TOOK PLACE
JUNCTION_DETAIL NUMBER DESCRIBES THE JUNCTION AT WHICH ACCIDENT OCCURRED
ACCIDENT_REFERENCE TEXT 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 TO VEHICLE AND CASUALTY
LATITUDE TEXT LATITUDE OF LOCATION AT WHICH ACCIDENT TOOK PLACE
SPECIAL_CONDITIONS_AT_SITE NUMBER DESCRIBES THE SPECIAL CONDITIONS IF ANY AT WHICH ACCIDENT OCCURRED
LOCATION_NORTHING_OSGR TEXT LOCATION NORTHING ON ORDNANCE SURVEY NATIONAL GRID
PEDESTRIAN_CROSSING_PHYSICAL_FACILITIES TEXT DESCRIBES THE PEDESTRIAN CROSSING PHYSICAL FACILITIES AT WHICH ACCIDENT THE ACCIDENT OCCURED
TRUNK_ROAD_FLAG NUMBER CHECKS IF THE ACCIDENT OCCURRED IS ON TRUNK ROAD
CARRIAGEWAY_HAZARDS NUMBER DETAILS ON THE CARRIAGEWAY HAZARDS CAUSED BY ACCIDENT
DID_POLICE_OFFICER_ATTEND_SCENE_OF_ACCIDENT NUMBER CHECKS IF THE POLICE OFFICER ATTEND SCENE OF ACCIDENT
FIRST_ROAD_NUMBER NUMBER REFERS TO THE FIRST ROAD NUMBER ON WHICH ACCIDENT TOOK PLACE
LOCATION_EASTING_OSGR TEXT LOCATION EASTING ON ORDNANCE SURVEY NATIONAL GRID
LONGITUDE TEXT LONGITUDE OF LOCATION AT WHICH ACCIDENT TOOK PLACE
LSOA_OF_ACCIDENT_LOCATION TEXT ONS LOCATION OF ENGLAND AND WALES ONLY.
ACCIDENT_INDEX TEXT UNIQUE VALUE FOR EACH ACCIDENT
LOCAL_AUTHORITY_ONS_DISTRICT TEXT LOOKS UP THE LOCAL AUTHORITY DISTRICT AT WHICH ACCIDENT TOOK PLACE(ONS DATA )
ROAD_TYPE NUMBER REFERS 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_NAME DATA_TYPE DESCRIPTION
AGE_OF_DRIVER NUMBER AGE OF THE DRIVER IN NUMBER
JOURNEY_PURPOSE_OF_DRIVER NUMBER JOURNEY PURPOSE OF DRIVER
VEHICLE_REFERENCE NUMBER REFERENCE FOR VEHICLE UNDER AN ACCIDENT
AGE_BAND_OF_DRIVER NUMBER FEATURE ENGINEERED VARIABLE TO FIND THE AGE CATEGORY OF DRIVER
TOWING_AND_ARTICULATION NUMBER DESCRIBES IF THE VEHICLE CARRAIAGE / TRAILER ETC
VALIDATION_STATUS TEXT TELLS IF THE DATA IS VALIDATED BY SRN
VEHICLE_LEAVING_CARRIAGEWAY NUMBER HOW AND WHERE ERRANT VEHICLES TRAVEL AFTER LEAVING THE CARRIAGEWAY
VEHICLE_LOCATION_RESTRICTED_LANE NUMBER VEHICLE LOCATION IN RESTRICTED LANE
VEHICLE_DIRECTION_FROM NUMBER REFERS TO THE DIRECTION FROM WHICH THE VEHICLE IS FROM
GENERIC_MAKE_MODEL TEXT MAKE MODEL OF THE VEHICLE
JUNCTION_LOCATION NUMBER REFERS ABOUT WHICH LOCATION CORRESPONDING TO THE JUNCTION
SKIDDING_AND_OVERTURNING NUMBER REFERS TO THE SKIDDING AND OVERTURNING CHARACTERISTICS OF VEHICLE
VEHICLE_LEFT_HAND_DRIVE NUMBER REFERS OF THE VEHICLE IS LEFT HAND DRIVE
VEHICLE_TYPE NUMBER SPECIFIES THE NATURE OF EACH VEHICLE INVOLVED IN A CRASH.
PROPULSION_CODE NUMBER FUEL ON WHICH VEHICLE OPERATES
VEHICLE_DIRECTION_TO NUMBER REFERS THE DIRECTION TO VEHICLE WAS MOVING
AGE_OF_VEHICLE NUMBER AGE OF THE ACCIDENT VEHICLE
DRIVER_HOME_AREA_TYPE NUMBER REFERS TO THE TYPE OF HOME ARES FROM WHICH THE DRIVER IS FROM
HIT_OBJECT_IN_CARRIAGEWAY NUMBER REFERS TO THE HIT OBJECT IN ON A CARRIAGEWAY
SEX_OF_DRIVER NUMBER SEX OF THE DRIVER
ACCIDENT_INDEX TEXT UNIQUE VALUE FOR EACH ACCIDENT
ENGINE_CAPACITY_CC NUMBER ENGINE CAPACITY OF THE VEHICLE IN LITERS
VEHICLE_ACCIDENT_INDEX TEXT UNIQUE REFERENCE FOR A VEHICLE UNDER A ACCIDENT_INDEX
FIRST_POINT_OF_IMPACT NUMBER FIRST POINT OF IMPACT ON THE VEHICLE
HIT_OBJECT_OFF_CARRIAGEWAY NUMBER REFERS TO THE HIT OBJECT OFF IN A CARRIAGEWAY
DRIVER_IMD_DECILE NUMBER AN 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_MANOEUVRE NUMBER INDICATES 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_NAME DATA_TYPE DESCRIPTION
SEX_OF_CASUALTY NUMBER REFERS TO THE SEX OF CASUALTY
VEHICLE_ACCIDENT_INDEX TEXT UNIQUE REFERENCE FOR A VEHICLE UNDER A ACCIDENT_INDEX
BUS_OR_COACH_PASSENGER NUMBER REFERS IF THE CASUALTY IS A BUS OR COACH PASSENGER
CASUALTY_IMD_DECILE NUMBER DEPRIVATION SCORES OF INDIVIDUAL AREAS INTO ONE OF TEN GROUPS OF EQUAL FREQUENCY
AGE_OF_CASUALTY NUMBER REFERS TO THE AGE OF CASUALTY
PEDESTRIAN_ROAD_MAINTENANCE_WORKER NUMBER REFERS IF THE CASUALTY BELONGS TO THE CATEGORY IF THE PEDESTRIAN IS A ROAD MAINTENANCE WORKER
CAR_PASSENGER NUMBER REFERS IF THE CASUALTY IS A CAR PASSENGER
CASUALTY_CLASS NUMBER REFERS TO THE CLASS OF CASUALTY SUCH AS PEDESTRIAN OR DRIVER ETC
CASUALTY_HOME_AREA_TYPE NUMBER REFERS TO THE TYPE OF HOME ARES FROM WHICH THE CASUALTY IS FROM
CASUALTY_REFERENCE NUMBER REFERS TO THE CASUALTY UNDER A ACCIDENT
VALIDATION_STATUS TEXT TELLS IF THE DATA IS VALIDATED BY SRN
CASUALTY_SEVERITY NUMBER REFERS TO THE SEVERITY CLASS OF CASUALTY
AGE_BAND_OF_CASUALTY NUMBER REFERS TO THE AGE BAND OF CASUALTY
PEDESTRIAN_LOCATION NUMBER PEDESTRIAN LOCATION WHILE THE ACCIDENT TOOK PLACE
VEHICLE_REFERENCE NUMBER REFERS TO THE VEHICLE UNDER A ACCIDENT
ACCIDENT_INDEX TEXT UNIQUE VALUE FOR EACH ACCIDENT
CASUALTY_ACCIDENT_INDEX TEXT UNIQUE REFERENCE FOR A CASUALTY UNDER A ACCIDENT_INDEX
CASUALTY_TYPE NUMBER REFERS TO WHICH CASUALTY TYPE CATEGORY THE CASUALTY IS ASSOCIATED
PEDESTRIAN_MOVEMENT NUMBER PEDESTRIAN 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.

NOTE : 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_NAME DATA_TYPE DESCRIPTION
ADJUSTED_SERIOUS FLOAT ADJUSTED SERIOUSNESS PROBABILITY OF THE ACCIDENT
ACCIDENT_INDEX TEXT UNIQUE VALUE FOR EACH ACCIDENT.
ADJUSTED_SLIGHT FLOAT ADJUSTED SLIGHTNESS PROBABILITY OF THE ACCIDENT
INJURY_BASED NUMBER A FLAG FOR WHETHER THE CASUALTY OR ACCIDENT WAS ORIGINALLY RECORDED ON AN INJURY BASED REPORTING SYSTEM

 

CASUALTY ADJUSTMENT_DETAILS

 

COLUMN_NAME DATA_TYPE DESCRIPTION
VEHICLE_REFERENCE NUMBER REFERS TO THE VEHICLE UNDER A ACCIDENT
ADJUSTED_SLIGHT FLOAT ADJUSTED SLIGHTNESS PROBABILITY OF THE CAUSALITY
CASUALTY_ACCIDENT_INDEX TEXT UNIQUE REFERENCE FOR A CASUALTY UNDER A ACCIDENT_INDEX
INJURY_BASED NUMBER A FLAG FOR WHETHER THE CASUALTY OR ACCIDENT WAS ORIGINALLY RECORDED ON AN INJURY BASED REPORTING SYSTEM
ACCIDENT_INDEX TEXT UNIQUE VALUE FOR EACH ACCIDENT.
ADJUSTED_SERIOUS FLOAT ADJUSTED SERIOUSNESS PROBABILITY OF THE CAUSALITY
CASUALTY_REFERENCE NUMBER REFERS TO THE CASUALTY UNDER A ACCIDENT
VEHICLE_ACCIDENT_INDEX TEXT UNIQUE 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_NAME DATA_TYPE DESCRIPTION
ALL_HGVS NUMBER AADF FOR ALL HGVS.
COUNT_POINT_ID TEXT A UNIQUE REFERENCE FOR THE ROAD LINK THAT LINKS THE AADFS TO THE ROAD NETWORK
HGVS_3_OR_4_ARTICULATED_AXLE NUMBER AADF FOR THREE OR FOUR-ARTICULATED AXLE HGVS
HGVS_3_RIGID_AXLE NUMBER AADF FOR THREE-RIGID AXLE HGVS.
HGVS_5_ARTICULATED_AXLE NUMBER AADF FOR FIVE-ARTICULATED AXLE HGVS.
LINK_LENGTH_KM FLOAT TOTAL LENGTH OF THE NETWORK ROAD LINK FOR THAT CP (IN KILOMETERS).
LGVS NUMBER AADF FOR LGVS.
ALL_MOTOR_VEHICLES NUMBER AADF FOR ALL MOTOR VEHICLES
DIRECTION_OF_TRAVEL TEXT DIRECTION OF TRAVEL.
REGION_ID NUMBER WEBSITE REGION IDENTIFIER
LONGITUDE FLOAT LONGITUDE OF THE CP LOCATION.
START_JUNCTION_ROAD_NAME TEXT THE ROAD NAME OF THE START JUNCTION OF THE LINK
EASTING NUMBER EASTING COORDINATES OF THE CP LOCATION.
PEDAL_CYCLES NUMBER AADF FOR PEDAL CYCLES.
ROAD_TYPE TEXT WHETHER THE ROAD IS A ‘MAJOR’ OR ‘MINOR’ ROAD.
ID TEXT PRIMAR KEY
ESTIMATION_METHOD TEXT THE METHOD USED TO ESTIMATE THE AADF, FOR EACH CP AND YEAR.
HGVS_2_RIGID_AXLE NUMBER AADF FOR TWO-RIGID AXLE HGVS.
LINK_LENGTH_MILES FLOAT TOTAL LENGTH OF THE NETWORK ROAD LINK FOR THAT CP (IN MILES).
ROAD_NAME TEXT THIS IS THE ROAD NAME (FOR INSTANCE M25 OR A3).
CARS_AND_TAXIS NUMBER AADF FOR CARS AND TAXIS.
HGVS_4_OR_MORE_RIGID_AXLE NUMBER AADF FOR FOUR OR MORE RIGID AXLE HGVS.
LATITUDE FLOAT LATITUDE OF THE CP LOCATION.
NORTHING NUMBER NORTHING COORDINATES OF THE CP LOCATION.
TWO_WHEELED_MOTOR_VEHICLES NUMBER AADF FOR TWO-WHEELED MOTOR VEHICLES.
HGVS_6_ARTICULATED_AXLE NUMBER AADF FOR SIX-ARTICULATED AXLE HGVS.
SEQUENCE NUMBER
BUSES_AND_COACHES NUMBER AADF FOR BUSES AND COACHES
ESTIMATION_METHOD_DETAILED TEXT THE DETAILED METHOD USED TO ESTIMATE THE AADF
LOCAL_AUTHORITY_ID NUMBER WEBSITE LOCAL AUTHORITY IDENTIFIER.
YEAR NUMBER AADFS ARE SHOWN FOR EACH YEAR FROM 2000 ONWARDS.
END_JUNCTION_ROAD_NAME TEXT THE ROAD NAME OF THE END JUNCTION OF THE LINK
ROAD_CATEGORY TEXT THE CLASSIFICATION OF THE ROAD TYPE.
RAMP NUMBER RAMP 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_NAME DATA_TYPE DESCRIPTION
LOCAL_AUTHORITY_ID NUMBER WEBSITE LOCAL AUTHORITY IDENTIFIER.
YEAR NUMBER AADFS ARE SHOWN FOR EACH YEAR FROM 2000 ONWARDS.
ROAD_NAME TEXT THIS IS THE ROAD NAME (FOR INSTANCE M25 OR A3).
ALL_HGVS NUMBER AADF FOR ALL HGVS.
EASTING NUMBER EASTING COORDINATES OF THE CP LOCATION.
HGVS_3_OR_4_ARTICULATED_AXLE NUMBER AADF FOR THREE OR FOUR-ARTICULATED AXLE HGVS.
LINK_LENGTH_MILES FLOAT TOTAL LENGTH OF THE NETWORK ROAD LINK FOR THAT CP (IN MILES).
LONGITUDE FLOAT LONGITUDE OF THE CP LOCATION.
START_JUNCTION_ROAD_NAME TEXT THE ROAD NAME OF THE START JUNCTION OF THE LINK
TWO_WHEELED_MOTOR_VEHICLES NUMBER AADF FOR TWO-WHEELED MOTOR VEHICLES.
CARS_AND_TAXIS NUMBER AADF FOR CARS AND TAXIS.
END_JUNCTION_ROAD_NAME TEXT THE ROAD NAME OF THE END JUNCTION OF THE LINK
ESTIMATION_METHOD TEXT THE METHOD USED TO ESTIMATE THE AADF, FOR EACH CP AND YEAR.
HGVS_5_ARTICULATED_AXLE NUMBER AADF FOR FIVE-ARTICULATED AXLE HGVS.
NORTHING NUMBER NORTHING COORDINATES OF THE CP LOCATION.
PEDAL_CYCLES NUMBER AADF FOR PEDAL CYCLES.
RAMP NUMBER RAMP IS PRESENT OR NOT
ROAD_CATEGORY TEXT THE CLASSIFICATION OF THE ROAD TYPE.
SEQUENCE NUMBER
COUNT_POINT_ID NUMBER A UNIQUE REFERENCE FOR THE ROAD LINK THAT LINKS THE AADFS TO THE ROAD NETWORK.
ESTIMATION_METHOD_DETAILED TEXT THE DETAILED METHOD USED TO ESTIMATE THE AADF.
HGVS_2_RIGID_AXLE NUMBER AADF FOR TWO-RIGID AXLE HGVS.
HGVS_4_OR_MORE_RIGID_AXLE NUMBER AADF FOR FOUR OR MORE RIGID AXLE HGVS.
LATITUDE FLOAT LATITUDE OF THE CP LOCATION.
REGION_ID NUMBER WEBSITE REGION IDENTIFIER
ROAD_TYPE TEXT WHETHER THE ROAD IS A ‘MAJOR’ OR ‘MINOR’ ROAD.
HGVS_3_RIGID_AXLE NUMBER AADF FOR THREE-RIGID AXLE HGVS
HGVS_6_ARTICULATED_AXLE NUMBER AADF FOR SIX-ARTICULATED AXLE HGVS.
ID TEXT PRIMAR KEY
LINK_LENGTH_KM FLOAT TOTAL LENGTH OF THE NETWORK ROAD LINK FOR THAT CP (IN KILOMETERS).
BUSES_AND_COACHES NUMBER AADF FOR BUSES AND COACHES
ALL_MOTOR_VEHICLES NUMBER AADF FOR ALL MOTOR VEHICLES.
LGVS NUMBER AADF 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_NAME DATA_TYPE DESCRIPTION
BUSES_AND_COACHES NUMBER COUNTS FOR BUSES AND COACHES
HGVS_3_OR_4_ARTICULATED_AXLE NUMBER COUNTS FOR THREE OR FOUR-ARTICULATED AXLE HGVS.
YEAR NUMBER COUNTS ARE SHOWN FOR EACH YEAR FROM 2000 ONWARDS.
LONGITUDE FLOAT LONGITUDE OF THE CP LOCATION.
COUNT_DATE TIMESTAMP_NTZ THE DATE WHEN THE ACTUAL COUNT TOOK PLACE.
HGVS_6_ARTICULATED_AXLE NUMBER COUNTS FOR SIX-ARTICULATED AXLE HGVS.
ROAD_TYPE TEXT WHETHER THE ROAD IS A ‘MAJOR’ OR ‘MINOR’ ROAD.
HOUR NUMBER THE TIME WHEN THE COUNTS IN QUESTIONS TOOK PLACE WHERE 7 REPRESENTS BETWEEN 7AM AND 8AM, AND 17 REPRESENTS BETWEEN 5PM AND 6PM.
RAMP NUMBER RAMP IS PRESENT OR NOT
HGVS_2_RIGID_AXLE NUMBER COUNTS FOR TWO-RIGID AXLE HGVS.
LGVS NUMBER COUNTS FOR LGVS
LINK_LENGTH_KM FLOAT TOTAL LENGTH OF THE NETWORK ROAD LINK FOR THAT CP (IN KILOMETERS).
LINK_LENGTH_MILES FLOAT TOTAL LENGTH OF THE NETWORK ROAD LINK FOR THAT CP (IN MILES).
PEDAL_CYCLES NUMBER COUNTS FOR PEDAL CYCLES.
SEQUENCE NUMBER
END_JUNCTION_ROAD_NAME TEXT THE ROAD NAME OF THE END JUNCTION OF THE LINK
HGVS_3_RIGID_AXLE NUMBER COUNTS FOR THREE-RIGID AXLE HGVS.
REGION_ID NUMBER WEBSITE REGION IDENTIFIER.
ROAD_NAME TEXT THIS IS THE ROAD NAME (FOR INSTANCE M25 OR A3).
COUNT_POINT_ID NUMBER A UNIQUE REFERENCE FOR THE ROAD LINK THAT LINKS THE AADFS TO THE ROAD NETWORK.
DIRECTION_OF_TRAVEL TEXT DIRECTION OF TRAVEL.
START_JUNCTION_ROAD_NAME TEXT THE ROAD NAME OF THE START JUNCTION OF THE LINK
EASTING NUMBER EASTING COORDINATES OF THE CP LOCATION.
HGVS_4_OR_MORE_RIGID_AXLE NUMBER COUNTS FOR FOUR OR MORE RIGID AXLE HGVS.
LOCAL_AUTHORITY_ID NUMBER WEBSITE LOCAL AUTHORITY IDENTIFIER.
ALL_HGVS NUMBER COUNTS FOR ALL HGVS.
ALL_MOTOR_VEHICLES NUMBER COUNTS FOR ALL MOTOR VEHICLES.
LATITUDE FLOAT LATITUDE OF THE CP LOCATION.
TWO_WHEELED_MOTOR_VEHICLES NUMBER COUNTS FOR TWO-WHEELED MOTOR VEHICLES.
ROAD_CATEGORY TEXT THE CLASSIFICATION OF THE ROAD TYPE .
CARS_AND_TAXIS NUMBER COUNTS FOR CARS AND TAXIS.
HGVS_5_ARTICULATED_AXLE NUMBER COUNTS FOR FIVE-ARTICULATED AXLE HGVS.
NORTHING NUMBER NORTHING COORDINATES OF THE CP LOCATION.
ID NUMBER PRIMAR 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_NAME DATA_TYPE DESCRIPTION
ALL_MOTOR_VEHICLES FLOAT ANNUAL ROAD TRAFFIC ESTIMATE FOR ALL MOTOR VEHICLES IN THE GIVEN LOCAL AUTHORITY
LINK_LENGTH_MILES FLOAT TOTAL LENGTH OF THE NETWORK ROAD LINK FOR THAT LOCAL AUTHORITY (IN MILES).
LOCAL_AUTHORITY_NAME TEXT THE NAME OF THE LOCAL AUTHORITY.
LINK_LENGTH_KM FLOAT TOTAL LENGTH OF THE ROAD NETWORK ROAD LINK FOR THAT LOCAL AUTHORITY (IN KILOMETERS).
REGION_ID NUMBER WEBSITE REGION IDENTIFIER
CARS_AND_TAXIS FLOAT ANNUAL ROAD TRAFFIC ESTIMATE FOR CARS AND TAXIS IN THE GIVEN REGION AND ROAD CATEGORY.
ID NUMBER IPRIMAR KEY
LOCAL_AUTHORITY_ID NUMBER WEBSITE LOCAL AUTHORITY IDENTIFIER.
ONS_CODE TEXT THE OFFICE FOR NATIONAL STATISTICS CODE IDENTIFIER FOR THE LOCAL AUTHORITY.
YEAR NUMBER TRAFFIC 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_NAME DATA_TYPE DESCRIPTION
BUSES_AND_COACHES FLOAT ANNUAL ROAD TRAFFIC ESTIMATE FOR BUSES AND COACHES IN THE GIVEN REGION AND ROAD CATEGORY.
ID NUMBER PRIMAR KEY
TOTAL_LINK_LENGTH_KM FLOAT TOTAL LENGTH OF THE ROAD NETWORK ROAD LINK FOR THAT REGION (IN KILOMETERS).
YEAR NUMBER TRAFFIC ESTIMATES ARE SHOWN FOR EACH YEAR FROM 1993 ONWARDS.
ALL_MOTOR_VEHICLES NUMBER ANNUAL ROAD TRAFFIC ESTIMATE FOR ALL MOTOR VEHICLES IN THE GIVEN REGION AND ROAD CATEGORY.
ONS_CODE TEXT THE OFFICE FOR NATIONAL STATISTICS CODE IDENTIFIER FOR THE REGION.
REGION_ID NUMBER WEBSITE REGION IDENTIFIER
LGVS NUMBER ANNUAL ROAD TRAFFIC ESTIMATE FOR LGVS IN THE GIVEN REGION AND ROAD CATEGORY.
TWO_WHEELED_MOTOR_VEHICLES FLOAT ANNUAL ROAD TRAFFIC ESTIMATE FOR TWO-WHEELED MOTOR VEHICLES IN THE GIVEN REGION AND ROAD CATEGORY.
TOTAL_LINK_LENGTH_MILES FLOAT TOTAL LENGTH OF THE NETWORK ROAD LINK FOR THAT REGION (IN MILES).
ALL_HGVS FLOAT ANNUAL ROAD TRAFFIC ESTIMATE FOR ALL HGVS IN THE GIVEN REGION AND ROAD CATEGORY.
CARS_AND_TAXIS NUMBER ANNUAL ROAD TRAFFIC ESTIMATE FOR CARS AND TAXIS IN THE GIVEN REGION AND ROAD CATEGORY.
PEDAL_CYCLES FLOAT ANNUAL 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_NAME DATA_TYPE DESCRIPTION
REGION_ID NUMBER WEBSITE REGION IDENTIFIER
TOTAL_LINK_LENGTH_MILES FLOAT TOTAL LENGTH OF THE NETWORK ROAD LINK FOR THAT REGION (IN MILES).
YEAR NUMBER TRAFFIC ESTIMATES ARE SHOWN FOR EACH YEAR FROM 1993 ONWARDS.
ROAD_CATEGORY_ID NUMBER THE CLASSIFICATION OF THE ROAD TYPE (SEE DATA DEFINITIONS FOR THE FULL LIST).
TOTAL_LINK_LENGTH_KM FLOAT TOTAL LENGTH OF THE ROAD NETWORK ROAD LINK FOR THAT REGION (IN KILOMETERS).
ALL_MOTOR_VEHICLES FLOAT ANNUAL ROAD TRAFFIC ESTIMATE FOR ALL MOTOR VEHICLES IN THE GIVEN REGION AND ROAD CATEGORY
ID NUMBER PRIMAR KEY
ONS_CODE TEXT THE OFFICE FOR NATIONAL STATISTICS CODE IDENTIFIER FOR THE REGION.

Road types

Category Category Description
PM M or Class A Principal Motorway
PA Class A Principal road
TM M or Class A Trunk Motorway
TA Class A Trunk road
M Minor road
MB Class B road
MCU Class C road or Unclassified road

Direction of flow

Category Category Description
N North
S South
E East
W West
C Combined (flows separated by the direction of travel unavailable)

Types of vehicle

Category Category Description
All_MV All Motor Vehicles
2WMV Two-wheeled motor vehicles (e.g. motorcycles etc)
Car Cars and Taxis
LGV Light Goods Vans
HGV Heavy Goods Vehicle total
HGVR2 2-rigid axle Heavy Goods Vehicle
HGVR3 3-rigid axle Heavy Goods Vehicle
HGVR4 4 or more rigid axle Heavy Goods Vehicle
HGVA3 3 and 4-articulated axle Heavy Goods Vehicle
HGVA5 5-articulated axle Heavy Goods Vehicle
HGVA6 6 or more articulated axle Heavy Goods Vehicle
PC Pedal 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.