83 lines
2.6 KiB
MySQL
83 lines
2.6 KiB
MySQL
|
-- 007_trips_for_day_v2.sql
|
||
|
|
-- Replaces 003_trips_for_day_rpc.sql to surface the enriched trip columns
|
||
|
|
-- exposed by 006_trips_viz_view_v2.sql.
|
||
|
|
--
|
||
|
|
-- Path/timestamps logic is unchanged: position_history is still scanned per
|
||
|
|
-- trip to build the LineString and per-vertex timestamps_rel needed by
|
||
|
|
-- deck.gl TripsLayer animation. (route_geom alone can't replace this — it
|
||
|
|
-- carries no per-vertex time.) Win is metadata: vehicle_plate, addresses,
|
||
|
|
-- daily_seq, drive/idle/fuel — all returned in one round-trip.
|
||
|
|
--
|
||
|
|
-- DROP + CREATE because the return type signature changes; CREATE OR REPLACE
|
||
|
|
-- FUNCTION cannot alter return columns.
|
||
|
|
|
||
|
|
DROP FUNCTION IF EXISTS public.trips_for_day(date, text[]);
|
||
|
|
|
||
|
|
CREATE FUNCTION public.trips_for_day(
|
||
|
|
p_date date,
|
||
|
|
p_cost_centres text[] DEFAULT NULL
|
||
|
|
) RETURNS TABLE (
|
||
|
|
trip_id bigint,
|
||
|
|
imei text,
|
||
|
|
vehicle_name text,
|
||
|
|
vehicle_number text,
|
||
|
|
cost_centre text,
|
||
|
|
start_time timestamptz,
|
||
|
|
end_time timestamptz,
|
||
|
|
distance_km numeric,
|
||
|
|
vehicle_plate text,
|
||
|
|
start_address text,
|
||
|
|
end_address text,
|
||
|
|
driving_time_s integer,
|
||
|
|
idle_time_s integer,
|
||
|
|
fuel_consumed_l numeric,
|
||
|
|
daily_seq bigint,
|
||
|
|
path_geojson json,
|
||
|
|
timestamps_rel int[]
|
||
|
|
)
|
||
|
|
LANGUAGE sql STABLE
|
||
|
|
SECURITY DEFINER
|
||
|
|
SET search_path = public, tracksolid
|
||
|
|
AS $$
|
||
|
|
WITH day_trips AS (
|
||
|
|
SELECT v.*
|
||
|
|
FROM public.trips_viz_v1 v
|
||
|
|
WHERE v.trip_date_eat = p_date
|
||
|
|
AND (p_cost_centres IS NULL OR v.cost_centre = ANY(p_cost_centres))
|
||
|
|
)
|
||
|
|
SELECT
|
||
|
|
dt.trip_id,
|
||
|
|
dt.imei,
|
||
|
|
dt.vehicle_name,
|
||
|
|
dt.vehicle_number,
|
||
|
|
dt.cost_centre,
|
||
|
|
dt.start_time,
|
||
|
|
dt.end_time,
|
||
|
|
dt.distance_km,
|
||
|
|
dt.vehicle_plate,
|
||
|
|
dt.start_address,
|
||
|
|
dt.end_address,
|
||
|
|
dt.driving_time_s,
|
||
|
|
dt.idle_time_s,
|
||
|
|
dt.fuel_consumed_l,
|
||
|
|
dt.daily_seq,
|
||
|
|
ST_AsGeoJSON(ST_MakeLine(ph.geom ORDER BY ph.gps_time))::json AS path_geojson,
|
||
|
|
array_agg(
|
||
|
|
EXTRACT(EPOCH FROM ph.gps_time - dt.start_time)::int
|
||
|
|
ORDER BY ph.gps_time
|
||
|
|
) AS timestamps_rel
|
||
|
|
FROM day_trips dt
|
||
|
|
JOIN tracksolid.position_history ph
|
||
|
|
ON ph.imei = dt.imei
|
||
|
|
AND ph.gps_time BETWEEN dt.start_time AND dt.end_time
|
||
|
|
AND ph.geom IS NOT NULL
|
||
|
|
GROUP BY dt.trip_id, dt.imei, dt.vehicle_name, dt.vehicle_number,
|
||
|
|
dt.cost_centre, dt.start_time, dt.end_time, dt.distance_km,
|
||
|
|
dt.vehicle_plate, dt.start_address, dt.end_address,
|
||
|
|
dt.driving_time_s, dt.idle_time_s, dt.fuel_consumed_l,
|
||
|
|
dt.daily_seq
|
||
|
|
HAVING count(ph.geom) >= 2;
|
||
|
|
$$;
|
||
|
|
|
||
|
|
GRANT EXECUTE ON FUNCTION public.trips_for_day(date, text[]) TO viz_anon;
|