The Boundary Moved, the Report Didn't: Modelling UK Places Over Time

Every UK team eventually gets the ticket. A report from 2021 puts a site in one council area; today the same site sits in another. Someone checks the address, finds the postcode unchanged, and asks why the database is wrong. Usually it is not wrong. The geography moved. The report is doing exactly what it was told: applying today's map to yesterday's question.

Postcodes are routing instructions, not polygons. Local authority names are labels. ONS area codes are identifiers tied to a particular vintage of a dataset. None of these are eternal facts. They split, merge, retire, get superseded and occasionally reappear in a form that makes a naive latest join look sensible right up until it is not. If your schema treats geography as a single current value on an address row, you have built a system that can only answer today's questions. That is fine until someone asks about last year.

Treat current as a query, not a column

A useful first move is to stop thinking of local_authority as a property of an address and start thinking of it as a claim: this address was associated with this geography code, at this level, according to this source, for this period. The address is the thing. The geography link is an observation.

A compact schema might look like this:

create table geography_claim (
  address_id bigint not null,
  geography_level text not null, -- postcode, lsoa, local_authority
  geography_code text not null,
  valid_from date not null,
  valid_to date not null default 'infinity',
  source_dataset text not null,
  source_vintage date not null,
  recorded_at timestamptz not null default now(),
  reason text
);

Use half-open intervals: valid_from is included, valid_to is excluded. A claim that starts on 1 April 2023 and is still open has valid_to = 'infinity'. A claim that ends on 31 March 2024 stores valid_to = '2024-04-01'. This sounds fussy until you have two boundary changes on adjacent dates and a report that includes both. Half-open intervals remove the off-by-one-day argument before it starts.

If you are on PostgreSQL, an exclusion constraint over address_id, geography_level and a daterange can stop overlapping claims for the same address and level. Do not add it blindly. Some UK geographies are genuinely many-to-many, and postcode-level lookups can be messier than address-level ones. Add the constraint where the business rule is real, and test the exceptions explicitly.

Splits, merges and the awkward middle

A boundary review rarely produces a tidy one-for-one swap. A district can be abolished and its area split between two new unitary authorities. A parish can merge. A postcode can be retired while the addresses inside it are reallocated. The useful pattern is to close the old claim and open the new claims with the same effective date. Do not edit the old row. Do not delete it. History is the product.

For a split, that might mean one claim ending on 31 March 2023 and two claims beginning on 1 April 2023, each pointing at a different successor code. For a merge, several old claims close and one new claim opens. The report for March asks for claims where valid_from <= report_date and report_date < valid_to, so it gets the old world. The report for April gets the new one. Nobody has to remember which spreadsheet was current at the time.

This is also where labels need care. Store the code as the stable-ish identifier and keep names in a separate geography table with their own validity. Names change for reasons that have nothing to do with boundaries: a council rebrands, a spelling is corrected, a ceremonial name differs from the operational one. If you copy the name onto every address row, you will spend years explaining why half the estate says one thing and half says another.

Provenance belongs in the schema

A geography claim without provenance is a rumour with a date. Record the source dataset, the vintage, the licence if it matters to your organisation, and the transformation version that produced the row. If someone manually overrides a lookup, record who did it, when, and why. That is not bureaucracy; it is the difference between a fix and a mystery.

This matters most when you ingest ONS lookups, AddressBase products, OS Places data or a commercial postcode file. Each has a vintage. Each may use a best-fit method for awkward cases. None of them are the territory itself. They are snapshots with rules. Your schema should say which snapshot made the claim, not pretend the claim fell from the sky.

Change feeds, not nightly replacement

A nightly truncate and reload is the easiest way to lose history. It also makes late-arriving files dangerous. If an older vintage arrives after a newer one, a blind reload can move your current view backwards. A better pattern is a small change feed: compare the incoming file with what you already have, close claims that are no longer present, open claims that are new, and leave unchanged claims alone. Emit an event for each close and open, with the source vintage attached.

You do not need a grand event-sourcing platform for this. A table of geography changes, processed in vintage order, is enough for most teams. The important part is that the feed is auditable and idempotent. Replaying yesterday's file should not create duplicate claims or shift validity dates.

Tests for the cases UK teams actually meet

The 2023 local government reorganisation in Cumbria, North Yorkshire and Somerset is a good reminder that this is not theoretical. Teams with reports spanning that period needed old codes and new codes to coexist without one silently overwriting the other. Write tests that look like the tickets you actually get.

• A report dated before a boundary change returns the old code and old name.
• A report dated on the change date returns the new code, not both.
• A split produces closed parent claims and open child claims with no accidental gap.
• A retired code does not appear in the current view.
• A reused or reissued code returns the right label for the requested date.
• A late-arriving older vintage does not overwrite a newer claim.
• An unknown geography is stored with a reason, not quietly defaulted to a nearby code.

That last one matters. UK geography has edge cases around islands, cross-boundary postcodes and addresses that no lookup quite agrees on. A visible unknown with provenance is more honest than a confident wrong answer. Clean builds are not just about compilation. They are about systems that can explain themselves after the map has been redrawn.

The boundary will move again. The report should still make sense.