Location Database — Usage

See also: location-db • schema • architecture

MCP tool: pg_query

Available on the misc MCP connector (https://www.critchley.biz/misc/mcp). Accepts any SELECT statement against the owntracks database. Only SELECT is permitted. Returns up to 500 rows as JSON.

If the tool returns an error mentioning "Please try again", the pool connection was stale — simply retry the query and it will succeed.

PostGIS quick reference

Always cast geom to ::geography for metre-accurate distance. Use ST_AsGeoJSON(geom) to return geometry as readable GeoJSON.

Example queries

Nearest approach to a point (e.g. Bristol Temple Meads, lat=51.449, lon=-2.581):

SELECT to_timestamp(tst) AT TIME ZONE 'Europe/London' AS t,
       lat, lon,
       round(ST_Distance(
           geom::geography,
           ST_SetSRID(ST_MakePoint(-2.581, 51.449), 4326)::geography
       )::numeric, 0) AS dist_m,
       conn, ssid
FROM locations
ORDER BY geom <-> ST_SetSRID(ST_MakePoint(-2.581, 51.449), 4326)
LIMIT 10;

All fixes within 500m of a point:

SELECT to_timestamp(tst) AT TIME ZONE 'Europe/London' AS t,
       lat, lon, acc, conn, ssid
FROM locations
WHERE ST_DWithin(
    geom::geography,
    ST_SetSRID(ST_MakePoint(-2.581, 51.449), 4326)::geography,
    500
)
ORDER BY tst;

Speed between two timestamps (haversine via PostGIS):

WITH ordered AS (
  SELECT tst, geom,
         lag(tst)  OVER (ORDER BY tst) AS prev_tst,
         lag(geom) OVER (ORDER BY tst) AS prev_geom
  FROM locations
  WHERE tst BETWEEN 1783900000 AND 1783950000
)
SELECT to_timestamp(tst) AT TIME ZONE 'Europe/London' AS t,
       round((ST_Distance(geom::geography, prev_geom::geography)
              / (tst - prev_tst) * 2.23694)::numeric, 1) AS speed_mph
FROM ordered
WHERE prev_tst IS NOT NULL AND (tst - prev_tst) BETWEEN 1 AND 300
ORDER BY tst;

WiFi-only fixes in a time window:

SELECT to_timestamp(tst) AT TIME ZONE 'Europe/London' AS t,
       ssid, ST_AsGeoJSON(geom) AS geojson, batt
FROM locations
WHERE conn = 'w'
  AND tst > extract(epoch FROM now() - interval '24 hours')
ORDER BY tst;

Today's fixes with geometry as GeoJSON:

SELECT to_timestamp(tst) AT TIME ZONE 'Europe/London' AS t,
       ST_AsGeoJSON(geom)::json AS geojson,
       acc, conn, ssid, batt
FROM locations
WHERE tst >= extract(epoch FROM current_date AT TIME ZONE 'Europe/London')
ORDER BY tst;

When asked a location-dependent question ("where am I", "nearest station", "how far to X"), always re-query SELECT ... ORDER BY tst DESC LIMIT 1 fresh — even if a position was already fetched earlier in the same conversation. The phone keeps reporting fixes in the background, so an earlier answer goes stale within minutes, especially in a moving vehicle. Caught in practice on 15 July 2026: answered "nearest station" using a fix from ~30 min prior instead of re-checking, giving a wrong answer (Radyr instead of Pencoed after the car had moved ~15 miles further west).

Agent best practice: don't reuse a cached fix

version 1