CODEWITHSUNDEEP
sqlpostgresqlpostgis
Rahul
Rahul

Reputation: 3386

How to remove multiple entries of same polygon from a multipolygon?

I'm trying to remove exactly similar polygons from a multipolygon geometry. I tried ST_RemoveRepeatedPoints but it doesn't seem to remove any of the geometry. Can anyone tell me how can I remove them ?

Upvotes: 1

Views: 315

Answers (1)

Tom-db
Tom-db

Reputation: 6868

If difficult to do with a single sql query but easy with a plpgsql function.

The function ST_Dump() expand a multigeometry to single geometries. Than you can iterate over the single geometries and check for uniqueness: 

CREATE or REPLACE FUNCTION clean_multipoly(input_multipoly geometry) 
RETURNS GEOMETRY AS $$
DECLARE
  single_poly geometry;
  polygons_array  geometry[];
  poly_array_element GEOMETRY;
  is_contained BOOLEAN;

BEGIN

   -- initialize the array to a empty array 
  polygons_array = array[]::geometry[];

  -- now iterate over the single polygons
  FOR single_poly IN SELECT (ST_Dump(input_multipoly)).geom LOOP
    is_contained = false;   

    -- Now you need the iterate again over the array you are building 
    -- and check every element if is equal to the actual single polygon.
    -- You cannot use a array operator for checking if a element is already contained in the array, 
    -- because this would eliminate polygons which are different but have the same extent.
    -- Only ST_Equals checks properly for geometries equality
    FOREACH poly_array_element IN ARRAY polygons_array LOOP
      IF ST_equals(single_poly, poly_array_element) THEN
        is_contained = TRUE;
      END IF;
    END LOOP;
    IF is_contained = FALSE THEN
      polygons_array = array_append(polygons_array, single_poly);  
    END IF;
  END LOOP;
  RETURN ST_collect(polygons_array);

END;
$$
LANGUAGE plpgsql;

Use the function so: 

SELECT clean_multipoly(your_geom) FROM your_table;

Upvotes: 1

Related Questions