1

How to query or find the geometry data type a given Oracle Database is using in SQL Developer (i.e. SDO_Geometry, ST_Geometry, etc)?

I thought I could just describe a feature class within the database and get the result, but it just returns the field types. The geometry field just returns 'NUMBER', which isn't helpful.

  • 2
    The type returned by DESCRIBE for a native geometry column should be MDSYS.SDO_GEOMETRY or MDSYS.ST_GEOMETRY. If you're getting NUMBER it probably isn't a native geometry column. Please edit the question to include the version of Oracle and a DESCRIBE output. You should also include SELECT * FROM USER_SDO_GEOM_METADATA output. – Vince Sep 17 '15 at 20:36
  • Note that ArcGIS enterprise feature classes can be stored in MDSYS.SDO_GEOMETRY, SDE.ST_GEOMETRY, SDELOB, or SDEBINARY storage types. The latter two use a Fn table to store the actual geometry with a NUMBER(38) foreign key in the "geometry_column" of the business table. So if ArcGIS sees the geometry column, but SQL Developer doesn't, you haven't used the appropriate DBTUNE keyword to request table creation with geometry storage the native format. – Vince Sep 17 '15 at 23:30
1

You can check the SDO metadata table to get spatial information:

select * from MDSYS.ALL_SDO_GEOM_METADATA where TABLE_NAME = 'tablename' 
  • Strange. I tried this query and it returned an empty result (not an error, just no data attached to the result) – Mike Sep 17 '15 at 20:49
  • 1
    It sounds like you don't have any Oracle spatial columns properly set up. – Mintx Sep 17 '15 at 21:11
  • This table has to be populated manually. It won't give the user any information if the data wasn't inserted. – jpmc26 Oct 13 '16 at 0:30
0

Using pure SQL, we can find the data type of a column using the ALL_TAB_COLUMNS view:

SELECT *
FROM ALL_TAB_COLUMNS 
WHERE UPPER(OWNER) = 'THE_SCHEMA' AND UPPER(TABLE_NAME) = 'THE_TABLE'

One of the result columns is DATA_TYPE. Note that there is a USER_TAB_COLUMNS if you are connecting as the schema that holds the table of interest.

We could also use DESCRIBE:

DESCRIBE THE_SCHEMA.THE_TABLE

But the result of that command cannot be queried any further.

We can also use ALL_TAB_COLUMNS to try to discover what spatial columns we have:

SELECT * FROM ALL_TAB_COLUMNS WHERE DATA_TYPE = 'SDO_GEOMETRY';

ST_GEOMETRY can be easily added to the WHERE clause. Based on Vince's comment, the following filter seems like a good place to start:

SELECT *
FROM ALL_TAB_COLUMNS
WHERE
    DATA_TYPE LIKE '%GEOM%' OR
    DATA_TYPE LIKE '%SDE%'
;

Note that neither Oracle nor ArcGIS will be able to do much with the columns unless the USER_SDO_GEOM_METADATA has an entry for each geometry column and a spatial index is added.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.