I've a bunch of overlapping polygons and I'm trying to partition the space to avoid having overlapping ones. I think my problem is fairly simple. Using some ESRI product and http://arcscripts.esri.com/details.asp?dbid=16700 my co-worker computed it in 48s.

I'm trying to do it with postgis using http://s3.opengeo.org/postgis-power.pdf#page=24 (guessing the details, using http://trac.osgeo.org/postgis/wiki/UsersWikiSimplifyPreserveTopology as inspiration) but it's so slow that I can't do it with more than 10 polys (I've got 800 of them to split). The slow part is the ST_Union, I tried various things, but none where successful, here is the current state of affairs :

select geom from
(select st_linemerge(st_union(geom)) as geom from
    (select st_exteriorring((st_dumprings((st_dump(t.geom)).geom)).geom) as geom from
        (SELECT geometry AS geom, id
               FROM tt
              WHERE campaign_id = 204
              ORDER BY id limit 200) t) t2) t3

this has been computing for 26 min (the linemerge() is actually not ). The polys are MultiPolygons in case the st_dump bugs you.

Do you have any tip ? The st_union() of the linework is the very slow part.



PS: here are some numbers : 852 multipolygons, leading to 14880 polygons, leading to 21467 linestrings totaling 315513 vertices.

  • If no-one answers you may wish to try the postGIS mailing list. – GIS-Jonathan May 22 '12 at 12:37
  • I'm not really fan of mailing lists, moreover, it might also be an GEOS problem, who might complain about JTS, well, I prefer keeping the problem open. – nraynaud May 22 '12 at 17:46
  • by collecting the linework and doing an union with an empty geometry, I can do it in 800s : st_union(st_collect(geom), st_setsrid(geomfromtext('POINT EMPTY'), 900913)) that's almost 20 times slower than the ESRI stuff. – nraynaud May 22 '12 at 17:49
  • 1
    from memory, try drop st_union from st_linemerge(st_union...) if it helps – simplexio Oct 26 '12 at 13:23

This answer may not help @nraynaud directly, but hopefully will shed some light on the subject.

There is a similar issue in spatiaLite < 4.0 due an issue with GEOS. See this link for a discussion of the issue.

The workaround is to replace the ST_Union() function with ST_UnaryUnion(ST_Collect( )). Unfortunately, ST_UnaryUnion is not available until postGIS 2.0 (as far as I can tell.)


Which version of PostGIS are you using? Unioning is much slower if you are using PostGIS < 1.4 or GEOS < 3.2. Much faster union was introduced in 1.4, but also requires GEOS 3.2+. So first if you are using lower than 1.4, I'd upgrade to at least 1.5.

SELECT postgis_full_version();

To check.

Also is your intention to keep the original edges of the polygons. If you just want to dissolve overlapping areas,

SELECT ST_Union(geom) FROM tt WHERE campaign_id = 204;

Would do the trick.

  • hello, here is the result : "POSTGIS="1.5.3" GEOS="3.3.2-CAPI-1.7.2" PROJ="Rel. 4.8.0, 6 March 2012" LIBXML="2.7.3" USE_STATS". I could not find any relevant addition to union in GEOS recently. I found one speedup in polygon unions, but I'm not merging polygons, and this speedup seems controversial. I absolutely don't want do union my polygons as I have to add values for overlapping polygons. – nraynaud Jun 3 '12 at 17:10

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.