Download IBM Informix Spatial Data User's Guide

Transcript
Table 7-6. Pattern matrix for the ST_Contains() function (continued).
b
Interior
a
Boundary
Exterior
Boundary
*
*
*
Exterior
F
F
*
Return type
BOOLEAN
Example
In the example below, two tables are created: buildingfootprints contains a city's
building footprints, and lots contains its lots. The city engineer wants to ensure
that all building footprints are completely inside their lots.
In both tables, the ST_MultiPolygon data type stores the geometry of the building
footprints and the lots. The database designer selected multipolygons for both
features because lots can be separated by natural features such as rivers, and
building footprints can comprise several buildings:
CREATE TABLE buildingfootprints (building_id
lot_id
footprint
integer,
integer,
ST_MultiPolygon);
CREATE TABLE lots (lot_id integer,
lot
ST_MultiPolygon);
The city engineer first selects the buildings that are not contained within one lot:
SELECT building_id
FROM buildingfootprints, lots
WHERE NOT ST_Contains(lot,footprint);
Although the first query provides a list of all building IDs that have footprints
outside a lot polygon, it does not determine whether the rest are assigned the
correct lot_id. The city engineer runs a second query to check the data integrity on
the lot_id column of the buildingfootprints table:
SELECT bf.building_id, bf.lot_id, lots.lot_id
FROM buildingfootprints bf, lots
WHERE NOT ST_Contains(lot,footprint)
AND lots.lot_id <> bf.lot_id;
In the following figure, the building footprints are labeled with their building IDs
and lie inside their lot lines. The lot lines are illustrated with dotted lines.
Although not shown, they extend to the street centerline to completely encompass
the lot lines and the building footprints within them.
Chapter 7. Spatial functions
7-29