This recipe supports the PostGIS vs DuckDB spatial for analytical loads comparison within the wider Orchestrating Spatial ETL Pipelines reference. Once you have chosen PostGIS as the load target, this is the write path that makes the Load stage safe to retry.

Loading GeoDataFrames into PostGIS with GeoPandas

The Problem: The One-Line Load Is Not Production Safe

gdf.to_postgis("features", engine, if_exists="append") works perfectly in a notebook and fails quietly in a pipeline. The single call handles the happy path and nothing else, and a scheduled task will eventually hit every unhandled case at once.

  • Duplicate geometries on retry: if_exists="append" re-inserts every row on a second run, so any orchestrated retry doubles the data. There is no key, so nothing dedupes it.
  • SRID drift: if the GeoDataFrame’s CRS is unset or mixed, PostGIS stores the geometry with SRID 0, and every later ST_Intersects against a properly-tagged table silently returns nothing.
  • All-or-nothing memory blowups: writing a multi-million-row GeoDataFrame in one statement buffers the entire INSERT, spiking memory on the worker and holding one long transaction open.
  • Missing spatial index: to_postgis never creates a GiST index, so the first analytical query does a sequential scan over the whole table and everyone assumes PostGIS is slow.

The fix is a small, boring function that enforces the SRID, streams rows into a staging table in chunks, upserts into the live target on a stable key, and builds the index once. The rest of this page is that function.

Version and Environment Compatibility

Library Version Note
GeoPandas >=1.0 to_postgis chunksize and stable WKB writing; pin to avoid the 0.x SRID quirks
SQLAlchemy >=2.0 Engine / text() API used throughout; 1.4 URL strings still parse
GeoAlchemy2 >=0.15 Registers the geometry type so to_postgis emits a typed column
PostGIS 3.4+ on PostgreSQL 15+ ON CONFLICT upsert and GiST on geometry; earlier versions work but tune vacuum 
pip install "geopandas>=1.0" "SQLAlchemy>=2.0" "geoalchemy2>=0.15" "psycopg[binary]>=3.1"

The Staging-Table Upsert Flow

The load never writes directly to the live table. It lands rows in a transient staging table, then swaps them into the target with an atomic upsert. The diagram traces the path a single batch takes.

GeoDataFrame to PostGIS staging-table upsert flow Left to right: a GeoDataFrame is reprojected to one known SRID, then written in chunks via to_postgis into a staging table. Inside a single transaction, an INSERT SELECT ON CONFLICT upserts the staged rows into the live target table keyed on a stable identifier. Finally a GiST index is ensured on the geometry column and the staging table is dropped. GeoDataFrame to_crs(srid) one known SRID chunks staging table to_postgis chunksize=N one transaction INSERT … SELECT … ON CONFLICT DO UPDATE — keyed on stable id live target table no duplicate rows on re-run GiST index ensure once drop staging

Staging first is what buys idempotency: the live table is only ever touched by the keyed upsert, so a retried task converges on the same rows instead of stacking duplicates.

The load_to_postgis Recipe

One copy-pasteable function. It enforces the SRID, streams into a staging table, upserts on a caller-supplied key, ensures the GiST index, and cleans up — all with logging and the transactional guarantees a scheduled task needs.

import logging

import geopandas as gpd
from sqlalchemy import Engine, text

logger = logging.getLogger(__name__)


def load_to_postgis(
    gdf: gpd.GeoDataFrame,
    engine: Engine,
    table: str,
    key: str = "feature_id",
    srid: int = 4326,
    geom_col: str = "geometry",
    chunksize: int = 50_000,
) -> int:
    """Idempotently load a GeoDataFrame into a PostGIS table via a staging upsert.

    Reprojects to a single SRID, streams rows into a transient staging table,
    then upserts into the live target keyed on ``key`` so re-runs overwrite
    rather than append. Ensures a GiST index on the geometry column.

    Returns the number of rows written from this batch.
    """
    if gdf.empty:
        logger.info("Empty GeoDataFrame for %s — nothing to load.", table)
        return 0

    # 1. Enforce one known SRID. An unset CRS would land as SRID 0 in PostGIS.
    if gdf.crs is None:
        raise ValueError(
            f"GeoDataFrame has no CRS; refusing to load into {table} with SRID {srid}."
        )
    if gdf.crs.to_epsg() != srid:
        logger.info("Reprojecting %d features to EPSG:%d", len(gdf), srid)
        gdf = gdf.to_crs(epsg=srid)

    if key not in gdf.columns:
        raise KeyError(f"Upsert key {key!r} not present in GeoDataFrame columns.")

    staging = f"_stg_{table}"
    cols = [c for c in gdf.columns]  # preserve column order for the INSERT SELECT
    col_list = ", ".join(f'"{c}"' for c in cols)
    update_set = ", ".join(
        f'"{c}" = EXCLUDED."{c}"' for c in cols if c != key
    )

    # 2. Stream the batch into a fresh staging table in chunks.
    gdf.to_postgis(staging, engine, if_exists="replace", index=False, chunksize=chunksize)

    with engine.begin() as conn:  # single transaction: upsert + index + cleanup
        # 3. Ensure the live target exists with the same shape as staging.
        conn.execute(
            text(f'CREATE TABLE IF NOT EXISTS "{table}" '
                 f'(LIKE "{staging}" INCLUDING DEFAULTS)')
        )
        # A stable key is what makes ON CONFLICT possible.
        conn.execute(
            text(f'ALTER TABLE "{table}" '
                 f'ADD CONSTRAINT IF NOT EXISTS "{table}_pk" PRIMARY KEY ("{key}")')
        )
        # 4. Upsert staged rows into the live target on the key.
        result = conn.execute(
            text(
                f'INSERT INTO "{table}" ({col_list}) '
                f'SELECT {col_list} FROM "{staging}" '
                f'ON CONFLICT ("{key}") DO UPDATE SET {update_set}'
            )
        )
        # 5. Build the GiST index once, then refresh planner statistics.
        conn.execute(
            text(f'CREATE INDEX IF NOT EXISTS "{table}_{geom_col}_gix" '
                 f'ON "{table}" USING GIST ("{geom_col}")')
        )
        conn.execute(text(f'ANALYZE "{table}"'))
        # 6. Drop the staging table inside the same transaction.
        conn.execute(text(f'DROP TABLE IF EXISTS "{staging}"'))

    written = result.rowcount if result.rowcount is not None else len(gdf)
    logger.info("Upserted %d features into %s (SRID %d)", written, table, srid)
    return written

Key Implementation Notes

  • The CRS check refuses to guess. An unset CRS raises rather than silently defaulting, because a wrong SRID is invisible until a spatial join returns zero rows in production. The reprojection normalizes to one EPSG so every geometry in the target shares a reference system — the same discipline the load-target comparison depends on when it benchmarks ST_Area across engines.
  • Staging plus ON CONFLICT is the whole idempotency story. The live table is only mutated by the keyed upsert, so a second run of the same batch converges to identical state. Nothing appends blindly, which is exactly why an orchestrated retry is safe.
  • ADD CONSTRAINT IF NOT EXISTS needs PostgreSQL 15+. On older servers, guard the constraint creation with a catalog lookup instead; the primary key is mandatory because ON CONFLICT requires a unique constraint on the conflict target.
  • The index is built once, after the load, not per chunk. Maintaining a GiST index across a 50k-row-per-chunk stream taxes every insert; creating it after the rows land and then running ANALYZE is both faster and leaves the planner with fresh selectivity statistics.
  • Everything after the staging write is one transaction. The upsert, index creation, statistics refresh, and staging drop either all commit or all roll back, so a mid-load crash never leaves a half-swapped target or an orphaned index.
  • chunksize bounds memory, not correctness. Larger chunks are faster but hold more rows in the INSERT buffer; 50k is a safe default for wide geometry rows, and you can raise it for narrow point tables.

Troubleshooting the Load Path

Failure Root Cause Fix
Geometry column stored with SRID 0 GeoDataFrame CRS was unset before writing Call set_crs/to_crs and let the recipe’s guard reject an unset CRS
ON CONFLICT errors: “no unique constraint matching” Target table has no primary key on the upsert key Ensure the ADD CONSTRAINT step ran; the conflict target must be unique
Duplicate rows still appear after a retry Load wrote straight to the target with append instead of staging Route every write through the staging table and the keyed upsert
First analytical query is a slow sequential scan GiST index missing or planner stats stale Confirm the CREATE INDEX ... GIST ran, then ANALYZE the table
Worker memory spikes during load Single-statement insert of the full GeoDataFrame Set a chunksize so to_postgis streams the batch
Load holds a long lock and blocks readers Index maintained during the chunked insert Build the index once after the rows land, not per chunk

Integration Note

This function is the body of the Load task when PostGIS is the target chosen in the PostGIS vs DuckDB spatial for analytical loads comparison. Because it upserts on a stable key inside one transaction, it satisfies the exactly-once contract an orchestrator expects — call it from a task that has already passed geometry and schema gates, and a retry costs nothing but a repeated upsert. The same keyed-write discipline generalizes to any engine, which is why it mirrors the pattern in writing idempotent spatial ETL tasks in Airflow: key on an identifier, mutate atomically, and let re-runs converge.