QGIS – Creating Dissolved Buffers Using PostGIS

bufferpostgisqgis

Creating a dynamic buffer in QGIS I use the following query:

CREATE VIEW buffer40units AS
SELECT gid, st_buffer(geom,40)::geometry(polygon, 31492) AS geom
FROM point;

Now I want to dissolve overlapping buffers but all queries I've tried aren't working.

CREATE VIEW buffer40units AS
SELECT gid, st_buffer(st_collect(geom),40)::geometry(polygon, 31492) AS geom
FROM point;

CREATE VIEW buffer40units AS
SELECT st_buffer(st_collect(geom),40)::geometry(polygon, 31492) AS geom
FROM point;

It seems that QGIS needs a (new) gid for every row in the view. Can anybody please help me?

Best Answer

ST_Collect is probably not the function you are looking for, as this simply combines geometries into a geometry collection of some type, and does not actually union/dissolve them. ST_Union, on the other hand, does dissolve overlaps, and assuming polygonal input (which is most probable in conjunction with ST_Buffer and an input table called point), and overlapping polygons will return a MultiPolygon. To return the individual unioned (dissolved) polygons you need to use ST_Dump which returns the geometries and a path id, which can be used as the gid required for visualization in QGIS -- as it is an array, you need to use [1] to access it. You will also want to union the buffers, rather than the other way round, as you currently have. Putting this altogether, you get:

CREATE VIEW buffer40units AS
SELECT 
   g.path[1] as gid, 
   g.geom::geometry(Polygon, 31492) as geom 
FROM
   (SELECT 
     (ST_Dump(ST_UNION(ST_Buffer(geom, 40)))).* 
   FROM point
) as g;
Related Question