On the walk to nursery this morning my wife and kids were startled by a car that drove onto the pavement within a foot of them. After reporting the offence via Operation Snap I spotted all of the historic offence data which was just begging to be liberated from its PDF prison and presented in a more accessible and open format.
I (read: Copilot) grabbed the data from Jan 2025 to now, normalised it, converted the free-text locations (street names, motorway junctions, etc) into GPS coordinates, and piped all that data into Datasette.
I'd not come across Datasette before, but it's a pretty lightweight way of taking an SQLite db and opening it up to be explored via predefined views, or allowing users to build out their own queries and views, and even re-export the data in a format of their own choosing. Basically, it does all of the hard work, all I did was scrape and normalise the data.
I'll endeavour to keep this updated going forward when West Midlands Police release new data.
In the meantime, take a guess at which make of car causes the most offences. Surprisingly, it's not Audi! Answer here.