Reading OSM PBF with DuckDB Spatial

Pointing SQL at a PBF file with no ingestion step is genuinely useful, and it is also the shortest route to a misunderstanding: the reader gives you OSM’s element model, not features, and the gap between them is where most of the work lives.

Prerequisites

Conceptual minimum

The reader exposes a PBF as a table of elements, not features. Each row is a node, a way or a relation, with its identifier, its tags as a map, and — for nodes — a coordinate. Ways carry a list of node references; relations carry members.

That model has three consequences.

Nodes are mostly not features. The overwhelming majority exist only to give ways their shape, and a query that selects all nodes selects ten times more rows than there are things in the world.

Way geometry is not there. A way row holds references, not coordinates, so building a line means joining back to the nodes and ordering by position — a self-join over the largest table in the file.

Relations are harder still. Assembling a multipolygon requires the ring logic described in Handling Multipolygon Members with No Role, which is not expressible in a single query.

Where the reader is excellent is tag-level questions about nodes: counting amenities, finding value distributions, auditing a key’s usage. Those are one filter over one table and they need no geometry assembly at all.

Which questions the direct reader answers well, and which need a parser A grid of four question shapes against how well a direct SQL reader handles each. Counting tagged nodes by value is a single filter over one table and is answered excellently. Auditing which keys appear and how often is a map aggregation and is also answered excellently. Building way geometry requires a self-join against the node table ordered by position, which works but is expensive. Assembling multipolygon relations requires ring logic that a single query cannot express, so a parser is the right tool. Four question shapes, two the reader excels at Reader handles it Why Count tagged nodes excellently one filter, one table Audit key usage excellently a map aggregation Build way geometry expensively a self-join on nodes Assemble relations not really ring logic, not SQL The first two are the majority of ad hoc questions people actually have about an extract, which is why the reader is so useful.
Reaching for it to build features is where the frustration starts; reaching for it to ask about tags is where it shines.

Runnable solution

sql 
-- Query a PBF directly. No ingestion, no intermediate files.
INSTALL spatial;
LOAD spatial;

-- 1. What the reader gives you: one row per ELEMENT, tags as a map.
SELECT kind, count(*) AS elements
FROM st_readosm('poland-latest.osm.pbf')
GROUP BY kind
ORDER BY elements DESC;

-- 2. The reader's strongest use: a tag-level question over nodes.
SELECT tags['amenity'] AS amenity, count(*) AS n
FROM st_readosm('poland-latest.osm.pbf')
WHERE kind = 'node' AND tags['amenity'] IS NOT NULL
GROUP BY amenity
ORDER BY n DESC
LIMIT 20;

-- 3. Key usage audit: which keys exist, and how widely.
SELECT key, count(*) AS uses
FROM (
  SELECT unnest(map_keys(tags)) AS key
  FROM st_readosm('poland-latest.osm.pbf')
  WHERE tags IS NOT NULL AND len(tags) > 0
)
GROUP BY key
HAVING uses > 1000
ORDER BY uses DESC;

-- 4. Way geometry: possible, and a self-join against the largest table.
--    Worth doing once into a table, never repeatedly in an ad hoc query.
CREATE TABLE node_xy AS
SELECT id, lon, lat
FROM st_readosm('poland-latest.osm.pbf')
WHERE kind = 'node';

CREATE TABLE way_line AS
WITH refs AS (
  SELECT w.id AS way_id,
         unnest(w.refs) AS node_id,
         generate_subscripts(w.refs, 1) AS position,
         w.tags AS tags
  FROM st_readosm('poland-latest.osm.pbf') w
  WHERE w.kind = 'way' AND w.tags['highway'] IS NOT NULL
)
SELECT r.way_id,
       any_value(r.tags) AS tags,
       -- Ordering by position is essential: a line built from unordered
       -- coordinates is a scribble, and nothing about it errors.
       ST_MakeLine(list(ST_Point(n.lon, n.lat) ORDER BY r.position)) AS geom
FROM refs r
JOIN node_xy n ON n.id = r.node_id
GROUP BY r.way_id
HAVING count(*) >= 2;
python 
from __future__ import annotations

import logging

import duckdb

logging.basicConfig(level=logging.INFO, format="%(levelname)s: %(message)s")
logger = logging.getLogger("osm.duckdb.pbf")


def key_audit(path: str, minimum: int = 1000) -> list[tuple[str, int]]:
    """Which tag keys appear often enough to be worth modelling?"""
    conn = duckdb.connect()
    conn.execute("INSTALL spatial; LOAD spatial;")
    rows = conn.execute("""
        SELECT key, count(*) AS uses
        FROM (SELECT unnest(map_keys(tags)) AS key
              FROM st_readosm(?) WHERE tags IS NOT NULL AND len(tags) > 0)
        GROUP BY key HAVING uses >= ? ORDER BY uses DESC
    """, [path, minimum]).fetchall()
    logger.info("%d key(s) used at least %d time(s)", len(rows), minimum)
    return rows


def geometry_cost_warning(path: str) -> None:
    """Report how large the node table is before anybody joins against it."""
    conn = duckdb.connect()
    conn.execute("INSTALL spatial; LOAD spatial;")
    nodes, tagged = conn.execute("""
        SELECT count(*) FILTER (WHERE kind = 'node'),
               count(*) FILTER (WHERE kind = 'node' AND len(tags) > 0)
        FROM st_readosm(?)
    """, [path]).fetchone()
    logger.info("%d node(s), of which %d carry tags (%.1f%%)",
                nodes, tagged, 100 * tagged / max(1, nodes))
    if nodes > 50_000_000:
        logger.warning("a way-geometry self-join against %d node(s) will be "
                       "slow and memory-hungry; materialise the node table "
                       "once or use a streaming parser", nodes)


if __name__ == "__main__":
    geometry_cost_warning("poland-latest.osm.pbf")
    for key, uses in key_audit("poland-latest.osm.pbf")[:15]:
        logger.info("%-24s %d", key, uses)

Step-by-step walkthrough

  1. Understand the row shape first. Grouping by element kind before anything else establishes that the table is elements, not features, and shows the ratio of nodes to everything else.
  2. Ask tag questions directly. A filter on a map key over one table is exactly what the reader is good at, and it needs no joins, no geometry and no assembly.
  3. Audit keys with an unnest. Expanding the tag map into rows makes “which keys exist and how often” a one-query answer, which is the fastest way to decide what a schema should promote.
  4. Materialise the node table once. Every way-geometry query joins against it, and re-reading the file for each one is the difference between a minute and an hour.
  5. Order by position when building lines. A way’s node references are ordered and the order is the shape; aggregating without it produces a line that is geometrically nonsense and raises nothing.
  6. Filter ways before joining. Restricting to the ways you actually want before the self-join keeps the join’s left side small, which is the only lever that matters on that query.
  7. Warn about scale. Reporting the node count before somebody writes a self-join is the difference between an informed wait and a confused one.
Relative cost of four query shapes against the same PBF file Four query shapes measured against one country extract. Counting elements by kind is a single pass with no joins and is the baseline. A tag-level aggregation over nodes is a similar single pass with a filter. Building geometry for a filtered subset of ways requires a self-join against the node table and costs an order of magnitude more. Building geometry for every way costs another order of magnitude again and is where a streaming parser becomes the better tool. Four query shapes against one country extract Count by element kind baseline Tag aggregation on nodes about 1.6x Geometry for filtered ways about 19x Geometry for all ways about 160x The jump between the second and third rows is the node self-join, and it is why materialising the node table once pays for itself immediately.
The first two shapes are the ones worth reaching for SQL to answer; the last is the one worth reaching for a parser.
What has to happen between an element row and a usable feature Four stages showing the gap the reader leaves. The element row arrives with an identifier, a tag map and either a coordinate or a list of references. The resolve stage joins way references back to node coordinates, which is a self-join against the largest table in the file. The order stage arranges those coordinates by reference position, since the order is the geometry. The assemble stage builds rings and resolves containment for relations, which requires logic a query cannot express. Three stages the reader leaves to you element row tags plus references what you get resolve join to node coordinates the expensive part order by reference position order is the shape assemble rings and containment not expressible in SQL A parser does all three internally, which is exactly the work you take on when reaching for SQL instead.
The trade is real in both directions: SQL gives you the tag questions for free and charges for the geometry ones.

Verification

  • Element counts match a reference. Compare the counts by kind against osmium fileinfo.
  • Tagged node share is plausible. Well under a fifth of nodes carrying tags is normal; a much higher figure suggests the file is not a general extract.
  • Line geometry follows the road. Render a few built ways and confirm they trace roads rather than zig-zagging, which is the signature of a missing order clause.
  • Key audit agrees with expectations. The most common keys should be the ones you would predict; a surprise near the top usually means an import in the region.
  • Memory stays bounded. Watch the process during a geometry query; a spike to many gigabytes means the join is materialising more than expected.

Common errors and fixes

Symptom Root cause One-line fix
Lines zig-zag randomly Node order lost in aggregation Order by the reference position inside the aggregate
Query runs for hours Self-join over all nodes Materialise the node table and filter ways first
Memory exhausted Whole-file join materialised Restrict the way set before joining
Ways have one point Nodes missing from the extract Require at least two matched references
Tag filter matches nothing Map access on a null tag column Guard for a null or empty tag map
Counts differ from a reference Elements confused with features Group by element kind and read the shape first
Relations look empty Members present but geometry absent Assemble relations with a parser, not in SQL

Specification reference

DuckDB’s spatial extension provides a reader that exposes an OSM PBF file as a table of elements, with identifier, kind, a tag map, node coordinates and way references or relation members. It does not assemble way or relation geometry, which remains the consumer’s responsibility. See the DuckDB spatial extension documentation for the reader’s columns and its limitations.

Frequently Asked Questions

Can I use this instead of a parser?

For tag-level questions, yes, and it is often the better tool: no ingestion step, full SQL, and an answer in the time a parser would take to start. For anything needing geometry it depends on scale — a filtered subset of ways is entirely workable, and building geometry for every way in a country is where a streaming parser wins decisively on both time and memory.

Why do my lines look like scribbles?

Because the node order was lost. A way’s references are an ordered list and that order is the road’s shape; aggregating the joined coordinates without an explicit order produces whatever the engine happened to emit. Nothing errors, the geometry is valid, and it traces a path no road follows. An ordering clause inside the aggregate is the whole fix.

Why is the way-geometry query so slow?

Because it joins against the node table, which is the largest thing in the file by an order of magnitude. Materialising the nodes once rather than re-reading the file per query removes most of the cost, and filtering the ways before the join rather than after removes most of the rest. Both together turn an intolerable query into a slow but usable one.

What about relations?

The reader gives you members and roles, which is the input to assembly rather than the result of it. Building a multipolygon requires stitching member ways into rings and resolving containment geometrically, which is not something a single query expresses. Read the members in SQL if that helps you understand the data, and assemble in a parser.

Up one level: Choosing an OSM Parser: pyosmium, pyrosm or osmium-tool.