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??
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
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).
"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
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
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).
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';