SDO_Geometry vs ST_Geometry in an Oracle DB

[Abhi0829]

Would like to know what are pros and cons of using SDO_Geometry vs ST_Geometry in an Oracle DB? Cause using SDO geometry, the dashboard indicators do not work and show error "Cannot Access Data" while the same works fine when the geometry type is ST_Geometry. 

[AJR]

What dashboard are you referring to?  ArcGIS Monitor? An Oracle tool? Something else?

[MarceloMarques]

@AJR 

"using SDO geometry, the dashboard indicators do not work and show error "Cannot Access Data" while the same works fine when the geometry type is ST_Geometry."

Which dashboard? Any specific ArcGIS Enterprise Service that you published, and it is not working!? If that is the case then please open a ticket with Esri Technical Support to investigate your issue further.

You can find more information about Esri ST_GEOMETRY and Oracle Spatial SDO in the ArcGIS Documentation links below.

Oracle data types supported in ArcGIS—ArcGIS Pro | Documentation
ST_Geometry in Oracle—ArcGIS Pro | Documentation
SDO_GEOMETRY and ArcGIS—ArcGIS Pro | Documentation

---------------------------

Geometry data types

ArcGIS creates and can work with two geometry data types in Oracle: Esri ST_Geometry and Oracle SDO_Geometry.

ST_Geometry

The ST_Geometry data type implements the SQL 3 specification of user-defined data types (UDTs), allowing you to create columns capable of storing spatial data such as the location of a landmark, a street, or a parcel of land. It provides International Organization for Standards (ISO) and Open Geospatial Consortium (OGC) compliant structured query language (SQL) access to the geodatabase and database. This storage extends the capabilities of the database by providing storage for objects (points, lines, and polygons) that represent geographic features. It was designed to make efficient use of database resources, to be compatible with database features such as replication and partitioning, and to provide rapid access to spatial data.

For more information, see ST_Geometry in Oracle.

SDO_Geometry

SDO_Geometry is implemented using an Oracle extensible object-relational-type system. The SDO_Geometry type is offered by Oracle using two primary options:

• Oracle Spatial is an optional feature of the Oracle Database Enterprise Edition. In addition to providing the SDO_Geometry type, Oracle Spatial provides a number of additional geospatial capabilities.
• Oracle Locator provides a subset of Oracle Spatial capabilities. It is included as a standard feature of Oracle Database Standard and Enterprise editions. Among other capabilities, it provides the Oracle Spatial geometry type (referred to as SDO_Geometry) and a SQL API to this content.

ArcGIS supports SDO_Geometry as an optional method to store spatial data. Specifically, Oracle Spatial or Locator geometry can be used to store and manage the feature and raster contents of datasets in enterprise geodatabases or Oracle databases.

For information on SDO_Geometry, consult Oracle documentation.

---------------------------

I hope this helps.

[Bud]

It’s my understanding that Oracle Locator doesn’t really exist anymore sine Oracle Spatial is free now.

[AJR]

As @MarceloMarques mentioned, both geometry types are supported within ArcGIS and should function equivalently within ArcGIS.  The pros/cons really come down to if you need to access the data outside of ArcGIS.  Many third party tools can read/write SDO, but generally only the esri tools can read/write ST_Geometry.  So if you have external systems that will be pushing data in to your oracle database and don't want to have to have additional libraries registered on your server and/or modifying client code to read/write st_geometries, then SDO geometry is the way to go.  For almost all other use cases (i.e. ones where you aren't pushing data into or pulling data out of the database using sql tools) you'll probably want to use st_geometry.  The grid indexing scheme (wrapped up as the domain index on your spatial column) is generally faster than the relational indexing scheme from oracle (based on my experience) especially for line and polygon data.

[Bud]

My interest is in advanced SQL querying.

SDE.ST_GEOMETRY:

I can't think of many pros other than:

• The barrier to entry is relatively low. It's fairly straightforward to start writing queries using ST_GEOMETRY functions.
• The documentation is relatively easy to read and has SQL examples. Although the docs rarely have enough detail.
• The ArcGIS platform might have slightly better support for ST_GEOMETRY (and fewer bugs), since it's Esri's proprietary spatial type. But I don't have any evidence to back that up, other than your comment in the original post about trouble with a dashboard of some sort.

SDO_GEOMETRY

• Has much more extensive SQL querying capabilities: packages like linear referencing, functions like GetVertices(), function-based spatial indexes, materialized view support, and much more.
• Performance is much better when it comes to spatial queries. For example, SDO_GEOMETRY functions are 20x faster than SDE.ST_GEOMETRY functions (startpoint XY).
  This specialized SDO_GEOMETRY query is orders of magnitude faster than the ST_GEOMETRY equivalent: Query Optimization: Select polygons that intersect points. There's no comparison; it's as fast as Select By Location in the ArcGIS Pro UI, which is pretty remarkable
• The documentation might be hard to parse, but it does have a fair bit of detail.
• The Oracle Forum is fairly active. https://forums.oracle.com/ords/apexds/domain/dev-community?tags=spatial. It's a clunky platform, but if you use the #spatial tag, then you can often get answers to your questions.
• Alternatively, you can post questions on GIS.StackExchange or Stack Overflow and tag as Oracle Spatial (or whatever's appropriate). You'll likely get an answer. Whereas I can rarely get help with ST_GEOMETRY in the Esri Community or on GIS.StackExchange.
• There is also an Oracle Spatial book: Pro Oracle Spatial for Oracle Database 11g. Although it is outdated. There are no books on ST_GEOMETRY.

My complaints with ST_GEOMETRY are 1) that Esri stopped improving it 10+ years ago so there are significant gaps in SQL functions. For example, there is no GetVertices() function. And 2) it is very slow when it comes to spatial queries.

In comparison, Oracle Spatial is being improved in new releases of Oracle.
https://blogs.oracle.com/database/post/whats-new-in-oracle-spatial-for-oracle-database-23c 

If I had the choice now, I'd choose SDO_GEOMETRY. Unfortunately, it's too late for my organization; I.T. went with ST_GEOMETRY ~7-8 years ago based on Esri's recommendation and since Oracle Spatial wasn't free at that time. Now, we've got too many dependencies on ST_GEOMETRY functions in queries, so it wouldn't make sense to switch. And it wouldn't make sense to have a mixed bag of ST_GEOMETRY and SDO_GEOMETRY FCs, since doing spatial queries amongst the two wouldn't be possible.

What I plan to do once my organization fully switches to Pro is: Create helper FCs using SDO_GEOMETRY. I'd use attribute rules to keep the helper FCs in sync with the main ST_GEOEMTRY FCs. Then I'd have the best of both worlds. I could use the main ST_GEOMETRY FC if I wanted to. But if I need advanced functionality like linear referencing, then I can point to the related SDO_GEOMETRY FC and do linear referencing analysis on it.

I'm not suggesting that's an ideal solution for most people. I'm just trying to show how much I crave full spatial SQL functionality, to the point where I'm willing to implement a workaround like that.

Summary:

I think you would be happier in the long run with SDO_GEOMETRY for the advanced spatial SQL querying functionality and spatial query performance. With that said, if you're absolutely sure that no one in your organization will ever do advanced spatial SQL querying, then maybe it doesn't matter what you choose. Although I still maintain that there isn't much benefit to ST_GEOMETRY (other than it's easier). Now that Oracle Spatial is free, I think you'll find that most experienced spatial database experts are moving to SDO_GEOMETRY if they have the choice.

Related: 

• What spatial type does Esri recommend for Oracle EGDBs?
• Remember to enable SPATIAL_VECTOR_ACCELERATION in existing Oracle databases. The parameter is enabled by default in new databases. But it wasn't always that way, so it is likely disabled in most existing Oracle databases. 

[Bud]

Regarding your dashboard error: Are you sure your SDO_GEOMETRY features are valid? Because the SDO_GEOMETRY spatial type allows invalid shapes to be created (you can check them after the fact with SQL functions). Whereas ST_GEOMETRY prevents invalid shapes from being created in the first place. 

What happens if you export your ST_GEOMETRY FC as an SDO_GEOMETRY FC using ArcGIS Pro (look up configuration keywords)? Does your dashboard work on that exported SDO_GEOMETRY FC?

As others have said, we need more information.