User's Guide and Reference

ST_Within

ST_Within takes two geometry objects and returns 1 (TRUE) if the first object is completely within the second; otherwise it returns 0 (FALSE).

Syntax

db2gse.ST_Within(g1 db2gse.ST_Geometry, g2 db2gse.ST_Geometry)

Return type

Integer

Examples

In the example below, two tables are created. The first table, BUILDINGFOOTPRINTS, contains a city's building footprints. The second table, LOTS, contains the city's 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 because lots can be disjointed by natural features, such as a river, and building footprints can often be made up 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 ); 

Using the following SELECT statement, the city engineer first selects the buildings that are not completely within a lot.

SELECT building_id
  FROM BUILDINGFOOTPRINTS, LOTS
 WHERE db2gse.ST_Within(footprint,lot) = 0; 

Although the first query will provide a list of all BUILDING_IDs that have footprints outside of a lot polygon, it will not determine whether the rest have the correct lot_id assigned to them. This second SELECT statement 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_Within(footprint,lot) = 1 AND
       LOTS.lot_id <> bf.lot_id;