8

What I need is to unite intersecting polygons in a single geometry and leave others without change.

enter image description here

I am able to do this using the following query, which unites all of the polygons into a single geometry and then dumps it to the separate ones.

INSERT INTO unioned(geom)
SELECT ST_MULTI((ST_Dump(ST_Union(geom))).geom) from footprints;

enter image description here

The problem is that I have more than a million rows in a database and ST_Union operation never ends. Is there any way to achieve the same without joining all of the geometries?

6

Here's a different approach that I expect will be much faster for the type of data you showed above:

ST_UnaryUnion(unnest(ST_ClusterIntersecting(geom))) FROM footprints;

This query:

  1. Uses ST_ClusterIntersecting to finds groups of building footprints that form a "chain of intersection", and sticks each group into a GeometryCollection. Returns an array.
  2. Uses unnest to the array of geometry collections into a row for each geometry collection.
  3. Uses ST_UnaryUnion to union the items within each geometry collection.
  • Many thanks for your reply @dbaston. Do you know why ST_ClusterIntersecting returns geometry with SRID=0 ? How this could be solved? Trying to execute the following query: INSERT INTO fixed_footprints(geom) SELECT ST_UnaryUnion(unnest(ST_ClusterIntersecting(geom))) as geom FROM footprints; I got the following error: [22023] ERROR: Geometry SRID (0) does not match column SRID (4326) – Serhey Ladonya Mar 23 '18 at 10:20
  • think I solved this: INSERT INTO fixed_footprints(geom) SELECT ST_Multi(st_setsrid(ST_UnaryUnion(unnest(ST_ClusterIntersecting(geom))), 4326)) as geom FROM footprints; Will try this out on a bigger set – Serhey Ladonya Mar 23 '18 at 10:57
  • @SerheyLadonya looks like the SRID issue is a bug in ST_ClusterIntersecting. Thanks for pointing it out, I will get it fixed. – dbaston Mar 23 '18 at 12:17

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.