Table Joins and Relations

“Attribute table joins is the process of combining two tables based on columns containing the same attribute values.”

In this module, we will look at the various types of table joins that can be achieved in. In order for two tables to be linked, there have to be common values between the two tables. The columns that are to be linked can have different names but contain/represent the same values.

You try:

Goal: To learn the basics of attribute joins and distinguish between 1 to 1 join and 1 to many joins

Problem: Identify the crime patterns recorded at a particular police station.

  • Load police_station, districts as spatial layers.
  • Add the crime.csv using add vector menu.
  • Navigate the attribute table for the police and crime.

Can you identify the column's containing identical data?

  • Join the crime layer to the police station using the column's you established above.
  • Open the police station attribute table and explore.

Why are the joined records empty?

  • Fix the issue with the join so that records can be seen in the joined table.
  • Open the attribute table for police stations and interpret the results. What type of join is it?
  • Remove the table join.
  • Click on project menu and choose project properties. Select relations tab.
  • Create a relation between crime and police station. (Referencing child: crime, Referencing parent: police station)
  • Open police station attribute table and chose form view. Interpret the results. What type of join is it?

Name Expectation

Spatial Operations

Expressions editor QGIS

Table operations

1-1 Join 1- Many Join

Data path

appendix3-local-data

data path

Crime.csv,police_station.shp,districts.shp

join_column

Crime,year,incidents

More about

A join is a means of appending data from another layer using common attributes. There are mainly two types of joins 1-1 and 1-many joins. 1 -1 joins explore the relationship between single rows from two separate layers. This is useful in order to link external data to spatial features. An attribute join provides a temporary view and records that have been joined have limited functionality in QGIS. A user cannot filter records using a column from the join. Once records are joined to make the join permanent one has to invoke the save as feature to create a new permanent vector layer.

Many joins occur when they are many rows in the external table which can be linked to the primary layer. In most cases, such joins are difficult to visualise them in the normal table view in QGIS. In order to visualize them, users have to change the type of view to Form View.

Check your knowledge:

  1. Describe a table join:
    1. A spatial operation that is only available in QGIS
    2. When one combines two tables using column values that are available in both tables.
    3. A spatial operation to select records within a specific geographic entity.
  2. Which of the following is True:
    1. The values of the columns involved in the join have to use the same Letter case
    2. Attribute joins are mainly between spatial layers and non spatial layers.
    3. You cannot search in the attribute table using a joined column.
  3. Table joins can only be used when the two tables use column with identical names:
    1. True
    2. False