2

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?

  • 2
    You may need to process your CSV to produce a new CSV with a single line for each class with a single value in each class field. Once you've processed your CSV to a better format, then you can do a one-to-many join. Multi-value fields are a bad idea for databases in general, and no good for joining. – Son of a Beach Apr 8 at 5:22
5

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

enter image description here

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:= ','
    )

enter image description here

Both gives following result: enter image description here

  • Thanks for the solutions Pieter, appreciate the effort you've gone to explain the process. I'll give each solution a crack to see how they go. Cheers. – Darren Apr 17 at 23:47
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.