3

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:

relation_Aggregate(
relation:='dist',
aggregate:='concatenate', 
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. – Gabriel De Luca Dec 1 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? – MAP Dec 1 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? – Gabriel De Luca Dec 1 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. – MAP Dec 1 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. – Gabriel De Luca Dec 2 at 23:52
5

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.

1


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).

2

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.

3

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.

4

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 (
  SELECT
    a.waysigns_fk,
    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
)
SELECT
  a.fid,
  a.name,
  b.label,
  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.

5


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

6


Notes:

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! – MAP 2 days ago
  • 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'... – MAP 2 days ago
  • 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. – Gabriel De Luca 2 days ago

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.