See also: location-db • architecture • schema
A named-point reference table in the same owntracks database as locations, for matching GPS fixes against known places (stations, GWML electrification neutral sections, etc). Added 2026-07-13 to replace the missing /home/john/tmp/uk_stations.tsv that location/speed-plot's plot cell used to depend on (that cell now derives stops from speed data directly instead; this table exists for other/future station-matching use).
CREATE TABLE gazetteer (
id BIGSERIAL PRIMARY KEY,
name TEXT NOT NULL,
lat DOUBLE PRECISION NOT NULL,
lon DOUBLE PRECISION NOT NULL,
geom GEOMETRY(Point, 4326) GENERATED ALWAYS AS
(ST_SetSRID(ST_MakePoint(lon, lat), 4326)) STORED,
category TEXT, -- 'station', 'neutral_section', GeoNames feature class, NaPTAN StopType ...
source TEXT NOT NULL,
notes TEXT,
admin1 TEXT, -- wider location: region/state/constituent country (GeoNames admin1), e.g. 'Wales', 'Alabama'
admin2 TEXT, -- county / unitary authority (GeoNames admin2), e.g. 'Lincolnshire', 'Telford and Wrekin'
country TEXT, -- country name, e.g. 'United Kingdom'
population BIGINT, -- GeoNames population (NULL if unknown); rank same-named places by this
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX gazetteer_geom_idx ON gazetteer USING GIST (geom);
CREATE INDEX gazetteer_source_idx ON gazetteer (source);
CREATE INDEX gazetteer_category_idx ON gazetteer (category);
-- admin1/admin2/country/population added 2026-08-03 (nullable, in-place).geom is a generated column (like a computed field) so loads only need to provide lat/lon — COPY/INSERT should list columns explicitly and omit geom.
Wider-location columns admin1/admin2/country/population (added 2026-08-03) disambiguate same-named places the way humans do — "Newport, Wales" vs "Newport, Telford and Wrekin, England" vs "Newport, Rhode Island, United States". Values are kept exactly as the source provides them (e.g. GeoNames' modern unitary-authority admin2 names, not historic counties). Populated for the geonames-* sources from each row's GeoNames id (in notes as gid=) via GeoNames admin1CodesASCII.txt/admin2Codes.txt/countryInfo.txt. For the GB-only sources, country is set to United Kingdom (naptan, gwml-neutral-sections, uk-railway-stations) since their scope fixes it; uk-railway-stations additionally has admin1 (England/Scotland/Wales) lifted from its own source field. admin2 and population remain geonames-only. Order by population to pick the principal place of a given name.
Distinct values of the source column — i.e. the distinct origins of data in this table. Row counts confirmed live via SELECT source, count(*) FROM gazetteer GROUP BY source on 2026-08-02; re-run that query rather than trusting these numbers if precision matters, since new loads will change them.
| Source | Rows | Category values | Good for |
|---|---|---|---|
| gwml-neutral-sections | 5 | neutral_section | Hand-curated in an earlier session from ~/tmp/gwml-neutral-sections.kml/.gpx. Only source for GWML electrification phase-break locations — used by hibernate-on-approach; nothing else provides this. |
| uk-railway-stations | 2,606 | station | github.com/davwheat/uk-railway-stations (stations.csv: name, lat, long, CRS code, IATA code, country). One row per station (clean, deduplicated), with notes holding "CRS=<code>, country=<n>". Best source for "which railway station is this" / station-to-station matching — use this over naptan's RSE/RLY/RPL rows (below) when you just need the station itself, not its individual entrances/platforms. |
| naptan | 397,896 | 34 StopType codes — see breakdown below | Department for Transport National Public Transport Access Node dataset — every GB bus stop, rail/tram/metro/underground access point, ferry terminal and taxi rank, including request stops with no scheduled timetable. https://beta-naptan.dft.gov.uk/. Best source for "nearest bus/tram stop" (category='BCT', see breakdown) — far more complete for on-street stops than general-purpose places-search tools, which tend to miss untimetabled ones. |
| geonames-GB | 109,177 | P, T, S, A, H, L, R, V, U | GeoNames.org geographical database, GB subset. category holds the GeoNames feature class (P=populated place, T=terrain/hill, S=spot/building/farm, A=administrative, H=hydrographic, L=park/area, R=road/railroad, V=vegetation, U=undersea). https://www.geonames.org/. Best for general place-name lookup (towns, hills, farms, administrative areas) — not transport-specific. |
| geonames-cities5000 | 69,562 | P (populated places) | GeoNames.org global 'cities' extract (populated places with population >= 5,000), all countries. https://download.geonames.org/export/dump/cities5000.zip. Best source for well-known towns/cities worldwide (adds the global coverage the GB-only sets lack). Overlaps geonames-GB for GB places (same GeoNames id under both sources) - filter to one source, or DISTINCT ON the gid in notes, for one row per real place. |
| jPhone | 0 (reserved) | — | Reserved for data John supplies directly (e.g. dictated from his phone). None loaded yet as of 2026-08-02. Do not duplicate entries already in the notes-store gazetteer here until the sync is actually implemented — see using-gps-data ("There are two gazetteers"). |
The single naptan row above covers 27 distinct StopType codes; grouped into families (per DfT NaPTAN schema — confirmed groupings, not every individual code's exact wording) they break down as:
| Family | Codes | Rows | Good for |
|---|---|---|---|
| On-street bus/coach/tram stops | BCT | 380,022 | The dominant category by far. Actual kerbside stop locations — the right family for "nearest bus stop" / walking-directions-to-a-stop questions. Includes untimetabled request stops (e.g. Parkside Avenue, Winterbourne). |
| Rail station access points | RSE, RLY, RPL | 6,677 | Entrances and platform-access points at railway stations — finer-grained than uk-railway-stations (multiple rows per station). Use uk-railway-stations for "which station", this family for "which entrance/platform". |
| Metro/tram/underground | TMU, MET, PLT | 4,136 | Entrance and platform-access points for tram, metro and underground systems (e.g. London Underground, DLR, Tyne & Wear Metro). |
| Bus/coach station (off-street) | BCS, BCE, BCQ, BST | 4,877 | Bays, entrances and stands within bus/coach stations (interchanges) — distinct from the on-street BCT stops above. |
| Ferry | FER, FBT, FTD | 1,134 | Ferry terminal, dock and berth access points. |
| Taxi rank | TXR, STR | 902 | Taxi rank and shared-taxi-rank head points. |
| Air | GAT, AIR | 148 | Airport entrance and airside access points. |
Same pattern as locations: table created/owned by postgres (superuser), then GRANT INSERT, SELECT ON gazetteer TO owntracks and GRANT SELECT ON gazetteer TO owntracks_ro. Note owntracks itself has no schema-level CREATE privilege (PG15 default), so new tables need the superuser step — confirmed via has_database_privilege(current_user, 'owntracks', 'CREATE') returning false for it.
Loaded directly from pomelo over the [postgres] stunnel tunnel (see architecture for the tunnel itself) using the write-capable owntracks role and psycopg2's copy_expert with a local CSV — no scp/ssh file transfer needed, since trust auth on the owntracks database doesn't care which host the tunnel-forwarded connection originates from, only the requested db+role.
-- Nearest gazetteer entries to a point (any source/category)
SELECT name, category, source, notes,
ST_Distance(geom::geography, ST_SetSRID(ST_MakePoint(:lon, :lat), 4326)::geography) AS dist_m
FROM gazetteer
ORDER BY geom <-> ST_SetSRID(ST_MakePoint(:lon, :lat), 4326)
LIMIT 5;