oracleoracle-spatial

Having valid SRIDs issue with SDO_GEOM.SDO_LENGTH in Oracle Spatial

When checking geometry object like this

SELECT t.STREET_ID,
t.FC_GEOM.SDO_SRID SRID,
SUBSTR(t.FC_GEOM.SDO_GTYPE, 1, 1) DIM_COUNT,
SUBSTR(t.FC_GEOM.SDO_GTYPE, 4, 1) GTYPE,
SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(t.FC_GEOM, 0.05) VALIDATION_RES
FROM ST_SEGMENT t WHERE t.STREET_ID = 'STEID5005';

I am getting all (74 rows) TRUE validation results, as you can see

enter image description here

But when I try to calculate length of the geometry object like

SELECT t.STREET_ID, SDO_GEOM.SDO_LENGTH(t.FC_GEOM, 0.005, 'KM')
FROM ST_SEGMENT t WHERE t.STREET_ID = 'STEID5005';

I am getting these errors

ORA-13029: Invalid SRID in the SDO_GEOMETRY object
ORA-06512: at "*****.MD", line 1723
ORA-06512: at "*****.MDERR", line 8
ORA-06512: at "*****.SDO_GEOM", line 2217
ORA-06512: at "*****.SDO_GEOM", line 2149
ORA-06512: at line 1
13029. 00000 -  "Invalid SRID in the SDO_GEOMETRY object"
*Cause:    There is an invalid SDO_SRID in the SDO_GEOMETRY object.
           The specified SRID may be outside the valid SRID range.
*Action:   Verify that the geometries have valid SRIDs.

Why am I still getting this error?

Solution

  • I'm surprised the validation in your example returns TRUE, i.e. indicates no error. You should really get the same 13029 error. For example:

    SQL> select sdo_geom.validate_geometry_with_context(sdo_geometry(3001, 82212, sdo_point_type(4675355.17, 5844284.58, 0), null, null),0.05) as result from dual;

RESULT
------
13029

1 row selected.

    The reason for the error is that your data is 3D (SDO_GTYPE is 3002 in the example you showed) but the coordinate system is 2D. See what happens when I compute the length of a 3D line:

    SQL> select sdo_geom.sdo_length (
  2    sdo_geometry(
  3      3002, 82212, null, 
  4    sdo_elem_info_array(1,2,1), 
  5    sdo_ordinate_array(4675355.17,5844284.58,0, 923377.227,3784105.88,0)
  6  ),
  7    0.005, 'KM'
  8 )
  9  from dual;
select sdo_geom.sdo_length (
       *
ERROR at line 1:
ORA-13029: Invalid SRID in the SDO_GEOMETRY object
ORA-06512: at "MDSYS.MD", line 1723
ORA-06512: at "MDSYS.MDERR", line 8
ORA-06512: at "MDSYS.SDO_GEOM", line 2213
ORA-06512: at "MDSYS.SDO_GEOM", line 2145
ORA-06512: at line 1

    If your data is not really 3D (all the Z are or even NULL), then you can do this:

    select sdo_geom.sdo_length (
  sdo_cs.make_2d (
    sdo_geometry (
      3002, 82212, null, 
      sdo_elem_info_array(1,2,1), 
      sdo_ordinate_array(4675355.17,5844284.58,0, 923377.227,3784105.88,0)
    )
  ),
  0.005, 'unit=KM'
) length
from dual;

    Notice that the correct way of specifying the unit is unit=xxx.

    The result is:

    SQL> select sdo_geom.sdo_length (
  2    sdo_cs.make_2d (
  3      sdo_geometry (
  4        3002, 82212, null, 
  5      sdo_elem_info_array(1,2,1), 
  6      sdo_ordinate_array(4675355.17,5844284.58,0, 923377.227,3784105.88,0)
  7    )
  8  ),
  9    0.005, 'unit=KM'
 10 ) length
 11  from dual;

    LENGTH
----------
4280.38255

1 row selected.