Trajectory Data Warehouses - Data Warehouse Systems: Design and Implementation - page 491

The next query combines a field and a trajectory.
Query 12.9. Deliveries that have driven along more than 50km of roads at
more than 1,000m of altitude.
SELECT D.DeliveryNumber
FROM Delivery D
WHERE ( SELECT SUM(ST Length(DefSpace(AtGeometry(
At(T.Elevation,Range(1000,6000)), Trajectory(S.Route)))))
FROM Segment S, State T
WHERE S.DeliveryKey = D.DeliveryKey AND
ST Intersects(T.StateGeom,Trajectory(S.Route)) ) > 50
For each delivery, the inner query collects the composing segments and the
states traversed during the segment. This is done by verifying in the WHERE
clause that the geometry of the state and the trajectory of the route intersect.
Then, for each couple of segment and state, the elevation field of the state
is projected to the range of values between 1,000 and 6,000 (it is supposed
that the latter is the maximal value) with function At and then projected to
the trajectory of the route with function AtGeometry . The part of the route
at more than 1,000m is obtained by function DefSpace , and then the length
of this route is computed. The SUM aggregation operation is then used to
compute the sum of the lengths of all the obtained routes and finally the
outer query verifies that this sum is greater than 50.
The two next queries combine a temporal field and a trajectory.
Query 12.10. Deliveries that have driven along more 50km on rainy
conditions during July 2013 in Belgium.
SELECT D.DeliveryNumber
FROM Delivery D
WHERE ( SELECT SUM(ST Length(DefSpace(AtGeometry(
AtPeriod(At(T.Precip,Range(1,100)),
PERIOD( ' 2013-07-01 ' , ' 2013-08-01 ' )), Trajectory(S.Route)))))
FROM Segment S, State T, Country C
WHERE S.DeliveryKey = D.DeliveryKey AND
T.CountryKey = C.CountryKey AND
C.CountryName = ' Belgium '
ST Intersects(T.StateGeom,Trajectory(S.Route)) ) > 50
In the above query, it is supposed that rainy conditions mean between 1mm
(moderate rain) and 100mm (extreme rain) per hour. For each delivery,
the inner query collects the composing segments and the states in Belgium
traversed during the segment. Then, for each couple of segment and state,
the precipitation field of the state is projected to the range of values between
1 and 100 with function At , then projected to the month of July 2013
with function AtPeriod , and then projected to the trajectory of the route
with function AtGeometry . The part of the route satisfying the conditions is
obtained by function DefSpace , and then the length of this route is computed.
The SUM operation is then used to add up the lengths of all the obtained
routes, and finally the outer query verifies that this sum is greater than 50.