Adding unique ID for consecutive groups using Field calculator in QGIS

Question

I found a similar question on this site and the answer suggested:

array_find(
    array_distinct(
        array_agg("PortCluster_ID")
        ),
        "PortCluster_ID"
    ) + 1

but using this expression gives me the following result (group1)

What I want to do is have what I wrote in white color on the attached image. As you can see I have consecutive "PortCluster_ID"s ordered in time, and i have a group of 119, then a group of 111 and then a group of 119 again, for this second group of 119 I want to have a new "ID".

What this data represents is fishing trips in and out of ports, some trips end in the same port (same "PortCluster_ID") but are obviously a different trip (ordered in ascending time).

Could you perhaps look at a way of classifying the data (refining it) by the Timestamp, and then use the methodology you have now to create the grouping? I am not sure what the timestamps are, and what constitutes a "trip" but refining the data a little may allow for your current method to work. — Keagan Allan, Dec 06 '21 at 02:17
Couldn't I use the same expression stated in my question but conditioned upon @row_number? So for example, this new expression or python code would see that it is again 119 and it would "want to group it to the same group as the first 119 group" but because in the previous row the PortCluster_ID was different,111, it would group this set of 119s to group 3. So i would have to modify my statement in such a way that it would check the previous row as well? But I do not know how to do that. — Peter Mastnak, Dec 06 '21 at 08:13
@Taras how to realize this specific task with Add autoincremental field? The tricky part is to group fields, partially based on PortCluster_ID, partly on GpsTime. — Babel, Dec 13 '21 at 08:44

pigreco · Accepted Answer · 2021-12-12T17:56:33.070

5

create an ID field following the GpsTime field, then create the group1 field and populate it with the following expression (can be improved):

with_variable('cucu',
   aggregate( 
   layer:=@layer_name,
   aggregate:='array_agg',
   expression:=
   (array_find(array_agg("ID", "PortCluster_ID"),"ID"))-"ID"),
with_variable('cucu2',
   (array_find(array_agg("ID", "PortCluster_ID"),"ID"))-"ID",
   array_find(array_distinct(@cucu),@cucu2)+1))

the scheme used:

sample data: https://gist.github.com/pigreco/48cf061c183809b0e69628a13493f771

edited Dec 12 '21 at 17:56

answered Dec 12 '21 at 13:28

pigreco

4,460
1
15
32

2

Works! And easier than my solution. – Babel Dec 12 '21 at 13:29
2

Which software did you use to create this awesome screenshot? – Babel Dec 12 '21 at 18:01
2

https://www.screenpresso.com/ – pigreco Dec 12 '21 at 18:27
1

Works! Thank you! – Peter Mastnak Dec 17 '21 at 10:05
if that's ok for you, please mark it as an answer – pigreco Dec 17 '21 at 11:40

Babel · Answer 2 · 2021-12-12T14:45:06.703

The basic idea

You want to create groups, based on an attribute value, but to assign different group-ids to the same values based on an order value (time) of another field.

To use this order, it's easier to first convert the datetime field to an integer id field.
The basic idea now is to check for each feature if the next feature in the defined order has the same value or not. If it has not the same value, it is the last feature of the group, the top feature. Assign a value to it - easiest one just take its id value.
In a next step, assign the same id value to all other features of the same group if they are before this "top" feature, based on the order.
Finally, convert the non consecutive group id values to consecutive numbers, starting with 1.

How to implement it

To do so, use QGIS expressions and create four new attribute fields, one for each of the steps described above, using Field calculator.

Create a new field called id that represents the order number based on your GPSTime field. Use this expression: array_find (array_sort (array_agg( "GPStime")), "GPStime")+1

Create a new field called top. It creates an entry, based on the id of the feature, for the last element (feature) of the groups you want: the highest / last element before the next group starts. Use this expression:

 array_get (
     array_foreach (
         generate_series (0,array_length ( array_agg ( "PortCluster_ID" ))),
         if (
             array_get (
                 array_agg("PortCluster_ID"),
                 @element
             ) = 
             array_get (
                 array_agg ("PortCluster_ID"),
                 @element+1
             ) = 0 or id = array_max (array_agg( id)),
             @element+1,
             ''
         )
     )
     ,id-1
 )

Create a new field group that assigns the value of the groups last feature (created in step 2) to all other features of the same group. Use this expression:

 if (
     top is NULL, 
     array_min (
         array_filter( 
             array_sort ( 
                 array_agg (top, filter:=top >0)
             ),
             @element > id-1
         )
     ),
     top
 )

Now create another field count that converts the group-values to incrementing numbers starting with 1. Use this expression:

 array_first (
     array_foreach (
         array_distinct (
             array_agg (group)
         ),
         array_find (
             array_distinct (
                 array_agg (group)
             ), 
             group
         )
     )
 )+1

See the results for each step in the corresponding attribute fields. The last column returns the values you're looking for, groups are highlighted in blue/orange:

Adding unique ID for consecutive groups using Field calculator in QGIS

2 Answers2

The basic idea

How to implement it

Linked