Contents

• gsscode 1.0
  • Format
  • Coverage
  • Parsing
  • Comparison and ordering
  • Partial matching
  • Text-compatibility shims
  • Rendering
  • Validity and reserved/NaN codes
  • Type names and descriptions
  • Refreshing gsscode_types from ONS

gsscode 1.0

UK ONS/GSS geography code encoded in 32 bits and optimised for indexing and prefix matches.

Format

A GSS code is 9 characters: <country:1><type:2><area:6>, eg E01000001 (an LSOA in England). Validated against the full live code_history table (511,305 real codes, 2026-08): every code currently in use is a single letter followed by 8 digits.

Coverage

Every field is a raw arithmetic passthrough of the source characters – there is no lookup table of valid country letters or type codes, and nothing to keep in sync as ONS adds new ones over time:

• country: 5 bits, letter - 'A' (0-25). 8 letters are in use as of 2026-08 (E, J, K, L, M, N, S, W); up to 32 are representable.
• type: 7 bits, the 2-digit type code verbatim (0-99).
• area: 20 bits, the 6-digit area code verbatim (0-999999).

Because nothing is enumerated, a new country prefix or type code ONS introduces tomorrow packs and unpacks correctly today, with no extension changes required.

Composite/cross-border entities (eg K02 “United Kingdom”, K03 “Great Britain”, K04 “England and Wales”) are ordinary type codes within their own country prefix, exactly like any other GSS code – not a derived union of the constituent countries' codes. There isn’t a bitwise relationship between eg E+W+S and K03; ONS encodes composites as their own distinct type-coded entries.

Parsing

Text input must be exactly 9 characters: one letter (case-insensitive) followed by 8 digits. No partial/fragmentary input, no separators – GSS codes are generated and consumed programmatically, not typed in by hand a character at a time.

Comparison and ordering

gsscode is stored as an unsigned 32-bit integer, so </>/etc. sort in country, then type, then area order – the same order as sorting the original text. Verified against a random 5,000-row sample of real codes: zero order mismatches between text sort and packed-integer sort.

Partial matching

The % and !% operators match a prefix: a bare country letter (‘E’), a country+type (‘E01’), or a full 9-character code. Any other length never matches – rather than raising an error, which would make these operators unsafe to use directly against untrusted/user-supplied input. % is registered in the type’s btree operator family, so it can drive an index scan directly:

SELECT * FROM areas WHERE gss % 'E01';   -- all English LSOAs
-- Index Scan using areas_gss_idx ...  Index Cond: (gss % 'E01'::text)

An array form matches against several prefixes in one call:

SELECT * FROM areas WHERE gss % ARRAY['E01','E02'];

Component accessors – country(gss), gss_type(gss), area(gss) – are also available when you need to filter or group by a field directly rather than by prefix.

Text-compatibility shims

~, ~*, !~, !~* and left(gsscode, n) are overloaded so that queries and reports written against the original plain-text column keep working unmodified once its type changes to gsscode:

WHERE gss ~* '^E03'
WHERE LEFT(gss, 3) = 'E03'

These render the packed value back to text and run the ordinary text operator/function – correct for any pattern, but a btree index on the gsscode column gives them no help; they cost the same as running the same query against the original text column would have. This is a hard limit of how PostgreSQL’s planner picks index scans (by the operator’s static registration in an opfamily, never by what a function does with a particular argument at runtime) – there’s no way to make a general regex operator index-eligible, since most patterns (character classes, alternation, anything unanchored) aren’t reducible to a single mask comparison the way a literal prefix is.

Two ways to get indexed speed back:

• Prefer % directly when the pattern really is just an anchored literal prefix: gss % 'E03' instead of gss ~* '^E03'.

• For LEFT(gss, n) = 'literal' queries specifically, a plain PostgreSQL expression index gets full index-scan speed with no extension code involved, since left(gsscode, integer) is IMMUTABLE:

  CREATE INDEX ON areas (LEFT(gss, 3));
  

  Verified live against the real dataset: this produces the same Index Scan plan and cost as the equivalent % query.

Rendering

Output is always the canonical 9-character upper-case form, eg E01000001. gsscode::text works without an explicit cast declaration (PostgreSQL derives it automatically from the type’s I/O functions).

Validity and reserved/NaN codes

is_valid(text) checks whether a string parses as a gsscode without actually raising – useful for filtering untrusted input before casting.

ONS reserves area=999999 within every type as a “no code assigned” placeholder – eg E00999999, E01999999, K99999999 – confirmed against the live Register of Geographic Codes' own “Reserved code (for CHD use)” column, which follows this pattern for all 206 current entity types with no exception. These parse and store as perfectly ordinary, fully-comparable gsscode values (nothing about =/</> treats them specially) – isnan(gsscode) is how you detect and filter them explicitly, the same role isnan() plays for float8.

Type names and descriptions

description(gsscode) and description(text) look up what a 3-character country+type prefix actually means, eg description('E01') -> 'Lower layer Super Output Areas'. Both overloads truncate to the first 3 characters, so a full 9-character code works too.

type_info(gsscode) and type_info(text) return the whole gsscode_types row instead of just name – abbreviation, theme, coverage and status as well – for when you want more than one field without hand-writing the join yourself:

SELECT (type_info('E01000001'::gsscode)).*;
--  gss | name                            | abbreviation | theme                       | coverage | status
-- -----+---------------------------------+--------------+------------------------------+----------+---------
--  E01 | Lower layer Super Output Areas  | LSOA         | Statistical Building Block  | England  | Current

The data comes from a private gsscode_types table shipped with the extension (206 rows: gss, name, abbreviation, theme, coverage, status), seeded from the ONS Register of Geographic Codes – a small, stable type registry that’s safe to ship as part of the extension itself. It does NOT hold individual area names (eg what E01000001 itself is called, as opposed to what “E01” as a type means) – that’s a much larger, per-installation dataset (500,000+ rows, changing as boundaries are redrawn) that stays external rather than being baked into a general- purpose extension.

The individual-code names live in ONS’s Code History Database (CHD), not the Register of Geographic Codes this extension ships:

• Code History Database (CHD) – ONS’s own overview/methodology page for the CHD.
• Open Geography Portal – where the actual CHD download (CSV or MS Access) is hosted, alongside the RGC and everything else ONS publishes geographically. Search for “Code History Database” to find the current dated release.
• Names, codes and lookups – ONS’s top-level index of everything in this space, including theme-specific names-and-codes listings (eg administrative geographies) if you only need one geography type rather than the full CHD.

If you have your own copy loaded into a table, wiring up individual-code names is a one-line function:

CREATE FUNCTION name(gsscode) RETURNS text LANGUAGE sql STABLE AS $$
   SELECT name FROM your_code_history_table WHERE gss = $1::text
$$;

gsscode_types survives pg_dump/restore even after a refresh (see below) – it’s registered via pg_extension_config_dump(), the same mechanism PostGIS uses for spatial_ref_sys, so restoring doesn’t silently revert it to the shipped snapshot.

Refreshing gsscode_types from ONS

ONS republishes the Register of Geographic Codes periodically under a new dated release (eg “(June 2025)” then “(June 2026)”, each a different underlying file). Two ways to refresh:

update_gsscode_types.py (recommended default) – an external script, stdlib + psycopg2 only, no extra Postgres privileges needed anywhere:

python3 update_gsscode_types.py --dsn "host=... port=... dbname=... user=..."

It finds the current release by querying ArcGIS’s search API for the item tagged PRD_RGC and category /Categories/LATEST, rather than a hardcoded item id or date, so it keeps working as ONS publishes new releases. Verified live: correctly found the “(June 2026)” release ahead of the “(June 2025)” one used to seed this table, parsed all 206 rows, and upserted them.

gsscode_ons_refresh extension (optional) – a separate extension (requires = 'gsscode, http') adding one function, update_gsscode_types(), that refreshes the table via a single SELECT. It’s deliberately a separate extension, not bundled into gsscode itself: most installations should never need to enable anything extra just to get the packed type and its operators.

The real obstacle to doing this in-database at all is that ONS only publishes the RGC as a ZIP (confirmed – no bare-CSV endpoint, no queryable feature-service), and PostgreSQL has no trusted, built-in way to decompress one – an HTTP-only path can fetch the file but can’t finish the job on its own. Rather than reaching for an untrusted language (plpython3u) to do that decompression inside the database, the decompression happens outside it entirely: this repo’s own GitHub Actions workflow (.github/workflows/refresh-gsscode-types.yml) fetches and unzips the current ONS release on a schedule, the same way update_gsscode_types.py does, and commits the parsed result as data/gsscode_types.json. update_gsscode_types() then only ever needs to do a plain HTTP GET of that file plus jsonb_populate_recordset() – both fully within PostgreSQL’s trusted core. The whole function is plpgsql (a trusted language); the only non-default piece is http itself, which – unlike plpython3u – can only make HTTP requests, nothing more.

Two things worth knowing before reaching for this:

• http still needs superuser to enable, same as any non-default extension, and is unavailable on some managed Postgres services – just a much narrower thing to grant than a general-purpose scripting language.
• This shifts part of what you’re trusting: instead of only ONS, you’re also trusting this repo’s GitHub Actions pipeline and whoever has write access to it. For most uses that’s a reasonable trade, but it’s a real, new dependency, not just a strict improvement – name it if you’re evaluating this for something that matters.
• The Postgres server process itself needs outbound internet access to raw.githubusercontent.com. Many production database hosts deliberately firewall that off, in which case this will simply time out – the external script has no such requirement since it can run from wherever you have egress.

Verified live: truncated gsscode_types to 0 rows, called SELECT update_gsscode_types();, got 206 back via a real HTTP fetch of the live file on GitHub and a real jsonb_populate_recordset() upsert – no plpython3u, no untrusted language, anywhere in the path.