Loading into PostGIS

GeoPackage is the recommended format. It carries the schema, the geometry and the projection definition in one file, with no encoding guesswork and no truncated column names.

Loading a GeoPackage

ogr2ogr \
  -f PostgreSQL \
  PG:"host=localhost dbname=addresses user=postgres" \
  addresses_bc_2026q3.gpkg \
  -nln addresses.bc_points \
  -lco GEOMETRY_NAME=geom \
  -lco FID=address_pk \
  -lco SPATIAL_INDEX=NONE \
  -nlt POINT \
  --config PG_USE_COPY YES

Two flags matter for speed.

PG_USE_COPY YES switches from row-by-row INSERT to COPY. On a multi-million row extract this is the difference between minutes and hours.

SPATIAL_INDEX=NONE skips index creation during load. Build it afterwards, when the table is populated and the planner has statistics to work with.

No -t_srs flag: the file is already 4326 and reprojecting on ingest is the wrong place to do it. See projections.

Indexes after loading

CREATE INDEX IF NOT EXISTS bc_points_geom_idx
  ON addresses.bc_points USING GIST (geom);

CREATE INDEX IF NOT EXISTS bc_points_address_id_idx
  ON addresses.bc_points (address_id);

CREATE INDEX IF NOT EXISTS bc_points_csd_idx
  ON addresses.bc_points (csd_uid);

CREATE INDEX IF NOT EXISTS bc_points_postal_idx
  ON addresses.bc_points (postal_code)
  WHERE postal_code IS NOT NULL;

ANALYZE addresses.bc_points;

The partial index on postal_code is worth it because a meaningful share of records are null there, and excluding them keeps the index substantially smaller.

Run ANALYZE explicitly. Autovacuum gets there eventually, but the first queries after a bulk load will otherwise use stale statistics and may choose a sequential scan.

Loading the CSV instead

CREATE TABLE addresses.bc_points_raw (
  address_id       text,
  civic_number     integer,
  civic_suffix     text,
  unit             text,
  street_name      text,
  street_type      text,
  street_direction text,
  full_address     text,
  source_address   text,
  latitude         numeric,
  longitude        numeric,
  position_class   text,
  province         text,
  cd_uid           text,
  csd_uid          text,
  csd_name         text,
  postal_code      text,
  source_id        text,
  release          text,
  change_flag      text
);

\copy addresses.bc_points_raw FROM 'addresses_bc_2026q3.csv' WITH (FORMAT csv, HEADER true)

ALTER TABLE addresses.bc_points_raw
  ADD COLUMN geom geometry(Point, 4326);

UPDATE addresses.bc_points_raw
SET geom = ST_SetSRID( ST_MakePoint(longitude, latitude), 4326 );

CREATE INDEX ON addresses.bc_points_raw USING GIST (geom);

Longitude first. ST_MakePoint takes X then Y, which is longitude then latitude. Reversing them is the most common CSV loading error and it puts Canadian addresses somewhere off the coast of Somalia — obvious on a map, invisible in a count.

Verifying the load

SELECT
  count(*)                             AS rows,
  count(*) FILTER (WHERE geom IS NULL) AS null_geom,
  count(DISTINCT address_id)           AS distinct_ids,
  min(ST_SRID(geom))                   AS srid_min,
  max(ST_SRID(geom))                   AS srid_max,
  ST_Extent(geom)::text                AS bbox
FROM addresses.bc_points;

rows and distinct_ids should match; if not, the load ran twice or a partial load was not rolled back.

srid_min and srid_max should both be 4326.

Check the bounding box against where the data should be. Canada sits roughly between -141 and -52 longitude, 41 and 84 latitude. A bbox outside that means swapped coordinates.

Loading postal code points

Same procedure, smaller file. The one thing worth doing on load:

CREATE INDEX ON addresses.bc_postal (postal_code);
CREATE INDEX ON addresses.bc_postal (address_count);

The index on address_count earns its place because most useful queries against this layer filter on it — see the schema notes on reading it alongside spread_m.

Applying a quarterly release

Do not reload the full extract. Load the diff into a staging table and apply it:

BEGIN;

DELETE FROM addresses.bc_points p
USING staging.bc_diff d
WHERE p.address_id = d.address_id
  AND d.change_flag = 'retired';

UPDATE addresses.bc_points p
SET geom = d.geom,
    position_class = d.position_class,
    release = d.release
FROM staging.bc_diff d
WHERE p.address_id = d.address_id
  AND d.change_flag IN ('moved', 'attribute_change');

INSERT INTO addresses.bc_points
SELECT * FROM staging.bc_diff
WHERE change_flag = 'added';

COMMIT;

Wrap it in a transaction. A half-applied diff is worse than a stale table, because nothing about it looks wrong.