Network Analysis of Frac Sand Mines in Wisconsin

Goal and Objectives

The goal of this assignment is to perform a network analysis on the transportation of sand from the mines to the nearest railroad depot via trucks. The trucks will have a substantial impact on the condition of local roads, which are maintained by the county.

This assignment is divided into the following six objectives:

1. Load features into the Network Analysis Window
2. Calculate a route
3. Calculate a closest facility and route
4. Build a model to calculate the closest facility route
5. Calculate the cost of sand truck travel on roads by county
6. Write a report based on the results (this blog is the report)

Data sets and Sources of Data

All data used in this analysis was obtained from ESRI, with the exception of mine locations. The mines were obtained from Wisconsinwatch.org, and the addresses were geocoded for this network analysis. A complete data set of railroad terminals could not be obtained due to many of them being private.

Methods

Objective 1: Load Features into Network Analysis Window

The first objective of this assignment was very straightforward. All that was required was to connect to a folder and add a streets network data set. Once that was completed we enabled the Network Analyst Extension of ArcGIS and imported the rail_terminals feature class that the professor provided for us. The final part of this objective was to add a combined mines feature class of all of the mines our group geocoded in the previous assignment. This proved to be more difficult than expected for me due to one group member dropping the class the first week, and then while waiting for the only other group member to import his feature class, I found out that he was dropping the class as well. As a result, I was unable to obtain the necessary mines, and so the professor provided me with another groups compiled feature class of mines (Figure 1).

Figure 1: Screenshot of the compiled mines feature class.  

Objective 2: Calculate a Route

The second objective was quite simple as well. All I needed to do was open the Network Analysis window and right click on stops to choose my mines feature class as the load location, accepting all other settings as default. Then I clicked the solve button and a route was generated. From here I could have exported the result as a feature class, but this is not useful to goal of the assignment. All this process did was calculate a route that would travel to all of the mines. What is needed is to find the most efficient route from each sand mine to its nearest rail terminal. Once we have that, we can calculate the length of the route by each county and estimate the cost to each county using a formula I create.

Figure 2: Merely a route to all of the mines.  This is not to be used for the actual analysis,
but rather was intended for us to see the result and learn how to use the tool.

Objective 3: Calculate a Closest Facility and Route

To begin Objective three I removed the route that was generated in the second objective, as it had no value and was cluttering the map. The purpose of this objective is the calculate a closest facility and route, which will allow us to find the closest railroad depot to each sand mine, as well as find the shortest route in mileage. To begin this process, I added a new closest facility layer from the Network Analysis toolbar. I loaded the mines as the facilities and the rail terminals as the incidents, and then solved. Upon examining the results, I noticed that this was the opposite route we wanted. This method found the closest sand mine to each rail depot, resulting in mostly completely unnecessary routes and very few connections with most sand mines (Figure 3a).

Figure 3a: This is a network analysis with the mines as the facilities and the rail terminals
as the incidents.  This resulted in a route from a rail depot to the nearest sand mine.  What
I needed were routes from each sand mine to the nearest rail depot.

To get the results I wanted, I had to switch the mines and rail depot, making rail depots the facilities and mines the incidents. Upon solving, I immediately saw this was the correct method, as there were routes going from each sand mine to the nearest rail depot (Figure 3b). To keep the map and table of contents clean, I removed the first calculation.

(Figure 3b: Correct Closest Facility)

Objective 4: Build a Model to Calculate the Closest Facility Route

This objective begins the creation of a model using the Model Builder present in ArcMap. Through this objective, the model will perform the same actions (and have the same results) as Objective 2. In Objective 5, we will expand this model to include calculating the costs incurred on counties from sand trucks traveling to and from the rail terminals.

The following process was used to create the model shown in Figure 4:

1. Add tool: Make Closest Facility Layer (Network Analyst)
   1. Input Streets feature class, leaving all other settings on default
2. Add tool: Add Locations (Network Analyst)
   1. Input Locations: Mines feature class; Set sub layer to incidents; all other settings on default
3. Connect output of Make Closest Facility Layer to Add Locations tool of Step 2, and input network analysis layer
4. Add tool: Add Locations (Network Analyst)
   1. Input Locations: rail_terminals feature class; Set sub layer to facilities; all other settings on default
5. Connect output of Add Locations in Step 2 to Add Locations in Step 4 and input network analysis layer
6. Add tool: Solve (Network Analyst)
   1. Connect output of Add Locations in Step 4 to Solve and input network analysis layer

I then ran this process to ensure it worked, however, the process said it could only partially complete, and it would not display on the map. I initially thought it was only partially completing due to there being 11 mines that were not completely geocoded and did not have matched addresses. To remedy this I started an edit session and looked in the attribute table of the mines. I found the 11 mines that had several fields with (null) values and no matched addresses. I deleted these from the attribute table, saved the edit, and stopped the edit session. This seemed to fix the partial completion, as this time it was fully successful, but it still did not display the results. Being confused by this, I decided to consult the ArcGIS Resource Center. I searched for “model builder not displaying,” and the first entry lead me to a tutorial on how to create tools with ModelBuilder (Tutorial can be found here). From there I found out that the output of a model will not be added to ArcMap unless you have both “Add To Display” and “Model Parameter” checked. This is because when a model is run from its tool dialog box, the “Add To Display” setting is ignored. When I ran the model with both of these checked on the Solve output, it successfully completed the model and added it to the display. The results were the same as found in Objective 3. Next I needed to export this result by adding processes onto the model I created.

Figure 4a: This is the model I created using the ModelBuilder in ArcMap.  This shows the
process explained above.  The 'P' above Closest Facility 2(2) denotes where I
had to select Model Parameter and Add To Display.

The following process was used to export the result in ModelBuilder:

1. Add tool: Select Data (Model only tool)
   1. Go to Insert → Model Only Tools → Select Data
   2. Connect Solve output to Select Data, and Input Data Element
   3. Open select data tool, choose Routes for child data element
2. Add tool: Copy Features (Data Management)
   1. Connect output of Select Data to Copy Features, and Input Features
   2. Open Copy Features, changed output workspace to my geodatabase

The model ran successfully. It created the feature class, but I forgot to add the “Add To Display” and “Model Parameter” to the Copy Features output, so I ran it again with these changes (Figure 4b). On this second run, I realized that it is necessary to have this as a Model Parameter because it allows you to change the output location and file name, which may be necessary if you run the model more than once. This completes Objective four.

Fig 4b: Model with added tools to export the Routes as a feature class. With the Add To Display
and Model Parameter selected on the Routes_final result, it will be added to the map
on completion.

Objective 5: Calculate the Cost of Sand Truck Travel on Roads by County

In order to calculate the cost of sand truck travel on the roads in each county, I needed to add some processes to my model. In my first attempt I merely intersected the routes feature class with a counties map, which resulted in an attribute table containing a Shape_Length field with really small values. After consulting a fellow classmate, I realized this was likely the sum of decimal degrees, as I had not projected both features into the same coordinate system.

My second attempt was successful. I projected both the routes and counties feature classes into NAD_1983_Wisconsin_TM_US_Ft. This is a Wisconsin State System that uses feet. I chose this coordinate system because it provided the smallest area that could be used effectively. A state plane system would be less accurate because it splits the state in half. UTM Zone 15N could have been used, but this is encompasses a much larger area, and so it does not provide as much accuracy as the state system. Wisconsin has two options for the state system, one in meters and the other in feet. I chose the one in feet since it would be the easiest and most accurate to convert feet into miles.

With the projections complete, I then intersected the projected routes (roads) with the projected counties. The intersect tool will provide me with an attribute table that links the truck routes to the county it is in. If a truck route goes into multiple counties, the intersect tool cuts the route at the county border. For example, if a truck route goes through three counties, at each county border the route will be cut, resulting in three line segments that each belong to a specific county. This will allow me to calculate the total length of roads in each county.

The final step to the model involves summarizing the Shape_Length field (which is the route/road length) based on the county. To do this I added the Summary Statistics tool and connected it to the output of the intersect. The output of Summary Statistics is a table, which can be seen in Figure 5a.

Figure 5a: This is the attribute table generated  by using the Summary Statistics tool.  The Name field contains
the name of the county, and the SUM_Shape_Length gives the total length of the routes in feet.

The model is now complete and can be seen in Figure 5b. However, I have still not completed objective five. In order to do so I will need to edit the attribute table generated by the Summary Statistics tool. First, I removed the frequency field, as that was just a count of the number of routes in the county. I also renamed the Name and SUM_Shape_Length to County and Road_Length, respectively. I added a Miles field to the table, and used the field calculator to convert the road length from feet to miles, using the formula depicted in Figure 5c. The final step was adding the Cost field, where I used the field calculator again to determine the cost to each county. The formula can be seen in Figure 5d. The formula used is hypothetical. It assumes that each mine takes 50 truck trips per year to the rail terminal, and then return to the mine. This results in 100 truck trips per year for each mine. The hypothetical cost is 2.2 cents per truck mile. The completed table and the cost to each county in dollars can be seen in Figure 5e.

Figure 5b: This is the completed model, showing the full process from start to the finish.  I reorganized the
model in order to make it more compact, allowing it to be more easily read and edited. 

Figure 5c: I used the Field Calculator on a new field to convert the total length of routes
in each county from feet to miles. The formula can be seen in the bottom box.

Figure 5d: Again, I used the Field Calculator.  This time the formula used will calculate the cost to
each county per year.

Figure 5e: This table was made in Microsoft Excel.  I exported the attribute table in ArcMap as a dBase, used a
statistics program to convert it to an .xlsx file, where I then opened it with Excel.  The Cost field shows
the cost to each county per year.

Results and Discussion

Figure 6: Created in ArcMap, this image contains a graph and chart of the cost to each county.  On the map,
you can see the routes from each mine to the closest railroad depot.  Counties that will bear a
financial burden are colored, with the cost increasing as the shade becomes more red.
The legend does not depict the proper color because I had the layer on transparency,
and I was unable to determine how to alter the legend colors.

As can be seen on the map, the vast majority of railroad terminals are located in the southwest portion of the state, where there are very few sand mines.  This results in most sand mines travelling to one of the three railroad terminals in west central Wisconsin.  When looking at La Crosse county, the southern most county in red, it only has one sand mine, yet it has the fourth highest annual cost due to most of the mines in the surrounding counties travelling to its railroad terminal.  

Our hypothesis assumes that there will only be 50 truckloads per mine each year.  This seems like an extremely conservative estimate, as that is saying each mine will only send about one truck per week.  Even so, it still demonstrates the proportion to which each county will be affected.  This means Chippewa, Eau Claire, Trempealeau, and La Crosse counties will have a significant impact on the deterioration of the roads, requiring more maintenance and potentially earlier and more frequent replacement.  This is certainly an issue to taxpayers within each county, as it is their tax dollars that will be spent in order to maintain and repair the roads.

Conclusions

While this analysis provides a general idea of the potential proportional costs to local communities due to the sand mines, it is neither definitive nor accurate enough to provide the answers people will want. There are many potential options to offset this issue, such as sand mines paying a fair share to each county they will affect, or creating more rail terminals in western Wisconsin to either reduce the number of trucks required, or at least reduce the distance they must travel.  This analysis does not provide the answers, but rather, points out the fact that more research is required in order to determine what the possible solutions are and which is the best course of action.