Adding row numbers by field in QGIS

Question

I have a point layer of features of interest that covers around 200 river catchments (each river catchment has between 50 and 100 associated points). I have joined attributes from the river catchments polygon shapefile to my points layer, so that each point is labelled with the river catchment name in which it falls. I would now like to add sequential row numbers by field to my point file, so that numbering starts at '1' for each river catchment.

I can select by each river catchment and use the @row_number to add sequential numbering, but it's laborious.

Is there a quicker, more automated way of doing it?

Answer 1

Use this expression to create increasing numbers separately for points belonging to each river (see below for a variant if points are in an arbitrary order along each river):

array_find(
    array_agg( $id, rivername),  -- replace rivername by the name of the river layer
    $id
)+1

Explanation: array_agg() aggregates the id of all features of the point layer that belong to the same river in an array. The function array_find() then get's the index (position) of this id inside the array. As the first element starts with 0, add 1.

The expression used as dynamic label for the points:

Variant

If points are randomly distributed and not created in the correct order along the river, you can't use the $id to get an increasing order along the river. In this case, use this solution that works indifferent of the creation order of the points, using the funtion line_locate_point:

with_variable(
    'dist',
    'line_locate_point( overlay_nearest (''rivername'',$geometry)  [0],$geometry)', -- change rivername to the name of the line layer
    array_find(
        array_sort (
        array_agg( 
            eval(@dist), 
            line  -- attribute name in the point layer of the river the feature belongs to
        )
    ), 
    eval(@dist)
    )+1
)

Black label: $id/creation order of the points; red label created dynamically by the expression above: