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:
- enable/document the PostGIS extension in the database deployment and restore path;
- import a tiny fixture gazetteer into provider-neutral tables using geometry/geography columns and spatial indexes;
- implement lookup by coordinate, centroid plus radius, and/or spatial envelope;
- return normalized candidates with provider/dataset/version/attribution provenance;
- 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.
Memories Wiki
Using Memories
- Accounts, the Library Hub, and Data Export
- Albums and Automatic Suggestions
- Explore and Discovery Domains
- The Media Viewer
- People and Faces
- Places and Map
- Plants (In Development)
- Datenschutzerklärung (Privacy Policy)
- Fast, Progressive Image Loading
- Search
- Sharing and Households
- Timeline and Photos
- Uploading and Import
Self-Hosting
- Backup and Restore
- CLI Password Reset
- Deployment Architecture
- Known Issues and Operator Diagnostics
- Map Tiles and Place Data Privacy
- Storage Layout
- Upgrades and Versioning
- Workers
Reference