DuckDB Source

This guide covers serving vector tiles generated on-the-fly from SQL queries against embedded DuckDB databases — PostGIS-class spatial queries with zero infrastructure.

Why DuckDB?

DuckDB is an embedded analytical database that runs in-process — no server to install, configure, or maintain. Combined with its spatial extension, it provides PostGIS-equivalent spatial operations:

Feature	DuckDB Source	PostgreSQL/PostGIS Source
Setup	Zero — embedded in binary	Requires running PostgreSQL server
Configuration	SQL query in config.toml	Connection string + credentials
Data formats	Parquet, CSV, JSON, Shapefile, S3	PostgreSQL tables only
Spatial ops	ST_Intersects, ST_Simplify, etc.	Full PostGIS suite
Deployment	Single binary, no deps	Requires PostgreSQL

Quick Start

[[sources]]
id = "places"
type = "duckdb"
path = "/data/overture.duckdb"
query = """
  SELECT name, ST_AsWKB(geometry) as geometry
  FROM places
  WHERE ST_Intersects(geometry, ST_MakeEnvelope({bbox}))
"""

Info

DuckDB support requires the duckdb feature flag: cargo build --features duckdb

Configuration

Query a DuckDB Database

[[sources]]
id = "places"
type = "duckdb"
path = "/data/overture.duckdb"
query = """
  SELECT name, category, ST_AsWKB(geometry) as geometry
  FROM places
  WHERE ST_Intersects(geometry, ST_MakeEnvelope({bbox}))
"""
layer_name = "places"
minzoom = 0
maxzoom = 14

Query GeoParquet Directly (No Database File)

[[sources]]
id = "buildings"
type = "duckdb"
path = ""
query = """
  SELECT name, height, ST_AsWKB(geometry) as geometry
  FROM read_parquet('/data/overture-buildings.parquet')
  WHERE bbox.xmin <= {bbox_xmax} AND bbox.xmax >= {bbox_xmin}
    AND bbox.ymin <= {bbox_ymax} AND bbox.ymax >= {bbox_ymin}
"""
layer_name = "buildings"

Set path to an empty string for in-memory DuckDB — no .duckdb file needed.

Full Options

Option	Description	Default
`query`	SQL template with `{bbox}`, `{z}`, `{x}`, `{y}` placeholders	Required
`path`	Path to `.duckdb` file (empty string = in-memory)	Required
`layer_name`	MVT layer name in tile output	Source `id`
`minzoom`	Minimum zoom level to serve	`0`
`maxzoom`	Maximum zoom level to serve	`22`

SQL Template Variables

The query field is a SQL template. These placeholders are substituted per tile request:

Variable	Type	Description	Example
`{bbox}`	4 doubles	Tile bounding box as `xmin, ymin, xmax, ymax`	`-122.5, 37.7, -122.4, 37.8`
`{bbox_xmin}`	double	Tile bbox minimum longitude	`-122.5`
`{bbox_ymin}`	double	Tile bbox minimum latitude	`37.7`
`{bbox_xmax}`	double	Tile bbox maximum longitude	`-122.4`
`{bbox_ymax}`	double	Tile bbox maximum latitude	`37.8`
`{z}`	integer	Zoom level	`14`
`{x}`	integer	Tile X coordinate	`8192`
`{y}`	integer	Tile Y coordinate	`5461`

{bbox} expands to four comma-separated values, which is exactly what ST_MakeEnvelope() expects:

ST_MakeEnvelope({bbox})
-- becomes:
ST_MakeEnvelope(-122.5, 37.7, -122.4, 37.8)

Spatial Functions

DuckDB's spatial extension provides PostGIS-equivalent functions. The extension is auto-loaded at startup.

Spatial Predicates

WHERE ST_Intersects(geometry, ST_MakeEnvelope({bbox}))
WHERE ST_Within(geometry, ST_MakeEnvelope({bbox}))
WHERE ST_Contains(boundary, ST_Point(-74.006, 40.7128))

Geometry Construction

ST_MakeEnvelope(xmin, ymin, xmax, ymax)  -- bbox → polygon
ST_Point(longitude, latitude)             -- coordinates → point
ST_GeomFromWKB(wkb_blob)                 -- WKB → geometry

Geometry Output

ST_AsWKB(geometry)   -- geometry → WKB (required for MVT encoding)

Geometry Processing

ST_Simplify(geometry, tolerance)                    -- Douglas-Peucker simplification
ST_SimplifyPreserveTopology(geometry, tolerance)     -- topology-preserving simplification
ST_Buffer(geometry, distance)                       -- buffer around geometry
ST_Centroid(geometry)                               -- centroid point
ST_Area(geometry)                                   -- polygon area

Query Patterns

Basic Spatial Filter

query = """
  SELECT name, ST_AsWKB(geometry) as geometry
  FROM places
  WHERE ST_Intersects(geometry, ST_MakeEnvelope({bbox}))
"""

Zoom-Dependent Simplification

Simplify geometries at low zoom levels for faster rendering:

query = """
  SELECT name, type,
    ST_AsWKB(
      CASE
        WHEN {z} >= 14 THEN geometry
        WHEN {z} >= 10 THEN ST_Simplify(geometry, 0.0001)
        WHEN {z} >= 6  THEN ST_Simplify(geometry, 0.001)
        ELSE                ST_Simplify(geometry, 0.01)
      END
    ) as geometry
  FROM polygons
  WHERE ST_Intersects(geometry, ST_MakeEnvelope({bbox}))
"""

Feature Filtering by Zoom

Show fewer features at low zoom levels:

query = """
  SELECT name, height, ST_AsWKB(geometry) as geometry
  FROM buildings
  WHERE ST_Intersects(geometry, ST_MakeEnvelope({bbox}))
    AND height > CASE WHEN {z} < 12 THEN 50 ELSE 0 END
"""

Multi-Table Joins

Combine data from multiple tables in a single tile:

query = """
  SELECT b.name, b.height, c.population,
         ST_AsWKB(b.geometry) as geometry
  FROM buildings b
  JOIN census c ON ST_Within(b.geometry, c.boundary)
  WHERE ST_Intersects(b.geometry, ST_MakeEnvelope({bbox}))
"""

Directory and Glob Support

Unlike the GeoParquet source (which currently takes a single file), DuckDB's read_parquet() natively supports globs and directories — no additional code needed:

-- All parquet files in a directory
query = """
  SELECT name, ST_AsWKB(geometry) as geometry
  FROM read_parquet('./data/overture/theme=places/type=place/*.parquet')
  WHERE bbox.xmin <= {bbox_xmax} AND bbox.xmax >= {bbox_xmin}
"""

-- Recursive glob across partitions
query = """
  SELECT name, ST_AsWKB(geometry) as geometry
  FROM read_parquet('./data/overture/theme=places/**/*.parquet')
  WHERE ST_Intersects(geometry, ST_MakeEnvelope({bbox}))
"""

-- Hive partitioning (auto-extracts theme/type as columns)
query = """
  SELECT name, theme, type, ST_AsWKB(geometry) as geometry
  FROM read_parquet('./data/overture/**/*.parquet', hive_partitioning=true)
  WHERE type = 'place'
    AND ST_Intersects(geometry, ST_MakeEnvelope({bbox}))
"""

This makes DuckDB the simplest way to serve tiles from large partitioned datasets like Overture Maps (483 files, 476 GB) without any preprocessing.

Query GeoParquet from S3/HTTP

DuckDB reads remote files natively:

query = """
  SELECT name, ST_AsWKB(geometry) as geometry
  FROM read_parquet('s3://overturemaps-us-west-2/release/2025-03/theme=places/**/*.parquet')
  WHERE ST_Intersects(geometry, ST_MakeEnvelope({bbox}))
"""

Aggregation per Tile

Compute statistics within each tile:

query = """
  SELECT type,
         count(*) as feature_count,
         avg(height) as avg_height,
         ST_AsWKB(ST_Centroid(ST_Collect(geometry))) as geometry
  FROM buildings
  WHERE ST_Intersects(geometry, ST_MakeEnvelope({bbox}))
  GROUP BY type
"""

Fast Filtering with Bbox Columns

For GeoParquet files with bbox struct columns (GeoParquet 1.1), combine column-level filtering with spatial predicates for best performance:

query = """
  SELECT name, ST_AsWKB(geometry) as geometry
  FROM read_parquet('/data/places.parquet')
  WHERE bbox.xmin <= {bbox_xmax} AND bbox.xmax >= {bbox_xmin}
    AND bbox.ymin <= {bbox_ymax} AND bbox.ymax >= {bbox_ymin}
    AND ST_Intersects(geometry, ST_MakeEnvelope({bbox}))
"""

The bbox pre-filter enables Parquet row group pruning before geometry evaluation — essential for performance on large files.

Geometry Column Convention

The query must return a column named geometry, geom, or wkb_geometry containing WKB-encoded geometry blobs. tileserver-rs detects these column names and uses them for MVT encoding.

Use ST_AsWKB() to convert DuckDB geometry objects to WKB:

SELECT ST_AsWKB(geometry) as geometry FROM ...

MLT Transcoding

DuckDB sources output MVT tiles. If a client requests .mlt format, the existing MLT transcoding layer converts MVT→MLT on-the-fly:

GET /data/places/14/8192/5461.mlt  →  SQL query → MVT → MLT → response

Performance

Scenario	Query Time	Notes
Simple bbox filter, 10K features	~10–50ms	With spatial extension
Aggregate query (count/sum)	~20–100ms	DuckDB excels at analytics
Multi-table join	~30–150ms	Depends on join selectivity
Remote Parquet over S3	~200–500ms	Network latency dominant
GeoParquet with bbox pre-filter	~50–200ms	Row group pruning

Tips

Use ST_AsWKB() for the geometry output — DuckDB's native GEOMETRY type isn't WKB by default
Combine bbox column pre-filter + ST_Intersects for best performance on GeoParquet
Use {z} for zoom-dependent simplification and feature filtering
For large datasets, create a .duckdb file with spatial indexes rather than querying raw Parquet per request
DuckDB operations run in spawn_blocking — they don't block the async runtime