-- An ornithologist wishes to study a bird population on several
-- south sea 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 identifies each island
-- while the land ST_Polygon column stores the island's geometry.

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

INSERT INTO islands VALUES(
   1,
   'hawaii',
   ST_PolyFromText('polygon ((42000 18000,48000 18000,48000 13000,42000 13000,42000 18000))',1000)
);

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

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