Road safety research · SQL
Road Collision Trends and Engineering Review
A road-safety analyst needs to decide where a highways engineer should inspect next. The answer must connect five final years of collision records to a location and a specific question, without pretending that a count proves cause.
- Final years
- 2020–2024
- Collisions
- 503,475
- Vehicle records
- 920,692
- Casualty records
- 640,522
The decision
Which location deserves an engineering inspection first?
The road-safety analyst prepares the evidence for a transport policy lead, police data liaison and highways engineer. An annual total is useful for scale, but it cannot show whether severe casualties keep appearing in the same area or what conditions an engineer should inspect there.
The build therefore keeps collision, vehicle and casualty records separate, compares years on a consistent severity measure and produces a transparent inspection order. The engineer, not the model, decides what happens next.
Three linked record types
One collision can contain several vehicles and several casualties.
The SQL model declares the row meaning before it calculates a total. This protects the national trend and the one-collision trace from duplication.
Five-year comparison
The latest movement is smaller for severe casualties than for casualties overall.
The published adjusted measure accounts for changes in the way police forces record non-fatal severity. It is kept separate from the reported fatal, serious and slight categories.
The 2020 comparison is retained but treated cautiously because travel patterns were disrupted during the pandemic.
Road-user comparison
Improvements are not shared evenly across road users.
The 2024 adjusted count was lower for pedestrians and cyclists but slightly higher for car and taxi occupants and motorcyclists. These are counts from reported collisions, not rates per journey.
When records concentrate
Friday at 16:00 has the largest adjusted severe-casualty count.
Across the five final years, the weekday and hour combination contributes 1,853.81 adjusted severe casualties. The comparison helps shape a review window; it does not show which hour is most dangerous without exposure data.
Transparent inspection order
Recent severe casualties and persistence identify a first place to inspect.
The method groups records into one-kilometre cells, keeps only areas with reported severe casualties in all five years and orders them by adjusted severe casualties in 2022–2024. It does not label the area safe or unsafe.
Fatalities count as one. Non-fatal casualties use the Department for Transport's published adjustment weights so recording changes do not masquerade as a trend.
The order has no traffic, walking or cycling denominator. It compares recorded casualty counts and persistence, not the chance of a collision.
E529000_N180000 comes first after recent adjusted severe casualties, fatalities and five-year persistence are ordered as separate fields. It is not a composite risk score.
One collision retained
A Christmas night collision becomes a precise site question.
Collision 2024010566856 was recorded on the A401 at 00:47 on 25 December 2024. One vehicle · four pedestrians are linked to the same collision record. One casualty was fatal, two were serious and one was slight.
- 01Reported collision
2024-12-25 at 00:47 · A401 · 20 mph · DfT final collision file
PASS · reviewed by Police data liaison - 02Vehicle link
1 car or taxi record joined on collision reference 2024010566856
PASS · reviewed by Road-safety analyst - 03Casualty links
4 casualty records · fatal pedestrian, age 25 · slight pedestrian, age 22 · serious pedestrian, age 57 · serious pedestrian, age 57
PASS · reviewed by Road-safety analyst - 04Severity check
3 reported KSI casualties · 3.0 adjusted KSI contribution · no fallback rows
PASS · reviewed by Road-safety analyst - 05Review-cell result
E529000_N180000 · inspection order 1 · reported KSI casualties present in all five retained years
PASS · reviewed by Highways engineer - 06Human decision
Retain the cell for inspection and ask a highways engineer to review the multi-arm junction, crossing paths and night-time sightlines.
PASS · reviewed by Road-safety analyst
The trace is retained in collision_record_trace.csv and reproduces with npm run verify:road-collision-research. The records justify inspection; they do not establish why the collision happened or which intervention would work.
Source, period and decision boundary
Useful for choosing an inspection, not for declaring a cause.
This research uses final Great Britain STATS19 records from 1 January 2020 to 31 December 2024. Provisional 2025 records are excluded.
Contains public sector information from Department for Transport road safety open data, licensed under the Open Government Licence v3.0. Quanta Meridian created the SQL model, validation and outputs.
These are police-reported personal-injury collisions on public roads. Unreported collisions and some non-fatal casualties are not present; 65 collisions without usable coordinates remain in national totals but not the spatial review.
A highways decision needs traffic and road-user exposure, network context, near-miss evidence and a site inspection. This project claims no causal explanation, chosen intervention or measured outcome.
Executable evidence
The shortlist is a SQL result, not a value typed into the page.
The retained build downloads the permitted final files, checks their checksums, types the three record sets, runs the model and writes the website figures from the same outputs.
Original SQL · sql/examples/02_location_review_shortlist.sql
-- Question:
-- Which one-kilometre areas combine recent severe casualties with a pattern
-- that has appeared in more than one final reporting year?
SELECT
review_order,
location_grid_id,
easting_1km,
northing_1km,
ROUND(longitude, 5) AS longitude,
ROUND(latitude, 5) AS latitude,
collision_count,
casualty_count,
fatal_casualties_all_years,
years_with_reported_ksi,
ROUND(latest_three_year_adjusted_ksi, 2)
AS latest_three_year_adjusted_ksi,
latest_three_year_fatalities,
ROUND(latest_year_adjusted_ksi_change, 2)
AS latest_year_adjusted_ksi_change,
adjustment_fallback_rows
FROM mart.location_review
ORDER BY review_order, location_grid_id
LIMIT 25;Keys, relationships, dates, counts and adjusted totals reconcile.
They remain in national totals but cannot enter the location review.
The retained public outputs match the verified build.
Technical documentationInspect the build command, retained files, data licence and analytical limits
Build
DuckDB 1.4.3 runs the ordered SQL files across raw, staging, model, mart and audit schemas. The clean-checkout verifier checks the retained CSV and JSON outputs without requiring the downloaded raw files.
Provenance
Contains Department for Transport road safety data licensed under the Open Government Licence v3.0. The analysis uses final records for 2020–2024 and excludes provisional 2025 data.
Boundary
Police-reported collision counts omit unreported incidents and are not adjusted for traffic, walking or cycling exposure. A highways decision requires network, traffic, near-miss and site evidence.
Bring the question
Turn a public or operational dataset into an inspectable decision.
Start with the people making the decision, the records they can use and the evidence still needed before action.