nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

SDO_GEOMETRY functions are 20x faster than SDE.ST_GEOMETRY functions (startpoint XY)

Bud

(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.

Find more posts tagged with

Accepted answers

All comments

Bud

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"

Bud

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;

Bud

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?

Anonymous User

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.

Bud

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.

Bud

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

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

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.

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.

Bud

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:

Created two new line FCs via Catalog with no extra columns, just the OBJECTID and SHAPE.
1. One FC with the ST_GEOMETRY datatype.
2. And one with the SDO_GEOMETRY datatype.
Loaded features from an existing FC into the new FCs via the Catalog load tool.
Tested equivalent functions for each datatype in queries in SQL Developer.
1. Hit CTRL+END to select all rows in the query, not just the first 50 rows that are selected by default.

To me, that seems like a good way to do objective testing for functions.

Some additional thoughts on testing:

Ideally, the test would be done with auto-generated fake data that can be publicly shared via db<>fiddle or Oracle Live SQL (or whatever works). Then testers could use that public/fake data and load it into our test FCs in our local environments.
- For example, here's a query that can be used to generate a grid of SDO_GEOMETRY line features:
  https://community.esri.com/t5/arcgis-enterprise-questions/generate-grid-line-features-using-sql/m-p/1182869/highlight/true#M33343
  The grid can be as big as we want, generating more features as needed. That query can be used to create a Query Layer or spatial view in ArcGIS Pro. Then that query/view could be loaded into our test FCs via Catalog...creating features to do the test on.
- The same kind of thing can be done for polygons using this query:
  https://community.esri.com/t5/arcgis-pro-questions/convert-grid-lines-to-polygons/m-p/1183816/highlight/true#M56289
- And I imagine the first query could be modified to make points instead of lines.

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.

Anonymous User

(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.

VinceAngelo

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

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.