-- Function: public.geometry_clean(geometry)

-- DROP FUNCTION public.geometry_clean(geometry);


-- Hard coded to create MULTIPOLYGONS
CREATE OR REPLACE FUNCTION public.geometry_clean(geometry)
  RETURNS geometry AS
'DECLARE

  shp ALIAS for $1;

  gshp GEOMETRY;

  i integer;

  j integer;

 

  exshp GEOMETRY;  

  c_shp  GEOMETRY;

  m_shp GEOMETRY;

BEGIN



   c_shp = NULL;


   IF ( numgeometries(shp) > 0 ) THEN

   FOR j IN 1..(numgeometries(shp)) LOOP



     gshp = geometryn(shp,j); 

     IF ( numpoints(exteriorring(gshp)) > 3 ) THEN

       exshp =  replace(replace(astext(exteriorring(gshp)),\'LINESTRING(\',\'MULTIPOLYGON(((\'),\')\',\')))\');



       IF ( c_shp is NULL ) THEN

          c_shp = geometry_sieve(exshp,\'MULTIPOLYGON\');

       ELSE

         IF ( exshp is NOT NULL ) THEN

           c_shp = geometry_filter(symmetricdifference(c_shp,exshp),\'MULTIPOLYGON\');

         END IF;

       END IF; 

     END IF;


     IF ( nrings(gshp) > 1 ) THEN

     FOR i IN 1..(nrings(gshp)-1) LOOP

       IF ( numpoints(interiorringn(gshp,i)) > 3 ) THEN 

         exshp = replace(replace(astext(interiorringn(gshp,i)),\'LINESTRING(\',\'MULTIPOLYGON(((\'),\')\',\')))\');



         IF ( c_shp is NULL ) THEN

           c_shp = geometry_filter(exshp,\'MULTIPOLYGON\');

         ELSE

           IF ( (npoints(exshp) > 3) and (exshp is NOT NULL) ) THEN

             c_shp = geometry_filter(symmetricdifference(c_shp,exshp),\'MULTIPOLYGON\');

           END IF;

         END IF;

       END IF;

 

     END LOOP;
     END IF;

   END LOOP;

   END IF;

   RETURN c_shp;



END;'
  LANGUAGE 'plpgsql' IMMUTABLE STRICT;
