7

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.

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

1 Answer 1

3

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 and acknowledge you have read our privacy policy.

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