1

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:

Elevation Data of Minnesota

Stream Data as MultilineString:

Stream data in Minnesota

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.

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.

3
  • 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? – standers Jan 5 '15 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. – LR1234567 Jan 5 '15 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 – standers Jan 5 '15 at 4:31
0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.