In order to compose the ROUTE_NAME components for crash the following fields have been transformed.
Table Name | Column Names
| Transformation (logic for transportation) |
SMS_CRS_CRASH | SMS_COUNTY_ID | LPAD(a.SMS_COUNTY_ID,2,0) To make sure the county ID is two-digit and padded with zero from the left |
SMS_CRS_CRASH | SMS_HWYCLASS_ID | SELECT b.LRS_COMP_ID from SETUP_SMS_HWYCLASS b WHERE a.SMS_HWYCLASS_ID=b.SMS_HWYCLASS_ID The code values for SIGN System in LRS DO NOT map 100% to the current Highway Class; therefore, a cross-reference table will be used. This is available in: Safety > Setup > Crash Location > Highway Class
|
Table Name | Column Names
| Transformation (logic for transportation) |
SMS_CRS_CRASH | SMS_ROUTE | LPAD(a.SMS_ROUTE,4,0) To make sure the Route ID is 4 digit and padded with zero from left. |
SMS_CRS_CRASH | SMS_SUPP_DESIG_ID | SELECT b.LRS_COMP_ID from SETUP_SMS_SUPP_DESIG b WHERE a.SMS_SUPP_DESIG_ID=b.SMS_SUPP_DESIG_ID The code values for supplementary code DO NOT map 100% to the current LRS supplementary code; Also there are many crashes where this value is not available; Therefore, this particular table will be used) This is available in (Safety > Setup > Crash Location > Highway Class. |
SMS_MP | SMS_MP/100 Mile post is expressed hundredth of a mile. Therefore, it will be divided by 100 |
