25 lines
1.7 KiB
MySQL
25 lines
1.7 KiB
MySQL
|
-- 04_inc_latlng.sql — fleettickets · populate lat/lng from the resolved geom
|
||
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
||
|
|
-- The source feed never carries coordinates (latitude/longitude are always empty),
|
||
|
|
-- but geocoding resolves a position into `geom` (feed -> location -> cluster). This
|
||
|
|
-- redefines the latitude/longitude generated columns to fall back to that geom when
|
||
|
|
-- the feed is empty, so they are actually populated for every geocoded ticket.
|
||
|
|
--
|
||
|
|
-- Precision still varies by geo_source ('location' = street-level, 'cluster' =
|
||
|
|
-- cluster centroid). geom is a regular column (set by the geom trigger / resolve),
|
||
|
|
-- which a generated column may reference; ST_X/ST_Y are IMMUTABLE. STORED, so it
|
||
|
|
-- recomputes whenever geom changes (e.g. after a geocode pass). Idempotent.
|
||
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
||
|
|
|
||
|
|
SET search_path = tickets, public;
|
||
|
|
|
||
|
|
-- redefine (drop + re-add) the two generated columns added in migration 03
|
||
|
|
ALTER TABLE tickets.inc DROP COLUMN IF EXISTS latitude;
|
||
|
|
ALTER TABLE tickets.inc DROP COLUMN IF EXISTS longitude;
|
||
|
|
|
||
|
|
ALTER TABLE tickets.inc
|
||
|
|
ADD COLUMN IF NOT EXISTS latitude double precision GENERATED ALWAYS AS
|
||
|
|
(COALESCE(NULLIF(raw->>'latitude','')::double precision, ST_Y(geom))) STORED,
|
||
|
|
ADD COLUMN IF NOT EXISTS longitude double precision GENERATED ALWAYS AS
|
||
|
|
(COALESCE(NULLIF(raw->>'longitude','')::double precision, ST_X(geom))) STORED;
|