1

I'm working with QGIS I have two layers:

  • Layer 1: has the id and geom columns (multypolygon layer)
  • Layer 2: has the id, gid and type columns (table layer).

The value gid of layer 2 is the foreign key and it is related with id of layer1, so for each id of layer 1 I have many gid of layer 2. I added the relation also in the Qgis project properties. enter image description here

Now I need to do a rule-based symbology based on this relation 1:N. In layer 1 I want to show a different colour based on Type column of layer2. I suppose I have to do a rule-based for each Type but I don't know the expression to use.

I tried relation_aggregate(relation:='gid_fk', aggregate:='array_agg'("Type", group_by:="Type", filter:="xxx"), expression:="Type") but probably it is not the solution and there is a syntax error.

I try to do a more specific example: layer1 with id=0 is a geometry, layer2 gid0-Type1 is an economic activity and in gid0-Type2 is a touristic accomodation. So I have more information related to one geometry. I was wondering if i can assign a rule-based symbology using the relation_aggregate or if I should create a virtual layer with overlapped geometry, in that case I would obtain 1:1 and no problem of symbology.

2
  • How do you want to choose which type to use? For example you have only one id 0 in layer1 but 2 gid 0 in layer2. You cannot assign two symbologys on one feature.
    – MrXsquared
    Mar 2, 2021 at 16:19
  • I try to do a more specific example. Layer 1 with id=0 is a geometry, layer2 gid0-Type1 is an economic activity and in gid0-Type2 is a touristic accomodation. So I have more information related to one geometry. I was wondering if i can assign a rule-based symbology using the relation_aggregate or if I should create a virtual layer with overlapped geometry, in that case I would obtain 1:1 and no problem of symbology.
    – ali_zaga
    Mar 2, 2021 at 16:58

2 Answers 2

0

For the usecase you have now added, I recommend using a virtual layer. Create one with this query:

select 
layer2.gid, 
layer2.Type, 
layer1.geometry 
from layer2
left join layer1
on layer1.id = layer2.gid

Now you can setup a point displacement symbology on the virtual layer and categorize it on the type of layer2:

enter image description here

You will now have duplicated points from layer1, each having a differnt type from layer2 as attribute. Example result:

enter image description here

0

This expression will return an array of all types in layer2 for the current id in layer1:

array_foreach(
 array_filter(
  aggregate(
   layer:='layer2', -- grab values from layer2
   aggregate:='array_agg', -- create an array from the layer2 values
   expression:="gid"||'_'||"Type"), -- this array should contain layer2 gid followed by an underscore followed by layer2 type. Kind of immitating a dictionary: gid is the key and Type the value
  regexp_substr(@element,'[^_]*')="id"), -- filter the array, so we only keep the values in the array, which match the current id of layer1. In dictionary speach it would say: keep all values, where the key is equal to the current layer1 id
regexp_substr(@element,'_(.*)')) -- remove the key and only keep the value

So for example on id=0 it will return ['Type1','Type2']. Or on id=1 it will return ['Type1'] and so on.

Since you did not specify, which type you want to use if there is more than one, it is now up to you which one to choose. You can turn the result into a string by using array_get(), array_to_string() or similar functions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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