A 3D data with a 2D SRID may result with ORA-13029 #JoelKallmanDay

INTRO – You should not store a 3D geometry with a 2D geodetic SRID

Before I start, I want to explain.

What is JoelKallman Day?

Joel Kallman was a professional whose life was dedicated to databases and one of the co-creators of Oracle Application Express. With a career spanning several decades, he made significant contributions to database development and management. He passed away from COVID-19 on May 25, 2021.

Since 2016, there has been an Oracle Community Day where bloggers share content on the same day to create a sense of community buzz. The name of the event has changed over the years, but in 2021, it was renamed “Joel Kallman Day.” This year, it will be held on Wednesday, October 16th. The details of how it works are explained in Tim Hall’s blog post.

What is SRID?

SRID stands for Spatial Reference Identifier, and it is a unique identifier used to define the coordinate system and spatial reference for geographic data. It helps ensure that spatial data is understood in the same way in relation to the Earth’s surface, allowing for consistent mapping and analysis across different systems.

What is 3D support in Oracle Spatial?

3D support in Oracle Spatial refers to the capability to store, manage, and analyze three-dimensional spatial data. This includes not only the traditional two dimensions (latitude and longitude) but also a third dimension, typically representing elevation or depth. This 3D support was first introduced in version 12gR1.

In a geographic information system application, we encountered the ORA-13029: Invalid SRID in the SDO_GEOMETRY object error for some spatial functions like SDO_GEOM.SDO_AREA, SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT etc. You may also use the query provided below to check for any columns storing spatial data.

SQL> SELECT SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(GEO_COLUMNN,0.05) as test_result FROM TESTER.SOME_TABLE_WITH_3D_DATA ;
		
test_result 
--------------------------------------------------
13029

After a quick search on My Oracle Support, I found the document titled ” ORA-13029 When Validating Geometry or from SDO operators (Doc ID 2713183.1) “.

According to the document, users were allowed to store 3D data using 2D SRIDs until version 19c. In 19c, it became a requirement to use 3D SRIDs for storing 3D data.

To check which release of Spatial you are running, use the SDO_VERSION function.

SQL> SELECT SDO_VERSION FROM DUAL;

SDO_VERSION
--------------------------------------------------------------------------------
19.0.0.0.0

You can use the query provided below to check if any columns are storing 3D data with a geographic 2D SRID.

SQL> SELECT T1.OWNER,T1.TABLE_NAME,T1.COLUMN_NAME 
FROM 
ALL_SDO_GEOM_METADATA T1, TABLE (T1.DIMINFO) DIM, SDO_COORD_REF_SYS T2
WHERE DIM.SDO_DIMNAME='Z'
AND T1.SRID=T2.SRID
AND T2.COORD_REF_SYS_KIND='GEOGRAPHIC2D'
ORDER BY OWNER,TABLE_NAME,COLUMN_NAME;

As a solution, it is recommended to apply patch 31022826, which is included in the Spatial Bundle Patch. After applying the patch, and it is required to set the 54713 event. A 2D geodetic SRID contains no information about the vertical unit of measure. For a projected SRID, you might simply assume the vertical unit is the same as the horizontal unit. But not so for geodetic. This is why the event is required.  You can find the relevant bundle patch number you will apply for your release in the document “What Is The Latest Spatial Patch Bundle for 12c and Higher Databases? (Doc ID 2514624.1)“

-- for setting at session level which can be used with a login trigger solution
SQL> alter session set events '54713 trace name context forever, level 1';

--To disable the event:
SQL> alter session set events '54713 trace name context off';

-- To enable the event at system level:
SQL> ALTER SYSTEM SET EVENT = '54713 trace name context forever, level 1' COMMENT='Allow 2D geodetic SRIDs with 3D geodetic geometries' SCOPE=SPFILE;

--To disable the event that was set at system level:
SQL> alter system set events '54713 trace name context off';

For a quick fix, i used a login trigger which sets the event on session level.

-- grant alter session explicitly
SQL> GRANT ALTER SESSION TO SYSTEM;

SQL>   CREATE OR REPLACE TRIGGER SYSTEM.TRG_AFTER_LOGON_TESTER 
AFTER LOGON ON TESTER.SCHEMA
BEGIN
EXECUTE IMMEDIATE ('alter session set events ''54713 trace name context off'''; );

EXCEPTION
WHEN OTHERS
THEN
RAISE_APPLICATION_ERROR (-20001,'Error on connection. Connect to InsaneDBA');
END TRG_AFTER_LOGON_TESTER ;
/

After coordinating with the development team, we decided to update the SRID information and related metadata for the data, since it is already in 3D format. We will also recreate the associated spatial index. This approach eliminates the need for setting any events.

In our case, although the table data is in 3D format, 4326 SRID WGS 84 Geographic2d was choosen.

What is 3D Equivalent for WGS 84?

There are two possible answers to the question What is 3D equivalent for the WGS 84 coordinate system? (that is, 2D Oracle SRID 8307 or EPSG SRID 4326):

• 4979 (in many or most cases), or
• It depends on what you mean by height (for example, above ground level, above or below sea level, or something else).

There are many different height datums. Height can be relative to:

• The ellipsoid, which requires the use of a coordinate system of type GEOGRAPHIC3d, for which SRID values 4327, 43229, and 4979 are predefined in Oracle Spatial.
• A non-ellipsoidal height datum, which requires the use of a coordinate system of type COMPOUND, for which a custom SRID must usually be defined. The non-ellipsoidal height may be specified in relation to the geoid, to some local or mean sea level (or a network of local sea levels), or to some other definition of height (such as above ground surface).

You can query the mdsys.sdo_coord_ref_sys to list all available SRIDs. Also it is possible to create custom SRID.

Here are the steps to correct SRID information.

-- First, Delete the wrong metadata 
SQL> DELETE FROM USER_SDO_GEOM_METADATA 
WHERE TABLE_NAME='SOME_TABLE_WITH_3D_DATA' 
AND COLUMN_NAME='GEO_COLUMNN';

-- Insert the correct metadata
SQL> INSERT INTO USER_SDO_GEOM_METADATA (
  TABLE_NAME, COLUMN_NAME, DIMINFO, 
  SRID
) 
VALUES 
  (
    'SOME_TABLE_WITH_3D_DATA', 
    'GEO_COLUMNN', 
    MDSYS.SDO_DIM_ARRAY(
      MDSYS.SDO_DIM_ELEMENT('X', -180, 180, 0.05), 
      MDSYS.SDO_DIM_ELEMENT('Y', -90, 90, 0.05), 
      MDSYS.SDO_DIM_ELEMENT ('Z', -1000000, 1000000, 0.05)
    ), 
    4979
  );

-- Update table data srid value
SQL> UPDATE 
  SOME_TABLE_WITH_3D_DATA A 
SET 
  A.GEO_COLUMNN.SDO_SRID = 4979;

SQL> COMMIT;

-- Create spatial index
SQL> CREATE INDEX TESTER.SOME_INDEX_ON_3D_DATA 
ON TESTER.SOME_TABLE_WITH_3D_DATA (GEO_COLUMNN) 
INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2 
PARAMETERS('TABLESPACE=USERS_AI ');

I also recommend ensuring that the SPATIAL_VECTOR_ACCELERATION database system parameter is set to TRUE for optimal performance of spatial operators. For more information, you may refer to “Oracle Spatial Operators: Leveraging SPATIAL_VECTOR_ACCELERATION”

Hope it helps.