2

I have a SDE.ST_GEOMETRY query in an Oracle 18c EGDB that gets the startpoint of polylines:

select
    objectid,
    sde.st_geomfromwkb(sde.st_asbinary(sde.st_startpoint(sde.st_geometryn(shape,1))), 26917) --sde.st_startpoint() will fail if there are any multi-part features. So I've used only the first part in multi-part features by specifying: SDE.ST_GeometryN(SHAPE,1).
from
    infrastr.active_transportation --15,000 features

enter image description here

That query takes 32 seconds to run on 15,000 features -- due to the slow ST_GEOMETRY functions.

How can I improve the performance of that query?


There is a known issue in ArcMap and ArcGIS Pro: we can't return the ST_POINT subtype as the shape, we can only return the ST_GEOMETRY supertype. Otherwise we'll get an error in ArcMap/Pro. So that's why I convert to binary and then back to ST_GEOMETRY again -- to convert from the ST_POINT subtype to the ST_GEOMETRY supertype. That conversion accounts for about 15 seconds of the execution time.

1 Answer 1

1

This solution gets the startpoints and the endpoints in a view. Whereas the original question only mentioned startpoints, not endpoints.

Edit: In hindsight, maybe I could have stored multiple XYs in a composite index (as multiple individual number columns), instead of concatenating them into a single text value. I had assumed that I could only index a single column within an index. But maybe that was incorrect.


Create a custom function:

create or replace function endpoint_list(shape in sde.st_geometry) return varchar2 
deterministic is
    coord_list varchar2(4000);
begin
    coord_list := 
        sde.st_geometry_operators.st_x_f(sde.st_geometry_operators.st_startpoint_f(sde.st_geometry_operators.st_geometryn_f(shape,1))) || ',' || 
            sde.st_geometry_operators.st_y_f(sde.st_geometry_operators.st_startpoint_f(sde.st_geometry_operators.st_geometryn_f(shape,1))) || ',' || 
                sde.st_geometry_operators.st_x_f(sde.st_geometry_operators.st_endpoint_f(sde.st_geometry_operators.st_geometryn_f(shape,sde.st_geometry_operators.st_numgeometries_f(shape)))) || ',' ||
                    sde.st_geometry_operators.st_y_f(sde.st_geometry_operators.st_endpoint_f(sde.st_geometry_operators.st_geometryn_f(shape,sde.st_geometry_operators.st_numgeometries_f(shape))));
    return coord_list; 
end;

Create a function-based index on the function (and on the OBJECTID column — as a composite index).

--Note: the index isn't a spatial index.
create index atn_endpoint_list_idx on
    infrastr.active_transportation(infrastr.endpoint_list(shape),OBJECTID);
                                                            🡅       🡅

Create a view that uses the function-based index:

create or replace view bc_endpoint_fbi_union_vw as (
select * from (
with cte as (
    select  
        cast(objectid as number(38,0)) as objectid,
        infrastr.endpoint_list(shape) as list
    from
        infrastr.active_transportation
    where
         infrastr.endpoint_list(shape) is not null
    )    
select
    objectid || '-START' as unique_id,
    objectid,
    'STARTPOINT' as TYPE,
    cast(regexp_substr(list, '[^,]+', 1, 1) as number) as x,
    cast(regexp_substr(list, '[^,]+', 1, 2) as number) as y
from
    cte  
union all
select
    objectid || '-END' as unique_id,
    objectid,
    'ENDPOINT' as TYPE,
    cast(regexp_substr(list, '[^,]+', 1, 3) as number) as x,
    cast(regexp_substr(list, '[^,]+', 1, 4) as number) as y
from
    cte  
))

enter image description here

We can see that the function-based index is indeed being used (see line #3):

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                             |  1454 |  2861K|    26   (0)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION               |                             |       |       |            |          |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D67BD_67C8FF97 |       |       |            |          |
|*  3 |    INDEX FAST FULL SCAN                  | ATN_ENDPOINT_LIST_IDX       |   727 |   299K|    53  (10)| 00:00:01 |
|   4 |   UNION-ALL                              |                             |       |       |            |          |
|   5 |    VIEW                                  |                             |   727 |  1430K|    13   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL                    | SYS_TEMP_0FD9D67BD_67C8FF97 |   727 |   299K|    13   (0)| 00:00:01 |
|   7 |    VIEW                                  |                             |   727 |  1430K|    13   (0)| 00:00:01 |
|   8 |     TABLE ACCESS FULL                    | SYS_TEMP_0FD9D67BD_67C8FF97 |   727 |   299K|    13   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("INFRASTR"."ENDPOINT_LIST"("SHAPE") IS NOT NULL)

In ArcGIS Pro, create an XY event layer on the view.

  • The XY event layer is really fast. Just as fast as a feature class, which is really surprising. In fact, it's faster than selecting all the rows in SQL Developer (2.5 seconds). That doesn't make any sense to me. But whatever, I'll take it.

Related: Function-based spatial indexes - Tips

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.