In-Place SRID Reprojection

Part of Spatial Schema Migrations & Evolution, this technique rewrites the coordinates already stored in a geometry column so the whole table lives in a new spatial reference system, without exporting the data or creating a shadow table. A land registry that ingested land_parcels in EPSG:25833 (ETRS89 / UTM zone 33N) but later standardised on EPSG:32633 (WGS 84 / UTM zone 33N) faces exactly this: millions of polygons whose numeric coordinates must move to the new CRS while every parcel keeps its real-world footprint. The tool for the coordinate math is ST_Transform, but doing it in place means also reconciling the column’s type modifier, the geometry_columns catalog view, and the GiST index that indexes the old coordinate space.

Because the coordinate values are being rewritten across the whole table, the two questions that dominate the migration are transactional footprint and index correctness. A single UPDATE land_parcels SET geom = ST_Transform(geom, 32633) on a large table holds every changed row’s lock for the duration, bloats the table with dead tuples, and blocks autovacuum from reclaiming them until it commits. The batched Python approach breaks that into bounded transactions. This page covers the surrounding schema work that a raw UPDATE alone does not handle.

Prerequisites and Infrastructure Validation

Confirm the extension, the transformation definitions, and the current column metadata before touching data.

PostGIS and reference-system availability:

-- PostGIS 3.2+ is assumed; ST_Transform relies on PROJ under the hood
SELECT postgis_full_version();

-- Both the source and target SRIDs MUST exist in spatial_ref_sys,
-- otherwise ST_Transform raises "transform: couldn't project point"
SELECT srid, auth_name, auth_srid
FROM spatial_ref_sys
WHERE srid IN (4326, 25833, 32633)
ORDER BY srid;
-- Expect three rows: 4326, 25833, 32633

Inspect the current column definition and typmod:

-- The authoritative view of the column's declared SRID and geometry type
SELECT f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, type
FROM geometry_columns
WHERE f_table_name = 'land_parcels';
-- Example: srid = 25833, type = 'POLYGON'

The srid reported by geometry_columns is derived from the column’s type modifier (typmod), not stored separately, so once you ALTER the column type the view updates automatically. Verify the actual data agrees with the declared SRID — mismatches predate this migration and would otherwise corrupt the transform:

-- Every stored row should currently report the source SRID
SELECT ST_SRID(geom) AS srid, count(*)
FROM land_parcels
GROUP BY ST_SRID(geom);
-- Expect a single group: 25833. A row reporting 0 has no SRID and must be fixed first.

Python baseline: the reprojection driver in this workflow uses psycopg >= 3.1 with a server-side cursor; shapely >= 2.0 is only needed if you validate a sample client-side. No geoalchemy2 mapping is required for the migration itself.

Core Execution Workflow

Step 1 — Take a Bounded Backup and Record the Source Extent

Reprojection is reversible in principle (transform back), but floating-point round-tripping is not bit-exact. Capture the pre-migration extent so you can sanity-check the result:

-- Record the source-CRS extent; store this output before proceeding
SELECT ST_Extent(geom) AS extent_25833, count(*) AS parcel_count
FROM land_parcels;
-- e.g. BOX(373000 5806000, 402000 5833000) — metres in UTM 33N

For a registry of record, dump the table with pg_dump -t land_parcels first. The in-place rewrite cannot be rolled back once you have re-applied the new typmod and dropped the old index.

Step 2 — Relax the SRID Typmod

The column is declared geometry(Polygon, 25833). That typmod enforces SRID 25833 on every value, so an UPDATE writing SRID 32633 geometries fails with a type-check error. Relax the constraint to a plain, SRID-agnostic geometry for the duration of the rewrite:

-- Relax the constraint so the column can hold mixed SRIDs mid-migration.
-- This is a metadata-only change and is effectively instant.
ALTER TABLE land_parcels
    ALTER COLUMN geom TYPE geometry(Polygon);

-- geometry_columns.srid now reports 0 for this column until we re-apply the typmod

Keeping the geometry type (Polygon) while dropping the SRID avoids an unintended widening to generic GEOMETRY, which would relax the type check too. If your table stores MultiPolygon survey boundaries, use geometry(MultiPolygon) accordingly.

Step 3 — Reproject in Batches

Do not run one table-wide UPDATE. Rewrite bounded id ranges so each transaction is small enough to commit quickly and let autovacuum reclaim dead tuples between batches. A pure-SQL batch loop using DO blocks looks like this:

-- Batched in-place reprojection over the primary-key range.
-- Each UPDATE is its own autocommit statement when run from a client
-- that does not wrap the whole loop in a single transaction.
DO $$
DECLARE
    batch_lo bigint;
    batch_hi bigint;
    step     bigint := 50000;
    max_id   bigint;
BEGIN
    SELECT max(parcel_id) INTO max_id FROM land_parcels;
    batch_lo := 0;
    WHILE batch_lo <= max_id LOOP
        batch_hi := batch_lo + step;
        UPDATE land_parcels
        SET geom = ST_Transform(geom, 32633)
        WHERE parcel_id >= batch_lo
          AND parcel_id <  batch_hi
          AND ST_SRID(geom) = 25833;   -- idempotency guard: skip already-moved rows
        batch_lo := batch_hi;
        RAISE NOTICE 'reprojected through parcel_id %', batch_hi;
    END LOOP;
END $$;

The ST_SRID(geom) = 25833 guard makes the loop idempotent: if it is interrupted and rerun, already-transformed rows (now reporting SRID 32633) are skipped rather than double-transformed. A DO block still runs inside a single transaction, however, so for genuinely large tables — tens of millions of parcels — move the loop into the chunked Python driver, which commits after each chunk and keeps table bloat bounded.

Step 4 — Re-apply the SRID Typmod

Once every row reports the target SRID, re-declare the column so the typmod and the geometry_columns catalog reflect EPSG:32633:

-- Re-apply the constraint. This scans the table to validate that every
-- row is SRID 32633; it fails fast if any row was missed.
ALTER TABLE land_parcels
    ALTER COLUMN geom TYPE geometry(Polygon, 32633)
    USING ST_SetSRID(geom, 32633);

The USING ST_SetSRID(geom, 32633) clause is a safety net: the coordinates were already moved by ST_Transform, so this only stamps the SRID metadata and does not re-project. If a batch was missed, the values still carry SRID 32633 numerically but represent the wrong location — which is why the Step 3 guard and the Step 6 verification matter.

Confirm the catalog now agrees:

SELECT f_geometry_column, srid, type
FROM geometry_columns
WHERE f_table_name = 'land_parcels';
-- Expect: srid = 32633, type = 'POLYGON'

Step 5 — Rebuild the GiST Index

The old GiST index stored bounding boxes computed in EPSG:25833 metres. After the rewrite those bounds are wrong, so any spatial query would prune the wrong candidates. Rebuild the index against the new coordinate space. On a live registry, build the replacement without blocking writes — the mechanics are covered in Concurrent Index Builds on Spatial Tables:

-- Build the new index concurrently, then drop the stale one.
CREATE INDEX CONCURRENTLY idx_land_parcels_geom_32633
ON land_parcels
USING GIST (geom);

-- Verify the new index is valid before removing the old one
SELECT indexrelid::regclass AS index, indisvalid
FROM pg_index
WHERE indrelid = 'land_parcels'::regclass;

DROP INDEX CONCURRENTLY idx_land_parcels_geom;  -- the old EPSG:25833 index

Step 6 — Verify and Refresh Statistics

-- 1. Every row now in the target CRS
SELECT ST_SRID(geom) AS srid, count(*)
FROM land_parcels
GROUP BY ST_SRID(geom);
-- Expect a single group: 32633

-- 2. Extent is plausible for UTM 33N and covers the same ground as before
SELECT ST_Extent(geom) AS extent_32633 FROM land_parcels;

-- 3. Planner statistics reference the new geometry distribution
ANALYZE land_parcels;

Because both EPSG:25833 and EPSG:32633 are UTM zone 33N in metres, the numeric extent should be very close to the pre-migration box — a useful cross-check that no batch reprojected into the wrong zone. When you later expose the data to web clients you will still cast to EPSG:4326 at query time; reprojecting to WGS 84 on the fly composes cleanly with metre-native filters such as ST_DWithin radius searches.

Performance Considerations

Reading the UPDATE Plan

An in-place reprojection is a heap rewrite, not an index-driven query, so the plan you care about is the per-batch UPDATE:

EXPLAIN (ANALYZE, BUFFERS)
UPDATE land_parcels
SET geom = ST_Transform(geom, 32633)
WHERE parcel_id >= 0 AND parcel_id < 50000
  AND ST_SRID(geom) = 25833;

You want an Index Scan using land_parcels_pkey on the parcel_id range, not a Seq Scan that reads the whole heap for each batch. If the planner chooses a sequential scan because the range covers most of the table, shrink step so each batch is a smaller, more selective slice. ST_Transform itself is CPU-bound in PROJ; expect the transform, not I/O, to dominate wall-clock time on warm data.

Relevant GUCs During the Rewrite

-- Make autovacuum aggressive on this table for the duration of the rewrite
ALTER TABLE land_parcels SET (autovacuum_vacuum_scale_factor = 0.02);

Bloat Between Batches

Every row ST_Transform rewrites leaves a dead tuple. Across millions of parcels that is a full table’s worth of dead space. Bounded batches let autovacuum interleave, but on a tight schedule run an explicit VACUUM partway through, and consider a final VACUUM (ANALYZE) once the typmod is re-applied. If you monitor for accumulated dead tuples and index fragmentation as a routine, the same signals surface in detecting GiST index bloat.

Common Failure Modes and Fixes

Source or Target SRID Missing from spatial_ref_sys

Diagnosis: ST_Transform aborts with couldn't project point ...: latitude or longitude exceeded limits or InternalError: transform: couldn't project.

SELECT count(*) FROM spatial_ref_sys WHERE srid IN (25833, 32633);
-- If this returns fewer than 2, a definition is missing

Fix: ensure a complete spatial_ref_sys (it ships fully populated with PostGIS). Custom or regional grids may need an inserted definition; never invent a proj4/WKT string by hand for a cadastral system — use the official EPSG parameters.

Rows With SRID 0 Block the Transform

Diagnosis: a subset of parcels was inserted without an SRID and ST_SRID(geom) returns 0. ST_Transform(geom, 32633) on an SRID-0 geometry raises Input geometry has unknown (0) SRID.

Fix: stamp the known source SRID before transforming — these rows are genuinely in EPSG:25833, they just lost their metadata:

UPDATE land_parcels
SET geom = ST_SetSRID(geom, 25833)
WHERE ST_SRID(geom) = 0;
-- ST_SetSRID only labels; it does NOT move coordinates

Typmod Re-apply Fails on a Missed Batch

Diagnosis: ALTER COLUMN ... TYPE geometry(Polygon, 32633) in Step 4 errors with Geometry SRID (25833) does not match column SRID (32633).

Fix: the constraint scan caught rows the loop skipped. Find and finish them, then retry the ALTER:

-- Locate the stragglers
SELECT parcel_id FROM land_parcels WHERE ST_SRID(geom) = 25833 LIMIT 20;

-- Reproject only those rows
UPDATE land_parcels
SET geom = ST_Transform(geom, 32633)
WHERE ST_SRID(geom) = 25833;

Spatial Queries Return Wrong Rows Before Reindex

Diagnosis: after the data rewrite but before Step 5, && and ST_Intersects filters return empty or wrong results because the GiST index still holds EPSG:25833 bounding boxes.

Fix: this is expected until the index is rebuilt. Do not “fix” it by disabling the index; complete Step 5. If you must serve reads mid-migration, force a sequential scan in a diagnostic session with SET enable_indexscan = off; and accept the slower plan until the new index is valid.

Verification

Run this closing check before declaring the reprojection done:

-- 1. Uniform target SRID across the whole table
SELECT DISTINCT ST_SRID(geom) FROM land_parcels;   -- exactly one row: 32633

-- 2. Catalog typmod matches the data
SELECT srid, type FROM geometry_columns WHERE f_table_name = 'land_parcels';

-- 3. New GiST index is valid and used
EXPLAIN (ANALYZE, BUFFERS)
SELECT parcel_id
FROM land_parcels
WHERE geom && ST_MakeEnvelope(380000, 5810000, 385000, 5815000, 32633);
-- Expect a Bitmap Index Scan on idx_land_parcels_geom_32633

-- 4. Fresh statistics
ANALYZE land_parcels;

A successful migration shows one SRID, a geometry_columns row reading 32633 / POLYGON, and a plan that uses the new GiST index against a 32633 envelope. If step 3 still shows Seq Scan, confirm the new index is indisvalid = true and that you dropped the stale one.

Frequently Asked Questions

Does ST_Transform change the shape of my parcels or only their coordinates?

It changes the numeric coordinates so that the same ground position is expressed in the target CRS. The real-world footprint is preserved to within the accuracy of the underlying transformation grid. Moving between two projected systems, or between a UTM zone and EPSG:4326, is precise enough for cadastral use provided both SRIDs exist in spatial_ref_sys and neither relies on a missing datum-shift grid.

Why must I drop the geometry typmod before the UPDATE?

A column declared geometry(Polygon, 25833) enforces SRID 25833 on every stored value. An UPDATE that writes SRID 32633 geometries into that column raises a constraint violation before a single row is rewritten. Relaxing the typmod to plain geometry(Polygon) lets the batched rewrite proceed, after which you re-apply geometry(Polygon, 32633) so the catalog and typmod agree again.

Do I need to rebuild the GiST index after reprojection?

Yes. A GiST index stores bounding boxes computed in the old coordinate space, so once every row is rewritten the index entries no longer describe the data. PostgreSQL still considers the index valid, but its bounds are meaningless and spatial filters will prune the wrong candidates. Drop and recreate it concurrently, then run ANALYZE.

Can I reproject only some rows instead of the whole column?

Not while keeping a single SRID typmod. A geometry column has exactly one declared SRID, so partial reprojection would leave the column with mixed SRIDs and an un-re-appliable typmod. If you genuinely need per-row CRS, store the source SRID in a separate integer column and keep the geometry column SRID-agnostic — but for a cadastral registry a single, uniform CRS is almost always the correct design.

Related Topics

• Spatial Schema Migrations & Evolution — parent section covering online schema changes for spatial tables
• Batch ST_Transform Reprojection in Python — the chunked, transaction-scoped driver for millions of rows
• Concurrent Index Builds on Spatial Tables — rebuilding the GiST index without blocking writes
• Adding Geometry Columns to Live Tables — sibling: online DDL for new geometry columns
• ST_DWithin Radius Searches — metre-native proximity filters that compose with on-the-fly reprojection to EPSG:4326