QGIS: Compare two concatenate fields and add similar strings to new column

Question

I am doing research on how planned improvements in urban green will likely affect biodiversity using QGIS. Each form of urban green has a list of so-called references species, species that are important for the health of that particular green space. I also have a list of the species that have been sighted in and around that area [see image, it's mostly in Dutch].

RefSpecies is copied from an Excel, the sightings have been extracted using the field calculator expression below, which extracts the name of the species that are within a polygon:

aggregate('NDFF_Centroids_5Km',  'concatenate_unique', "soort_ned",filter:=intersects($geometry,geometry(@parent)),
concatenator:=',') 

The NDFF_Centroids_5Km refers to the flora and fauna sightings data, soort_ned refers to the kind of species.

My aim is to create a new column with lists that only include those species that have been sighted and are also present in the list of references species. This could be either a list of the species' names or just a single number representing the amount of sighted species that are present in the list of reference species.

I am not an QGIS-expert, and I have never worked with lists or concatenations in QGIS. I have created a simplified version to show what I mean. The column I want to create is Sighted_RefSpecies.

I am hoping someone knows how to do this and I'm open to other suggestions, such as not using lists or concatenations.

Answer 1

It can be achieved by converting the RefSpecies and Sightings strings to arrays and checking whether each element of the Sightings array appears in the RefSpecies array:

-- create arrays of the `RefSpecies` and `Sightings` values (the delimiter is a comma and space to reflect the formatting in the original string)
with_variable('ref', string_to_array(RefSpecies, ', '),
    with_variable('sighted', string_to_array(Sightings, ', '),
        
        -- declare an empty string as a variable ready to receive the matching species
        with_variable('sighted_ref', '',    
            
            array_to_string(
                array_filter(
                    -- loop through the sighted species
                    array_foreach(
                        @sighted,
                        -- check if the sighted species appears in the reference array
                        if(
                            array_contains(@ref, @element),
                            -- if it does, concatenate it to the `@sighted_ref variable
                            concat(@sighted_ref, @element),
                            -- if not, concatenate `absent` (could also be an empty string, but `absent` is clear)
                            concat(@sighted_ref, 'absent')
                        )
                    ),
                    -- filter out all the `absent` elements from the array
                    @element != 'absent'                
                )
                -- convert the array to a string using a comma and space as the delimiter
                , ', '
            )
        )
    )
)

Note:

This approach requires that the RefSpecies and Sightings columns are consistently formatted. I.e., comma + space between each item (or just comma as I notice in your 'real' data), and the spelling and capitalisation are the same. If these things vary, you might need to introduce some fail-safes, such as converting everything to lowercase, or using some regex to do the matching.

Answer 2

Use field calculator to:

1. Create your custom function (Function editor tab):

   @qgsfunction(args="auto", group='Custom')
   
   def myFunc(f1, f2, feature, parent):
   
       l1 = [x.strip() for x in f1.split(",")]    
   
       l2 = [x.strip() for x in f2.split(",")]
   
       return ', '.join(list(set(l1) & set(l2)))
   

2. Fill Sighted_RefSpecies field with value (Expression tab)

   myFunc("RefSpecies", "Sightings")