I know Nathan has stated the Lrs_Edit_Log is not meant for human consumption and I don’t disagree, but one can still find what I believe is useful information, especially if you compare years. Here is the type/number of edits I calculated for NCDOT for 2018 using 10.5.1:
TransactionDate > '1/1/2018' AND TransactionDate < '1/1/2019' AND ActivityType = X
Edit log Code/R&H Route action name/number of records
1 Create Route 4684
3 Reverse Route 140
4 Retire Route 1260
5 Extend Route 1070
6 Reassign Route 1020
7 Realign Route 349
12 Cartographic Realignment 19139
What are your stats?
Either this table doesnt do what we think it does, or we will be filing a support ticket, because we are pretty sure it is not tracking all the edits we've done.
Is there any pattern to the edits you believe are missing from the log?
We think there could be a pattern related to undoing the edits though the act of not saving edits in a version and then redoing the edits in that version. We are going to test this.
Maybe this is known, but my hypotheses is that if there were no events tied to the route, the edit isn't logged. I created a subordinate concurrent route for the Kansas Turnpike very recently and don't see it logged. Probably a lot of the ramp route reversals were implemented with data gaps so those weren't logged. I think that's the common connection. Is this a known behavior and I didn't know about it? It makes sense based on Esri's stated purpose for the table.
Maybe to count edits more thoroughly we should query the editor tracking on routes.
Kyle
This might be a byproduct of the 2 LRMs. WV and you which both have 2 LRMs are both reporting some discrepancies with the log. Hopefully Nathan or Esri can provide some enlightenment on that front.
What I have seen is that if you have R&H turned on (yes some workflows are instructed to have it turned off) that all route edits using the toolbar and cartographic realignments are captured. A reassign does not capture the route it will be come in the reassign record for the route but there should be a record for a created route with the new route id. A visual or centerline sequence table query can help figure that out.
I would like to see something similar or another method for capturing event changes. I want to break out event behavior changes vs business owner changes. For NC if we have different user names for LRS editors, LRS event editors and other event editors so removing the time filter some counts be derived.
Ryan Koschatzky wrote:
A reassign does not capture the route it will be come in the reassign record for the route but there should be a record for a created route with the new route id.
To add to this a little, the ROUTEID field in the Lrs_Edit_Log will show the original Route ID. To access the NewRouteId, you can right click the text "Blob" in the EDITDATA field and look through the XML.
Kyle, if the missing Route IDs were created using the Create Route tool, that's probably something to be concerned about. Especially if you're using the Relocate Events workflow with external systems. However, if they were loaded in as new routes using the "Load Routes" wizard, it's expected that the Route ID does not show up in the ROUTEID field. You need to query the EDITDATA field for the XML, which will have a list of all of the routes loaded in that run. This one tripped me up while trying to do some trouble shooting on an external system.
Andrew Vitale wrote:
To add to this a little, the ROUTEID field in the Lrs_Edit_Log will show the original Route ID. To access the NewRouteId, you can right click the text "Blob" in the EDITDATA field and look through the XML.
Great tip. Thanks
If I query my County LRM base table by for edit dates I get 2864 rows that have been edited in the same time period as my edit log stats.
SELECT count(*) as EditCount, [LastEditedUser]
FROM [RH].[LRS_COUNTY]
where [LastEditedDate] is not null
group by [LastEditedUser]
EditCount LastEditedUser
9 User1
68 User2
640 User3
1967 User4
I also noticed my _evw view is missing edit tracking dates that exist in the base table. :{
I also can remove end dated features:
SELECT count(*) as EditCount, [LastEditedUser]
FROM [RH].[LRS_COUNTY]
where [LastEditedDate] is not null
and [LRSToDate] is null
group by [LastEditedUser]
EditCount LastEditedUser
5 user1
66 user2
464 user3
1940 user4
for more interesting stats I can add SUM((Shape.STLength())/5280) as mileage to the select statement
Here's our stats by activity and "Roadway Type". We currently have 4 roadway types:
ROUTEID
ACTIVITY ROADWAYTYPE
01 - Create route Not in RIS 87
Ramp 46
Road 1292
02 - Calibrate route Not in RIS 12
Ramp 95
Road 816
Route 41
03 - Reverse route Not in RIS 2
Road 188
Route 3
04 - Retire route Not in RIS 64
Ramp 117
Road 622
Route 40
05 - Extend route Not in RIS 7
Ramp 10
Road 531
Route 7
06 - Reassign route Not in RIS 3
Ramp 10
Road 175
Route 8
07 - Realign route Not in RIS 111
Ramp 39
Road 322
Route 154
12 - Carto realign/GP Tool Not in RIS 4
Ramp 112
Road 1386
Route 10
# Edit: My original "join" logic was adding rows to the table
I'd say on the whole, the stats kind of match my expectations. The vast majority of our edits are on local roads. This makes sense given the shear quantity of local roads and our business practices. The most surprising stat to me in the above table is the fact that 3 of our state routes underwent a "Reverse Route" edit. If I had to guess, these were probably short administrative routes that we have in our system.
We've had various "efforts" throughout the years that partially show up in these statistics, but I feel like a lot of the story is missing. For example, as we became comfortable with R&H's concurrency model, we made a concerted effort to remove local roads that were fully overlapped by state routes. Much of this work took place in 2018, hence we see a slightly larger number of retire route activities in 2018 compared to 2019. I'd have expected the difference to be much greater. I think the interesting story for this particular example lies in the magnitude of the edits. We'd probably have to parse the XML from the edit log to test this, but I'd guess that the majority of the 2018 retire route activities were conducted on the entirety of a route, whereas 2019 probably saw more partial route retirements.
We are still working towards managing our inventory as internal events. I'll be curious how the event edit statistics compare to the route geometry statistics once we get there. I'd imagine a lot more transactions take place on the event tables.