🔧 Fix PostGIS & databases CRS & projections Spatial SQL & PostGIS · step 13 of 15 Updated

PostGIS Write Fails on SRID or Geometry Type: How to Fix It

Problem statement

The load worked in testing. In production it stops on row 4,112 with one of these:

psycopg.errors.InternalError_: Geometry SRID (0) does not match column SRID (27700)

psycopg.errors.InternalError_: Geometry type (MultiPolygon) does not match
    column type (Polygon)

psycopg.errors.NotNullViolation: null value in column "geom" of relation
    "parcels" violates not-null constraint

psycopg.errors.InternalError_: Geometry has Z dimension but column does not

Every one of these is the database refusing data that does not match the column you declared. That is the column doing its job — the alternative is a table where half the rows are in the wrong coordinate system and nobody finds out for a year.

The fix is never to widen the column until the error stops. It is to work out which of four things the incoming geometry disagrees about: its SRID, its geometry type, its dimensionality, or its nullity.

Quick answer

Diagnose in one query against the frame you are about to write, then fix the specific mismatch:

def describe_geometry(gdf):
    """What PostGIS is about to object to."""
    return {
        "crs":        str(gdf.crs),
        "srid":       gdf.crs.to_epsg() if gdf.crs else None,
        "types":      sorted(gdf.geom_type.dropna().unique().tolist()),
        "has_z":      bool(gdf.geometry.has_z.any()),
        "nulls":      int(gdf.geometry.isna().sum()),
        "empties":    int(gdf.geometry.is_empty.sum()),
        "invalid":    int((~gdf.is_valid).sum()),
    }

print(describe_geometry(gdf))
# {'crs': None, 'srid': None, 'types': ['Polygon', 'MultiPolygon'],
#  'has_z': True, 'nulls': 3, 'empties': 1, 'invalid': 0}

That output explains all four errors at once. The fixes, in the order they should be applied:

Error says Actually means Fix
SRID (0) does not match the frame has no CRS to_crs(target), or set_crs only if already correct
SRID (4326) does not match (27700) wrong CRS, right label gdf = gdf.to_crs(27700)
type (MultiPolygon) does not match (Polygon) mixed single/multi parts promote all to Multi, or explode()
has Z dimension but column does not 3D coordinates shapely.force_2d, or declare PolygonZ
null value ... violates not-null null geometries in the frame drop them, or make the column nullable 
# the whole fix, in order
gdf = gdf[gdf.geometry.notna() & ~gdf.geometry.is_empty]   # nullity
gdf = gdf.set_geometry(shapely.force_2d(gdf.geometry))     # dimensionality
gdf["geometry"] = [g if g.geom_type.startswith("Multi") else MultiPolygon([g])
                   for g in gdf.geometry]                  # type
gdf = gdf.to_crs(27700)                                    # SRID
gdf.to_postgis("parcels", conn, if_exists="append", index=False)

The four gates, and what each rejects

Four sequential gates checking nullity, dimensionality, geometry type and SRID before an insert.
Four checks, in this order. Fixing SRID first wastes work on rows about to be dropped.

Step-by-step solution

Triage rows pairing each PostGIS write error with its cause and the corresponding fix.
Read the error, not the row number. Each message names its own gate.

1. SRID (0) does not match column SRID — the frame has no CRS

SRID 0 means "unknown". GeoAlchemy2 writes 0 whenever the GeoDataFrame's crs is None, which happens after a GeoJSON read with no CRS member, after building a frame from raw coordinates, and after some concat operations.

print(gdf.crs)          # None

The fix depends on a question only you can answer: are the coordinates already in the target CRS?

# The coordinates ARE in 27700, they were just unlabelled.
gdf = gdf.set_crs(27700)                 # relabel, do not move

# The coordinates are in something else (or you do not know).
gdf = gdf.set_crs(4326).to_crs(27700)    # label the truth, then transform

Getting this backwards puts your data in the wrong place without any error, which is much worse than the write failing. A quick sanity check:

print(gdf.total_bounds)
# [-3.25 55.90 -3.10 56.00]  → degrees, so it is 4326-ish, not 27700
# [ 320000 670000 330000 680000 ] → metres, consistent with British National Grid

Degrees are between -180 and 180. If your bounds look like that and you were about to call set_crs(27700), stop.

2. SRID (4326) does not match column SRID (27700) — reproject

The frame is correctly labelled and in the wrong CRS. This is the easy one.

TARGET = 27700
if gdf.crs.to_epsg() != TARGET:
    gdf = gdf.to_crs(TARGET)

Make it a guard rather than a one-off, because the next file from the same supplier will be in a third CRS:

def conform(gdf, 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)

3. Geometry type does not match column type — decide on multiparts

A geometry(Polygon, 27700) column takes single-part polygons only. Almost every real dataset has a few multiparts.

print(gdf.geom_type.value_counts())
# Polygon         4109
# MultiPolygon       3

Three rows are stopping a 4,112-row load. Two honest fixes:

from shapely.geometry import MultiPolygon

# A — promote everything, keep one row per feature (usually right)
gdf["geometry"] = [
    g if g.geom_type == "MultiPolygon" else MultiPolygon([g]) for g in gdf.geometry
]
# then the column must be geometry(MultiPolygon, 27700)

# B — split multiparts into rows, keep the column as Polygon
gdf = gdf.explode(index_parts=False).reset_index(drop=True)

Verified behaviour worth knowing: PostGIS promotes automatically in this direction. Inserting a single-part Polygon into a geometry(MultiPolygon, …) column succeeds and stores a one-part MultiPolygon, so option A needs only the column declaration — the Python loop above just makes the frame match what will be stored, which keeps geom_type assertions honest on both sides.

Option B changes the row count, so anything that sums or counts by row now counts differently. If the ids must stay unique, add a part number before exploding — see how to split multipart geometries.

The tempting third option — redeclare the column as bare geometry — accepts everything and pushes the problem onto whoever queries the table. Sometimes correct for a staging table; rarely correct for the table of record.

4. Geometry has Z dimension but column does not — drop or declare Z

Survey data, LiDAR-derived outlines and some CAD exports carry a Z coordinate you may not have noticed.

print(gdf.geometry.has_z.sum())    # 4112 — all of them
import shapely

# drop Z, if the elevation is not part of the analysis
gdf = gdf.set_geometry(shapely.force_2d(gdf.geometry))
-- or keep it, and say so in the column
ALTER TABLE parcels
  ALTER COLUMN geom TYPE geometry(MultiPolygonZ, 27700) USING geom;

Keep Z only if something reads it. A Z of 0.0 on every vertex is worse than no Z — it doubles the storage and implies data that is not there.

5. null value in column "geom" violates not-null constraint 

print(gdf.geometry.isna().sum())      # 3
print(gdf.geometry.is_empty.sum())    # 1

Nulls and empties are different, and both cause trouble. A null is rejected by a NOT NULL column. An empty geometry is accepted — it is not null — and then matches nothing in every spatial query afterwards, which is a subtler bug.

before = len(gdf)
gdf = gdf[gdf.geometry.notna() & ~gdf.geometry.is_empty]
dropped = before - len(gdf)
if dropped:
    print(f"dropped {dropped} rows with null or empty geometry")

Log the count. Silently dropping rows in a load is how row counts stop reconciling.

6. Validate before you write, so the error arrives early

The database checks per row and stops on the first failure, which tells you nothing about the other 4,000. Check the whole frame first:

def check_writable(gdf, *, srid, geom_type, allow_z=False):
    problems = []
    if gdf.crs is None:
        problems.append("no CRS set")
    elif gdf.crs.to_epsg() != srid:
        problems.append(f"CRS is {gdf.crs.to_epsg()}, column expects {srid}")
    bad_types = sorted(set(gdf.geom_type.dropna()) - {geom_type})
    if bad_types:
        counts = gdf.geom_type.value_counts()
        problems.append(
            "unexpected types: " + ", ".join(f"{t} ({counts[t]})" for t in bad_types)
        )
    if not allow_z and gdf.geometry.has_z.any():
        problems.append(f"{gdf.geometry.has_z.sum()} geometries have Z")
    if gdf.geometry.isna().any():
        problems.append(f"{gdf.geometry.isna().sum()} null geometries")
    if gdf.geometry.is_empty.any():
        problems.append(f"{gdf.geometry.is_empty.sum()} empty geometries")
    if problems:
        raise ValueError("not writable:\n  - " + "\n  - ".join(problems))

One call, every problem listed at once, before a single row is sent.

Code examples

Example 1: the conforming loader 

import geopandas as gpd
import shapely
from shapely.geometry import MultiPolygon

def conform_for_postgis(gdf, *, srid=27700, multi=True, keep_z=False):
    """Make a frame match a geometry(MultiPolygon, srid) column, reporting losses."""
    report = {"in": len(gdf)}

    gdf = gdf[gdf.geometry.notna() & ~gdf.geometry.is_empty].copy()
    report["dropped_empty"] = report["in"] - len(gdf)

    if not keep_z and gdf.geometry.has_z.any():
        report["flattened_z"] = int(gdf.geometry.has_z.sum())
        gdf = gdf.set_geometry(shapely.force_2d(gdf.geometry))

    if gdf.crs is None:
        raise ValueError("layer has no CRS — set it deliberately before loading")
    if gdf.crs.to_epsg() != srid:
        report["reprojected_from"] = gdf.crs.to_epsg()
        gdf = gdf.to_crs(srid)

    if multi:
        promoted = (~gdf.geom_type.str.startswith("Multi")).sum()
        if promoted:
            report["promoted_to_multi"] = int(promoted)
            gdf["geometry"] = [
                g if g.geom_type.startswith("Multi") else MultiPolygon([g])
                for g in gdf.geometry
            ]

    invalid = (~gdf.is_valid).sum()
    if invalid:
        report["repaired_invalid"] = int(invalid)
        gdf["geometry"] = gdf.geometry.make_valid()

    report["out"] = len(gdf)
    return gdf, report
gdf, report = conform_for_postgis(gpd.read_file("parcels.shp"))
print(report)
# {'in': 4112, 'dropped_empty': 4, 'flattened_z': 4108,
#  'reprojected_from': 4326, 'promoted_to_multi': 4105, 'out': 4108}

The report is the point. A loader that silently fixes things is a loader that hides a supplier sending you the wrong CRS every week.

Example 2: finding the offending rows instead of guessing

When the error names a row you cannot find, ask the frame:

odd = gdf[gdf.geom_type != "Polygon"]
print(odd[["parcel_id", "geom_type"]].assign(geom_type=odd.geom_type))

with_z = gdf[gdf.geometry.has_z]
print(f"{len(with_z)} rows carry Z, first ids: {with_z['parcel_id'].head().tolist()}")

And after a partial load, ask the database what actually landed:

SELECT ST_SRID(geom) AS srid,
       GeometryType(geom) AS type,
       ST_NDims(geom) AS dims,
       count(*)
FROM parcels
GROUP BY 1, 2, 3
ORDER BY 4 DESC;

A table with more than one row in that result is a table that was loaded without these checks.

Example 3: fixing a column that is already wrong 

-- geometries are in 27700 but the column says 0
SELECT DISTINCT ST_SRID(geom) FROM parcels;        -- 0

-- relabel in place (coordinates unchanged — only correct if they really are 27700)
ALTER TABLE parcels
  ALTER COLUMN geom TYPE geometry(MultiPolygon, 27700)
  USING ST_SetSRID(geom, 27700);

-- genuinely reproject (coordinates move)
ALTER TABLE parcels
  ALTER COLUMN geom TYPE geometry(MultiPolygon, 27700)
  USING ST_Transform(ST_SetSRID(geom, 4326), 27700);

REINDEX TABLE parcels;    -- the GIST index must be rebuilt after either

ST_SetSRID relabels. ST_Transform moves. The same trap as set_crs versus to_crs, one layer down.

Explanation

Two panels contrasting relabelling coordinates with transforming them, showing where the shape ends up.
Relabelling leaves the coordinates where they are. That is either the fix or the disaster.

A PostGIS geometry column declared as geometry(MultiPolygon, 27700) is enforced by a constraint, checked on every insert. It tests three things: the geometry type, the coordinate dimension, and the SRID. Nothing gets in that disagrees.

That strictness is the reason to use a typed column at all. A bare geometry column accepts a Polygon in 4326, a MultiPolygon in 27700 and a Point with a Z value, all in the same table — and every query afterwards has to cope with the mixture. Most of the "why is this join returning nothing" questions in PostGIS trace back to a table where the SRID varies by row.

The values themselves are simple. A geometry is stored as EWKB — well-known binary with an SRID prefix. That prefix comes from the GeoDataFrame's crs at write time, so crs=None produces SRID=0, which is the literal meaning of the first error message: the geometry says "unknown" and the column says 27700.

The dimension check catches a category of problem people rarely anticipate. A shapefile of building outlines from a survey often carries Z on every vertex — the surveyor's instrument recorded it, nothing stripped it, and gdf.plot() never showed it. It costs storage, breaks equality comparisons against 2D geometry, and confuses ST_Area, which ignores Z but not everyone knows that.

The one asymmetry worth remembering: PostGIS is strict about SRID and permissive about validity. It will happily store a self-intersecting polygon; it will not store one whose SRID is wrong. So the checks in step 6 include validity even though the database does not — see how to fix invalid geometries.

Edge cases or notes

  • set_crs versus to_crs is the whole story for SRID errors. set_crs changes the label; to_crs changes the coordinates. Check total_bounds if unsure which you need.
  • ST_SetSRID versus ST_Transform is the same distinction in SQL, and just as easy to get backwards.
  • A NOT NULL geometry column plus a left join produces rows PostGIS cannot store. Materialise join results carefully.
  • Empty geometries pass every constraint and break every query. Filter them explicitly; notna() does not catch them.
  • GeometryCollection is what ST_Intersection returns when shapes touch at a point. Filter with ST_CollectionExtract(geom, 3) to keep only polygons.
  • Changing a column type rebuilds the table and invalidates the index. REINDEX afterwards, and expect a lock on a large table.
  • Mixed Z and 2D in the same frame fails per row, so a load can get most of the way through before stopping. Check has_z.any() and has_z.all() — a mixture is the awkward case.
  • ogr2ogr has flags for all of this (-nlt PROMOTE_TO_MULTI, -dim 2, -t_srs) and is often the faster route for a one-off load.

FAQ

What does SRID 0 mean?

Unknown. PostGIS stores it when the incoming geometry carries no spatial reference — which in Python means the GeoDataFrame's crs was None.

Should I just declare the column as bare geometry?

Only for staging tables. On a table of record it accepts mixed CRS and mixed types, and every query and join afterwards has to defend against that mixture.

How do I tell whether to use set_crs or to_crs?

Print gdf.total_bounds. Values between -180 and 180 are degrees; values in the hundreds of thousands are a projected grid in metres. Match that against what the CRS claims.

Why does the error appear thousands of rows in?

The write is batched, and the constraint is checked per row. The first 4,111 rows were fine; row 4,112 was the first multipart. Validate the whole frame first and the error arrives immediately.

Can I load a mix of Polygons and MultiPolygons?

Not into a Polygon column. Promote all to MultiPolygon and declare the column that way, or explode the multiparts into separate rows.

Does dropping Z lose anything I need?

Only if something reads it. ST_Area and ST_Length ignore Z anyway; ST_3DLength and ST_3DDistance do not. If nothing in your queries starts with ST_3D, you are not using it.

How do I fix a table that already has mixed SRIDs?

Group by ST_SRID(geom) to see the split, transform each group to the target with ST_Transform, then alter the column to the typed declaration and REINDEX.