Oracle Spatial-Extracting Individual Rings in a MultiPolygon

Question

In a multipolygon,I need to count how many rings the polygon has,and then individually extract the rings and it's coordinates

Answer 1

First, You need to use a function for getting inner polygon count.

    create or replace
Function GetNumRings( p_geometry  in mdsys.sdo_geometry,
                      p_ring_type in integer default 0 /* 0 = ALL; 1 = OUTER; 2 = INNER */ )
  Return Number
Is
   v_ring_count number := 0;
   v_ring_type  number := p_ring_type;
   v_elements   number;
   v_etype      pls_integer;
Begin
   If ( p_geometry is null ) Then
      return 0;
   End If;
   If ( p_geometry.sdo_elem_info is null ) Then
      return 0;
   End If;
   If ( v_ring_type not in (0,1,2) ) Then
      v_ring_type := 0;
   End If;
   v_elements := ( ( p_geometry.sdo_elem_info.COUNT / 3 ) - 1 );
   <<element_extraction>>
   for v_i IN 0 .. v_elements LOOP
     v_etype := p_geometry.sdo_elem_info(v_i * 3 + 2);
     If  ( v_etype in (1003,1005,2003,2005) and 0 = v_ring_type )
      OR ( v_etype in (1003,1005)           and 1 = v_ring_type )
      OR ( v_etype in (2003,2005)           and 2 = v_ring_type ) Then
         v_ring_count := v_ring_count + 1;
     end If;
   end loop element_extraction;
   Return v_ring_count;
End GetNumRings;

Ref: GetNumRings Function for Oracle-Spatial

Then, with this count you will get inner polygons

 FOR i IN 2..countFromFunction
    LOOP            
        begin
         insert into target_table(geom) 
          select SDO_UTIL.EXTRACT(t.geom,1,i) FROM source_table t;
          exception when others then 
          continue;
          end;
   end loop;

Loop is started from 2, because 1 will get outer ring.

Hope this helps.