3

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?!

  • 1
    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? – MaryBeth Dec 17 '15 at 19:44
  • 1
    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. – Vince Dec 17 '15 at 19:47
  • @Vince The docs say that SDO_GEOMETRY is supported, at least for reading data. – Evil Genius Dec 17 '15 at 19:57
  • 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). – Vince Dec 17 '15 at 20:14
  • What ended up being the correct answer? – Wilson Mar 24 '17 at 20:34
3

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.
  • Thanks for the quick reply! I'll let you know how it works out... – Dan Weiss Dec 17 '15 at 22:44
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)

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.