Section : 10. Vector

Module : 10.2. Table Joins

Table Joins

“Attribute table joins is the process of combining two or more tables based on columns containing the same data type.”

In this module, we will look at joining two or more tables together based on occurrences of the same data type. Attribute join works when the data type is the same. If a layer contains strings type data it will only join to string type data. This simple relationship between two tables is called a 1-1 join where a row in table A matches a row in table B.
The columns that are to be linked can have different names but contain/represent the same values.

You try:

Goal: To explore the use case of joining attributes from two tables sharing a unique column

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

Table Join
1. Load the spatial data into QGIS.
2. Add the crime.csv using add vector menu. (Why didn't we add it using the delimited text layer ? )
3. Open the attribute table for the police and crime side by side for comparison.
Can you identify the column's containing identical data?
4. Select Join* tab from the properties of the police_station.
5. Create a join between the crime and the police station.
6. Open the police station attribute table and explore. Are there any joined records. If not give a reason and identify how to fix it.
7. Fix the issue with the join.
8. Open the attribute table for police stations and interpret the results. What type of join is it?

Name Expectation

Crime Layer

crime.csv

Police Layer

police_station.shp

Boundary layer

districts.shp

More about attribute table joins

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 ie you can filter a table with join without creating a virtual layer first. Once records are joined to make the join permanent one has to invoke the save as feature to create a new permanent vector layer.

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

Further reading:

Download the sample data for the lesson.