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.
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))