5 Replies Latest reply: Oct 8, 2013 1:05 PM by user13174287 RSS

    Strange Lengths from SDO_LENGTH

    user13174287

      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

          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

            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

              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

                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

                  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';