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 May 2 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 May 2 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 May 2 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 May 2 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 May 2 at 15:56

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 May 3 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 May 3 at 7:21

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.