Indiana Bridges

Scott Haraburda

To select bridges that meet specific criteria and geographically portray them on a map. This involved filtering more than 600k United States bridges to those meeting four criteria: 1) within the State of Indiana, 2) selected historic bridges, 3) on major roads, and 4) over major rivers.

INTRODUCTION

Historic bridges are an important part of the heritage, development, and transportation system within a community. Unfortunately, throughout the past century, historic bridges have been replaced with modern bridges to accommodate today's higher traffic volumes and larger vehicles. For more than a decade, the State of Indiana has been working to preserve historic bridges, such as rehabilitating them, relocating them to a new location, and repurposing them in their original locations. The Indiana Historic Bridges Inventory is an official government website that contains valuable information about historic bridges within Indiana.

VIDEO EXAMPLE

An example of a historic bridge in Indiana is the West Union Bridge (HAER IN-105) , which is the work of the prolific Indiana covered bridge builder Joseph J. Daniels (1826-1916) and is an excellent example of his use and development of the Burr Arch Truss. At 315 feet from portal to portal, the double-span bridge is the longest covered bridge in Parke County, a county known for its numerous covered bridges of considerable length. J.J Daniels built the bridge in 1876 to replace the previous bridge (also of his design and construction) crossing Sugar Creek, which was destroyed by high water. Daniels built nearly sixty covered bridges in Indiana between 1855 and ca. 1900. The West Union Bridge represents a later Burr Arch Truss design. The  National Covered Bridges Recording Project  was undertaken by the Historic American Engineering Record (HAER). It is a long-range initiative to document historically significant engineering and industrial works in the United States.  The following is a video HAER Fly-through animation of this bridge.

HAER Fly-through of West Union Bridge, Spanning Sugar Creek, Montezuma, Indiana

RETRIEVE FEATURES

The following four publicly-available features were retrieved online:

ANALYZE DATA

The following Python script is used to select the bridges within Indiana from this file and save the resulting database from the query for use in the assessment, specifically saving this as bridges within Indiana (bridges_IN). Selection of the bridges uses the script format of name.layers[0].query(where='field filter'). The field filter uses the STATE_CODE_001 field name with 18 as the code for the State of Indiana.

bridges_IN=bridges.layers[0].query(where='STATE_CODE_001=18')
bridges_IN.save(pGDB, "bridges_IN")

The above script extracted 19,381 bridges within the State of Indiana from the 621,581 bridges throughout the United States.

Statistical Information for Attributes

The following script is used to calculate the statistical information for attributes of these bridges.

bridges_IN.describe().T

Statistical Information for Attributes for Indiana Bridges

Frequency for Years Bridges Built

The following script is used to create frequency table for the year that Indiana bridges were built. Additional information about this function is located on the  Frequency (Analysis)  site.

input1 = path + name_GDB + "/bridges_IN"
output1 = path + name_GDB + "/bridges_IN_freq"
temp = arcpy.analysis.Frequency(input1, output1, "YEAR_BUILT_027")
table1 = arcpy.da.TableToNumPyArray(output1, '*')
# convert to a Pandas DataFrame
bridges_IN_freq = pd.DataFrame(table1)
bridges_IN_freq

Frequency of Years Indiana Bridges Built

Histogram Plot for Years Bridges Built

The following script is used to plot a histogram of the density for the year that Indiana bridges were built (including a normal distribution curve). Information about this matplotlib function is found at the  histogram (hist) function  site.

del3 = bridges_IN['YEAR_BUILT_027']
mean1 = statistics.mean(del3)
stdev1 = statistics.stdev(del3)
num_bins = 42
fig, ax = plt.subplots()
# the histogram of the data
n, bins, patches = ax.hist(del3, num_bins, density=False)
# add a 'best fit' line
y = 80000*((1 / (np.sqrt(2 * np.pi) * stdev1)) *
     np.exp(-0.5 * (1 / stdev1 * (bins - mean1))**2))
ax.plot(bins, y, '--')
ax.set_xlabel('Year')
ax.set_ylabel('Frequency (count)')
ax.set_title('Histogram of year bridges built: '
             fr'$\mu={mean1:.0f}$, $\sigma={stdev1:.0f}$')
# Tweak spacing to prevent clipping of ylabel
fig.tight_layout()
plt.show()

Selection of Historical Bridges

The following script is used to select historical bridges within Indiana and save the resulting database from the query for use in the assessment, specifically saving this as historic bridges within Indiana (bridges_IN_hist). The field filter uses the code for the State of Indiana and the HISTORY_037 field name with 1 as the code for selected historic bridges and 2 as the code for non-selected historic bridges.

bridges_IN_hist=bridges.layers[0].query(
    where='STATE_CODE_001=18 AND HISTORY_037=1')
bridges_IN_hist.save(pGDB, "bridges_IN_hist")
bridges_IN_hist2=bridges.layers[0].query(
    where='STATE_CODE_001=18 AND HISTORY_037=2')
bridges_IN_hist2.save(pGDB, "bridges_IN_hist2")

After executing this Python script, we have 85 selected and 477 non-selected bridges within Indiana.

Calculate Buffer for Roads

To account for slight differences within the geospatial data, a small buffer (100 feet for this calculation) is created to establish a slight buffer from the lines that the major roads portray on a map. This uses the following script format, which is further discussed at Buffer (Analysis).

temp = arcpy.analysis.Buffer("roads_IN", 
                             "roads_IN_Buffer", 
                             "100 Feet", 
                             "FULL", 
                             "ROUND", 
                             "NONE", 
                             None, 
                             "PLANAR")

Number of Bridges on Major Roads

To select the Indiana selected and non-selected historic bridges that are on, or within 100 feet, of major roads. This uses the following script format, which is further discussed at Intersect (Analysis). In this script, I created an input of the two lists used to create the intersection.

Input = ['bridges_IN_hist', 'roads_IN_Buffer']
Input2 = ['bridges_IN_hist2', 'roads_IN_Buffer']
Output = "bridges_IN_hist_intersect_rd"
Output2 = "bridges_IN_hist_intersect_rd2"
temp = arcpy.analysis.Intersect(Input, Output, "ALL", None, "INPUT")
temp = arcpy.analysis.Intersect(Input2, Output2, "ALL", None, "INPUT")

Retrieving data from the personal geodatabase requires the following script format, which is further discussed at ArcGIS API for Python - API Reference.

bridges_IN_hist_intersect_rd = pd.DataFrame.spatial.from_featureclass("bridges_IN_hist_intersect_rd")
bridges_IN_hist_intersect_rd2 = pd.DataFrame.spatial.from_featureclass("bridges_IN_hist_intersect_rd2")

These tables contain numerous duplicates, requiring additional script to remove them.

Remove Duplicate Bridges on Roads

This requires two lines of code to remove duplicates from a database. Information about this can be found at Delete Identical (Data Management). The STRUCTURE_NUMBER_008 field was used to determine duplicates.

arcpy.env.overwriteOutput = True
arcpy.management.DeleteIdentical("bridges_IN_hist_intersect_rd", "STRUCTURE_NUMBER_008")
bridges_IN_hist_intersect_rd = pd.DataFrame.spatial.from_featureclass("bridges_IN_hist_intersect_rd")
arcpy.management.DeleteIdentical("bridges_IN_hist_intersect_rd2", "STRUCTURE_NUMBER_008")
bridges_IN_hist_intersect_rd2 = pd.DataFrame.spatial.from_featureclass("bridges_IN_hist_intersect_rd2")

The following script is used to print the number of bridges on major roads in Indiana.

x_Intersect = len(bridges_IN_hist_intersect_rd)
x_Intersect2 = len(bridges_IN_hist_intersect_rd2)
print("There are " +  format(x_Intersect, ',') + 
      " selected and " + format(x_Intersect2, ',') +
      " non-selected historic bridges on major roads in Indiana.")

There are 6 selected and 70 non-selected historic bridges on major roads in Indiana.

Bridges over Major Rivers

The following script is used to account for slight differences within the geospatial data, a small buffer (50 feet for this calculation) is created to establish a slight buffer from the lines that the major rivers portray on a map.

temp = arcpy.analysis.Buffer("drainage_IN0", 
                             "drainage_IN0_Buffer", 
                             "50 Feet", 
                             "FULL", 
                             "ROUND", 
                             "NONE", 
                             None, 
                             "PLANAR")

The following script is used to select the Indiana selected historic bridges that cross over, or within 50 feet of, major rivers, those that are at least 10 square miles.

Input = ['bridges_IN_hist', 'drainage_IN0_Buffer']
Input2 = ['bridges_IN_hist2', 'drainage_IN0_Buffer']
Output = "bridges_IN_hist_intersect_drain"
Output2 = "bridges_IN_hist_intersect_drain2"
temp = arcpy.analysis.Intersect(Input, Output, "ALL", None, "INPUT")
temp = arcpy.analysis.Intersect(Input2, Output2, "ALL", None, "INPUT")

Remove Duplicate Bridges over Rivers

This applies the same code used for removing duplicate bridges on roads.

arcpy.management.DeleteIdentical("bridges_IN_hist_intersect_drain", "STRUCTURE_NUMBER_008")
bridges_IN_hist_intersect_drain = pd.DataFrame.spatial.from_featureclass("bridges_IN_hist_intersect_drain")
arcpy.management.DeleteIdentical("bridges_IN_hist_intersect_drain2", "STRUCTURE_NUMBER_008")
bridges_IN_hist_intersect_drain2 = pd.DataFrame.spatial.from_featureclass("bridges_IN_hist_intersect_drain2")

The following script is used to print the number of bridges over major rivers in Indiana.

x_Intersect3 = len(bridges_IN_hist_intersect_drain)
x_Intersect4 = len(bridges_IN_hist_intersect_drain2)
print("There are " +  format(x_Intersect3, ',') + 
      " selected and " + format(x_Intersect4, ',') +
      " non-selected historic bridges that cross major rivers in Indiana.")

There are 38 selected and 160 non-selected historic bridges that cross major rivers in Indiana.

Number of Bridges on Major Roads over Rivers

The following script is used to select the Indiana selected and non-selected historic bridges that are on major roads and over rivers.

Input = ['bridges_IN_hist_intersect_rd', 'bridges_IN_hist_intersect_drain']
Input = ['bridges_IN_hist_intersect_rd2', 'bridges_IN_hist_intersect_drain2']
Output = "bridges_IN_hist_intersect_rd_drain"
Output = "bridges_IN_hist_intersect_rd_drain2"
temp = arcpy.analysis.Intersect(Input, Output, "ALL", None, "INPUT")
temp = arcpy.analysis.Intersect(Input2, Output2, "ALL", None, "INPUT")
bridges_IN_hist_intersect_rd_drain = pd.DataFrame.spatial.from_featureclass("bridges_IN_hist_intersect_rd_drain")
bridges_IN_hist_intersect_rd_drain2 = pd.DataFrame.spatial.from_featureclass("bridges_IN_hist_intersect_rd_drain2")

The following script is used to print the number of bridges on major roads that cross over major rivers in Indiana.

x_Intersect5 = len(bridges_IN_hist_intersect_rd_drain)
x_Intersect6 = len(bridges_IN_hist_intersect_rd_drain2)
print("There are " + format(x_Intersect5, ',') + 
      " selected and " + format(x_Intersect6, ',') +
      " non-selected historic bridges on major roads that cross major rivers in Indiana.")

There are 3 selected and 26 non-selected historic bridges on major roads that cross major rivers in Indiana.

MAPPING

The following is an interactive map of the three selected historic bridges selected above with pop-up for each with a photo and the national bridge number.

Selected Historic Bridges in the State of Indiana that are on major roads and cross rivers.

The following is a map layout of the State of Indiana with its major roads and rivers, along with the selected and non-selected historic bridges selected using the previously mentioned analysis with Python scripts.

ArcGIS Layout

CONCLUSION

The following are results of this geospatial analysis of bridges within Indiana.

  1. From the histrogram of years that the 19,381 Indiana's bridges were built, a large construction effort began in the 1960s and remained consistent for the next 40 years. With a life expectancy of 100 years for each bridge, we can expect to see a large increase in transportation dollars needed beginning in the 2060s.
  2. From the selection, there were only three historical bridges that were on major roads that cross rivers, with another three on major roads that do not cross rivers. These 3-6 bridges might receive increased priority for restoration dollars. For a lower level of priority might be the 26 non-selected historic bridges on major roads that cross major rivers.
Be the first to like this item

About the author

Statistical Information for Attributes for Indiana Bridges

Frequency of Years Indiana Bridges Built

ArcGIS Layout