Standalone Table — Select where no duplicates, join to FC, update table's field with joined shape length

Question

ArcGIS Pro 2.9.5; Oracle 18c 10.7.1 EGDB

I have a ROADS line FC (has an ID field) and an EVENTS standalone table. EVENTS is a linear referencing table with ID, FMEAS (double), and TMEAS (double) fields. The relationship between ROADS and EVENTS is 1:M. One road to 0, 1, or more events.

Some of the EVENTS.TMEAS values are larger than the length of the related road, which is incorrect.

I want to:

* Select EVENTS where there is only one event per road.
* Join EVENTS to ROADS to bring the ROAD shape length field into the EVENTS attribute table.
* Calculate field: For the selected EVENTS, update the FMEAS field to the ROAD's shape length.

How can I do that?

I am running into join errors when trying to use a SQL subquery for the selection, so am looking for a different approach.

Bud · Answer

For #1, I could use the Frequency GP tool or Summary Statistics GP tool to output a separate FGDB table with info about the duplicates. Then join the output table back to the EVENTS table and perform a selection. Then #2 and #3 should be relatively straightforward.

GP tool to populate COUNT field with count of attribute occurrences

Edit: When joining EVENTS (input table) to ROADS (join table), the shape length from ROADS won't be included in the resulting join. I suppose that complicates things, since I can't add fields to the enterprise geodatabase tables/FCs, such as a temporary ROAD_LENGTH field.

Join to FC — Include shape length field in attribute table

Hornbydd · Answer

My gut feeling is you need to create your route events using your linear referencing then export those into a polyline FeatureClass and do your spatial querying. Then as some final processing step copy your fields of interest back into the original standalone event tables.