QGIS Relations

Imagine the following scenario: You are maintaining a cities wastewater network. Of course you are doing this in QGIS. You have a layer with all the reaches (pipes) which are connected to houses and make sure, that whatever flows down the sink of these buildings will end up in the clarification plant. You have a layer with the geometries (lines) of the reaches, which also has some attributes for width, material and a unique id (which acts as primary key). That's easy so far.

Foreign keys

But now imagine, that your reaches once in a while have planned maintenances or break unexpectedly and need to be repaired. So you want to also keep track of this work. The most simple solution would be to add another field to your reach layer and have a some kind of embedded CSV. E.g. comma- and semicolon-separated. date,responsible_person,company,note;2013-05-06,"Hugo","ABC","-";2013-03-27,"Hugo","ABC","Found my wife's lost marriage ring". Sounds like a pain to maintain, doesn't it?
For things like this, database people have come up with the solution of foreign keys. A new table maintenances will be made with the fields date, responsible_person, company, description and another field fk_reach which acts as a foreign key (and if you have a database, you will probably want to define this constraint on it).

This field fk_reach will always contain an id of a reach. It can be seen like a pointer to the reach it belongs to.

What's so interesting about that? Because we now have a new layer with only the maintenances, we can design a custom edit form for a maintenance. So you don't have to take care of formatting your CSV and take care of commas, semicolons etc.

QGIS will help you with this setup. It does work across the boundaries of different providers (so you can make use of it with shapfiles and CSV files, as kind of a poor-mans-database) and the only thing you have to do is to tell QGIS the relations between your tables.

Layers

Note: QGIS has no difference between a table and a vector layer. Basically, a vector layer is a table with a geometry. Any table needs to be added as a vector layer.

We will work with the following layers. The layer reaches has the geometry, while the layer maintenanaces is a data-only table and has a foreign key to reaches. This means, that each maintenance is belongs to exactly one reach while each reach can have any number of maintenances (A classical 1:N relation).

Layers

Reach layer

A layer of reaches
Reaches

Maintenance layer

A layer of maintenances. Note the field fk_reach, which is actually a foreign key, pointing to the layer reaches
Maintenances

Definition (Relation Manager)

The first thing we are going to do is to let QGIS know about the relations between the layer. This is done in the Project Properties. Open the tab Relations and click on add.

  • The field name is going to be used as a title. It should be a human readable string, describing, what the relation is used for. We will just call say "Maintenances" in this case.
  • The field referencing layer is the one with the foreign key field on it. In our case this is the maintenances layer
  • The field referencing field will say, which field points to the other layer so this is fk_reach in this case
  • The field referenced layer is the one with the primary key, pointed to, so here it is the reach layer
  • The field referenced field is the primary key of the referenced layer so it is id
  • The field id will be used for internal purposes and has to be unique. You may need it to build custom forms once this is supported. If you leave it empty, one will be generated for you but you can assign one yourself to get one that is easier to handle.

Add relation dialog

Once created, the dialog will show your newly defined relation

Relation manager

Forms

Now that QGIS knows about the relation between the layers, it can use this knowledge to improve the forms it generates. As we did not change the default form method (autogenerated) it will just have added a new widget in our form. So let's select the layer reaches in the legend and use the identify tool on a reach. Depending on your settings, the form might open directly or you will have to choose to open it in the identification dialog under actions.

Relation table

As we can see, the maintenances assigned to this particular reach are all shown in a nice table. And there are also some buttons available. Let's review them shortly

  • The well-known pencil button is for toggling the edit mode. Be aware that it toggles the edit mode of the maintenances layer although we are in the feature form of a feature from the reaches layer. But the table is representing features of the maintenances layer.
  • The plus button will add a new feature to the maintenances layer. And it will assign the new maintenance to the current reach by default.
  • The x button will delete the selected maintenance. Permamently. You have been warned.
  • The chain symbol will open a new dialog where you can select any existing maintenance which will then be assigned to the current reach. This may be handy if you created the maintenance on the wrong reach by accident.
  • The split chain symbol will unlink the selected maintenances from the current reach, leaving them unassigned (the foreign key is set to NULL) effectively.
  • And last but not least, to the right, there are two buttons which switch between table view and form view where the later one let's you view all the maintenances in their respective form.

On the other hand, we also have the maintenances table. So what will happen if we open the feature form of a maintenance? The answer is nothing special by default, but... We have a new widget type available which lets us embed the feature form of the referenced reach on the feature form of the maintenance. So we will have to open the layer properties of the maintenances layer, switch to the Fields tab and change the widget type of the foreign key field fk_reach to Relation Reference.

Relation reference

If we look at the feature dialog now, we will see, that the form of the reach is embedded inside the maintenance form and will even have a combobox, which allows us to assign the current maintenance to another reach.

Relation reference form

Comments

Is this part of QGIS 2.1 (still in development)? It looks really good, I've been looking for such a solution for years. Thank you!

a very short message : this is awesome! thanks for this work.

Hi,

Do you think about implementing an automatic FK detection in the relation manager, for datasource which support it like PostgreSQL ? That would be a time saver !

And what about N:N relations ?

Vincent

Foreign key detection definitely is on the TODO list. Ideally, this implementation will include work in the dataprovider, so QGIS is able to group layers in databases and doesn't treat them as stand-alone sources.

N:N relations already work with the current implementation (just create an intermediate table with FK's to both linked tables and define them in the properties). This will probably be polished a bit (less clicks...) for future releases.

Very nice feature, thank you for posting. I have a small question. When linking/relating two vector layers (water-mains, water-laterals) have you thought about the possibility of inserting/modifying geometry of the child layer (water-lateral) from the parent layer (water-mains)? What options are there? Thank you, all the best, Tudor

There are some ideas concerning geometry modification from the feature form. One idea is, that a button will be introduced which will hide the form and leave you with digitizing tools on the map canvas. But I actually didn't think that much about it yet.

Excellent work! Congrats.

BUT it doesn't work on MSSQL - is this possible?

Regards, Todd

Hi Matthias, many thanks for this tutorial. One doubt: are relations working in QGIS master? I'm trying but at no avail. Thanks in advance!

Hi Giovanni,

The support for autogenerated forms was broken when I intruduced support for custom UIs. I just fixed it today.

Great new feature! I will certainly use it in my conservation management system. It will allow me to easily create and manage different actions for each land parcels.

I have created a simple local "plugin" to replicate the same action (referencing layer) for all selected feature in the parcels layer (referenced layer), but it was tied to two specific layers. With the new relations feature, I think I can adapt it to a more generic use, by reading the layers relations in the project. If so, I will publish it in the plugin repository.

Great, I expect that long ... That would be great if we could select reaches from a selection in the table maintenance ... am I clear? Currently I have to gone through spatialite as explained here: (sorry it's in french) http://archeomatic.wordpress.com/2012/12/04/qgis-qspatialite-sql-gerer-les-relations-de-1-a-n-1-a-plusieurs/ Coukld we expect this in the future development ?

I am not sure I understand your needs correctly. You would like to create a feature selection, based on a feature selection on a related layer. For example the following workflow:

  • Open the attribute table of the layer maintenances
  • Select a couple of maintenances
  • Right click and choose "Select related features => Reaches"

All is in the title... I select a couple in the child table and when i do a right-clic i don't have "selected related feature" but just "select all" or "open form".. i missed something but what ? can you bring me an image of this ? thanks and sorry for my bad english !

Sorry, that was intended to be a question from my side, but I forgot to add a question mark.

Is the draft workflow I have outlined what you would expect? Or did you have another use-case in mind? If yes, are you able to describe the steps you would like to be able to perform?

Hi Your example is substantially similar to what I want to do ..

Spatial entities (Reaches for you and archaeological remains for me ) that can match multiple records in a table (maintenance for you, archaeological layers for me).

I would like to select multiple records in maintenance table (the maintenance done on a certain date, for example) and be able to create a feature selection of reaches that have experienced these maintenance on the canvas.

After you select the maintenance that interest me, I would love to do a right click> "select related features"=>"reaches" and see my selected "reaches" appear (in yellow ;) ) on the canvas.

Can you show me a screenshot of the "select related features" option with your example ...

sorry for the inconvenience and thank you in advance.

Archeomatic, it is a very interesting idea, but this is not implemented yet. I think it would perfectly make sense to have such a solution, but before implementing it, I would like to think about it in a broader way (e.g. can we make an easy solution that also works for N:M relations)

Hi, it's a great improvement! I'm a forester and now I can connect my vector layer with forest parts with table of species and age in all forest layers (you know: big trees on top, smaller in middle, small trees and bushes on bottom of the forest). But what I really need is to be able to make question like this: QGIS, please show me on vector layer all forest parts with Pine older than 80 years in top layer. Make selection. Save selected as... And this new layer from selection should have table with added columns form related table. I hope you will understand what I need (sorry, my english is very poor). I'm curious if this is at all possible?

Does this feature support large relationship tables from a database. I tried to build a relationship between PostgreSQL tables where the related table has about 2 million rows (which has foreign key column indexed). However when I tried to do a feature info on the map canvas QGIS hung for 20mins and then crashed. Does it try to fetch and cache all of the relationship data, rather than do a index look-up each time?

Currently, a server-side index is not used. Filtering is all done locally, by fetching them and only accepting the ones matching the current filter (meaning: the ones with a foreign key to the current feature). There is not much caching done if at all, so I wonder, where the roots of this crash are. However, there is a bug report open meanwhile, so please refer to that if you have information or are interested in the status of this issue.

For a future release, I hope to be able to implement support for using server-side indexes. Anybody interested in funding (can also be partially) this feature, please contact me.

Is QGIS relations development completed entirely? I need this great feature in my local environment ASAP. Shall i compile the QGIS master to get this feature today itself?

It is part of QGIS master. For a lot of environments, packages are out there already, called master, nightly or testing (this is all the same). You can have a look at the QGIS download page where some sources of testing builds are listed. For Windows, there is OSGeo4W which offers nightly builds. Of course you can also compile master yourself, or wait until 2.2 is released (scheduled release date: 21.2.2014)

Really big feature! Thanks for all the work you put into this looks great. Just one question does it support nested one to many relationships? Looks like it would but thought I would check. Again thanks

If you are talking about layer A pointing to layer B and layer B pointing to layer C, and your the question is if you are able to click through to features on layer C, starting from layer A and vice versa... There should be nothing preventing you from doing so.

Looks interesting! Been playing with it in v2.2. Am I right that you can not use the attribute values from maintenance to create a map with labels or styles for the reach layer - for instance a map showing who is responsible for every reach section (I know there can be more than one and this is perhaps the answer to my question)? I mean like you could have done with 1:1 joined data.

You are right, at the moment there is only support inside the forms (data management). But it can be expected that this feature is going to be integrated in other parts of QGIS such as

  • Labels (as you said)
  • Attribute Table (could eventually replace joins)
  • Identify Tool
  • Print Composer
  • Expressions
  • ... You nameit ...

You also correctly pointed out that there can be more than one, so we will need to develop methods to deal with multiple attributes. This will require some kind of aggregate functions (min, max, mean, median, join( e.g.: join every related maintainers name with a semicolon) )

All this blog is very good stuff. Thanks for sharing your knowledge. I am a newbie to QGIS/Python. I have a spatialite DB with one geographic element and one table, connected by a 1-N relationship. I managed to make a custom form in QT Designer using no code. I was trying to make a datetime widget work but not possible until now, then i noticed that, in QGIS, when i open the fields tab and if I am using the autogenerated form, the fields match the spatialite type, but not when I do use my custom form, which I did using almost exclusively line edit widgets. I know I am missing the important stuff. But I do not know where to look. Is it that I rather program the form?? Thank you very much!!

I noticed that you said, "Note the field fk_reach, which is actually a foreign key, pointing to the layer reaches." Was that relationship already defined in your data (that each maintenance referred to a reach), or is this tool what defines it?

I'm making a new set of shapefiles and wondering if QGIS can relate them as I make them, or if I will need to do it through SQL afterwards.

Thank you.

For QGIS it doesn't matter if there's a foreign key constraint defined in the database. It's only the field that has to be there and that's it. I would define the constraints on database side for consistency reasons wherever possible. But that's a recommendation rather than a requirement.

Add new comment

profile for Matthias Kuhn at Geographic Information Systems Stack Exchange, Q&A for cartographers, geographers and GIS professionals