[postgis-users] Advice on Unioning Polygons

[Stephen Woodbridge]

To be a little more specific and build on Nicklas' idea try:

select <list of attributes>, ST_Union(geom) as new_geom
   from my_polygons a, my_polygons b
  where a.id != b.id and st_intersects(a.geom, b.geom)
  group by <list of attributes>;

Depending on the version of PostGIS you might get better perfomance using:

a.geom && b.geom and st_distance(a.geom, b.geom)

instead of

st_intersects(a.geom, b.geom)

So this avoids union with yourself and only collects the other polygons 
the intersect with the original.

-Steve


On 2/1/2013 6:27 AM, Nicklas Avén wrote:
> Hallo
>
> As said before you will gain a lot if upgrading since PostGIS has
> implemented cascading union since 1.3.
>
> You say that you only want to union intersecting polygons.
>
> Easier is if it is ok to union all polygons with common attribute to
> multipolygons.
>
> ST_Union is an aggregate function which means it can be used like this:
>
> SELECT ST_Union(geom) as new_geom , group_id FROM my_polygons GROUP BY
> group_id;
>
> Then if you want polygons taht doesn't intersect on different rows you
> can dump them, then it could look like this:
>
> SELECT (ST_Dump(new_geom)).geom as newest_geom, id FROM
> (SELECT ST_Union(geom) as new_geom , group_id FROM my_polygons GROUP BY
> group_id) a;
>
> There is many more possibilities, but the point is that you are in the
> SQL-world now. iterations is done by the database in a very efficient way.
>
> HTH
>
> Nicklas
>
>
>
> 2013-02-01 Rebecca Clarke wrote:
>
>  >
> Hi there
>  >
>
>  >
>  >
> Looking for some advice.
>  >
>
>  >
>  >
> I have a table with thousands of polygon records.
>  >
>
>  >
>  >
> I want to union all intersecting polygons that have the same attributes
> into one polygon (So one record, rather than 10 or 20 etc.).
>  >
>
>  >
>  >
> Can anyone recommend the best way to do this.
>  >
>
>  >
>  >
> I can do a loop which goes through each record, unions its geometry with
> the geometry's of the other matching records that intersect with it,
> then move to the next record. Problem with this is that it creates
> duplicate polygons when it comes to a record that has already been
> unioned with a previous one record. I can easily delete any duplicate
> records generated, but I'm wondering if there's a less long winded way.
>  >
>
>  >
>  >
> I hope I'm explaining myself correctly.
>  >
>
>  >
>  >
> My details are:
>  >
> postgis 1.3.5
>  >
> PostgreSQL 8.3.8
>  >
>
>  >
>  >
> Many thanks in advance.
>  >
>
>  >
>  >
> Rebecca
>  >
>
>  >
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>