ArcGIS Server Banner

ST_Contains

ST_Contains

Release 9.3 E-mail This TopicPrintable VersionGive Us feedback

Definition

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

Syntax

Oracle

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

PostgreSQL

st_contains (g1 st_geometry, g2 st_geometry)

Return type

Boolean

Example

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

See Also

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