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! HannesPS: 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.kroe...@wheregroup.com www.wheregroup.com Geschäftsführer: Olaf Knopp, Peter Stamm Amtsgericht Bonn, HRB 9885 -------------------------------
OpenPGP_0x840A4B843789797F.asc
Description: OpenPGP public key
OpenPGP_signature
Description: OpenPGP digital signature
_______________________________________________ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
