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 joins available and differentiate how to establish 1-1 join versus 1- M join. 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-1 join and 1-M joins

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

Table Join
Load police_station.shp, districts.shp into QGIS.
Add the crime.csv using add vector menu. (Why didn't we add it using the delimited text layer ? )
Open the attribute table for the police and crime side by side for comparison.
Can you identify the column's containing identical data?

Select Join tab from the properties of the police_station.
Create a join between the crime and the police station. Use the columns containing identical data.
Open the police station attribute table and explore. Are there any joined records. If not give a reason and identify how to fix it.
Fix the issue with the join.
Open the attribute table for police stations and interpret the results. What type of join is it?
Remove the table join.

Table Relations
Click on Project menu and choose Project properties. Select the 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

Data

table_joins/crime.csv, table_joins/police_station.shp, table_joins/districts.shp

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