5

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?

EDIT

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

romainbh
  • 1,045
  • 7
  • 13
  • 1
    How many features do you have in 'grid' and 'surfaces' layers? What are the technical characteristics of your system? – Taras May 02 '21 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 02 '21 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 02 '21 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 02 '21 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 02 '21 at 15:56
  • @Taras In 'grid' there are a lot of features (dependig of my QGIS projects, 2000 to 10000). In 'surfaces' are only max 10 features. And my computer is "standard" Win10x64 i7 8Go ram. – romainbh May 02 '21 at 15:57
  • you can try to limit the features to the visible ones before doing the group-by – JGH May 03 '21 at 11:24
  • 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:41

1 Answers1

10

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.

geozelot
  • 30,050
  • 4
  • 32
  • 56
  • That last sentence is great. Helps me understand why they are so slow sometimes – M Bain May 03 '21 at 01: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 03 '21 at 07:21