Database Reference
In-Depth Information
Geometry
properties map to PostGIS
GEOMETRY
columns in the dimension
tables. As can be seen, spatial dimensions are typically defined as snowflake
dimensions because star schemas induce a redundancy of geometries located
at higher levels of a hierarchy, which would require much storage space and
wouldslowdownqueries.
Once the spatial cube is defined, MDX queries containing spatial predicates
and functions can be addressed to GeoMondrian, as we will see in the next
section.
11.6 Querying the GeoNorthwind Cube in MDX
We now show through a series of examples how a spatial data warehouse can
be queried with MDX. For this, we use the GeoNorthwind data warehouse.
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 Measures.SalesAmount ON COLUMNS,
FILTER(Customer.Geography.City.MEMBERS,
ST Within(Customer.Geography.CURRENTMEMBER.Properties(
'
CityGeo
'
),
ST GeomFromText(
'
POLYGON ((200.0 50.0, 300.0 50.0,
300.0 80.0, 200.0 80.0, 200.0 50.0))
'
))) ON ROWS
FROM Sales
WHERE OrderDate.[1997]
The above query uses the spatial predicate
ST Within
to filter mem-
bers according to their location. The polygon given as argument to the
ST GeomFromText
function will be defined by the user with the mouse in
a graphical interface showing a map.
Query 11.2.
Total sales to customers located in a state that contains the
capital city of the country.
SELECT
ON COLUMNS,
NON EMPTY FILTER(Customer.Geography.State.MEMBERS,
ST Contains(Customer.Geography.CURRENTMEMBER.
Properties(
'
StateGeo
'
),Customer.Geography.CURRENTMEMBER.
PARENT.PARENT.Properties(
'
CapitalGeo
'
))) ON ROWS
FROM Sales
The above query uses the function
ST Contains
to verify that the geometry
of a state contains the geometry of the capital of its country.
{
Measures.SalesAmount
}
The following query defines a calculated member that is a geometry.
Query 11.3.
Spatial union of the states in the USA where at least one
customer placed an order in 1997.
Search WWH ::
Custom Search