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.

enter image description here

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,
       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?

1 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 
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)
  • hello, it works but I have to complete ID column of myLine. I use to fill this colum using row_number function, but it adds a previous step. Is there a way to add this function into the virtual layer?
    – zlikotp
    Sep 13, 2022 at 13:34
  • 1
    Do you mean you don't have a line id and want to generate one? sure, but the result could be ordered in an unexpected manner... to follow your screen shot and order from right to left, replace l.id line_ID, with st_maxX(l.geometry) line_ID, and order by line_ID, line_pcent with order by line_ID desc, line_pcent asc
    – JGH
    Sep 13, 2022 at 14:12
  • I mean, myLine doesn't have id field. I tried with the change you suggested me, but it seems it doesn't work : the 1rst line has the max number, the second line has the 1rst number, and the third line has the third number.
    – zlikotp
    Sep 13, 2022 at 16:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.