List the vehicles that have passed at a region from Regions.

SELECT DISTINCT r.RegionId, t.VehicleId
FROM Trips t, Regions r
WHERE ST_Intersects(trajectory(t.Trip), r.Geom)
ORDER BY r.RegionId, t.VehicleId;

This is a spatial range query. The query verifies that the trajectory of the vehicle intersects the region. PostGIS performs an implicit bounding box comparison trajectory(t.Trip) && r.Geom using the spatial index on table Regions when executing the predicate ST_Intersects.

List the vehicles that were within a region from Regions during a period from Periods.

SELECT r.RegionId, p.PeriodId, t.VehicleId
FROM Trips t, Regions r, Periods p
WHERE t.Trip && stbox(r.Geom, p.Period) AND 
  eIntersects(atTime(t.Trip, p.Period), r.Geom)
ORDER BY r.RegionId, p.PeriodId, t.VehicleId;

This is a spatio-temporal range query. The query performs a bounding box comparison with the && operator using the spatio-temporal index on table Trips. After that, the query verifies that the location of the vehicle during the period intersects the region. Notice the predicate eIntersects (ever intersects) that is applied to the arguments atTime(Trip, p.Period) and r.Geom.

List the pairs of vehicles that were both located within a region from Regions during a period from Periods.

SELECT DISTINCT t1.VehicleId AS VehId1, t2.VehicleId AS VehId2, r.RegionId, p.PeriodId
FROM Trips t1, Trips t2, Regions r, Periods p
WHERE t1.VehicleId < t2.VehicleId AND t1.Trip && stbox(r.Geom, p.Period) AND
  t2.Trip && stbox(r.Geom, p.Period) AND 
  eIntersects(atTime(t1.Trip, p.Period), r.Geom) AND
  eIntersects(atTime(t2.Trip, p.Period), r.Geom)
ORDER BY t1.VehicleId, t2.VehicleId, r.RegionId, p.PeriodId;

This is a spatio-temporal range join query. The query selects two trips of different vehicles and performs bounding box comparisons of each trip with a region and a period using the spatio-temporal index of the Trips table. The query then verifies that both vehicles were located within the region during the period.

List the first time at which a vehicle visited a point in Points.

SELECT t.VehicleId, p.PointId, MIN(startTimestamp(atValues(t.Trip,p.Geom))) AS Instant
FROM Trips t, Points p
WHERE ST_Contains(trajectory(t.Trip), p.Geom)
GROUP BY t.VehicleId, p.PointId;

The query selects a trip and a point and verifies that the vehicle passed by the point by testing that the trajectory of the trip contains the point. Notice that PostGIS will perform the bounding box containment trajectory(t.Trip) ~ p.Geom using the spatial index on table Points before executing ST_Contains. Then, the query projects the trip to the point with the atValue function, get the first timestamp of the projected trip with the startTimestamp function, and applies the traditional MIN aggregate function for all trips of the vehicle and the point.

Compute how many vehicles were active at each period in Periods.

SELECT p.PeriodId, COUNT(*), tCount(atTime(t.Trip, p.Period))
FROM Trips t, Periods p
WHERE t.Trip && p.Period
GROUP BY p.PeriodId
ORDER BY p.PeriodId;

This an instant temporal aggregate query. For each period, the query projects the trips to the given period and applies the temporal count to the projected trips. The condition in the WHERE clause is used for filtering the trips with the spatio-temporal index on table Trips.

For each region in Regions, give the window temporal count of trips with a 10-minute interval.

SELECT r.RegionId, wCount(atGeometry(t.Trip, r.Geom), interval '10 min')
FROM Trips t, Regions r
WHERE t.Trip && stbox(r.Geom)
GROUP BY r.RegionId
HAVING wCount(atGeometry(t.Trip, r.Geom), interval '10 min') IS NOT NULL
ORDER BY r.RegionId;

This is a window temporal aggregate query. Suppose that we are computing pollution levels by region. Since the effect of a vehicle passing at a location lasts some time interval, this is a typical case for window aggregates. For each region, the query computes the spatial projection of the trips to the given region and apply the window temporal count to the projected trips. The condition in the WHERE clause is used for filtering the trips with the spatio-temporal index. The condition in the HAVING clause is used for removing regions that do not intersect with any trip.

Count the number of trips that were active during each hour in May 29, 2007.

WITH TimeSplit(Period) AS (
  SELECT span(H, H + interval '1 hour')
  FROM generate_series(timestamptz '2007-05-29 00:00:00', 
    timestamptz '2007-05-29 23:00:00', interval '1 hour') AS H )
SELECT Period, COUNT(*)
FROM TimeSplit s, Trips t
WHERE s.Period && t.Trip AND atTime(Trip, Period) IS NOT NULL
GROUP BY s.Period
ORDER BY s.Period;

This is a span temporal aggregate query. The query defines the intervals to consider in the TimeSplit temporary table. For each of these intervals, the main query applies the traditional count function for counting the trips that overlap the interval.

List the overall traveled distances of the vehicles during the periods from Periods.

SELECT t.VehicleId, p.PeriodId, p.Period,
  SUM(length(atTime(t.Trip, p.Period))) AS Distance
FROM Trips t, Periods p
WHERE t.Trip && p.Period
GROUP BY t.VehicleId, p.PeriodId, p.Period
ORDER BY t.VehicleId, p.PeriodId;

The query performs a bounding box comparison with the && operator using the spatio-temporal index on the Trips table. It then projects the trip to the period, computes the length of the projected trip, and sum the lengths of all the trips of the same vehicle during the period.

List the minimum distance ever between each vehicle and each point from Points.

SELECT t.VehicleId, p.PointId, MIN(trajectory(t.Trip) <-> p.Geom) AS MinDistance
FROM Trips t, Points p
GROUP BY t.VehicleId, p.PointId
ORDER BY t.VehicleId, p.PointId;

The query projects the trip to the spatial dimension with the trajectory function and computes the traditional distance between the trajectory of the trip and the point. The traditional minimum function is then applied for computing the minimum distance between all trips of the vehicle and the point.

List the minimum temporal distance between each pair of vehicles.

SELECT t1.VehicleId AS Veh1Id, t2.VehicleId AS Veh2Id,
  tMin(t1.Trip <-> t2.Trip) AS MinDistance
FROM Trips t1, Trips t2
WHERE t1.VehicleId < t2.VehicleId AND timeSpan(t1.Trip) && timeSpan(t2.Trip)
GROUP BY t1.VehicleId, t2.VehicleId
ORDER BY t1.VehicleId, t2.VehicleId;

The query selects two trips t1 and t2 from different vehicles that were both traveling during a common period of time, computes the temporal distance between the trips, and then computes the temporal minimum distance between all trips of the two vehicles. The query uses the spatio-temporal index to filter the pairs of trips that were both traveling during a common period of time.

List the nearest approach time, distance, and shortest line between each pair of trips.

SELECT t1.VehicleId AS Veh1Id, t1.TripId AS Trip1Id, t2.VehicleId AS Veh2Id, 
  t2.TripId AS Trip2Id, timeSpan(nearestApproachInstant(t1.Trip, t2.Trip)) AS Time,
  nearestApproachDistance(t1.Trip, t2.Trip) AS Distance, 
  shortestLine(t1.Trip, t2.Trip) AS Line
FROM Trips t1, Trips t2
WHERE t1.VehicleId < t2.VehicleId AND timeSpan(t1.Trip) && timeSpan(t2.Trip)
ORDER BY t1.VehicleId, t1.TripId, t2.VehicleId, t2.TripId;

This query shows similar functionality as that provided by the PostGIS functions ST_ClosestPointOfApproach and ST_DistanceCPA. The query selects two trips t1 and t2 from different vehicles that were both traveling during a common period of time and computes the required results.

List when and where a pairs of vehicles have been at 10 m or less from each other.

SELECT t1.VehicleId AS VehId1, t2.VehicleId AS VehId2, atTime(t1.Trip,
  getTime(tDwithin(t1.Trip, t2.Trip, 10.0, TRUE))) AS Position
FROM Trips t1, Trips t2
WHERE t1.VehicleId < t2.VehicleId AND t1.Trip && expandSpace(t2.Trip, 10) AND
  tDwithin(t1.Trip, t2.Trip, 10.0, TRUE) IS NOT NULL
ORDER BY t1.VehicleId, t2.VehicleId, Position;

The query performs for each pair of trips t1 and t2 of different vehicles a bounding box comparison with the && operator using the spatio-temporal index on the Trips table, where the bounding box of t2 is expanded by 10 m. Then, the period expression computes the periods during which the vehicles were within 10 m. from each other and the atTime function projects the trips to those periods. Notice that the expression tDwithin(t1.Trip, t2.Trip, 10.0) is conceptually equivalent to dwithin(t1.Trip, t2.Trip) #<= 10.0. However, in this case the spatio-temporal index cannot be used for filtering values.

For each trip from Trips, list the three points from Points that have been closest to that vehicle.

WITH TripsTraj AS (
  SELECT TripId, VehicleId, trajectory(Trip) AS Trajectory FROM Trips )
SELECT t.VehicleId, ps1.PointId, ps1.Distance
FROM TripsTraj t CROSS JOIN LATERAL (
  SELECT p.PointId, t.Trajectory <-> p.Geom AS Distance
  FROM Points p
  ORDER BY Distance LIMIT 3 ) AS ps1
ORDER BY t.TripId, t.VehicleId, ps1.Distance;

This is a nearest-neighbor query with moving reference objects and static candidate objects. The query above uses PostgreSQL's lateral join, which intuitively iterates over each row in a result set and evaluates a subquery using that row as a parameter. The query starts by computing the trajectory of the trips in the temporary table TripsTraj. Then, given a trip t in the outer query, the subquery computes the traditional distance between the trajectory of t and each point p. The ORDER BY and LIMIT clauses in the inner query select the three closest points. PostGIS will use the spatial index on the Points table for selecting the three closest points.

For each trip from Trips, list the three vehicles that are closest to that vehicle

SELECT t1.VehicleId AS VehId1, v2.VehicleId AS VehId2, v2.Distance
FROM Trips t1 CROSS JOIN LATERAL (
  SELECT t2.VehicleId, minValue(t1.Trip <-> t2.Trip) AS Distance
  FROM Trips t2
  WHERE t1.VehicleId < t2.VehicleId AND timeSpan(t1.Trip) && timeSpan(t2.Trip)
  ORDER BY Distance LIMIT 3 ) AS v2
ORDER BY t1.VehicleId, v2.VehicleId;

This is a nearest-neighbor query where both the reference and the candidate objects are moving. Therefore, it is not possible to proceed as in the previous query to first project the moving points to the spatial dimension and then compute the traditional distance. Given a trip t1 in the outer query, the subquery computes the temporal distance between t1 and a trip t2 of another vehicle different from the vehicle from t1 and then computes the minimum value in the temporal distance. Finally, the ORDER BY and LIMIT clauses in the inner query select the three closest vehicles.

For each trip from Trips, list the points from Points that have that vehicle among their three nearest neighbors.

WITH TripsTraj AS (
  SELECT TripId, VehicleId, trajectory(Trip) AS Trajectory FROM Trips ),
PointTrips AS (
  SELECT p.PointId, t2.VehicleId, t2.TripId, t2.Distance
  FROM Points p CROSS JOIN LATERAL (
    SELECT t1.VehicleId, t1.TripId, p.Geom <-> t1.Trajectory AS Distance
    FROM TripsTraj t1
    ORDER BY Distance LIMIT 3 ) AS t2 )
SELECT t.VehicleId, t.TripId, p.PointId, pt.Distance
FROM Trips t CROSS JOIN Points p JOIN PointTrips pt
  ON t.VehicleId = pt.VehicleId AND t.TripId = pt.TripId AND p.PointId = pt.PointId
ORDER BY t.VehicleId, t.TripId, p.PointId;

This is a reverse nearest-neighbor query with moving reference objects and static candidate objects. The query starts by computing the corresponding nearest-neighbor query in the temporary table PointTrips as it is done in Query 13. Then, in the main query it verifies for each trip t and point p that both belong to the PointTrips table.

For each trip from Trips, list the vehicles having the vehicle of the trip among the three nearest neighbors.

WITH TripDistances AS (
  SELECT t1.VehicleId AS VehId1, t1.TripId AS TripId1, t3.VehicleId AS VehId2, 
    t3.TripId AS TripId2, t3.Distance
  FROM Trips t1 CROSS JOIN LATERAL (
    SELECT t2.VehicleId, t2.TripId, minValue(t1.Trip <-> t2.Trip) AS Distance
    FROM Trips t2
    WHERE t1.VehicleId < t2.VehicleId AND timeSpan(t1.Trip) && timeSpan(t2.Trip)
    ORDER BY Distance LIMIT 3 ) AS t3 )
SELECT t1.VehicleId, t1.TripId, t2.VehicleId, t2.TripId, td.Distance
FROM Trips t1 JOIN Trips t2 ON t1.VehicleId < t2.VehicleId
  JOIN TripDistances td ON t1.VehicleId = td.VehId1 AND t1.TripId = td.TripId1 AND
  t2.VehicleId = td.VehId2 AND t2.TripId = td.TripId2
ORDER BY t1.VehicleId, t1.TripId, t2.VehicleId, t2.TripId;

This is a reverse nearest-neighbor query where both the reference and the candidate objects are moving. The query starts by computing the corresponding nearest-neighbor query in the temporary table TripDistances as it is done in Query 14. Then, in the main query it verifies for each pair of trips t1 and t2 that both belong to the TripDistances table.

For each group of ten disjoint vehicles, list the point(s) from Points, having the minimum aggregated distance from the given group of ten vehicles during the given period.

WITH Groups AS (
  SELECT ((ROW_NUMBER() OVER (ORDER BY v.VehicleId))-1)/10 + 1 AS GroupId, v.VehicleId
  FROM Vehicles v ),
SumDistances AS (
  SELECT g.GroupId, p.PointId,
    SUM(ST_Distance(trajectory(t.Trip), p.Geom)) AS SumDist
  FROM Groups g, Points p, Trips t
  WHERE t.VehicleId = g.VehicleId
  GROUP BY g.GroupId, p.PointId )
SELECT s1.GroupId, s1.PointId, s1.SumDist
FROM SumDistances s1
WHERE s1.SumDist <= ALL (
  SELECT SumDist
  FROM SumDistances s2
  WHERE s1.GroupId = s2.GroupId )
ORDER BY s1.GroupId, s1.PointId;

This is an aggregate nearest-neighbor query. The temporary table Groups splits the vehicles in groups where the GroupId column takes the values from 1 to total number of groups. The temporary table SumDistances computes for each group g and point p the sum of the distances between a trip of a vehicle in the group and the point. The main query then selects for each group in table SumDistances the points(s) that have the minimum aggregated distance.