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"
For some polygons there are more than two intersecting polygons, therefore the query is run more than once.