• Query Iowa DOT roadway centerline theme to select rural expressways. For this study, rural expressways were defined as non-Interstate roadways with four (or more) lanes located outside of corporate limits.

• Select (by Theme) all roadway segments from the centerline theme that are within 16 meters of the rural expressways (previous selection set). This value may be adjusted, but, if too large, too many roadways will be selected.
• Add these records to the existing selection set.
• Save all records (rural four-lane expressways and intersecting roadways) as new theme, "rural_express.shp", and add to view.

• Query the GIS-ALAS "A" records theme(s) to select rural, intersection crashes. For this study, rural, intersection crashes were defined as those indicated as rural, non-interchange crashes possessing a valid intersection node id and intersection class code. These selection criteria yield all rural, intersection crashes and are not limited to expressways.

• Repeat this query for all "A" record themes containing data from years of interest.

• Select (by Theme) all rural, intersection crashes, from the "A" records theme(s), previous selection set, that are within 50 meters of the rural expressways (selection set from "Identify Intersecting Roadways"). This selection may yield non-expressway crashes as well, specifically intersection crashes occurring along the intersecting roadways.
• Merge selected features from all "A" record themes and save as "int94_98.shp". (This represents a five-year analysis period.)

• A value of 50 meters was utilized because it is approximately equal to the accuracy of the cartographic data.

(Note: If DEV is known for all [Int_id], the following procedure is not required.)

• Edit "int94_98.shp" and add a new field: [Coint_id], string(8) or decimal(8).
• Update [Coint_id] with the concatenation of [Co] and [Int_id].

• Using the Geoprocessing Wizard, dissolve rural, expressway intersection crashes by [Coint_id]. Create output file "int94_98x.shp". Summarize [Prop_dmg] by sum for use in crash ranking. Crash frequency [Count] is calculated automatically and will also be used in crash ranking.
• Other fields may also be summarized to yield the general intersection physical characteristics and crash history. In addition, discrepancies among recording officers may become apparent through the summary data.
• Potential fields on interest include: [Vehicles] by sum, [Killed] by sum, [Injured] by sum, [Prop_dmg] by sum, [Severity] by minimum, [County] by minimum, [City] by minimum, [Rur_urb] by first, [Route] by first, [Int_class] by average, [Road_class] by average, [Road_char] by average, [Road_geo] by average, and [Locality] by average.

• This step not only yields a single point feature at each crash site with the selected, summarized "A" record attributes, but the total number (count) of crashes at the site as well.
• This step is necessary because the primary emphasis of analysis is location-based, not incident-based. However, for incident-based analysis, all crashes may be tied back to a specific site using the unique [Coint_id].
• [Int_id] is only unique if proceeded by county number, [Co].
• Although infrequent, crashes at a single site may incorrectly posses different [Coint_id] values, resulting in several disparate locations with the same [Coint_id]. These locations must be assessed independently.

• With the "int94_98x.shp" theme active, load, compile, and run "bufferid.ave" script.
• Use the [Cont_id] field to identify output buffers and set buffer radius approximately equal to 50 meters (the cartographic accuracy).
• Save buffers as "int94_98buf.shp".

Notes:

• This script creates a buffer around each intersection and assigns [Coint_id] to the buffer.
• The newly created buffers will be used to assign intersection approaches to each intersection.

• Using the Geoprocessing Wizard, clip the "rural_express.shp" theme with the "int94_98buf.shp" theme. Save the resulting, partial roadway centerline theme as "re_int.shp".

• Using the Geoprocessing Wizard, assign data by location from the "int94_98buf.shp" theme to the "re_int.shp" theme.

• Summarize [Input Shape ID] ([Coint_id]) field of "re_int.shp" theme. Save resulting dbf file as "re_int_sum.dbf".

• Assign the number of centerline segments to each intersection point feature. This is accomplished by joining "re_int_sum.dbf" to "re_int" theme, using [Coint_id] and saving the new theme as "re_int2.shp". A [Count] field is now present in "re_int2.shp". This represents the number of unique centerline segments located within the intersection buffer.

• Centerline segments may not terminate at intersections.
• Edit "re_int2.shp" and add field: [Seg_type], string(1). This field will be populated with one of the following: "E" = centerline segment ends/terminates at intersection, "T" = centerline segment continues through intersection, "D" = centerline segment should not be associated with intersection.
• Edit "rural_express.shp" and add fields: [Coint_id], string(8) or decimal(8), [Count], decimal(1), and [Seg_type], string(1).
• Create new table, "re_int2_append.dbf". Add fields: [Road_link], [Coint_id], string(8) or decimal(8), [Count], decimal(1), and [Seg_type], string(1). As necessary, records will be added this table to accurately and completely reflect intersection approaches.

• Sort "re_int2.dbf" by [Input Shape Id] ([Coint_id]).
• Select records (centerlines) possessing the same [Input Shape Id] and fit these records in the active view. Assess whether the selected records are correctly attributed to the active intersection.
• If a segment is correctly attributed to an intersection, update the [Count] and [Seg_type] fields accordingly.
• If a segment appears to be incorrectly attributed to an intersection, update the [Coint_id] field with null. This may occur if concurrent segments of roadway (along a common intersection approach) fall within the intersection buffer.
• If an approach segment appears to be missing, identify the appropriate segment in "rural_express.shp" and update the [Coint_id], [Count], and [Seg_type] fields. If this record has already been assigned to another intersection, update [Road_link], [Coint_id], [Count], and [Seg_type] in the "re_int2_append.dbf" file. This may occur if a segment lies within multiple intersection buffers.
• Repeat for all intersections.

• Select all records from "re_int2.dbf", "rural_express.shp", and "re_int2_append.dbf" where [Coint_id], or [Input Shape ID], is greater than zero or not null.
• Export the selected records from the aforementioned tables to individual dbf files.
• Using Microsoft Excel, format and append the exported tables. The final table should contain the following fields, [Road_link], [Coint_id] ([Input Shape Id]), [Count], [Seg_type], as well as new, blank fields of [Aadt], decimal(8), [Adj_aadt], decimal(8), and [Yr_cnt], decimal(4). Save this table as "int1.dbf".

• Within ArcView, join "int1.dbf" with a dbf file containing roadway centerline data, specifically [Road_link], [Aadt], and [Yr_cnt] fields, using [Road_link]. The [Road_link] field represents the unique, Base Record segment identifier.
• Update the [Aadt] and [Yr_cnt] fields in "int1.dbf" with the values contained in the corresponding fields from the joined table. If the field names are the same, aliases must be assigned to the common fields in one of the tables.
• Select from "int1.dbf" where [Seg_type]="E". Calculate [Adj_aadt]=[Aadt]/2.
• Switch selection and calculate [Adj_aadt]=[Aadt].
• Select none and save table "int1.dbf".
• Summarize "int1.dbf" on [Input Shape Id] ([Coint_id]), calculating [Adj_aadt] by sum. The resulting [Adj_aadt_sum] field represents intersection daily entering vehicles (DEV). Save the summary table as "Dev.dbf".

• Link GIS-ALAS "C" record table to "int94_98" on [Crash_key]. Only display [Crash_key] and [Severity] fields from "C" records table.
• Select crashes from the merged "A" records which occur in the same year as the crashes represented in the "C" records table. This query will select both the "A" records of interest as well as the corresponding "C" records.
• For example, if the "C" records table is "zc1c1998.dbf", representing 1998 crashes, perform the following query on the merged "A" records:

• Remove all links from the "C" and merged "A" records tables. Clear all selected records (select none) and link a different "C" records table, representing a different year, to the merged "A" records.
• Repeat until the appropriate "C" records for all analysis years have been selected.
• Export, from all "C" records tables, all selected records.
• Append all newly exported "C" records tables by loading, compiling, and running "table_append.ave" script. Save appended tables as "sev94_98.dbf".
• Edit "sev94_98.dbf" and add fields: [Coint_id], string(8) or decimal(8), and [Sev_loss], decimal(8).
• Join "int94_98x" to "sev94_98" using [Crash_key] and update [Coint_id] with [Coint_id] from "int94_98x".
• Open "sev94_98.dbf" in Microsoft Excel. Use an "IF" statement to update [Sev_loss] based on the value of [Severity], e.g.

• Within Excel, create a pivot table report using [Coint_id] as the rows of the table and the sum of [Sev_loss] as the data. This yields the total Export the pivot table as "sev_loss.dbf".

• Within ArcView, join "sev_loss" and "Dev" to "int94_98x" using [Coint_id].

• Not all records will possess [Adj_aadt_sum] (DEV) or [Sev_loss] values. This occurs when the location is no longer considered in analysis, or when no injuries resulted, or were reported, at an intersection.
• Select all records where [Adj_aadt_sum] (DEV) is greater than zero and convert to a new shape file, "int94_98x_sum.shp"
• Open "int94_98x_sum.dbf" in Excel and calculate crash rates and ranks.
• For each row:
• Sum the [Sev_loss] and [Sum_prop_dmg] columns. This provides a value for total loss [Tot_loss] at the intersection.
• Calculate [Crash_rate] = ([Freq]*[1,000,000])/(365*DEV*5), where the number of analysis years equals five.
• Use Excel’s "Rank" function to independently rank intersections by crash rate, crash frequency, and total loss. For example, if [Tot_loss] is located in column "B", and the database contains 353 records, the rank for record one is calculated using the following expression,

• Calculate the sum the loss, crash rate, and crash frequency ranks.
• Rank the sum of all ranks in ascending order. For example, if the sum of ranks is located in column "O", the following expression applies,