Polyline endpoint query layer in ArcGIS Pro

Question

I have an Oracle 18c/SDE.ST_GEOMETRY polyline FC (15,000 features).

I want to create a query layer (or db view) that will display the startpoints and endpoints of the lines in ArcGIS Pro.

Is there a way to create a query layer that will dynamically display those points?

---

Related:

• ArcGIS Pro Community - Dynamic endpoints layer.
• Improve performance of startpoint query (ST_GEOMETRY)

Answer 1

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.