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

Problem statement

The load worked in testing. In production it stops with one of these:

ValueError: The CRS of the target table (EPSG:27700) differs from the CRS of
    current GeoDataFrame (EPSG:0).

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

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

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

Every one of these is a check refusing data that does not match the column you declared — the first from GeoPandas before anything is sent, the rest from PostGIS. 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.

Every error on this page comes from a real layer: HM Land Registry's Rutland parcels delivered as a shapefile without its .prj, England's 63 unitary authorities as the ONS serves them, US Geological Survey lake and pond outlines from the National Hydrography Dataset, and the Food Standards Agency's register of Rutland food businesses. They are written to PostGIS 3.4 through GeoPandas.

Quick answer

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

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

for name, layer in layers.items():
    print(f"{name:20s}", describe_geometry(layer))

parcels (no .prj)    {'crs': None, 'types': ['Polygon'], 'has_z': 0, 'nulls': 0, 'empties': 0, 'invalid': 0}
authorities          {'crs': 'EPSG:4326', 'types': ['MultiPolygon', 'Polygon'], 'has_z': 0, 'nulls': 0, 'empties': 0, 'invalid': 0}
lakes                {'crs': 'EPSG:4979', 'types': ['Polygon'], 'has_z': 266, 'nulls': 0, 'empties': 0, 'invalid': 0}
food premises        {'crs': 'EPSG:27700', 'types': ['Point'], 'has_z': 0, 'nulls': 124, 'empties': 0, 'invalid': 0}

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

import shapely
from shapely.geometry import MultiPolygon

# the whole fix, in order, for the lakes going into a geometry(MultiPolygon, 32618) column
gdf = lakes
gdf = gdf[gdf.geometry.notna() & ~gdf.geometry.is_empty]              # nullity
gdf = gdf.set_geometry(shapely.force_2d(gdf.geometry))                # dimensionality
gdf = gdf.set_geometry([g if g.geom_type.startswith("Multi") else MultiPolygon([g])
                        for g in gdf.geometry], crs=gdf.crs)          # type
gdf = gdf.to_crs(32618)                                               # SRID
print(write(gdf, "lakes"), sql("SELECT count(*) FROM lakes")[0][0], "rows")

ok 266 rows

The four gates, and what each rejects

Missing CRS, reprojection and type mismatches

1. CRS ... differs ... (EPSG:0) — the frame has no CRS

The parcels arrived as .shp, .shx and .dbf with no .prj, so GeoPandas reads them with crs=None. Appending them to a geometry(Polygon, 27700) table stops before a row is sent:

print(parcels_shp.crs)
print(write(parcels_shp, "parcels"))

None
ValueError: The CRS of the target table (EPSG:27700) differs from the CRS of current GeoDataFrame (EPSG:0).

That GeoPandas check is a safety net worth knowing about, because PostGIS itself does not provide one. Send an unlabelled geometry straight to the database and PostGIS 3.4 does not complain — it stamps the column's SRID on it:

sql("DROP TABLE IF EXISTS srid_demo; CREATE TABLE srid_demo (geom geometry(Point, 27700))")
print(sql("INSERT INTO srid_demo VALUES (ST_GeomFromText('POINT(-0.7263 52.6698)'))"))   # Oakham, in degrees
print(sql("SELECT ST_SRID(geom), ST_AsText(ST_Transform(geom, 4326)) FROM srid_demo"))

ok
[(27700, 'POINT(-7.557223917266134 49.767278243214534)')]

Oakham's longitude and latitude, read as British National Grid metres, land a few metres from the grid's false origin in the Atlantic south-west of the Isles of Scilly. No error was raised. Any loader that writes WKT or WKB without an SRID — hand-written SQL, some ETL tools — gets this behaviour.

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

print(parcels_shp.total_bounds.round())

[479001. 292381. 506878. 319201.]

Six-figure eastings and northings inside Great Britain: these coordinates are British National Grid that lost its label.

# The coordinates ARE in 27700, they were just unlabelled.
fixed = parcels_shp.set_crs(27700)                 # relabel, do not move
print(write(fixed, "parcels"), sql("SELECT count(*) FROM parcels")[0][0], "rows")

# The coordinates are in something else — label the truth, then transform:
# gdf = gdf.set_crs(4326).to_crs(27700)

ok 21253 rows

Getting this backwards puts your data in the wrong place without any error, which is much worse than the write failing. Degrees are between -180 and 180; if your bounds look like that and you were about to call set_crs(27700), stop.

2. CRS ... differs ... (EPSG:4326) — reproject

The frame is correctly labelled and in the wrong CRS. The ONS serves its boundaries as GeoJSON in longitude and latitude:

print(write(authorities, "authorities"))

ValueError: The CRS of the target table (EPSG:27700) differs from the CRS of current GeoDataFrame (EPSG:4326).

The USGS lakes are subtler. Their GeoJSON carries a Z value on every vertex, so GeoPandas labels them EPSG:4979 — WGS 84 in three dimensions — not 4326, and a check that only looks for 4326 misses them:

print(lakes.crs, lakes.crs.to_epsg(), lakes.crs.name)
print(write(lakes, "lakes_poly"))

EPSG:4979 4979 WGS 84
ValueError: The CRS of the target table (EPSG:32618) differs from the CRS of current GeoDataFrame (EPSG:4979).

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 boundary dataset has multiparts — coastal authorities with islands, estuaries that split a district:

authorities_bng = conform(authorities, 27700)
print(authorities_bng.geom_type.value_counts().to_dict())
print(write(authorities_bng, "authorities"))
print(sql("SELECT count(*) FROM authorities")[0][0], "rows stored")

{'Polygon': 37, 'MultiPolygon': 26}
InvalidParameterValue: Geometry type (MultiPolygon) does not match column type (Polygon)
0 rows stored

The 37 single-part authorities were fine, and none of them was stored: the write is one transaction. Two honest fixes:

from shapely.geometry import MultiPolygon

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

# B — split multiparts into rows, keep the column as Polygon
exploded = authorities_bng.explode(index_parts=False).reset_index(drop=True)
print(len(authorities_bng), "authorities;", len(exploded), "rows after explode;", write(exploded, "authorities"))

63 authorities; 381 rows after explode; ok

Verified behaviour worth knowing: PostGIS promotes automatically in the other direction. A single-part Polygon inserted into a geometry(MultiPolygon, …) column is accepted and stored as a one-part MultiPolygon:

sql("DROP TABLE IF EXISTS multi_demo; CREATE TABLE multi_demo (geom geometry(MultiPolygon, 27700))")
print(sql("INSERT INTO multi_demo SELECT geometry FROM authorities LIMIT 1"))
print(sql("SELECT GeometryType(geom) FROM multi_demo"))

ok
[('MULTIPOLYGON',)]

So option A needs only the column declaration; the Python loop just makes the frame match what will be stored, which keeps geom_type assertions honest on both sides.

Option B changes the row count — 63 authorities became many more rows — 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

The National Hydrography Dataset stores its lakes and ponds with a Z coordinate. On this download, every Z is zero:

lakes_utm = conform(lakes, 32618)
z = shapely.get_coordinates(lakes_utm.geometry.values, include_z=True)[:, 2]
print(f"{lakes_utm.geometry.has_z.sum()} of {len(lakes_utm)} geometries have Z; distinct Z values: {sorted(set(z.tolist()))}")
print(write(lakes_utm, "lakes_poly"))

266 of 266 geometries have Z; distinct Z values: [0.0]
InvalidParameterValue: Geometry has Z dimension but column does not

# drop Z, since nothing here reads the elevation
lakes_2d = lakes_utm.set_geometry(shapely.force_2d(lakes_utm.geometry))
print(write(lakes_2d, "lakes_poly"))

ok

-- or keep it, and say so in the column
ALTER TABLE lakes_poly
  ALTER COLUMN geometry TYPE geometry(PolygonZ, 32618);

Keep Z only if something reads it. A Z of 0.0 on every vertex implies data that is not there, and it costs space: the same 266 lakes take half as much memory again with it.

sql("DROP TABLE IF EXISTS lakes_z; CREATE TABLE lakes_z (\"PERMANENT_IDENTIFIER\" text, geometry geometry(PolygonZ, 32618))")
write(lakes_utm[["PERMANENT_IDENTIFIER", "geometry"]], "lakes_z")
print(sql("SELECT (SELECT sum(ST_MemSize(geometry)) FROM lakes_poly) AS flat, (SELECT sum(ST_MemSize(geometry)) FROM lakes_z) AS with_z"))
print(sql("""SELECT ST_Equals(z.geometry, f.geometry), z.geometry = f.geometry
             FROM lakes_z z JOIN lakes_poly f USING ("PERMANENT_IDENTIFIER") LIMIT 1"""))

[(1739616, 2606192)]
[(True, False)]

The comparison shows the other cost. ST_Equals compares in two dimensions and calls the flat and 3D lake the same shape; the = operator, which PostGIS uses for DISTINCT, GROUP BY and joins on the geometry column, says they differ.

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

The food hygiene register lists every business the council inspects, and not all of them have coordinates:

print(premises.geometry.isna().sum(), "of", len(premises), "premises have no location")
print(write(premises, "food_premises"))

124 of 462 premises have no location
NotNullViolation: null value in column "geometry" of relation "food_premises" violates not-null constraint

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:

print(sql("INSERT INTO parcels VALUES (0, 'POLYGON EMPTY'::geometry)"))
print(sql("SELECT count(*) FROM parcels WHERE ST_IsEmpty(geometry)"))
sql("DELETE FROM parcels WHERE ST_IsEmpty(geometry)")

ok
[(1,)]

before = len(premises)
located = premises[premises.geometry.notna() & ~premises.geometry.is_empty]
print(f"dropped {before - len(located)} rows with null or empty geometry;", write(located, "food_premises"))

dropped 124 rows with null or empty geometry; ok

Log the count. Silently dropping rows in a load is how row counts stop reconciling — and here the dropped rows are a quarter of the register, which somebody downstream will want to know.

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

The database stops at the first failure and tells you nothing about the rest of the frame — and the write is one transaction, so after a failure nothing at all has been stored. 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))

for layer, srid, kind in [(lakes, 32618, "MultiPolygon"), (authorities, 27700, "Polygon")]:
    try:
        check_writable(layer, srid=srid, geom_type=kind)
    except ValueError as err:
        print(err)

not writable:
  - CRS is 4979, column expects 32618
  - unexpected types: Polygon (266)
  - 266 geometries have Z
not writable:
  - CRS is 4326, column expects 27700
  - unexpected types: MultiPolygon (26)

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

A conforming loader and finding bad rows

The data used on this page

Four real layers and a throwaway PostGIS 3.4 in Docker on port 55433. HM Land Registry's INSPIRE Index Polygons for Rutland (Open Government Licence, © Crown copyright and database rights) are written to a shapefile and its .prj deleted, the way a partial delivery arrives. England's 63 unitary authorities come from the ONS Local Authority Districts (December 2024) boundaries; the lakes and ponds from the USGS National Hydrography Dataset map service for a patch of the Catskills in New York; the food businesses from the Food Standards Agency's open data for Rutland (authority 879). The register changes as the council updates it, so counts drift a little between runs. write() appends a frame to a table and returns the error message instead of raising, and sql() does the same for a statement.

import io
import json
import os
import time
import zipfile
from pathlib import Path

import geopandas as gpd
import pandas as pd
import requests
from sqlalchemy import create_engine
from sqlalchemy.exc import DBAPIError

Path("data").mkdir(exist_ok=True)
if not Path("data/parcels.shp").exists():
    reply = requests.Session().get(
        "https://use-land-property-data.service.gov.uk/datasets/inspire/download/Rutland.zip", timeout=300)
    reply.raise_for_status()
    zipfile.ZipFile(io.BytesIO(reply.content)).extract("Land_Registry_Cadastral_Parcels.gml", "data")
    gml = gpd.read_file("data/Land_Registry_Cadastral_Parcels.gml", columns=["INSPIREID"])
    gml.to_file("data/parcels.shp")
    os.remove("data/parcels.prj")                      # the supplier forgot it
if not Path("data/authorities.geojson").exists():
    gpd.read_file(
        "https://services1.arcgis.com/ESMARspQHYMw9BZ9/arcgis/rest/services/"
        "Local_Authority_Districts_December_2024_Boundaries_UK_BGC/FeatureServer/0/query"
        "?where=LAD24CD%20LIKE%20%27E06%25%27&outFields=LAD24CD,LAD24NM&f=geojson"
    ).to_file("data/authorities.geojson", driver="GeoJSON")
if not Path("data/lakes.geojson").exists():
    Path("data/lakes.geojson").write_bytes(requests.get(
        "https://hydro.nationalmap.gov/arcgis/rest/services/nhd/MapServer/12/query",
        params={"geometry": "-74.40,41.90,-74.10,42.10", "geometryType": "esriGeometryEnvelope",
                "inSR": 4326, "spatialRel": "esriSpatialRelIntersects", "returnZ": "true",
                "outFields": "permanent_identifier,gnis_name,ftype", "f": "geojson"},
        timeout=120).content)
if not Path("data/premises.json").exists():
    Path("data/premises.json").write_bytes(requests.get(
        # the FSA register file for Rutland as saved on 9 October 2026 (updated daily); live:
        # ratings.food.gov.uk/api/open-data-files/FHRS879en-GB.json
        "https://data.spatialworkflow.io/fhrs-879-2026-10-09.json", timeout=120).content)

found = json.loads(Path("data/premises.json").read_text())["FHRSEstablishment"]["EstablishmentCollection"]
records = pd.json_normalize(found if isinstance(found, list) else found["EstablishmentDetail"])
xy = records[["Geocode.Longitude", "Geocode.Latitude"]].apply(pd.to_numeric, errors="coerce")
located = xy.notna().all(axis=1)
geometry = gpd.GeoSeries([None] * len(records), crs=4326)
geometry[located] = gpd.points_from_xy(xy.loc[located].iloc[:, 0], xy.loc[located].iloc[:, 1])
premises = gpd.GeoDataFrame(records[["FHRSID", "BusinessName", "BusinessType"]], geometry=geometry).to_crs(27700)

parcels_shp = gpd.read_file("data/parcels.shp")
authorities = gpd.read_file("data/authorities.geojson")
lakes = gpd.read_file("data/lakes.geojson")[["PERMANENT_IDENTIFIER", "geometry"]]
layers = {"parcels (no .prj)": parcels_shp, "authorities": authorities, "lakes": lakes, "food premises": premises}

os.environ["PGPASSWORD"] = "guide"
if os.system("docker inspect guide-postgis >/dev/null 2>&1") != 0:
    os.system("docker run -d --name guide-postgis -e POSTGRES_USER=user -e POSTGRES_PASSWORD=guide "
              "-e POSTGRES_DB=gis -p 127.0.0.1:55433:5432 postgis/postgis:16-3.4 >/dev/null")
    time.sleep(10)
engine = create_engine("postgresql+psycopg://user@localhost:55433/gis")


def sql(statement):
    try:
        with engine.begin() as conn:
            result = conn.exec_driver_sql(statement)
            return result.all() if result.returns_rows else "ok"
    except DBAPIError as err:
        return "ERROR: " + str(err.orig).splitlines()[0]


def write(gdf, table):
    try:
        gdf.to_postgis(table, engine, if_exists="append", index=False)
        return "ok"
    except Exception as err:
        return f"{type(err).__name__}: {str(getattr(err, 'orig', err)).splitlines()[0]}"


# the typed tables of record the layers are loaded into
sql("""
DROP TABLE IF EXISTS parcels, authorities, lakes, lakes_poly, food_premises;
CREATE TABLE parcels       ("INSPIREID" bigint, geometry geometry(Polygon, 27700) NOT NULL);
CREATE TABLE authorities   ("LAD24CD" text, "LAD24NM" text, geometry geometry(Polygon, 27700) NOT NULL);
CREATE TABLE lakes         ("PERMANENT_IDENTIFIER" text, geometry geometry(MultiPolygon, 32618) NOT NULL);
CREATE TABLE lakes_poly    ("PERMANENT_IDENTIFIER" text, geometry geometry(Polygon, 32618) NOT NULL);
CREATE TABLE food_premises ("FHRSID" bigint, "BusinessName" text, "BusinessType" text,
                            geometry geometry(Point, 27700) NOT NULL);
""")
print({name: len(layer) for name, layer in layers.items()})

{'parcels (no .prj)': 21253, 'authorities': 63, 'lakes': 266, 'food premises': 462}

Example 1: the conforming loader

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

def conform_for_postgis(gdf, *, srid, 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 = gdf.set_geometry(
                [g if g.geom_type.startswith("Multi") else MultiPolygon([g]) for g in gdf.geometry], crs=gdf.crs
            )

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

    report["out"] = len(gdf)
    return gdf, report

sql("TRUNCATE lakes")
conformed, report = conform_for_postgis(lakes, srid=32618)
print(report)
print(write(conformed, "lakes"))

{'in': 266, 'dropped_empty': 0, 'flattened_z': 266, 'reprojected_from': 4979, 'promoted_to_multi': 266, 'out': 266}
ok

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 problem you cannot find, ask the frame:

odd = authorities_bng[authorities_bng.geom_type != "Polygon"]
print(len(odd), "multipart authorities, e.g.", odd["LAD24NM"].head(4).tolist())

missing = premises[premises.geometry.isna()]
print(len(missing), "premises without a location, by type:")
print(missing["BusinessType"].value_counts().head(4).to_string())

26 multipart authorities, e.g. ['Hartlepool', 'Middlesbrough', 'Redcar and Cleveland', 'Stockton-on-Tees']
124 premises without a location, by type:
BusinessType
Other catering premises    53
Mobile caterer             18
Retailers - other          17
Restaurant/Cafe/Canteen    11

The premises without a location are not a data error to repair: mobile caterers, for one, have no fixed site to put on a map. They belong in a table with a nullable geometry, or in a separate list, not dropped without a count.

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

print(sql("""
    SELECT ST_SRID(geometry) AS srid, GeometryType(geometry) AS type, ST_NDims(geometry) AS dims, count(*)
    FROM authorities GROUP BY 1, 2, 3 ORDER BY 4 DESC
"""))

[(27700, 'POLYGON', 2, 381)]

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

Joins produce geometry types of their own. Intersect neighbouring authorities and the result is not a polygon at all:

print(sql("""
    SELECT GeometryType(ST_Intersection(a.geometry, b.geometry)), count(*)
    FROM authorities a JOIN authorities b
      ON a."LAD24CD" < b."LAD24CD" AND ST_Intersects(a.geometry, b.geometry)
    GROUP BY 1
"""))

[('LINESTRING', 24), ('MULTILINESTRING', 97)]

Example 3: fixing a column that is already wrong

Create a table from the unlabelled parcels with if_exists="replace", and GeoPandas declares the column with SRID 0 rather than refusing:

import warnings

with warnings.catch_warnings():
    warnings.simplefilter("ignore")
    parcels_shp.to_postgis("parcels_srid0", engine, if_exists="replace", index=False)
sql("CREATE INDEX parcels_srid0_geom ON parcels_srid0 USING GIST (geometry)")
print(sql("SELECT type, srid FROM geometry_columns WHERE f_table_name = 'parcels_srid0'"))

# relabel in place (coordinates unchanged — only correct because they really are 27700)
print(sql("""
    ALTER TABLE parcels_srid0
      ALTER COLUMN geometry TYPE geometry(Polygon, 27700)
      USING ST_SetSRID(geometry, 27700)
"""))
print(sql("SELECT type, srid FROM geometry_columns WHERE f_table_name = 'parcels_srid0'"))
print(sql("SELECT indexrelid::regclass, indisvalid FROM pg_index WHERE indrelid = 'parcels_srid0'::regclass"))

[('POLYGON', 0)]
ok
[('POLYGON', 27700)]
[('idx_parcels_srid0_geometry', True), ('parcels_srid0_geom', True)]

-- genuinely reproject (coordinates move) — for a column whose data really is 4326
ALTER TABLE authorities_raw
  ALTER COLUMN geometry TYPE geometry(MultiPolygon, 27700)
  USING ST_Transform(ST_SetSRID(geometry, 4326), 27700);

ST_SetSRID relabels. ST_Transform moves. The same trap as set_crs versus to_crs, one layer down. ALTER COLUMN ... TYPE rewrites the table and rebuilds its indexes as part of the same statement, so the GIST index is valid afterwards without a REINDEX — but it holds an exclusive lock for the whole rewrite, which on a large table is a maintenance-window job.

What a typed column checks

A PostGIS geometry column declared as geometry(MultiPolygon, 27700) checks every insert against three things: the geometry type, the coordinate dimension, and the SRID. Two of them are strict. The SRID check has one hole: a geometry whose SRID is 0 (unknown) is not rejected but given the column's SRID, as the Oakham point showed.

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. Many "why is this join returning nothing" questions in PostGIS trace back to a table where the SRID varies by row.

A geometry is stored with its SRID alongside it. When GeoPandas writes, that SRID comes from the GeoDataFrame's crs; with crs=None GeoPandas either refuses to append (the target table has a known SRID) or creates a new column with SRID 0 and a warning.

The dimension check catches a category of problem people rarely anticipate. The USGS lakes carry Z on every vertex because the source database is 3D, and every one of those values is zero; nothing on a map reveals it. It costs memory, makes the = operator disagree with ST_Equals, and it is not something ST_Area sees at all, since ST_Area ignores Z.

The one asymmetry worth remembering: PostGIS is strict about type and dimension, and permissive about validity. It will happily store a self-intersecting polygon — see how to fix invalid geometries — so the checks in step 6 are worth extending with validity even though the database does not ask.

Joins, SQL inserts and ogr2ogr

• SQL inserts without an SRID are not rejected. PostGIS 3.4 gives them the column's SRID, as the Oakham point showed; write ST_GeomFromText(wkt, srid) with the SRID the coordinates are actually in.
• A NOT NULL geometry column plus a left join produces rows PostGIS cannot store. Materialise join results carefully.
• ogr2ogr has flags for all of this (-nlt PROMOTE_TO_MULTI, -dim XY, -t_srs, -a_srs) and is often the faster route for a one-off load.

Internal links

• How to write a GeoDataFrame to PostGIS — the load procedure that avoids these errors
• How to connect GeoPandas to PostGIS — before any of this can fail
• How to run spatial SQL queries from Python with PostGIS — where an inconsistent SRID silently kills performance
• PostGIS explained: when a spatial database beats files — why the strictness is a feature
• Cannot transform naive geometries to CRS — the GeoPandas-side version of SRID 0
• How to fix CRS mismatch in GeoPandas — the same mismatch before the database sees it
• Mixed geometry types error when saving a GeoDataFrame — the file-format equivalent of the type constraint
• How to split multipart geometries into single parts — option B in step 3

FAQ

What does SRID 0 mean?

Unknown. GeoPandas writes it when the GeoDataFrame's crs is None and it is creating the table; appending such a frame to a table with a real SRID is refused. PostGIS 3.4 does not reject an SRID-0 geometry sent to a typed column — it gives it the column's SRID — so SRID 0 in raw SQL is how coordinates end up silently mislabelled.

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 one bad row fail the whole write?

to_postgis sends the rows in one transaction, and the constraint is checked per row. The 37 single-part unitary authorities were fine, but the first multipart one failed and none of the 63 was stored. Validate the whole frame first and every problem is listed before anything is sent.

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. The ALTER rebuilds the indexes itself.