Import Pipeline¶

This guide explains how OSM data flows from a Geofabrik PBF extract into the PostgreSQL database. It is aimed at contributors who want to add new OSM data types or change how existing data is processed.

Overview¶

Geofabrik PBF (~300 MB Bundesland extract)
        │
        ▼  osmium extract (bbox clip — Nominatim lookup or OSM_BBOX override)
Bbox-clipped PBF (~5–20 MB)
        │
        ▼  osmium tags-filter
Tag-filtered PBF (~1–5 MB)
        │
        ▼  osm2pgsql --slim --drop --hstore
        │  (default pgsql output — no Lua script)
        │
        ▼  PostgreSQL tables (EPSG:3857)
        │   planet_osm_point    — OSM nodes
        │   planet_osm_polygon  — closed ways and relations as polygons
        │
        ▼  api.sql applied by import.sh
        │   ALTER SYSTEM (pg tuning)
        │   CREATE INDEX on planet_osm_* (idempotent)
        │   BUILD playground_stats under a staging name, then swap it in
        │   CREATE/REPLACE api schema functions
        │
        ▼  PostgREST /api/rpc/* endpoints

The pipeline is driven by importer/import.sh, which is the CMD of the importer Docker image (importer/Dockerfile).

osm2pgsql and the hstore schema¶

osm2pgsql runs in its default pgsql output mode — no Lua flex script is involved. It imports the filtered PBF into standard tables (planet_osm_point, planet_osm_polygon), all in EPSG:3857.

A fixed set of common OSM keys get their own columns (name, operator, access, surface, leisure, amenity, sport, natural, highway, …). Every other tag is stored as a key→value pair in the tags hstore column. The --hstore flag enables this.

All application logic (computing completeness, deriving filter flags, building cluster counts) lives in importer/api.sql, which queries these standard tables.

Adding a new attribute to an existing object type¶

Two distinct cases:

The object type is already imported (e.g. adding a new attribute to playground equipment nodes, which are already in the filter as n/playground):

The tag value is already in the tags hstore — access it with tags->'my_tag' in importer/api.sql.
Run make db-apply to apply the SQL change (no re-import needed).

The tag is used on a new object type not yet in the filter (e.g. amenity=drinking_water):

Add the tag to the osmium tags-filter list in importer/import.sh. Otherwise the importer silently discards those objects.
In importer/api.sql, add or extend a function to read the new data. Use tags->'my_tag' for hstore values or the named column for common keys.
Run make import — the tag filter change requires a full re-import.

Adding a new OSM object type¶

Add its tag to the osmium tags-filter list in importer/import.sh.
In importer/api.sql, add a new PostgREST function that queries planet_osm_point or planet_osm_polygon (depending on geometry type). Follow the pattern of existing functions.
Expose it: GRANT EXECUTE ON FUNCTION api.your_function(...) TO web_anon;
Run make import to load the data. Use make db-apply for subsequent SQL-only iterations.

Testing: there are no SQL unit tests. Verify changes with a real import (make import) against a region that has the relevant data, then check the feature appears correctly in the UI.

The tag filter (`importer/import.sh`)¶

Before osm2pgsql runs, osmium tags-filter keeps only objects with tags the app actually queries. This is a performance optimisation — a Bundesland PBF goes from ~300 MB to ~1–5 MB. The current filter passes:

leisure=playground, leisure=pitch, leisure=fitness_station, leisure=picnic_table
amenity=bench, amenity=shelter, amenity=toilets, amenity=ice_cream, amenity=cafe, amenity=restaurant
natural=tree, natural=tree_row, playground=*
highway=bus_stop, shop=chemist/supermarket/convenience, emergency=*
boundary=administrative, type=multipolygon

If you add a new OSM object type, add its tag to this filter — otherwise the importer will silently discard those objects.

The `playground_stats` materialised view¶

The most important post-import step in importer/api.sql is building the playground_stats materialized view. It pre-computes per-playground statistics (tree count, bench count, sport types, completeness state) so get_playgrounds_bbox is a fast indexed lookup rather than an aggregation query.

It is built entirely from the standard osm2pgsql tables:

Playground polygons and nodes come from planet_osm_polygon (leisure = 'playground') and planet_osm_point.
Equipment and trees are joined from planet_osm_point and planet_osm_polygon using spatial containment (ST_Intersects / ST_DWithin).
Less common tags (e.g. panoramax, opening_hours) are read from the tags hstore column.

How the rebuild avoids an outage¶

The view is not dropped and recreated in place. api.sql builds it under the staging name playground_stats_new, indexes it, and then swaps it in inside a single DO block:

DROP MATERIALIZED VIEW IF EXISTS public.playground_stats CASCADE;
ALTER MATERIALIZED VIEW public.playground_stats_new RENAME TO playground_stats;

The build is the long statement — minutes on a Bundesland-sized region. Dropping first meant the view was absent for that whole window, and every reader of it (get_meta, both map tiers) failed with relation "public.playground_stats" does not exist. Since the daemon importer re-applies api.sql on every container start, that made any routine restart a multi-minute API outage (#720). With the swap, readers keep the old view until the rename commits.

A DO block rather than a literal BEGIN/COMMIT because both callers must stay atomic: import.sh runs the file with plain psql -f (autocommit), make db-apply runs it with --single-transaction.

The planet_osm_* indexes are created before the build for the same reason — the build joins against those tables, so it is the statement that needs them.

The PostgREST connections are terminated inside the same transaction as the swap, not in a statement ahead of it. PostgREST reconnects on its own, so terminating separately leaves a gap in which a fresh connection can take a read lock — and the swap's pending AccessExclusiveLock then queues ahead of every later reader, stalling API traffic for as long as that straggler runs. In one transaction there is no gap.

Disk headroom: the staging view coexists with the live one, so peak disk use for playground_stats and its indexes is roughly double the steady-state size during a rebuild. Negligible for a city-sized region (Fulda's matview is ~230 kB) but worth checking before an apply on a Bundesland-sized node with a nearly full volume.

The completeness logic in api.sql must stay in sync with app/src/lib/completeness.js in the frontend. Both implement the same rule:

Criterion	SQL (api.sql)	JS (completeness.js)
Has equipment	a `playground=*` object inside the area, or a soccer / basketball / table-tennis pitch	`device_count > 0 \\|\\| table_tennis_count > 0 \\|\\| has_soccer \\|\\| has_basketball`
Has info	`surface` set, or `access` set and not `'yes'`, or `opening_hours` set	`!!(props.opening_hours \\|\\| props.surface \\|\\| (props.access && props.access !== 'yes'))`

complete = both present
partial = exactly one present
missing = neither

Street furniture does not count as equipment. Benches, shelters and picnic tables are often mapped inside a playground area by someone who never mapped the play equipment, so counting them lifts playgrounds that have nothing to play on. The flags derived from equipment tags (is_water, for_baby, for_toddler, for_wheelchair) are excluded for the same reason — a bench carrying wheelchair=yes is the same false signal through a side door (#776).

A photo is not a criterion. Photo tags are rare in OSM, so requiring one for the top bucket gated whole regions out of it. Availability is still derived — has_photo on playground_stats, hasPhotoSignal() in JS — but only to drive an additive marker, never the rating. name and operator are not criteria either: administrative data, not useful to a parent choosing a playground.

Full rationale in ../reference/completeness.md.

If you change the completeness criteria, update both files and rebuild the materialised view with make db-apply.

Filter flags (`for_baby`, `for_toddler`, `is_water`, …)¶

Several boolean columns in playground_stats are computed from equipment found inside each playground polygon. They drive both the filter UI and the filter_attrs payload in the cluster RPC.

Flag	Triggers
`for_baby`	`baby=yes` on any equipment; `playground` ∈ `baby_swing`, `basketswing`, `sandpit`, `springy`; `capacity:baby` present
`for_toddler`	`provided_for:toddler=yes` on any equipment; `playground=basketswing`
`is_water`	`playground` contains `water` or ∈ `splash_pad`, `pump`
`for_wheelchair`	boolean projection of `wheelchair_play = 'yes'` (see below)
`wheelchair_play`	Tri-state, derived from play devices only (`playground=`). `'yes'` when one is tagged `wheelchair` ∈ `yes`, `limited`, `designated`; `'no'` when devices carry a `wheelchair` tag but none qualifies; `NULL` when none carries one. A `playground=sandpit` counts only on `wheelchair=yes` — the wiki documents no value for a roll-under sand table, so that tag is a mapper's only way to record one; `limited`/`no` on a sandpit is too ambiguous to support a negative finding and is ignored on both sides. The playground area's own `wheelchair` tag is deliberately not* an input — it describes site access, which barely separates playgrounds, whereas the equipment tag is what the audience is asking about. Street furniture and pitches cannot set it either. See `docs/user-guide.md` for the user-facing wording
`has_soccer` / `has_basketball`	`leisure=pitch` with matching `sport` value
`has_fence`	`enclosed=yes` or `barrier=fence` on playground
`has_dogs`	`dog=yes` on playground
`has_theme`	an allowlisted `playground:theme` value on the playground area or any device within it (allowlist mirrors `SUPPORTED_THEMES` in `app/src/lib/playgroundThemes.js`)
`has_shade`	`shade` tag on playground — `true` when `shade=yes`, `false` when `shade=no`, `null` when untagged

All flag logic lives in importer/api.sql (the equip_stats CTE inside playground_stats).

If you add a new flag, add it to playground_stats in api.sql and run make db-apply. A full make import is only needed if the new flag depends on a tag not yet in the tag filter.

Testing: verify with a real import against a region that has playgrounds with the relevant equipment, then check the filter chip appears correctly in the UI.

Applying schema changes without a full re-import¶

make db-apply runs only the api.sql step (skips the osm2pgsql data load). Use it when: - You added or changed a PostgREST function - You changed the playground_stats view definition - You added a new filter flag

Do a full make import when you changed the tag filter (new object types needed in the PBF) — these affect which objects are stored in planet_osm_* tables.