19 lines
637 B
MySQL
19 lines
637 B
MySQL
|
-- migrate:up
|
||
|
|
|
||
|
|
-- Cache layer for reverse geocoding. Keyed by lat/lng rounded to 4 decimals
|
||
|
|
-- (~10m grid), so parked vehicles share a row and only moving vehicles trigger
|
||
|
|
-- fresh lookups. Filled asynchronously by the geocoder cron job (PRD F2.3).
|
||
|
|
CREATE TABLE state.geocoded_positions (
|
||
|
|
lat_rounded numeric(8,4) NOT NULL,
|
||
|
|
lng_rounded numeric(8,4) NOT NULL,
|
||
|
|
address text,
|
||
|
|
address_short text,
|
||
|
|
source text NOT NULL DEFAULT 'nominatim',
|
||
|
|
fetched_at timestamptz NOT NULL DEFAULT now(),
|
||
|
|
PRIMARY KEY (lat_rounded, lng_rounded)
|
||
|
|
);
|
||
|
|
|
||
|
|
-- migrate:down
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS state.geocoded_positions;
|