Skip to content

ADR 013: Our own OpenStreetMap places in PostGIS, refreshed on demand

  • Status: Accepted (numbered 008 until 2026-10-25, when it was renumbered: the depth ADR has 008 too)
  • Date: 2026-10-19
  • Deciders: Owner
  • Technical Context: apps/server (place names of tracks, stops and trip days), infra/postgres; behaviour in ingestion.md § OSM places, operation in DEPLOYMENT.md. Naming rules unchanged: ADR 002.

Context

Place names came live from the public Overpass server: several lookups per track (start, end, every stop, every trip day). On 2026-10-18 Overpass answered with 30 s timeouts and HTTP 502, an upload of 20 files took 20 minutes or more, and failed lookups left coordinates as names. Naming was switched off (SAILEVER_LOCATION_NAMING). Every lookup also sent a position of the user's track to an outside server.

Decision

  1. The slice of OpenStreetMap that naming needs lives in our own Postgres, with PostGIS. The Postgres accessory runs our own image, postgres:16.15-trixie plus Debian's postgresql-16-postgis-3 (infra/postgres), built for arm64 in Actions. Same server version and data directory as before, so switching needed no dump and restore.
  2. Refreshed only on demand, never on a schedule: bin/rails osm:refresh (Server workflow mode osm-refresh). Places change slowly. The task runs on the app VM itself: Geofabrik extracts one country at a time (MD5 checked), osmium filter and export, staging tables, then one swap of the live tables. No build pipeline, bucket or CI secret. Considered: a GitHub Actions build into an OCI bucket, as the original plan proposed. Rejected for now, because it needs a bucket, a CI key and a recurring job for data that rarely changes.
  3. The Mediterranean and Adriatic countries first; more countries are a change of OsmPlaces::Refresh::COUNTRIES.
  4. The coastline comes from the same extracts (natural=coastline ways), not from osmdata.openstreetmap.de, so one tool (osmium) does all of it. Coastline ways on the shore of a named island or of any islet are taken out at import, as the Overpass query did.
  5. The tables belong to the import, not to the schema: built by CREATE TABLE … AS, swapped in by renames, kept out of schema.rb (ignore_tables) and out of the nightly backup (--exclude-table-data). A restore rebuilds them with osm:refresh. No activerecord-postgis-adapter and no structure.sql: the queries are plain SQL.
  6. Overpass is removed, and with it the SAILEVER_LOCATION_NAMING switch. Until the first import nothing is named. bin/rails places:rename then names all tracks, stops and trip days again (typed names stay, no trip mail).

Consequences

  • A lookup is one SQL query of a few milliseconds and the same position always gets the same name. No position leaves our servers for naming.
  • The places are as old as the last osm:refresh. A refresh takes about an hour on the 1-OCPU app VM, and the 50 GB disk briefly holds the largest extract (France, about 4.6 GB).
  • Positions outside the imported countries are named by coordinates or a landmark that is imported, until their countries are added.
  • The database needs PostGIS everywhere: development (brew install postgis), CI (postgis/postgis service) and the restore test (postgis/postgis:16-3.5).
  • The tables already carry the kinds and the name index for a later POI search (nearest marina or fuel berth, search by name).