[GIS] Merge intersecting polygons in same table using Postgis

mergepostgispostgresqlsql

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.

Best Answer

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;
Related Question