[postgis-users] reparing invalid geometry (polygon defined byoneor two points)
Ivan Mincik
ivan.mincik at gmail.com
Wed Mar 25 01:36:53 PDT 2009
Thanks for Simon's blog, I have found very interesting things there.
I hope I understand what's going on in that function, but I always get
empty geometry result.
As test I tried following:
SELECT AsText(BuildArea(Collect(b.final_geom))) as filtered_geom
FROM (SELECT MakePolygon((/* Get outer ring of polygon */
SELECT ExteriorRing(a.the_geom) as outer_ring /* ie
the outer ring */
), ARRAY(/* Get all inner rings > a particular area */
SELECT ExteriorRing(b.geom) as inner_ring
FROM (SELECT (DumpRings(a.the_geom)).*) b
WHERE b.path[1] > 0 /* ie not the outer ring */
AND (NPoints(b.geom) > 2 AND IsClosed(b.geom))
) ) as final_geom
FROM (SELECT g.geom As the_geom
FROM Dump(geomFromText('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, -266463.00 -1233431.00, -266463.01 -1233431.01,
-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)))')) As g
WHERE NPoints(g.geom) > 2 AND IsClosed(g.geom)
) a
) b
Result is empty geometry. After removing both IsClosed conditions
geometry will appear. Why ? Where can be mistake ?
Ivan
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
>
More information about the postgis-users
mailing list