[Qgis-user] Spatial indexes in virtual layers

[Johannes Kröger (WhereGroup)]

Hi list,

using spatial predicates on virtual layers is slow by default as spatial 
indexes are not used.

https://docs.qgis.org/testing/en/docs/user_manual/managing_data_source/create_layers.html#use-of-indexes 
says that one can use the _search_frame_ column to "trigger the use of a 
spatial index". The page has an example where this is compared to a 
MBR/BBOX:

SELECT * FROM vtab WHERE 
_search_frame_=BuildMbr(-2.10,49.38,-1.3,49.99,4326)

What does it actually mean and what does it really do? What is the 
_search_frame_ really and why can we compare it for *equality* against a 
simple bounding box? Is the "=" operator used for a different logic?

Does it magically "trigger" the use of the sources' spatial indexes in 
the background? That would surprise me.

The original docs of virtual layers at 
https://github.com/mhugo/qgis_vlayers#index-support _search_frame_ say

 > For spatial indexes, a 'hidden' field named '/search_frame/' is 
created for each virtual table (i.e. each referenced layer of the 
virtual layer). The bounding box of the given geometry will be used to 
restrain the query to a particular region of space. This bounding box is 
passed to the underlyng provider by a QgsFeatureRequest with a "filterRect".

and show this example:

 > SELECT * FROM pt, poly WHERE pt._search_frame_ = poly.geometry AND 
Intersects(pt.geometry, poly.geometry)

I have some trouble parsing that in my brain. Does it mean that there 
are N QgsFeatureRequests and each uses the _search_frame_ BBOX of its 
feature geometry as filterRect?


It would be great to demystify this and make people more aware of how to 
make their virtual layers not slow.

Thanks!

Hannes

PS: QgsFeatureRequest.setFilterRectcould use a note about the spatial 
predicate being used to determine which features to return. Intersection 
probably but it is not clear. People might expect Within. Might file/fix 
this later but feel welcome to do it before me. :)

-- 
Johannes Kröger / GIS-Entwickler/-Berater

---------------------------------------------
Schon gewusst?
In unserem Blog geben wir Tipps & Tricks zu Open-Source-GIS-Software
und berichten aus unserem Experten-Alltag:
https://wheregroup.com/blog/
---------------------------------------------

WhereGroup GmbH
Grevenweg 89
20537 Hamburg
Germany

Tel: +49 (0)228 / 90 90 38 - 36
Fax: +49 (0)228 / 90 90 38 - 11

johannes.kroeger at wheregroup.com
www.wheregroup.com
Geschäftsführer:
Olaf Knopp, Peter Stamm
Amtsgericht Bonn, HRB 9885
-------------------------------

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20220913/34e66a49/attachment.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: OpenPGP_0x840A4B843789797F.asc
Type: application/pgp-keys
Size: 2472 bytes
Desc: OpenPGP public key
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20220913/34e66a49/attachment.key>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: OpenPGP_signature
Type: application/pgp-signature
Size: 665 bytes
Desc: OpenPGP digital signature
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20220913/34e66a49/attachment.sig>