Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

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

share|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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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