6

I have a table with multilinestring geometries that can be updated or inserted by external users, and a raster table with elevation values.

I want to write a trigger that sets the z value of each point in the line from the DEM.

Probably I can write a function that iterates over the linestring points with ST_NPoint or ST_DumpPoints. Gets the elevation from the raster with ST_Value. And recreates the point with ST_SetPoint.

But, is there any easier way to to this?

  • It's hard to see how you would avoid iterating over the linestring points, given that different points on the linestring may have different z values. – amball Mar 24 '17 at 3:54
  • I don't see it either. But iterating over the points with a LOOP is terrible slow. Maybe PostGIS has a function that I didn't know about that already to this. – Francisco Puga Mar 24 '17 at 7:56
  • 2
    You don't need a loop for the method you suggest in your question. 1. Dump the points with ST_DumpPoints (making sure to retain the path id of each linestring, the id of the multilinestring, and the order of the points). 2. Calculate the z values with ST_Value. 3. Add the z value to each point with ST_MakePoint'. 4. Use ST_Makeline` to aggregate the rows back to multilinestrings. – amball Mar 24 '17 at 23:00
2

Partial answer for producing a LineStringZ:

WITH points2d AS (
  SELECT NEW.gid,
         ST_DumpPoints(NEW.geog::geometry) AS point2d
  FROM lines
)
SELECT
  gid,
  ST_MakeLine(
    ST_Translate(
      ST_Force3DZ((point2d).geom), 
      0, 
      0, 
      ST_Value(
        rast, 
        (point2d).geom
      )
    ) 
  ORDER BY (point2d).path) AS linestringz
FROM points2d 
  LEFT JOIN elevations ON ST_Intersects(rast, (point2d).geom)
GROUP BY 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.