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).

Oracle

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

PostgreSQL

st_contains (g1 st_geometry, g2 st_geometry)

Boolean

In the example below, two tables are created. One, bfp, 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 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 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