On Wednesday 16 July 2008, Brent Fraser wrote: > Regina, > > I'm not convinced ST_Union is the way to go (using ST_Overlaps OR > ST_Intersects as a condition). Basically I want to iterate over the > collection (recursively?) clipping one polygon to another until I'm left > with no overlapping (or intersecting) polygons (planar topology). This is > sightly more complicated than the way I originally posed the problem (I > wanted to created slivers from the overlapping areas to get planar > topology). > > A little background: > > My polygons represent a classification of vegetation of a large area of > interest. In theory any point in the area of interest must fall in one and > only one polygon. Due to an artifact of my image segmentation process, my > polygons currently have slight overlap which I need to "dissolve" (and I > don't care which polygon the overlap sliver gets dissolved into). > > Thanks! > Brent.
It really sounds like you need to perform some "cleaning" operations in a topologically-aware GIS. Check out the v.clean module in GRASS. Cheers, Dylan > Paragon Corporation wrote: > > One more question - you sure you want ST_Overlaps and not ST_Intersects. > > If one geometry sits completely inside another, it is not considered to > > overlap, but they do intersect. > > > > -----Original Message----- > > From: [EMAIL PROTECTED] > > [mailto:[EMAIL PROTECTED] On Behalf Of > > Paragon Corporation > > Sent: Wednesday, July 16, 2008 12:41 PM > > To: 'PostGIS Users Discussion' > > Subject: RE: [postgis-users] Transform overlapping polygons to > > non-overlapping? > > > > Brent, > > > > INSERT INTO temp3_lines (the_geom) > > SELECT ST_ExteriorRing( ST_GeometryN(the_geom, generate_series(1, > > ST_NumGeometries(the_geom)))) AS the_geom FROM temp2_polys; > > > > Can be done more efficiently using ST_Dump > > > > INSERT INTO temp3_lines (the_geom) > > SELECT ST_ExteriorRing((ST_Dump(the_geom)).geom) AS the_geom FROM > > temp2_polys; > > > > > > Regarding the unioning I mentioned - I thought about more why what I > > proposed had still overlapping polygons and I realized its because the > > grouping I proposed needs to be A recursive query (which means you'd need > > to wrap it in an sql or plpgsql > > function) since as it stands it would only find the first root overlaps > > and not the A overlap B overlap C (e.g. c would not be in the same > > grouping and A,B if it doesn't also overlap with A) . > > > > So two ways > > 1) Write recursive query (using a plpgsql or sql helper function) - which > > I haven't given much thought to the most efficient way of doing that > > > > Or > > > > 2) Repeat the union thing I mentioned over and over again until you have > > a set that has no more overlapping polygons. > > > > Then you do a ST_Dump to get back individual polygons. > > > > Hope that helps, > > Regina > > > > > > -----Original Message----- > > From: [EMAIL PROTECTED] > > [mailto:[EMAIL PROTECTED] On Behalf Of Brent > > Fraser > > Sent: Wednesday, July 16, 2008 12:13 PM > > To: PostGIS Users Discussion > > Subject: Re: [postgis-users] Transform overlapping polygons to > > non-overlapping? > > > > To all, > > > > My quest for non-overlapping polygons continues: > > > > I started with a table (temp_polys) of 3253 polygons (with some overlap) > > with a "class" attribute. > > > > To get rid of overlapping polys with the same class value: > > CREATE TABLE temp2_polys as SELECT class, ST_UNION(the_geom) from > > temp_polys GROUP BY class; > > > > This created a table of 32 multi-polygons (grouped by class). I still > > have to remove the overlap between polygons with different class values, > > so my plan is to convert to linestrings, node the linestrings, > > polygonize, and (re)assign the class value using StarSpan. So first: > > > > Convert to linestrings: > > INSERT INTO temp3_lines (the_geom) SELECT ST_ExteriorRing( > > ST_GeometryN(the_geom, generate_series(1, ST_NumGeometries(the_geom)))) > > AS the_geom FROM temp2_polys; > > > > This produced 1768 linestring records. Attempting to node the > > linestrings: INSERT INTO temp4_lines (the_geom) SELECT St_Union(the_geom) > > AS the_geom FROM temp3_lines; > > > > Yikes! This query ran for 4.5 hours and crashed Postgres (1.8 gHz > > Windows XP, Postgres 8.3.3, PostGIS 1.3.3). > > > > I dumped the temp3_lines table into a shapefile and asked OpenJump to > > node AND polygonize. That took 24 seconds. > > > > Since the above data is a small sub-set of my 1.2 million polygons, > > OpenJump is not really a solution for cleaning the data all at once. > > Looks like some scripting is in order... > > > > Brent > > > > Brent Fraser wrote: > >> Regina, > >> > >> The "SELECT MAX..." query didn't work on my sub-set of 12800 polygons. > >> It created 12643 polygons some of which overlap (I expected more, not > >> less, than the original). > >> > >> I may try converting to linestrings, creating one "minimum bounding > >> rectangle" for the entire dataset, then doing an intersect of the > >> lines with the MBR. In my case this would be ok as there are not > >> attributes on the polygons yet. > >> > >> Thanks! > >> Brent > >> > >> Obe, Regina wrote: > >>> Brent, > >>> > >>> I guess it really depends on how exactly you want to achieve > >>> non-overlapping. > >>> If for example you are basing it on some sort of attribute and all > >>> your overlapping polygons are valid > >>> > >>> Then a simple > >>> > >>> SELECT ST_Union(the_geom) As newgeom, field1 FROM sometable GROUP BY > >>> field1 > >>> > >>> 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. > >>> > >>> 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 > >>> > >>> SELECT MAX(a.gid) As newgid, ST_Union(a.the_geom) As the_geom FROM > >>> poly a GROUP BY (SELECT MAX(r.gid) FROM poly r > >>> WHERE (a.gid = r.gid OR ST_Overlaps(r.the_geom, a.the_geom))); > >>> > >>> Hope that helps, > >>> Regina > >>> > >>> --------------------------------------------------------------------- > >>> --- > >>> *From:* [EMAIL PROTECTED] on behalf of > >>> Brent Fraser > >>> *Sent:* Fri 7/11/2008 12:14 PM > >>> *To:* PostGIS Users Discussion > >>> *Subject:* Re: [postgis-users] Transform overlapping polygons to > >>> non-overlapping? > >>> > >>> To All, > >>> > >>> 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. > >>> > >>> 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? > >>> GDAL's OGR > >>> PostgreSQL's libpq > >>> other? > >>> > >>> Thanks! > >>> Brent Fraser > >>> > >>> Brent Fraser wrote: > >>>> PostGIS'ers, > >>>> > >>>> I've got a table of overlapping polygons. How can I make it a > >>>> table of non-overlapping polygons? > >>>> > >>>> For example, if table "polys2" contains two polygons A1 and B1 > >>>> which overlap. I'd like to create table "polys3" with polygons A2, > >>>> B2, C2, where C2 is the overlap region of A1 and B1, and A2 = A1 - > >>>> C2, and B2 = > >>>> B1 - C2. > >>>> > >>>> Looking at the overlay operations in the JTS doc it looks like > >>>> doing an Intersection (to get only the overlapping area) then > >>>> adding the Symmetric Difference (to get the non-overlapping areas) > > > > might work. > > > >>>> Am I on the right track or is there an easier way (since all the > >>>> polygons are in one table)? > >>>> > >>>> Thanks! > >>>> Brent Fraser > >>>> _______________________________________________ > >>>> postgis-users mailing list > >>>> [email protected] > >>>> http://postgis.refractions.net/mailman/listinfo/postgis-users > >>> > >>> _______________________________________________ > >>> postgis-users mailing list > >>> [email protected] > >>> http://postgis.refractions.net/mailman/listinfo/postgis-users > >>> > >>> --------------------------------------------------------------------- > >>> --- > >>> > >>> * 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. * > >>> > >>> --------------------------------------------------------------------- > >>> --- > >>> > >>> * Help make the earth a greener place. If at all possible resist > >>> printing this email and join us in saving paper. * > >>> > >>> * * > >>> > >>> * * > >>> > >>> > >>> --------------------------------------------------------------------- > >>> --- > >>> > >>> _______________________________________________ > >>> postgis-users mailing list > >>> [email protected] > >>> http://postgis.refractions.net/mailman/listinfo/postgis-users > >> > >> _______________________________________________ > >> postgis-users mailing list > >> [email protected] > >> http://postgis.refractions.net/mailman/listinfo/postgis-users > > > > _______________________________________________ > > postgis-users mailing list > > [email protected] > > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > > > > _______________________________________________ > > postgis-users mailing list > > [email protected] > > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > > > > _______________________________________________ > > postgis-users mailing list > > [email protected] > > http://postgis.refractions.net/mailman/listinfo/postgis-users > > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users -- Dylan Beaudette Soil Resource Laboratory http://casoilresource.lawr.ucdavis.edu/ University of California at Davis 530.754.7341 _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
