Definition
ST_ExteriorRing returns the exterior ring of a polygon as a linestring.
Syntax
sde.st_exteriorring (polygon1 sde.st_geometry)
Oracle and PostgreSQL
sde.st_exteriorring (polygon1 sde.st_geometry)
SQLite
st_exteriorring (polygon1 geometryblob)
Return type
ST_LineString
Example
An ornithologist, wanting to study the bird population on several islands, knows that the feeding zone of the bird species she is interested in is restricted to the shoreline. As part of her calculation of the islands' carrying capacity, the ornithologist requires the islands' perimeters. Some of the islands are so large they have several lakes on them. However, the shoreline of the lakes is inhabited exclusively by another more aggressive bird species. Therefore, the ornithologist requires the perimeter of only the exterior ring of the islands.
The ID and name columns of the islands table identify each island, while the land polygon column stores the island's geometry.
The ST_ExteriorRing function extracts the exterior ring from each island polygon as a linestring. The length of the linestring is calculated by the ST_Length function. The linestring lengths are summarized by the SUM function.
The exterior rings of the islands represent the ecological interface each island shares with the sea.
Oracle
--Create the table and insert two polygons.
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))', 4326)
);
INSERT INTO islands VALUES (
2,
'Johnson',
sde.st_polygon ('polygon ((10 10, 50 10, 10 30, 10 10))', 4326)
);
--Extract the exterior ring from each island and find its length.
SELECT SUM (sde.st_length (sde.st_exteriorring (land)))
FROM ISLANDS;
SUM(ST_LENGTH(ST_EXTERIORRING(LAND)))
264.72136
PostgreSQL
--Create the table and insert two polygons.
CREATE TABLE islands (
id serial,
name varchar(32),
land sde.st_geometry
);
INSERT INTO islands (name, land) VALUES (
'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))', 4326)
);
INSERT INTO islands (name, land) VALUES (
'Johnson',
sde.st_polygon ('polygon ((10 10, 50 10, 10 30, 10 10))', 4326)
);
--Extract the exterior ring from each island and find its length.
SELECT SUM (sde.st_length (sde.st_exteriorring (land)))
FROM islands;
sum
264.721359549996
SQLite
--Create the table and insert two polygons.
CREATE TABLE islands (
id integer primary key autoincrement not null,
name varchar(32)
);
SELECT AddGeometryColumn (
NULL,
'islands',
'land',
4326,
'polygon',
'xy',
'null'
);
INSERT INTO islands (name, land) VALUES (
'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))', 4326)
);
INSERT INTO islands (name, land) VALUES (
'Johnson',
st_polygon ('polygon ((10 10, 50 10, 10 30, 10 10))', 4326)
);
--Extract the exterior ring from each island and find its length.
SELECT SUM (st_length (st_exteriorring (land)))
FROM islands;
sum
264.721359549996