Last active
September 9, 2025 14:01
-
-
Save spreeker/c8ff96af9d37115323ddeea52a5d8dd4 to your computer and use it in GitHub Desktop.
postcodes to geo index.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| drop function postcode_to_int; | |
| -- create a function turning a postal code into a number. | |
| CREATE OR REPLACE FUNCTION postcode_to_int(pc text) | |
| RETURNS int AS $$ | |
| DECLARE | |
| num_part int; | |
| letters text; | |
| letter_1 int; | |
| letter_2 int; | |
| BEGIN | |
| -- First 4 digits | |
| num_part := substring(pc from '^\d{4}')::int * 1000; | |
| -- Two letters (force uppercase just in case) | |
| letters := upper(substring(pc from '\d{4}([A-Z]{2})$')); | |
| -- Map AA..ZZ → 0..675 | |
| letter_1 := (ascii(substring(letters,1,1)) - ascii('A')) * 26; | |
| letter_2 := (ascii(substring(letters,2,1)) - ascii('A')); | |
| -- Combine into single number | |
| RETURN (num_part + letter_1 + letter_2); | |
| END; | |
| $$ LANGUAGE plpgsql IMMUTABLE; | |
| select (1121 || 60::text || 90::text)::int | |
| select postcode_to_int('1121AA'); | |
| -- optional. | |
| ALTER TABLE kv.kleinverbruik_2024 | |
| ADD COLUMN postcode_from_int int GENERATED ALWAYS AS (postcode_to_int(postcode_van)) STORED, | |
| ADD COLUMN postcode_to_int int GENERATED ALWAYS AS (postcode_to_int(postcode_tot)) stored | |
| -- the 'geo' column | |
| alter table kv.kleinverbruik_2024 | |
| ADD COLUMN postcode_range int8range | |
| GENERATED ALWAYS AS (int8range(postcode_to_int(postcode_van), postcode_to_int(postcode_tot), '[]')) STORED; | |
| -- creating the geo index | |
| CREATE INDEX idx_postcode_range_gist | |
| ON kv.kleinverbruik_2024 USING gist(postcode_range); | |
| -- actual example use. without the geo index folloing query would take about 50 min. | |
| drop table if exists kv.gas_nums_2024_v4; | |
| select mp.numid, mp.product, mp.pid, mp.postcode, mp.gridarea as netvlakid, mp.organisation, k.* | |
| into kv.gas_nums_2024_v4 | |
| from eancodeboek.metering_points_nums_2025 mp --unique gas aansluitingen. TODO. | |
| left outer join baghelp.num_netbeheerder nb on nb.numid = mp.numid | |
| left outer join baghelp.num_buurt_wijk_gemeente_provincie nbwgp on (nbwgp.numid = mp.numid) | |
| left outer join kv.kleinverbruik_2024 k on ( | |
| postcode_to_int(mp.postcode)::bigint <@ k.postcode_range | |
| and k.netbeheerder = nb.netbeheerder | |
| and k.productsoort = 'GAS' | |
| ) | |
| left outer join baghelp.gemcode_p6 gp on (k.postcode_van = gp.postcode) | |
| and mp.product = 'GAS' | |
| where gp.gemeentecode = nbwgp.gemeentecode |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment