Translating PostGIS Functions to DuckDB Spatial

Most ST_ calls port between PostGIS and DuckDB unchanged, but the ones that do not — SRID setters, the aggregate ST_Union, and anything that leaned on the ellipsoidal geography type — fail quietly rather than loudly, returning plausible wrong numbers instead of an error. This reference sits under the PostGIS to DuckDB Spatial migration guide and gives a function-by-function translation table plus the handful of semantic gaps that turn a mechanical find-and-replace into a subtle correctness bug. The organizing principle is simple: DuckDB shares PostGIS’s function names far more than it shares PostGIS’s assumptions about stored SRIDs and geodesic math.

Root-Cause Analysis of Translation Failures #

A ported query breaks along four axes, and each maps to a different column in the table below:

• No stored SRID. PostGIS functions that read or write a per-geometry SRID — ST_SRID, ST_SetSRID, and the single-argument ST_Transform(geom, srid) — have no faithful DuckDB form, because DuckDB’s GEOMETRY carries no SRID at all. The coordinate system lives outside the value.
• Unit assumptions. ST_Distance, ST_DWithin, ST_Buffer, and ST_Area all return results in the coordinate system’s own units. A PostGIS query that ran against a geography column returned metres; the same call on migrated planar geometry returns degrees, off by a factor of roughly 111,000 near the equator.
• Aggregate versus scalar overloads. PostGIS overloads ST_Union as both a two-argument scalar and a one-argument aggregate. DuckDB splits them: the scalar stays ST_Union, but the aggregate is ST_Union_Agg. A blind port silently loses the dissolve.
• Missing capability. The geography type, the topology extension, and raster functions have no DuckDB counterpart, so their functions need a projected-CRS workaround or must stay in PostGIS.

Deterministic Configuration #

The translation examples below need only the extension loaded and a metric CRS discipline; nothing here depends on threading or memory tuning.

INSTALL spatial;   -- one-time; downloads the spatial extension to the local cache
LOAD spatial;      -- per-session; ST_ functions and RTREE indexes resolve only after this

-- Quiet the progress bar so EXPLAIN ANALYZE timings below are not skewed by rendering.
SET enable_progress_bar = false;

Because DuckDB stores no SRID, adopt one convention up front: keep every table in a single, documented CRS, and reproject at the boundary with ST_Transform rather than assuming a value’s units. The reprojection rules are covered in the CRS mapping and transformations reference and the parent migration guide.

The Translation Table #

Reading order for each row: the PostGIS call, the DuckDB call you replace it with, and the semantic gap that a mechanical rename would miss. a and b are geometry columns, d a distance in CRS units.

The rows worth memorizing are the three that change shape rather than just names: ST_Transform gains a mandatory source CRS, ST_Union splits into scalar and ST_Union_Agg, and the SRID accessors vanish entirely.

Functions With No Direct Equivalent #

Four families of PostGIS functions have no DuckDB translation and need an explicit workaround rather than a rename.

SRID accessors (ST_SRID, ST_SetSRID). There is nothing to read or set, because the SRID is not part of a DuckDB geometry. The workaround is structural: keep a documented table-level CRS and carry it as an integer column through any format that lacks CRS metadata. When you need a different CRS, transform explicitly.

Geodesic operations on geography. PostGIS computes ST_Distance, ST_Area, and ST_Length on the ellipsoid when the column is geography. DuckDB has only planar GEOMETRY. For distance, ST_Distance_Sphere gives a spherical approximation directly; for area and length, project into an equal-area or metric CRS first and measure there:

-- No geography area in DuckDB. Project to an equal-area CRS, then measure in m².
SELECT region_id,
       ST_Area(ST_Transform(geom, 'EPSG:4326', 'EPSG:6933')) AS area_m2
FROM regions;

Topology and raster functions. Anything from postgis_topology (ST_Node, TopoGeo_*) or the raster extension (ST_MapAlgebra, ST_Clip) has no home in DuckDB. Precompute topology upstream, or keep those steps in PostGIS and migrate only the results — the trade-offs are laid out in the parent migration guide.

Aggregate ST_Union at scale. Even after renaming to ST_Union_Agg, a large dissolve is memory-heavy; reduce precision first and consider ST_Collect when topology resolution is not required, following the aggregation patterns in vectorized aggregations.

Optimized Execution Pattern: Before and After #

The most error-prone real-world port is a proximity query that leaned on geography distances and a single-argument ST_Transform. Here is the PostGIS original:

-- PostGIS: geography distance in metres, SRID read from the stored geometry.
SELECT s.id, c.name
FROM stops s
JOIN cities c
  ON ST_DWithin(s.geog, c.geog, 500)          -- 500 metres, ellipsoidal
WHERE c.region = 'north';

The faithful DuckDB rewrite makes three changes explicit: project both inputs into a metric CRS so 500 means metres, add the && bounding-box pre-filter so the R-tree drives the join, and keep the exact ST_DWithin on the survivors.

-- DuckDB: project once to a metric CRS, add the && pre-filter, then exact ST_DWithin.
WITH s AS (SELECT id, ST_Transform(geog, 'EPSG:4326', 'EPSG:3857') AS g FROM stops),
     c AS (SELECT name, region, ST_Transform(geog, 'EPSG:4326', 'EPSG:3857') AS g FROM cities)
SELECT s.id, c.name
FROM s
JOIN c
  ON c.g && s.g                                -- R-tree bounding-box pre-filter
WHERE c.region = 'north'
  AND ST_DWithin(s.g, c.g, 500);               -- 500 metres, now planar in EPSG:3857

The annotated diff: s.geog/c.geog became transformed planar columns, the implicit ellipsoidal distance became an explicit metric-CRS distance, and the bare join predicate gained an index-servable && stage. The &&-then-exact rewrite is the same two-stage shape analyzed in spatial joins and proximity filters; without the explicit &&, DuckDB has no way to reach the R-tree that a migrated GiST index became, as detailed in migrating GiST indexes to R-tree.

Diagnostic Queries & Plan Validation #

Confirm a translated query kept its index path with EXPLAIN ANALYZE. The signature of a correctly translated proximity join is an R-tree scan feeding the exact predicate, not a nested loop.

EXPLAIN ANALYZE
SELECT s.id, c.name
FROM s JOIN c ON c.g && s.g
WHERE ST_DWithin(s.g, c.g, 500);

Check three things, top-down:

• RTREE_INDEX_SCAN present. Its absence means the && predicate never reached the index — usually because the indexed column was wrapped in ST_Transform inside the join. Transform in a CTE first, index the projected column, then join on the bare column.
• No implicit VARCHAR cast. A CAST(... AS GEOMETRY) or per-row ST_GeomFromText node means a WKT literal leaked in; cast it once outside the join.
• Row estimate within ~30% of actual at the join. Large drift after a translation points at a unit mismatch — the 500 is being read in degrees, not metres.

Geometry Validation & Fallback Routing #

A translation can be syntactically perfect and still fail on geometry that PostGIS tolerated. ST_MakeValid exists in both engines, so gate any ported dissolve or overlay behind a validity check before trusting its output:

-- Repair before the ported operator runs; ST_MakeValid has identical semantics to PostGIS.
SELECT id, ST_IsValidReason(geom) FROM parcels WHERE NOT ST_IsValid(geom);
UPDATE parcels SET geom = ST_MakeValid(geom) WHERE NOT ST_IsValid(geom);

When a geography-derived tolerance no longer makes sense in planar space, fall back to a small metric buffer instead of a raw degree value, keeping the && pre-filter in front so the index still drives the scan. If a translated ST_Union_Agg breaches memory on a large group, reduce precision with ST_ReducePrecision before the union or switch to a batched ST_Collect, exactly as the aggregation guide recommends.

Related #

See also

• CRS mapping and transformations — the explicit source/target reprojection that replaces PostGIS’s stored-SRID ST_Transform.
• Spatial joins and proximity filters — the &&-then-exact pattern every translated proximity query needs.
• Migrating PostGIS GiST indexes to R-tree — why the explicit && predicate is what reaches the migrated index.

Up: PostGIS to DuckDB Spatial migration