This discussion is archived
5 Replies Latest reply: Oct 8, 2013 11:05 AM by user13174287 RSS

Strange Lengths from SDO_LENGTH

user13174287 Newbie
Currently Being Moderated

The SDO_LENGTH command appears to be giving us the length of only the first line segment in a mult-segmented polylines....

 

For example..  for this polyline....

 

MDSYS.SDO_GEOMETRY(3002,82212,null,

MDSYS.SDO_ELEM_INFO_ARRAY(1,4,2,1,2,1,7,2,2),

MDSYS.SDO_ORDINATE_ARRAY(

489308.53608219,5465823.43147879,0,

489254.621813806,5465739.29869485,0,

489223.453489433,5465690.81111309,0,

489217.608149169,5465676.83952032,0,

489213.696031073,5465662.20843221,0,

489213.337168734,5465657.12388026,0,

489213.259485918,5465652.02727197,0))

 

Running

UPDATE TEMP_1 SET LEN = SDO_GEOM.SDO_LENGTH (GEOMETRY,0.005,'unit=meter');

 

We get 99.925 meters, and not the 198.075 that we were expecting.  Are we doing something wrong??

  • 1. Re: Strange Lengths from SDO_LENGTH
    _jum Journeyer
    Currently Being Moderated

    You have a valid geometry and should get a length = 198.075 (if SPATIAL extension is installed) as expected:

    WITH gdata AS
    (SELECT MDSYS.SDO_GEOMETRY(3002,82212, null,
        MDSYS.SDO_ELEM_INFO_ARRAY(1,4,2,1,2,1,7,2,2),
          MDSYS.SDO_ORDINATE_ARRAY(
            489308.53608219 ,5465823.43147879,0,
            489254.621813806,5465739.29869485,0,
            489223.453489433,5465690.81111309,0,
            489217.608149169,5465676.83952032,0,
            489213.696031073,5465662.20843221,0,
            489213.337168734,5465657.12388026,0,
            489213.259485918,5465652.02727197,0)) geom
      FROM dual)
    SELECT SDO_GEOM.VALIDATE_GEOMETRY(geom, 0.005) val,
            sdo_geom.sdo_length(geom,0.005,'unit=meter') len
      FROM gdata;
    VAL       LEN
    ------------------------------------------
    TRUE    198,075416280215
    
    
  • 2. Re: Strange Lengths from SDO_LENGTH
    Luc Van Linden Pro
    Currently Being Moderated

    Hi

     

    This might depends on your version.

     

    The validation since r 11 has changed for 3D geometries. In your case this 3D line contains circular arcs which is invalid since r11. As a consequence this will (propably) returns invalid results (in this case for sdo_length).

     

    SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT SDO_GEOM Package (Geometry)

     

    "Setting the flag10g parameter value to TRUE causes the validation logic for Oracle Spatial Release 10.2 to be used, irrespective of the dimensionality of the geometry. This can be useful for allowing three-dimensional geometries that contain geometries in pre-Release 11.1 format to pass the validation check when they would otherwise fail. For example, a three-dimensional line is not valid if it contains circular arcs; and setting flag10g to TRUE will allow such geometries to avoid being considered invalid solely because of the circular arcs. (You should later make these geometries valid according to the criteria for the current release, such as by densifying the circular arcs.)"

     

    This means in a 11g release your geometry is invalid, If you make a 2D line of this you will get your 198,0..... length

     

    Luc

  • 3. Re: Strange Lengths from SDO_LENGTH
    _jum Journeyer
    Currently Being Moderated

    The flag10g is very interesting, thaks for sharing, it gives  ORA 54668 (with ORACLE 11.2.0.3.0) "a 2D SRID cannot be used with a 3D geometry".
    After some "googling" I found a way to define a valid geometry ?!:

    WITH gdata AS
    (SELECT MDSYS.SDO_GEOMETRY(3302,82212, null,
        MDSYS.SDO_ELEM_INFO_ARRAY(1,4,2,1,2,1,7,2,2),
          MDSYS.SDO_ORDINATE_ARRAY(
            489308.53608219 ,5465823.43147879,0,
            489254.621813806,5465739.29869485,0,
            489223.453489433,5465690.81111309,0,
            489217.608149169,5465676.83952032,0,
            489213.696031073,5465662.20843221,0,
            489213.337168734,5465657.12388026,0,
            489213.259485918,5465652.02727197,0)) geom
      FROM dual)
    SELECT SDO_GEOM.VALIDATE_GEOMETRY(geom, 0.005) val,
            SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(geom,0.005,'TRUE') valxt,
            SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(geom,0.005,'FALSE') valxf,
            sdo_geom.sdo_length(geom,0.005,'unit=meter') len
      FROM gdata;
    val          valxt     valxf        len
    -----------------------------------------------------------------
    TRUE    TRUE    TRUE    198,075416280215
    
    
    
  • 4. Re: Strange Lengths from SDO_LENGTH
    Luc Van Linden Pro
    Currently Being Moderated

    Note that you turned it into a LRS geometry, in your case a 2D LRS linestring with the 3rd "dimension" being the LRS measure (M), not the height (Z).

  • 5. Re: Strange Lengths from SDO_LENGTH
    user13174287 Newbie
    Currently Being Moderated

    Thanks for assistance, all provided great info.  We are indeed running 11g.  Another solution was passed to us from a consultant that appears to work well given that our 3d lines are really only 2d with zero elevation....  below gives us the 198.075 meter length for the above geometry.  I have not yest tested it on the entire dataset.

     

    select SDO_GEOM.SDO_LENGTH(SDO_CS.MAKE_2D(Q.geometry),0.1) from WATER_MAINS Q where GIS_ID = '2900';

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points