3

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)

enter image description here

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

5
  • 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. Commented Dec 6, 2021 at 2: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. Commented Dec 6, 2021 at 8:13
  • @MrXsquared do you have a suggested answer? Commented Dec 6, 2021 at 12:23
  • Did you also try the "Add autoincremental field"?
    – Taras
    Commented Dec 13, 2021 at 8:41
  • 1
    @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
    Commented Dec 13, 2021 at 8:44

2 Answers 2

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

enter image description here

the scheme used:

enter image description here

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

5
  • 2
    Works! And easier than my solution.
    – Babel
    Commented Dec 12, 2021 at 13:29
  • 2
    Which software did you use to create this awesome screenshot?
    – Babel
    Commented Dec 12, 2021 at 18:01
  • 2
    screenpresso.com
    – pigreco
    Commented Dec 12, 2021 at 18:27
  • 1
    Works! Thank you! Commented Dec 17, 2021 at 10:05
  • if that's ok for you, please mark it as an answer
    – pigreco
    Commented Dec 17, 2021 at 11:40
3

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.

  1. To use this order, it's easier to first convert the datetime field to an integer id field.

  2. 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.

  3. 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.

  4. 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.

  1. 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

  2. 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
     )
    
  3. 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
     )
    
  4. 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: enter image description here

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.