Expression for assigning ID by grouping and ordering polygons in QGIS

Question

To solve a problem (details see here) I am looking for an expression in QGIS (3.16) that creates a new field and should achieve this:

1. On a polygon layer, it should group together all polygons that have the same attribute-value
2. For each group, it should order the containing features according to some value, let's say for demonstration purposes the area ($area)
3. It should assign kind of an auto-increment value to the features of each group (smallest gets 1)

The result would look something like this:

I tried different combinations of aggregate and arrays, however, I'm not sure where to start and how to combine: how do I create an expression that creates such a new value?

Answer 1

You can achieve this using a "Virtual Layer", which you can then export if you need to persist the values.

Go to the menu Layer > Add Layer > Add/Edit Virtual Layer... and enter the following query.

select ROW_NUMBER() OVER(PARTITION BY category_field_name order by st_area(geometry)) as newID, *
from myLayer

So, it takes all fields (*) from myLayer and creates a new field "newID".

This ID is the current row ID, that is grouped by category_field_name so it restarts at 1 for every group. At last, the rows are ordered by area ascending.

Answer 2

from PyQt5.QtCore import QVariant
from qgis.core import QgsFeatureRequest

layer = iface.activeLayer()

original_field = "group"

# order by group
request = QgsFeatureRequest()
clause = QgsFeatureRequest.OrderByClause(original_field, ascending=False)
orderby = QgsFeatureRequest.OrderBy([clause])
request.setOrderBy(orderby)

current_group = None
last_group = None
new_field = "group_id"

if target_field not in layer.fields().names():
    new_field = QgsField(new_field, QVariant.Int)
    layer.dataProvider().addAttributes([new_field])
    layer.updateFields()

with edit(layer):
    for f in layer.getFeatures(request):
        current_group = f[field_name]
        if current_group != last_group:
            id = 1
        else:
            id += 1

        last_group = current_group
        f[new_field] = id
        layer.updateFeature(f)

Answer 3

Inspired by @GabrielDeLuca's answer:

array_find(
    array_reverse(
        array_agg(
            "id",
            group_by:="Group",
            order_by:="area"
            )
        ),
    "id"
    ) + 1