-- A local ecologist studying the migratory patterns of the salmon
-- population in the county's waterways wants the length of all
-- stream and river systems within the county. 
-- 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 an aggregate of several linestrings.

CREATE TABLE waterways (id     integer,
                        name   varchar(128),
                        water  ST_MultiLineString);

INSERT INTO waterways VALUES(
   829, 'Fedders creek',
   ST_MLineFromText('multilinestring ((5000 0,2000 12000,17000 6000,8000 19000,21000 11000,23000 13000,25000 16000,29000 19000,31000 21000,39000 27000,45000 31000, 51000 31000, 59000 29000, 66000 29000, 75000 32000),(29000 19000,32500 26000,34000 31000,35000 39000,36000 41000,39000 48000,42000 51000,49000 58000,51000 59000,57000 62000))',1000)
);

-- The query returns the name of each system along with the length of
-- the system generated by the ST_Length function.

SELECT name, ST_Length(water) Length
   FROM waterways;
