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

Query 12.6. Average duration of the deliveries that started in a residential
area and ended in an industrial area on February 1, 2012.
SELECT D.TotalDuration
FROM Delivery D, Location L1, Location L2, City C1, City C2,
State S1, State S2
WHERE CONVERT(DATE,D.StartDateTime) = ' 2012-02-01 ' AND
CONVERT(DATE,D.EndDateTime) = ' 2012-02-01 ' AND
D.StartLocation = L1.LocationGeom AND
D.EndLocation = L2.LocationGeom AND
L1.CityKey = C1.CityKey AND L2.CityKey = C2.CityKey AND
C1.StateKey = S1.StateKey AND C2.StateKey = S2.StateKey AND
ST Intersects(D.StartLocation,At(S1.LandUse, ' Residential ' )) AND
ST Intersects(D.EndLocation,At(S2.LandUse, ' Industrial ' ))
Since it is supposed that attributes StartDateTime and EndDateTime are
of type timestamp, the function CONVERT is used for obtaining the
corresponding dates. Then, the query selects the members L1 and L2 of the
Location level corresponding to the start and end locations of the delivery,
and the subsequent joins obtain the correspond states. Further, the function
At (see Table 12.1 ) projects the land use fields of the corresponding states to
the values of type residential or industrial. Finally, the function ST Intersects
ensures that the start and end locations of the delivery are included in the
filtered rasters. Notice that, as is the case in PostGIS, the ST Intersects
predicate can compute not only if two geometries intersect but also if a
geometry and a raster intersect.
The above query involved the (partitioned) land use field in level State .
We could have used, instead, the global LandUse field in Fig. 12.8 .Inthis
case, the query would be written as follows:
SELECT D.TotalDuration
FROM Delivery D, LandUse L
WHERE CONVERT(DATE,D.StartDateTime) = ' 2012-02-01 ' AND
CONVERT(DATE,D.EndDateTime) = ' 2012-02-01 ' AND
ST Intersects(D.StartLocation,At(L, ' Residential ' )) AND
ST Intersects(D.EndLocation,At(L, ' Industrial ' ))
In this case, we can see that it is more ecient to use the global field rather
than the partitioned field. However, there are cases that working the other
way round is more ecient.
Note that the query above does not involve temporal data since it does
not mention a temporal geometry such as measure Route nor a temporal field
such as Temperature . The next query involves both temporal attributes.
Query 12.7. Average speed and maximum temperature during the seg-
ment, for trajectory segments that occurred on February 1, 2012.
SELECT S.SegmentKey, S.AvgSpeed, TMax(AtMGeometry(E,S.Route))
FROM SegmentS,TimeT,TemperatureE
WHERE S.TimeKey = T.TimeKey AND T.Date = ' 2012-02-01 '