Unpacking USER_SDO_GEOM_METADATA’s DIMINFO structure using SQL

Unpacking user_sdo_geom_metadata’s diminfo object.

Objects in Oracle require special handling when querying.

Firstly, Oracle says that, when querying something that contains an object, and you want to access one of the object’s elements (or fields), use of a table alias in a DML query is required.

So, let’s say we want to discover all possible geometry types being stored in a table/sdo_geometry column pair (we will use: my_table/shape) we would do the following (note the use of the table alias gd3):

 codesys@XE> SELECT DISTINCT gd3.geom.sdo_gtype
   2* FROM GEODPOINT3D gd3
 codesys@XE> /
 GEOM.SDO_GTYPE
 --------------
           3001
 </code>

That’s pretty straight forward. But let’s now apply this to the diminfo column of the user_sdo_geom_metadata view. Naively we could write:

 codesys@XE> SELECT usgm.diminfo.sdo_tolerance
   2 FROM user_sdo_geom_metadata usgm
   3 WHERE table_name = 'GEODPOINT3D';
 SELECT usgm.diminfo.sdo_tolerance
        *
 ERROR at line 1:
 ORA-00904: "USGM"."DIMINFO"."SDO_TOLERANCE": invalid identifier
 </code>

But on execution we get the above error.

What you need to do is to use a TABLE function to unpack the elements of the diminfo structure as follows:

 codesys@XE> SELECT dim.sdo_tolerance
   2    FROM user_sdo_geom_metadata usgm,
   3         TABLE(usgm.diminfo) dim
   4   WHERE table_name = 'GEODPOINT3D';
 SDO_TOLERANCE
 -------------
           500
           500
            .5

And to access all elements in the diminfo structure you can do this:

 codesys@XE> SELECT dim.*
   2    FROM user_sdo_geom_metadata usgm,
   3         TABLE(usgm.diminfo) dim
   4   WHERE table_name = 'GEODPOINT3D';
 SDO_DIMNAME                                                          SDO_LB     SDO_UB SDO_TOLERANCE
 ---------------------------------------------------------------- ---------- ---------- -------------
 Long                                                                 112.03     146.82           500
 Lat                                                                  -43.98     -10.04           500
 Z                                                                      15.6    9995.75            .5

I have create a report for use in Oracle’s SQL Developer that generates a report showing each of the individual elements in USER_SDO_GEOM_METADATA. It can be downloaded using this <txp:file_download_link id=”26″>link.</txp:file_download_link>