7

I am trying to make a Virtual layer that is based on a map layer that only returns the features in the current map canvas extent. The purpose is to setup a Data Graph that shows statistical data of only the features in the current map extent.

I cannot find any examples of this being done in QGIS or using SQL. Is it possible?

Taras
  • 32,823
  • 4
  • 66
  • 137
MrKingsley
  • 1,443
  • 13
  • 26

1 Answers1

13

The trick is to create a function that access QGIS graphical interface, and that is piped to the query of the virtual layer.

  1. Open a function editor (from anywhere, including from field calculator) and create a new function that reads the canvas extent and returns it as a geometry.
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())
  1. Create a new virtual layer and use the new function, selecting features that intersects the displayed area. To get features entirely within the displayed area, use ST_Within() instead.
SELECT * 
FROM mylayer
WHERE st_intersects(mylayer.geometry, currentExtent());

Note that if you have the attribute table open, you would need to click the refresh icon after panning the map.

Taras
  • 32,823
  • 4
  • 66
  • 137
JGH
  • 41,794
  • 3
  • 43
  • 89
  • How will this handle features that are partially outside the map canvas? – csk Apr 26 '19 at 18:16
  • 1
    @csk Since it uses st_intersects, features that are partially displayed will be returned. One can change the query and use ST_Within instead (or any other spatial predicate) – JGH Apr 26 '19 at 18:19
  • 1
    @csk and since it return *, the original (complete) geometry will be returned – JGH Apr 26 '19 at 18:21
  • Thank you for the explanation. That's what I suspected, but wanted to be sure. A large feature that only slightly extends into the map canvas could have an unexpected impact on the graph. – csk Apr 26 '19 at 18:24
  • I didn't know custom expressions could be used in virtual layers ... that's cool ... i suppose they can have a big impact on queries execution times ... ? – Snaileater Apr 26 '19 at 18:50
  • @snaileater I guess it depends on what the function does. I made this currentExtent function + virtual layer in an attempt to improve display performance of a big layer... and it had 0 effect – JGH Apr 26 '19 at 19:41
  • It says my layer has no field geometry – MrKingsley Apr 29 '19 at 11:51
  • @TylerVeinot it is the default name. If you have another name (common if the data comes from Postgis), replace geometry by the real field name – JGH Apr 29 '19 at 11:53
  • Ha! Sorry, I had the geometry section set to defined It worked when I turned it too autondetect. This is very similar to my first attempt my function returned the min max xy's and in the SQL I had it making a polygon out of those values to intersect with. It was only giving me the extent that the map was at the time I made the query; Your approach not only is much cleaner but also auto updates to the new extent, can you tell me what part of the code is doing this for future reference? – MrKingsley Apr 29 '19 at 12:07
  • @TylerVeinot when panning, the virtual layer query is (re)executed, so it calls the currentExtent function (each time) and filters the data accordingly. The same occurs when opening/refreshing the attribute table. – JGH Apr 29 '19 at 12:16
  • Makes sense, I didn't get a chance to use mine in the virtual layer I only ran it in the expression builder. – MrKingsley Apr 29 '19 at 12:47
  • Is it possible to add a new function using python? I would like to add the function when a plugin is started. Thanks. – doktoreas Oct 31 '19 at 15:26
  • @doktoreas Please create a new question with all the important information (add a new function to what? based on what? from your own plugin or a 3rd party one? etc). You can link to this question if needed – JGH Oct 31 '19 at 15:31
  • This can probably be made faster by using _search_frame_ in the query: https://docs.qgis.org/3.22/en/docs/user_manual/managing_data_source/create_layers.html#use-of-indexes – bugmenot123 Sep 13 '22 at 08:42