The ST_Within() function

The ST_Within() function returns t (TRUE) if the first object is completely within the second; otherwise, it returns f (FALSE).

Syntax

ST_Within(g1 ST_Geometry, g2 ST_Geometry)

Usage

To return TRUE, the boundary and interior of the first geometry cannot intersect the exterior of the second geometry. ST_Within() tests for the exact opposite result of ST_Contains().

This graphic shows various geometric objects where one object is entirely within the other. — Figure 1: Geometries within other geometries

The results of the spatial relationship of the ST_Within() function can be understood or verified by comparing the results with a pattern matrix that represents the acceptable values for the DE-9IM. The ST_Within() function pattern matrix states that the interiors of both geometries must intersect and that the interior and boundary of the primary geometry (geometry a) must not intersect the exterior of the secondary (geometry b).

Table 1. Pattern matrix for the ST_Within() function
The table is a matrix. The top row and first column identify the geometry.
		b
		Interior	Boundary	Exterior
	Interior	T	*	F
a	Boundary	*	*	F
	Exterior	*	*	*

Return type

BOOLEAN

Example

In the example, two tables are created: buildingfootprints contains a city's building footprints, while the other, lots, contains its lots. The city engineer wants to make sure that all the building footprints are completely inside their lots.

In both tables, the ST_MultiPolygon data type stores the ST_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 a river, and building footprints comprise several buildings:

CREATE TABLE buildingfootprints (building_id  integer,
                                 lot_id       integer,
                                 footprint    ST_MultiPolygon);


CREATE TABLE lots (lot_id  integer,
                   lot     ST_MultiPolygon);

The city engineer first retrieves the buildings that are not completely within a lot:

SELECT building_id
   FROM buildingfootprints, lots
   WHERE ST_Within(footprint,lot);

building_id 

        506
        543
        178

The city engineer realizes that although the first query produces a list of all building IDs that have footprints outside a lot polygon, it does not ascertain whether the rest 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, bf.lot_id bldg_lot_id, lots.lot_id lots_lot_id 
   FROM buildingfootprints bf, lots
   WHERE ST_Within(footprint,lot)
   AND lots.lot_id <> bf.lot_id;

building_id bldg_lot_id lots_lot_id 

        178        5192         203