I would like to use a virtual layer that numbering a layer of polygons. This numbering should be incremented along another layers of polylines. Example:
- polyline 1 :start_point = Index : 0 ; end_point index = 8
- polyline 2 : start_point = Index : 9 ; end_point index = 16
- polyline 3 : etc.
At the moment, I use a virtual layer that create polylines with the numbering expected :
select geometry, ROW_NUMBER() over (order by id, lpcent) as ID FROM ( select st_intersection(l.geometry, p.geometry) as geometry, l.id, ST_Line_Locate_Point(l.geometry, st_startpoint(st_intersection(l.geometry, p.geometry))) as lpcent from myPolygon p join myLine l on st_intersects(l.geometry, p.geometry) )
The next step on my workflow was to match the attribute of the new polyline numbering, by location, to the polygons layer.
I now need to skip this step, and get directly polygons with the numbering as result.
Can I do this into the same virtual layer?