# Combine Elevation Raster and MultilineString to create MultilineStringZ in PostGIS

I want to show how steep a given stream is, with PostGIS and QGIS.

My thought process for solving this problem of steepness is as follows: 1). Given Raster and Multilinestring data, combine them into a MultilineStringZ column 2). Perform some elementary calculus on the new MultilineStringZ column to find steeper sections. Colorize them in QGIS if possible to examine the results.

Elevation Data:

Stream Data as MultilineString:

I need a better understanding on how a MultilineString and a raster layer are combined to create a 3D stream, or alternatives to completing the task of encoding "steepness" into line strings.

To create a MultiLineStringZ from raster and MultiLineString, use the ST_Intersection function or ST_Value (combined with ST_Segmentize).

You can also compute steepnesses right in raster space using the ST_Slope function.

• By sheer coincidence, I had the MEAP 2nd edition in my drop box. Thanks for your hard work. However, it seems that using ST_Intersection creates many different LineStrings and Multiline strings, like pushing a piece of spaghetti through a screen door. Now I have about 300+ pieces of macaroni. Is there a way to ensure that one single geometry results in a single MultilineStringZ? Jan 5, 2015 at 0:34
• Usually for LineStrings, I go the ST_Segmentize approach (think we have that covered in the book as well) which better handles this issue. ST_Segmentize will add more nodes to your line, and then you take the ST_Value at each node and reconstruct the linestring from these. So its less accurate than ST_Intersection, but you won't run into the meat grinder problem. Jan 5, 2015 at 3:26
• I used the following code successfully that can be found in Chapter 12 of PostGIS in Action, 2nd Edition: pastebin.com/wdKfRU7q Jan 5, 2015 at 4:31

Partial answer for transforming a LineString column `geog` into a LineStringZ by adding Z values from a raster table called `elevations`:

``````WITH points2d AS (
SELECT gid,
ST_DumpPoints(geog::geometry) AS point2d -- Split the LineString into points
FROM lines
),
linestrings3d AS (
SELECT gid,
ST_MakeLine( -- Combine the points into LineStringZ
ST_Translate( -- Add the Z value
ST_Force3DZ((point2d).geom), -- Force the points to 3D
0,
0,
ST_Value( -- Extract the Z value from the raster
rast,
(point2d).geom
)
) ORDER BY (point2d).path -- Ensure the points keep their initial order
) AS linestringz
FROM points2d
LEFT JOIN elevations ON ST_Intersects(rast, (point2d).geom) -- Only use the raster rows containg information for those points
GROUP BY gid
)
UPDATE lines
SET geog = linestringz -- Update the original column from 2D to 3D
FROM linestrings3d
WHERE lines.gid = linestrings3d.gid;
``````