1

In QGIS I have:

  • a point layer;
  • a table;
  • a relation (Project > Properties > Relations) 1 to many between the points and the table;

What I need to do is to set a graduated symbology on the layer points using the related data stored in the table. You can think of the layer points as representing the offices of a company, each one with a unique id, and the records in the table as the persons working in the different offices. I'd like to set the size of the points depending on the number of persons for every office. Following this answer, I used the db-manager, run a sql query, upload the query results in the project and made a join with the point layer. I tried to use the expression builder with aggregate(), but in order to get the result I'm looking for I think I should also use count() and then group_by, and in my knowledge this is not allowed in aggregate().

Is it the db-manager the only possible solution in such cases of 1 to many relations?

1 Answer 1

4

Yes, there is an easier way using expressions.

In Layer Styling select the expression builder next to the attribute dropdown (circled red in image below). (For the example expression using aggregate() that I explain below, the layer I would be styling here is 'offices', not 'tree_pt' as shown in the image).

enter image description here

The you can build your expression. This will depend very much on what you are trying to do. But for instance, for something similar to the example you link to from your post you might need to use the aggregate function. Let's assume you have

  • a point layer "offices" with attributes 'fid' and 'office'
  • a table "people" with attributes 'fid', 'officeID' and 'name'
  • a relation (1 => n) between "offices".'fid' => "people".'officeID'

And you want to style "offices" based on the number (count) of "people" for each office.

Once in the expression builder dialog (See image below), you can search for functions in the middle pane. When you select a function, its syntax is shown in the right hand pane. If you read the syntax notes for aggregate carefully (always a good tip) then you will see that for the filter argument, unless otherwise stated, fields and geometery are from the joined layer, i.e. in this case 'people'. The source feature is accessed using the @parent variable. So, following this syntax, and in order to 'count' the number of people per office, the expression would be

aggregate(
     layer:='people', 
     aggregate:='count', 
     expression:='fid',
     filter:="officeID"=attribute(@parent,'fid')
)

enter image description here

As an alternative example, you might be styling the child layer based on an attribute value of the parent layer. To do this you could use get_feature() to return the parent feature, and nest this inside of attrbutes() to access its attributes. This might look something like: attributes(get_feature('parentlayer','parentlayer_primarykey',"childlayer_foreignkey"))['parentattribute']

In the above example, get_feature() returns the first feature on the layer named 'parentlayer', where the attribute named 'parentlayer_primarykey' has a value equal to the value of the attribute 'childlayer_foreignkey'. (I use the terms 'primary key' and 'foreign key' loosely here - they only need be the primary key or foreign key for your current purpose). This is then nested into the attributes() function, with attribute to be returned defined at the end, in this case ['fid'].

5
  • Ok, thank you for the answer; but that didn't worked (maybe it was my fault). You're right when you say that aggregate() would be needed for such an example. But when I tried to use it I also needed to use the count() and group_by. Which in my knowledge it's not supported within agggregate() (only order_by). That's why I had to use the db-manager to bypass this issue. Is it correct or have I improperly used the expression builder?
    – HyPhens
    Jun 21 at 14:58
  • You need to provide a full explanation of what you are trying to do.
    – Oisin
    Jun 21 at 15:35
  • I've expanded the question, hope it is clearer now. Thanks
    – HyPhens
    Jun 22 at 14:43
  • I've expaned my answer to address your specific case. See example using aggregate() function.
    – Oisin
    Jun 23 at 10:47
  • Yes, now it works and you're right, there's also an example in the syntax notes. But now it's much clearer, thanks. I will delete these last comments in the next days.
    – HyPhens
    Jun 23 at 14:42

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.