I'm trying to speed up a query in a virtual layer. I noticed that the query runs at the same time for a partial extension and full extent of the layer shown on the map. So I thought if I filtered the query for the canvas extension I would gain an acceleration in performance.

I thought of something like this but don't know if is possible:

      s.id       AS id,
      s.codigo   AS codigo,
      s.geometry AS geometry,
      SUM(d.e1)  AS sum1,  
      SUM(d.e2)  AS sum2,  
      SUM(d.e3)  AS sum3,  
      SUM(d.e4)  AS sum4,  
      SUM(d.e5)  AS sum5,
      DT_atu     AS DT_atu
 FROM "lyr1" AS s, 
      "lyr2" AS d  
WHERE d._search_frame_ = s.geometry
  AND ST_WITHIN(d.geometry, s.geometry) 
  AND ST_WITHIN(s.geometry, iface.mapCanvas())
GROUP BY s.id;
  • 2
    Do you have a spatial index? and another one on id? These are likely the bottleneck, as I am pretty sure the virtual layer already filters by spatial extent. – JGH Nov 20 '18 at 12:33

The trick to use a python expression in a virtual layer is to first create a QGIS expression, and then to call this expression.

1) create the expression. You can do this from several places, one of them being to open an attribute table and click the button select features using an expression. Click new file and paste the following code:

Define new functions using @qgsfunction. feature and parent must always be the
last args. Use args=-1 to pass a list of values as arguments

from qgis.core import *
from qgis.gui import *
from qgis.utils import iface

@qgsfunction(args='auto', group='Custom')
def currentExtent(feature, parent):
    return QgsGeometry.fromRect(iface.mapCanvas().extent())

2) in your virtual layer, you can now access your function using:

-- get a polygon filling the current extent
select currentExtent() as geometry;

-- get intersecting features (entire features)
select * from a
   where st_intersects(a.geometry,    currentExtent());

-- Using the spatial index + filter spatially
select * from a
   where  _search_frame_ =   currentExtent();
| improve this answer | |
  • Wow, never thought about using expression functions in QGIS virtual layers. Very cool!! – christoph Nov 30 '19 at 17:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.