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

Query 12.11. For each delivery, give the total time when it has driven on
rainy conditions at more than 70km/h.
SELECT D.DeliveryNumber, SUM(Duration(DefTime(AtPeriod(
AtMGeometry(At(T.Precip,Range(1,100),S.Route)),
DefTime(At(Speed(S.Route),Range(70,150)))))))
FROM Delivery D, Segment S, State T
WHERE S.DeliveryKey = D.DeliveryKey AND
ST Intersects(T.StateGeom,Trajectory(S.Route))
GROUP BY D.DeliveryNumber
For each delivery, the composing segments and the states traversed during
the segment are collected. 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 moving point with function
AtMGeometry , and then projected to the period of time in which the speed
of the trajectory was between 70 and 150km/h with function AtPeriod .The
function DefTime computes the time period during which the route satisfies
the conditions and then the duration of this period is computed. Finally, the
SUM operation is used to add up the durations of all the obtained periods.
12.6 Summary
We have discussed data warehousing techniques that applied to trajectory
data help to improve the decision-making process. For this, we first defined
temporal types, which capture the variation of a value across time. Applying
temporal types to spatial data leads to the notion of temporal spatial types,
which provide a conceptual view of trajectories. Finally, applying temporal
types to field data types produces spatiotemporal field data types, which
model temporal continuous fields. At the logical level, we studied how these
conceptual data types can be implemented in PostGIS. We presented a
concrete case extending the Northwind data warehouse with trajectory data
and show how to query this data warehouse using PostGIS extended with
temporal types of different kinds.
12.7 Bibliographic Notes
An overall perspective of the current state of the art in trajectory manage-
mentcanbefoundin the topics[ 173 , 240 ]. A state of the art in spatiotemporal
data warehousing, OLAP, and mining can be found in [ 70 ]. This chapter is
based on previous research work on spatiotemporal data warehousing and
continuous fields performed by the authors [ 212 , 213 ].