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

enter image description here

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

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.

enter image description here

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

2 Answers 2


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', '',    
                    -- loop through the sighted species
                        -- check if the sighted species appears in the reference array
                            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
                , ', '

enter image description here


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.


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

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.