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. _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
