This section of the free chapter preview of Mastering QGIS looks at working with tables in QGIS. Mastering QGIS is designed to bring users who want to go beyond the basics of QGIS to an advanced level of QGIS expertise.
There are two types of tables you can work with in QGIS, attribute tables, and standalone tables. Whether from a database, associated with a shapefile, or a flat-file, they are all treated the same. Standalone tables can be added via clicking on the Layer | Add Layer | Add Vector Layer menu. QGIS supports the table formats supported by OGR along with database tables. Tables are treated like any other GIS layer, they simply have no geometry. Both types of tables can be opened within Desktop by selecting the layer/table in the Layers Panel, and either clicking Layer | Open Attribute Table, or by right clicking on the data layer and choosing Open Attribute Table from the context menu. They can also be previewed in Browser by choosing the Attribute tab.
The table opens in a new window which displays the number of table rows and selected records in the title bar. Below the title bar are a series of buttons that allow you to toggle editing, manage selections and add and delete columns. The majority of the window is filled with the table body. The table can be sorted by clicking on the column names. An arrow will appear in the column header indicating either an ascending or descending sort. Rows can be selected by clicking on the row number on the left side. In the lower left corner is a Tables Menu that allows you to manage what portions of the table are displayed. You can choose to Show All Features (default setting), Show Selected Features, Show Features Visible on Map (only available for when viewing an attribute table), Show Edited and New Features, create Column Filters, and Advanced Filter (Expression). The lower right corner has a toggle between the default table view and a forms view of the table.
Attribute tables are associated with the features of a GIS layer. Typically one record in the attribute table corresponds to one feature in the GIS layer. The exception to this is multipart features, which have multiple geometries linked to a single record in the attribute table. Standalone tables are not associated with GIS data layers. However, they may have data of a spatial nature from which a spatial data layer can be generated (see Chapter 6). They also may contain data that you wish to join to an existing attribute table with a table join.
Let’s say you need to make a map of total population by county. However, the counties GIS layer does not have population as an attribute. Rather this data is contained in an Excel spreadsheet. It is possible to join additional tabular data to an existing attribute table.
There are two requirements:
- The two tables need to share fields with attributes to match for joining .
- There needs to be a cardinality of one-to-one or many-to-one between the attribute table and the standalone table.
To create a join, load both the GIS layer and the standalone table into QGIS Desktop. QGIS will accept a variety of standalone table file formats including Excel spreadsheets, dbf files, and comma delimited text files. You can load these tabular data via the Layer | Add Layer | Add Vector Layer menu.
Once the data is loaded a join can be completed by following these steps:
- Select the GIS layer in the Layers panel that will receive the new data from the join.
- Click Layer | Properties and choose the Joins
- Click the Add Join button (green plus sign).
- Choose the Join Layer, Join Field and Target Field. The Join Layer and Join Field represent the standalone table. The Target Field is the column in the attribute table the join will be based on.
Although in this example the Join Field and the Target Field have the same name that is not a requirement. The two fields merely need to hold the same unique ID.
- At this point you can choose to Cache the join in virtual memory, Create attribute index on the join field and Choose which fields are joined to the attribute table. This last option lets you choose which fields from the Join Layer are appended to the attribute table.
- Once created, the join will be listed on the Joins The extra attribute columns from the Join Layer will be appended to the attribute table where the value in the Join Field matched the value in the Target Field.
- Joins can be removed by clicking the Remove Join button (red minus sign).
Joins only exist in virtual memory within the QGIS Desktop document. To preserve the join outside the map document click Layer | Save as… and save a new copy of the layer. The new layer will include the attributes appended via the join.