Thanks a lot, I will try and get back with result.
On Tue, Mar 24, 2009 at 1:24 PM, Obe, Regina <[email protected]> wrote: > Ivan, > > Oops more of a variant. I see you have invalid polygons as well not > just rings. Hmm try this instead. I didn't check my logic, but > hopefully you get the idea if this doesn't compile. > > This is a bit of overkill if you have no multipolys with invalid rings. > But I'll leave as an exercise for you to figure out the simpler variant > if you need it. > > CREATE OR REPLACE FUNCTION remove_invalidpoly(geometry) > RETURNS geometry AS > $BODY$ > SELECT ST_BuildArea(ST_Collect(b.final_geom)) as filtered_geom > FROM (SELECT ST_MakePolygon((/* Get outer ring of polygon */ > SELECT ST_ExteriorRing(a.the_geom) as outer_ring /* ie > the outer ring */ > ), ARRAY(/* Get all inner rings > a particular area */ > SELECT ST_ExteriorRing(b.geom) as inner_ring > FROM (SELECT (ST_DumpRings(a.the_geom)).*) b > WHERE b.path[1] > 0 /* ie not the outer ring */ > AND (ST_NPoints(b.geom) > 2 AND ST_IsClosed(b.geom)) > ) ) as final_geom > FROM (SELECT g.geom As the_geom > FROM ST_Dump($1) As g > WHERE ST_NPoint(g.geom) > 2 AND ST_IsClosed(g.geom) > ) a > ) b > $BODY$ > LANGUAGE 'sql' IMMUTABLE; > > > -----Original Message----- > From: [email protected] > [mailto:[email protected]] On Behalf Of Obe, > Regina > Sent: Tuesday, March 24, 2009 8:14 AM > To: PostGIS Users Discussion > Subject: RE: [postgis-users] reparing invalid geometry (polygon defined > byoneor two points) > > Ivan, > > I think a variant of Simon's script would work for you except instead of > keeping rings with a large enough hole, keep rings that are closed and > have enough points > > http://www.spatialdbadvisor.com/postgis_tips_tricks/92/filtering-rings-i > n-polygon-postgis > > so something like > > CREATE OR REPLACE FUNCTION remove_invalidrings(geometry) > RETURNS geometry AS > $BODY$ > SELECT ST_BuildArea(ST_Collect(b.final_geom)) as filtered_geom > FROM (SELECT ST_MakePolygon((/* Get outer ring of polygon */ > SELECT ST_ExteriorRing(a.the_geom) as outer_ring /* ie > the outer ring */ > ), ARRAY(/* Get all inner rings > a particular area */ > SELECT ST_ExteriorRing(b.geom) as inner_ring > FROM (SELECT (ST_DumpRings(a.the_geom)).*) b > WHERE b.path[1] > 0 /* ie not the outer ring */ > AND (ST_NPoints(b.geom) > 2 AND ST_IsClosed(b.geom)) > ) ) as final_geom > FROM (SELECT ST_GeometryN(ST_Multi($1),/*ST_Multi converts any > Single Polygons to MultiPolygons */ > > generate_series(1,ST_NumGeometries(ST_Multi($1))) > ) as the_geom > ) a > ) b > $BODY$ > LANGUAGE 'sql' IMMUTABLE; > > Hope that helps, > Regina > > > > -----Original Message----- > From: [email protected] > [mailto:[email protected]] On Behalf Of Ivan > Mincik > Sent: Tuesday, March 24, 2009 5:03 AM > To: PostGIS Users Discussion > Subject: [postgis-users] reparing invalid geometry (polygon defined by > oneor two points) > > Dear PostGIS users, > we have quite huge MULTIPOLYGON database table containing some 3 > percents of invalid geometries. > Most problematic to repair are MULTIPOLYGONs which often contain polygon > defined by one or two points. > > Example is here (second polygon is defined by one point): > MULTIPOLYGON(((-266469.87 -1233431.41,-266423.25 -1233425.84,-266423.23 > -1233426.17,-266422.74 -1233434.54,-266422.75 -1233439.44,-266425.68 > -1233439.9, > -266457.02 -1233444.89,-266463.17 -1233437.07,-266463.27 > -1233436.29,-266469.15 -1233437.08,-266469.84 -1233431.65,-266469.87 > -1233431.41),(-266463.92 -1233431.02), > (-266459.41 -1233430.46,-266458.67 -1233436.67,-266451.29 > -1233435.76,-266451.1 -1233437.26,-266438.9 -1233435.75,-266439.07 > -1233434.35,-266426.37 -1233432.65,-266427.07 -1233426.46, > -266439.85 -1233428.04,-266459.41 -1233430.46))) > > It is not possible to run buffer(0) or many other functions on these > geometries because of "POSTGIS2GEOS conversion failed" (no suprise in > case of such non-sense geometry). > > Is there any possibility how to repair them directly in database? > > > Thanks > Ivan > > _______________________________________________ > postgis-users mailing list > [email protected] > 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. > _______________________________________________ > postgis-users mailing list > [email protected] > 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. > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
