How PostGIS Stores Geometry: SRID, EWKB and the Typed Column #

Problem statement #

Two tables look identical in QGIS. One joins in 12 milliseconds, the other takes four seconds and returns nothing.

\d parcels_a
--  geom | geometry(MultiPolygon,27700) |  not null

\d parcels_b
--  geom | geometry                     |

The second column is untyped. It accepts a MultiPolygon in EPSG:27700, a Point in EPSG:4326 and a 3D LineString in the same column, all at once — and every query against it has to cope with whatever is actually in there.

Understanding what PostGIS keeps in a geometry column, and what a column declaration adds on top, explains most of the surprises: why an SRID mismatch stops a query dead, why a MultiPolygon column quietly accepts a Polygon, why a spatial index sometimes does nothing, and why to_postgis on an untyped column is a decision rather than a default.

Quick answer #

A PostGIS geometry value is EWKB — well-known binary with an SRID attached. A geometry column optionally constrains three things about the values it accepts:

CREATE TABLE parcels (
    geom geometry(MultiPolygon, 27700) NOT NULL
    --       │        │           │
    --       │        │           └── SRID: which coordinate system
    --       │        └────────────── type: which geometry kinds are allowed
    --       └─────────────────────── the base type; ZM go on the type name
);

# what Python sends, and what the column checks
gdf.crs                      # EPSG:27700  → becomes the SRID in EWKB
gdf.geom_type.unique()       # ['MultiPolygon'] → checked against the declared type
gdf.geometry.has_z.any()     # False → checked against the declared dimension

Three properties, checked on every insert. Get them right once at CREATE TABLE and the database refuses everything that would have become a data-quality problem later.

What a geometry value actually contains #

Step-by-step solution #

The value: EWKB #

Standard WKB — the OGC binary format — describes a shape and nothing else. It has no idea which coordinate system its numbers are in. PostGIS extends it:

SELECT ST_AsEWKT(geom) FROM parcels LIMIT 1;
-- SRID=27700;MULTIPOLYGON(((325113 674881, 325160 674881, ...)))

SELECT ST_AsText(geom) FROM parcels LIMIT 1;
-- MULTIPOLYGON(((325113 674881, ...)))       ← the SRID is gone

ST_AsEWKT keeps the SRID; ST_AsText drops it. That distinction matters when you round-trip geometry through Python:

# loses the SRID — the GeoDataFrame comes back with crs=None
"SELECT ST_AsBinary(geom) AS wkb FROM parcels"

# keeps it
"SELECT ST_AsEWKB(geom) AS wkb FROM parcels"

read_postgis reads the SRID from the EWKB and sets the GeoDataFrame's crs from it. A query that constructs geometry with a plain constructor produces SRID 0, and the frame comes back with no CRS — which is the SRID 0 error waiting to happen on the next write.

The SRID: a lookup key, not a definition #

SELECT srid, auth_name, auth_srid, left(srtext, 60)
FROM spatial_ref_sys WHERE srid = 27700;
-- 27700 | EPSG | 27700 | PROJCS["OSGB 1936 / British National Grid",...

spatial_ref_sys is an ordinary table that PostGIS populates on install. The SRID stored with each geometry is a foreign key into it in spirit, though not enforced as one. Two consequences follow:

• The number is meaningless without the table. A database missing rows in spatial_ref_sys cannot transform, which is the server-side counterpart of a missing proj.db.
• SRID 0 is legal. It means "unknown", not "invalid". PostGIS stores it happily and then refuses to transform it, because there is nothing to transform from.

SELECT ST_Transform(ST_SetSRID(geom, 0), 4326) FROM parcels;
-- ERROR: Input geometry has unknown (0) SRID

The type modifier: a constraint, checked per row #

ALTER TABLE parcels ALTER COLUMN geom TYPE geometry(MultiPolygon, 27700);

This is not metadata — it is enforced. An insert of the wrong SRID or an incompatible type raises, which is exactly the behaviour that keeps a table trustworthy.

The type check has one asymmetry worth knowing, because it is not what people expect. A MultiPolygon column accepts a single-part Polygon and promotes it; a Polygon column rejects a MultiPolygon:

INSERT INTO t_multi  VALUES (ST_SetSRID(ST_MakeEnvelope(0,0,1,1), 27700));          -- ok, stored as MULTIPOLYGON
INSERT INTO t_single VALUES (ST_Multi(ST_SetSRID(ST_MakeEnvelope(0,0,1,1), 27700)));
-- ERROR: Geometry type (MultiPolygon) does not match column type (Polygon)

So declaring MultiPolygon is the forgiving choice — it takes both, and normalises. The type name also carries dimensionality:

geometry(Geometry, 27700) is the useful middle ground people forget: it accepts mixed shape types while still guaranteeing one coordinate system — which is the constraint that actually prevents silent join failures.

The index: bounding boxes in an R-tree #

CREATE INDEX parcels_geom_idx ON parcels USING GIST (geom);

A GIST index does not store geometry. It stores each row's bounding box, in a tree that supports "which boxes overlap this box" efficiently. That is why the index can accelerate ST_Intersects and ST_DWithin but not ST_Distance(...) < 500 — the first two can be expressed as a box question, the third cannot.

SELECT ST_Extent(geom) FROM parcels;
-- BOX(320111 670222, 330544 680915)         ← the same idea, one level up

Two things silently disable it:

-- a function around the indexed column: the index is on geom, not on ST_Buffer(geom)
WHERE ST_Intersects(ST_Buffer(geom, 10), :shape)

-- an SRID mismatch: forces a per-row transform, and the transformed value is not indexed
WHERE ST_Intersects(geom, ST_Transform(:shape_4326, 27700))   -- fine, :shape transformed once
WHERE ST_Intersects(ST_Transform(geom, 4326), :shape_4326)    -- not fine, geom transformed per row

Transform the constant, never the column. See spatial indexes explained.

geography is a different storage decision #

geom  geometry(Point, 4326)     -- planar maths on lon/lat degrees
geog  geography(Point, 4326)    -- spheroidal maths, metres

geography stores the same coordinates and computes differently: ST_Distance returns metres on the spheroid rather than degrees on a plane. It is right for global data where no single projection works, and slower for everything else. Most national-grid work should use geometry in a projected SRID, where planar maths is both correct and fast.

Code examples #

Example 1: inspecting what is really in a column #

-- the declaration
SELECT f_geometry_column, type, srid, coord_dimension
FROM geometry_columns
WHERE f_table_name = 'parcels';
-- geom | MULTIPOLYGON | 27700 | 2

-- the reality, which can differ on an untyped column
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;

On a properly declared column the second query returns exactly one row. More than one row means the column is untyped and the table has been accumulating a mixture — usually via to_postgis(..., if_exists="replace"), which creates an untyped column every time. See how to write a GeoDataFrame to PostGIS.

Example 2: tightening a column that was created loosely #

BEGIN;

-- 1. see what is actually there
SELECT DISTINCT ST_SRID(geom), GeometryType(geom) FROM parcels;

-- 2. make every row conform
UPDATE parcels
SET geom = ST_Multi(ST_Force2D(ST_SetSRID(geom, 27700)))
WHERE ST_SRID(geom) = 0;            -- only the unlabelled rows

UPDATE parcels
SET geom = ST_Multi(ST_Force2D(ST_Transform(geom, 27700)))
WHERE ST_SRID(geom) NOT IN (0, 27700);   -- genuinely wrong CRS

-- 3. now the constraint can be added
ALTER TABLE parcels
  ALTER COLUMN geom TYPE geometry(MultiPolygon, 27700);

ALTER TABLE parcels ALTER COLUMN geom SET NOT NULL;
CREATE INDEX IF NOT EXISTS parcels_geom_idx ON parcels USING GIST (geom);
ANALYZE parcels;

COMMIT;

Steps 1 and 2 are in the opposite order from the instinct. Adding the constraint first fails on the first bad row and tells you nothing about the rest; conforming first means the ALTER either succeeds or reveals a case you have not handled.

Note ST_SetSRID for the unknown rows and ST_Transform for the wrong ones — relabelling versus moving, one layer down from set_crs versus to_crs.

Example 3: the round trip, with the SRID intact #

import geopandas as gpd
from shapely import from_wkb

# read: ST_AsEWKB keeps the SRID, so the CRS survives
rows = pd.read_sql(
    "SELECT parcel_id, ST_AsEWKB(geom) AS geom FROM parcels", engine
)
gdf = gpd.GeoDataFrame(
    rows.drop(columns="geom"),
    geometry=from_wkb(rows["geom"]),
    crs=27700,                       # from_wkb ignores the EWKB SRID — set it explicitly
)

# write: GeoAlchemy2 takes the SRID from gdf.crs
gdf.to_postgis("parcels", conn, if_exists="append", index=False)

shapely.from_wkb parses EWKB but discards the SRID, because a Shapely geometry has no CRS concept — the CRS lives on the GeoDataFrame, not the shape. Setting crs= explicitly is not optional here; without it the frame comes back CRS-less and the next write produces SRID 0.

Explanation #

The design worth internalising is that PostGIS separates the value from the constraint, and both are optional.

A geometry value always carries an SRID slot — that is what EWKB adds over WKB — but the value in that slot can be 0, and PostGIS will store it without complaint. So a table can hold geometry with no meaningful spatial reference, indefinitely, with no error. The database is not being permissive by accident: geometry is a general-purpose type, and there are legitimate uses for unreferenced coordinates.

The type modifier is what turns permission into a guarantee. geometry(MultiPolygon, 27700) is a check constraint in all but syntax, and its value is entirely preventative: it converts a class of future confusion into an immediate, loud, specific error at insert time. That trade — a rejected write today against a mystery tomorrow — is almost always worth making.

The consequences of skipping it are not evenly distributed. A mixed type column is annoying: ST_Area on a Point returns 0, ST_Union produces a GeometryCollection, and downstream code needs branches. A mixed SRID column stops queries outright:

ERROR: ST_Intersects: Operation on mixed SRID geometries (MultiPolygon, 27700) != (Polygon, 4326)

That is PostGIS being better than the Python side, and worth appreciating. GeoPandas' sjoin across mismatched CRS emits a warning and returns nothing useful, which looks exactly like "no matches"; PostGIS refuses to guess. The cost is that a table with mixed SRIDs has queries that fail for some rows and not others depending on which the planner reaches — which is why the SRID belongs in the column declaration rather than in each row.

The index is the third layer and the one with the clearest cost model: without it, every spatial predicate is a sequential scan; with it, the bounding-box filter discards almost everything before the exact test runs. to_postgis never creates one, which is why a freshly loaded table so often feels slower than the GeoPackage it replaced.

Edge cases or notes #

• geometry_columns is a view, not a table, in modern PostGIS — it reads the real type modifiers, so it cannot drift from reality.
• ST_SRID returns 0, never NULL, for unreferenced geometry. Test = 0, not IS NULL.
• The type modifier does not validate the shape. A self-intersecting polygon inserts fine into geometry(Polygon, 27700); validity is a separate concern.
• ST_Multi is idempotent — safe to apply to geometry that is already multi-part.
• Changing a column's type rewrites the table and takes an ACCESS EXCLUSIVE lock. On a large table, plan for it.
• geography only supports SRID 4326 in practice, and a smaller set of functions than geometry.
• ST_Force2D drops Z silently. If the Z means something, declare PolygonZ instead.
• An SRID present in your geometry but absent from spatial_ref_sys stores fine and fails on transform. Custom SRIDs need inserting into that table.

Internal links #

• PostGIS explained: when a spatial database beats a folder of files — whether to be here at all
• How to write a GeoDataFrame to PostGIS — creating the typed column deliberately
• PostGIS write fails on SRID or geometry type — the errors these constraints produce
• How to run spatial SQL queries from Python with PostGIS — where the index earns its keep
• How to connect GeoPandas to PostGIS — getting there in the first place
• WKT, WKB and GeoJSON: how geometry is actually stored — the formats EWKB extends
• Spatial indexes explained: R-trees and why spatial joins are fast — what GIST implements
• EPSG codes explained — what the number in the declaration means

FAQ #

What is the difference between WKB and EWKB? #

EWKB is PostGIS's extension of WKB that carries an SRID (and Z/M flags) alongside the coordinates. ST_AsBinary produces WKB and loses the SRID; ST_AsEWKB keeps it.

Does declaring the type slow down inserts? #

Marginally — it is a per-row check on values already in memory. The cost is invisible next to the I/O, and far smaller than the cost of finding a mixed-SRID table six months later.

Does a MultiPolygon column reject a single Polygon? #

No — it accepts it and promotes it to a one-part MultiPolygon. The rejection only runs the other way: a MultiPolygon into a geometry(Polygon, …) column fails with Geometry type (MultiPolygon) does not match column type (Polygon).

Should I ever use a bare geometry column? #

For staging tables that receive unknown input, yes. For a table of record, no — geometry(Geometry, 27700) gives you mixed types with a guaranteed CRS, which is almost always what "I need flexibility" actually means.

What happens if I compare geometries with different SRIDs? #

PostGIS raises Operation on mixed SRID geometries and the query fails. That is deliberate and helpful — it refuses to guess. Declare the SRID on the column so the mismatch cannot get into the table in the first place.

Is geography better than geometry? #

Only for global data where no single projection works. It is slower and supports fewer functions. For national or regional data in a projected CRS, geometry is both faster and more accurate.

Why is my spatial query slow right after loading a table? #

to_postgis does not create a GIST index, and a freshly loaded table has no planner statistics. Create the index and run ANALYZE.

Can one table have two geometry columns? #

Yes — for example a parcel outline and its centroid. Each gets its own declaration and its own index, and queries must name the one they mean.