[postgis-users] Transform overlapping polygons to non-overlapping?

Kevin Neufeld kneufeld at refractions.net
Wed Jul 16 11:24:38 PDT 2008


Ah, another reason to have topology finished in PostGIS. If only, eh?

Brent, what if you created a plpgsql script that simply iterated through 
a table of source polygons and slowly inserted them one at a time into a 
target table by:

foreach poly in source table

   1. move all bounding box overlaps in target table with current poly 
into a temp table.
   2. take all polygons from your temp table and union the current poly 
using the techniques described on the wiki and what Regina suggested.
   3. insert the individual polygons from the overlay back into the 
target table using ST_Dump.

end foreach.

It might take a while, but in the end, you would have a single 
topologically correct (non-overlapping) polygonal table.

You may want to insert the geometries ordered by some x,y grid so that 
your working area will more likely be cached in memory.

Also, you may want to perform vacuum once in while on your target table. 
  It could bloat really quickly.  So don't iterate through all the 
source polygons all at once.

Cheers,
Kevin


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.
> 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



More information about the postgis-users mailing list