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
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?
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.