Backfilling Geometry From Latitude/Longitude Columns

Problem Statement

This page zooms in on one step of the broader zero-downtime migration workflow: taking the millions of historical rows in an IoT sensor_pings table that carry only legacy latitude and longitude float columns and populating a freshly added geom column of type geometry(Point, 4326). The new column already exists and is nullable, the application already dual-writes geometry on new rows, and now the past has to be filled in. The catch is scale — the table is large and hot, so the fill cannot run as one giant statement. It has to advance in small, committed batches that never lock the table long enough to disturb ingestion.

Why the Naive Approach Fails

The obvious one-liner looks harmless and is a production incident waiting to happen:

-- DO NOT run this on a large live table
UPDATE sensor_pings
SET geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326)
WHERE geom IS NULL;

A single UPDATE across every row opens one enormous transaction. It holds row locks on the entire table until it commits, writes one dead tuple per updated row so the table doubles in size before autovacuum can react, and produces a WAL surge that leaves streaming replicas minutes behind. On a table of a few hundred million rows it can run for hours and, if it fails near the end, rolls back every bit of progress.

Reaching for LIMIT/OFFSET to chunk it is the next trap:

# Anti-pattern: OFFSET re-scans and discards all skipped rows every batch
offset = 0
while True:
    cur.execute(
        "SELECT id FROM sensor_pings WHERE geom IS NULL ORDER BY id LIMIT 5000 OFFSET %s",
        (offset,),
    )
    ids = [r[0] for r in cur.fetchall()]
    if not ids:
        break
    # ... update these ids ...
    offset += 5000   # every iteration re-counts and throws away `offset` rows

OFFSET makes PostgreSQL walk and discard every row before the window on each batch, so cost climbs linearly and the last batches crawl. Worse, because the WHERE geom IS NULL set shrinks as you fill it, the offset arithmetic drifts and you skip rows. The correct tool is keyset pagination on the primary key.

Production-Ready Implementation

The pattern below walks the id space in fixed windows, builds each point with longitude-first ST_MakePoint, stamps SRID 4326 explicitly, and commits after every batch. It tracks the last id processed rather than an offset, so every batch is an index range scan of constant cost. A short sleep throttles the loop so replication lag stays bounded.

import time
import logging
import psycopg

logging.basicConfig(level=logging.INFO)
log = logging.getLogger("geom_backfill")

DSN = "host=localhost dbname=iot user=migrator password=secret"

BACKFILL_SQL = """
    UPDATE sensor_pings
    SET geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326)
    WHERE id > %(last_id)s
      AND id <= %(last_id)s + %(batch)s
      AND geom IS NULL
      AND latitude  IS NOT NULL
      AND longitude IS NOT NULL
"""

def backfill_geometry(
    dsn: str = DSN,
    batch: int = 5000,
    pause_s: float = 0.05,
) -> int:
    """
    Backfill sensor_pings.geom from legacy latitude/longitude floats.

    Walks the primary key in keyset windows of `batch` ids, committing after
    each window so lock duration, table bloat, and replication lag stay bounded.
    Returns the total number of rows updated.
    """
    total = 0
    with psycopg.connect(dsn) as conn:
        # Establish the id range we must cover among still-NULL rows.
        with conn.cursor() as cur:
            cur.execute(
                "SELECT min(id), max(id) FROM sensor_pings WHERE geom IS NULL"
            )
            lo, hi = cur.fetchone()

        if lo is None:
            log.info("No rows require backfill; geom already populated.")
            return 0

        last_id = lo - 1
        while last_id < hi:
            with conn.cursor() as cur:
                cur.execute(BACKFILL_SQL, {"last_id": last_id, "batch": batch})
                updated = cur.rowcount
            conn.commit()               # release locks; bound WAL per transaction
            total += updated
            last_id += batch
            log.info("Backfilled through id %s (+%s rows, %s total)",
                     last_id, updated, total)
            if pause_s:
                time.sleep(pause_s)     # throttle to keep replicas caught up

    log.info("Backfill complete: %s rows updated.", total)
    return total


if __name__ == "__main__":
    backfill_geometry()

Three details make this safe. First, the UPDATE window is expressed as id > last_id AND id <= last_id + batch, a half-open range that rides the sensor_pings_pkey index directly — no OFFSET, no re-scan. Second, ST_MakePoint(longitude, latitude) puts longitude first because PostGIS points are (X, Y) and X is longitude; swapping them silently relocates every sensor. Third, ST_SetSRID(..., 4326) tags each point with SRID 4326 so it matches the column’s declared type and is directly comparable with other 4326 geometry in later queries.

The loop advances by the fixed batch stride rather than by the number of rows actually updated, so gaps in the id sequence (deleted rows, rows that were already dual-written and are skipped by geom IS NULL) never stall progress — an empty batch simply moves the cursor forward and continues.

Verification Steps

The migration is not done until zero rows with valid coordinates still carry a NULL geometry. Check that first:

-- Must return 0 before you build the index or cut reads over
SELECT count(*) AS remaining_null
FROM sensor_pings
WHERE geom IS NULL
  AND latitude  IS NOT NULL
  AND longitude IS NOT NULL;

Then confirm the reconstructed coordinates round-trip back to the legacy floats, catching any axis swap or precision loss:

-- Any non-zero count means geometry disagrees with the source coordinates
SELECT count(*) AS mismatched
FROM sensor_pings
WHERE geom IS NOT NULL
  AND (
        abs(ST_X(geom) - longitude) > 1e-9
     OR abs(ST_Y(geom) - latitude)  > 1e-9
      );

Confirm every populated row is registered as SRID 4326, not a stray SRID 0:

SELECT DISTINCT ST_SRID(geom) AS srid
FROM sensor_pings
WHERE geom IS NOT NULL;   -- expect a single row: 4326

Only once those three checks pass do you build the spatial index, deferred to the end so the backfill UPDATEs never pay index-maintenance cost:

-- Build after the column is fully populated, without locking ingestion
CREATE INDEX CONCURRENTLY idx_sensor_pings_geom
ON sensor_pings USING GIST (geom);

The mechanics and recovery paths for concurrent builds on tables this size are covered under CREATE INDEX CONCURRENTLY on Large Spatial Tables. Finish by confirming a spatial read actually uses the new index:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM sensor_pings
WHERE ST_DWithin(
        geom::geography,
        ST_SetSRID(ST_MakePoint(-122.42, 37.77), 4326)::geography,
        750
      );
-- Expect: Index Scan using idx_sensor_pings_geom

Configuration and Tuning Knobs

Apply the table-level autovacuum override with ALTER TABLE sensor_pings SET (autovacuum_vacuum_scale_factor = 0.02); and server GUCs with ALTER SYSTEM SET ... followed by SELECT pg_reload_conf();. Monitor replica lag while the loop runs with SELECT now() - pg_last_xact_replay_timestamp(); on a replica and raise pause_s if it trends upward.

Gotchas Checklist

• Longitude comes first in ST_MakePoint. ST_MakePoint(longitude, latitude) builds (X, Y); reversing the arguments swaps the axes and puts every point in the wrong place. Verify with the round-trip ST_X/ST_Y parity query above.
• Always wrap with ST_SetSRID(..., 4326). A bare ST_MakePoint yields an SRID-0 point. Even though the column is typed geometry(Point, 4326) and rejects mismatches, forgetting ST_SetSRID produces an error mid-batch or, in looser schemas, silently unindexable data.
• Never wrap the whole loop in one transaction. The per-batch conn.commit() is what bounds lock duration and WAL volume. If you accidentally hold one transaction open across all batches, you have recreated the single-UPDATE problem with extra steps.
• Skip rows with NULL coordinates deliberately. The latitude IS NOT NULL AND longitude IS NOT NULL guard leaves genuinely coordinate-less rows as NULL geometry. Decide before enforcing any NOT NULL constraint whether those rows are excluded or backfilled from another source.
• Build the index last, once. Creating the GiST index before the backfill makes every batch maintain it, roughly doubling write cost and WAL. Populate fully, verify, then index — and read the parent zero-downtime migration guide for how this step fits the full expand-and-contract sequence.

Related Topics

• Backfilling & Zero-Downtime Spatial Migrations — the parent workflow this backfill step belongs to, from expand through contract
• Spatial Schema Migrations & Evolution — the broader context of evolving spatial schemas without downtime
• Adding Geometry Columns to Live Tables — the preceding expand step that creates the nullable geometry column this page fills
• CREATE INDEX CONCURRENTLY on Large Spatial Tables — building the GiST index safely once the backfill completes