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...
SELECT geometry_n(geometry,1) as geom FROM 'layer'
. Therefore, if you could know the index of the part to be extracted (even the parts should be able to be sorted withorder_parts()
) I think the problem would be reduced to how to implement the expressions in the query.num_geometries(geometry) >1
) using a sequence of indices generated by a CTE ingeometry_n()
, then keeping the ones that don't intersect with the point geometry... then collecting those back... cripes.