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

average is computed as in the previous query. Note that this solution would
be used if the information of the deliveries is not precomputed in the Delivery
dimension table, that is, if the attribute TotalDuration is not present in such
table.

We consider now the spatial data types and their associated operations,
which we studied in Chap. 11 . These kinds of queries are denoted SOLAP
queries. For example, the predicate ST Intersects can be used to test whether
two geometries intersect.

Query 12.3. Number of deliveries in the last quarter of 2012, for each road
that intersects Brussels.
SELECT RoadKey, COUNT(DISTINCT DeliveryKey) AS NoDeliveries

FROM Road R, Delivery D

WHERE EXISTS (

SELECT *
FROM City C
WHERE C.CityName = ' Brussels ' AND
ST Intersects(R.RoadGeom,C.CityGeom) ) AND
EXISTS (
SELECT *
FROM Segment S, Time T
WHERE S.RoadKey = R.RoadKey AND
S.DeliveryKey = D.DeliveryKey AND
S.TimeKey = T.TimeKey AND T.Quarter = ' Q4 2012 ' )

GROUP BY RoadKey
The first inner query selects the roads that intersect the city of Brussels
using the ST Intersects predicate, which determines if a pair of geometries
intersect. The second inner query selects the deliveries that have a segment
that occurred on the road in the last quarter of 2012. Then, the outer query
groups for each road all the selected deliveries and then counts the number
of distinct ones.

Spatiotemporal OLAP accounts for the case when the spatial objects
evolve over time, that is, they involve temporal spatial types as introduced
above. As an example, the following query includes the Route measure, that
is, the movement track of a segment.

Query 12.4. For each road, give the geometry of the segments of the road
on which at least one delivery passed on May 1, 2012.

WITH SegmentTrajs AS (
SELECT S.SegmentKey, Trajectory(S.Route) AS Trajectory
FROM Segment S )
SELECT R.RoadKey, ST Union(S.Trajectory)
FROM Road R, SegmentTraj S, Time T
WHERE R.RoadKey = R.RoadKey AND S.TimeKey = T.TimeKey AND
T.Date = ' 2012-05-01 ' )
GROUP BY R.RoadKey