1 ADR 0023 postgis for reverse place lookup
Forgejo Actions edited this page 2026-09-25 16:38:07 +00:00

ADR 0023: PostGIS For Reverse Place Lookup

Status: accepted Date: 2026-05-22

Context

ADR 0022 accepts a Core-owned, local-first, provider-neutral reverse place lookup boundary. The remaining storage/indexing question was whether the first implementation should start with plain PostgreSQL tables, PostgreSQL plus PostGIS, a separate SQLite/flat-file index, or a custom local search structure.

Reverse place lookup needs to answer spatial questions that are central to automatic album titles and future Places/search/media-detail labels:

  • nearest place candidates for a coordinate, centroid, or rounded coordinate;
  • containment/fit for an album cluster envelope or centroid plus radius;
  • hierarchy selection across venue/toponym, locality, city, region, country, and fallback labels;
  • future provider data that may include points, bounds, polygons, or provider-specific geometry;
  • operator-inspectable import/update/cache state in the same deployment model as the rest of Core metadata.

Starting without spatial database support would keep the first slice slightly simpler, but it risks custom distance/containment logic and a foreseeable migration as soon as title quality requires better spatial queries.

Options Considered

Option Summary Benefits Costs / Risks
A. PostgreSQL tables only Store provider rows and approximate spatial fields in normal tables Lowest new dependency; simple backup story Weak nearest/containment semantics; likely custom spatial code; probable migration once quality matters
B. PostgreSQL + PostGIS Use PostGIS geometry/geography types and spatial indexes inside the authoritative PostgreSQL deployment Strong spatial primitives; inspectable; one DB authority; good fit for gazetteer import and envelopes Adds extension dependency and Docker/restore operational requirements
C. SQLite/flat-file spatial index Keep gazetteer index as a replaceable local artifact Isolates large provider data; easy to rebuild/delete Adds another storage artifact, backup/config story, and query/runtime boundary
D. Custom file/index structure Purpose-built lookup optimized for selected dataset Potentially fast and compact Least inspectable; more bespoke maintenance; harder migration/export reasoning

Decision

Start reverse place lookup with PostgreSQL + PostGIS.

PostGIS is part of the reverse lookup storage/indexing boundary, not an optional later optimization. Core will still expose provider-neutral lookup contracts and normalized place-label candidates. Product features must not depend on PostGIS-specific implementation details, GeoNames-native schema, or provider-native IDs. PostGIS is the internal spatial indexing/query mechanism for local gazetteer providers and cached lookup inputs/results.

The initial implementation should still be incremental:

  1. enable/document the PostGIS extension in the database deployment and restore path;
  2. import a tiny fixture gazetteer into provider-neutral tables using geometry/geography columns and spatial indexes;
  3. implement lookup by coordinate, centroid plus radius, and/or spatial envelope;
  4. return normalized candidates with provider/dataset/version/attribution provenance;
  5. prove automatic album title generation can consume the Core lookup service without GeoNames-specific logic.

Consequences

Positive

  • Avoids a foreseeable migration from ad hoc spatial approximation to real spatial indexing.
  • Keeps gazetteer data, cached labels, provenance, backup/restore state, and authorization-adjacent metadata in the existing PostgreSQL authority boundary.
  • Supports centroid/radius, bounding-box/envelope, nearest-place, and future polygon/bounds queries more honestly.
  • Makes country/region/venue/toponym quality experiments closer to production shape from the beginning.

Negative / Costs

  • Adds PostGIS as a deployment and restore dependency.
  • Docker Compose, database image/extension setup, migrations, backup/restore docs, and validation must account for PostGIS.
  • Some self-hosters may find PostGIS heavier than plain PostgreSQL, though it is still a boring and widely used extension.
  • Tests need a clear strategy for PostGIS availability rather than relying only on simple in-memory/unit tests.

Boundaries Preserved

  • Reverse lookup labels remain derived/presentation metadata, not authoritative media coordinates.
  • GeoNames remains a replaceable provider candidate, not the domain model.
  • External online providers remain opt-in only.
  • Product features consume Core's provider-neutral lookup service, not database/provider implementation details.

Revisit Triggers

Revisit this decision if:

  • PostGIS meaningfully complicates self-hosted deployment, backup, restore, or upgrades beyond its spatial-query value.
  • Local gazetteer datasets are better handled as reinstallable external artifacts with a dedicated embedded index.
  • Performance or storage of imported gazetteers in PostgreSQL becomes unacceptable for household deployments.
  • A future provider requires spatial semantics that are poorly represented by PostGIS.