ST_NumInteriorRing takes an ST_Polygon and returns the number of its interior rings.

Oracle

sde.st_numinteriorring (pl1 sde.st_geometry)

PostgreSQL

st_numinteriorring (pl1 st_geometry)

Integer

An ornithologist wants to study a bird population on several South Seas islands. She wants to identify which islands contain one or more lakes because the bird species she is interested in feeds only in freshwater lakes.

The ID and name columns of the islands table identify each island, while the land ST_Polygon column stores the islands' geometry.

Oracle

CREATE TABLE islands (id integer,
name varchar(32),
land sde.st_geometry);

INSERT INTO islands VALUES (
1,
'Bear',
sde.st_polygon ('polygon ((40 120, 90 120, 90 150, 40 150, 40 120),(50 130, 60 130, 60 140, 50 140, 50 130),(70 130, 80 130, 80 140, 70 140, 70 130))', 0)
);

INSERT INTO islands VALUES (
2,
'Johnson',
sde.st_polygon ('polygon ((10 10, 50 10, 10 30, 10 10))', 0)
);

PostgreSQL

CREATE TABLE islands (id integer,
name varchar(32),
land st_geometry);

INSERT INTO islands VALUES (
1,
'Bear',
st_polygon ('polygon ((40 120, 90 120, 90 150, 40 150, 40 120),(50 130, 60 130, 60 140, 50 140, 50 130),(70 130, 80 130, 80 140, 70 140, 70 130))', 0)
);

INSERT INTO islands VALUES (
2,
'Johnson',
st_polygon ('polygon ((10 10, 50 10, 10 30, 10 10))', 0)
);

Because interior rings represent the lakes, the ST_NumInteriorRing function lists only those islands that have at least one interior ring.

Oracle

SELECT name
FROM islands
WHERE sde.st_numinteriorring (land)> 0;

NAME

Bear

PostgreSQL

SELECT name
FROM islands
WHERE st_numinteriorring (land)> 0;

name

Bear