qgis – Using @row_number with Virtual Layers in QGIS

qgisvirtual-layer

I use a virtual layer that numbering and order features with their ID.
Their IDs are previously calculated by using @row_number in the field calculator.

So my workflow is to create ID with @row_number, then I can create the virtual layer.

My aim is to skip this manual step (filling ID by @row_number in field attribute) and managing row number directly in the virtual layer.

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)
)

In this case, I first have to create the id of the layer myLine l : this is the step I would like to skip. I would like my virtual layer works without the need of having id defined in the layer myLine l

 select p.*,
           l.id line_ID

Is there a way to do this?

Best Answer

You can use ROW_NUMBER() to generate a row number as long as there is some other field to use in the OVER parameter, doesn't matter if it's non-unique. Apparently, using geometry field works (tested using shapefile datasource), so you could try the following:

select *, ROW_NUMBER() over (order by line_ID, line_pcent) as new_ID 
FROM 
(
select p.*,
       ROW_NUMBER() over (order by l.geometry) as 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)
)
Related Question