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

[Patrick Valsecchi]

Hi,

Thanks for the quick answer! Comments bellow.

On Sunday 16 September 2007, Kevin Neufeld wrote:
> So you're trying to use ST_Buffer(poly, 0.0) to clean-up your polygon?

Yes, trying. Because if my polygons are not clean, PostGIS/GEOS is dying on me 
all the time. And I'm trying to document a method to fix polygons (thus the 
blog) to help the others in my case.

> A little investigation revealed that your polygon is not as harmless as
> you thought.  You actually have coincident line segments in the exterior
> ring of your polygon.
> LineSegment( 9.50351715087891 47.3943328857422 ) occurs three times.
> This, as well as other self-intersections, is why st_buffer() was dying
> on you.

Sorry, but I'm new to PostGIS and I come from a world where self-intersecting 
polygons are considered mostly harmless. ;-)

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?

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.

> -- Rebuild the polygon from all the linework
> SELECT ST_BuildArea(geom) AS geom
> FROM
> (
>    -- Recreate the line segments (2-point lines) from the ordered points.
>    SELECT ST_Union(ST_MakeLine(pt1, pt2)) AS geom
>    FROM
>    (
>       -- Extract the points as pairs from the exterior ring of the polygon
>       SELECT ST_PointN(geom, generate_series(1, ST_NumPoints(geom)-1))
> AS pt1,
>              ST_PointN(geom, generate_series(2, ST_NumPoints(geom))) AS pt2
>       FROM
>       (
>          -- The exterior ring of your polygon
>          SELECT ST_ExteriorRing(
>            'POLYGON((
>                9.50351715087891 47.3943328857422,
>                9.50386047363281 47.3943328857422,
>                9.50351715087891 47.3943328857422,
>                9.50248718261719 47.3943328857422,
>                9.50214385986328 47.3939895629883,
>                9.50180053710938 47.3943328857422,
>                9.50145721435547 47.3939895629883,
>                9.50111389160156 47.3936462402344,
>                9.50145721435547 47.3936462402344,
>                9.50145721435547 47.3939895629883,
>                9.50214385986328 47.3939895629883,
>                9.50248718261719 47.3939895629883,
>                9.50386047363281 47.3943328857422,
>                9.50351715087891 47.3943328857422))'::geometry) AS geom
>       ) AS ring
>    ) AS point_pairs
> ) AS line_segments
>
> On a side note, your GEOS version is a little out of date.  The new
> version doesn't crash when trying to buffer your polygon, but it does
> return an empty geometry collection.

That, IMHO, would be worse. I'd rather have an error than corrupted data.

>
> Hope this clarifies things.

Helps a lot, thanks.