SDO_Geometry vs ST_Geometry in an Oracle DB

Question

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.

Bud · Answer

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.

Bud · Answer

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.

* 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 · Answer

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