Incremented index on polygons using virtual layer in QGIS

Question

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?

Answer 1

I understand you don't want the computed ID to be on the line but rather on the polygon.

You can adapt the query to select the polygon attributes (p.*) and to return all of them (*). If you don't want to include the line % and line id, you can replace * by a list of polygon attributes, including geometry (ex: select geometry, poly_id, poly_attribute2 ...)

select *, ROW_NUMBER() over (order by line_ID, line_pcent) as new_ID 
FROM 
(
select p.*,
       l.id line_ID,
       ST_Line_Locate_Point(l.geometry, st_startpoint(st_intersection(l.geometry, p.geometry))) as line_pcent
from myPolygon p
 join myLine l
 on st_intersects(l.geometry, p.geometry)
)