Joining multiple layers in QGIS

Question

I have about 25 GeoPackage files, each containing a table with an "ID" field and another with density information about that area. I want to join all those density information into one layer, using the "ID" as the linking criteria.

The problem is that "Join multiple attributes by field value" or the "Joins"-section in the layer properties can only join one layer at a time, so it would take a lot of time to process all of my files.

Is there another option to join all layers at once?

I tried, but this creates an output file for each join operation in this batch. I would like to get a single output file that combines all the tables. — derdan_iel, Jan 16 '22 at 13:01

score 7 · Accepted Answer · edited Jan 26 '22 at 07:36

7

You can use a Virtual layer through Layer > Add Layer > Add/Edit Virtual Layer... for that.

Use this query:

select
    *
from
    layer1 as l1,
    layer2 as l2,
    layer3 as l3,
    layer4 as l4
where
    l1.id = l2.id = l3.id = l4.id

The principle is demonstrated here with four layers, layer1 to layer4, each having an attribute field called id used to join them. Just add more layers using the same pattern in lines 2 and 3.

edited Jan 26 '22 at 07:36

Taras

32,823
4
66
137

answered Jan 17 '22 at 10:10

Babel

71,072
14
78
208

1

i would suggest being a bit more specific i.e. instead of select * use select l1.*, l2.*, l3.*, l4.* – Taras Jan 18 '22 at 14:13
Is there any difference between selecting all and selecting all fields from the layers listed separately? – Babel Jan 18 '22 at 15:58
1

Well, at least with select * there are undesired copies of the id as joined field for each layer, named id:1, id:2 etc. – winnewoerp Nov 22 '23 at 13:35

Taras · Answer 2 · 2022-02-24T10:20:56.890

Additionally, you may try using some PyQGIS for that purpose.

Let's assume there are three layers 'centroids_test', 'dresden_buildings_test', and 'dresden_buildings_test2' with corresponding attribute tables, see image below.

Proceed with Plugins > Python Console > Show Editor and paste the script below

# your input/main layer
input_layer = QgsProject.instance().mapLayersByName('centroids_test')[0] 
# this is a list with target layers that you would like to join to your input layer
target_layers = ['dresden_buildings_test', 'dresden_buildings_test2']

# mediator fields between input and target layers
input_field, target_field = 'id', 'id'

joinObject = QgsVectorLayerJoinInfo()
joinObject.setJoinFieldName(target_field)
joinObject.setTargetFieldName(input_field)
joinObject.setUsingMemoryCache(True)

for name in target_layers:
    layer = QgsProject.instance().mapLayersByName(name)[0]
    joinObject.setJoinLayerId(layer.id())
    joinObject.setJoinLayer(layer)
    input_layer.addJoin(joinObject)

Press Run script and get the output that will look like

For more details, please check the QgsVectorLayerJoinInfo class.

References:

Thank you, I actually wrote a script that is very similar to yours following this description: http://www.qgistutorials.com/de/docs/3/processing_algorithms_pyqgis.html — derdan_iel, Jan 27 '22 at 16:11

Joining multiple layers in QGIS

2 Answers2