How to create dissolved buffers using QGIS virtual layers?

Question

To create dissolved buffers (single geometries) in PostGIS I use ST_Dump and ST_Union:

SELECT 
    row_number() over() AS gid,
    sbqry.*
FROM (
    SELECT
        (ST_Dump(ST_Union(ST_Buffer(input_layer.geom, 100)))).geom::geometry(Polygon, 31256) AS geom
    FROM input_layer
) AS sbqry;

As far as I know QGIS virtual layers are based on SpatiaLite. So at least ST_Dump isn't supported. Is there a SpatiaLite equivalent to the query posted above?

Answer 1

For ST_Dump, there are roughly equivalent dot macro commands found here: https://www.gaia-gis.it/fossil/libspatialite/wiki?name=dot+macros, under the section: 'useful in order to resolve complex Geometries into many distinct rows (more or less equivalent to PostGIS's own ST_Dump)'.

---

For ST_Union: According to this article: https://groups.google.com/forum/#!msg/spatialite-users/FTO_cmLCfpE/HCLil1hzjgoJ, ST_Union is not working properly in 4.3.0 but will be fixed in 4.4.0. The workaround until that release is:

---

Original Query:

Select f.field1 as field1, st_union(f.geometry) as geometry From tableA as f Group by field1;

Workaround:

Select f.field1 as field1, st_unaryunion(st_collect(f.geometry)) as geometry From tableA as f Group by field1;

---

This information about the workaround came from this post: What is the efficient way to dissolve polygons using Spatialite 3.0