remove_duplicate_vertices in Oracle Spatial converted polygon/multipolygon types to collections, How do I convert these back to multipolygons?

Question

I have run REMOVE_DUPLICATE_VERTICES on a spatial table which contained polygons and multipolygons, now there are 6 geometries tagged as Collection (2004). How can I safely change these back to multipolygons?

Example of one (with shortened coodinate array)

MDSYS.SDO_GEOMETRY(2004,8307,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1,5,1003,1,13,1003,1,21,1003,1,279,2003,1,581,2003,1,961,2003,1,1551,2003,1,2073,2003,1,2215,2003,1,2277,2003,1,2349,2003,1,2379,2003,1,2671,2003,1,2847,2003,1,3033,2003,1,3145,2003,1,3263,2003,1,3271,2003,1,3403,2003,1),MDSYS.SDO_ORDINATE_ARRAY(-89.60549292,-1.30359069399998,...,-89.6571104179999,-0.900517332999925))

Answer 1

One way to do this would be:

declare wgeom mdsys.sdo_geometry; outgeom mdsys.sdo_geometry; 
begin
for i in 1 .. nvl(sdo_util.getNumElem(ingeom)  ,0) 
loop
  wgeom := sdo_util.extract(ingeom, i, 0);  

  if wgeom.get_gtype() in (3,7) then  
  outgeom := sdo_util.append(outgeom, wgeom);          
  end if;

end loop;

It loops through the geometry's elements and for each one that is a polygon appends it to a geometry variable. You can put it in a function which you 'feed' with your geometry (which should be a valid geometry) and it outputs the filtered outgeom.
I use this approach because it is easy to put logic in the loop to filter out e.g. elements of under a certain area e.t.c. I use in production, with millions of geometries.
If you need further assistance, let me know.