Difference between revisions of "QGIS/C3/Table-Joins-and-Spatial-Joins/English"

From Script | Spoken-Tutorial
Jump to: navigation, search
 
(6 intermediate revisions by 3 users not shown)
Line 1: Line 1:
−
 
−
 
 
{|border=1
 
{|border=1
 
|| '''Visual Cue'''
 
|| '''Visual Cue'''
Line 8: Line 6:
  
 
'''Title Slide'''
 
'''Title Slide'''
−
||Welcome to this tutorial on '''Table Joins''' and '''Spatial Joins '''in '''QGIS'''.
+
||Welcome to this tutorial on '''Table Joins''' and '''Spatial Joins''' in '''QGIS'''.
  
 
|-
 
|-
Line 26: Line 24:
 
|| Here I am using
 
|| Here I am using
  
−
'''Ubuntu Linux '''OS version. 16.04
+
'''Ubuntu Linux''' OS version. 16.04
  
 
'''QGIS '''version 2.18
 
'''QGIS '''version 2.18
Line 51: Line 49:
 
|-
 
|-
 
||Show the map '''Stations.shp''' on '''Desktop'''.
 
||Show the map '''Stations.shp''' on '''Desktop'''.
−
|| I have already downloaded the '''Code file''', extracted and saved in a folder on the '''Desktop'''.
+
|| I have already downloaded the '''Code files''', extracted and saved in a folder on the '''Desktop'''.
  
 
|-
 
|-
−
|| Double click on code file folder.
+
|| Double click on '''Code files''' folder.
  
 
Point to '''Stations.shp'''.
 
Point to '''Stations.shp'''.
Line 81: Line 79:
  
 
'''https://www.gislounge.com/working-with-tables-mastering-qgis/'''
 
'''https://www.gislounge.com/working-with-tables-mastering-qgis/'''
−
|| There are two ways to '''join table''',  
+
|| There are two ways to join a table,  
  
 
1. '''Table Join''' that is, joining '''tables''' having one or more common column '''data'''.  
 
1. '''Table Join''' that is, joining '''tables''' having one or more common column '''data'''.  
Line 114: Line 112:
  
 
A dialog-box opens.
 
A dialog-box opens.
−
Navigate to '''Stations.shp''' file from the code files folder on the '''Desktop'''.
+
 
 +
Navigate to '''Stations.shp''' file from the '''Code files''' folder on the '''Desktop'''.
  
 
|-
 
|-
Line 121: Line 120:
  
 
|-
 
|-
−
|| '''Add vector Layer dialogue''' box will open.
+
|| '''Add vector Layer dialog''' box will open.
  
 
Click on '''Open.'''
 
Click on '''Open.'''
Line 127: Line 126:
  
 
|-
 
|-
−
|| Cursor on '''Layer panel'''.
+
|| Cursor on '''Layers panel'''.
  
 
Cursor on the map.
 
Cursor on the map.
Line 140: Line 139:
 
Let us open the '''attribute table''' for this '''layer'''.
 
Let us open the '''attribute table''' for this '''layer'''.
  
−
In the '''Layers Panel''', right-click on '''Stations.shp''' .
+
In the '''Layers Panel''', right-click on '''Stations.shp'''.
 
|-
 
|-
 
|| Click on '''Open Attribute Table''' option.
 
|| Click on '''Open Attribute Table''' option.
Line 146: Line 145:
  
 
|-
 
|-
−
|| Cursor on the Attribute table window.
+
|| Cursor on the '''Attribute''' table window.
 
|| '''Attribute table '''opens.
 
|| '''Attribute table '''opens.
  
−
Notice that '''data''' for only one attribute '''District '''is available.
+
Notice that '''data''' for only one attribute '''District ''' is available.
  
 
|-
 
|-
Line 156: Line 155:
  
 
|-
 
|-
−
||Click on Layer menu from the menu bar.
+
||Click on '''Layers''' menu from the menu bar.
 
|| Now we will add another''' data-set''' which is a spreadsheet to the '''Layers Panel'''.
 
|| Now we will add another''' data-set''' which is a spreadsheet to the '''Layers Panel'''.
  
−
This dataset is in CSV format.
+
This data set is in '''CSV''' '''format'''.
  
 
|-
 
|-
Line 189: Line 188:
 
|| A dialog box opens.
 
|| A dialog box opens.
  
−
Navigate to '''Rainfall.csv''' file from the code files folder on the '''Desktop'''.  
+
Navigate to '''Rainfall.csv''' file from the '''Code files''' folder on the '''Desktop'''.  
  
 
Click on '''Open''' button.
 
Click on '''Open''' button.
  
 
|-
 
|-
−
|| Cursor on '''Delimited Text File '''dialogue''' '''box.
+
|| Cursor on '''Delimited Text File '''dialog''' '''box.
  
 
Select '''CSV '''as''' File Format'''.
 
Select '''CSV '''as''' File Format'''.
−
|| In the '''Delimited Text File '''dialog box, select''' CSV '''as''' File Format.'''
+
|| In the '''Delimited Text File '''dialog box, select ''' CSV''' as''' File Format.'''
−
 
+
 
|-
 
|-
 
|| Click on the '''No geometry (attribute only table)'''.
 
|| Click on the '''No geometry (attribute only table)'''.
Line 211: Line 209:
  
 
|-
 
|-
−
||Cursor on '''Layer panel'''.
+
||Cursor on '''Layers panel'''.
 
||On '''QGIS''' canvas, '''Rainfall layer''' will be added in the '''Layers Panel'''.
 
||On '''QGIS''' canvas, '''Rainfall layer''' will be added in the '''Layers Panel'''.
  
Line 223: Line 221:
  
 
|-
 
|-
−
||Point towards the attribute table columns.
+
||Point towards the '''attribute''' table columns.
 
|| '''Attribute table''' opens.
 
|| '''Attribute table''' opens.
  
Line 291: Line 289:
 
|-
 
|-
 
|| Cursor on the options.
 
|| Cursor on the options.
−
|| Here we have options to choose for '''Join layer, Join field''' and '''Target field'''.
+
|| Here we have options to choose for '''Join layer''', '''Join field''' and '''Target field'''.
  
 
|-
 
|-
Line 305: Line 303:
  
 
Select '''Districts.'''
 
Select '''Districts.'''
−
|| '''Join field''' is the field or '''attribute''' in the '''Rainfall table''' to be '''joined'''.
+
|| '''Join field''' is the field or '''attribute''' in the '''Rainfall table''' to be joined.
  
 
In the '''Join field''', select '''District''' from the drop-down.
 
In the '''Join field''', select '''District''' from the drop-down.
Line 313: Line 311:
  
 
Cursor on the '''District'''.
 
Cursor on the '''District'''.
−
|| '''Target field''' is the field to be '''joined''' in the '''Stations table'''.
+
|| '''Target field''' is the field to be joined in the '''Stations table'''.
  
−
In the '''Target field, District '''is already selected.
+
In the '''Target field''', '''District''' is already selected.
  
 
This field is common to both the '''tables'''.
 
This field is common to both the '''tables'''.
Line 334: Line 332:
 
|| Check the boxes for the columns '''January''' to '''Annual Average '''in the text box.
 
|| Check the boxes for the columns '''January''' to '''Annual Average '''in the text box.
  
−
Click on ''' OK''' button to close the''' Add vector join '''dialog box.
+
Click on ''' OK''' button to close the '''Add vector join ''' dialog box.
  
 
|-
 
|-
Line 340: Line 338:
  
 
Click on '''Apply''' button >>'''OK'''.
 
Click on '''Apply''' button >>'''OK'''.
−
|| In''' Layer Properties '''dialog box, information about layer and columns '''joined''' are indicated at the top.
+
|| In''' Layer Properties '''dialog box, information about layer and columns joined are indicated at the top.
  
 
Click on '''Apply''' button, then '''OK '''button.
 
Click on '''Apply''' button, then '''OK '''button.
Line 347: Line 345:
 
|| Cursor on '''QGIS ''' Interface.
 
|| Cursor on '''QGIS ''' Interface.
  
−
Cursor on the '''Layer Panels'''.
+
Cursor on the '''Layers Panels'''.
  
 
Right Click on '''Stations layer'''.
 
Right Click on '''Stations layer'''.
Line 388: Line 386:
 
|| Click on '''Open '''button in '''Add Vector Layer''' dialog box.
 
|| Click on '''Open '''button in '''Add Vector Layer''' dialog box.
  
−
Point to the '''Admin''' layer in the '''Layer panel'''.
+
Point to the '''Admin''' layer in the '''Layers panel'''.
  
 
|| Again click on '''Open''' button in the '''Add Vector Layer''' dialog box.
 
|| Again click on '''Open''' button in the '''Add Vector Layer''' dialog box.
Line 399: Line 397:
  
 
|-
 
|-
−
|| Click the '''Admin layer''' in the '''Layer panel'''.  
+
|| Click the '''Admin layer''' in the '''Layers panel'''.  
  
 
Drag and bring it below station layer.
 
Drag and bring it below station layer.
Line 441: Line 439:
 
Scroll down the menu and click on '''Data Management Tools'''.
 
Scroll down the menu and click on '''Data Management Tools'''.
  
−
From the sub-menu select '''Join attributes by location.'''
+
From the sub-menu select '''Join attributes by location'''.
  
 
|-
 
|-
Line 504: Line 502:
  
 
|-
 
|-
−
|| Cursor on the '''Layer panel'''.
+
|| Cursor on the '''Layers panel'''.
  
 
||On the canvas, a new '''layer''', '''Joined layer''' is added in the '''Layers Panel'''.
 
||On the canvas, a new '''layer''', '''Joined layer''' is added in the '''Layers Panel'''.
Line 519: Line 517:
  
 
|-
 
|-
−
|| Close the attribute table.
+
|| Close the '''attribute table'''.
 
||Close the '''attribute table'''.
 
||Close the '''attribute table'''.
  

Latest revision as of 16:13, 28 October 2020

Visual Cue Narration
Slide Number 1

Title Slide

Welcome to this tutorial on Table Joins and Spatial Joins in QGIS.
Slide Number 2

Learning Objectives

In this tutorial, we will learn to join the attribute tables of two data-sets having,
  • common field (Table Join), and
  • same spatial data (Spatial Join)
Slide Number 3

System Requirement

Here I am using

Ubuntu Linux OS version. 16.04

QGIS version 2.18

Slide Number 4

Pre-requisites

To follow this tutorial learner should be familiar with QGIS interface.

For the prerequisite QGIS tutorials, please visit this website.

https://spoken-tutorial.org/

Slide Number 5

Example for Demonstration

Download the folder given in the Code files link, located below the player.

Extract the contents of the downloaded zip file and save it in a folder.

Show the map Stations.shp on Desktop. I have already downloaded the Code files, extracted and saved in a folder on the Desktop.
Double click on Code files folder.

Point to Stations.shp.

Double-click on the folder to open it.

Locate Stations.shp file in the extracted folder.

Point to Stations.shp

Point to other files.

The Stations.shp file shows locations of meteorological stations or air stations across India.

Here we also have other files required to practice this tutorial.

Slide Number 6

About Table Joins

Joining the attribute tables means, combining the attribute data between two data-sets.
Slide Number 7

About Table Joins

https://www.gislounge.com/working-with-tables-mastering-qgis/

There are two ways to join a table,

1. Table Join that is, joining tables having one or more common column data.

2. Spatial Join means joining tables having same spatial data.

In this tutorial we will be demonstrating both the methods.

For more information please visit this website.

Open QGIS software . Open QGIS interface.
Cursor on the interface. First, we will join attribute tables having common field.
Click on Add Vector Layer tool. Click on Add Vector Layer tool from the toolbar on the left-side.
Add Vector Layer dialog box open.

Click on Browse button.

Navigate to Stations.shp file.

Add Vector Layer dialog box opens.

Click on Browse button next to Dataset field.

A dialog-box opens.

Navigate to Stations.shp file from the Code files folder on the Desktop.

Click on Open button. Click on Open button.
Add vector Layer dialog box will open.

Click on Open.

In the Add vector layer dialog box, click on Open button.
Cursor on Layers panel.

Cursor on the map.

The Stations.shp layer will be added in the Layers Panel.

The corresponding map appears on the canvas.

Right-click on Stations.shp layer in the Layers panel. This map shows point features, corresponding to meteorological stations located in different states in India.

Let us open the attribute table for this layer.

In the Layers Panel, right-click on Stations.shp.

Click on Open Attribute Table option. From the context menu choose Open Attribute Table option.
Cursor on the Attribute table window. Attribute table opens.

Notice that data for only one attribute District is available.

Click on the minimize button on the top-left corner of the Stations table. Minimize the attribute table.
Click on Layers menu from the menu bar. Now we will add another data-set which is a spreadsheet to the Layers Panel.

This data set is in CSV format.

Click on Layer menu.

From the drop-down menu select Add Layer option.

From the sub-menu click on Add Delimited Text Layer option.

Click on Layer menu on the menu bar.

Click on Add layer.

From the sub-menu click on Add Delimited Text Layer option.

Create a layer from Delimited Text File dialog box open.

Click on Browse Button.

A dialog box opens.

Click on Browse button located next to File Name field.

Navigate to Rainfall.csv file.

Click on Open Button.

A dialog box opens.

Navigate to Rainfall.csv file from the Code files folder on the Desktop.

Click on Open button.

Cursor on Delimited Text File dialog box.

Select CSV as File Format.

In the Delimited Text File dialog box, select CSV as File Format.
Click on the No geometry (attribute only table). Select Geometry definition as No geometry.

Leave all other fields as such.

Click on OK button. Click on OK button.
Cursor on Layers panel. On QGIS canvas, Rainfall layer will be added in the Layers Panel.
Cursor on Rainfall.csv Layer.

Click on the Open Attribute Table from context menu.

Right-click on Rainfall layer.

Click on the Open Attribute Table from the context menu.

Point towards the attribute table columns. Attribute table opens.

The attribute table has rainfall data from January to December for various districts.

Cursor on the tables. Maximize the Stations attribute table and compare both the tables.
Point to District field in both the tables. Please note, the District field is common in both Rainfall and Stations layers.
Add attribute data.

Cursor on the station layer.

Now we will add the attribute data from Rainfall layer to Stations layer.
Cursor on the first column of the Attribute table . In the Stations attribute table there is only one column named District.

We will add the Rainfall data to the Stations attribute table.

Click on x button on the top-left corner of the table. Close the attribute tables.
Click on Stations layer in Layer’s panel.

Cursor on Stations layer.

Select Stations layer in the Layers Panel.

This layer will receive the new data from the Rainfall layer.

Right click on the stations layer.

Click on the Properties option from context menu.

Right-click on Stations layer.

Click on the Properties option from the context menu.

Layer Properties dialog box will open.

Click on Joins from the left panel.

Layer Properties dialog box opens.

Click on Joins from the left panel.

Cursor on the Joins window.

Click on Plus sign.

Add vector Join dialog-box opens.

In the new window, click on the plus sign, located on the bottom-left corner.

Add vector join dialog box opens.

Cursor on the options. Here we have options to choose for Join layer, Join field and Target field.
Cursor on Join layer field. Join layer will be Rainfall layer, from which data will be added to Stations attribute table.

Here Rainfall layer is already selected.

Click on the Join field.

Cursor on the Join field.

Select Districts.

Join field is the field or attribute in the Rainfall table to be joined.

In the Join field, select District from the drop-down.

Point to the Target field.

Cursor on the District.

Target field is the field to be joined in the Stations table.

In the Target field, District is already selected.

This field is common to both the tables.

Cursor on Choose which fields are Joined.

Scroll down the slider to see all the check-boxes.

Check the check-box for Choose which fields are joined.

The text-box below is now populated with all the columns and check-boxes.

Check all the check-boxes from January to Annual Average.

Click on OK button.

Check the boxes for the columns January to Annual Average in the text box.

Click on OK button to close the Add vector join dialog box.

Dialog box will open.

Click on Apply button >>OK.

In Layer Properties dialog box, information about layer and columns joined are indicated at the top.

Click on Apply button, then OK button.

Cursor on QGIS Interface.

Cursor on the Layers Panels.

Right Click on Stations layer.

On the QGIS interface, as shown earlier open attribute table for Stations layer.
Select Open attribute table. Observe that this table shows Rainfall data for all the stations.
Click on x button on top-left corner of Stations layer. Close attribute table.
Add another data set.

Point to the Layers panel.

Next, we will learn how to join attribute table of two data-sets by location.

Let us add another layer to the Layers Panel.

Select Add Vector Layer tool from the left tool-bar. For this click on Add Vector Layer tool.
Click on the Browse button next to the Dataset.

Navigate to the Admin.shp

In the Add Vector Layer dialog box, click on Browse button.

Navigate to the Admin.shp from the Code files folder on the Desktop.

Click on Open button. Click on Open button.
Click on Open button in Add Vector Layer dialog box.

Point to the Admin layer in the Layers panel.

Again click on Open button in the Add Vector Layer dialog box.

Admin layer is now added to the Layers Panel.

Cursor on the map. Admin layer map opens showing the administrative state boundaries of India.
Click the Admin layer in the Layers panel.

Drag and bring it below station layer.

Cursor on the map.

Click the Admin layer in the Layers panel.

Drag and bring it below Stations layer.

Now we can view the point features located in different States.

Right click on the Admin layer.

Select Open attribute table.

Open attribute table for Admin layer.
Cursor on the attribute table.

Minimize the Admin attribute table.

Open Stations attribute table.

The attribute table shows information related to states.

Minimize the Admin attribute table.

Again open the Stations attribute table.

Now we will join the attributes by location for Stations layer and Admin layer.

Cursor on map. Close both the attribute tables.
Click on Vector >>Data Management Tools>>Join attributes by location. Click on Vector menu.

Scroll down the menu and click on Data Management Tools.

From the sub-menu select Join attributes by location.

Cursor on Join attribute by location. Join attributes by location dialog box opens.
Cursor on Target vector layer. Click on the drop-down for Target vector layer.
Cursor on the Stations layer.

Select Stations[EPSG: 4326].

Here we need to specify the target vector layer for joining the attribute table.

In our case we need to add new data to the Stations layer.

Hence Stations layer is the Target Layer.

So we will select Stations [EPSG: 4326] from the drop-down as target layer.

Cursor on join Vector Layer.

Select Admin [EPSG: 4326] from the drop-down.

Click on the drop-down for Join vector layer.

Here we have to select the layer which we want to join with the target layer.

Select Admin [EPSG: 4326] from the drop-down.

Cursor on Geometry predicate.

Click on within option.

Scroll down and show all the options.

Here we have several options for joining the attributes.

We are interested to find the meteorological stations located in various states.

So under Geometric predicate, we will select within check-box.

Scroll down.

Check the check-box for Open output file after running algorithm. Check the check-box for Open output file after running algorithm.
Click on the Run button. Leave the rest of the settings as default.

Click on the Run button.

Point to status bar. Status bar at the bottom shows the progress of the processing algorithm.

Wait for the process to complete.

Cursor on the Layers panel. On the canvas, a new layer, Joined layer is added in the Layers Panel.
Open attribute table of Joined layer Right-click on the Joined layer and open attribute table.
Cursor on the attribute table. This table contains all the attributes from Admin layer for each point on the Stations layer.

Every point feature has information about the state.

Close the attribute table. Close the attribute table.
Click on the Project menu from the menu bar>> select Save As >> type appropriate name. To save the project, click on Project menu from the menu bar.

Select Save As option.

In the Save As dialog-box >> type QGIS-Stations as file name.

Select Desktop as location.

Give an appropriate name and select a convenient location.
Click on Save button. Click on Save button.
Let us summarize.
Slide Number 8

Summary

In this tutorial we have learnt to join the attribute tables of two data-sets having,
  • common field (Table Join), and
  • same spatial data (Spatial Join)
Slide Number 9

Assignment

As an assignment,
  • Join the rainfall data from June to December with the stations data.
  • Use Rainfall.csv and Stations.shp files given in the Code files folder.
Show the glimpse of completed assignment. The completed assignment should look like this.
Slide Number 10

About the Spoken Tutorial Project

This video summarizes the Spoken Tutorial project.

Please download and watch it.

Slide Number 11

Spoken Tutorial Workshops

The spoken tutorial project team conducts workshops and gives certificates.

For more information please write to us.

Slide Number 12

Forum for specific question

Please post your timed queries on this forum.
Slide Number 13

Acknowledgement

The Spoken Tutorial Project is funded by NMEICT, MHRD Government of India.

This tutorial is contributed by Ambadas Maske from College of Engineering Pune, Snehalatha Kaliappan and Himanshi Karwanje from IIT Bombay.

Thank you for joining.

Contributors and Content Editors

Karwanjehimanshi95, Madhurig, Nancyvarkey, PoojaMoolya, Snehalathak

Retrieved from "https://script.spoken-tutorial.org/index.php?title=QGIS/C3/Table-Joins-and-Spatial-Joins/English&oldid=54261"