4

In QGIS I want to set up a n:m relation between two tables "parcels" (polygons) and "owners" (no geometry). Both share a field index1 (containing of numbers and letters) by which the relation can be made. One parcel can have multiple owners, one owner can own multiple parcels.

I followed the official QGIS documentary on that matter, but now I'm a little bit stuck creating the connection of the two GeoPackage tables. After creating a table Parcels_Owners with the one column (ID) I exectued the following SQL commands via Database-> DB Manager:

ALTER TABLE Parcels_Owner
   ADD COLUMN Parcels_fk text
   REFERENCES Parcels (index1)
   ON DELETE CASCADE
   ON UPDATE CASCADE
   DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE Parcels_Owner
   ADD COLUMN Owner_fk TEXT
   REFERENCES Owners (index1)
   ON DELETE CASCADE
   ON UPDATE CASCADE
   DEFERRABLE INITIALLY DEFERRED;

The table is created succesfully. But how can I fill it now with all the parcels/owner data in the right way?

My ultimate goal is to be able to search for all parcels owned by one owner and select them so they are highlighted on the map.

6
  • By using the relation (whose nature you didn't divulge to us, hence we can't be more specific)?
    – Erik
    Dec 2, 2021 at 11:37
  • I m not entirely sure about what more informations are vital. Strenght (=association)? Bot tables have a common "index1" field. anything else which might be important?
    – sn1ks
    Dec 2, 2021 at 12:08
  • 2
    If "One parcel can have multiple owners, and one owner can have multiple parcels" then you have a n:m relation not a 1:n (documentation : docs.qgis.org/3.22/en/docs/user_manual/working_with_vector/…)
    – J.R
    Dec 2, 2021 at 12:33
  • true that, i m sorry. i edited the question accordingly - how to slect all children of a parent
    – sn1ks
    Dec 6, 2021 at 10:44
  • You need to provide more information, usually a n:m relationship use a third table (pivot table) to map all possible pair between the two main table Do you have this table ? If not how is your data organised to account for the n:m relationship ?
    – J.R
    Dec 6, 2021 at 12:08

2 Answers 2

7
+50

A solution using the Select by Relationship plugin

I created three layers:

  1. parcels (vector, with polygon geometry);
  2. owners (table without geometry);
  3. rel_nm (table without geometry)

the diagram is below:

enter image description here

project relationships:

enter image description here

activate plugin and set it like this:

enter image description here

the attribute tables of the three layers

enter image description here

demo:

enter image description here

finally, open the two tables parcels and owners, select a parcel and the owner/s will be selected; select an owner and parcels will be selected.

for labels:

expression used:

array_to_string(
relation_aggregate(
relation:='rel1',
aggregate:='array_agg',
expression:= attribute(get_feature_by_id('owners',id_own),'owners')))

I am attaching data and project

download

the plugin must be activated every time the project is reopened, the bug has been reported.

2
  • thank you, this is exactly what i was looking for! much appreciated! One small follow-up question, in the plugin-settings, what does the "Active Parent Layer" option do?
    – sn1ks
    Mar 21, 2022 at 9:28
  • 1
    if I remember correctly, when you select a record of a child level, the parent layer is activated
    – pigreco
    Mar 21, 2022 at 9:31
1

Click the owners layer, use select by expression and type the id you want to sleect, like id=7 and the corresponding owners will be selected:

enter image description here

To get all names (or any other attribute) from the owners layer to the corresponding parcel feature, use this expression:

with_variable(
    'currentid',
    id,
    aggregate( 'owners', 'concatenate', name, filter:=id=@currentid, concatenator:=','))

enter image description here

2
  • this calculates a new field with all owner names in it,right? I want to select the owners (to export them to a spreadsheet etc) based on the selected parcels
    – sn1ks
    Dec 13, 2021 at 8:23
  • With the new field created, you can export the attribute table as a CSV that you can open in Excel etc. If you select parcels, you can check the box Only update N selecte features.
    – Babel
    Dec 13, 2021 at 8:35

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.