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;