ST_Within returns 1 (Oracle) or t (PostgreSQL) if the first object is completely within the second; otherwise, it returns 0 (Oracle) or f (PostgreSQL).

Oracle

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

PostgreSQL

st_within (g1 st_geometry, g2 st_geometry)

Boolean

In the example below, two tables are created: one, bfootprints, 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 multipolygon data type stores the ST_Geometry of the building footprints and lots. The database designer selected ST_MultiPolygons for both features because she realized a lot can be separated by a natural feature, such as a river, and a building footprint can be made up of several buildings.

Oracle

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

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

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

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

INSERT INTO bfootprints (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 bfootprints (building_id integer,
footprint st_geometry);

CREATE TABLE lots (lot_id integer,
lot st_geometry);

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

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

INSERT INTO bfootprints (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 within a lot.

Oracle

SELECT bf.building_id
FROM bfootprints bf, lots l
WHERE sde.st_intersects (bf.footprint, l.lot) = 1 
AND sde.st_within (bf.footprint, l.lot) = 0;

BUILDING_ID

          2

PostgreSQL

SELECT bf.building_id
FROM bfootprints bf, lots l
WHERE st_intersects (bf.footprint, l.lot) = 't' 
AND st_within (bf.footprint, l.lot) = 'f';

building_id

          2