Create new layer in QGIS using SQL query

Question

This is a related question to: Speed up performance of virtual layer based on GeoPackages in QGIS

In that question, I was trying to use a virtual layer in QGIS (3.30) to add a region field to a suburb layer, by locating the centroid of the suburb within a region. The virtual layer was taking a ridiculously long time to create.

One of the comments to the question included: "That being said, since the boundaries are unlikely to change, I wouldn't bother using a virtual layer".

So the question is: Is there a straightforward way of creating a new layer in QGIS using an SQL query, that doesn't involve a virtual layer. Obviously, not being a virtual layer, the query would only run once on creation - which would be fine for this particular use.

enter image description here

Both the suburb layer and the region layer are of polygon type, in GeoPackage format, with a spatial index added using Vector->Create Spatial Index... I confirmed that the spatial indexes existed, using ogrinfo eg: ogrinfo -sql "SELECT HasSpatialIndex('suburbs', 'geom')" suburbs.gpkg

The suburb layer has around 4500 records, the region layer has 100 records.

SQL query is as follows - the region details/geometries are in the layer ABSStatisticalAreasLevel3_ :

select s.*, l3.SA3_NAME11
from Suburbs_ s 
join ABSStatisticalAreasLevel3_ l3 on ST_CONTAINS(l3.geometry, ST_CENTROID(s.geometry))

• jbalk: It looks like you could do it with the GDAL tool Execute SQL using the sqlite dialect, maybe

Answer 1

You can use the GDAL Execute SQL tool. My test data is different (ABS SA3 and SAL 2021), but the SQL statement I used is otherwise the same as yours. Both input sal and sa3 layers are in a geopackage. I used the -nln parameter to specify the new layer name.

enter image description here

enter image description here

enter image description here

EDIT: If your layers are in separate gpkgs, you need to tell the sqlite driver to attach the other files using the PRELUDE_STATEMENTS open option:

enter image description here

• Tom Brennan: Thanks, that's useful. However I couldn't get it to work as my two layers were in two separate GeoPackages, and the interface didn't seem to allow for more than one input file? Is that possible? I have managed to get it to work using the native Execute SQL tool, which I'll write up as a separate answer.
• user2856: @TomBrennan you can attach the other gpkg, see edit

Answer 2

You can use the native Execute SQL tool under Vector General in the Processing Toolbox.

The main advantage of this tool over the GDAL Execute SQL tool is that the native SQL can use the layers as named in the Layers panel - so the same SQL will work as for the Virtual Layers tool.

enter image description here

enter image description here

Timewise, it took around 12-15 minutes to run - comparable to the Virtual Layer time - but obviously a one-off cost.

Note that as per Speed up performance of virtual layer based on GeoPackages in QGIS, you can add a _search_frame_ clause to the SQL and this will create the layer in around 60s (as opposed to 12 minutes+).

select s.*, l3.SA3_NAME11
from Suburbs_ s 
join ABSStatisticalAreasLevel3_ l3 on ST_CONTAINS(l3.geometry, ST_CENTROID(s.geometry))
where s._search_frame_ = l3.geometry