ST_Contains takes two geometry objects and returns 1 (TRUE) if the first object completely contains the second; otherwise it returns 0 (FALSE).
Syntax
db2gse.ST_Contains(g1 db2gse.ST_Geometry, g2 db2gse.ST_Geometry)
Return type
Integer
Examples
In the example below two tables are created. One table contains a city's building footprints, while the other table contains its lots. The city engineer wants to make sure that all the building footprints are completely inside their lots.
In both tables the multipolygon data type stores the geometry of the building footprints and the lots. The database designer selected multipolygons for both features. The designer realized that the lots can be disjointed by natural features, such as a river, and that the building footprints can often be made of several buildings.
CREATE TABLE BUILDINGFOOTPRINTS (building_id integer,
lot_id integer,
footprint db2gse.ST_MultiPolygon);
CREATE TABLE LOTS (lot_id integer, lot db2gse.ST_MultiPolygon);
The city engineer first selects the buildings that are not completely contained within one lot.
SELECT building_id FROM BUILDINGFOOTPRINTS, LOTS WHERE db2gse.ST_Contains(lot,footprint) = 0;
The city engineer realizes that the first query will return a list of all building IDs that have footprints outside of a lot polygon. But the city engineer also knows that this information will not indicate whether the other buildings have the correct lot ID assigned to them. This second query performs a data integrity check on the lot_id column of the BUILDINGFOOTPRINTS table.
SELECT bf.building_id "building id", bf.lot_id "buildings lot_id",
LOTS.lot_id "LOTS lot_id"
FROM BUILDINGFOOTPRINTS bf, LOTS
WHERE db2gse.ST_Contains(lot,footprint) = 1 AND LOTS.lot_id <> bf.lot_id;
In Figure 30, the building footprints labeled with their building IDs lie inside their lots. The lot lines are illustrated with dotted lines. Although not shown, these lines extend to the street centerline and completely encompass the lots and the building footprints within them.
Figure 30. Using ST_Contains to ensure that all buildings are contained within their lots
|