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

WITH MEMBER Measures.GeoUnion AS
ST UnionAgg(Customer.Geography.CURRENTMEMBER.CHILDREN,
' StateGeo ' )

SELECT { Measures.SalesAmount, Measures.GeoUnion } ON COLUMNS,

{ Customer.Geography.Country.[USA] } ON ROWS

FROM Sales
WHERE OrderDate.[1997]

Here,weusethefunction ST UnionAgg to perform the spatial union of all
the states of the USA that satisfy the query condition. The second argument
of the function states the name of the property (i.e., StateGeo ) containing
the geometries that will be aggregated.

Query 11.4. Distance between the customers' locations and the capital of
the state in which they are located.
WITH MEMBER Measures.Distance AS
ST Distance(Customer.Geography.CURRENTMEMBER.
Properties( ' CustomerGeo ' ), Customer.Geography.
CURRENTMEMBER.PARENT.PARENT.Properties( ' CapitalGeo ' ))

SELECT ON COLUMNS,

Customer.Geography.Customer.MEMBERS ON ROWS
FROM Sales
The above query defines a calculated measure Distance , which is a numerical
value obtained by computing with the function ST Distance the distance from
the geometries of the customer and the capital of its state.
Query 11.5. For each customer, total sales amount to its closest supplier.

{ Measures.Distance }

WITH MEMBER Measures.Distance AS
ST Distance(
Customer.Geography.CURRENTMEMBER.Properties( ' CustomerGeo ' ),
Supplier.Geography.CURRENTMEMBER.Properties( ' SupplierGeo ' ))
SELECT Measures.SalesAmount ON COLUMNS,
GENERATE(Customer.Geography.Customer.MEMBERS,
BOTTOMCOUNT(Customer.Geography.CURRENTMEMBER *
Supplier.Geography.Supplier.MEMBERS, 1, Measures.Distance)) ON ROWS
FROM Sales

In the above query, we use the GENERATE function to obtain for each
customer the closest supplier. The latter is obtained by applying the
BOTTOMCOUNT function with respect to the calculated measure Distance .

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 { ON COLUMNS,

GENERATE(Customer.Geography.Customer.MEMBERS,
FILTER(Customer.Geography.CURRENTMEMBER *

Measures.[Sales Amount] }