Extract Polygons from Compound Polygon

I was asked the other day:

In thinning the data we sometimes end up with collection features (2004 gtype), and I need to pull out the relevant geometry type (usually polygon) from the collection, because of course SDE is not happy with 2004 gtypes.

I have covered this before, but often people email me asking for single function solutions based on a specific release.

This particular customer is running 10gR2 so a custom, standalone, function is a lot easier than the one in my free PL/SQL packages as those packages have to handle 8i, 9i etc which don’t have some of the sdo_util functions at 10gR2.

For 10gR2 and above users, this is what you need as a standalone function for extracting polygons (2003 or 2007) from a compound (2004) object.

 Create Or Replace Function ExtractPolygon( p_geometry IN MDSYS.SDO_GEOMETRY )
   RETURN MDSYS.SDO_GEOMETRY DETERMINISTIC
 IS
    v_element         number;
    v_elements        number;
    v_geometry        mdsys.sdo_geometry;
    v_extract_shape   mdsys.sdo_geometry;
 Begin
    IF ( MOD(p_geometry.sdo_gtype,10) <> 4 ) Then
        RETURN p_geometry;
    END IF;
    v_elements := mdsys.sdo_util.GetNumElem(p_geometry);
    FOR v_element IN 1..v_elements LOOP
       v_extract_shape := mdsys.sdo_util.Extract(p_geometry,v_element,0);   -- Extract element with all sub-elements
       IF ( v_extract_shape.Get_Gtype() = 3 ) Then
         IF ( v_geometry is null ) Then
            v_geometry := v_extract_shape;
         ELSE
            v_geometry := MDSYS.SDO_UTIL.APPEND(v_geometry,v_extract_shape);
         END IF;
       END IF;
    END LOOP;
    RETURN( v_geometry );
 END ExtractPolygon;

Let’s test it with a compound sdo_geometry object.

 select extractpolygon( mdsys.sdo_geometry (2004, null, null, mdsys.sdo_elem_info_array (1,1,1, 3,2,1, 7,1003,1),
     mdsys.sdo_ordinate_array (10,5, 10,10, 20,10, 10,105, 15,105, 20,110, 10,110, 10,105))) as poly
   from dual;

 POLY
 -----------------------------------------------------------------------------------------------------------------------------------
 MDSYS.SDO_GEOMETRY(2003,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(10,105,15,105,20,110,10,110,10,105))

 1 rows selected

I hope this helps someone.