According to this question: Get Attributes from 1:n relation in QGIS as Expression to use in Atlas I try to bulid waysigns for a hiking-GIS using the QGIS Atlas. The user should be able, to choose the destinations on the way sign.

I need to get only the attribute values from the relation "dist", which are written in a list ("wanted_dest"). As you can see at the screenshot below, at the time the way signs are labeled with all their destinations using this relation_Aggregate-expression:

expression:=concat("destination_id", ': ',round("network_cost"/1000,1), ' km  '), concatenator:=',')

enter image description here

But I don't want them all - I just wan't the destinations, of every list item (Place D, Place E and Place F) so I need to modify the aggregate or use a virtual layer. But I can't find a working solution.

Here is the used project for testing: http://www.map-site.de/tmp/WaySigns.zip

  • The relation dist has Places A to E linked to that Way Sign. It can be seen in the form. If you need that relation for other purposes, create a new many to many relation which expresses the Wanted Destinations instead of all Destinations for each Way Sign. Or you can just label the Way Signs with the values of the "wanted_dest" field instead of the relation items. Dec 1, 2019 at 15:58
  • Sounds like, you got into it! But I just got stuck... Could you expand your comment as answer? Do you need more information?
    – MartinMap
    Dec 1, 2019 at 17:07
  • I have a lot of other work to do but I like this problems. Yes, I need more information: I don't know how your dist relation is defined (seems to be defined between OD-Matrix as child and Way Signs as parent?). Also I don't know the Destinations attributes. Do you need the dist relationt just for this labelling purpose? Dec 1, 2019 at 17:14
  • 1
    All you need is on the end of the question. I uploaded the test-project ;-) Look at the print layout - I use the atlas to create the waysigns for printing.
    – MartinMap
    Dec 1, 2019 at 17:32
  • I hope I have not misunderstood the problem. How does the user select the destinations and how is the list written? If either of the two procedures could be replaced by writing rows in a double dependency table, then the SQLite solution provided could work. If the data must necessarily be read from the list provided in the question, probably a python approach that reads the list and makes the queries would be more appropriate. Dec 2, 2019 at 23:52

2 Answers 2


We are going to create a new table that represents the many-to-many relationship, between the way signs and the destinations, in which the user can create the combinations between both of them.

Then, in a virtual layer, we will dinamically reproduce all the waysigns with an additional field that contains a concatenation of each chosen destination, and the total cost to it.

The constraints that define the new relationship, and the virtual layer, will be saved within the project and managed by QGIS.

Both the normal labeling and the layout item will be done from this virtual layer.

Create a new temporary scratch layer and save it. I will name it waysigns_destinations.

In the new layer, create two integer type columns for the foreign keys. I will name them: waysigns_fk and destinations_fk.

Then, create two relations in the Project Properties window, Relations tab.
For each relation: the parent layer is Way Signs or Destination; the parent field is fid; the child layer is waysigns_destinations; and the child field is waysigns_fk or destinations_fk.


Now, define the relation in the parent's layer widget. The cardinality is not Many to one. For the Way Signs widget, it is Destinations (fid).


Do the same for the Destinations layer.

In the waysigns_destinations layer widgets I am going to ask only that a NOT NULL restriction be respected.


Note that the expression to display in the widget will be the name, not the fid. However, remember that the field is linked to the fid, so that is the real value saved in the attribute.

Now, we can start to create some features.


Save the changes and get ready that here comes the magic.

Relationships are just a set of restrictions and triggers that apply to the fields of the tables, so that they retain referential integrity. That is, you can not store a value that does not meet the indicated reference.

This referential integrity is required so that the queries that combine several tables have the desired result.

We will create, through a Virtual layer, a query that involves all four tables, and hopefully everything works fine.

WITH magic AS (
    group_concat(d.destination_id || ': ' || round(d.total_cost,1) || ' Km', ' <\n> ') label
  FROM waysigns_destinations a
  INNER JOIN "Way Signs" b
    ON a.waysigns_fk = b.fid
  INNER JOIN Destinations c
    ON a.destinations_fk = c.fid
  INNER JOIN "OD-Matrix" d
    ON (b.name = d.origin_id AND c.Name = d.destination_id)
  GROUP BY a.waysigns_fk
  a.geometry geom
FROM "Way Signs" a
LEFT JOIN magic b
ON a.fid = b.waysigns_fk;

First, we are taking all the waysigns_destinations rows and joining them with the Way Signs, Destinations and OD-Matrix matching rows.
For that table, we are aggregating a concatenation of the destination name and the total cost, with some characters, grouped by signways_fk. Then we are taken all the Way Signs rows and matching them with these magic rows.
For that table, we are taking the way sign fid, the way sign name, the concatenation and the way sign geometry.


I would think that you are not going to have problems in labeling or creating the map item you want from this new table.



The relationship is not the most important: the table that relates the way signs to the destinations could be filled out by hand, simply taking the precaution of placing valid values (the drop-down lists or expressions in the widget could help).
GeoPackage have some limitations in the implementation of foreign keys. QGIS seems obstinate in bringing the PRAGMA foreign_keys value to False for the database contained in the GeoPackage file.
So I did not include the establishment of foreign keys. The implementation of triggers in its replacement exceeds my knowledge, even so it does not seem necessary at this time (I leave that investigation pending).
If the relation provided in this answer brings you problems, you can get rid of it.

A reading discovered:

  • Great! I will check this!
    – MartinMap
    Dec 4, 2019 at 6:50
  • I have to tell you, that your answer is not exactly what I was expecting, but it's so well explained, that it is a bounty worth! There must be a shorter and more simple 'expression-way'...
    – MartinMap
    Dec 4, 2019 at 18:39
  • Yes, must be a shorter expression way. I was thinking in that. If I see that I can find that expression, I will write it in another answer later. Dec 4, 2019 at 20:03

The Expression Way

I see no way to query the features of the OD-Matrix layer by matching two attributes at the same time. So I propose the creation of a new combined key field with the origins and destinations in that table.

Having each feature of the OD-Matrix layer unequivocally identified, we can access those rows with the combination of Way-Signs and Wanted Destinations, from the Way Signs layer.

I don't know if it's simpler to do it through an expression than by creating a virtual layer based on an SQL query, but it's another option.

As soon as I wanted to add a virtual field in the OD-Matrix layer, a succession of "Invalid Relation" messages were issued from the panel, so I dared to export all layers to a new GeoPackage and create a new QGIS project without the addition of relationships.
In this process I changed the name of the layers, consider that change when importing the expression into your project.

Create a new field in the od_matrix layer, with the following expression:

"origin_id" || '-' || "destination_id"

I named it od. The expression concatenates the content of those fields with a hyphen in the middle. This new field will be used as a combined key to search for features.


Now, create a new field in the way_signs layer, with the following expression:

            array( '{', '"', ', ', '}'),
            array( '', '' , ',' ,''))),
        @element <> ''),
      "name" || '-' || @element),
      ': ',
      ' km')),

Is not easy to explain, but you will know what are we doing there:

We are taking the value of the wanted_dest field and replacing some remaining characters, so that they are left in a way that can be translated into an array. Immediately followed we convert it to an array.

We filter that array so that it does not have non-null but empty elements.

We create a new array with one element for each concatenation of the name attribute of the same row, a hyphen and each of the wanted_dest elements.

We use that array to search features in the od_matrix.

For each element we return the destination_id value and the total_cost value.

We transform costs from meters to kilometers, and concatenate with more characters to create a multiline string.

I named that field: label


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.