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.

  • Where do you want to save the intersecting polygons ID's? as columns? could a string with comma separated values be enough? – Alexandre Neto Oct 4 '16 at 14:17
  • @AlexandreNeto, in a column preferably. But I am open to a csv option. – PyMapr Oct 4 '16 at 14:33
  • I think you are looking for ST_ClusterIntersecting. – John Powell Oct 4 '16 at 17:33
  • 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. – alphabetasoup Oct 5 '16 at 0:08

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.