<div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="auto">The answer below in response to your question about "temporary spatial index". However, I don't see how a spatial index would help speed up the query in your question as that one doesn't make use of any spatial predicates, and I don't know if you can explicitly create non-spatial indexes on virtual layers (in your example, that would be an index on <font face="monospace">"objekt-id"</font> in both of the virtual layers). <br></div><div dir="auto"><br></div><div dir="auto">Virtual Layers use SpatiaLite lib (if I understand correctly, the tables are read into memory or virtualized, in a way, they have no "memory' of their origins, hence, no access to underlying indexes): you need to work out your queries as if you are working in a SpatiaLite db, i.e. you need to invoke the indexes explicitly (<a href="https://www.gaia-gis.it/fossil/libspatialite/wiki?name=SpatialIndex">https://www.gaia-gis.it/fossil/libspatialite/wiki?name=SpatialIndex</a>).<br></div><div dir="auto"><br></div><div>Luckily, there is a convenience function added by Virtual Layers: _search_frame_, so that you can skip the part about referring to the underlying index table etc.See:</div><div><a href="https://docs.qgis.org/3.16/en/docs/user_manual/managing_data_source/create_layers.html#use-of-indexes">https://docs.qgis.org/3.16/en/docs/user_manual/managing_data_source/create_layers.html#use-of-indexes</a></div><div><a href="https://github.com/mhugo/qgis_vlayers#index-support">https://github.com/mhugo/qgis_vlayers#index-support</a></div><div><a href="http://osgeo-org.1560.x6.nabble.com/QGIS-Developer-QGIS-virtual-layer-incredibly-slow-td5403735.html">http://osgeo-org.1560.x6.nabble.com/QGIS-Developer-QGIS-virtual-layer-incredibly-slow-td5403735.html</a></div><div><br></div><div>E.g.:</div><div style="margin-left:40px"><span style="font-family:monospace">SELECT b.zip_code, a.geometry<br>FROM buildings a, zip_code_areas b<br>WHERE<br> a._search_frame_ = b.geometry<br> AND ST_Within(a.geometry, b.geometry)</span></div></div></div></div></div></div></div></div></div></div><br></div><div dir="ltr">Note that "<span style="font-family:monospace">b._search_frame_ = a.geometry</span>" instead of <span style="font-family:monospace">"a._search_frame_ = b.geometry"</span> above also speeds up the query, but not by as much in my specific situation: this depends on the counts of geometries on each layer and the "average" relationship between features in one layer to the other.<br></div><div dir="ltr"><br><div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Thu, May 6, 2021, 04:33 Bo Victor Thomsen <<a href="mailto:bo.victor.thomsen@gmail.com" target="_blank">bo.victor.thomsen@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div>
<p>Hi list -</p>
<p>I have created a virtual layer using Python and shown this in my
QGIS mapping window like this:</p>
<p><font face="monospace">layer = QgsVectorlayer(?layer=<i>...layer
"ref" definition... </i>&layer=<i>... "cur" definition...</i>&query=select
ref.* from ref left join cur on ref."objekt-id" =
cur."objekt-id" where cur."objekt-id" is NULL,"My layer",
"virtual")</font><code><br>
</code></p>
<p>i.e a left join between a Postgres layer "cur" and a GeoPackage
layer "ref". And it works !!! (Thank you Hugo and whoever that has
developed this facility).</p>
<p>However, it is somewhat slow to react, when you ex. are using the
"info" tool on the layer. AFAIK, it's not using the indexes in the
original layers. <br>
</p>
<p>Is there any method (Python or otherwise) to speed up the
response of the virtual layer ? For example by adding a temporary
spatial index to the resulting virtual layer ?? Some "magical
keywords" in the creation parameters for the layer ??? <br>
</p>
--
<pre cols="72">Med venlig hilsen / Kind regards
Bo Victor Thomsen</pre>
</div>
_______________________________________________<br>
QGIS-Developer mailing list<br>
<a href="mailto:QGIS-Developer@lists.osgeo.org" rel="noreferrer" target="_blank">QGIS-Developer@lists.osgeo.org</a><br>
List info: <a href="https://lists.osgeo.org/mailman/listinfo/qgis-developer" rel="noreferrer noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/qgis-developer</a><br>
Unsubscribe: <a href="https://lists.osgeo.org/mailman/listinfo/qgis-developer" rel="noreferrer noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/qgis-developer</a><br>
</blockquote></div>
</div></div></div></div></div></div>