Skip to content

Instantly share code, notes, and snippets.

@gingerwizard

gingerwizard/generating_postcode_to_3166_2.sql

Created December 12, 2022 12:58
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save gingerwizard/b863d765e9df46994145982d7f7a6c82 to your computer and use it in GitHub Desktop.
Save gingerwizard/b863d765e9df46994145982d7f7a6c82 to your computer and use it in GitHub Desktop.
Download ZIP
Query to generate postcode to iso 3166-2 codes on play.clickhouse.com
Raw
generating_postcode_to_3166_2.sql
SELECT
postcode,
any(code)
FROM
(
SELECT
concat(postcode1, ' ', postcode2) AS postcode,
dictGetOrDefault('blogs.uk_codes_dict', 'code', county, dictGetOrDefault('blogs.uk_codes_dict', 'code', district, dictGetOrDefault('blogs.uk_codes_dict', 'code', town, dictGetOrDefault('blogs.uk_codes_dict', 'code', locality, dictGetOrDefault('blogs.uk_codes_dict', 'code', replaceAll(district, 'CITY OF ', ''), ''))))) AS code
FROM default.uk_price_paid
)
WHERE code != ' '
GROUP BY postcode
FORMAT `Null`
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment