Automating CRS Transformations in ETL Pipelines for Spatial Data Archival

Uncoordinated Coordinate Reference System (CRS) normalization during batch ingestion is a primary driver of spatial data corruption in cold storage tiers, and it almost never raises an exception — it writes plausible-looking wrong coordinates that only surface in a failed spatial join months later. This page is for the data engineers, GIS archivists, and cloud architects who own a high-throughput ingestion path and need a deterministic, idempotent reprojection stage that default GDAL/OGR configurations cannot give them: the standard fallbacks guess at missing datums, swap axis order, and silently drop vertical components. It operationalises the design decisions in CRS Synchronization in Pipelines, sits inside the broader Format Conversion & Pipeline Automation discipline, and produces archival outputs whose projection is auditable on both sides of every conversion hop.

Transformation Pipeline #

The ETL stage canonicalizes, transforms, validates, then commits — each step auditable:

Step-by-Step Procedure #

Phase 0: Pipeline Configuration & Environment Hardening #

The transformation stage must operate as a stateless, projection-aware middleware layer. Implicit GDAL/OGR fallbacks introduce non-reproducible datum shifts and silently drop vertical/horizontal components, so the first task is to pin PROJ data paths, disable on-the-fly CRS guessing, and force strict WKT2:2019 canonicalization before any payload is read.

# Pin PROJ/GDAL data dirs and disable every non-deterministic fallback.
export PROJ_DATA=/usr/share/proj          # PROJ 9.1+ name for the data dir
export PROJ_LIB=/usr/share/proj           # legacy pre-9.1 name, kept for older images
export GDAL_DATA=/usr/share/gdal
export GDAL_DISABLE_READDIR_ON_OPEN=EMPTY_DIR
export OGR_ENABLE_PARTIAL_REPROJECTION=NO  # never write a partially-reprojected layer
export PROJ_NETWORK=OFF                     # no runtime grid downloads in cold-storage workers

Route all spatial payloads through a dedicated CRS normalization container before partitioning. This isolates geometry transformation from attribute serialization, preventing cross-format metadata bleed, and lets the type-coercion contract in Schema Mapping & Attribute Validation run alongside reprojection rather than fighting it. Mount the PROJ database read-only so no worker node can mutate proj.db mid-run.

Phase 1: Input CRS Interrogation & Canonicalization #

Resolve the source CRS from embedded metadata only — never from a connector default — and reject anything that cannot be mapped to a current authority code.

import json, hashlib, sys
import pyproj
from osgeo import gdal

gdal.UseExceptions()
INPUT = "datasets/vector/raw/parcels_region_north.shp"

src = gdal.OpenEx(INPUT, gdal.OF_VECTOR)
src_srs = src.GetLayer().GetSpatialRef()
if src_srs is None:
    sys.exit(f"QUARANTINE: {INPUT} has no embedded CRS; refusing implicit EPSG:4326")

crs = pyproj.CRS.from_wkt(src_srs.ExportToWkt())
if crs.is_deprecated or not crs.to_epsg():
    sys.exit(f"QUARANTINE: {INPUT} declares a deprecated or non-authority CRS")

# Normalize to WKT2:2019 so downstream readers never re-guess axis order.
canonical_wkt = crs.to_wkt(version="WKT2_2019")
with open(INPUT, "rb") as fh:
    src_hash = hashlib.sha256(fh.read()).hexdigest()

with open("/var/log/crs_manifest.jsonl", "a") as log:
    log.write(json.dumps({
        "file": INPUT, "status": "CANONICALIZED",
        "source_epsg": crs.to_epsg(), "sha256": src_hash,
    }) + "\n")

Writing the canonical WKT and source hash to a manifest before transformation gives you an exactly-once reconciliation record that is independent of the storage write, and an absent-CRS payload halts the run instead of inheriting a silent geographic assumption that would violate archival compliance.

Phase 2: Deterministic PROJ Transformation Execution #

Apply a single, auditable reprojection targeting the archival standard CRS (EPSG:4326 for global indexing, EPSG:3857 for tiled web archives). Vector reprojection uses ogr2ogr — gdalwarp is a raster utility and cannot reproject vector layers. Skip the transform entirely when source already equals target so the stage stays idempotent.

# Idempotent vector reprojection to the archival target CRS.
ogr2ogr \
  -t_srs "EPSG:4326" \
  -nlt PROMOTE_TO_MULTI \
  --config OGR_NUM_THREADS ALL_CPUS \
  -lco GEOMETRY_NAME=geom \
  -overwrite \
  datasets/vector/normalized/parcels_region_north.gpkg \
  datasets/vector/raw/parcels_region_north.shp

For datum changes (for example NAD27 → NAD83) the correct .gsb/.gtx transformation grid must be present, because PROJ_NETWORK=OFF makes PROJ silently fall back to a lower-accuracy ballpark shift if the grid is missing. Pre-bundle every required grid into the container image and treat a missing grid as a hard failure:

# Fail the build if a required datum-shift grid was not baked into the image.
for grid in us_noaa_nadcon5_nad27_nad83.tif ca_nrc_ntv2_0.tif; do
  test -f "$PROJ_DATA/$grid" || { echo "FATAL: missing grid $grid"; exit 1; }
done

Validation & Verification #

Before committing to cold storage, assert coordinate bounds, geometry topology, and that the CRS authority code survived the write into the output file metadata.

# 1. Bounds must fall inside the EPSG:4326 envelope.
ogrinfo datasets/vector/normalized/parcels_region_north.gpkg -al -so | grep -i "Extent"

# 2. Serialize to the archival columnar format with topology promotion.
ogr2ogr -f Parquet \
  datasets/vector/archive/parcels_region_north.parquet \
  datasets/vector/normalized/parcels_region_north.gpkg \
  -nlt PROMOTE_TO_MULTI -lco COMPRESSION=ZSTD

# 3. Assert the embedded CRS on the FINAL artifact, not just the intermediate.
python -c "
import geopandas as gpd, pyproj
df = gpd.read_parquet('datasets/vector/archive/parcels_region_north.parquet')
assert pyproj.CRS(df.crs).equals(pyproj.CRS('EPSG:4326')), 'CRS mismatch in Parquet geo metadata'
print('Schema validation passed.')"

# 4. Checksum the immutable artifact into the audit manifest.
sha256sum datasets/vector/archive/parcels_region_north.parquet >> /var/log/crs_manifest.jsonl

Annotated expected output of step 1:

Extent: (-123.421000, 48.401000) - (-122.118000, 49.002000)

X stays within −180…180 and Y within −90…90, confirming the geometries are in longitude/latitude order rather than projected metres. If you see values in the hundreds of thousands (for example Extent: (472000, 5360000) - …), projected coordinates were written into a geographic column and the artifact must be rejected. The -lco COMPRESSION=ZSTD flag only sets a default level; tune it deliberately with ZSTD Level Configuration for Spatial Files after — never before — coordinate precision is fixed at this CRS stage.

Troubleshooting #

Operational note: never rely on implicit OGR driver defaults for CRS normalization — they prioritise throughput over projection fidelity and can drop vertical datums or apply heuristic shifts without logging. Enforce an explicit ogr2ogr -t_srs / pyproj.Transformer pipeline for vector archival outputs, and reserve gdalwarp for raster reprojection.

Operational Execution Checklist #

• PROJ/GDAL data paths pinned and every non-deterministic fallback disabled before the first read
• Source CRS resolved from embedded metadata only; absent or deprecated CRS routed to quarantine
• Canonical WKT2:2019 and source SHA-256 written to the manifest ahead of the transform
• Idempotent guard skips reprojection when source CRS already equals the target
• Required datum-shift grids baked into the image; a missing grid fails the build
• Transformers forced to traditional GIS (lon/lat) axis order
• Post-write bounds, topology, and geo-metadata assertions gate the commit
• Final artifact checksummed into the immutable audit manifest
• Raw untransformed source retained in a separate cold tier for forensic re-projection

Related #

• Up one level: CRS Synchronization in Pipelines — the parent design page covering target-CRS selection, quarantine policy, and write-time partition constraints this procedure implements.
• GeoParquet Migration Workflows — the sibling conversion path where the normalised CRS is embedded into columnar geometry metadata during the archival write.
• Schema Mapping & Attribute Validation — the attribute contract that runs alongside reprojection so a precision change never breaks a join key.
• Metadata Cataloging & Discovery — the catalog layer that records the transformation pipeline string and grid version as lineage for every committed artifact.