2

I am 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 general approach is taken from: Spatial join based on polygon centroid in QGIS

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. This doesn't seem excessive.

However, the virtual layer is taking around 7 minutes to create, and much longer (30min+ ?) to bring up the attribute table (F6).

Query for the virtual layer 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))

There are other questions around slow performance of virtual layers eg:

However, I haven't found any useful answers, other than possibly to try PostGIS.

Are there any practical ways of speeding up the performance of the virtual layer in this instance?

[Note that I can produce the same outcome by:

  • creating a Centroid layer from the suburb layer using the Centroid tool from processing toolbox
  • adding a field using say array_first(overlay_within('ABSStatisticalAreasLevel3_',"SA3_NAME11"))
  • joining this field back to the original suburb layer via a unique id

However, it is considerably more clunky. Hence the desire to get virtual layers working.]

Tom Brennan
  • 4,787
  • 6
  • 26
  • How long does it take to create the layer if the output is not a virtual file? VRTs apply processing when read/loaded which means it is most likely running the query every time the VRT is accessed. Lastly, have you tried using ST_Intersects rather than ST_Contains? – PyMapr Sep 18 '23 at 05:24
  • How would I create the output layer without a virtual layer? I did google that but the results all seemed to suggest that to use SQL, you create a virtual layer. – Tom Brennan Sep 18 '23 at 06:45
  • A wild guess: the features are extremely complex so computing the centroid and the contain/intersect is slow. In such case, simplifying or subdividing the geometries can help a lot. That being said, since the boundaries are unlikely to change, I wouldn't bother using a virtual layer. – JGH Sep 18 '23 at 12:01
  • 1
    Regarding the time difference: in the 1st case (7min), the intersection is computed only for the features being displayed. For the 2nd case (30min), it is computed for every feature – JGH Sep 18 '23 at 12:02
  • The Centroid tool from the toolbox only takes about 2s to run, which doesn't suggest that the features are overly complex. Likewise, using overlay_within only takes about 30s - so not instantaneous, but still way quicker than the virtual layer. What is the SQL-based alternative to a virtual layer in QGIS? – Tom Brennan Sep 18 '23 at 21:52
  • Try to use _search_frame_ in your query! This lists.osgeo.org/pipermail/qgis-developer/2021-May/063582.html helped me a lot.. – Kay Oct 03 '23 at 13:30

1 Answers1

0

Based on Kay's comment, adding a _search_frame_ clause to the SQL query speeds it up considerably. The following SQL creates the layer in around 30s, and the attribute table (F6) is brought up in up in just under a minute. So around 15-30x as fast as the original.

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

It is also possible to switch the _search_frame_ around. This was a touch slower, but not conclusively. Which will be faster apparently depends on the details of the geometries relative to each other (count, location). The _search_frame_ seems to work better applied to the layer with the higher geometry count. With the following SQL, the virtual layer still took about 30s to load, and the attribute table appeared in just over a minute.

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

See: https://lists.osgeo.org/pipermail/qgis-developer/2021-May/063582.html for further details and links.

Note that 30-60s is still slow if you are used to just panning and zooming around, and bringing up attribute tables instantaneously. If you don't need the Virtual Layer (eg the underlying data is not changing), you can use the modified SQL queries above in the native Execute SQL tool in the Processing Toolbox, and they will run in similar times to the load of the attribute table above.

Tom Brennan
  • 4,787
  • 6
  • 26