2

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:

SELECT 
      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;
1
  • 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
9

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();
6
  • Wow, never thought about using expression functions in QGIS virtual layers. Very cool!!
    – christoph
    Nov 30 '19 at 17:01
  • How could we extend the function currentExtent() so that even layout map items benefit from it?
    – christoph
    Jun 16 at 8:19
  • @christoph I am not sure to understand your question. Please ask a new question with more details (what do you mean by "benefit" and "layout map items", that is not already covered by the virtual layer speed-up?)
    – JGH
    Jun 16 at 12:12
  • here's my question regarding this issue: gis.stackexchange.com/questions/401620/…. Your currentExtent() function only returns mapCanvas extents and not the extents of a Layout map item.
    – christoph
    Jun 16 at 12:18
  • @christoph Ah I see, maybe this? I have no idea how (not) nicely a virtual layer would handle such function, especially when viewing the map canvas...
    – JGH
    Jun 16 at 13:26

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.