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.