In QGIS, take a polygon grid (named 'grid') and a multipolygon layer (named 'surfaces').

For each feature in surfaces, I want to union all features of the grid that are disjoint (not intersected) with. I need to do this with a virtual layer (datasets regularly updated and exported with an atlas).

I have tried this two solutions, they work but are extremely slow (4-5 minutes):

select s.id, st_union(g.geometry)
from grid g, surfaces s
where not(st_intersects(g.geometry,s.geometry)) -- st_dissolve also works but is slower...
group by s.id

select s.id, st_union(g.geometry)
from grid g
join surfaces s on not(st_intersects(g.geometry,a.geometry))  -- st_dissolve also works but is slower...
group by s.id

The problem seems to be the intersects. The time is about the same with or without st_union.

For testing, I tried to add a rule-based symbology to my grid, with this rule:

not(intersects($geometry, @atlas_geometry ))

And I get the result instantly. Why intersects is slow on the Virtual Layer and fast in symbology? And how can I get a fast result in the Virtual layer?


What I want to achieve (without union to see the grid): result with grid visible

And with union, to calculate the area of each part of the grid: enter image description here

  • 1
    How many features do you have in 'grid' and 'surfaces' layers? What are the technical characteristics of your system?
    – Taras
    Commented May 2, 2021 at 12:32
  • 1
    I guess you created a spatial index on both of your layers? Could you try using virtual fields with field-calculator and QGIS expressions? They will also update automatically.
    – Babel
    Commented May 2, 2021 at 13:22
  • 3
    In the virtual layer, you are computing the intersection/union for every shape between the two layers (that's a LOT of computation!), while in the ruled based symbology you are computing the intersection between 1) the visible geometries and 2) a single atlas geometry, which is likely involving just a few shapes. How are you using/timing the virtual layer?
    – JGH
    Commented May 2, 2021 at 13:45
  • @Babel Yes I have indexed my two layers. And I can't use virtual fields because output of my virtual layer because they can't create new features.
    – romainbh
    Commented May 2, 2021 at 15:56
  • @JGH The goal is to make an atlas for every feature in 'surfaces' and show the empty spaces symbolized by the grid - and calculate area for each.
    – romainbh
    Commented May 2, 2021 at 15:56

1 Answer 1


The issue is the cross join on non-intersection. Your query reads:

  • for every row in g, return every row in s that does not intersect with the current g

which seems in fact what you want, but about the worst performing operation on unfiltered and non-indexed relations in SQL.

Example: for 100 rows in g and 100 rows in s, and with no intersection between any of them, you would get 10.000 rows in return that will then get grouped in a (costly) geometric union.

QGIS likely utilizes a low-level, in-memory index structure on loaded layers, and the expression is executed on features inside the view-port only - both of these facts do not apply to SQLite-interpreted Virtual Layers.

  • That last sentence is great. Helps me understand why they are so slow sometimes
    – M Bain
    Commented May 3, 2021 at 1:14
  • 1
    It may be that it's not even related to an index in the stricter sense, but that expressions on symbology is a graphic culling mechanic, in which case it's GPU powered. In any way, to achieve better performance on the base data, that data should live in a data format that allows for indexing, and a better performing query (e.g. using NOT EXISTS (<sub-query>), or NOT IN (<sub-query) ).
    – geozelot
    Commented May 3, 2021 at 7:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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