<HTML dir=ltr><HEAD><TITLE>Re: [postgis-users] Transform overlapping polygons to non-overlapping?</TITLE>
<META http-equiv=Content-Type content="text/html; charset=unicode">
<META content="MSHTML 6.00.2900.3354" name=GENERATOR></HEAD>
<BODY>
<DIV id=idOWAReplyText67761 dir=ltr>
<DIV dir=ltr><FONT face=Arial color=#000000 size=2>Brent,</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2>I guess it really depends on how exactly you want to achieve non-overlapping.</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2>If for example you are basing it on some sort of attribute and all your overlapping polygons are valid</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2>Then a simple</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2>SELECT ST_Union(the_geom) As newgeom, field1</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2>FROM sometable</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2>GROUP BY field1</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2>I think will guarantee non-overlapping polygons because as part of the process of ST_Union - it would irradicate the overlapping regions to just create one. That is part of the reason why its so much slower than ST_Collect for example.</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2>For your exact case below - you would union all the overlapping polygons together which could be really slow depending on how many overlap. The query I would write to achieve that would be something like this</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr>SELECT MAX(a.gid) As newgid, ST_Union(a.the_geom) As the_geom<BR>FROM poly a<BR>GROUP BY (SELECT MAX(r.gid) FROM poly r <BR> WHERE (a.gid = r.gid OR ST_Overlaps(r.the_geom, a.the_geom)));</DIV>
<DIV dir=ltr> </DIV>
<DIV dir=ltr>Hope that helps,</DIV>
<DIV dir=ltr>Regina<BR></DIV></DIV>
<DIV dir=ltr><BR>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B> postgis-users-bounces@postgis.refractions.net on behalf of Brent Fraser<BR><B>Sent:</B> Fri 7/11/2008 12:14 PM<BR><B>To:</B> PostGIS Users Discussion<BR><B>Subject:</B> Re: [postgis-users] Transform overlapping polygons to non-overlapping?<BR></FONT><BR></DIV>
<DIV>
<P><FONT size=2>To All,<BR><BR> There doesn't seem to be an obvious answer to the problem given below (aka cleaning polygons, creating planar polygons, etc). I did see a note on the PostGIS wiki wishlist to "Add a geometry cleaner". There is also a suggestion to convert to linestrings, node, then polygonize (while that may work for a small set of polygons, I've got 1.1 million to clean). JTS, Geos, etc will likely fail due to the large number of polygons so I'll need a different approach.<BR><BR> I'm considering writing some code to iterate through my table of polygons, cleaning a small subset at a time. I think using PostGIS for the geometry storage and spatial query/selection makes sense. Any suggestions on which API to use?<BR> GDAL's OGR<BR> PostgreSQL's libpq<BR> other?<BR><BR>Thanks!<BR>Brent Fraser<BR><BR>Brent Fraser wrote:<BR>> PostGIS'ers,<BR>><BR>> I've got a table of overlapping polygons. How can I make it a table of<BR>> non-overlapping polygons?<BR>><BR>> For example, if table "polys2" contains two polygons A1 and B1 which<BR>> overlap. I'd like to create table "polys3" with polygons A2, B2, C2,<BR>> where C2 is the overlap region of A1 and B1, and A2 = A1 - C2, and B2 =<BR>> B1 - C2.<BR>><BR>> Looking at the overlay operations in the JTS doc it looks like doing an<BR>> Intersection (to get only the overlapping area) then adding the<BR>> Symmetric Difference (to get the non-overlapping areas) might work.<BR>><BR>> Am I on the right track or is there an easier way (since all the<BR>> polygons are in one table)?<BR>><BR>> Thanks!<BR>> Brent Fraser<BR>> _______________________________________________<BR>> postgis-users mailing list<BR>> postgis-users@postgis.refractions.net<BR>> <A href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>><BR>_______________________________________________<BR>postgis-users mailing list<BR>postgis-users@postgis.refractions.net<BR><A href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR></FONT></P></DIV></BODY></HTML>
<HTML><BODY><P><hr size=1></P>
<P><STRONG>
The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer.
</STRONG></P></BODY></HTML>
<P><hr size=1></P>
<P><STRONG><font size="2" color="339900"> Help make the earth a greener place. If at all possible resist printing this email and join us in saving paper. </p> <p> </font></STRONG></P>