7

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?

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

  • Hi jbalk, can you post a sample query for dissolved buffers (single geometries) in SpatiaLite? – eclipsed_by_the_moon Aug 20 '16 at 22:06
  • I already have in my answer. – jbalk Aug 22 '16 at 4:43

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.