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.

The only category that does not announce itself is the second, which is why a value diff matters more than a compile check.

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.

Frequently Asked Questions #

Which functions differ most subtly between the two? #

The pairs where argument order carries the meaning — ST_Contains versus ST_Within is the classic — and anything that relied on a geography column, where PostGIS was silently computing on a spheroid. Both compile and both return plausible numbers, which is exactly what makes them expensive to find later.

Is there a mechanical way to find the functions that need attention? #

Extract every ST_ call from your SQL corpus with a regular expression, deduplicate it, and check each name against DuckDB’s function catalogue. That gives you the “does not exist” list in minutes. It does not give you the “exists but behaves differently” list, which only a value diff will find.

What replaces the geography type? #

An explicit reprojection into a metric frame, followed by ordinary planar functions. Choose the frame for accuracy over your extent — a national grid or the relevant UTM zone — rather than reaching for Web Mercator, whose scale error grows with latitude and will quietly change every distance you compute.

Do aggregate function names match? #

Mostly, with one recurring trap: ST_Union is both a two-argument function and an aggregate in PostGIS, and the aggregate form is spelled ST_Union_Agg in DuckDB. A port that leaves it as ST_Union in a GROUP BY either errors or silently does something else depending on the arity, so it is worth grepping for specifically.

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