Sunday 22 May 2016

How to create dissolved buffers in QGIS with PostgreSQL/PostGIS?


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?



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;

No comments:

Post a Comment

arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...