oracle-databaseoracle-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.