Importante

Translation is a community effort you can join. This page is currently translated at 39.22%.

16.4. Connecting and Editing Data Across Layers

Ability to connect data from different layers is one of the duties of a GIS software. Such a connection can be based on the spatial relationship between the features, or on their shared attributes. QGIS provides tools to handle any of these associations, such as:

Processing algorithms that can create a new layer as a result of the connection, namely Unir atributos por localização, Unir atributos por mais próximo, Unir atributos por valor de campo, …
SQL queries to create a new layer from the DB Manager or as a virtual layer
Joins properties or relations settings that temporarily extend attributes of features in a given layer, with those of features in another layer based on some matching attribute(s).

Joins and relations are technical concepts borrowed from databases to get the most out of data stored in tables by combining their contents. The idea is that features (rows) of different layers (tables) can be associated to each other. The number of rows which are matching each other can be of any value (zero, one, many).

16.4.1. Joining features between two layers 

Joins in QGIS allow you to associate features of the current layer to features from another loaded vector layer. Whether they are spatially enabled and the type of geometry do not matter. The join is based on an attribute that is shared by the layers, in a one-to-one relationship.

To create a join on a layer (identified below as target layer):

Go to the layer Properties ► Joins tab
Clique no botão | symbologyAdd | : sup: botão ʻAdicionar nova junção`. A caixa de diálogo: guilabel: ʻAdicionar junção de vetor` aparece.
Selecione o: guilabel: camada de junção que deseja conectar com a camada vetorial de destino
Specify the Join field (from the join layer) and the Target field (from the target layer). These are the fields that are used to find matching feature in both layers hence they should have values in common.
Pressione: guilabel: ʻOK` e um resumo dos parâmetros selecionados é adicionado ao painel: guilabel: Join.

../../../_images/join_attributes.png — Fig. 16.101 Una uma tabela de atributos a uma camada vetorial existente

The steps above will create a join, where ALL the attributes of the first matching feature in the join layer is added to the target layer’s feature. The following logic is used to pair features during a join process:

All the features in the target layer are returned, regardless they have a match
If the target field contains duplicate values, these features are assigned the same feature from the join layer.
If the join field contains duplicate matching values, only the first fetched feature is picked.

Nota

Joins in QGIS are based on a single field matching so most of the times, you would want to make sure that values in the matchable fields are unique.

QGIS provides some more options to tweak the join:

Cache join layer in virtual memory: allows you to cache values in memory (without geometries) from the joined layer in order to speed up lookups.
Create attribute index on the join field to speed up lookups
Dynamic form: helps to synchronize join fields on the fly, according to the Target field. This way, constraints for join fields are also correctly updated. Note that it’s deactivated by default because it may be very time consuming if you have a lot of features or a myriad of joins.
Caso a camada de destino seja editável, alguns ícones serão exibidos na tabela de atributos ao lado dos campos, para informar sobre o seu estado:
- : the join layer is not configured to be editable. If you want to be able to edit join features from the target attribute table, then you have to check the option Editable join layer.
- : the join layer is well configured to be editable, but its current status is read only.
- : the join layer is editable, but synchronization mechanisms are not activated. If you want to automatically add a feature in the join layer when a feature is created in the target layer, then you have to check the option Upsert on edit. Symmetrically, the option Delete cascade may be activated if you want to automatically delete join features.
Joined fields: instead of adding all the fields from the joined layer, you can specify a subset.
Custom field name prefix for joined fields, in order to avoid name collision

16.4.2. Setting relations between multiple layers 

Unlike joins that define a one-to-one link between features across two layers, relations help you build interconnections between multiple features across two or more layers. As such, relations are project level settings and are set in Project ► Properties ► Relations tab. From there, you can:

Add relation whose type can be:
- one to many relation
- many to many relation
- polymorphic relation that you can add or edit with the dedicated tools in the action drop-down menu.
Nota

There is no simple way yet to edit a non-polymorphic relation once it has been created. Only the name can be edited with a double-click. For any other parameters of such a relation you will have to remove and recreate it.
Discover relations: QGIS is able to discover existing relations from supported database formats (PostgreSQL, GeoPackage, ESRI File Geodatabase, …). This can be a good way to ease the relations definition.
Remove relation

../../../_images/project_relations.png — Fig. 16.102 Aba de relações

16.4.2.1. One to many (1-N) relations 

As an example you have a layer with all regions of Alaska (polygon) which provides some attributes about its name and region type and a unique id (which acts as primary key).

Em seguida, você obtém outra camada ou tabela de pontos com informações sobre aeroportos localizados nas regiões e também deseja acompanhá-los. Se você deseja adicioná-los à camada de regiões, você precisa criar uma relação de um para muitos usando chaves estrangeiras, pois existem vários aeroportos na maioria das regiões.

../../../_images/regions_with_airports.png — Fig. 16.103 Região do Alasca com aeroportos

Layers and keys 

QGIS makes no difference between a table and a vector layer. Basically, a vector layer is a table with a geometry. So you can add your table as a vector layer. To demonstrate the 1-n relation, you can load the regions and airports layers in the sample dataset. In practice, each airport belongs to exactly one region while each region can have any number of airports (a typical one to many relation).

which has a foreign key field (fk_region) to the layer regions.

In addition to the attributes describing the airports, the aiports layer has another field fk_region which acts as a foreign key (if you have a database, you will probably want to define a constraint on it). This fk_region field will always contain an id of a region. It can be seen like a pointer to the region it belongs to.

All you have to do is to tell QGIS the relation between the layers so that you can design a custom edit form for editing and QGIS takes care of the setup. It works with different providers (so you can also use it with shape and csv files).

Definindo relações 1-N 

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

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 airport_relation in this case.
Referenced Layer (Parent) also considered as parent layer, is the one with the primary key, pointed to, so here it is the regions layer. You need to define the primary key of the referenced layer, so it is ID.
Referencing Layer (Child) also considered as child layer, is the one with the foreign key field on it. In our case, this is the airports layer. For this layer you need to add a referencing field which points to the other layer, so this is fk_region.

Nota

Sometimes, you need more than a single field to uniquely identify features in a layer. Creating a relation with such a layer requires a composite key, i.e. more than a single pair of matching fields. Use the ^{Add new field pair as part of a composite foreign key} button to add as many pairs as necessary.
Id will be used for internal purposes and has to be unique. You may need it to build custom forms. 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
Relationship strength sets the strength of the relation between the parent and the child layer. The default Association type means that the parent layer is simply linked to the child one while the Composition type allows you to duplicate also the child features when duplicating the parent ones and on deleting a feature the children are deleted as well, resulting in cascade over all levels (means children of children of… are deleted as well).

../../../_images/regions_airports_mapping.png — Fig. 16.104 Adicionando uma relação entre camadas de regiões e de aeroportos

From the Relations tab, you can also press the Discover Relation button to fetch the relations available from the providers of the loaded layers. This is possible for layers stored in data providers like PostgreSQL or SpatiaLite.

Formulários para relações 1-N 

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

../../../_images/airport_relation_dataview.png — Fig. 16.105 Regiões de diálogo de identificação com relação a aeroportos

As you can see, the airports assigned to this particular region are all shown in a table. And there are also some buttons available. Let’s review them shortly:

The button is for toggling the edit mode. Be aware that it toggles the edit mode of the airport layer, although we are in the feature form of a feature from the region layer. But the table is representing features of the airport layer.
O botão |Salvar Edições | é para salvar todas as edições na camada infantil (aeroporto).
The button lets you digitize the airport geometry in the map canvas and assigns the new feature to the current region by default. Note that the icon will change according to the geometry type.
The button adds a new record to the airport layer attribute table and assigns the new feature to the current region by default. The geometry can be drawn later with the Add part digitizing tool.
The button allows you to copy and paste one or more child features within the child layer. They can later be assigned to a different parent feature or have their attributes modified.
The button deletes the selected airport(s) permanently.
The symbol opens a new dialog where you can select any existing airport which will then be assigned to the current region. This may be handy if you created the airport on the wrong region by accident.
The symbol unlinks the selected airport(s) from the current region, leaving them unassigned (the foreign key is set to NULL) effectively.
With the button you can zoom the map to the selected child features.
The two buttons and to the right switch between the table view and form view of the related child features.

Se você utilizar a função Design de arrastar e soltar’ para as regiões, você pode selecionar quais ferramentas estão disponíveis. Você pode até decidir se deseja abrir um novo formulário quando um novo recurso for adicionado utilizando a opção :guilabel:`Forçar a forma de ocultação no recurso adicionar. Esteja ciente de que esta opção implica que atributos não nulos devem ter um valor padrão válido para funcionar corretamente.

../../../_images/airport_relation_formproperties.png — Fig. 16.106 Design de arrasta/solta para configuração de regiões - ferramentas de relação aeroportos

In the above example the referencing layer has geometries (so it isn’t just an alphanumeric table) so the above steps will create an entry in the layer attribute table that has no corresponding geometric feature. To add the geometry:

Choose Open Attribute Table for the referencing layer.
Select the record that has been added previously within the feature form of the referenced layer.
Use the ^{Add Part} digitizing tool to attach a geometry to the selected attributes table record.

If you work on the airport table, the widget Relation Reference is automatically set up for the fk_region field (the one used to create the relation), see Relation Reference widget.

In the airport form you will see the button at the right side of the fk_region field: if you click on the button the form of the region layer will be opened. This widget allows you to easily and quickly open the forms of the linked parent features.

../../../_images/airport_attributes.png — Fig. 16.107 Diálogo de identificação do aeroporto com relação às regiões

The Relation Reference widget has also an option to embed the form of the parent layer within the child one. It is available in the Properties ► Attributes Form menu of the airport layer: select the fk_region field and check the Show embedded form option.

If you look at the feature dialog now, you will see that the form of the region is embedded inside the airports form and will even have a combobox, which allows you to assign the current airport to another region.

../../../_images/airport_attributes_expanded.png

Moreover if you toggle the editing mode of the airport layer, the fk_region field has also an autocompleter function: while typing you will see all the values of the id field of the region layer. Here it is possible to digitize a polygon for the region layer using the button if you chose the option Allow adding new features in the Properties ► Attributes Form menu of the airport layer.

The child layer can also be used in the Selecionar Feições Por Valor tool in order to select features of the parent layer based on attributes of their children.

In Fig. 16.108, all the regions where the mean altitude of the airports is greater than 500 meters above sea level are selected.

Você verá que muitas funções de agregação diferentes estão disponíveis no formulário.

../../../_images/relation_select_by_value.png — Fig. 16.108 Selecione as características dos pais com valores infantis

16.4.2.2. Many-to-many (N-M) relations 

N-M relations are many-to-many relations between two tables. For instance, the airports and airlines layers: an airport receives several airline companies and an airline company flies to several airports.

This SQL code creates the three tables we need for an N-M relationship in a PostgreSQL/PostGIS schema named locations. You can run the code using the Database ► DB Manager… for PostGIS or external tools such as pgAdmin. The airports table stores the airports layer and the airline table stores the airlines layer. In both tables few fields are used for clarity. The tricky part is the airports_airlines table. We need it to list all airlines for all airports (or vice versa). This kind of table is known as a pivot table. The constraints in this table force that an airport can be associated with an airline only if both already exist in their layers.

CREATE SCHEMA locations;

CREATE TABLE locations.airports
(
   id serial NOT NULL,
   geom geometry(Point, 4326) NOT NULL,
   airport_name text NOT NULL,
   CONSTRAINT airports_pkey PRIMARY KEY (id)
);

CREATE INDEX airports_geom_idx ON locations.airports USING gist (geom);

CREATE TABLE locations.airlines
(
   id serial NOT NULL,
   geom geometry(Point, 4326) NOT NULL,
   airline_name text NOT NULL,
   CONSTRAINT airlines_pkey PRIMARY KEY (id)
);

CREATE INDEX airlines_geom_idx ON locations.airlines USING gist (geom);

CREATE TABLE locations.airports_airlines
(
   id serial NOT NULL,
   airport_fk integer NOT NULL,
   airline_fk integer NOT NULL,
   CONSTRAINT airports_airlines_pkey PRIMARY KEY (id),
   CONSTRAINT airports_airlines_airport_fk_fkey FOREIGN KEY (airport_fk)
      REFERENCES locations.airports (id)
      ON DELETE CASCADE
      ON UPDATE CASCADE
      DEFERRABLE INITIALLY DEFERRED,
   CONSTRAINT airports_airlines_airline_fk_fkey FOREIGN KEY (airline_fk)
      REFERENCES locations.airlines (id)
      ON DELETE CASCADE
      ON UPDATE CASCADE
      DEFERRABLE INITIALLY DEFERRED
 );

Ao invés do PostgreSQL, você também pode usar o GeoPackage. Neste caso, as três tabelas podem ser criadas manualmente utilizando o Base de Dados ► Gerenciar BD…. No GeoPackage não há esquemas, portanto o prefixo localizações não é necessário.

Foreign key constraints in airports_airlines table can´t be created using Table ► Create Table… or Table ► Edit Table… so they should be created using Database ► SQL Window…. GeoPackage doesn’t support ADD CONSTRAINT statements so the airports_airlines table should be created in two steps:

Configure a tabela somente com o campo “ID”, utilizando Tabela ► Criar Tabela…

Utilizando Base de dados ► SQL Window…, digite e execute este código SQL:

ALTER TABLE airports_airlines
   ADD COLUMN airport_fk INTEGER
   REFERENCES airports (id)
   ON DELETE CASCADE
   ON UPDATE CASCADE
   DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE airports_airlines
   ADD COLUMN airline_fk INTEGER
   REFERENCES airlines (id)
   ON DELETE CASCADE
   ON UPDATE CASCADE
   DEFERRABLE INITIALLY DEFERRED;

Então, no QGIS, você deve estabelecer duas: ref:uma-para-muitas relações <one_to_many_relation> como explicado acima:

uma relação entre a “tabela de linhas aéreas” e a tabela dinâmica;
e uma segunda entre a mesa “aeroportos” e a tabela dinâmica.

Uma maneira mais fácil de fazê-lo (somente para o PostgreSQL) é utilizar o :guilabel:`Relações de Descoberta’ em :menuselection:`Projeto –> Propriedades –> Relações’. QGIS lerá automaticamente todas as relações em seu banco de dados e você só terá que selecionar as duas que você precisa. Lembre-se de carregar primeiro as três tabelas do projeto QGIS.

../../../_images/airports_airlines_relation.png — Fig. 16.109 Relações e auto-descobrimento

Caso você queira remover um “aeroporto” ou uma “linha aérea”, o QGIS não removerá o(s) registro(s) associado(s) na tabela “airports_airlines”. Esta tarefa será feita pelo banco de dados se especificarmos as restrições corretas na criação da tabela pivô, como no exemplo atual.

Nota

Combinação da relação N-M com grupo de transação automática

Você deve ativar o modo de transação em Propriedades do projeto ► Fontes de dados ► quando trabalhar em tal contexto. QGIS deve ser capaz de adicionar ou atualizar linha(s) em todas as tabelas (companhias aéreas, aeroportos e as tabelas pivot).

Finally we have to select the right cardinality in the Layer Properties ► Attributes Form for the airports and airlines layers. For the first one we should choose the airlines (id) option and for the second one the airports (id) option.

../../../_images/airports_airlines_relation_formproperties.png — Fig. 16.110 Definir a cardinalidade da relação

Agora você pode associar um aeroporto com uma companhia aérea (ou uma companhia aérea com um aeroporto) utilizando Adicionar feição filha ou Link existente de feição filha nas subformulários. Um registro será automaticamente inserido na tabela aeroportos_companhiaAerea`.

../../../_images/add_airport_airline.png — Fig. 16.111 Relação N-M entre aeroportos e companhias aéreas

Nota

Usando cardinalidade relação muitos para um

Sometimes hiding the pivot table in an N-M relationship is not desirable. Mainly because there are attributes in the relationship that can only have values when a relationship is established. If your tables have a geometry field, it could be interesting to activate the On map identification option (Layer Properties ► Attributes Form ► Available widgets ► Fields) for the foreign key fields in the pivot table.

Nota

Chave primária da tabela dinâmica

Avoid using multiple fields in the primary key in a pivot table. QGIS assumes a single primary key so a constraint like constraint airports_airlines_pkey primary key (airport_fk, airline_fk) will not work.

16.4.2.3. Polymorphic relations 

The purpose 

Polymorphic relations are special case of 1-N relations, where a single referencing (document) layer contains the features for multiple referenced layers. This differs from normal relations which require different referencing layer for each referenced layer. A single referencing (document) layer is achieved by adding an adiditonal layer_field column in the referencing (document) layer that stores information to identify the referenced layer. In its most simple form, the referencing (document) layer will just insert the layer name of the referenced layer into this field.

Para ser mais preciso, uma relação polimórfica é um conjunto de relações normais com a mesma camada de referência, mas com a camada referenciada dinamicamente definida. O ajuste polimórfico da camada é resolvido usando uma expressão que tem que combinar algumas propriedades da camada referenciada como o nome da tabela, id da camada, nome da camada.

Imagine we are going to the park and want to take pictures of different species of plants and animals we see there. Each plant or animal has multiple pictures associated with it, so if we use the normal 1:N relations to store pictures, we would need two separate tables, animal_images and plant_images. This might not be a problem for 2 tables, but imagine if we want to take separate pictures for mushrooms, birds etc.

Polymorphic relations solve this problem as all the referencing features are stored in the same table documents. For each feature the referenced layer is stored in the referenced_layer field and the referenced feature id in the referenced_fk field.

Definindo relações polimórficas 

First, let QGIS know about the polymorphic relations between the layers. This is done in Project ► Properties…. Open the Relations tab and click on the little down arrow next to the Add Relation button, so you can select the Add Polymorphic Relation option from the newly appeared dropdown.

../../../_images/polymorphic_relation_properties.png — Fig. 16.112 Adicionando uma relação polimórfica utilizando “documentos” como camadas de referência e “animais” e “plantas” como camadas de referência.

Id will be used for internal purposes and has to be unique. You may need it to build custom forms. 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
Referencing Layer (Child) also considered as child layer, is the one with the foreign key field on it. In our case, this is the documents layer. For this layer you need to add a referencing field which points to the other layer, so this is referenced_fk.

Nota

Sometimes, you need more than a single field to uniquely identify features in a layer. Creating a relation with such a layer requires a composite key, i.e. more than a single pair of matching fields. Use the ^{Add new field pair as part of a composite foreign key} button to add as many pairs as necessary.
Layer Field is the field in the referencing table that stores the result of the evaluated layer expression which is the referencing table that this feature belongs to. In our example, this would be the referenced_layer field.
Layer expression evaluates to a unique identifier of the layer. This can be the layer name @layer_name, the layer id @layer_id, the layer’s table name decode_uri(@layer, 'table') or anything that can uniquely identifies a layer.
Relationship strength sets the strength of the generated relations between the parent and the child layer. The default Association type means that the parent layer is simply linked to the child one while the Composition type allows you to duplicate also the child features when duplicating the parent ones and on deleting a feature the children are deleted as well, resulting in cascade over all levels (means children of children of… are deleted as well).
Referenced Layers also considered as parent layers, are those with the primary key, pointed to, so here they would be plants and animals layers. You need to define the primary key of the referenced layers from the dropdown, so it is fid. Note that the definition of a valid primary key requires all the referenced layers to have a field with that name. If there is no such field you cannot save a polymorphic relation.

Uma vez adicionada, a relação polimórfica pode ser editada através da entrada do menu Editar relação polimórfica.

../../../_images/polymorphic_relations.png — Fig. 16.113 Preview of the newly created polymorphic relation and its child relations for animals and plants.

O exemplo acima utiliza o seguinte esquema de banco de dados:

CREATE SCHEMA park;

CREATE TABLE park.animals
(
   fid serial NOT NULL,
   geom geometry(Point, 4326) NOT NULL,
   animal_species text NOT NULL,
   CONSTRAINT animals_pkey PRIMARY KEY (fid)
);

CREATE INDEX animals_geom_idx ON park.animals USING gist (geom);

CREATE TABLE park.plants
(
   fid serial NOT NULL,
   geom geometry(Point, 4326) NOT NULL,
   plant_species text NOT NULL,
   CONSTRAINT plants_pkey PRIMARY KEY (fid)
);

CREATE INDEX plants_geom_idx ON park.plants USING gist (geom);

CREATE TABLE park.documents
(
   fid serial NOT NULL,
   referenced_layer text NOT NULL,
   referenced_fk integer NOT NULL,
   image_filename text NOT NULL,
   CONSTRAINT documents_pkey PRIMARY KEY (fid)
);