Definition
ST_Length returns the length of a line string or multiline string.
Syntax
Oracle and PostgreSQL
sde.st_length (line1 sde.st_geometry) sde.st_length (multiline1 sde.st_geometry)
SQLite
st_length (line1 geometryblob) st_length (multiline1 geometryblob) st_length (line1 geometryblob, unit_name text) st_length (multiline1 geometryblob, unit_name text)
For a list of supported unit names, see ST_Distance.
Return type
Double precision
Example
An ecologist studying the migratory patterns of the salmon population in the country's waterways wants the length of all stream and river systems within the country.
The waterways table is created with the ID and name columns, which identify each stream and river system stored in the table. The water column is a multilinestring because the river and stream systems are often aggregates of several linestrings.
The SELECT query returns the name of the system along with the length of the system generated by the ST_Length function. In Oracle and PostgreSQL, the units are those used by the coordinate system. In SQLite, kilometer units are specified.
Oracle
CREATE TABLE waterways (
oid integer,
name varchar(128),
water sde.st_geometry
);
INSERT INTO waterways (oid, name, water) VALUES (
1111,
'Genesee',
sde.st_multilinestring ('multilinestring ((33 2, 34 3, 35 6),
(28 4, 29 5, 31 8, 43 12), (39 3, 37 4, 36 7))', 4326)
);
SELECT name, sde.st_length (water) "Length"
FROM WATERWAYS;
NAME Length
Genesee 27.6437123
PostgreSQL
CREATE TABLE waterways (
oid serial,
name varchar(128),
water sde.st_geometry
);
INSERT INTO waterways (name, water) VALUES (
'Genesee',
sde.st_multilinestring ('multilinestring ((33 2, 34 3, 35 6),
(28 4, 29 5, 31 8, 43 12), (39 3, 37 4, 36 7))', 4326)
);
SELECT name AS "Watershed Name",
sde.st_length (water) AS "Length"
FROM waterways;
Watershed Name | Length
Genesee | 27.6437123387202
SQLite
CREATE TABLE waterways (
oid integer primary key autoincrement not null,
name text(128)
);
SELECT AddGeometryColumn (
NULL,
'waterways',
'water',
4326,
'multilinestring',
'xy',
'null'
);
INSERT INTO waterways (name, water) VALUES (
'Genesee',
st_multilinestring ('multilinestring ((33 2, 34 3, 35 6),
(28 4, 29 5, 31 8, 43 12), (39 3, 37 4, 36 7))', 4326)
);
SELECT name AS "Watershed Name",
st_length (water, 'kilometer') AS "Length"
FROM waterways1;
Watershed Name Length
Genesee 3047.75515002795