<div dir="ltr"><div>I have been having problems with the clean geometry function referenced 
at <br><a href="http://trac.osgeo.org/postgis/wiki/UsersWikiCleanPolygons">http://trac.osgeo.org/postgis/wiki/UsersWikiCleanPolygons</a> returning null geometries. So I have fixed the script and I'm providing it below if anyone else wants to test it and let me know I could update the wiki.<br>
<br></div><div>(Requires Postgres v9 +)<br></div><div><br>-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -<br>-- <br>-- $Id: cleanGeometry.sql 2014-01-16 Paul Pfeiffer<br>--<br>-- cleanGeometry - remove self- and ring-selfintersections from <br>
--                 input Polygon geometries <br>-- <br>-- Copyright 2014 Paul Pfeiffer<br>-- Version 2.0<br>-- contact: nightdrift at gmail dot com<br>-- <br>-- modified from cleanGeometry.sql 2008-04-24 from <a href="http://www.kappasys.ch">http://www.kappasys.ch</a><br>
--<br>-- This is free software; you can redistribute and/or modify it under<br>-- the terms of the GNU General Public Licence. See the COPYING file.<br>-- This software is without any warrenty and you use it at your own risk<br>
--  <br>-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -<br><br><br>CREATE OR REPLACE FUNCTION cleangeometry(geom "public"."geometry")<br>    RETURNS "public"."geometry" AS<br>
$BODY$<br>    DECLARE<br>    inGeom ALIAS for $1;<br>    outGeom geometry;<br>    tmpLinestring geometry;<br>    sqlString text;<br><br>BEGIN<br>    <br>    outGeom := NULL;<br>    <br>    -- Clean Polygons --<br>    IF (ST_GeometryType(inGeom) = 'ST_Polygon' OR ST_GeometryType(inGeom) = 'ST_MultiPolygon') THEN<br>
<br>        -- Check if it needs fixing<br>        IF NOT ST_IsValid(inGeom) THEN<br>        <br>            sqlString := '<br>                -- separate multipolygon into 1 polygon per row<br>                WITH split_multi (geom, poly) AS (<br>
                    SELECT <br>                        (ST_Dump($1)).geom,<br>                        (ST_Dump($1)).path[1] -- polygon number<br>                ), <br>                -- break each polygon into linestrings<br>
                split_line (geom, poly, line) AS (<br>                    SELECT <br>                        ST_Boundary((ST_DumpRings(geom)).geom),<br>                        poly,<br>                        (ST_DumpRings(geom)).path[1] -- line number<br>
                    FROM split_multi<br>                ), <br>                -- get the linestrings that make up the exterior of each polygon<br>                line_exterior (geom, poly) AS (<br>                    SELECT <br>
                        geom,<br>                        poly<br>                    FROM split_line<br>                    WHERE line = 0<br>                ), <br>                -- get an array of all the linestrings that make up the interior of each polygon<br>
                line_interior (geom, poly) AS (<br>                    SELECT <br>                        array_agg(geom ORDER BY line),<br>                        poly<br>                    FROM split_line<br>                    WHERE line > 0<br>
                    GROUP BY poly<br>                ), <br>                -- use MakePolygon to rebuild the polygons<br>                poly_geom (geom, poly) AS (<br>                    SELECT <br>                        CASE WHEN line_interior.geom IS NULL<br>
                            THEN ST_Buffer(ST_MakePolygon(line_exterior.geom), 0)<br>                            ELSE ST_Buffer(ST_MakePolygon(line_exterior.geom, line_interior.geom), 0)<br>                        END,<br>
                        line_exterior.poly<br>                    FROM line_exterior<br>                    LEFT JOIN line_interior USING (poly)<br>                )<br>            ';<br>            <br>            IF (ST_GeometryType(inGeom) = 'ST_Polygon') THEN<br>
                sqlString := sqlString || '<br>                    SELECT geom<br>                    FROM poly_geom<br>                ';<br>            ELSE<br>                sqlString := sqlString || '<br>
                    , -- if its a multipolygon combine the polygons back together<br>                    multi_geom (geom) AS (<br>                        SELECT <br>                            ST_Multi(ST_Collect(geom ORDER BY poly))<br>
                        FROM poly_geom<br>                    )<br>                    SELECT geom<br>                    FROM multi_geom<br>                ';<br>            END IF;<br><br>            EXECUTE sqlString INTO outGeom USING inGeom;<br>
<br>            RETURN outGeom;<br>        ELSE        <br>            RETURN inGeom;<br>        END IF;<br><br>    -- Clean Lines --<br>    ELSIF (ST_GeometryType(inGeom) = 'ST_Linestring') THEN<br>        <br>        outGeom := ST_Union(ST_Multi(inGeom), ST_PointN(inGeom, 1));<br>
        RETURN outGeom;<br>    ELSIF (ST_GeometryType(inGeom) = 'ST_MultiLinestring') THEN <br>        outGeom := ST_Multi(ST_Union(ST_Multi(inGeom), ST_PointN(inGeom, 1)));<br>        RETURN outGeom;<br>    ELSE <br>
        RAISE NOTICE 'The input type % is not supported',ST_GeometryType(inGeom);<br>        RETURN inGeom;<br>    END IF;        <br>END;<br>$BODY$<br>LANGUAGE 'plpgsql' VOLATILE COST 100<br>;<br><br></div>
</div>