QGIS – Creating Virtual Layer (Points to Line) Refering to Specific Rows in Attribute Table

points-to-lineqgissqlvirtual-layer

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".

enter image description here

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

with RECURSIVE rec(geometry,Name,FromNode,ToNode,Startheight,Gradient,Length,Endheight) AS (
    select a.geometry, a.Name, a.FromNode, a.ToNode, a.Startheight, a.Gradient, a.Length, 
          a.Startheight - (a.Gradient * a.Length) AS Endheight -- compute 1st segment end height
    from segments a
    where a.Startheight > 0 --start the computation where a start height is defined
UNION ALL
    select b.geometry, b.Name, b.FromNode, b.ToNode, 
           a.Endheight as Startheight, --Previous end is current start
           b.Gradient, b.Length, 
           a.Endheight - (b.Gradient * b.Length) AS Endheight --current start height is not defined yet, use previous end height
    from  rec a,  segments b
    where b.Name = a.ToNode
          and b.Startheight = 0 --stop the recursion when a segment in the path has a start height
)
SELECT * FROM rec;

If you want to skip creating the segments layer first, you can embed it in its own CTE:

with RECURSIVE 
segments AS (
  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),
rec(geometry,Name,FromNode,ToNode,Startheight,Gradient,Length,Endheight) AS ( ...
Related Question