SpatialDB Advisor

Free version of sdo_length

For users of 10gR2 Locator not licensed for Spatial, the use of sdo_geom.sdo_length is forbidden. So is use of sdo_geom.sdo_length. However, use of sdo_geom.sdo_distance is allowed for Locator users.

The sdo_geom.sdo_distance function can be used to construct an sdo_length alternative function that is fully licensed.

Here is one such attempt:

create or replace
Function sdo_length(p_geometry  in mdsys.sdo_geometry,
                    p_tolerance in number default 0.005 )
  Return Number
Is
  v_length       number;
  v_i            pls_integer;
  v_num_rings    pls_integer;
  v_num_elements pls_integer;
  v_element_no   pls_integer;
  v_element      mdsys.sdo_geometry;
  v_ring         mdsys.sdo_geometry;
  v_geometry     mdsys.sdo_geometry := p_geometry;
 
  Function ComputeLength (p_geometry  in mdsys.sdo_geometry,
                          p_tolerance in number default 0.005 )
    Return Number
  Is
    v_length   number := 0.0;
    v_vertex   mdsys.vertex_type;
    v_vertices mdsys.vertex_set_type;
  Begin 
    v_vertices := mdsys.sdo_util.getVertices(p_geometry);
    if ( v_vertices is null ) Then
       v_length := 0.0;
    Else
       v_vertex := v_vertices(1);
       for v_i in 2..v_vertices.COUNT loop
           v_length := v_length + 
                       mdsys.sdo_geom.sdo_distance(
                              mdsys.sdo_geometry(2001,p_geometry.sdo_srid,mdsys.sdo_point_type(       v_vertex.x,       v_vertex.y,null),null,null),
                              mdsys.sdo_geometry(2001,p_geometry.sdo_srid,mdsys.sdo_point_type(v_vertices(v_i).x,v_vertices(v_i).y,null),null,null),
                              p_tolerance);
           v_vertex := v_vertices(v_i);
       end loop;
    End If;
    return v_length;
  End ComputeLength;
 
  Function hasRectangles( p_elem_info in mdsys.sdo_elem_info_array  )
    Return Pls_Integer
  Is
     v_rectangle_count number := 0;
     v_etype           pls_integer;
     v_interpretation  pls_integer;
     v_elements        pls_integer;
  Begin
     If ( p_elem_info is null ) Then
        return 0;
     End If;
     v_elements := ( ( p_elem_info.COUNT / 3 ) - 1 );
     <<element_extraction>>
     for v_i IN 0 .. v_elements LOOP
       v_etype := p_elem_info(v_i * 3 + 2);
       v_interpretation := p_elem_info(v_i * 3 + 3);
       If  ( v_etype in (1003,2003) AND v_interpretation = 3  ) Then
           v_rectangle_count := v_rectangle_count + 1;
       end If;
     end loop element_extraction;
     Return v_rectangle_Count;
  End hasRectangles;
 
  Function hasCircularArcs(p_elem_info in mdsys.sdo_elem_info_array)
     return boolean 
   Is
     v_elements  number;
   Begin
     v_elements := ( ( p_elem_info.COUNT / 3 ) - 1 );
     <<element_extraction>>
     for v_i IN 0 .. v_elements LOOP
        if ( ( /* etype */         p_elem_info(v_i * 3 + 2) = 2 AND
               /* interpretation*/ p_elem_info(v_i * 3 + 3) = 2 )
             OR
             ( /* etype */         p_elem_info(v_i * 3 + 2) in (1003,2003) AND
               /* interpretation*/ p_elem_info(v_i * 3 + 3) IN (2,4) ) ) then
               return true;
        end If;
     end loop element_extraction;
     return false;
  End hasCircularArcs;
 
  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;
 
  PROCEDURE ADD_Coordinate( p_ordinates  in out nocopy mdsys.sdo_ordinate_array,
                            p_dim        in number,
                            p_x_coord    in number,
                            p_y_coord    in number,
                            p_z_coord    in number,
                            p_m_coord    in number,
                            p_measured   in boolean := false,
                            p_duplicates in boolean := false)
    IS
      Function Duplicate
        Return Boolean
      Is
      Begin
        Return case when p_ordinates is null or p_ordinates.count = 0
                    then False
                    Else case p_dim
                              when 2
                              then ( p_ordinates(p_ordinates.COUNT)   = p_y_coord
                                     AND
                                     p_ordinates(p_ordinates.COUNT-1) = p_x_coord )
                              when 3
                              then ( p_ordinates(p_ordinates.COUNT)   =  case when p_measured then p_m_coord else p_z_coord end
                                     AND
                                     p_ordinates(p_ordinates.COUNT-1) = p_y_coord
                                     AND
                                     p_ordinates(p_ordinates.COUNT-2) = p_x_coord )
                              when 4
                              then ( p_ordinates(p_ordinates.COUNT)   = p_m_coord
                                     AND
                                     p_ordinates(p_ordinates.COUNT-1) = p_z_coord
                                     AND
                                     p_ordinates(p_ordinates.COUNT-2) = p_y_coord
                                     AND
                                     p_ordinates(p_ordinates.COUNT-3) = p_x_coord )
                          end
                  End;
      End Duplicate;
 
  Begin
    If ( p_ordinates is null ) Then
       p_ordinates := new mdsys.sdo_ordinate_array(null);
       p_ordinates.DELETE;
    End If;
    If ( p_duplicates or Not Duplicate() ) Then
      IF ( p_dim >= 2 ) Then
        p_ordinates.extend(2);
        p_ordinates(p_ordinates.count-1) := p_x_coord;
        p_ordinates(p_ordinates.count  ) := p_y_coord;
      END IF;
      IF ( p_dim >= 3 ) Then
        p_ordinates.extend(1);
        p_ordinates(p_ordinates.count)   := case when p_dim = 3 And p_measured
                                                 then p_m_coord
                                                 else p_z_coord
                                            end;
      END IF;
      IF ( p_dim = 4 ) Then
        p_ordinates.extend(1);
        p_ordinates(p_ordinates.count)   := p_m_coord;
      END IF;
    End If;
  END ADD_Coordinate;
 
  Function Rectangle2Polygon(p_geometry in mdsys.sdo_geometry)
    return mdsys.sdo_geometry 
  As
    v_dims      pls_integer;
    v_ordinates mdsys.sdo_ordinate_array := new mdsys.sdo_ordinate_array(null);
    v_vertices  mdsys.vertex_set_type;
    v_etype     pls_integer;
    v_start_coord mdsys.vertex_type;
    v_end_coord   mdsys.vertex_type;
  Begin
      v_ordinates.DELETE;
      v_dims        := p_geometry.get_dims();
      v_etype       := p_geometry.sdo_elem_info(2);
      v_vertices    := sdo_util.getVertices(p_geometry);
      v_start_coord := v_vertices(1);
      v_end_coord   := v_vertices(2);
      -- First coordinate
      ADD_Coordinate( v_ordinates, v_dims, v_start_coord.x, v_start_coord.y, v_start_coord.z, v_start_coord.w );
      -- Second coordinate
      If ( v_etype = 1003 ) Then
        ADD_Coordinate(v_ordinates,v_dims,v_end_coord.x,v_start_coord.y,(v_start_coord.z + v_end_coord.z) /2, v_start_coord.w);
      Else
        ADD_Coordinate(v_ordinates,v_dims,v_start_coord.x,v_end_coord.y,(v_start_coord.z + v_end_coord.z) /2,
            (v_end_coord.w - v_start_coord.w) * ((v_end_coord.x - v_start_coord.x) /
           ((v_end_coord.x - v_start_coord.x) + (v_end_coord.y - v_start_coord.y)) ));
      End If;
      -- 3rd or middle coordinate
      ADD_Coordinate(v_ordinates,v_dims,v_end_coord.x,v_end_coord.y,v_end_coord.z,v_end_coord.w);
      -- 4th coordinate
      If ( v_etype = 1003 ) Then
        ADD_Coordinate(v_ordinates,v_dims,v_start_coord.x,v_end_coord.y,(v_start_coord.z + v_end_coord.z) /2,v_start_coord.w);
      Else
        Add_Coordinate(v_ordinates,v_dims,v_end_coord.x,v_start_coord.y,(v_start_coord.z + v_end_coord.z) /2,
            (v_end_coord.w - v_start_coord.w) * ((v_end_coord.x - v_start_coord.x) /
           ((v_end_coord.x - v_start_coord.x) + (v_end_coord.y - v_start_coord.y)) ));
      End If;
      -- Last coordinate
      ADD_Coordinate(v_ordinates,v_dims,v_start_coord.x,v_start_coord.y,v_start_coord.z,v_start_coord.w);
      return mdsys.sdo_geometry(p_geometry.sdo_gtype,p_geometry.sdo_srid,null,mdsys.sdo_elem_info_array(1,v_etype,1),v_ordinates);
  End Rectangle2Polygon;
 
Begin
  -- If the input geometry is null, just return null
  IF ( p_geometry IS NULL ) THEN
    RETURN NULL;
  END IF;
 
  -- Only process linestrings and polygons
  --
  If ( p_geometry.get_gtype() not in (2,6,3,7) ) Then
    RETURN NULL;
  End If;
 
  If ( hasCircularArcs(p_geometry.sdo_elem_info) ) then
      return null;
  End If;
 
  v_num_elements := mdsys.sdo_util.GetNumElem(p_geometry);
  v_length := 0.0;
  <<for_all_elements>>
  FOR v_element_no IN 1..v_num_elements LOOP
     v_element := mdsys.sdo_util.Extract(p_geometry,v_element_no);   -- Extract element with all sub-elements
     If ( v_element.get_gtype() = 2 ) Then
        v_length := v_length + ComputeLength(v_element,p_tolerance);
     Else
        v_num_rings := GetNumRings(v_element); 
        <<for_all_rings>>
        FOR v_i in 1..v_num_rings Loop
             v_ring := MDSYS.SDO_UTIL.EXTRACT(p_geometry,v_element_no,v_i);  -- Extract ring from element .. must do it this way, can't correctly extract from v_element.
            If (hasRectangles(v_ring.sdo_elem_info)>0 ) Then
               v_length := v_length + ComputeLength(Rectangle2Polygon(v_ring),p_tolerance);
            else
               v_length := v_length + ComputeLength(v_ring,p_tolerance);
            End If;
        End Loop for_all_rings;
     End If;
  END LOOP for_all_elements;
  return v_length;
  exception 
     when others then
        return null;
End sdo_length;

A quick and simple test.

select a.stype,
       sdo_length(a.geometry,0.005) as my_length,
       mdsys.sdo_geom.sdo_length(a.geometry,0.005) as ora_length
 from (select 'line' as stype, MDSYS.SDO_GEOMETRY(2006, NULL, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1,9,2,1), MDSYS.SDO_ORDINATE_ARRAY(50.0, 55.0, 50.0, 60.0, 55.0, 58.0, 50.0, 55.0, 56.0, 58.0, 60.0, 55.0, 60.0, 60.0, 56.0, 58.0)) as geometry from dual
       union all 
       select 'poly 2 rectangles' as stype, MDSYS.SDO_GEOMETRY(2003, NULL, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3,5,2003,3), MDSYS.SDO_ORDINATE_ARRAY(10.0, 135.0, 20.0, 140.0, 30.0, 100.0, 70.0, 100.0)) as geometry from dual
       union all
       select 'poly 1 rectangle' as stype, MDSYS.SDO_GEOMETRY(2003, NULL, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3), MDSYS.SDO_ORDINATE_ARRAY(10.0, 135.0, 20.0, 140.0)) as geometry from dual) a
 where a.geometry is not null 
   and a.geometry.sdo_gtype is not null
order by 1;
 
STYPE             MY_LENGTH         ORA_LENGTH
----------------- ----------------- ----------------
line              30.68825265697938 30.6882526569794
poly 1 rectangle  30                30
poly 2 rectangles 110               110

For those not wanting to create or use such a thing, the best thing to do is upgrade to 11g where sdo_length and sdo_area are now licensed (Oracle does not “do” back licensing).

Hope this helps those still on 10g.