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.
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
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).
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.
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).
A layer of reaches
A layer of maintenances. Note the field fk_reach, which is actually a foreign key, pointing to the layer
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.
Once created, the dialog will show your newly defined relation
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.
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.
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.