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