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.