10

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?

eclipsed_by_the_moon
  • 1,855
  • 21
  • 44
  • Did you try the Dissolve feature embeded in QGIS, Vector > GeoProcessing Tools > Dissolve – Shiko Aug 02 '16 at 07:05
  • I want to create the geometries dynamically. – eclipsed_by_the_moon Aug 03 '16 at 07:59
  • Using programming or commands ? as using ogr2ogr you can dissolve dynamically with the help of spatialite. http://www.gaia-gis.it/gaia-sins/spatialite-sql-4.3.0.html – Shiko Aug 03 '16 at 08:02
  • Not sure if it's possible, but that could be a start: https://www.gaia-gis.it/fossil/libspatialite/wiki?name=VirtualElementary – Matthias Kuhn Aug 03 '16 at 08:19
  • You'll need to use dot macro commands. In this article, there is a section - 'useful in order to resolve complex Geometries into many distinct rows (more or less equivalent to PostGIS's own ST_Dump)' - which should be what you need. Link to article: https://www.gaia-gis.it/fossil/libspatialite/wiki?name=dot+macros. Is this helpful? – jbalk Aug 04 '16 at 03:52

1 Answers1

2

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

Community
  • 1
jbalk
  • 7,505
  • 1
  • 17
  • 39