14.4. Mit Attributtabelle arbeiten
The attribute table displays information on features of a selected layer. Each row in the table represents a feature (with or without geometry), and each column contains a particular piece of information about the feature. Features in the table can be searched, selected, moved or even edited.
QGIS allows you to load spatial and non-spatial layers. This currently includes tables supported by OGR and delimited text, as well as the PostgreSQL, MSSQL, SpatiaLite, DB2 and Oracle provider. All loaded layers are listed in the Layers panel. Whether a layer is spatially enabled or not determines whether you can interact with it on the map.
Non-spatial tables can be browsed and edited using the attribute table view. Furthermore, they can be used for field lookups. For example, you can use columns of a non-spatial table to define attribute values, or a range of values that are allowed, to be added to a specific vector layer during digitizing. Have a closer look at the edit widget in section Attributes Form Properties to find out more.
To open the attribute table for a vector layer, activate the layer by clicking on it in the Layerfenster. Then, from the main menu, choose . It is also possible to right-click on the layer and choose from the drop-down menu, or to click on the Open Attribute Table button in the Attributes toolbar. If you prefer shortcuts, F6 will open the attribute table. Shift+F6 will open the attribute table filtered to selected features and Ctrl+F6 will open the attribute table filtered to visible features.
This will open a new window that displays the feature attributes for the layer (figure_attributes_table). According to the setting in menu, the attribute table will open in a docked window or a regular window. The total number of features in the layer and the number of currently selected/filtered features are shown in the attribute table title, as well as if the layer is spatially limited.
Die anderen Knöpfe im Kopf der Attributtabelle stellen die folgenden Funktionalitäten zur Verfügung:
Toggle editing mode
Enable editing functionalities
Toggle multi edit mode
Update multiple fields of many features
Save current modifications
Reload the table
Add new geometryless feature
Delete selected features
Remove selected features from the layer
Cut selected features to clipboard
Copy selected features to clipboard
Paste features from clipboard
Insert new features from copied ones
Select features using an Expression
Select all features in the layer
Invert the current selection in the layer
Deselect all features in the current layer
Filter/Select features using form
Move selected to top
Move selected rows to the top of the table
Pan map to the selected rows
Zoom map to the selected rows
Add a new field to the data source
Remove a field from the data source
Open field calculator
Update field for many features in a row
Enable table formatting
Dock attribute table
Allows to dock/undock the attribute table
Lists the actions related to the layer
Depending on the format of the data and the OGR library built with your QGIS version, some tools may not be available.
Below these buttons is the Quick Field Calculation bar (enabled only in edit mode), which allows to quickly apply calculations to all or part of the features in the layer. This bar uses the same expressions as the Field Calculator (see Editiere Attributwerte).
QGIS provides two view modes to easily manipulate data in the attribute table:
The Form view shows feature identifiers in a first panel and displays only the attributes of the clicked identifier in the second one. There is a pull-down menu at the top of the first panel where the „identifier“ can be specified using an attribute (Column preview) or an Expression. The pull-down also includes the last 10 expressions for re-use. Form view uses the layer fields configuration (see Attributes Form Properties). You can browse through the feature identifiers with the arrows on the bottom of the first panel. Once you markered the feature in yellow in the list it is selected in yellow on the canvas. Use the on top of the attribute table to zoom to the feature. Clicking on an entry in the list (without using the rectangles) makes a feature flash in red color once so you can see where it is situated.
You can switch from one mode to the other by clicking the corresponding icon at the bottom right of the dialog.
You can also specify the Default view mode at the opening of the attribute table in menu. It can be ‚Remember last view‘, ‚Table view‘ or ‚Form view‘.
Right-click in a column header when in table view to have access to tools that help you configure what can be displayed in the attribute table and how.
By right-clicking in a column header, you can choose to hide it from the attribute table. To change several columns behavior at once, unhide a column or change the order of the columns, choose Organize columns …. In the new dialog, you can:
check/uncheck columns you want to show or hide
drag-and-drop items to reorder the columns in the attribute table. Note that this change is for the table rendering and does not alter the fields order in the layer datasource
enable a new virtual Actions column that displays in each row a drop-down box or button list of actions for each row, see Actions Properties for more information about actions.
Columns width can be set through a right-click on the column header and select either:
Set width… to enter the desired value. By default, the current value is displayed in the widget
Autosize to resize at the best fit the column.
It can also be changed by dragging the boundary on the right of the column heading. The new size of the column is maintained for the layer, and restored at the next opening of the attribute table.
The table can be sorted by any column, by clicking on the column header. A
small arrow indicates the sort order (downward pointing means descending
values from the top row down, upward pointing means ascending values from
the top row down).
You can also choose to sort the rows with the sort option of the
column header context menu and write an expression, e.g. to sort the row
using multiple columns you can write
Sorting based on columns of different types
Trying to sort an attribute table based on columns of string and numeric types
may lead to unexpected result because of the
concat("USE", "ID") expression
returning string values (ie,
'Borough105' < 'Borough6').
You can workaround this by using eg
concat("USE", lpad("ID", 3, 0)) which
'Borough105' > 'Borough006'.
Conditional formatting settings can be used to highlight in the attribute table features you may want to put a particular focus on, using custom conditions on feature’s:
geometry (e.g., identifying multi-parts features, small area ones or in a defined map extent…);
or field value (e.g., comparing values to a threshold, identifying empty cells…).
the name of the rule;
a condition using any of the expression builder functions;
the formatting: it can be choosen from a list of predefined formats or created based on properties like:
background and text colors;
use of icon;
bold, italic, underline, or strikeout;
In table view, each row in the attribute table displays the attributes of a unique feature in the layer. Selecting a row selects the feature and likewise, selecting a feature in the map canvas (in case of geometry enabled layer) selects the row in the attribute table. If the set of features selected in the map canvas (or attribute table) is changed, then the selection is also updated in the attribute table (or map canvas) accordingly.
Zeilen können ausgewählt werden, indem Sie auf die Zeilennummer links neben der Zeile klicken. Mehrere Zeilen können ausgewählt werden, indem die Strg Taste während der Auswahl gedrückt wird.Eine kontinuierliche Auswahl ist möglich, indem Sie bei der Selektion die Umschalt Taste gedrückt halten, während Sie die Zeilennummern auswählen. Alle Zeilen zwischen der aktuell ausgewählten Zeile und der Mauspfeilposition werden dadurch selektiert. Bewegt man den Mauszeiger in der Attributtabelle indem man in eine Zelle in der Tabelle klickt ändert die Zeilenauswahl nicht. Verändert man die Auswahl im Kartenfenster bewegt sich der Mauszeiger in der Attributtabelle nicht.
In form view of the attribute table, features are by default identified in the left panel by the value of their displayed field (see Display Properties). This identifier can be replaced using the drop-down list at the top of the panel, either by selecting an existing field or using a custom expression. You can also choose to sort the list of features from the drop-down menu.
Click a value in the left panel to display the feature’s attributes in the right one. To select a feature, you need to click inside the square symbol at the left of the identifier. By default, the symbol turns into yellow. Like in the table view, you can perform multiple feature selection using the keyboard combinations previously exposed.
Beyond selecting features with the mouse, you can perform automatic selection based on feature’s attribute using tools available in the attribute table toolbar, such as (see section Automatic selection and following one for more information and use case):
It is also possible to select features using the Filtering and selecting features using forms.
Once you have selected features in the attribute table, you may want to display only these records in the table. This can be easily done using the Show Selected Features item from the drop-down list at the bottom left of the attribute table dialog. This list offers the following filters:
Show All Features
Show Selected Features
Show Features visible on map
Show Edited and New Features
Field Filter - allows the user to filter based on value of a field: choose a column from a list, type a value and press Enter to filter. Then, only the matching features are shown in the attribute table.
Advanced filter (Expression) - Opens the expression builder dialog. Within it, you can create complex expressions to match table rows. For example, you can filter the table using more than one field. When applied, the filter expression will show up at the bottom of the form.
It is also possible to filter features using forms.
Filtering records out of the attribute table does not filter features out of the layer; they are simply momentaneously hidden from the table and can be accessed from the map canvas or by removing the filter. For filters that do hide features from the layer, use the Query Builder.
Update datasource filtering with
Show Features Visible on Map
When for performance reasons, features shown in attribute table are spatially limited to the canvas extent at its opening (see Data Source Options for a how-to), selecting Show Features Visible on Map on a new canvas extent updates the spatial restriction.
From this point onwards, this tool functionality is similar to the one described in Select Features By Value, where you can find descriptions of all operators and selecting modes.
When selecting / filtering features from the attribute table, there is a Filter features button that allows defining and refining filters. Its use triggers the Advanced filter (Expression) option and displays the corresponding filter expression in an editable text widget at the bottom of the form.
If there are already filtered features, you can refine the filter using the drop-down list next to the Filter features button. The options are:
Filter within („AND“)
Extend filter („OR“)
To clear the filter, either select the Show all features option from the bottom left pull-down menu, or clear the expression and click Apply or press Enter.
Users have several possibilities to manipulate feature with the contextual menu like:
Select all (Ctrl+A) the features;
Copy the content of a cell in the clipboard with Copy cell content;
Zoom to feature without having to select it beforehand;
Pan to feature without having to select it beforehand;
Flash feature, to highlight it in the map canvas;
Open form: it toggles attribute table into form view with a focus on the clicked feature.
If you want to use attribute data in external programs (such as Excel, LibreOffice, QGIS or a custom web application), select one or more row(s) and use the Copy selected rows to clipboard button or press Ctrl+C.
In Copy features as dropdown list:menu you can define the format to paste to with
Plain text, no geometry,
Plain text, WKT geometry,
You can also display a list of actions in this contextual menu. This is enabled in the Actions Properties for more information on actions.tab. See
The selected features can be saved as any OGR-supported vector format and also transformed into another coordinate reference system (CRS). In the contextual menu of the layer, from the Layers panel, click on to define the name of the output dataset, its format and CRS (see section Creating new layers from an existing layer). You’ll notice that is checked. It is also possible to specify OGR creation options within the dialog.
Editing attribute values can be done by:
typing the new value directly in the cell, whether the attribute table is in table or form view. Changes are hence done cell by cell, feature by feature;
using the field calculator: update in a row a field that may already exist or to be created but for multiple features. It can be used to create virtual fields;
using the quick field calculation bar: same as above but for only existing field;
or using the multi edit mode: update in a row multiple fields for multiple features.
The Field Calculator button in the attribute table allows you to perform calculations on the basis of existing attribute values or defined functions, for instance, to calculate length or area of geometry features. The results can be used to update an existing field, or written to a new field (that can be a virtual one).
The field calculator is available on any layer that supports edit. When you click on the field calculator icon the dialog opens (see Abb. 14.73). If the layer is not in edit mode, a warning is displayed and using the field calculator will cause the layer to be put in edit mode before the calculation is made.
Based on the Expression Builder dialog, the field calculator dialog offers a complete interface to define an expression and apply it to an existing or a newly created field. To use the field calculator dialog, you must select whether you want to:
apply calculation on the whole layer or on selected features only
create a new field for the calculation or update an existing one.
If you choose to add a new field, you need to enter a field name, a field type (integer, real, date or string) and if needed, the total field length and the field precision. For example, if you choose a field length of 10 and a field precision of 3, it means you have 7 digits before the dot, and 3 digits for the decimal part.
Ein kurzes Beispiel zeigt wie der Feldrechner arbeitet, wenn Sie den Reiter Ausdruck verwenden. Wir wollen die Länge des ‚railroads‘ Layers aus dem QGIS Beispieldatensatz in km berechnen:
Set Output field name to
Decimal number (real)as Output field type
Set the Output field length to
10and the Precision to
Double click on
$lengthin the Geometry group to add the length of the geometry into the Field calculator expression box.
Complete the expression by typing
/ 1000in the Field calculator expression box and click OK.
You can now find a new length_km field in the attribute table.
A virtual field is a field based on an expression calculated on the fly, meaning
that its value is automatically updated as soon as an underlying parameter
changes. The expression is set once; you no longer need to recalculate the field
each time underlying values change.
For example, you may want to use a virtual field if you need area to be evaluated
as you digitize features or to automatically calculate a duration between dates
that may change (e.g., using
Virtuelle Felder benutzen
Virtuelle Felder sind nicht dauerhaft in den Layerattributen, was bedeutet, dass sie in der Projektdatei gespeichert und verfügabr sind, in der sie gespeichert wurden.
A field can be set virtual only at its creation. Virtual fields are marked with a purple background in the fields tab of the layer properties dialog to distinguish them from regular physical or joined fields. Their expression can be edited later by pressing the expression button in the Comment column. An expression editor window will be opened to adjust the expression of the virtual field.
While Field calculator is always available, the quick field calculation bar on top of the attribute table is only visible if the layer is in edit mode. Thanks to the expression engine, it offers a quicker access to edit an already existing field:
Unlike the previous tools, multi edit mode allows multiple attributes of different features to be edited simultaneously. When the layer is toggled to edit, multi edit capabilities are accessible:
Unlike the tool from the attribute table, hitting theoption provides you with a modal dialog to fill attributes changes. Hence, features selection is required before execution.
In order to edit multiple fields in a row:
Select the features you want to edit.
At the right side of the attribute table, fields (and values) of selected features are shown. New widgets appear next to each field allowing for display of the current multi edit state:
Clicking any of these widgets allows you to either set the current value for the field or reset to original value, meaning that you can roll back changes on a field-by-field basis.
Make the changes to the fields you want.
Click on Apply changes in the upper message text or any other feature in the left panel.
Changes will apply to all selected features. If no feature is selected, the whole table is updated with your changes. Modifications are made as a single edit command. So pressing Undo will rollback the attribute changes for all selected features at once.
Multi edit mode is only available for auto generated and drag and drop forms (see Customizing a form for your data); it is not supported by custom ui forms.
Relations are a technique often used in databases. The concept is that features (rows) of different layers (tables) can belong to each other.
Als Beispiel nehmen wir einen Layer mit allen Regionen von Alaska (Polygon) in dem einige Attribute über den Namen und den Regionstyp sowie eine eindeutige ID (die als Primärschlüssel eingesetzt wird) vorhanden sind.
Then you get another point layer or table with information about airports that are located in the regions and you also want to keep track of these. If you want to add them to the regions layer, you need to create a one to many relation using foreign keys, because there are several airports in most regions.
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 shapefile and
airports shapefile which has a foreign key field (
the layer regions. This means, that each airport belongs to exactly one region
while each region can have any number of airports (a typical one to many
In addition to the already existing attributes in the airports attribute table,
you’ll need 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 field fk_region will always contain an id of a region. It can be seen like a pointer to the region it belongs to. And 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) and all you have to do is to tell QGIS the relations between your tables.
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
regionslayer. You need to define the primary key of the referenced layer, so it is
Referencing Layer (Child) also considered as child layer, is the one with the foreign key field on it. In our case, this is the
airportslayer. For this layer you need to add a referencing field which points to the other layer, so this is
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, ie 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.
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.
Jetzt wo QGIS von der Beziehung weiss wird es dazu benutzt das Formular das es erstellt zu verbessern. Da wir die Standard Formular Methode (autogenerated) nicht verändert haben, wird es Ihrem Formular einfach ein neues Bearbeitungselement hinzufügen. Also lassen Sie uns einen Layer ‚region‘ in der Legende auswählen und das Objekte abfragen Werkzeug benutzen. Abhängig von Ihren Einstellungen öffnet sich das Formular entweder direkt oder Sie müssen den Identifizieren Dialog unter Aktionen öffnen.
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:
Der Knopf ist zum Umschalten des Bearbeitungsmodus da. Seien Sie sich dessen bewusst dass es den Bearbeitungsmodus des ‚airport‘ Layers umschaltet, trotzdessen wir uns im Objektformular eines Objektes aus dem ‚region‘ Layer befinden. Die Tabelle jedoch stellt Objekte des ‚aiport‘ Layer dar.
Das Symbol öffnet einen neuen Dialog in dem Sie jeden vorhandenen ‚airport‘ auswählen können was dann der aktuellen ‚region‘ zugewiesen wird. Dies kann nützlich sein wenn Sie den ‚airport‘ aus Versehen in der falschen ‚region‘ erstellt haben.
Das Symbol hebt die Verbindung zwischen dem ausgewählten ‚airport‘ und der aktuellen ‚region‘ wieder auf so dass dieser auf wirksame Art und Weise nicht mehr zugewiesen ist (der Fremdschlüssel wird auf NULL gesetzt).
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:
Select the record that has been added previously within the feature form of the referenced layer.
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.
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
fk_region field and check the
Show embedded form option.
Wenn Sie jetzt einen Blick auf den Objektdialog werfen werden Sie sehen dass das Formular der ‚region‘ in das ‚airports‘ Formular eingebettet ist und sogar eine Kombobox vorhanden ist mit dem der aktuelle ‚airport‘ zu einer anderen ‚region‘ zugeordnet werden kann.
Moreover if you toggle the editing mode of the airport layer, the
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
menu of the airport layer.
The child layer can also be used in the Select Features By Value tool in order to select features of the parent layer based on attributes of their children.
In Abb. 14.80, all the regions where the mean altitude of the airports is greater than 500 meters above sea level are selected.
You will find that many different aggregation functions are available in the form.
N-M relations are many-to-many relations between two tables. For instance, the
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
pgAdmin. The airports table stores the
airports layer and the airlines
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 );
Instead of PostgreSQL you can also use GeoPackage. In this case, the three tables can be created manually using the. In GeoPackage there are no schemas so the locations prefix is not needed.
Foreign key constraints in
airports_airlines table can´t be created using
or so they should be created using .
GeoPackage doesn’t support ADD CONSTRAINT statements so the
table should be created in two steps:
Set up the table only with the
Using, type and execute this SQL code:
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;
Then in QGIS, you should set up two one-to-many relations as explained above:
a relation between
airlinestable and the pivot table;
and a second one between
airportstable and the pivot table.
An easier way to do it (only for PostgreSQL) is using the Discover Relations in . QGIS will automatically read all relations in your database and you only have to select the two you need. Remember to load the three tables in the QGIS project first.
In case you want to remove an
airport or an
airline, QGIS won’t remove
the associated record(s) in
airports_airlines table. This task will be made by
the database if we specify the right constraints in the pivot table creation as
in the current example.
Combining N-M relation with automatic transaction group
You should enable the transaction mode inwhen working on such context. QGIS should be able to add or update row(s) in all tables (airlines, airports and the pivot tables).
Finally we have to select the right cardinalilty in the
airlines layers. For the first one we should choose the airlines (id) option
and for the second one the airports (id) option.
Now you can associate an airport with an airline (or an airline with an airport)
using Add child feature or Link existing child feature
in the subforms. A record will automatically be inserted in the
Using Many to one relation cardinality
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 are layers (have a geometry field) it could be interesting to activate the On map identification option ( ) for the foreign key fields in the pivot table.
Pivot table primary key
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.