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
[email protected]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
