I would like to number each polygon that is crossed by a polyline. For every polyline entity, the numbering has to be reset from 1.

I would like to keep the same logic of numbering incrementation used while digitizing the orignal polyline, defined by start_point (number = 1) and end_point. enter image description here

at the moment I used this code made by JGH that number polygon, but the numbering doesn't reset at 1 at every polyline entity :

    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 p
 join l
 on st_intersects(l.geometry, p.geometry)

1 Answer 1


Change the first line to

select geometry, ROW_NUMBER() over (partition by id order by lpcent) as ID FROM 

"For each line id: number it in the order of lpcent"

See SQL Window Functions

Your Answer

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

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