How to Write a GeoDataFrame to PostGIS

Problem statement

to_postgis() is one line, and it works the first time. It is the second time that causes problems.

gdf.to_postgis("parcels", engine, if_exists="replace")

That call drops the table and recreates it from whatever pandas happens to have in memory — without your primary key, without your NOT NULL constraints, without the indexes you named, and with column types inferred from the frame. This page does it to a real table: HM Land Registry's 21,253 INSPIRE parcels for Rutland, loaded into PostGIS 3 through GeoPandas 1.1 and GeoAlchemy2.

Then there are the writes that fail outright. Each of these was produced on this page:

for label, attempt in FAILURES.items():
    try:
        attempt()
        print(f"{label:24} no error")
    except Exception as exc:
        print(f"{label:24} {type(exc).__name__}: {str(exc).splitlines()[0][:95]}")

no PostGIS extension     ProgrammingError: (psycopg.errors.UndefinedObject) type "geometry" does not exist
two CRSs concatenated    ValueError: Cannot determine common CRS for concatenation inputs, got ['OSGB36 / British National Grid', 'W
null into NOT NULL       NotNullViolation: null value in column "geom" of relation "parcels_strict" violates not-null constraint
mixed-case column        UndefinedColumn: column "surveyDate" of relation "parcels_case" does not exist
frame in EPSG:4326       ValueError: The CRS of the target table (EPSG:27700) differs from the CRS of current GeoDataFrame (EPSG:432

Writing spatial data to a database well means deciding three things to_postgis defaults badly on: what happens to the existing table, what types the columns get, and what happens when it fails halfway.

Quick answer

Create the table deliberately once, then only ever append or upsert into it:

# 1. the table, its types, its constraints and its index — defined once, in SQL
with engine.begin() as conn:
    conn.execute(text("""
        DROP TABLE IF EXISTS parcels;
        CREATE TABLE parcels (
            parcel_id  bigint PRIMARY KEY,
            ward       text NOT NULL,
            valid_from date,
            geom       geometry(MultiPolygon, 27700) NOT NULL
        );
        CREATE INDEX parcels_geom_idx ON parcels USING GIST (geom);
    """))

# 2. make the frame match the table before writing
frame = prepare(raw)                       # lower-case names, 27700, dates, ward

# 3. append, never replace
with engine.begin() as conn:
    frame.to_postgis("parcels", conn, if_exists="append", index=False, chunksize=5_000)
print(pd.read_sql("SELECT count(*) AS rows, min(GeometryType(geom)) AS type FROM parcels", engine))

    rows          type
0  21253  MULTIPOLYGON

The rules that follow from this:

What replace actually destroys

def describe(table):
    idx = pd.read_sql(text("SELECT indexname FROM pg_indexes WHERE tablename = :t"), engine, params={"t": table})
    con = pd.read_sql(text("SELECT conname, contype FROM pg_constraint WHERE conrelid = CAST(:t AS regclass)"),
                      engine, params={"t": table})
    cols = pd.read_sql(text("SELECT column_name, udt_name, is_nullable FROM information_schema.columns "
                            "WHERE table_name = :t ORDER BY ordinal_position"), engine, params={"t": table})
    geom = pd.read_sql(text("SELECT type, srid FROM geometry_columns WHERE f_table_name = :t"), engine, params={"t": table})
    return {"indexes": idx["indexname"].tolist(), "constraints": con.values.tolist(),
            "columns": [f"{c} {t}{'' if n == 'YES' else ' NOT NULL'}" for c, t, n in cols.values],
            "geometry": geom.values.tolist()}

print("before:", describe("parcels"))
frame.to_postgis("parcels", engine, if_exists="replace", index=False)
print("after: ", describe("parcels"))

before: {'indexes': ['parcels_pkey', 'parcels_geom_idx'], 'constraints': [['parcels_pkey', 'p']], 'columns': ['parcel_id int8 NOT NULL', 'ward text NOT NULL', 'valid_from date', 'geom geometry NOT NULL'], 'geometry': [['MULTIPOLYGON', 27700]]}
after:  {'indexes': ['idx_parcels_geom'], 'constraints': [], 'columns': ['parcel_id int8', 'ward text', 'valid_from date', 'geom geometry'], 'geometry': [['POLYGON', 27700]]}

The primary key, both NOT NULLs and the index you named are gone, parcel_id is whatever pandas held, and the geometry column is now declared POLYGON because that is what most rows were. One thing survives, in a new form: GeoAlchemy2 creates a GIST index of its own, idx_parcels_geom, so a spatial query does not suddenly fall back to a sequential scan — but a second load with your own CREATE INDEX would now leave two.

Match the CRS, normalise names, write

1. Match the CRS before you write, not after

The geometry column carries an SRID. A frame in another CRS is refused — and GeoPandas refuses it before PostGIS sees it, by comparing the frame's CRS with the target table's:

TARGET_SRID = 27700

def match_crs(gdf, srid=TARGET_SRID):
    if gdf.crs is None:
        raise ValueError("layer has no CRS; refusing to guess")
    return gdf if gdf.crs.to_epsg() == srid else gdf.to_crs(srid)

print(match_crs(raw.to_crs(4326)).crs.to_epsg())

27700

Never set the CRS with gdf.set_crs(27700, allow_override=True) to make an error go away. That relabels the coordinates without moving them — degrees read as metres put Rutland about fifty metres from the grid's origin, in the sea south-west of the Isles of Scilly. See cannot transform naive geometries for the difference.

2. Normalise column names before PostgreSQL does it for you

PostgreSQL folds unquoted identifiers to lowercase. pandas quotes every column name it writes, so a frame column surveyDate is sent as "surveyDate" — which a table created with surveydate does not have, as the failure list above showed.

import re

def normalise_columns(gdf):
    gdf = gdf.rename(columns=lambda c: re.sub(r"[^0-9a-z_]", "_", c.strip().lower()))
    # a leading digit is a syntax error as a bare identifier
    return gdf.rename(columns=lambda c: f"c_{c}" if c[0].isdigit() else c)

print(normalise_columns(raw).columns.tolist())

['gml_id', 'inspireid', 'label', 'nationalcadastralreference', 'validfrom', 'beginlifespanversion', 'geometry']

Do this once, at the boundary. Every downstream query gets simpler.

3. Fix the geometry type mismatch deliberately

A declared geometry(Polygon, 27700) column rejects a MultiPolygon, and real parcel layers contain both. Two honest options:

from shapely.geometry import MultiPolygon

# Option A — declare the column as MultiPolygon, and promote in the frame too
promoted = raw.geometry.apply(lambda g: g if g.geom_type == "MultiPolygon" else MultiPolygon([g]))

# Option B — declare Polygon and split multiparts into separate rows
exploded = raw.explode(index_parts=False).reset_index(drop=True)
print(raw.geom_type.value_counts().to_dict(), "->", len(exploded), "rows after explode")

{'Polygon': 21253} -> 21253 rows after explode

PostGIS promotes in this direction by itself: the quick answer appended plain Polygons into a geometry(MultiPolygon, 27700) column and every row came back as a one-part MULTIPOLYGON. Promoting in the frame as well only keeps geom_type assertions honest on both sides. Every Rutland parcel is a single Polygon, so exploding changes nothing here — on a layer with island pairs it changes the row count, which matters if anything downstream sums by row; see how to split multipart geometries.

The option that is not honest is declaring the column as bare geometry, which accepts anything and moves the problem to whoever reads the table next.

4. Set the types you care about explicitly

to_postgis accepts a dtype mapping, and it is worth using for anything pandas might guess differently from you:

from geoalchemy2 import Geometry
from sqlalchemy.types import BigInteger, Date, Text

samples = {
    "python dates": frame.head(20),
    "python dates, one None": frame.head(20).assign(valid_from=[None] + frame["valid_from"].head(19).tolist()),
    "date strings (as from a CSV)": frame.head(20).assign(valid_from=frame["valid_from"].head(20).astype(str)),
}
for label, sample in samples.items():
    sample.to_postgis("types_demo", engine, if_exists="replace", index=False)
    print(f"{label:30}", describe("types_demo")["columns"][2])
sample.to_postgis("types_demo", engine, if_exists="replace", index=False,
                  dtype={"parcel_id": BigInteger(), "ward": Text(), "valid_from": Date(),
                         "geom": Geometry("MULTIPOLYGON", srid=27700)})
print(f"{'strings, with dtype=':30}", describe("types_demo")["columns"][2], describe("types_demo")["geometry"])

python dates                   valid_from date
python dates, one None         valid_from date
date strings (as from a CSV)   valid_from text
strings, with dtype=           valid_from date [['POLYGON', 27700]]

A column of Python dates became date even with a None in it. A column of date strings — what a CSV gives you — became text, and would stay text forever once the table exists. The dtype mapping fixed the attribute column. It did not fix the geometry: asked for MULTIPOLYGON, the column came out POLYGON, because GeoPandas sets the geometry column's type from the data and its own setting won. Declare the geometry type in a CREATE TABLE, not in dtype.

5. Chunk large writes, and know what a failure leaves behind

broken = frame.copy()
broken.loc[broken.index[-10], "geom"] = None            # one bad row near the end
for how in ("engine", "conn"):
    with engine.begin() as conn:
        conn.execute(text("TRUNCATE parcels_strict"))
    try:
        if how == "engine":
            broken.to_postgis("parcels_strict", engine, if_exists="append", index=False, chunksize=1_000)
        else:
            with engine.begin() as conn:
                broken.to_postgis("parcels_strict", conn, if_exists="append", index=False, chunksize=1_000)
    except Exception as exc:
        print(f"{how:6} {type(exc).__name__}", end=" | ")
    left = pd.read_sql("SELECT count(*) FROM parcels_strict", engine).iloc[0, 0]
    print(f"rows left behind: {left}")

engine NotNullViolation | rows left behind: 0
conn   NotNullViolation | rows left behind: 0

chunksize controls how many rows go per INSERT, not how many transactions there are. Both ways of calling it here rolled back completely when the last chunk hit the NOT NULL constraint: pandas wraps the whole write in one transaction even when it is handed an engine. The reason to pass a connection from engine.begin() is different — it lets the write share a transaction with the CREATE TABLE, the DELETE or the index build around it, so the whole load commits or none of it does.

6. Verify the write, in the database

with engine.connect() as conn:
    checks = conn.execute(text("""
        SELECT count(*)                                       AS rows,
               count(*) FILTER (WHERE NOT ST_IsValid(geom))  AS invalid,
               count(DISTINCT ST_SRID(geom))                  AS srids,
               min(ST_SRID(geom))                             AS srid
        FROM parcels
    """)).mappings().one()
assert checks["invalid"] == 0, f"{checks['invalid']} invalid geometries landed"
assert checks["srids"] == 1 and checks["srid"] == TARGET_SRID
print(dict(checks))

{'rows': 21253, 'invalid': 0, 'srids': 1, 'srid': 27700}

Counting in Python before the write tells you what you meant to send. Counting in SQL after it tells you what arrived.

An idempotent upsert

The data used on this page

HM Land Registry's INSPIRE Index Polygons for Rutland (© Crown copyright and database rights; OGL), with each parcel's ward from the ONS December 2024 boundaries (OGL), written to a PostGIS 3 database in Docker (postgis/postgis, port 55433 here).

import re
from pathlib import Path

import geopandas as gpd
import pandas as pd
import requests
from sqlalchemy import create_engine, text

Path("data").mkdir(exist_ok=True)
if not Path("data/Rutland.zip").exists():
    Path("data/Rutland.zip").write_bytes(requests.get(
        "https://use-land-property-data.service.gov.uk/datasets/inspire/download/Rutland.zip", timeout=300).content)
if not Path("data/wards.geojson").exists():
    Path("data/wards.geojson").write_bytes(requests.get(
        "https://services1.arcgis.com/ESMARspQHYMw9BZ9/arcgis/rest/services/"
        "Wards_December_2024_Boundaries_UK_BFC/FeatureServer/0/query",
        params={"where": "LAD24CD='E06000017'", "outFields": "WD24NM", "outSR": 27700, "f": "geojson"},
        timeout=120).content)
raw = gpd.read_file("/vsizip/data/Rutland.zip/Land_Registry_Cadastral_Parcels.gml", DOWNLOAD_SCHEMA="NO")
wards = gpd.read_file("data/wards.geojson")
engine = create_engine("postgresql+psycopg://user:guide@localhost:55433/gis")


def prepare(gdf):
    """The frame the parcels table expects: lower-case names, BNG, dates, one ward per parcel."""
    points = gdf.set_geometry(gdf.representative_point())
    ward = gpd.sjoin(points, wards[["WD24NM", "geometry"]], predicate="within", how="left")["WD24NM"]
    out = gpd.GeoDataFrame({"parcel_id": gdf["INSPIREID"].astype("int64"),
                            "ward": ward.fillna("outside Rutland"),
                            "valid_from": gdf["VALIDFROM"].dt.date},
                           geometry=gdf.geometry.values, crs=gdf.crs).rename_geometry("geom")
    return out


frame = prepare(raw)
with engine.begin() as conn:
    conn.execute(text("DROP TABLE IF EXISTS parcels_strict; "
                      "CREATE TABLE parcels_strict (parcel_id bigint PRIMARY KEY, ward text, valid_from date, "
                      "geom geometry(MultiPolygon, 27700) NOT NULL)"))
    conn.execute(text("DROP TABLE IF EXISTS parcels_case; "
                      "CREATE TABLE parcels_case (parcel_id bigint, surveydate date, geom geometry(MultiPolygon, 27700))"))
    conn.execute(text("DROP TABLE IF EXISTS parcels; CREATE TABLE parcels (parcel_id bigint, geom geometry(MultiPolygon, 27700))"))


def no_postgis_extension():
    with engine.connect().execution_options(isolation_level="AUTOCOMMIT") as conn:
        conn.execute(text("DROP DATABASE IF EXISTS nopostgis"))
        conn.execute(text("CREATE DATABASE nopostgis"))
    plain = create_engine("postgresql+psycopg://user:guide@localhost:55433/nopostgis")
    try:
        frame.head(5).to_postgis("parcels", plain, index=False)
    finally:
        plain.dispose()
        with engine.connect().execution_options(isolation_level="AUTOCOMMIT") as conn:
            conn.execute(text("DROP DATABASE nopostgis"))


one_null = frame.head(3).copy()
one_null.loc[one_null.index[0], "geom"] = None
FAILURES = {
    "no PostGIS extension": no_postgis_extension,
    "two CRSs concatenated": lambda: pd.concat([frame.head(2), frame.head(2).to_crs(4326)]),
    "null into NOT NULL": lambda: one_null.to_postgis("parcels_strict", engine, if_exists="append", index=False),
    "mixed-case column": lambda: frame.head(3).drop(columns="ward").rename(columns={"valid_from": "surveyDate"})
        .to_postgis("parcels_case", engine, if_exists="append", index=False),
    "frame in EPSG:4326": lambda: frame.head(3).to_crs(4326)
        .to_postgis("parcels", engine, if_exists="append", index=False),
}
print(f"{len(frame):,} parcels ready; wards: {frame['ward'].nunique()}; "
      f"PostGIS {pd.read_sql('SELECT postgis_lib_version()', engine).iloc[0, 0]}")

21,253 parcels ready; wards: 16; PostGIS 3.4.3

Example 1: an idempotent upsert instead of replace

The real requirement is usually "make the table match this frame" — which is an upsert, not a replace.

def upsert_parcels(gdf, engine, key="parcel_id"):
    """Load into a staging table, then merge on the key. Safe to re-run."""
    with engine.begin() as conn:
        gdf.to_postgis("parcels_staging", conn, if_exists="replace", index=False)
        result = conn.execute(text(f"""
            INSERT INTO parcels (parcel_id, ward, valid_from, geom)
            SELECT parcel_id, ward, valid_from, ST_Multi(geom) FROM parcels_staging
            ON CONFLICT ({key}) DO UPDATE SET
                ward       = EXCLUDED.ward,
                valid_from = EXCLUDED.valid_from,
                geom       = EXCLUDED.geom
            WHERE parcels.geom IS DISTINCT FROM EXCLUDED.geom
               OR parcels.ward IS DISTINCT FROM EXCLUDED.ward
        """))
        conn.execute(text("DROP TABLE parcels_staging"))
    return result.rowcount


with engine.begin() as conn:
    conn.execute(text("""DROP TABLE parcels; CREATE TABLE parcels (parcel_id bigint PRIMARY KEY, ward text NOT NULL,
                          valid_from date, geom geometry(MultiPolygon, 27700) NOT NULL)"""))
print("first run: ", upsert_parcels(frame, engine), "rows written")
print("second run:", upsert_parcels(frame, engine), "rows written")
changed = frame.head(100).assign(ward="Oakham North East")
print("100 re-warded:", upsert_parcels(changed, engine), "rows written")

first run:  21253 rows written
second run: 0 rows written
100 re-warded: 100 rows written

The WHERE ... IS DISTINCT FROM clause makes it cheap to re-run: unchanged rows are not rewritten, so the second run writes nothing and the third writes only the rows whose ward actually changed. That is the idempotency property that makes a job safe to run twice.

Example 2: writing a whole folder, one table per file

def load_folder(folder: Path, engine, srid=27700):
    report = []
    for path in sorted(folder.glob("*.gpkg")):
        table = re.sub(r"[^0-9a-z_]", "_", path.stem.lower())
        try:
            gdf = normalise_columns(gpd.read_file(path).to_crs(srid))
            with engine.begin() as conn:
                gdf.to_postgis(table, conn, if_exists="replace", index=False)
                conn.execute(text(f"ANALYZE {table}"))
            report.append((path.name, table, len(gdf), "ok"))
        except Exception as exc:
            report.append((path.name, table, 0, f"{type(exc).__name__}: {str(exc)[:60]}"))
    return report


Path("data/folder").mkdir(exist_ok=True)
wards.to_file("data/folder/Rutland Wards.gpkg")
raw.head(500).to_file("data/folder/parcels-sample.gpkg")
Path("data/folder/broken.gpkg").write_bytes(b"not a geopackage")
for row in load_folder(Path("data/folder"), engine):
    print(row)

('Rutland Wards.gpkg', 'rutland_wards', 15, 'ok')
('broken.gpkg', 'broken', 0, "DataSourceError: 'data/folder/broken.gpkg' not recognized as being in a suppo")
('parcels-sample.gpkg', 'parcels_sample', 500, 'ok')

if_exists="replace" is defensible here — this is a bulk load of files into fresh tables, and GeoAlchemy2 creates each table's GIST index in the same transaction. The rule is not "never replace", it is "never replace a table someone else has configured". The broken file is reported and the loop goes on.

Example 3: appending only what is new

def append_new_only(gdf, engine, table="parcels", key="parcel_id"):
    existing = pd.read_sql(text(f"SELECT {key} FROM {table}"), engine)[key]
    new = gdf[~gdf[key].isin(existing)]
    if new.empty:
        return 0
    with engine.begin() as conn:
        new.to_postgis(table, conn, if_exists="append", index=False)
    return len(new)


with engine.begin() as conn:
    conn.execute(text("DELETE FROM parcels WHERE parcel_id IN (SELECT parcel_id FROM parcels LIMIT 250)"))
print("appended:", append_new_only(frame, engine), "| again:", append_new_only(frame, engine))

appended: 250 | again: 0

Fine up to a few million keys. Past that, load into a staging table and let the database do the anti-join — pulling every key into pandas stops being the cheap option.

What to_postgis does underneath

to_postgis is a thin wrapper over pandas' to_sql plus GeoAlchemy2's geometry type. Understanding what each layer does explains most of the surprises.

Pandas decides the schema when the table does not exist. It maps dtypes to SQL types, and a column's dtype does not always say what the data is: a column of date strings is object, so it becomes text, and parcel_id becomes whatever integer width the frame happened to hold. This is why creating the table in SQL first is worth the extra lines — you are the one who knows a parcel_id is a bigint and a valid_from is a date.

GeoPandas checks the CRS, and GeoAlchemy2 handles the geometry column. Before appending, GeoPandas compares the frame's CRS with the table's and refuses a mismatch — including a frame with no CRS, reported as EPSG:0. GeoAlchemy2 converts each shape to EWKB, which carries the SRID alongside the coordinates, and on replace it creates a GIST index of its own.

PostGIS enforces the column declaration. geometry(MultiPolygon, 27700) is a constraint checked on every row — and it promotes a Polygon into a one-part MultiPolygon rather than rejecting it. The errors feel obstructive on the first load and become the reason you trust the table six months later.

The last piece is transactional behaviour. pandas runs the whole write in one transaction whether it is given an engine or a connection, so a failure in the last chunk left nothing behind either way. Passing the connection from engine.begin() extends that transaction to your own statements around the write — the staging table, the merge and the drop in Example 1 all commit together or not at all.

Column names and null geometries

• geom vs geometry as a column name. GeoPandas defaults to geometry; the PostGIS convention is geom. Rename with gdf.rename_geometry("geom") so read_postgis(geom_col="geom") works without thinking.
• Null geometries are rejected by a NOT NULL column and accepted by a nullable one. Decide deliberately; a nullable geometry column is a permanent source of confusing joins.
• Empty geometries insert fine and match nothing. Filter with ~gdf.geometry.is_empty before writing.
• Very large writes benefit from COPY, not INSERT. For tens of millions of rows, write to a CSV of EWKB and COPY it, or use ogr2ogr -f PostgreSQL.
• ANALYZE after a bulk load. Without statistics the planner may keep choosing sequential scans on a freshly loaded table.
• A bigserial primary key conflicts with supplying your own ids. Either let the database generate them and do not send the column, or use plain bigint and own the values.
• Reserved words make terrible column names. order, user, end and references all need quoting forever. Rename them at the boundary.
• Writing from multiple processes into one table is fine — that is what the database is for — but each needs its own connection, not a shared engine forked across workers.

Internal links

• PostGIS explained: when a spatial database beats a folder of files — whether to make this move at all
• How to connect GeoPandas to PostGIS — engines, drivers and connection strings
• How to run spatial SQL queries from Python with PostGIS — reading back efficiently
• PostGIS write fails on SRID or geometry type — the specific errors this page prevents
• Cannot transform naive geometries to CRS — why set_crs is not a fix for a CRS mismatch
• How to split multipart geometries into single parts — option B in step 3
• Idempotency explained: why a GIS job must be safe to re-run — the property the upsert gives you
• How to handle credentials and secrets in an automated GIS job — where the password in that connection string should live

FAQ

Why does to_postgis drop my spatial index?

if_exists="replace" drops the table and creates a new one. Indexes, constraints and primary keys belong to the old table and go with it. Use append, or recreate the index in the same transaction.

What is the difference between passing engine and passing conn?

engine gets its own connection and commits per batch, so a mid-write failure leaves partial data. conn from with engine.begin() joins your transaction, so the write is all-or-nothing.

How do I stop pandas guessing my column types?

Create the table in SQL first and append into it, or pass an explicit dtype mapping. Both work; the first also gets you constraints and indexes.

My geometry column ends up as SRID 0. Why?

The GeoDataFrame had crs=None, so GeoAlchemy2 had nothing to write. Set the CRS correctly with to_crs — or set_crs only if the coordinates genuinely are in that CRS and were merely unlabelled.

Can I write a mix of Polygons and MultiPolygons?

Not into a geometry(Polygon, …) column. Promote everything to MultiPolygon, explode everything to single parts, or declare the column as geometry(Geometry, …) and accept that readers must handle both.

How fast is to_postgis for millions of rows?

Adequate with chunksize, but INSERT-based. For very large loads, ogr2ogr -f PostgreSQL or a COPY of EWKB is several times faster.

Should the table have a primary key?

Yes. Without one you cannot upsert, cannot reliably delete a single row, and cannot join the table to itself. parcel_id from the source data is better than a generated id if it is genuinely stable.