Post 5: Network Analysis

Goals and Objectives:

The purpose of this lab was to conduct a network analysis to determine the economic impact of trucking sand from mines to rail terminals on local Wisconsin roads. Trempealuea County was of particular interest in the analysis. The number of truck routes and cost per mile were estimated to determine hypothetical costs of mining per county in Wisconsin. A script was created to select mines suitable for analysis. Network analysis was then used to determine the closet route from each mine to the nearest rail terminal. Using network analysis is a valuable tool that can be used by project managers to predict the costs frac sand mines will have on Wisconsin roads.

Background: 

The Midwest is home to many deposits of sand well-suited for fracking. In order to use frac sand in hydraulic fracking, the sand has to be transported from the mining site to the user. It it quite typical for mines to use rail roads to transport sand to the user. The only problem is that sand mines are usually in rural locations, and are often not near rail roads. In order to get their sand to the rail roads, mine operators will use trucks on rural roads that are not built to withstand heavy loads over long periods of time (National Center for Freight & Infrastructure Research & Educatoin, 2013). Once frac sand mines are in full swing in Wisconsin, approximately 40 million tons of sand will be transported out of Wisconsin per year (Wisconsin Department of Transportation NW Region Planning Staff, interview, May 16, 2012). In order to upkeep the roads, Wisconsin has implemented road upgrade maintenance agreements (RUMA) with frac sand mine owners. The agreements ensure mine owners pay for any repairs needed for roads their trucks drive on (National Center for Freight & Infrastructure Research & Educatoin, 2013). The suitability model we create will determine the cost of trucking sand on Wisconsin roads. This will enable land planners to make better-informed decisions on where to allow frac sand mines.

Methods: 

Data Sources:

• Dr. Christina Hupy
• ESRI street map USA
• Wisconsin Department of Natural Resources

Part 1: Creating a Script

The goal for the script was to select frac sand mines suitable for network analysis in our class project, which is focused on creating a suitability model for frac sand mining in Wisconsin. These mines had to fit the following criteria:

• Must be active 
• Mine must not have a rail loading station. Mines with a rail loading station will use the rails to transport their sand, not the local roads. We are focused on mining's impact on local roads. 
• Must be 1.5 km away from any rail roads. Rail road data is not totally up to date, and mines closer than 1.5 km may have had a rail spur built to them within the past few years.

Wrtiting the script used the following procedure (Figure 1): 

• Set up the script in PyScripter
• Set the variables for the feature classes and feature layers to be used in the exercise
• Write 3 SQL statements to query the data based on the above criteria
• Use the SQL statements to select the mines that meet your criteria
• Use arcpy.MakeFeatureLayer_Management
• Use arcpy.SelectLayerByLocation_management to 
• Select only mines in Wisconsin
• Remove all mines within 1.5 km of a rail line
• Save the output layer resulting from the SQL statement using the CopyFeatures_management tool

Figure 1: The python script for this exercise used SQL statements to select frac sand mines suitable for network analysis. The SQL statement was based off three criteria listed above. 

Part 2: Network Analysis

The goal of network analysis was to find the quickest route from each frac sand mine to the nearest rail terminal. The following steps had to be taken to complete the network analysis:

• Connect to ESRI's originalrawdata2013\streetmap_na\data
• Add the "streets" network data set ArcMap
• Turn on Network Analyst and open the Network Analyst toolbar
• Make sure to turn on the Network Analyst extenstion in ArcMap
• Add the final rail selection from the scipting portion of this exercise
• Add the rail terminals feature class
• Select only the terminals that have RAIL in the MODE_TYPE field. Export the selection as a feature class and add it to the map. Use these rail terminals in the analysis.
• Use "closest facility" analysis in Network Analyst to determine which rail terminal each mine will transport its sand to and the most effecitent route between them
• Add a new closest facility layer form the Network Analyst menu
• Load the final mines and rail terminals into the closet facility layer
• Incidents: mines
• Facilities: rail terminals
• Solve the closet facility
• Use Model Builder to develop a data flow model that will determine the most efficient routes between each mine and the closest rail terminal
• Before creating the model, delete the address field in the Mines final feature class (the address field causes an error in model builder)
• Create a new tool box in your geodatabase to save the model to
• Add the Make Closest Facility Layer tool 
• use the Travel to facilty option in the tool
• Use the Add Locations tool to add both the Mines and the Rail Terminals feature classes 
• Add the Solve tool and run the model
• Export the route that was produced from the Closet Facility Layer tool as a feature class in your geodatabase
• Use the Select Data tool to select the route
• Use the Copy Features tool to export the Routes feature class to your geodatabase
• Use Model Builder to calculate the lenth of route by county and estimate the costs each county incurs in road matainanc due to trucking sand
• Project the “Routes” output from the model to Wisconsin Transverse Mercator 1983 (WTM83)
• Intersect the “Wisconsin Cont. Bond” feature class w/ the projected routes
• Add a field to calculate miles (from meters) for that field
• Summarize miles based on County name
• Calculate the cost per county based on results 
• Hypothetically, sand trucks will drive 50 times per year to the closet rail terminal, and will have to make the trip back to the mine as well. The hypothetical cost per truck mile is 2.2 cents. 
• Use the following equation to determine cost: 
• Cost in dollars per year = [Distance (miles)]*[2.2 cents/1 mile]*[$1/100 cents]*100
• Join table back to “Wisconsin Boundaries”

Figure 2: The data flow model was created in Model Builder to create a feature class that contained values for damages each county incurred from trucking sand.

Results:

Seventeen counties in the state had frac sand mines, and their roads were therefore affected by sand trucking. Please note the number of truck trips and costs are hypothetical. They were created for educational purposes, and are not completely accurate of the real world. Even though costs are hypothetical, the results are still meaningful because network analysis shows the frequency the roads are traveled by sand trucks and the resulting cost the trucks incur due to the frequency. 

Figure 3: The map shows the locations of mines, rail terminals, and quickest routes between mines and rail terminals. Seventeen counties had mines in them, and the darker-colored counties incurred larger damages due to trucking sand.

Figure 4: The table shows distance sand trucks travel per county and the cost incurred due to those trucks by county. The number of truck routes and costs are hypothetical, but still meaningful since they are based off real frac sand mine and rail terminal locations.

Figure 5: The bar graph shows which counties incurred the most damage due to trucking sand. Thirteen counties sustained less than $200 in damages per year, while only one county sustained more than $400 in damages per year.

Discussion:

The map in Figure 3, the table in Figures 4, and the bar chart in Figure 5 show the trucking impact is greatest in Chippewa, Barron, and Eau Claire Counties. This is because there are 12 mines in these 3 counties and only 2 rail terminals. Mines in these counties must then truck their sand long distances to the nearest rail terminal. The total mileage trucks hypothetically covered per year in each county were: Chippewa County 278.72 miles, Barron County 169.24 miles, and Eau Claire County 154.84 miles.

Trempealeau County had 10 frac sand mines, which was the greatest number of mines per county in Wisconsin (Figure 3). Surprisingly, Trempealeau County only incurred $180.82 in damages, which made it the fifth (out of 17) most expensive county in the state based on damages due to trucking sand. One reason to explain why the county incurred such low costs is because the average distance between frac sand mines and rail terminals was only 1.5 miles. The maximum distance traveled was only 11.5 miles in Trempealeau County. In comparison, Chippewa County had an average distance between mines and rail terminals of 4.6 miles and a maximum distance of 7.9 miles. This meant that sand trucks traveled three times more miles in Chippewa County than in Trempealeau County per year, which resulted in Chippewa County paying 3.4 times more money for road damages than Trempealeau County. These results demonstrate Trempealeau County would be a good county to have frac sand mines operating in.

Conclusion:

Overall, this lab helped me develop a lot of spatial and GIS skills. I further developed my scripting and model building skills. I also learned how to conduct network analysis in ArcMap, which was very helpful for the class's suitability and risk model for frac sand mining in Wisconsin. The lab gave me a chance to build upon my spatial reasoning skills, which helped me determine why Chippewa County had the highest costs for sand trucking out of all Wisconsin Counties. I made the important discovery that Trempealeau County is a very suitable place for operating frac sand mines because the county's mines are within 1.5 miles, on average, of rail terminals. Overall, the skills gained in this lab will be valuable to the rest of the class project and my career in the geospatial workforce.

References Listed:

Hart, M. V., Adam, T., Schwartz, A. (2013). Transportation Impacts of Frac Sand Mining in the MAFC Region: Chippewa County Case Study. National Center for Freight & Infrastructure Research & Educatoin, White Paper Series: 2013, Retrieved  November 19, 2015 from http://midamericafreight.org/wp-content/uploads/FracSandWhitePaperDRAFT.pdf