Version 0.1.2

Release notes for version 0.1.2

Amended impact scores model with normalisation improvements.

Impact Scores Model

Overview

This model calculates and normalises impact scores for road works across England's highway network.

It combines permit data with traffic and infrastructure metrics to produce weighted impact scores that reflect both the direct impact of works and the broader network.

Input Data Sources

1. Permit Data

   • In-progress works (in_progress_list_england)
   • Completed works (completed_list_england)
   • Key fields: USRN, street name, highway authority, work category, TTRO requirements, traffic sensitivity, traffic management type

2. Infrastructure Data

   • UPRN-USRN mapping (uprn_usrn_count)
   • DFT Local Authority data (dft_la_data_latest) contains road length and traffic flow information

Impact Score Calculation

Base Impact Factors

• Work Category Impact (0-5 points)

  • Major works: 5 points
  • Immediate works: 4 points
  • Standard works: 2 points
  • Minor works: 1 point
  • etc

• Additional Impact Factors

  • TTRO Required: +0.5 points
  • Traffic Sensitive: +0.5 points
  • Traffic Management Impact: +0-2 points based on severity
  • UPRN Density Impact: +0.2-1.6 points based on UPRN point density on a USRN

Network Context Adjustment

The model applies a network importance factor based on:

• Total road length in the authority
• Traffic flow data (2023)
• Traffic density per km (length/flow)
• Normalised network importance factor (0-1 scale)

Output

The final model produces a table with:

• Location identifiers (USRN, street name, highway authority)
• Raw impact scores
• Network metrics (road length, traffic flow, density)
• Final weighted impact scores that account for both direct works impact and network importance

This model helps identify high-impact works areas by considering both the immediate disruption of works and their context within the broader road network.

Version 0.1.1

Added archive impact score model - this will build up impact scores over time

Version 0.1.0

Impact Scores v0.1.0

Overview

This dbt model calculates impact scores for street works across England by combining in-progress and completed works data. It factors in multiple impact variables including work category, traffic management requirements, and building density.

Impact Score Components

Work Category Base Scores

• Major: 5.0
• Standard: 2.0
• Minor: 1.0
• HS2 (Highway): 2.0
• Immediate (urgent/emergency): 3.0

Additional Impact Factors

• TTRO Required: +0.5
• Traffic Sensitive Street: +0.5

Traffic Management Impact

High Impact (+2.0):

• Road closure
• Contra flow
• Lane closure
• Convoy workings
• Multi-way signals
• Two-way signals

Medium Impact (+1.0):

• Give and take
• Stop/go boards
• Priority working

Low Impact (+0.5):

• Some carriageway incursion
• Unknown/NULL traffic management

No Impact (0.0):

• No carriageway incursion

Building Density Impact (UPRN Count)

≤5 UPRNs:    +0.3 (33.52% of streets)
≤10 UPRNs:   +0.4 (11.04% of streets)
≤25 UPRNs:   +0.5 (23.20% of streets)
≤50 UPRNs:   +0.7 (16.51% of streets)
≤100 UPRNs:  +0.9 (10.11% of streets)
≤200 UPRNs:  +1.1 (4.01% of streets)
≤500 UPRNs:  +1.3 (1.35% of streets)
>500 UPRNs:  +1.5 (0.26% of streets)

Model Structure

1. Combines in-progress and completed works using UNION ALL
2. Joins with UPRN counts per street
3. Calculates impact scores based on multiple factors
4. Aggregates total impact per street (USRN)

Output Columns

• usrn
• street_name
• highway_authority
• uprn_count
• geometry
• total_impact_level
• date_processed (timestamp of calculation)

Notes

• Default to 0.5 impact for NULL traffic management types
• UPRN count uses COALESCE to default to 0 when no data
• Impact scores are summed per USRN to get total street impact