7

I am working on a road network for routing operations using postgis and QGIS.

I am new to both of these tools and need to calculate the slope for each edge (by retrieving the Z value for the start and end node for the edge from a raster). Any ideas of the best approach to this?

I know postgis can dump points and get the value from a raster, but can I get the slope attribute to the edge without breaking the network topology (with edges that have corresponding start/end nodes) using this method?

thanks

|improve this question
4

You probably could do something like this (assuming you already have an elevation raster in the database called 'elev' and the road network is called 'roads' with a primary key 'road_id')

-- Add a column for slope values
ALTER roads ADD COLUMN slope float;

-- Update with: (elevation of start pt - elev of end pt )/length of segment
UPDATE roads SET slope=(
   SELECT ( ST_Value(elev, ST_StartPoint(r.geom))-ST_Value(elev,ST_EndPoint(r.geom))/ST_Length(r.geom) )
   FROM roads AS r
   WHERE r.road_id=roads.road_id);

The ST_Length will, of course return values in the same units as the CRS. If your data is in Lon/Lat degrees, use ST_Length_Spheroid() instead. Slopes going down in the direction of the segments will be positive. Slopes up in the direction of the segment will be negative. Also, the above assumes the elevation units are the same as the CRS units.

HTH

|improve this answer
  • Thanks for this very detailed and helpful answer. As I am new to sql, I don´t fully understand why to use the WHERE clause? – Mac Aug 5 '12 at 13:20
  • Whenever you use a subquery to UPDATE a value for all rows, you need to add a WHERE restriction so that each row will get its correct value. Without the WHERE clause, the UPDATE would change the slope for all roads each time it gets a row back from the subquery. The end result would be that all slope values would be whatever the last row of the SELECT returned. Hope that's clear ;-) . Using the WHERE clause insures that each road_id will get the slope value for the same road_id in the subquery. – Micha Aug 6 '12 at 19:26

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.