Joining table with multiple-values-field in QGIS

Question

I have a vegetation vector layer with vegetation type mapping. I want to join this vector layer with a CSV-file (no geometry) which includes threatened species occurrence.

The vector layer has a "vegetation class" field which provides a single vegetation classification for each polygon. The CSV-file has a species list with a "Class" field which indicates all the vegetation classes (comma separated) the species is known to occur.

When I create a join between the vector layer and the CSV-table data, there doesn't appear to be anywhere for me to indicate the join field contains multiple values and I, therefore, receive NULL results when inspecting the join.

How can I indicate that the value from the target field ("vegetation class"), which is a single value, needs to search each row in the join field ("Class") which contains multiple comma separated values?

Answer 1

You can follow different ways to achieve this.

You can do this with SQL.

sollution 1

Go to Database > DB-manager > Virtual Layers > Project Layers

Open the SQL-window and add following code. Change 'vector_layer' and 'csv_file' to the appropriate layernames:

select v."vegetation class", c."species"
from vector_layer v 
join csv_file c 
where c."Class" like '%'||v."vegetation class"||'%'

This code creates a single row for every vegetatian class - species occurence.

• Check 'load as a new layer'
• Uncheck 'Geometry column'
• Give a layer name
• click load

Now you can define a relation to the newly created table. Go to project > Properties > Relations.

sollution 2

You can create an entire new layer, with everything added in the way you want.

Go to Database > DB-manager > Virtual Layers > Project Layers

Open the SQL-window and add following code. Change 'vector_layer' and 'csv_file' to the appropriate layernames:

select v.geometry , v."vegetation class", group_concat(c."species") as species
from vector_layer v 
join csv_file c 
where c."Class" like '%'||v."vegetation_class"||'%'
group by v."vegetation_class"

• Check 'load as new layer
• Check 'load as a new layer'
• Check 'Geometry column' and choose geometry
• Give a layer name
• click load

you can do it with a QGIS-expression

sollution 3

add a virtual field to your vector_layer with following expression:

either

 aggregate( 
    layer:='csv_file',
    aggregate:= 'concatenate',
    expression:= "species",
    filter:= "Class" like '%' || attribute(@parent,'vegetation class') ||'%',
    concatenator:= ','
    )

or

 aggregate( 
    layer:='species',
    aggregate:= 'concatenate',
    expression:= "species",
    filter:=  array_contains( string_to_array( "Class" ), attribute(@parent,'vegetation class')),
    concatenator:= ','
    )

Both gives following result:

Answer 2

I think what you are trying to do will not work. You cannot join a single value to a comma separated values. What you need to do is create a single csv table that has each value in a distinct row or better create an extra integer column or a code to match the single value to the csv. You might be better of creating a relation which allows you to create 1-M relationships. Can you please share the data. Maybe two rows from each table