ArcGIS Server Banner

ST_Within

ST_Within

Release 9.3 E-mail This TopicPrintable VersionGive Us feedback

Definition

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

Syntax

Oracle

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

PostgreSQL

st_within (g1 st_geometry, g2 st_geometry)

Return type

Boolean

Example

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

See Also

  • An overview of SQL functions used with ST_Geometry types
  • Spatial relationships