Joining the Datasets

1 Why joining is the hard part

The model target — collision rate per road link per year — does not exist in any single dataset. It has to be constructed by joining three sources that were never designed to be combined:

  • STATS19 collisions are points with GPS coordinates and a police-recorded road name.
  • AADF traffic counts are points at DfT count-point locations, keyed by count_point_id.
  • WebTRIS sensors are points on the National Highways network, keyed by site_id.
  • OS Open Roads links are line geometries with a link_id and a road_classification.

No shared key links them. Every connection has to be inferred from spatial proximity, road names, or both.

Important

The joining stage is where most of the signal is gained or lost. A collision snapped to the wrong road changes both its traffic denominator and the road attributes it inherits. Under-reporting aside, snap quality is the dominant source of noise in the final rates.

2 What this page answers

  1. What is being joined, and in what order?
  2. How are collisions matched to road links, given that two collisions 10m apart might belong to an A-road and a parallel B-road?
  3. How are traffic counts attached to road links when AADF count points are sparse and WebTRIS sensors are even sparser?
  4. What happens to data beyond the distance caps, and why not just pick the nearest match?
  5. How is match confidence tracked and used?

3 Pipeline overview

The joining pipeline runs in five stages, implemented across clean.py, snap.py, and join.py:

Stage Input Output Join method
1. Clean STATS19 Raw collision CSVs Validated collisions with road_name_clean LSOA coordinate check
2. Snap collisions to links Collisions + OS Open Roads Collisions with link_id + snap_score Weighted multi-criteria
3. Attach WebTRIS to AADF AADF + WebTRIS AADF with sensor features Spatial nearest (5 km cap)
4. Attach AADF to road links Road links + (AADF + WebTRIS) Road features per link × year Spatial nearest (2 km cap)
5. Aggregate to link × year Snapped collisions + road features road_link_annual.parquet Key join on link_id × year

Each stage preserves confidence information — snap scores, distances, join methods, availability flags — so the feature engineering can filter on quality.

4 Stage 1: STATS19 cleaning

Collision cleaning happens in clean_stats19() and does three things that matter for the joins downstream.

4.1 Coordinate validation

Police-recorded lat/lon is generally reliable across Yorkshire forces, but a small number of records have systematic errors. The pipeline validates each collision’s coordinates against its recorded LSOA centroid using haversine distance:

  • Collisions more than 10 km from their LSOA centroid are flagged as coords_suspect = True.
  • Records outside the GB bounding box are flagged as coords_valid = False.

Flagged records are not dropped — they remain available for non-spatial analysis — but they are excluded from the spatial snap.

Note

Spatial snapping uses STATS19 latitude / longitude, not location_easting_osgr / location_northing_osgr. A previous notebook suspected a Yorkshire BNG grid-square error, but a direct check against the current raw DfT STATS19 CSV found no systematic mismatch: Yorkshire BNG fields agree with lat/lon-derived BNG positions within a few metres.

4.2 Road name reconstruction

STATS19 encodes road classification and number separately: first_road_class (integer code) and first_road_number. These are reconstructed into a single string:

class=1, number=62    → "M62"
class=3, number=64    → "A64"
class=4, number=1234  → "B1234"
class=6               → ""   (unclassified, no named road)

The result is stored as road_name_clean and feeds one of the four scoring dimensions in Stage 2.

4.3 COVID flag

is_covid = True for collisions in 2020 or 2021. Carried through all downstream tables so the modelling code can optionally exclude or separately model these years.

6 Stage 3: WebTRIS → AADF

WebTRIS sensors are point locations on motorways and major A-roads. AADF count points are also points. Neither has a shared ID — the link is spatial.

_attach_webtris_to_aadf() runs a per-year nearest-neighbour join from AADF points to WebTRIS sites using geopandas.sjoin_nearest with max_distance = 5000 m. Beyond 5 km, WebTRIS features are nulled rather than retaining the nearest-but-far match.

The per-year scoping matters: a 2019 WebTRIS reading only attaches to the 2019 AADF row, not to adjacent years. This keeps temporal alignment clean but means WebTRIS columns are NaN for AADF years outside the WebTRIS pull window (2019, 2021, 2023).

6.1 Why nullify beyond the cap?

An alternative would be to keep the nearest WebTRIS match regardless of distance. This is rejected because:

  • Beyond 5 km, the sensor and the count point are likely on different corridors with different traffic composition.
  • A “nearest” match that is actually irrelevant is worse than missing data — it adds noise that looks like signal to the model.

NaN is the honest representation of “no nearby sensor”.

9 Quality tracking

Several confidence fields propagate from the joining pipeline into the feature table:

Field Meaning
snap_score Composite score 0–1 from weighted snap
score_spatial, score_class, score_junction, score_number Per-dimension breakdown for diagnostics
snap_method weighted / spatial / unmatched / invalid_coords
snap_distance_m Metres from collision to snapped link
pct_attribute_snapped Per link-year share matched via legacy attribute path
aadf_snap_distance_m Metres from link centroid to matched count point
aadf_join_method spatial or name_match
aadf_available, webtris_available Boolean flags for downstream filtering

The per-dimension scores are particularly useful for diagnosing where the weighted snap is making compromises — a link-year with mean(score_spatial) = 0.9 and mean(score_class) = 0.4 is saying “I’m confident about the geometry but the attribute match is weak”, which is exactly the kind of signal that should reduce confidence in any rate derived from those collisions.

10 Quick-vs-weighted comparison

compare_snaps() runs both methods on the same collisions and reports agreement rate, per-link-id disagreements, and weighted-only matches. This is the standard QA step when changing any scoring parameter — the diff against snap_quick tells you what the scoring is actually doing relative to pure proximity.

Typical observations from the Yorkshire pilot:

  • Agreement is high on motorways and major A-roads (separated carriageways aside), where the nearest link is almost always the correct one.
  • Disagreement concentrates on urban areas with parallel A / B / minor roads, and at junctions where slip roads sit close to the main carriageway.
  • Weighted-only matches occur when the nearest link is beyond the quick-snap 500 m cap but within 500 m of a same-class alternative — these are rare but legitimate.

11 Known issues and tradeoffs

11.1 Weights are fixed, not learned

The 40/25/25/10 split across spatial/class/junction/number is a design choice. No formal optimisation has been run against a labelled validation set. The weights feel broadly right — spatial dominates, attributes act as a tiebreaker, road number is down-weighted because of its known quality issues — but this is engineering judgement, not a fitted parameter.

11.2 Distance caps are empirical

The 100 m half-life, 500 m search radius, 2 km AADF cap, and 5 km WebTRIS cap are all engineering choices. Tighter caps increase precision but reduce coverage. No sensitivity analysis has been run.

11.3 Error compounding across stages

Joins are chained: WebTRIS→AADF→link. A 4 km WebTRIS match plus a 1.8 km AADF match means the WebTRIS sensor is effectively 5+ km from the road link. The individual caps don’t bound the total separation. In practice this mostly affects minor roads that already have aadf_available = False, so the compounding is concentrated where the data is least used anyway.

11.4 Road name normalisation is strict

road_name_clean is an uppercased whitespace-stripped match. “M62” matches “M 62” (after cleaning) but not “M062” or “M62 Westbound”. Non-standard road name encoding silently fails the road number score — it falls through to the spatial + class + junction components, which usually still yield a correct match but with a lower composite score.

11.5 Dual carriageways

OS Open Roads represents dual carriageways as two separate links. Nearest-neighbour can snap a collision to the wrong carriageway, particularly on motorways where GPS drift + narrow carriageway separation produce ambiguous cases. The weighted snap does not distinguish between carriageways — both have the same road_name_clean and usually the same road_classification and form_of_way, so the composite score is nearly tied.

11.6 Coordinate-source limitations

STATS19 lat/lon is reliable but not perfectly accurate — drift of 20–50 m is normal. The 100 m spatial half-life was chosen with this in mind: a correct match at 50 m still scores 0.71, which is high enough to dominate if the attribute scores are at least neutral.

12 Next steps

The joined table feeds into:

  • features.py — rate calculation, log-transforms, temporal features, lag features.
  • model/collision.py — regression on collision_rate_per_mvkm.