[postgis-users] unable to assign hole to a shell

[Martin Davis]

Patrick Valsecchi wrote:
> Sorry, but I'm new to PostGIS and I come from a world where self-intersecting 
> polygons are considered mostly harmless. ;-)
>   
And PostGIS comes from a world (defined by the OGC) where 
self-intersecting polygons are absolutely forbidden!  So we have a 
collision of worlds here...   This is actually more than just 
flippancy.  One very good reason for having this constraint is that if 
you allow self-intersections in general, you can wind up with situations 
where the interior and exterior of a polygon is not very well-defined.  
I suspect that in your world you have some extra rules (which may be 
implicit) which allow you to determine the precise topology of your 
polygons.  Or, perhaps your polygons are still constrained in some way 
which allows this determination (for instance, perhaps in your model 
lines can touch but not cross?)

If that's the case, you could certainly develop a procedure to convert 
your polygons into OGC-valid polygons.  And if your data is constrained 
enough, this might be fairly simple to develop (i.e. using existing 
functions such as Kevin described). 
> I think my next post on this mailing list will be about a query on polylines 
> that makes the postgres backend crash (signal 11). I'm still investigating on 
> this one...
>
>   
>> Be careful using st_buffer() to cleanup geometries.  Using 0.0 to trick
>> the function to not actually buffer at all is really a hack that does
>> not always work.  I think we have planned to build a proper
>> geometry-cleanup function down the road, but until then, just be careful.
>>     
>
> What do you mean by "not working"? Returning a wrong polygon or throwing an 
> error?
>
> Wouldn't that be a bug of st_buffer, if the 0.0 trick is not working?
>   
No.  ST_buffer is technically only specified to work on valid polygons. 
The fact that it often helps to clean up invalid ploygons is a lucky 
artifact of its implementation - but it's not part of the design. 
> A geometry-cleanup would be more than welcome. I've around 5 million polygons 
> in my DB and a lot of them are self-intersecting. Self intersection is a 
> trick used in my source data to reduce the number of points needed to 
> represent the polygons.
>
>   
>> I was able to rebuild your polygon this way:
>> 1. extract the exterior ring - you may want to add to this subquery all
>> the interior rings if you have any
>> 2. extract all the points of the ext ring as ordered pairs (ie as
>> point1-point2, point2-point3, point3-point4, ....)
>> 3. recreate line segments from all these ordered point pairs, removing
>> duplicates.
>> 4. rebuild the polygon using st_buildarea()
>>     
>
> Would this trick work on every polygon? By construction my polygons have no 
> interior ring.
>   
Knowing that you have no holes definitely helps.

Another thing you might be able to try is to:
- convert the polygon to a linestring
- call intersection on it with a single point from the linestring (this 
effectively nodes the linework)
- polyognize the resulting linework back to a polygon
>   
>   

-- 
Martin Davis
Senior Technical Architect
Refractions Research, Inc.
(250) 383-3022