postgis - calculate slope for a road network

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

Answer

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