Top 5 Recent Articles
ARTICLES CATEGORIES
- Algorithms (13)
- All (407)
- Biography (1)
- Blog (44)
- Business Requirements (1)
- Commentary (1)
- Customers (2)
- Data Models (1)
- Education (2)
- GeoRaptor (5)
- Image Processing (2)
- Import Export (5)
- Licensing (2)
- Linear Referencing (3)
- Manifold GIS (3)
- Mapping (1)
- MySQL Spatial (7)
- Networking and Routing (including Optimization) (3)
- Open Source (16)
- Oracle Spatial and Locator (178)
- PostGIS (33)
- Published Articles (1)
- Recommendations (1)
- Services (1)
- Software Change Log (1)
- Source Code (35)
- Space Curves (9)
- Spatial Database Functions (101)
- Spatial DB comparison (1)
- Spatial XML Processing (10)
- SQL Server Spatial (General) (83)
- SQL Server Spatial (LRS) (38)
- Standards (1)
- Stored Procedure (15)
- Tessellation or Gridding (9)
- Tools (2)
- Training (2)
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>
Documentation
- MySQL Spatial General Function Documentation
- Oracle Spatial Exporter Package Documentation
- Oracle Spatial Object Function Documentation
- Oracle Spatial Object Function Documentation (Multi Page Version)
- PostGIS pl/pgSQL Function Documentation
- SC4O Oracle Java Topology Suite (Stored Procedures) Package Documentation
- SQL Server Spatial General TSQL Function Documentation
- SQL Server Spatial LRS TSQL Function Documentation