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