[postgis-users] Transform overlapping polygons to non-overlapping?
Dylan Beaudette
dylan.beaudette at gmail.com
Wed Jul 16 11:07:09 PDT 2008
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: postgis-users-bounces at postgis.refractions.net
> > [mailto:postgis-users-bounces at postgis.refractions.net] 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: postgis-users-bounces at postgis.refractions.net
> > [mailto:postgis-users-bounces at postgis.refractions.net] 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:* postgis-users-bounces at postgis.refractions.net 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
> >>>> postgis-users at postgis.refractions.net
> >>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >>>
> >>> _______________________________________________
> >>> postgis-users mailing list
> >>> postgis-users at postgis.refractions.net
> >>> 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
> >>> postgis-users at postgis.refractions.net
> >>> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >>
> >> _______________________________________________
> >> postgis-users mailing list
> >> postgis-users at postgis.refractions.net
> >> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> 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
More information about the postgis-users
mailing list