ORA-54668: A 2D SRID Cannot Be Used With A 3D Geometry Validating A Geometry (Doc ID 2020288.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Spatial and Graph - Version 11.2.0.3 and later
Information in this document applies to any platform.

Symptoms

On an 11.2.0.3 database created a 3D Custom Coordinate System then inserted a row with a valid geometry but the validation gives error

ORA-54668: a 2D SRID cannot be used with a 3D geometry

Note: the 2 vertical systems 5798 and 5773 were provided by us (Oracle) then customer created the custom 3D using horizontal 32610

horizontal 32610

Testing the 2 vertical systems we provided 5798 and 5773 with the horizontal 32610

Compound customer SRID 579832610
32610, -- Horizontal
5798, -- Vertical

and

Compound customer SRID 577332610
32610, -- Horizontal
5773, -- Vertical

conn / as sysdba
ALTER SESSION SET CURRENT_SCHEMA = mdsys;

insert into sdo_datums (
 DATUM_ID,
 DATUM_NAME,
 DATUM_TYPE,
 ELLIPSOID_ID,
 PRIME_MERIDIAN_ID,
 INFORMATION_SOURCE,
 DATA_SOURCE,
 SHIFT_X,
 SHIFT_Y,
 SHIFT_Z,
 ROTATE_X,
 ROTATE_Y,
 ROTATE_Z,
 SCALE_ADJUST,
 IS_LEGACY,
 LEGACY_CODE)
values (
 5203,
 'EGM84 geoid',
 'VERTICAL',
 NULL,
 NULL,
 'US National Geospatial-Intelligence Agency (NGA); http://earth-info.nga.mil/G
andG/',
 'OGP',
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 'FALSE',
 NULL);

insert into sdo_coord_ref_system (
 SRID,
 COORD_REF_SYS_NAME,
 COORD_REF_SYS_KIND,
 COORD_SYS_ID,
 DATUM_ID,
 GEOG_CRS_DATUM_ID,
 SOURCE_GEOG_SRID,
 PROJECTION_CONV_ID,
 CMPD_HORIZ_SRID,
 CMPD_VERT_SRID,
 INFORMATION_SOURCE,
 DATA_SOURCE,
 IS_LEGACY,
 LEGACY_CODE,
 LEGACY_WKTEXT,
 LEGACY_CS_BOUNDS,
 IS_VALID,
 SUPPORTS_SDO_GEOMETRY)
values (
 5798,
 'EGM84 geoid height',
 'VERTICAL',
 6499,
 5203,
 5203,
 NULL,
 NULL,
 NULL,
 NULL,
 'US National Geospatial-Intelligence Agency (NGA); http://earth-info.nga.mil/GandG/',
 'OGP',
 'FALSE',
 NULL,
 NULL,
 null,
 'TRUE',
 'TRUE');



insert into sdo_datums (
 DATUM_ID,
 DATUM_NAME,
 DATUM_TYPE,
 ELLIPSOID_ID,
 PRIME_MERIDIAN_ID,
 INFORMATION_SOURCE,
 DATA_SOURCE,
 SHIFT_X,
 SHIFT_Y,
 SHIFT_Z,
 ROTATE_X,
 ROTATE_Y,
 ROTATE_Z,
 SCALE_ADJUST,
 IS_LEGACY,
 LEGACY_CODE)
values (
 5171,
 'EGM96 geoid',
 'VERTICAL',
 NULL,
 NULL,
 'NASA http://cddis.gsfc.nasa.gov/926/egm96/egm96.html',
 'OGP',
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 NULL,
 'FALSE',
 NULL);

insert into sdo_coord_ref_system (
 SRID,
 COORD_REF_SYS_NAME,
 COORD_REF_SYS_KIND,
 COORD_SYS_ID,
 DATUM_ID,
 GEOG_CRS_DATUM_ID,
 SOURCE_GEOG_SRID,
 PROJECTION_CONV_ID,
 CMPD_HORIZ_SRID,
 CMPD_VERT_SRID,
 INFORMATION_SOURCE,
 DATA_SOURCE,
 IS_LEGACY,
 LEGACY_CODE,
 LEGACY_WKTEXT,
 LEGACY_CS_BOUNDS,
 IS_VALID,
 SUPPORTS_SDO_GEOMETRY)
values (
 5773,
 'EGM96 geoid height',
 'VERTICAL',
 6499,
 5171,
 5171,
 NULL,
 NULL,
 NULL,
 NULL,
 'NASA http://cddis.gsfc.nasa.gov/926/egm96/egm96.html',
 'OGP',
 'FALSE',
 NULL,
 NULL,
 null,
 'TRUE',
 'TRUE');
commit;

-- Create new Compound 3D CRSs referencing newly added EGM84 and EGM96 Vertical CRSs


insert into MDSYS.SDO_COORD_REF_SYSTEM (
SRID,
COORD_REF_SYS_NAME,
COORD_REF_SYS_KIND,
CMPD_HORIZ_SRID,
CMPD_VERT_SRID,
INFORMATION_SOURCE,
DATA_SOURCE,
IS_LEGACY
)
VALUES (
579832610, -- New SRID
'WGS 84 / UTM zone 10N + EGM84 geoid height',
'COMPOUND',
32610, -- Horizontal
5798, -- Vertical
'US ARMY CECOM', -- Information source
NULL,
'FALSE'
);

commit;



insert into MDSYS.SDO_COORD_REF_SYSTEM (
SRID,
COORD_REF_SYS_NAME,
COORD_REF_SYS_KIND,
CMPD_HORIZ_SRID,
CMPD_VERT_SRID,
INFORMATION_SOURCE,
DATA_SOURCE,
IS_LEGACY
)
VALUES (
577332610, -- New SRID
'WGS 84 / UTM zone 10N + EGM96 geoid height',
'COMPOUND',
32610, -- Horizontal
5773, -- Vertical
'US ARMY CECOM', -- Information source
NULL,
'FALSE'
);
commit;


-- Create table test for testing new compound CRS 579832610

conn scott/tiger

drop table test;
create table test(
 geom sdo_geometry);

delete user_sdo_geom_metadata where table_name='TEST';

insert into user_sdo_geom_metadata values (
 'TEST',
 'GEOM',
 MDSYS.SDO_DIM_ARRAY(
  MDSYS.SDO_DIM_ELEMENT('X', 0, 100, 0.005),
  MDSYS.SDO_DIM_ELEMENT('Y', 0, 100, 0.005),
  MDSYS.SDO_DIM_ELEMENT('Z', 0, 100, 0.005)),
 579832610);


insert into test values(
 sdo_geometry(
  3001,
  579832610,
  sdo_point_type(1, 2, 3),
  null,
  null));


commit;

drop table val_results;
CREATE TABLE val_results(
 sdo_rowid ROWID,
 result varchar2(1000));


CALL SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT(
  'TEST',
  'GEOM',
  'VAL_RESULTS');

/*
  *
ERROR at line 1:
ORA-54668: a 2D SRID cannot be used with a 3D geometry
ORA-06512: at "MDSYS.MD", line 1723
ORA-06512: at "MDSYS.MDERR", line 8
ORA-06512: at "MDSYS.SDO_GEOM", line 2867
ORA-06512: at line 1
*/
-- Create table test for testing new compound CRS 577332610

conn scott/tiger

drop table test;
create table test(
 geom sdo_geometry);

delete user_sdo_geom_metadata where table_name='TEST';

insert into user_sdo_geom_metadata values (
 'TEST',
 'GEOM',
 MDSYS.SDO_DIM_ARRAY(
  MDSYS.SDO_DIM_ELEMENT('X', 0, 100, 0.005),
  MDSYS.SDO_DIM_ELEMENT('Y', 0, 100, 0.005),
  MDSYS.SDO_DIM_ELEMENT('Z', 0, 100, 0.005)),
 577332610);


insert into test values(
 sdo_geometry(
  3001,
  577332610,
  sdo_point_type(1, 2, 3),
  null,
  null));


commit;

drop table val_results;
CREATE TABLE val_results(
 sdo_rowid ROWID,
 result varchar2(1000));


CALL SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT(
  'TEST',
  'GEOM',
  'VAL_RESULTS');

/*
  *
ERROR at line 1:
ORA-54668: a 2D SRID cannot be used with a 3D geometry
ORA-06512: at "MDSYS.MD", line 1723
ORA-06512: at "MDSYS.MDERR", line 8
ORA-06512: at "MDSYS.SDO_GEOM", line 2867
ORA-06512: at line 1
*/

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms