Extract part of multipolygon that does not intersect with another geometry in Spatialite / QGIS DB manager

Question

I have a point layer (with a buffer value), and a polygon layer (footprint) that intersects the point buffer.

Using only SQL in QGIS Virtual Layers (i.e. based on Spatialite), how can I get the difference between the point buffer (buffer(pt.geometry,bufvalue)) and footprint, where the difference does not intersect with the point geometry?

See the following image for an illustration - green is the footprint, orange pentagon is the point, orange polygon is the difference. I just want the ticked geometry.

The difference returns a multipolygon, and I only need one part. However, I can't split multipolygons into single polygons (there is no ST_Dump() equivalent in Spatialite due to SQLite not handling array values, apparently)

And I can't use the CLI/GUI commands (which separate multigeometries into 'elementary geometries') as I want to create a virtual layer in QGIS, so it has to be 100% in SQL.

How can I approach this problem using SQL in Virtual Layers only? (i.e. not using any other processing tools)

I am thinking I may have to loop over the number of geometries for multipolygons using recursive functions in place of generate_series(), and test if each one intersects with the point geometry...

Answer 1

I was trying to improve my query a bit, to dump single-part geometries in a virtual layer. I would appreciate if you can test it with your data:

WITH RECURSIVE parts AS (
  SELECT d.buffer_fid, d.poly_fid, d.total, 1 AS part
  FROM dump_me AS d
  UNION ALL
    SELECT d.buffer_fid, d.poly_fid, d.total, part + 1 AS part
    FROM dump_me AS d 
    INNER JOIN parts as p
      ON d.buffer_fid = p.buffer_fid AND d.poly_fid = p.poly_fid AND p.part < d.total
),
dump_me AS (
  SELECT buffer_fid, poly_fid, NumGeometries( geom) AS total
  FROM diff
),
diff AS (
  SELECT b.fid AS buffer_fid, p.fid AS poly_fid , ST_Difference( b.geom, p.geometry) AS geom
  FROM buffers AS b
  INNER JOIN polys AS p
    ON ST_Intersects( b.geom, p.geometry) = 1
),
buffers AS (
  SELECT fid, ST_Buffer( geometry, <bufvalue>) AS geom
  FROM points
),
dumped AS (
SELECT p.buffer_fid, p.poly_fid, p.part, GeometryN( d.geom, p.part) as geom
FROM parts AS p
INNER JOIN diff AS d
  ON p.buffer_fid = d.buffer_fid AND p.poly_fid = d.poly_fid
)
--SELECT * FROM buffers;
--SELECT * FROM diff;
--SELECT * FROM dump_me;
--SELECT * FROM parts;
--SELECT * FROM dumped;
-- to debug, comment on the following lines and uncomment the previous SELECT clause of your interest
SELECT d.buffer_fid, d.poly_fid, d.part, d.geom
FROM dumped AS d
INNER JOIN points AS p
 ON  d.buffer_fid = p.fid AND ST_Intersects( d.geom, p.geometry) = 0;

I am assuming the existence of a points layer and the existence of a polys layer, both with unique values for the fid field.

The same would appreciate the opinion of any SQL specialist to improve the query using tools available in SpatiaLite, in the virtual layers environment of QGIS.