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

CommonArea GEOMETRY(LINESTRING, 4326),
FOREIGN KEY (SegmentKey) REFERENCES Segment(SegmentKey),
/* Other foreign key constraints */ );
As an example of mapping of spatial hierarchies, Fig. 11.15 shows the
mapping of the relationship between the Region and Country levels in
Fig. 11.4 . We see that the Region table includes an attribute RegionKey
referencing the parent level Country .
11.4.3 Topological Constraints
We conclude this section studying how a topological constraint in a fact
or between two spatial levels is mapped to the relational model. These
constraints restrict either the geometries of spatial members related to a
fact or the geometry of children members with respect to the geometry of
their associated parent member. For example, the spatial fact Maintenance in
Fig. 11.6 has an Overlaps relationship that states that a segment and a county
related to each other in a fact member must overlap. Similarly, in Fig. 11.4 ,
a CoveredBy relationship exists between the Region and Country levels, which
indicates that the geometry of a region is covered by the geometry of a
country.
The trigger that enforces the topological constraint in the spatial fact
Maintenance can be written as follows:
CREATE OR REPLACE FUNCTION SegmentOverlapsCounty()
RETURNS TRIGGER AS $SegmentOverlapsCounty$
DECLARE
SegmentGeo GEOMETRY;
CountyGeo GEOMETRY;
BEGIN
/* Retrieve the geometries of the associated segment and county */
SegmentGeo = (SELECT S.SegmentGeo FROM Segment S
WHERE NEW.SegmentKey = S.SegmentKey);
CountyGeo = (SELECT C.CountyGeo FROM County C
WHERE NEW.CountyKey = C.CountyKey);
/* Raise error if the topological constraint is violated */
IF NOT ST OVERLAPS(SegmentGeo, CountyGeo) THEN
RAISE EXCEPTION ' The segment and the county must overlap ' ;
END IF;
RETURN NEW;
END;
$SegmentOverlapsCounty$ LANGUAGE plpgsql;
CREATE TRIGGER SegmentOverlapsCounty
BEFORE INSERT OR UPDATE ON Maintenance
FOR EACH ROW EXECUTE PROCEDURE SegmentOverlapsCounty();