[postgis-users] reparing invalid geometry (polygon defined byoneor two points)

[Ivan Mincik]

Thanks a lot,
I will try and get back with result.

On Tue, Mar 24, 2009 at 1:24 PM, Obe, Regina <robe.dnd at cityofboston.gov> 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: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] 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: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] 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
> 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.
> _______________________________________________
> 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.
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>