Regarding ST_GEOMETRY functions:

I've had a lot of problems trying to create queries using ST_GEOMETRY functions -- when the query returns an ST_POINT subtype shape: ArcGIS Pro crashes when copying Oracle view to FGDB.

And ST_GEOMETRY functions are generally too slow for this kind of thing. For example, this would take 20 seconds to run on 15,000 features:

select
    sde.st_x(sde.st_startpoint(sde.st_geometryn(shape,1))) shape
from
    active_transportation

So an alternative idea might be:

Use the linear referencing functionality in ArcGIS Pro (in conjunction with a non-spatial db view):

1. Create a db view that has a point_evt_measure column.

   • The measure value for startpoints is 0. The measure value for endpoints is: the total length of the line.
   • Note: This technique only works for cases where the polyline FC is M-enabled. And where the M-values have been entered as the cumulative length of the line. In other words, the last vertex's M-value is the same as the length of the line.

   create or replace view point_evt_measure_vw as (
   select
       atn_id || '-' || 'START' as unique_id,
       atn_id,
       0 as point_evt_measure
   from
       active_transportation
   union all
   select
       atn_id || '-' || 'END' as unique_id,
       atn_id,
       sde.st_length(shape) as point_evt_measure  --Unlike other ST_GEOMETRY functions, ST_LENGTH is actually quite fast, since it's stored as an "attribute" in the SHAPE object. 
       --https://desktop.arcgis.com/en/arcmap/latest/manage-data/gdbs-in-oracle/stgeometry-oracle.htm#:~:text=The%20perimeter%20length%20of%20the%20geometry
   from
       active_transportation
   )
   

   

2. In ArcGIS Pro, create a Route Event Layer that uses the point_evt_measure column from the view:

   

That seems to work.

Performance:

• The DB view is relatively fast: 1.2 seconds to select all rows in SQL Developer.
• But the Route Event Layer is a lot slower. It takes about 10 seconds to show the 30,000 start/endpoints. With that said, once the layer is initially drawn, it seems to be quite fast after that. Maybe ArcGIS Pro caches the data in RAM? Or does Oracle do some caching?

Ultimately, the performance is a tad slow. But the layer is still useful for exporting as a FC to other DBs, AGOL, etc.

Regarding ST_GEOMETRY functions:

I've had a lot of problems trying to create queries using ST_GEOMETRY functions -- when the query returns an ST_POINT subtype shape: ArcGIS Pro crashes when copying Oracle view to FGDB.

And ST_GEOMETRY functions are generally too slow for this kind of thing. For example, this would take 20 seconds to run on 15,000 features:

select
    sde.st_x(sde.st_startpoint(sde.st_geometryn(shape,1))) shape
from
    active_transportation

So an alternative idea might be:

Use the linear referencing functionality in ArcGIS Pro (in conjunction with a non-spatial db view):

1. Create a db view that has a point_evt_measure column.

   • The measure value for startpoints is 0. The measure value for endpoints is: the total length of the line.
   • Note: This technique only works for cases where the polyline FC is M-enabled. And where the M-values have been entered as the cumulative length of the line. In other words, the last vertex's M-value is the same as the length of the line.

   create or replace view point_evt_measure_vw as (
   select
       atn_id || '-' || 'START' as unique_id,
       atn_id,
       0 as point_evt_measure
   from
       active_transportation
   union all
   select
       atn_id || '-' || 'END' as unique_id,
       atn_id,
       sde.st_length(shape) as point_evt_measure  --Unlike other ST_GEOMETRY functions, ST_LENGTH is actually quite fast, since it's stored as an "attribute" in the SHAPE object. 
       --https://desktop.arcgis.com/en/arcmap/latest/manage-data/gdbs-in-oracle/stgeometry-oracle.htm#:~:text=The%20perimeter%20length%20of%20the%20geometry
   from
       active_transportation
   )
   

   

2. In ArcGIS Pro, create a Route Event Layer that uses the point_evt_measure column from the view:

   

That seems to work.

Performance:

• The DB view is relatively fast: 1.2 seconds to select all rows in SQL Developer.
• But the Route Event Layer is a lot slower. It takes about 10 seconds to show the 30,000 start/endpoints. With that said, once the layer is initially drawn, it seems to be quite fast after that. Maybe ArcGIS Pro caches the data in RAM?

Ultimately, the performance is a tad slow. But the layer is still useful for exporting as a FC to other DBs, AGOL, etc.

Regarding ST_GEOMETRY functions:

I've had a lot of problems trying to create queries using ST_GEOMETRY functions -- when the query returns an ST_POINT subtype shape: ArcGIS Pro crashes when copying Oracle view to FGDB.

And ST_GEOMETRY functions are generally too slow for this kind of thing. For example, this would take 20 seconds to run on 15,000 features:

select
    sde.st_x(sde.st_startpoint(sde.st_geometryn(shape,1))) shape
from
    active_transportation

So an alternative idea might be:

Use the linear referencing functionality in ArcGIS Pro:

1. Create a db view that has a point_evt_measure column.

   • The measure value for startpoints is 0. The measure value for endpoints is the length of the line.
   • Note: This technique only works for cases where the polyline FC is M-enabled. And where the M-values have been entered as the cumulative length of the line. In other words, the last vertex's M-value is the same as the length of the line.

   create or replace view point_evt_measure_vw as (
   select
       atn_id || '-' || 'START' as unique_id,
       atn_id,
       0 as point_evt_measure
   from
       active_transportation
   union all
   select
       atn_id || '-' || 'END' as unique_id,
       atn_id,
       sde.st_length(shape) as point_evt_measure  --Unlike other ST_GEOMETRY functions, ST_LENGTH is actually quite fast, since it's stored as an "attribute" in the SHAPE object. 
       --https://desktop.arcgis.com/en/arcmap/latest/manage-data/gdbs-in-oracle/stgeometry-oracle.htm#:~:text=The%20perimeter%20length%20of%20the%20geometry
   from
       active_transportation
   )
   

   

2. In ArcGIS Pro, create a Route Event Layer that uses the point_evt_measure column from the view:

   

That seems to work.

Performance:

• The DB view is relatively fast: 1.2 seconds to select all rows in SQL Developer.
• But the Route Event Layer is a lot slower. It takes about 10 seconds to show the 30,000 start/endpoints. With that said, once the layer is initially drawn, it seems to be quite fast after that. Maybe ArcGIS Pro caches the data in RAM? Or does Oracle do some caching?

Ultimately, the performance is a tad slow. But the layer is still useful for exporting as a FC to other DBs, AGOL, etc.

Regarding ST_GEOMETRY functions:

I've had a lot of problems trying to create queries using ST_GEOMETRY functions -- when the query returns an ST_POINT subtype shape: ArcGIS Pro crashes when copying Oracle view to FGDB.

And ST_GEOMETRY functions are generally too slow for this kind of thing. For example, this would take 20 seconds to run on 15,000 features:

select
    sde.st_x(sde.st_startpoint(sde.st_geometryn(shape,1))) shape
from
    active_transportation

So an alternative idea might be:

Use the linear referencing functionality in ArcGIS Pro (in conjunction with a non-spatial db view):

1. Create a db view that has a point_evt_measure column.

   • The measure value for startpoints is 0. The measure value for endpoints is: the total length of the line.
   • Note: This technique only works for cases where the polyline FC is M-enabled. And where the M-values have been entered as the cumulative length of the line. In other words, the last vertex's M-value is the same as the length of the line.

   create or replace view point_evt_measure_vw as (
   select
       atn_id || '-' || 'START' as unique_id,
       atn_id,
       0 as point_evt_measure
   from
       active_transportation
   union all
   select
       atn_id || '-' || 'END' as unique_id,
       atn_id,
       sde.st_length(shape) as point_evt_measure  --Unlike other ST_GEOMETRY functions, ST_LENGTH is actually quite fast, since it's stored as an "attribute" in the SHAPE object. 
       --https://desktop.arcgis.com/en/arcmap/latest/manage-data/gdbs-in-oracle/stgeometry-oracle.htm#:~:text=The%20perimeter%20length%20of%20the%20geometry
   from
       active_transportation
   )
   

   

2. In ArcGIS Pro, create a Route Event Layer that uses the point_evt_measure column from the view:

   

That seems to work.

Performance:

• The DB view is relatively fast: 1.2 seconds to select all rows in SQL Developer.
• But the Route Event Layer is a lot slower. It takes about 10 seconds to show the 30,000 start/endpoints. With that said, once the layer is initially drawn, it seems to be quite fast after that. Maybe ArcGIS Pro caches the data in RAM? Or does Oracle do some caching?

Ultimately, the performance is a tad slow. But the layer is still useful for exporting as a FC to other DBs, AGOL, etc.