Data Tooling

The initial part of Phase II of the project focusses on the concept of peer hex identification: given a hex cell, can we identify, nationally, other cells that are very similar in terms of built-environment and sociodemographic features? The cells are small so there are a lot of them, and a lot of features.

We've put a lot of thought over the last few months into how best to build the necessary datasets and implement this. We've homed in on a very flexible and efficient way of sharing this data (subject to licencing and confidentiality restrictions).

We've also built an automated ETL (extract-transform-load) process to build these datasets. This post describes what goes into it, how it works, and how others can get at the data.

What data?

Currently the pipeline pulls together over 20 datasets covering England & Wales. Most are open data and are downloaded automatically; a few are licensed and have to be downloaded manually (by someone with the appropriate licence).

Two caveats worth highlighting:

• Street lights and CCTV come from OpenStreetMap, where coverage of street furniture is excellent in some areas and entirely absent in others (often the result of a one-off import of a council's asset register). Nationally we only have ~129k street lights, concentrated in a handful of well-mapped areas - most cells report zero not because they are unlit, but because nobody has tagged the lights. These are best treated as an indicative signal only. Ordnance Survey has an authoritative street lighting layer which we'll switch to if our licence permits publishing aggregates derived from it.
• Police data: Greater Manchester Police do not currently supply public crime data, and ~1.5% of recorded crimes have no location, so can't be placed on a map.

The tooling

Requirements:

• automated and configurable: a single command should rebuild everything, and it should be straightforward to add a new dataset or aggregation.
• fast: building the features shouldn't take hours, even when intensive spatial joins are required.
• flexible: users should be able to take the data at whatever level they need - raw features, per-cell counts, or pre-joined cell characteristics - and on a variety of spatial units.
• accessible: the outputs should be usable from python, R, the browser, or an SQL database, without requiring a database server.
• automatically restrict data as necessary: licensed and confidential data must never end up somewhere it shouldn't, and this shouldn't rely on someone remembering not to upload it.
• optional for most users: the vast majority of users should never need to run the tooling themselves - they can simply access the published data (see below).

The tooling lives in the safer-streets-tooling repo and builds on our safer-streets-core package. At its heart is duckdb with the spatial and h3 extensions, and every output is a parquet or GeoParquet file.

An asynchronous pipeline

Both the extract and transform phases are built on the same small, generic framework: a pipeline of asynchronous nodes, wired together by their dependencies. Each node is a unit of work - downloading and processing one dataset, or computing one aggregation - and declares the nodes it depends on simply by naming them as (keyword-only) arguments to its execute method. A simplified example:

class Schools(AsyncNode):
    async def execute(self, *, open_roads: Result[Path]) -> Result[Path]:
        # only runs once open_roads has completed, and receives its result
        ...

The pipeline assembles the nodes into a directed acyclic graph (using the standard library's graphlib) and then launches all of them at once with asyncio. Each node waits until its dependencies have produced their results, then runs. The actual work - downloads, and CPU-intensive duckdb queries - is blocking, so it's handed off to a worker thread, which means that anything which doesn't depend on something else runs in parallel, with no need for anyone to work out a sensible running order by hand. Adding a new dataset or aggregation is just a matter of writing a new node and registering it; where it fits in the graph follows from its arguments.

The whole framework - pipeline, node and result types - is under 150 lines of python, uses nothing beyond the standard library, and knows nothing about crime or geography, so it could be lifted into any project that needs to run a set of interdependent tasks.

Errors are handled explicitly: every node returns a Result, which is either Ok (with a value) or Err (with the exception that was raised). A failure in one node doesn't bring down the others mid-flight - when the pipeline has finished, the results are inspected and the build either stops (if something essential failed) or reports what was skipped and carries on.

In the transform phase all the steps share a single in-memory duckdb database, with each step working through its own cursor so concurrent steps don't trip over each other. duckdb is itself heavily multithreaded, so its thread count and memory usage are capped (spilling to disk if necessary) - running several large spatial joins at once on a national dataset will otherwise happily consume all the memory on a machine.

Extract

Each dataset has its own small module which downloads the source (caching the raw files locally), cleans it up, reprojects to British National Grid, and writes out a single parquet file. Every point dataset (buildings, schools, POIs, transport stops, food outlets, street lights and CCTV) is also tagged with the id of the cell it falls in on each of our grids, so later on it can be aggregated with a simple GROUP BY rather than an expensive spatial join.

The extracts run concurrently in the pipeline described above - for example schools need the road network in order to compute their walking catchments, so they wait for it, but otherwise everything downloads and processes in parallel. Each dataset is cached independently, so refreshing one (e.g. this month's crime data) doesn't mean rebuilding everything. The crime extract checks whether police.uk has published a newer archive since it last downloaded, and fetches it only if so.

Datasets are either required (crime and boundaries - the build stops if these fail) or optional (everything else - the build carries on without them), which also means that someone without access to the licensed data can still run the pipeline and get everything else.

Transform

The extracted parquet files are loaded into a throwaway in-memory duckdb instance, and a second dependency graph of steps aggregates the data onto two hexagonal grids:

• H3 resolution 9 hexes (~0.1km²), the global standard.
• BEAHIV 202m hexes, our own equal-area hexagonal grid native to British National Grid, which we introduced here. These are almost exactly the same size as H3 resolution 9, so the two can be compared like-for-like.

For each grid the outputs are:

• counts per cell: crimes (by type and month), street lights, buildings (by use), road junctions, and residential and workplace population. Census populations are only published per output area, so we disaggregate them onto cells by distributing each area's population across its buildings, in proportion to floor area and weighted by building use.
• lookups giving each cell's overlap with greenspace, urban and suburban land cover, and the road network, and its nearest retail centre.
• geogs: one row per cell with everything above folded in, plus the code of every ONS geography (OA, LSOA, MSOA, local authority, police force) it falls in.

Crime counts are also produced for each of the ONS geographies directly.

Load

There's no database to load into - the parquet files are the deliverable. Alongside them we write a catalogue, index.parquet, with one row per table giving its name, a description, its size and schema, and when it was last built. The whole lot is then synced to an Azure blob storage container.

This is where the restrictions are enforced. Data that mustn't be shared is flagged as local-only, and is then automatically excluded from the sync in both directions, along with anything derived from it - so a new derived table added in future is excluded without anyone having to remember to do so.

Everything is driven by a single command line tool:

uv run data build                    # extract anything missing, then transform
uv run data extract --only crime_data # refresh a single dataset
uv run data transform --grid beahiv  # rebuild just one grid's outputs
uv run data sync --update newer      # two-way sync with the cloud

Accessing the data

Most users will never actually need to use the safer-streets-tooling package, they can simply read the data from the cloud.

There are a few different ways of getting at the data, depending on who you are and what you need. Parquet files can be read directly into R or python (e.g pandas and geopandas). We have found duckdb to be far more performant (python and R packages available) but requires some knowledge of SQL.

Querying directly from the cloud

This requires a connection string, which we can supply. From pandas, you can load directly, e.g:

# requires: pandas, pyarrow, fsspec, adlfs

df = pd.read_parquet(
    "az://phase2/extract/poi.parquet",
    storage_options={"connection_string": AZURE_STORAGE_CONNSTR},
)

But if you're comfortable with SQL, there's no need to download the entire file at all: duckdb's azure extension can query the parquet files in place, and because parquet is a columnar format with row-group statistics, only the columns and rows a query actually needs are fetched. For example, to get total violent crime per H3 cell alongside the cell's LSOA and deprivation score:

INSTALL azure; LOAD azure;
SET azure_storage_connection_string = '...';

SELECT hex.spatial_id, hex.lsoa21cd, imd.imd_score, SUM(crime.count) AS crimes
FROM read_parquet('az://phase2/transform/h3r9_crime_counts.parquet') crime
JOIN read_parquet('az://phase2/transform/h3r9_geogs.parquet') hex USING (spatial_id)
LEFT JOIN read_parquet('az://phase2/extract/imd_scores_pct.parquet') imd ON hex.lsoa21cd = imd.spatial_id
WHERE crime.crime_type = 'Violence and sexual offences'
GROUP BY ALL
ORDER BY crimes DESC;

The same query works from python, R, or the duckdb command line. Reading index.parquet first is a good way to see what's available.

Syncing a local copy

For heavier work it may be faster to keep a local copy, although we haven't seen much difference in performance between local and cloud files. uv run data sync --update newer does a two-way sync with the container, only transferring files that have changed, and the same queries then run against local paths instead.

In the browser

duckdb also runs in the browser (via WebAssembly), which is how our Crime Capture Explorer works: it queries the parquet files directly from blob storage with no server in between. Here access is granted using short-lived, read-only tokens, each scoped to a single file, and only to the open-data tables the app needs - never to the whole container, which also holds licensed data. See here for more on the different ways we host apps.

Building it yourself

The tooling is open source (MIT licence), so anyone can run the pipeline themselves. The open datasets download automatically (you'll need a free nomis API key for the residential population), and the licensed ones can be added if you have your own licence - just drop the downloaded files into the data directory. Any that are missing are simply skipped. See the README for details.

If you're interested in working with the data, get in touch.

Reference

• safer-streets-tooling
• safer-streets-core
• beahiv
• duckdb, and its spatial, h3 and azure extensions
• GeoParquet