DEV Community

Postal codes are the easy part: reference data lessons from 249 countries

Disclosure: The author works at Datopian, the team behind DataHub. This post is based on our own work building postal-code and region reference data - no sponsors, no affiliate links. Originally published on the DataHub blog.

If you have ever joined two datasets on "region" and got zero matches, or shipped an address form that only spoke one language, this post is for you.

Checkout validation, parcel routing, delivery pricing, customer-record checks and regional reporting all start with the same request: "we need a worldwide postal-code database". We get that request a lot, and when we work through it with the team asking, the problem moves away from postal codes fast.

The same four needs come up again and again:

  • Coverage: postal codes for every country, or at least for every country they ship to.
  • Clear codes for regions: knowing whether a region code is an ISO code, a national official code, or someone's own identifier.
  • Regions as data in their own right: a list of provinces, counties and districts, not just the ones that happen to appear next to a postal code.
  • Names in more than one language: local names, English, and the official languages of multilingual countries.

On top of that, almost every company has a few fields of its own.

This post goes through each need with data and code, and shows why "postal codes" is the easy part of reference data.

Not every country has postal codes

Start with coverage, because the answer surprises people. ISO 3166-1 lists 249 countries and territories. We publish postal codes for 131 of them.

The other 118 fall into three groups:

  • No postal-code system (50). Many countries never introduced one, or deliver by PO box, building number or neighbourhood instead. Qatar, Jamaica, the Bahamas, Angola and much of Central and West Africa are in this group. For some of them we publish a directory of localities and districts instead, with the postal-code column left empty on purpose.
  • Codes exist, but only behind closed doors (60). Saudi Arabia, Israel, Egypt, Kazakhstan, Nigeria and Venezuela all use postal codes, but the national post either sells them, offers only a one-at-a-time lookup on its website, or publishes no terms we could rely on. "Open data" stops here.
  • An open source exists and we haven't added it yet (8). South Korea, Paraguay and Armenia are already in GeoNames under an open licence; La Poste publishes Saint-Barthélemy and Saint-Martin in its open file. These are on our list.

So the honest answer to "all 249?" is: every country and territory can be in the divisions table, because every one of them has an ISO code and most have regions, but postal codes stop where national postal systems stop. Any vendor promising postal codes for all 249 is counting differently.

The status of each country is in data/countries.csv. Many of the "no postal-code system" calls rest on Universal Postal Union addressing sheets, some of them old. We re-check a country before telling a customer it has no codes.

Every country codes its regions differently

"What do the region codes mean?" has a different answer for almost every country. Postal datasets usually carry three administrative levels with a name and a code each, but the code systems underneath are whatever each country uses.

Here is one place in each of ten large economies, as the codes appear in postal data:

Country Example Level 1 code Same region in ISO 3166-2 Level 2 code Level 3 code Australia Sydney NSW AU-NSW 17200 (ABS local government area) - Canada Toronto ON CA-ON 3520 (census division) 3520005 (census subdivision) China Hefei CN-AH (ISO 3166-2) CN-AH 3401 (GB/T 2260 prefecture, first 4 of 6 digits) - France Lyon 84 (INSEE region) FR-ARA 69 (département) 691 (arrondissement) Germany Munich BY (ISO without prefix) DE-BY 091 (Regierungsbezirk) 09162 (Kreisschlüssel) Italy Milan 25 (ISO region number) IT-25 MI (province abbreviation) - Japan Sapporo 01 (prefecture number) JP-01 a GeoNames ID, not an official code 01102 (JIS X 0402) Spain Seville AN (ISO without prefix) ES-AN 41 (INE province) 41091 (INE municipality) United Kingdom Westminster E92000001 (ONS GSS) GB-ENG E09000033 (GSS district) E05… (GSS ward) United States Los Angeles CA (USPS) US-CA 06037 (Census FIPS county) -

Three things follow:

  • The same region has several codes. England is E92000001 to the Office for National Statistics and GB-ENG to ISO. Auvergne-Rhône-Alpes is 84 to INSEE and FR-ARA to ISO. If two of your systems use different ones, they won't join.
  • Not every code is official. Open datasets often fill a gap with their own identifiers, as GeoNames does for Japan's cities. That is fine as long as the data says so. A code that looks official but isn't is worse than a blank.
  • "Level 2" means different things. In Germany it is the Regierungsbezirk, which only some states have; in France the département; in Canada a census division. A level number on its own tells you little.

Our answer is to put a code_system on every code, so a reader always knows whether 3520 is a StatCan census division or something else, and to keep a crosswalk to ISO 3166-2 at level 1, which is the only level with one code system worldwide.

Codes by country: data/admin-code-systems.csv.

Divisions are a dataset of their own

Most requests end up asking for three tables, not one, and that is the right design. A postal-code file only contains the regions that happen to have postal codes, and it repeats each region on thousands of rows. A company that wants a dropdown of provinces, or a list of every county to assign to a shipping zone, needs the divisions themselves.

A small example of the three tables, joined on codes:

divisions Country Level Code Code system Parent English Local ES 0 ES ISO 3166-1 - Spain España ES 1 ES-CT ISO 3166-2 ES Catalonia Catalunya ES 2 ES:08 INE province ES-CT Barcelona Barcelona ES 3 ES:08019 INE municipality ES:08 Barcelona Barcelona US 1 US-CA ISO 3166-2 US California California US 2 US:06037 Census FIPS US-CA Los Angeles County Los Angeles County postal_codes and postal_code_divisions Country Postal code Place Level 1 Level 2 Level 3 ES 08002 Barcelona ES-CT ES:08 ES:08019 US 90012 Los Angeles US-CA US:06037 -

How big is the divisions table? At level 1 alone, ISO 3166-2 has 3,590 subdivisions in 200 countries. The other 49 countries and territories, mostly small islands and overseas territories, have no subdivisions at all.

The one country with more than 100 is Slovenia, whose 212 municipalities are its first level in ISO 3166-2. Russia (83), Türkiye (81), North Macedonia (80) and Thailand (78) follow.

Below level 1 the numbers grow fast: about 3,100 counties in the United States, about 8,100 municipalities in Spain and about 5,200 census subdivisions in Canada.

Because every row has a parent, you can walk up the tree to the code you need. For example, to find the ISO 3166-2 region for any division, whatever level and code system it starts from:

-- From any division (here Barcelona municipality, ES:08019)
-- up to its level-1 region with an ISO 3166-2 code
WITH RECURSIVE up AS (
  SELECT code, parent, level, code_system, name_en FROM divisions WHERE code = 'ES:08019'
  UNION ALL
  SELECT d.code, d.parent, d.level, d.code_system, d.name_en FROM divisions d JOIN up ON d.code = up.parent
)
SELECT code, name_en FROM up WHERE level = 1 AND code_system = 'ISO 3166-2';
-- → ES-CT, Catalonia

The same rule applies when joining your own data: match on the code and its code system, never on the code alone. 08 is a Spanish province, a French département and a lot of other things.

No single open source has official codes for every level of every country under a licence that allows commercial use. The most complete global boundary set, GADM, is free for academic and non-commercial use only. So the practical recipe is layers: ISO 3166-2 for level 1 everywhere, national statistics offices for the countries that matter most, and open global sources, clearly labelled, for the rest.

Names in more than one language

Address forms, invoices and reports often need a region's name in more than one language: the local name, an English one, and in multilingual countries every official language. Spain has co-official languages in several regions (Catalan, Basque, Galician and others), Canada has two official languages, and the United Kingdom recognises Welsh and Scottish Gaelic.

Wikidata, the open knowledge base behind Wikipedia, has names in hundreds of languages for most administrative divisions, and it carries each country's official codes, so names can be joined on codes rather than on spelling. We measured how many divisions have a name in a second language there:

Three lessons:

  • Official languages are well covered; others thin out further down. Spain's municipalities have names in Catalan, Basque and Galician almost everywhere. Canada's census subdivisions have French names in 78% of cases. But for a language that isn't local, such as Chinese in Canada or the UK, coverage falls from nearly complete at level 1 to under 10% at level 3.
  • Not every name is right. One UK council area came back with a Chinese label meaning "Edinburgh politics", because its code was attached to two different items and we picked the wrong one. Many labels are also in the wrong script variant. A wrong name in a checkout form is worse than a missing one, so we never machine-translate and we leave a name blank when no established name exists.
  • A label is not an official name. Spain's municipalities have official names in Catalan, Basque and Galician. Wikidata records those separately from the everyday label, and only for some municipalities. For official local names, the national register wins.

Coverage by country, language and level: data/name-coverage.csv.

The fields companies add on top

Even with clean codes and names, the list doesn't stop there. What companies really want are answers about each postal code that no postal authority publishes in one place. For a business that ships goods, these are typical:

  • Special tax and customs territories. Some postal codes are inside a country but outside its VAT or customs area. Shipping to them means different duties and paperwork.
  • Military, PO-box-only and business-only codes, which some carriers won't deliver to or treat differently.
  • Remote areas, as each carrier defines them, with their surcharges.
  • The company's own shipping zones, mapped onto postal codes.

The first of these is a short, stable table. These territories are part of an EU country but outside its VAT area, and some are outside the customs territory too, under Article 6 of the EU VAT Directive (2006/112/EC):

Territory Country Postal codes EU VAT area EU customs territory Canary Islands ES 35xxx, 38xxx outside inside Ceuta, Melilla ES 51xxx, 52xxx outside outside Büsingen am Hochrhein DE 78266 outside outside Heligoland DE 27498 outside outside Livigno IT 23041 outside outside Campione d'Italia IT 22061 outside inside Mount Athos GR 63086 outside inside Åland Islands AX 22xxx outside inside French outermost regions FR 971xx - 974xx, 976xx, 97150 outside inside

Even a table this small has traps. Two of these places have changed postal code, and some open datasets still carry the old ones (Livigno 23030, Campione d'Italia 22060). The French regions and Åland also have their own ISO country codes (GP, MQ, GF, RE, YT, MF, AX), so the same address can be filed under two countries.

In code, a first pass is a prefix lookup. The traps above are the parts worth commenting:

# EU territories outside the VAT area (Art. 6, Directive 2006/112/EC).
# (country, postal-code prefix, territory, inside EU customs territory?)
SPECIAL_TERRITORIES = [
  ("ES", "35", "Canary Islands", True),
  ("ES", "38", "Canary Islands", True),
  ("ES", "51", "Ceuta", False),
  ("ES", "52", "Melilla", False),
  ("DE", "78266", "Büsingen am Hochrhein", False),
  ("DE", "27498", "Heligoland", False),
  ("IT", "23041", "Livigno", False), # formerly 23030
  ("IT", "22061", "Campione d'Italia", True), # formerly 22060
  ("GR", "63086", "Mount Athos", True),
  ("AX", "22", "Åland Islands", True), # also filed as FI 22xxx
  ("FI", "22", "Åland Islands", True),
  # French outermost regions; also have their own ISO codes
  # (GP, MQ, GF, RE, YT, MF), so check those too
  ("FR", "971", "Guadeloupe / Saint-Martin", True),
  ("FR", "972", "Martinique", True),
  ("FR", "973", "French Guiana", True),
  ("FR", "974", "Réunion", True),
  ("FR", "976", "Mayotte", True),
]
def eu_special_territory(country: str, postal_code: str):
  """Return the territory if this address is outside the EU VAT area, else None."""
  pc = postal_code.replace(" ", "")
  for c, prefix, name, in_customs in SPECIAL_TERRITORIES:
    if country == c and pc.startswith(prefix):
      return {"territory": name, "eu_vat_area": False, "eu_customs_territory": in_customs}
  return None
eu_special_territory("ES", "38001") # Santa Cruz de Tenerife → Canary Islands
eu_special_territory("IT", "23030") # None: the old Livigno code slips through

The last line is the point: a lookup is only as good as the codes you feed it, so normalise old and alternative codes first. Full list with notes: data/special-territories.csv.

The other fields depend on the customer: their carriers' remote-area lists, their own zones. That is the pattern we see again and again: a shared base of open, well-documented reference data, and a thin layer of fields specific to one company, built with them.

Who needs this

The same shape of request turns up well beyond shipping:

Banks and payment companies keep country, currency, holiday and subdivision tables for onboarding and compliance, and need to know which codes are official. Marketplaces and retailers need clean address forms in many languages, and regions that match how each country actually divides itself. Logistics companies need postal codes, airports and ports joined to the same

Read on DEV Community ↗ ← Back to News

Comments

No comments yet. Start the discussion.