b0db22c669
Adds migrations 006/007/008 that wrap tracksolid.v_trips_enriched (introduced upstream in tracksolid_timescale_grafana_prod migration 09) and DROP+CREATE the trips_for_day / trips_for_range RPCs to return the new metadata columns: vehicle_plate, start_address, end_address, driving_time_s, idle_time_s, fuel_consumed_l, and daily_seq. Path/timestamps logic is unchanged — position_history is still scanned to produce the per-vertex timestamps_rel that drive deck.gl TripsLayer animation. route_geom can't replace this since it carries no per-vertex time, but the GIST index on it is available for future area filters. Day filter switches from start_time::date to v.trip_date_eat (the Africa/Nairobi local date already computed by v_trips_enriched), which fixes the latent bug where trips spanning midnight UTC could be misbucketed. Frontend Trip type still receives all original fields in compatible positions; the new fields are additive and ignored by the existing TripsLayer code. Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>
41 lines
1.7 KiB
SQL
41 lines
1.7 KiB
SQL
-- 006_trips_viz_view_v2.sql
|
|
-- Extend trips_viz_v1 with the enrichment columns added in upstream
|
|
-- migration 09 (tracksolid.trips: route_geom, start_address, end_address,
|
|
-- vehicle_plate, waypoints_count) plus webhook-supplied trip metrics from
|
|
-- migration 03 (idle_time_s, driving_time_s, fuel_consumed_l), surfaced via
|
|
-- tracksolid.v_trips_enriched (which adds trip_date_eat + daily_seq).
|
|
--
|
|
-- Columns are appended only — CREATE OR REPLACE VIEW is safe (no column
|
|
-- removed, no type changed). Existing consumers see the same first ten
|
|
-- columns in the same positions.
|
|
|
|
CREATE OR REPLACE VIEW public.trips_viz_v1 AS
|
|
SELECT
|
|
t.id AS trip_id,
|
|
t.imei,
|
|
d.vehicle_name,
|
|
d.vehicle_number,
|
|
COALESCE(NULLIF(TRIM(d.cost_centre), ''), 'Unassigned') AS cost_centre,
|
|
t.start_time,
|
|
t.end_time,
|
|
t.distance_km,
|
|
t.avg_speed_kmh,
|
|
t.max_speed_kmh,
|
|
-- ── Enrichment columns (upstream migration 09) ─────────────────────────
|
|
COALESCE(t.vehicle_plate, d.vehicle_number) AS vehicle_plate,
|
|
t.start_address,
|
|
t.end_address,
|
|
t.waypoints_count,
|
|
-- ── Webhook-supplied trip metrics (upstream migration 03) ──────────────
|
|
t.driving_time_s,
|
|
t.idle_time_s,
|
|
t.fuel_consumed_l,
|
|
-- ── Stable per-day trip ordinal (upstream migration 09) ────────────────
|
|
t.trip_date_eat,
|
|
t.daily_seq
|
|
FROM tracksolid.v_trips_enriched t
|
|
JOIN tracksolid.devices d ON d.imei = t.imei
|
|
WHERE t.start_time IS NOT NULL
|
|
AND t.end_time IS NOT NULL;
|
|
|
|
GRANT SELECT ON public.trips_viz_v1 TO viz_anon;
|