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

Notice that in the above example, the topological constraint involves only two
spatial levels. It is somewhat more complex to enforce a topological constraint
that involves more than two spatial dimensions.
A topological constraint between spatial levels can be enforced either at
each insertion of a child member or after the insertion of all children members.
The choice among these two solutions depends on the kind of topological
constraint. For example, a topological constraint stating that a region is
located inside the geometry of its country can be enforced each time a city is
inserted, while a topological constraint stating that the geometry of a country
is the spatial union of all its composing regions must be enforced after all of
the regions and the corresponding country have been inserted.
As an example of the first solution, a trigger can be used to enforce the
CoveredBy topological constraint between the Region and Country levels in
Fig. 11.4 . This trigger should raise an error if the geometry of a region member
is not covered by the geometry of its related country member. Otherwise, it
should insert the new data into the Country table.Thetriggerisasfollows:
CREATE OR REPLACE FUNCTION RegionInCountry()
RETURNS TRIGGER AS $RegionInCountry$
DECLARE
CountryGeo GEOMETRY;
BEGIN
/* Retrieve the geometry of the associated country */
CountryGeo = (SELECT C.CountryGeo FROM Country C
WHERE NEW.CountryKey = C.CountryKey);
/* Raise error if the topological constraint is violated */
IF NOT ST COVERS(CountryGeo, NEW.RegionGeo) THEN
RAISE EXCEPTION ' A region cannot be outside its country ' ;
END IF;
RETURN NEW;
END;
$RegionInCountry$ LANGUAGE plpgsql;
CREATE TRIGGER RegionInCountry
BEFORE INSERT OR UPDATE ON Region
FOR EACH ROW EXECUTE PROCEDURE RegionInCountry();
In the second solution, child members are inserted without activating a
trigger. When all children members have been inserted, the verification is
performed. For the GeoNorthwind case study, suppose that the geometries of
Region partition the geometry of Country . When all regions of a country have
been inserted into the warehouse, the following query can be used to look for
countries whose regions do not partition the geometry of the country.
SELECT CountryKey, CountryName
FROM Country C
WHERE NOT ST EQUALS(C.CountryGeo,
(SELECT ST UNION(R.RegionGeo)
FROM Region R WHERE R.CountryKey = C.CountryKey))