1

QGIS virtual layer has big performance impact. Even when panning or zooming in the canvas, a virtual layer could take seconds to minutes to refresh. From my speculation, the reason might be the query it has to go through.

My questions is, would that be possible to cache a virtual layer, so it doesn't have to go through all the query until a user asks for it?

Say, I may update the database once a day, so I would like the virtual layer to update also once a day on my command, instead of refreshing every time I zoom in or change a layer visibility.

Specifically, I have a virtual layer lot_comparison combining two layers, existing_lot(containing 900 records) and planning_lot (containing 300 records).

the query goes like this,

select st_union(pl.geometry), el.*, pl.*
from "existing_lot" as el, "planning_lot" as pl
where el.fid = pl.fid 
and (el."lotIDtype" in ('use previous lot index' ,'need subdivision') 
or pl."lotIDtype" = 'need new lot ID')
group by pl."identifier"
2
  • 2
    Could you add information about the virtual layer - how it's structured, size, and how added to canvas? – Simbamangu Sep 21 '20 at 5:54
  • @Taras Could you please explain more on that point? If you mean by making that layer invisible, that doesn't solve the problem, since I need to use it frequently, and that's when I experience the performance issue. – David H. J. Sep 21 '20 at 6:02
1

Other than using PostGIS suggested by Taras, or saving the virtual layer as a new regular layer suggested bay Jakob, I gound yet another way to speed this up. The solution was found in this webpage.

Basically it involves spatial indexes. The code would be like this:

select st_union(pl.geometry), el.*, pl.*
from "existing_lot" as el, "planning_lot" as pl
where el.fid = pl.fid 
    and (el."lotIDtype" in ('use previous lot index' ,'need subdivision') or 
    pl."lotIDtype" = 'need new lot ID')
    and pl._search_frame_ = el.geometry
    --the above logic is like this: aaa and (bbb or ccc) and ddd
group by pl."identifier"

I added code in the third to the bottom line, which means for each geoemtry of layer existing_lot, compute its bounding box, select only geometries of the planning_lot that are inside this box, then do the rest of the calculation.

It has an order issue, meaning el._search_frame_ = pl.geometry doesn't work, for reasons I haven't explored yet.

2
  • Do you have control of your 'and-or-and'. I would but some ( ) for clarifying how 'and or and' are interconnected. Something like: where (aaa and xxx or yyy) and zzz – Jakob Sep 21 '20 at 10:04
  • I'm not quite sure what you meant, but I reorganized that clause and added an explanation. – David H. J. Sep 21 '20 at 12:54
0

After you created a Virtual Layer, simply take off the check mark, see image below.

result

And the next time you need it click the checkmark back.

3
  • Uncheck a virtual layer could definitely speed up operations on all other layers. But when interacting with the virtual layer itself, the loading speed is still an issue. How could we deal with that problem? – David H. J. Sep 21 '20 at 6:14
  • 1
    Maybe use PostGIS – Taras Sep 21 '20 at 6:15
  • 1
    Save it in a file format or database table and then rerun it manually to get a update for another save. – Jakob Sep 21 '20 at 7:07

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.