Database Reference
In-Depth Information
SELECT C.CountryName, COUNT(DISTINCT S.CustomerKey)
FROM Sales S, Customer U, City Y, State T, Country C, Continent A
WHERE S.CustomerKey = U.CustomerKey AND U.CityKey = Y.CityKey AND
Y.StateKey = T.StateKey AND T.CountryKey = C.CountryKey AND
ST Area(C.CountryGeo) > 50000 AND
C.ContinentKey = A.ContinentKey AND A.ContinentName = ' Europe '
GROUP BY C.CountryName
Query 11.9. For each supplier, number of customers located at more than
100km from the supplier.
SELECT P.SupplierName, COUNT(DISTINCT C.CustomerKey)
FROM Sales S, Supplier P, Customer C
WHERE S.SupplierKey = P.SupplierKey AND
S.CustomerKey = C.CustomerKey AND
ST Distance(P.SupplierGeo,C.CustomerGeo) > 100
GROUP BY P.SupplierName
Query 11.10. For each supplier, distance between the location of the
supplier and the centroid of the locations of all its customers.
SELECT P.SupplierName, ST Distance(P.SupplierGeo,
ST Centroid(ST Union(DISTINCT C.CustomerGeo)))
FROM Sales S, Supplier P, Customer C
WHERE S.SupplierKey = P.SupplierKey AND S.CustomerKey = C.CustomerKey
GROUP BY P.SupplierName
11.8 Spatial Data Warehouse Design
In this section, we extend the method studied in Chap. 10 to support
spatial data. Recall that the method includes the phases of requirements
specification, conceptual design, logical design, and physical design. We
revisit these phases, describing how to take spatial support into account.
Similar to traditional data warehouse design, this method is independent of
the conceptual model used. We will use the MultiDim model extended with
spatial data studied in the present chapter.
There is not yet a well-established method for the design of spatial data
warehouses. In general, the four phases described for designing traditional
data warehouses can be applied for spatial data warehouses. Two main
approaches can be distinguished. In the first one, spatial elements are included
in the initial conceptual schema. In the other approach, the nonspatial schema
is initially developed, and it is augmented afterward with spatial elements.
In both cases, the spatially extended conceptual schema is then translated
into logical and physical schemas using mapping rules. Nevertheless, owing
to the lack of a well-accepted conceptual model for the design of spatial data
warehouses, in many situations the phase of conceptual design is skipped,
starting the design process with the logical schema.
 
Search WWH ::




Custom Search