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