Spatial Data Warehouses - Data Warehouse Systems: Design and Implementation - page 450

In the calculated measure CustomerLocations , the function FILTER is used
for selecting the customers of the current supplier. Then, the ST UnionAgg
function is used for aggregating into a single geometry all the locations of
the selected customers. In the calculated measure CentroidCustomers ,the
function ST Centroid function is used to compute the centroid of the locations
of the customers of the current supplier. Finally, in the calculated measure
DistanceCentroid , the distance between the location of the supplier and the
centroid of all its customers is computed.
11.7 Querying the GeoNorthwind Data Warehouse
in SQL
Analogously to what we did in Chap. 6 , we show that MDX queries can also be
expressed in SQL. Given that in this topic we have covered SQL extensively,
we do not comment on the queries, which are straightforward.
Query 11.1. Total sales in 1997 to customers located in cities that are
within an area whose extent is a polygon drawn by the user.
SELECT C.CustomerName, SUM(S.SalesAmount)
FROM SalesS,CustomerC,CityY,TimeT
WHERE S.CustomerKey = C.CustomerKey AND C.CityKey = Y.CityKey AND
S.TimeKey = T.TimeKey AND T.Year = 1997 AND
ST Within(C.CityGeo, ST GeomFromText( ' POLYGON((200.0 50.0,
300.0 50.0, 300.0 80.0, 200.0 80.0, 200.0 50.0)) ' )
GROUP BY C.CustomerName
Query 11.2. Total sales to customers located in a state that contains the
capital city of the country.
SELECT C.CustomerName, SUM(S.SalesAmount)
FROM SalesS,CustomerC,CityY,StateA,CountryO
WHERE S.CustomerKey = C.CustomerKey AND
C.CityKey = Y.CityKey AND Y.StateKey = A.StateKey AND
A.CountryKey = O.CountryKey AND
ST Contains(A.StateGeo,O.CapitalGeo)
GROUP BY C.CustomerName
Query 11.3. Spatial union of the states in the USA where at least one
customer placed an order in 1997.
SELECT ST Union(DISTINCT A.StateGeo)
FROM Sales S, Customer C, City Y, State A, Country O, Time T
WHERE S.CustomerKey = C.CustomerKey AND C.CityKey = Y.CityKey AND
Y.StateKey = A.StateKey AND A.CountryKey = O.CountryKey AND
O.CountryName = ' United States ' AND
S.TimeKey = T.TimeKey AND T.Year = 1997