Untitled
---------------------------------------Mobility Databases-------------------------------------- -- a. Give the first time when each vehicle visited a point of interest in table Points. SELECT v.vehId, p.pointId MIN(startTimestamp(atGeometry(t.trip, p.geom))) AS first_visit_time FROM Vehicles v, Trips t, Points p -- OR CROSS JOIN Points p JOIN Trips t ON v.vehId = t.vehId WHERE atGeometry(t.trip, p.geom) IS NOT NULL GROUP BY v.vehId, p.pointId ORDER BY v.vehId, p.pointId -- b. Give the minimal distance between each pair of vehicles whenever their time intervals overlap. SELECT v1.vehId AS Vehicle1 v2.vehId AS Vehicle1 (t1.trip |=| t2.trip) AS minimal_distance_m FROM Vehicle v1, Vehicle v2, Trips t1, Trips t2 WHERE t1.vehId < t2.vehId AND v1.vehId = t1.vehId AND v2.vehId = t2.vehId WHERE timeSpan(t1.trip) * timeSpan(t2.trip) IS NOT NULL -- time intervals overlap GROUP BY Vehicle1, Vehicle2 ORDER BY Vehicle1, Vehicle2; -- c. Give for each vehicle the duration of the trip of maximum length over all of its trips. -- The result should show three columns: vehId, duration of the trip, trajectory of the trip. -- If a vehicle has multiple trips with the same maximum length, it must appear multiple times -- in the result set (one for each duration time). WITH trip_stats AS( SELECT t.tripId, t.vehId, ST_Lenght(trajectory(t.trip)) AS geom_lenght, duration(timeSpan(t.trip)) AS trip_duration, trajectory(t.trip) AS trip_trajectory FROM Trips t ), max_lenght AS( SELECT vehId, MAX(geom_lenght) AS max_lenght FROM trip_stats GROUP BY vehId ) SELECT ts.vehId ts.trip_duration ts.trip_trajectory FROM trip_stats ts, max_lenght ml WHERE ts.vehId = ml.vehId AND ts.geom_lenght = ml.max_lenght ORDER BY ts.vehId; -- d. For every pair of adjacent communes, give the trips that cross each of them. -- You should only show the trips restricted by these boundaries and the time when -- the intersection occurred. WITH adjacent_communes AS( SELECT c1.communeId AS c1id, c2.communeId AS c2id, c1.geom AS c1geom, c2.geom AS c2geom FROM Communes c1, Communes c2 WHERE c1.communeId < c2.communeId AND ST_Touches(c1.geom, c2.geom) ), trips_in_communes AS ( SELECT t.tripId, t.vehId, atGeometry(t.trip, ac.c1geom) AS portion_in_c1, atGeometry(t.trip, ac.c2geom) AS portion_in_c2 FROM Trips t, adjacent_communes ac WHERE eIntersects(t.trip, ac.c1geom) IS NOT NULL AND eIntersects(t.trip, ac.c2geom) IS NOT NULL ) SELECT t.vehId, t.tripId, t.c1id, t.c2id, -- Partial trajectory in each commune trajectory(t.portion_in_c1) AS traj_in_c1, trajectory(t.portion_in_c2) AS traj_in_c2, -- Times when the vehicle is in each commune (start, end) startTimestamp(portion_in_c1) AS enter_c1, endTimestamp(portion_in_c1) AS exit_c1, startTimestamp(portion_in_c2) AS enter_c2, endTimestamp(portion_in_c2) AS exit_c2 FROM trips_in_communes t WHERE t.portion_in_c1 IS NOT NULL AND t.portion_in_c2 IS NOT NULL ORDER BY t.vehId, t.c1id, t.c2id;
Leave a Comment