Merge intersecting polygons in same table using Postgis

Question

I am trying to merge polygons in the same table where the polygons intersect while preserving a unique id (can be any id from the features which intersect) in postgis. Currently, I end up with more features instead of less features after running this sql statement more than once.

create table general.bldg_done as 
SELECT DISTINCT least(q.a_id, q.b_id) as gid,
q.newgeom as geom FROM (
 SELECT a.gid as a_id, b.gid as b_id, 
 ST_Union(a.geom,b.geom) as newgeom 
 FROM general.bldg_to_merge a JOIN general.bldg_to_merge b ON a.geom &&
 b.geom AND ST_Intersects(a.geom, b.geom)
 WHERE a.gid <> b.gid"
 ) 
q;

For some polygons there are more than two intersecting polygons, therefore the query is run more than once.

• Alexandre Neto: Where do you want to save the intersecting polygons ID's? as columns? could a string with comma separated values be enough?
• PyMapr: @AlexandreNeto, in a column preferably. But I am open to a csv option.
• John Powell: I think you are looking for ST_ClusterIntersecting.
• alphabetasoup: Why do you want one of the IDs to be preserved? They could also be re-obtained by taking the unioned geometry and seeing what features intersect it.

Answer 1

I had a similar issue and solved it with the following in Postgis:

INSERT INTO urban_dissolved.dissolved 
SELECT fid,id,(ST_DUMP(ST_UNION(ST_SNAPTOGRID(geom,0.0000)))).geom as geom
FROM urban.bldg GROUP BY fid, id;