Do I need to use ESRI's Create Spatial Type functionality

Question

Does anyone know if I need to use ESRI's Create Spatial Type functionality when creating a field in a non ESRI / non geodatabase Oracle 11g database?

I have an older customer database in Oracle. After enabling the datatype ST_GEOMETRY on the database via Oracle's Locator functionality, I added a column to an existing table and populated each row as a point with relevant x,y & ,srid values.

However, ArcGIS v10.2.1 does not even recognize the column as a valid datatype. It labels it "unknown" in ArcCatalog and returns invalid data type when trying to build a query layer.

Would this indicate I need to use ESRI's Create Spatial Type in order to get ArcGIS to recognize the dataype/column in this non-geodatabase?

If so, can someone explain what it is doing that simply enabling the datatype directly in ORACLE does not?

Any theories as to why ArcGIS will not recognize this ST_GEOMETRY datatype?!

• MaryBeth: Welcome! Please take the tour, if you haven't already: gis.stackexchange.com/tour Could you copy/paste the text of the error you receive in your question?
• Vince: Oracle has a ST_GEOMETRY type as well -- MDSYS.ST_GEOMETRY; it's a wrapper around SDO_GEOMETRY. I don't believe Esri supports MDSYS.ST_GEOMETRY.
• Evil Genius: @Vince The docs say that SDO_GEOMETRY is supported, at least for reading data.
• Vince: Certainly, MDSYS.SDO_GEOMETRY is supported (read-only in a Query Layer, and read/write through an enterprise geodatabase), but MDSYS.ST_GEOMETRY is not supported. The "ST_GEOMETRY" in that doc refers to SDE.ST_GEOMETRY (which is what Create Spatial Type installs).
• User1974: What ended up being the correct answer?

Answer 1

Your issue is a name collision between differing implementations. Esri developed the SDE.ST_GEOMETRY datatype long ago (at ArcSDE 8.0 for Oracle 8i), but Oracle started shipping an MDSYS.ST_GEOMETRY type for ISO compatibility (I believe it was first released with Oracle 11gR1).

The "ST_GEOMETRY" you enabled through Locator is actually MDSYS.ST_GEOMETRY, which is not supported by ArcGIS.

You have two avenues for spatial type support in Oracle with ArcGIS for Desktop 10.2.x:

1. Use MDSYS.SDO_GEOMETRY directly (MDSYS.ST_GEOMETRY is a wrapper around the SDO type)
2. Install SDE.ST_GEOMETRY via Create Spatial Type, and use that instead of the MDSYS wrapper.

• Dan Weiss: Thanks for the quick reply! I'll let you know how it works out...

Answer 2

When adding new geometry columns in Oracle, be sure to update the MDSYS.SDO_GEOM_METADATA table.

insert into user_sdo_geom_metadata (table_name, column_name, diminfo, srid) values (your, values, go, here)