I have a point layer "Points" with the following attributes:
Name | FromNode | ToNode | Startheight | Gradient | Length | Endheight |
---|---|---|---|---|---|---|
14870040 | 14870040 | 14870055 | 39 | 0.005 | 57.51 | |
14870056 | 14870056 | 18880033 | 0 | 0.005 | ||
14870055 | 14870055 | 14870056 | 40 | 0.005 | 57.92 | |
14870057 | 14870057 | 14870040 | 0 | 0.005 | 65.35 | |
14870297 | 14870297 | 14870057 | 41 | 0.005 | 93.27 |
With the attributes I am able to generate a virtual layer as Polyline according to the "FromNode"/"ToNode" values:
select a.Name, a.FromNode, a.ToNode, a.Startheight, a.Gradient, a.Length, ''AS Endheight, makeline(a.geometry, b.geometry) as geometry
from Points a, Points b
where b.Name = a.ToNode
So line segments are generated where the start point of a segment is the same like the end point of the previous segment.
I'd like now to calculate the "Endheight" of the following segments based on the "Startheight" of the previous/first line segments (if "Startheight" is '0', else take "Startheight" here '41') and based on the other fields "Gradient", "Length".
The formula should look like: "Endheight" = "Startheight" – ("Gradient" * "Length").
The problem for me is to refer to the right row where "FromNode" = "ToNode".
Best Answer
There is no simple field to order the segments, so to know the "before" or "after" segment, one must recursively build the chain.
Also, let's note that a segment end height is the same as the next segment start height.
Create a layer/virtual layer of your segments, call is "segments"
Create a new virtual layer to recursively compute the missing fields
If you want to skip creating the segments layer first, you can embed it in its own CTE: