ST_Contains

Definition

ST_Contains takes two geometry objects and returns 1 (Oracle) or t (PostgreSQL) if the first object completely contains the second; otherwise, it returns 0 (Oracle) or f (PostgreSQL).

Syntax

sde.st_contains (g1 sde.st_geometry, g2 sde.st_geometry)

Return type

Boolean

Example

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

In both tables, the multipolygon datatype stores the geometry of the buildingfootprints 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 can comprise several buildings.

Oracle

CREATE TABLE bfp (building_id integer,
footprint sde.st_geometry);

CREATE TABLE lots (lot_id integer,
lot sde.st_geometry);

INSERT INTO BFP (building_id, footprint) VALUES (
1,
sde.st_polygon ('polygon ((0 0, 0 10, 10 10, 10 0, 0 0))', 0)
);

INSERT INTO BFP (building_id, footprint) VALUES (
2,
sde.st_polygon ('polygon ((20 0, 20 10, 30 10, 30 0, 20 0))', 0)
);

INSERT INTO BFP (building_id, footprint) VALUES (
3,
sde.st_polygon ('polygon ((40 0, 40 10, 50 10, 50 0, 40 0))', 0)
);

INSERT INTO LOTS (lot_id, lot) VALUES (
1,
sde.st_polygon ('polygon ((-1 -1, -1 11, 11 11, 11 -1, -1 -1))', 0)
);

INSERT INTO LOTS (lot_id, lot) VALUES (
2,
sde.st_polygon ('polygon ((19 -1, 19 11, 29 9, 31 -1, 19 -1))', 0)
);

INSERT INTO LOTS (lot_id, lot) VALUES (
3,
sde.st_polygon ('polygon ((39 -1, 39 11, 51 11, 51 -1, 39 -1))', 0)
);

PostgreSQL

CREATE TABLE bfp (building_id integer,
footprint st_geometry);

CREATE TABLE lots (lot_id integer,
lot st_geometry);

INSERT INTO bfp (building_id, footprint) VALUES (
1,
st_polygon ('polygon ((0 0, 0 10, 10 10, 10 0, 0 0))', 0)
);

INSERT INTO bfp (building_id, footprint) VALUES (
2,
st_polygon ('polygon ((20 0, 20 10, 30 10, 30 0, 20 0))', 0)
);

INSERT INTO bfp (building_id, footprint) VALUES (
3,
st_polygon ('polygon ((40 0, 40 10, 50 10, 50 0, 40 0))', 0)
);

INSERT INTO lots (lot_id, lot) VALUES (
1,
st_polygon ('polygon ((-1 -1, -1 11, 11 11, 11 -1, -1 -1))', 0)
);

INSERT INTO lots (lot_id, lot) VALUES (
2,
st_polygon ('polygon ((19 -1, 19 11, 29 9, 31 -1, 19 -1))', 0)
);

INSERT INTO lots (lot_id, lot) VALUES (
3,
st_polygon ('polygon ((39 -1, 39 11, 51 11, 51 -1, 39 -1))', 0)
);

The city engineer selects the buildings that are not completely contained within one lot.

Oracle

SELECT UNIQUE (building_id)
FROM BFP, LOTS
WHERE sde.st_intersects (lot, footprint) = 1
AND sde.st_contains (lot, footprint) = 0;

BUILDING_ID

          2

PostgreSQL

SELECT DISTINCT (building_id)
FROM bfp, lots
WHERE st_intersects (lot, footprint) = 't'
AND st_contains (lot, footprint) = 'f';

building_id

          2