6

I have a Layer Group surveyor with lots of layers (shapefiles) with seemingly random names, and with identical schemas. I would like to combine them all to one layer; preferably with a Virtual Layer. The following does work.

SELECT * FROM monday
UNION 
SELECT * FROM wed_22
UNION 
SELECT * FROM parking

However, this requires manually typing all the layers. Is there a better solution, where I won't have to manually type the layers?

Taras
  • 32,823
  • 4
  • 66
  • 137
Mads Skjern
  • 759
  • 3
  • 15
  • You may want to have a look at merging those shapefiles - here with the filename as column, using ogr2ogr (with this you could also merge into a different file format, or into a GPKG/DB directly) – geozelot May 11 '22 at 18:48

2 Answers2

8

You can use python to print out the sql needed in the console, then copy paste it to the virtual layer. Add all layers to a group named surveyor and execute:

root = QgsProject.instance().layerTreeRoot()
layerlist = []

#List the layers in surveyor group
for layergroup in root.children():
    if layergroup.name() == 'surveyor':
        for lyr in layergroup.children():
            layerlist.append(lyr.layer().name())


#Print the sql
for n, layer in enumerate(layerlist):
    print("select * from {}".format(layer))
    if n<len(layerlist)-1: #Dont print "union" at the end
        print("union")

enter image description here

BERA
  • 72,339
  • 13
  • 72
  • 161
  • To work only with shapefiles, please refer to this thread : https://gis.stackexchange.com/questions/430438/getting-all-shapefile-layers-in-project-using-pyqgis – Taras May 11 '22 at 19:34
  • I expect OP to understand to only place the vector layers in sruveyor layer group – BERA May 12 '22 at 05:10
4

Another possible workflow.

Step 1. Apply the "Merge vector layers" tool from the Processing Toolbox (Ctrl+Alt+T)

Step 2. Create a "Virtual Layer" through Layer > Add Layer > Add/Edit Virtual Layer... with the following query:

SELECT *
FROM "Merged"
Taras
  • 32,823
  • 4
  • 66
  • 137