Working with postal codes
Three things the files support that remove work you may currently be doing with a geocoding service.
Zooming a map to a postal code
A postal code point is a coordinate. Typeahead on six characters, look up the point, move the map. No geocoding service, no API key, no rate limit.
SELECT postal_code, latitude, longitude, postal_code_unit_count
FROM postal_codes
WHERE postal_code LIKE upper(:typed) || '%'
ORDER BY postal_code
LIMIT 10;CREATE INDEX ON postal_codes (postal_code text_pattern_ops);Use postal_code_unit_count to set zoom. A code carrying four units and one carrying four hundred want very different framing, and a fixed zoom makes one of them useless.
Resolving a service area without an address
The useful case: someone gives you a postal code and you need to know which catchment, service area or zone they fall in.
Most implementations geocode a full address, then run point-in-polygon. That needs a geocoder and a complete address.
Instead, test whether every location carrying that code falls inside the polygon:
SELECT
count(*) AS locations,
count(*) FILTER (WHERE ST_Within(l.geom, s.geom)) AS inside
FROM locations l
JOIN service_areas s ON s.id = :area_id
WHERE l.postal_code = :postal_code;Three outcomes, and the third is the point:
inside = locations— every location is in the area. Definitive, no address needed.inside = 0— none are. Definitive the other way.- otherwise — the code straddles a boundary, and you need the street address. Ask for it only now.
Most enquiries resolve on six characters. The ones that straddle are exactly where guessing would have been wrong, and this tells you which they are.
Note the postal_code on the location file is null where units at that location carry different codes — common in larger buildings. Those need the address file:
SELECT DISTINCT a.location_id
FROM addresses a
WHERE a.postal_code = :postal_code;Joining locations and addresses without multiplying counts
This is the mistake the two-file structure exists to prevent.
A tower is one location with several hundred addresses. Join the address file to parcels, boundaries or catchments and every one of those rows matches — so a count of “addresses in this catchment” counts one building hundreds of times.
Join at location grain and carry the count:
SELECT
s.name,
count(*) AS buildings,
sum(l.location_unit_count) AS dwelling_units
FROM locations l
JOIN service_areas s ON ST_Within(l.geom, s.geom)
GROUP BY s.name;buildings and dwelling_units are different questions with different answers, and both are usually worth reporting. Counting address rows answers neither cleanly.
Go to the address file only when you need a specific unit.
What the coordinates are, and are not
Positions come from the register’s contributing sources and vary in accuracy between them. They are published as supplied, reprojected to WGS 84 without adjustment, and carry no quality or confidence field — inventing one would be a judgement we are not in a position to make.
src_x and src_y hold the untransformed source values so the reprojection can be checked.
Some records have no coordinate at all. Those carry no census identifiers and no H3 value, because both are derived from position.
Aggregating by hex
The H3 cell index is on every location and postal code row, so density work is a grouped query rather than a spatial operation:
SELECT h3_9, count(*) AS buildings, sum(location_unit_count) AS units
FROM locations
GROUP BY h3_9;Coarser resolutions derive from the value supplied.
What this does not do
It does not look up the postal code for an address. The direction is postal code in, location out. Assigning postal codes to addresses is not offered here or in any download.
It is not a substitute for a geocoder. No fuzzy matching against messy input, no interpolation where no address exists, no international coverage. Those are real requirements and there are good providers serving them.