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

SELECT Measures.CustomerCount ON COLUMNS,
FILTER(Customer.Geography.Country.MEMBERS,
CountryArea > 50000 AND Customer.Geography.
CURRENTMEMBER.PARENT.NAME = ' Europe ' )ONROWS
FROM Sales

In this query, the calculated measure CountryArea uses the function ST Area
for obtaining the area of a country. The calculated measure CustomerCount
uses the keyword EXISTING to force the set of customers to be evaluated
within the current context, that is, the current country. Finally, the function
FILTER allows to select European countries whose area is greater than 50,000.

Query 11.9. For each supplier, number of customers located at more than
100km from the supplier.
WITH MEMBER Measures.CustomerCount AS
COUNT(FILTER(Supplier.Geography.CURRENTMEMBER *
Customer.Geography.Customer.MEMBERS, ST Intersect(ST Buffer(
Supplier.Geography.CURRENTMEMBER.Properties( ' SupplierGeo ' ), 100)
Customer.Geography.CURRENTMEMBER.Properties( ' CustomerGeo ' ))
AND Measures.SalesAmount > 0))
SELECT

ON COLUMNS,
Supplier.Geography.Supplier.MEMBERS ON ROWS
FROM Sales
The calculated measure CustomerCount uses the function ST Buffer to
produce a circle of 100km radius centered in the location of the current
supplier. The function ST Intersects then verifies that this circle intersects
with the location of the customer. The function FILTER selects for each
supplier the customers that satisfy the topological constraint and that are
related through at least one order, and, finally, the function COUNT is used
to obtain the number of selected customers.
Query 11.10. For each supplier, distance between the location of the
supplier and the centroid of the locations of all its customers.

{ Measures.CustomerCount }

WITH MEMBER Measures.CustomerLocations AS
ST UnionAgg(FILTER(Customer.Geography.Customer.MEMBERS,
Measures.SalesAmount > 0), ' CustomerGeo ' ))
MEMBER Measures.CentroidCustomers AS
ST Centroid(CustomerLocations)
MEMBER Measures.DistanceCentroid AS
ST Distance(CentroidCustomers,
Supplier.Geography.CURRENTMEMBER.Property( ' SupplierGeo ' ))

SELECT ON COLUMNS,

Supplier.Geography.Supplier.MEMBERS ON ROWS
FROM Sales

{ Measures.DistanceCentroid }