topic Re: R&H usage in Roads and Highways User Group (RHUG) Questions

R&H usage

RyanKoschatzky — Tue, 11 Jun 2019 12:59:59 GMT

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?

Re: R&H usage

AndrewVitale3 — Sun, 12 Dec 2021 07:31:31 GMT

I'll take any opportunity I get to play with Pandas!

Those stats are very impressive. You guys are churning out the transactions! Hopefully once we get our implementation solidified, we'll see these numbers grow.

Here's what I found for NYS:

                            COUNT
ACTIVITY
01 - Create route             382
02 - Calibrate route         3204
03 - Reverse route            211
04 - Retire route            1169
05 - Extend route             494
06 - Reassign route          1272
07 - Realign route            806
12 - Carto realign/GP Tool   1404
13 - Load route                46

>>> sum(log_df_by_activity['COUNT'])
8988
‍‍‍‍‍‍‍‍‍‍‍‍‍‍

There was a lot of route loading that went on in 2018. I would need to parse out the ROUTE_IDs from the log table's blob field to get a true indication of how many routes were loaded into the system, and I don't think the juice is worth the squeeze.

Here's how I came up with the table:

import datetime
import os

import arcpy
import numpy as np
import pandas as pd

arcpy.env.workspace = r'Database Connections\prod_elrs_elrs_ad_lockroot.sde'

log_table = arcpy.ListTables('*Edit_Log')[0]

where_clause = (
    "TRANSACTIONDATE >= '{}' ".format(datetime.datetime(2018, 1, 1)) +
    "AND TRANSACTIONDATE < '{}'".format(datetime.datetime(2019, 1, 1))
)
table_array = arcpy.da.TableToNumPyArray(
    log_table,
    ['TRANSACTIONDATE', 'ROUTEID', 'ACTIVITYTYPE', 'USERNAME'],
    where_clause=where_clause
)
log_df = pd.DataFrame(table_array)

condition_list = [
    log_df.ACTIVITYTYPE == 1, log_df.ACTIVITYTYPE == 2,
    log_df.ACTIVITYTYPE == 3, log_df.ACTIVITYTYPE == 4,
    log_df.ACTIVITYTYPE == 5, log_df.ACTIVITYTYPE == 6,
    log_df.ACTIVITYTYPE == 7, log_df.ACTIVITYTYPE == 12,
    log_df.ACTIVITYTYPE == 13,
]
choice_list = [
    'Create route', 'Calibrate route',
    'Reverse route', 'Retire route',
    'Extend route', 'Reassign route',
    'Realign route', 'Carto realign/GP Tool',
    'Load route',
]

log_df['ACTIVITYDESCRIPTION'] = np.select(condition_list, choice_list)

log_df['ACTIVITY'] = (
    log_df['ACTIVITYTYPE'].apply(lambda x: '{:02d}'.format(x)) + 
    ' - ' + 
    log_df['ACTIVITYDESCRIPTION']
)

log_df_by_activity = log_df.groupby(['ACTIVITY']).count()
log_df_by_activity = log_df_by_activity[['ROUTEID']]
log_df_by_activity.columns = ['COUNT']
print(log_df_by_activity)

log_df_by_user = log_df.groupby(['USERNAME']).count()
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: R&H usage

PhilHardy — Thu, 20 Jun 2019 16:32:57 GMT

Andrew,

This is good stuff and thanks for sharing the code. I know what I would like is some way to do something similar to make a readable audit trail of edits. But to do that I would need a programmatic way to do the "Show XML" right-click to the edit log. Have you (or anybody else) been able to do this?

Re: R&H usage

RyanKoschatzky — Thu, 20 Jun 2019 17:05:26 GMT

Nice Andrew.

Yes, we a have a few transactions but with up to 11 editors at one point I would hope so.

I had dropped out calibration point because our numbers would look over inflated as we typically have more then the traditional 2 points per route from our data load. For 2018 our Calibrate Route was 32463 records.

We don't have any loaded routes because we did that work in 2016 before we started going live when we where fixing branched routes etc. Our 2017 number are slightly below 2018 totals but 2019 seems on pace with 2018 numbers. As with anything, I not 100% sure what numbers really mean other then we have touched a lot of data and if we want to put metrics behind them in the future we have something that can be comparable through the years.

Re: R&H usage

PhilHardy — Thu, 20 Jun 2019 17:34:39 GMT

Andrew, good stuff. The edit log adds an extra difficulty over just working with XML. It stores the XML as a blob in the EditData field. That is the part that has me stuck. I don't know how to unlock that blob data.

Re: R&H usage

AndrewVitale3 — Thu, 20 Jun 2019 17:52:15 GMT

Phil,

It definitely adds some complexity, but I can see why Esri made this decision. Our log table would be nothing but load route activities at this point. If you come up with a more efficient way to parse the xml once it's extracted in the cursor, I'd love to see it.

Re: R&H usage

RyanKoschatzky — Fri, 03 Jan 2020 13:10:08 GMT

With a new year, I thought I would report on NCDOT's 2019 stats.

TransactionDate > '1/1/2019' AND TransactionDate < '1/1/2020' AND ActivityType = X

Edit log Code/R&H Route action name/number of records

1 Create Route 7476

3 Reverse Route 127

4 Retire Route 4563

5 Extend Route 1992

6 Reassign Route 1058

7 Realign Route 795

12 Cartographic Realignment 32154

Please share your stats.

Re: R&H usage

AndrewVitale3 — Sun, 12 Dec 2021 07:31:36 GMT

Nice! You've been busy. Here's where NYSDOT came in for the year:

                            COUNT
ACTIVITY
01 - Create route            1425
02 - Calibrate route          964
03 - Reverse route            193
04 - Retire route             843
05 - Extend route             555
06 - Reassign route           196
07 - Realign route            626
12 - Carto realign/GP Tool   1512
13 - Load route                 7‍‍‍‍‍‍‍‍‍‍‍

Re: R&H usage

KyleGonterwitz — Tue, 07 Jan 2020 21:24:07 GMT

ACTIVITY for 2019-2020, 1471 total

1 - Create Route: 9

2- Calibrate Route: 1203

3 - Reverse Route: 0

4 - Retire: 86

5 - Extend: 11

6 - Reassign: 35

7- Realign: 17

12 - Carto Realign: 107

13 - Load: 3

Re: R&H usage

KyleGonterwitz — Tue, 07 Jan 2020 21:32:43 GMT

all of our KDOT stats start in early July 2019 when we implemented into production roads and highways

Re: R&H usage

MarshallStevenson__SHA_ — Tue, 07 Jan 2020 21:43:26 GMT

Maryland's Report:

1 - Create Route - 227

2 - Calibrate Route - 4443

3 - Reverse Route - 37

4 - Retire Route - 175

5 - Extend Route - 51

6 - Reassign Route - 198

7 - Realign Route - 689

12 - Cartographic Realignment - 4037

13 - Load Route - 0

Re: R&H usage

MichaelClement — Wed, 08 Jan 2020 18:30:56 GMT

Iowa's Numbers:

ACTIVITYTYPE	EDITS_2019	EDITS_2018	EDITS_2017	EDITS_2016
1	402	574	257	356
2	2137	3081	1655	625
3	15	34	7	5
4	340	334	232	126
5	293	452	175	203
6	210	257	84	37
7	223	270	130	78
12	429	600	429	72

Re: R&H usage

RyanKoschatzky — Thu, 09 Jan 2020 12:13:39 GMT

Thanks Andrew, Kyle, Marshall and Michael for responding.

Have any of you broken down the edit types per route class or system vs nonsystem? I have not yet for our data but that is planned.

When looking at the activity type record numbers, did any of the values surprise you for the way your departments are set up? I was expecting most of our values to increase based on what the mid year results showed but not by the numbers I saw.

Re: R&H usage

KyleGonterwitz — Sun, 12 Dec 2021 07:31:39 GMT

SELECT [ActivityType], count([ObjectId]) as Edits
  FROM .[RH].[LRS_EDIT_LOG]
  group by ActivityType
  order by ActivityType

SELECT [ActivityType], count([ObjectId]) as Edits
  FROM .[RH].[LRS_EDIT_LOG]
      WHERE (NetworkId = 1 AND Substring(RouteID, 4 ,1) in (1, 2, 3, 4)) OR  (NetworkId =2 AND Substring(RouteID, 1 ,1) in (1, 2, 3, 4))
  group by ActivityType
  order by ActivityType

SELECT [ActivityType], count([ObjectId]) as Edits
FROM .[RH].[LRS_EDIT_LOG]
    WHERE NetworkId = 1 
	AND Substring(RouteID, 4 ,1) not in (1, 2, 3, 4)
group by ActivityType
order by ActivityType
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

This SQL query give me breakdowns by state/nonstate system

Total:

ActivityType Edits
1 9
2 1203
4 86
5 11
6 35
7 17
12 107
13 3

State System Edits (we have 2 LRMS with state system) including ramps

ActivityType Edits
1 2
2 243
4 31
5 5
6 6
7 1
12 51

Non State System Edits

ActivityType Edits
1 7
2 960
4 55
5 6
6 29
7 16
12 56

The 3 "13" edit types were from a consultant and have null values on the row, I can't explain that.

It is an interesting performance metric. I thought our State System edits would be higher, but this makes sense because we put our greatest effort into loading the State System as clean as possible. Most of these edits are calibrations of the nonstate classified system. I can also break them down by editor - we have three editors, one has done about 89 edits, one has done about 15, and the third did all the rest. I'm mostly surprised that we have not done or accounted for any reverse route edits.

Re: R&H usage

KyleGonterwitz — Thu, 09 Jan 2020 14:38:15 GMT

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.

Re: R&H usage

AndrewVitale3 — Sun, 12 Dec 2021 07:31:42 GMT

Here's our stats by activity and "Roadway Type". We currently have 4 roadway types:

Ramp
Road (Local roads)
Route (Signed Routes)
Not in RIS (Dual-carriageway geometries that are waiting on the inventory)

                                        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.

Re: R&H usage

AndrewVitale3 — Thu, 09 Jan 2020 15:09:14 GMT

Is there any pattern to the edits you believe are missing from the log?

Re: R&H usage

KyleGonterwitz — Thu, 09 Jan 2020 15:24:07 GMT

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.

Re: R&H usage

CharlesMcNeel — Thu, 09 Jan 2020 16:23:15 GMT

I'm a little late to the discussion but wanted to get in here.

WV numbers are this:

1 - Create Route	351
2 - Calibrate Route	2194
3 - Reverse Route	1
4 - Retire Route	15
5 - Extend Route	3
6 - Reassign Route	64
7 - Realign Route	10
12 - Cartographic Realignment	6379
TOTAL	9017

A note is needed. WVDOT has two LRM's and these are numbers for NetworkID "1". Further I'm not all that confident in those numbers since as I look at the NetworkID compared to the RouteID I see that they don't always match. In other words, we use a different format for the RouteID between our LRM's so we can tell at a glance which network it is. Problem is that doesn't always match the recorded attribute for NetworkID. Makes me wonder how much we can glean from this table.

Re: R&H usage

NicoleHanson1 — Thu, 09 Jan 2020 16:33:31 GMT

1- Create Route: 6571

2- Calibrate Route: 466

3- Reverse Route: 54

4- Retire Route: 117

5- Extend Route: 451

6- Reassign Route: 6

7- Realign Route: 14

12- Carto Realign: 246

13- Load: 0

LOL