(edited)
Oracle 18c 10.7.1 EGDB:
I thought I'd share a test I did recently — for anyone who might be trying to decide between SDE.ST_GEOMETRY and SDO_GEOMETRY.
Select the startpoint X & Y from 14,000 single-part lines:
SDE.ST_GEOMETRY:
select
sde.st_x(sde.st_startpoint(shape)) as startpoint_x,
sde.st_y(sde.st_startpoint(shape)) as startpoint_y
from
sidewalks
STARTPOINT_X STARTPOINT_Y
------------ ------------
668287.112 4869366.88
...
Time: 20 seconds
SDO_GEOMETRY
select
sdo_util.get_coordinate(shape,1).sdo_point.x as startpoint_x,
sdo_util.get_coordinate(shape,1).sdo_point.y as startpoint_y
from
sidewalks
STARTPOINT_X STARTPOINT_Y
------------ ------------
668287.112 4869366.88
...
Time: 0.9 seconds
Result: Getting the startpoint XY from SDO_GEOMETRY is 20x faster than SDE.ST_GEOMETRY in this case.
That's consistent with the testing I've done over the last couple of years. I've found ST_GEOMETRY functions to be slower than SDO_GEOMETRY in general. Of course, there are benefits to ST_GEOMETRY too, such as it being easier to learn than SDO_GEOMETRY (in my experience).
I just thought I'd share those results in case they're useful to anyone.
That's not exactly a common use pattern, and therefore probably shouldn't be used as final arbiter for storage selection.
I've had cases where SDO_GEOMETRY was 15-20% faster, and cases where ST_GEOMETRY was 40-120% faster, though I've mostly been using PostgreSQL in the past decade, with PostGIS geometry, exclusively (which has been 1-2 orders of magnitude faster returning millions of rows against some very disadvantaged Oracle configurations).
Best practice is to benchmark apples-to-apples for an entire solution, then evaluate from there, not just rely on a single datapoint.
- V
(I'm not an Oracle user.)
I got curious to look at the docs on these to see if the queries are equivalent...
Apparently the get_coordinates() is undocumented?
Is it possible to lookup SDE.ST_GEOMETRY by index?
It seems hard to make any real claims about performance without testing more here. The time difference just looks too large for these to be equivalent queries.
Yes, get_coordinates() is undocumented. I've talked to folks on the Oracle Spatial team about that. They plan to document it in the next release.
If for some reason we didn't want to use get_coordinates(), maybe since it's undocumented, then there are other ways to get the startpoint X&Y from SDO_GEOMETRY.
The performance of the alternative techniques is roughly the same as get_coordinates(), although slightly more complicated:
select v.x, v.y from sidewalks cross join sdo_util.getvertices(shape) v where v.id = 1
Or
with function sdo_startpoint_x(shape sdo_geometry) return number is begin return shape.sdo_ordinates(1); end; function sdo_startpoint_y(shape sdo_geometry) return number is begin return shape.sdo_ordinates(2); end; select sdo_startpoint_x(shape) as startpoint_x, sdo_startpoint_y(shape) as startpoint_y from sidewalks
Or
select
json_value((shape).Get_GeoJson(),'$.coordinates[0][0]' returning number) as x,
json_value((shape).Get_GeoJson(),'$.coordinates[0][1]' returning number) as y
from
sidewalks
There is also:
sdo_lrs.geom_segment_start_pt
But that just returns a LRS geometry. We'd still need to get the XY from the SDO_ORDINATES_ARRAY, which can be done via cross join sdo_util.getvertices(shape). But that's more clunky than I'd like.
Related: Idea: Add a SDO_ORDINATES member function (for extracting collection elements by index)
The nice thing about ST_GEOMETRY is that it's easy to use, well documented, and there aren't several weird ways to do a thing...there's usually just one way to do it. But with SDO_GEOMETRY, as I demonstrated above, it's messier.
Is it possible to lookup SDE.ST_GEOMETRY by index?
I don't think so. That's part of the problem with ST_GEOMETRY. The ordinates are hidden in a blob. Whereas SDO_GEOMETRY ordinates are in plain sight in an array in an object attribute (easily accessible by index number).
Note: With ST_GEOMETRY, we can get a specific point/vertex via the point index number: SDE.ST_PointN. But that's just as slow as SDE.ST_StartPoint. And I assume you weren't really referring to that.
It seems hard to make any real claims about performance without testing more here. The time difference just looks too large for these to be equivalent queries.
Yes, the "startpoint function test" was just one test for a specific use case. I haven't done any official benchmarking with dozens of documented tests or anything like that.
But the test I did was legitimate & correct. And it's a good example of how slow ST_GEOMETRY functions can be when used on a medium-sized dataset. I stand by my statement that ST_GEOMETRY functions are generally slower. I've heard the same from others, including Esri staff.
Thank you for this great write up! I wish I could give more than 1 up vote - this type of post makes the community that much more valuable.
I stand by my statement that ST_GEOMETRY functions are generally slow compared to SDO_GEOMETRY functions. I've done lots of testing over the past couple of years. And the results are always the same.
But I should clarify that my testing has been with constructor, accessor, and geometry functions. Not relational functions. I've tested dozens of constructor, accessor, and geometry functions, and I consistently find that they're slower than I need them to be.
And to be fair, ST_GEOMETRY has been perfectly fast when simply selecting the geometry column from a FC in a map. And the spatial indexing seems to work fine too. But that's not what I was referring to in this post...it's the functions that are slower than SDO_GEOMETRY.
I'd be very interested in seeing reproducible examples of ST_GEOMETRY constructor, accessor, or geometry functions being faster than SDO_GEOMETRY. I've not experienced that yet.
Here's what I did to make sure the data was apples-to-apples:
To me, that seems like a good way to do objective testing for functions.
Some additional thoughts on testing:
As mentioned, I'd be interested to see cases where ST_GEOMETRY functions (constructor, accessor, or geometry) are faster than SDO_GEOMETRY functions. They could be out there...and I just haven't seen them yet.
For what it's worth, SDO_Geometry’s SPATIAL_VECTOR_ACCELERATION is disabled in my database (18c), since it's only enabled by default in 21c+.
Oracle Spatial Vector Acceleration: Flick the Switch
I tried checking to see if it's enabled (from a non-DBA connection) in SQL Developer, but got errors:
Check if SPATIAL_VECTOR_ACCELERATION is enabled (as a non-dba)
I don't know much about SPATIAL_VECTOR_ACCELERATION. But it sounds like it will make SDO_Geometry even faster in some cases.
I did a related test recently (Oracle):
Select road right-of-way polygons (~3,000) that have at least one intersecting point (road midpoints; ~3,000). Only select one row per polygon; I want a unique list of polygons that intersect at least one point.
1. ArcGIS Pro Select By Location tool (processes in RAM): 1 second
2. SDO_GEOMETRY database query: 10 seconds
SELECT poly_objectid, pnt_objectid FROM ( SELECT poly.objectid as poly_objectid, pnt.objectid as pnt_objectid, row_number() over(partition by poly.objectid order by null) rn FROM polygons poly CROSS JOIN points pnt WHERE sdo_anyinteract(poly.shape, pnt.shape) = 'TRUE' ) WHERE rn = 1
The above query would likely be faster if SPATIAL_VECTOR_ACCELERATION parameter was enabled in the database.
A related post: Select polygons that intersect points and a parallel post here.
3. SDE.ST_GEOMTERY database query: 50 seconds
SELECT poly_objectid, pnt_objectid FROM ( SELECT poly.objectid as poly_objectid, pnt.objectid as pnt_objectid, row_number() over(partition by poly.objectid order by null) rn FROM polygons poly CROSS JOIN points pnt WHERE sde.st_intersects (poly.shape, pnt.shape) = 1 ) WHERE rn = 1
It surprises me that my mediocre office computer with ArcGIS Pro —> Select By Location outperforms the enterprise Oracle database by such a wide margin. Why are databases so bad at spatial queries?
Related:
Using different data, but still relevant:
4. SDO_GEOMETRY database query (different query from #2)
I got an answer from @kpatenge on Stack Overflow that blows my previous SDO_GEOMETRY query out of the water.
It run instantly: 0.3 seconds.
select objectid from ( select /*+ ordered use_nl (a,b) use_nl (a,c) */ c.objectid as objectid, row_number() over(partition by c.objectid order by null) rn from table(sdo_join('PNT','SHAPE','PLY','SHAPE')) a, pnt b, ply c where a.rowid1 = b.rowid and a.rowid2 = c.rowid and sdo_geom.relate (b.shape, 'ANYINTERACT', c.shape, 1.0) = 'TRUE' ) where rn = 1;
Again, using different data, but still relevant:
5. Mobile geodatabase ST_GEOMETRY database query: 45 seconds
The mobile gdb was stored on my computer's C drive.
SELECT objectid FROM (SELECT ply.objectid, row_number() over(partition by ply.objectid order by null) rn FROM ply CROSS JOIN pnt WHERE st_intersects(ply.shape, pnt.shape) = 1 ) WHERE rn = 1
Load ST_GEOMETRY .dll extension into SQLite/mobile geodatabase — Error: "not authorized"