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

In the definition of the temporary table SegmentTrajs , we suppose that there
is an operation Trajectory (see Table 12.1 ) that takes as argument a temporal
point and returns the line containing all the points traversed by the former.
Then, the query performs a spatial union on all the geometries thus obtained.
Notice that a function ST Union that acts as an aggregate function (e.g., as
COUNT ) is not defined by the OGC, but it is available in PostGIS.
We next present another example of a spatiotemporal OLAP query.
Query 12.5. Number of deliveries that started in Brussels on May 1, 2012.
SELECT COUNT(D.DeliveryKey)
FROM Delivery D, City C
WHERE C.CityName = ' Brussels ' AND
CONVERT(DATE, D.StartDateTime) = ' 2012-05-01 ' AND
ST Intersects(D.StartLocation,C.CityGeom)
Notice that since D.StartDateTime returns a timestamp, the CONVERT
function is applied for obtaining the corresponding day. The reader could
be asking herself/himself that this is actually not a spatiotemporal query.
This is, however, because the query takes advantage of the fact that the start
time and the start location of trajectories are precomputed in the Delivery
dimension. If this were not the case, the query would read
WITH DeliveryFull AS (
SELECT D. DeliveryKey, InitialValue(S.Route) AS StartLocation,
InitialInstant(S.Route) AS StartDateTime
FROM Delivery D, Segment S
WHERE D.DeliveryKey = S.DeliveryKey AND NOT EXISTS (
SELECT *
FROM Segment S1
S1.DeliveryKey = D.DeliveryKey AND
InitialInstant(S1.Route) < InitialInstant(S.Route) ) )
SELECT COUNT(D.DeliveryKey)
FROM DeliveryFull D, City C
WHERE C.CityName = ' Brussels ' AND
CONVERT(DATE, D.StartDateTime) = ' 2012-05-01 ' AND
ST Intersects(D.StartLocation,C.CityGeom)
In the definition of the temporary table DeliveryFull , the functions InitialValue
and InitialValue return, respectively, the starting point and the starting
instant of the moving point geometry S.Route . The inner query of the
temporary table definition ensures that segment S is the first segment of
a delivery by verifying that its start time is the smallest among all those of
the segments composing the delivery. This is done with the help of function
InitialInstant . Finally, the query counts the deliveries that started in the city
of Brussels on May 1, 2012. Since attribute StartDateTime is a timestamp,
the CONVERT function is applied for obtaining the corresponding day.
Our next example query involves the LandUse field.