macjusten (3) [Avatar] Offline

I want to add slope as an attribute to the edges of an existng road network. Can I use the method described in 13.4, dump points? And if so, does this require to rebuild the entire network? I just need to add the Z value to the start and end nodes (and points inbetween using segmentize).

regina.leo (265) [Avatar] Offline
Re: chapter 13 ST_value
Since we wrote the book, the release of 2.0.0 included an ST_Slope function.

I suspect that will better fit for your needs.
(Sorry haven't had a chance to provide an example)

You'd still use ST_Value, but you'd use it against the sloped raster tiles that intersect your edge areas of interest.

So the COALESCE line would change to
COALESCE(ST_Value(ST_Slope(rast), (gd).geom),0 )

Now regarding rebuilding the network or just using it against the start / end points. Not sure I understand your question about how you plan to use segmentize. The example adds the Z to the points that define the line -- so the nodes of the line (not the internal segments if that is what you are asking). So in that case yes. But if you are talking about adding Z to the start and end points of a non-2 point segment segmentize doesn't seem like the right tool.
macjusten (3) [Avatar] Offline
Re: chapter 13 ST_value
Thanks for the quick reply!

I should probably provide more detail. I am using pgrouting to calculate shortest path where I want the slope to affect the cost and have imported OSM data using osm2po. The pros of this is that the reverse cost for one-way restrictions is already set, so I kind of like to keep it that way. So I have a table with edges that has source and targets nodes as attributes (with xy columns). I need to know the direction of the slope, wether it is uphill or downhill from source to target.

Regarding using ST_slope I believe that will result in incorrect slope values as it calculates the steepest slope value while I am only interested in the slope in one direction (or two- along the road). Or will it calculate slope only along the line with the query?

The reason for using segmentize: edges need to be broken into smaller parts to not miss any up-or downhills inbetween the start/end nodes, edge that is 200 m long can have the same elevation for the corresponding nodes but 50 m of the edge can be downhill and 10 m steep uphill. I don´t want to miss out on this detail. However there might be a better method for this, I don´t know

I am new to postgis and SQL queries, and to be honest I am about going crazy on how to proceed with this, using PostGis and SQL or perhaps some desktop GIS which I am more comfortable with.

btw why use coalesce (what does this do?)
regina.leo (265) [Avatar] Offline
Re: chapter 13 ST_value
Probably best to ask on PostGIS mailing list. David Zwarg and Bborie Park both worked on implementing the neighbor functions and Pierre Racine did a lot of the architecting so would have a better idea. They all answer questions on the mailing list

I think you are right that ST_Slope might not do quite what you need.

The ST_Slope is a neighbor function that considers the 3x3 next door neighbor pixels that surround a cell. So like you said its not going to give slope along the line. The input raster you get is the same dimension as the output raster. Though since you can't control the neighboring, I guess it might not be that useful and you'd be better off doing ST_Value of regular raster and computing the slope along the line.

COALESCE ensures that if there is no pixel that intersects the line, you get some number back instead of NULL.

Okay I see the purpose of your segmentize now. I'm still unclear when you say xy. Are you storing each segmented point separately or as a single line.

If you just want something that takes your segmentized linestring -> function --> segmentized line with z added

Then the easiest would be to wrap that logic in an SQL or plpgsql function and apply to each of your linestrings. You can wrap the segmentize process as part of the function. It would be essentially the same code but you'd paramterize geom with $1 or something.

We covered things like this in our new book which happens to be on sale today:
macjusten (3) [Avatar] Offline
Re: chapter 13 ST_value
thanks for your reply and info, didn´t think of the mailing list, that´s a good resource of course.

I think it is getting clearer.

I have not done the segmentization yet, actually.

Can you just help me with one last question (maybe obvious anwer), however I would be really thankful, and maybe it is in the book, but I don´t have access to it at the moment? Does ST_segmentize followed by ST_DumpPoints, result in points that reference back to the edge id, in path order from start to end point? (each poinr will have info of what edge they belong to? )
regina.leo (265) [Avatar] Offline
Re: chapter 13 ST_value
ST_DumpPoints like all the other geometry Dump functions returns a path and a geometry

In the case of a single linestring or multipoints, ST_DumpPoints path will be 1 dimensional. For multi linestrings and others its a bit more complicated.

Hopefully this little self-contained example answers your question:

SELECT edge_id, (dp).path[1] As index, ST_AsText((dp).geom) As ptwkt
FROM (SELECT 1 As edge_id
, ST_DumpPoints(ST_Segmentize(ST_GeomFromText('LINESTRING(1 2, 3 4, 10 10)'), 0.5)) dp
) As foo;