
1. Re: Strange Lengths from SDO_LENGTH
_jum Oct 8, 2013 1:41 AM (in response to user13174287)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 Oct 8, 2013 3:09 AM (in response to user13174287)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 toTRUE
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 threedimensional geometries that contain geometries in preRelease 11.1 format to pass the validation check when they would otherwise fail. For example, a threedimensional line is not valid if it contains circular arcs; and settingflag10g
toTRUE
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 Oct 8, 2013 4:21 AM (in response to Luc Van Linden)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 Oct 8, 2013 4:40 AM (in response to _jum)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 Oct 8, 2013 1:05 PM (in response to 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';