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

Query 11.4. Distance between the customers' locations and the capital of
the state in which they are located.
SELECT DISTINCT C.CompanyName AS CustomerName,
ST Distance(C.CustomerGeo,CS.CapitalGeo) AS Distance
FROM Sales S, Customer C, City AS CC, State AS CS
WHERE S.CustomerKey = C.CustomerKey AND
C.CityKey = CC.CityKey AND CC.StateKey = CS.StateKey
ORDER BY C.CompanyName
Query 11.5. For each customer, total sales amount to its closest supplier.
SELECT C.CustomerName, SUM(S.SalesAmount)
FROM Sales S, Customer C, Supplier U
WHERE S.CustomerKey = C.CustomerKey AND
S.SupplierKey = U.SupplierKey AND
ST Distance(C.CustomerGeo,U.SupplierGeo) < = (
SELECT MIN(ST Distance(C.CustomerGeo,U1.SupplierGeo)
FROM Sales S1, Supplier U1
WHERE S1.CustomerKey = C.CustomerKey AND
S1.SupplierKey = U1.SupplierKey )
GROUP BY C.CustomerName
Query 11.6. Total sales amount for customers that have orders delivered
by suppliers such that their locations are less than 200km from each other.
SELECT C.CustomerName, SUM(S.SalesAmount)
FROM Sales S, Customer C, Supplier U
WHERE S.CustomerKey = C.CustomerKey AND
S.SupplierKey = U.SupplierKey AND
ST Distance(C.CustomerGeo,U.SupplierGeo) < 200
GROUP BY C.CustomerName
Query 11.7. Distance between the customer and supplier for customers
that have orders delivered by suppliers of the same country.
SELECT DISTINCT C.CompanyName AS CustomerName,
U.CompanyName AS SupplierName,
ST Distance(C.CustomerGeo,U.SupplierGeo) AS Distance
FROM Sales S, Customer C, City AS CC, State AS CS,
Supplier U, City AS SC, State AS SS
WHERE S.CustomerKey = C.CustomerKey AND
C.CityKey = CC.CityKey AND CC.StateKey = CS.StateKey AND
S.SupplierKey = U.SupplierKey AND U.CityKey = SC.CityKey AND
SC.StateKey = SS.StateKey AND SS.CountryKey = CS.CountryKey
ORDER BY C.CompanyName, U.CompanyName
Query 11.8. Number of customers for European countries with an area
larger than 50,000km 2 .