Sand Mining in Western Wisconsin: Sand Trucking Costs/Network Analysis

Introduction

The feature class containing mine locations that do not have easy access to rail depots that was created with python served as a starting point for the network analysis project that this blog entails. ArcMap’s model making feature was used to combine a plethora of tools, feature classes, and a street network to calculate the distance a sand laden truck would have to travel from an isolated sand mine to the nearest rail depot. This distance measurement was then used to calculate the hypothetical maintenance cost that each Wisconsin county would have to pay on their public roads from this sand shipping traffic.

Methods

Table one. Rail terminals feature class. Airplanes and semi-trucks are not the most efficient ways to transport sand (MODE_TYPE field). Terminals like this were removed from the feature class. 

The first step towards completing a network analysis for this project was the preparation of data for use in ESRI’s network analysis tool set. There were two important step for this. One: the output feature class from the previous Python blog containing the location of all Wisconsin mines without railroad access had to have the address data field removed. The addresses are no longer need and they interfere with the network analysis toolset. Two: features from the original rail terminal data set had to be removed, as several of the terminals listed were for semi-truck and airplane depots. Train shipping is a much more efficient way to move sand from place to place.  

All processing of data from this point was completed with the use of ESRI ArcMap’s model building feature. Individual processing tools were strung together in the creation of this model. Using a model to do data processing has several advantages over running tools individually. First off, one can see the data processing flow in real time which allows for easy trouble shooting in the case of an error. Secondly, if a change is made to an early process in the model the resulting output will be applied to all later tools. This saves a tremendous amount of time compared to running a sequence of tools over again. And finally, the completed model is very useful to reference as it is literally a record of how the data has been manipulated.  

Figure one. ESRI model builder showing the data processes for this network analysis project. 

There were three major feature classes and one layer file used for this the project:

1. Mines_norail_final: this is the feature class from the previous Python script blog. It contains all mines in Wisconsin which likely do not have access to a rail line on location. This data originally came from the Wisconsin DNR.
2. Rail_terminals_final: this feature class contains all major transport depots across the United States. Originally from ESRI this data contained not only rail depots but trucking, shipping, and airports as well. As this project explored only rails this feature class needed to be edited prior to use, see above paragraphs for details.
3. WI_Counties: this feature class came from the United States Census Bureau’s website www.americanfactfinder.com.  Originally, it downloaded as a shape file but a simple export process was used to add to a geodatabase as a feature class for use in this project.
4. Streets: this layer file came directly from ESRI. It is comprised of the road network across the United States. ESRI doesn’t’ really specialize in navigation so this street network is not as polished as street networks offered by more specialized companies. For the purposes of this project it will work well. 

Figure two. Results of network analysis closest facility tool. Note the lines leading from the square mine features to the circular depot features. 

The first step to building the tool process model (Figure one) for this project was to use the street network from ESRI as the base file for the following network analysis processes. A “make closest facility layer” tool was used in conjunction with two different “add locations” tools to create a network analysis route layer (Figure Two). In order to answer the research question this route layer was selected and copied as a new feature class (figure one “sand_routes”). It was then intersected with WI_Counties. This created the SR_intersect feature class which is essentially a line feature class divided by county borders, each line segment now has the county name associated with it.

Figure three. Route segment distance measurement. Note that all three selected features have the same shape length of approximately 11,600 meters. 

SR_Intersect was also saved in a new feature dataset that was defined to have a Wisconsin nad83 projection with meter distance measurements, meaning that all measurements of distance from any feature class placed in this feature dataset will be displayed in meters, a decidedly huge advantage over the original spatial reference system where measurements were in decimal degrees.

The final two lines of the model pertained to the creation, and modification of a table which contains all route distance and cost per county information. Two new fields were added and populated with statements that converted the meter measurements per county miles and calculated the cost per mile, 2.2 cents, for fifty trucks leaving and returning to each mine. The cost per mile and number of embarked truck are purely hypothetical numbers for this exersize. The final step to the model is the “table to excel” tool, which, as the name suggest, converts the ESRI dBase table created from earlier steps directly to an excel spreadsheet. 

Results and discussion

Graph one. Total number of miles round trip for one truck from each mine combined for each county. 

Graph two. Estimated (hypothetical) cost of road maintenance for each county if each mine dispatched fifty trucks. 

Map one. Sand trucking costs for Wisconsin counties. 

County Name	Miles One Truck Covers Both Ways	Cost For 50 Truck Trips
Barron	338.49	$372.34
Buffalo	41.01	45.11
Burnett	2.55	2.80
Chippewa	557.45	613.20
Clark	90.50	99.55
Dunn	139.30	153.23
Eau Claire	309.68	340.65
Jackson	152.12	167.33
La Crosse	37.90	41.69
Monroe	66.58	73.23
Outagamie	39.18	43.10
Pepin	12.24	13.47
Pierce	87.08	95.78
Saint Croix	3.86	4.25
Trempealeau	164.38	180.82
Winnebago	1.92	2.11
Wood	215.88	237.47

Table two. Results for single round trip distance in miles and estimated county cost for fifty trucks. 

From the above results it is relatively easy to see that Chippewa County has the most sand truck traffic, meaning that the cost to public roads is the highest of all counties in the state. The same convention applies to the counties with the fewest traveled miles, they have the lowest cost. It is important to point out that these low traffic counties mostly have sand mines just within their borders and the actually truck route goes almost exclusively through neighboring counties. The best example of this would be Burnett County on the Northwestern edge of the state. It has one mine within three road miles of the Wisconsin and Minnesota border. The rest of the distance the tuck travels is across Minnesotan counties, which more than likely receives little to no taxes from said mining company.

Conclusions

Combing feature classes from earlier exercises with ESRI street network layer yielded an analysis of truck traffic maintenance costs on Wisconsin roads. This was done stinging together a wide range of data manipulation tools in ArcMap’s model builder to create a spread sheet of costs. Of all the counties Chippewa had the most traveled miles, meaning that the maintenance costs were also the highest. Other counties, such as Burnett and Winnebago, had compatibly low costs due to how close to the county’s borders the mines or rail depots are.