DuckDB in FME: how coordinate queries change between versions

If you run DuckDB SQL inside FME through the SQLCreator, SQLExecutor or DatabaseQuerier, the same query does not behave the same way in every release. Getting longitude and latitude out of a geometry column is the clearest example, and it matters most when you upgrade.

DuckDB has become one of the quickest ways to work with cloud-native data in FME. It reads Parquet straight from object storage, filters it in SQL and hands FME only the rows you need. But the way FME and DuckDB treat geometry has changed twice in recent releases. A query written for one version can fail or behave differently in another.

Our colleague Simon Green has been taking a deep dive into DuckDB in FME. He built a small workspace that shows all three behaviours side by side, and you can download it at the end of this post.

The example: castles from Overture Maps

The workspace queries the Places theme of Overture Maps directly from its public S3 bucket. It finds every castle with a phone number, postcode and address inside a bounding box around the border between England and Wales. It uses an in-memory DuckDB connection, so there is nothing to download or set up.

Here is the query as it runs in FME 2025.2:

FME_SQL_DELIMITER ; INSTALL spatial; LOAD spatial; SELECT  names.primary AS name,  ST_X(geometry) AS longitude,  ST_Y(geometry) AS latitude,  phones[1] AS phone,  addresses[1].freeform AS address_freeform,  addresses[1].locality AS address_locality,  addresses[1].postcode AS address_postcode FROM read_parquet('s3://overturemaps-us-west-2/release/2026-09-23.0/theme=places/type=*/*') WHERE  basic_category = 'castle'  AND phone IS NOT NULL  AND address_postcode IS NOT NULL  AND address_freeform IS NOT NULL  AND bbox.xmin BETWEEN -3.5 AND -2.2  AND bbox.ymin BETWEEN 51.5 AND 53.5

The first line tells FME to split the statement on semicolons. Without it, the INSTALL and LOAD commands for the spatial extension won't run as separate statements.

The workspace holds three versions of this query, one for each range of FME releases. Only the geometry lines change.

Three versions, three ways to get coordinates

• FME 2024.1 to 2025.1 use ST_X(ST_GeomFromWKB(geometry)). Longitude and latitude arrive as attributes, with no geometry on the features.
• FME 2025.2 to 2026.2 use ST_X(geometry). Longitude and latitude still arrive as attributes only.
• FME 2026.3 onwards use ST_X(geometry) and add geometry to the SELECT. The features come out as points.

FME 2024.1 to 2025.1: cast the blob first

In these releases the Overture geometry column reaches DuckDB as a binary blob of well-known binary (WKB). The spatial functions don't accept a blob, so ST_X(geometry) fails. You have to convert it to a geometry first:

ST_X(ST_GeomFromWKB(geometry)) AS longitude, ST_Y(ST_GeomFromWKB(geometry)) AS latitude,

FME 2025.2 to 2026.2: no cast needed

The reason is the version of DuckDB that ships inside FME. DuckDB 1.1 taught the spatial extension to read GeoParquet geometry columns as a native geometry type, but FME 2025.0 still bundled DuckDB 1.0, as users noted on the Safe Community idea asking for newer DuckDB support. From FME 2025.2 the cast is no longer needed and ST_X(geometry) works directly.

This is the change to watch when you move between versions. A query written for 2025.2 fails if you run it in FME 2024.1, because the column is still a blob there. If you run an older query in a newer release, the cast is redundant. Remove it so the SQL matches the version you run.

FME 2026.3 onwards: geometry comes through as geometry

FME 2026.3 adds support for geometry columns in DuckDB query results. Add geometry to the SELECT list and the features leave the SQLCreator as points, with the coordinate attributes alongside:

  addresses[1].postcode AS address_postcode,  geometry

In earlier releases you rebuild the points yourself. Either build them from the longitude and latitude attributes with a VertexCreator, or select geometry as a binary attribute and convert it with a GeometryReplacer set to OGC Well Known Binary. Our older DuckDB demos, also available below, use the second approach. From 2026.3 that step is optional, and you only need ST_X and ST_Y if you want the coordinates as attributes too.

What about the DatabaseQuerier?

Safe Software's SQLCreator documentation now says to consider the newer DatabaseQuerier instead. The DatabaseQuerier combines the two older transformers. With nothing connected to its Initiator port it runs once, like the SQLCreator. With input features it runs once per feature, like the SQLExecutor.

Safe's DuckDB reader documentation for 2026.3 now names the DatabaseQuerier as the primary way to use DuckDB in FME. All three transformers send the SQL to the same DuckDB connection, so the version rules above apply whichever one you choose.

A note on Overture releases

Overture publishes new releases regularly and removes older ones from the bucket over time. If you run the workspace and see an error like this one:

IO Error: No files found that match the pattern "s3://overturemaps-us-west-2/release/2026-09-23.0/theme=places/type=*/*"

then the release in the read_parquet() path has been retired. Check the releases page in the Overture documentation and update the date in the URL to the latest release.

Before you upgrade

If your workspaces use DuckDB, check these four things when you move to a new FME release:

1. Search your workspaces for ST_GeomFromWKB in DuckDB queries. On FME 2025.2 or later, remove the cast.
2. On FME 2026.3 or later, decide whether to return geometry directly and drop the VertexCreator that rebuilds points.
3. If a workspace runs on FME Flow, write the SQL for the Flow engine's version, not your FME Form version.
4. Rerun each workspace and compare the output before you publish it.

Download the workspaces

DuckDB SQL FME version subtleties (FME 2024.1 or later)

The three SQLCreators in this workspace each carry an annotation saying which FME versions they suit. Run the one that matches your version, or run all three to see which fail.