Combine Elevation Raster and MultilineString to create MultilineStringZ in PostGIS

Question

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.

Answer 1

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.

Examples of using ST_Value.

Answer 2

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;