Go to content Go to navigation and search

Home

Current Oracle Spatial Blogs

Search

RSS / Atom

Email me

textpattern

Extract Polygons from Compound Polygon

· Nov 12, 10:03 pm by Simon Greener

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.

post this at del.icio.uspost this at Diggpost this at Technoratipost this at Newsvinepost this at Ma.gnoliapost this at Furlpost this at Blinklistpost this at Spurlpost this at Wistspost this at Simpypost this at Redditpost this at Farkpost this at Blogmarkspost this at Yahoo! my webpost this at Mr. Wongpost this at Windows Livepost this at Google Bookmarkspost this to Twitter
  Textile Help