Go to content Go to navigation and search

Home

Current Oracle Spatial Blogs

Search

RSS / Atom

Email me

textpattern

Unpacking USER_SDO_GEOM_METADATA's DIMINFO structure using SQL

· Nov 21, 07:55 pm by Simon Greener

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

post this at del.icio.uspost this at Diggpost this at Technoratipost this at Newsvinepost this at Ma.gnoliapost this at Furlpost this at Blinklistpost this at Spurlpost this at Wistspost this at Simpypost this at Redditpost this at Farkpost this at Blogmarkspost this at Yahoo! my webpost this at Mr. Wongpost this at Windows Livepost this at Google Bookmarkspost this to Twitter
  Textile Help