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. 

Python Scripting Three: Creating a Feature Class with Search Criteria

The Python script below was created to find the Wisconsin sand mines most likely to deteriorate Wisconsin public roads via the transportation of sand from the mine to the nearest rail depot. The script took all 129 recorded mines in Wisconsin and thinned down the number to 44. These 44 mines only were not classified as a rail depot or and are further than 1.5 kilometers away from a rail line. 

#-------------------------------------------------------------------------------

# Name: EX_7 Mine Selection

# Purpose: To select sand mines based on below criteria for the state

#    of wisconsin

#

# Author:      James S. Erickson

#

# Created:     04/12/2016

#-------------------------------------------------------------------------------

# Criteria for sand mine selection

# 1) The mine must be active.

# 2) The mine must not also have a rail loading station on-site. If the mine is

#       also a rail loading station the sand will be loaded directly onto the

#       rail line and sand will not be trucked to the nearest rail depot ?

#       therefore it will not impact local roads.

# 3) If the mine is within a 1.5 km of the rail it is likely that a spur has been

#       built. The railroad data set is not completely up to date and therefore

#       does not have all rail spurs. Therefore we will eliminate any mines that

#       are within 1.5 km of the railroads.

#-------------------------------------------------------------------------------

#importing arcpy module to the script and setting enviornments

print "starting setup"

import arcpy

#enviornments from arcmap- can set home geodatabase with this amongst all other enviornmental settings

from arcpy import env

#env.workspace = <file path> is the line to set this geodatabase as home

env.workspace = "Q:\StudentCoursework\CHupy\GEOG.337.001.2165\ERICKSJS\EX_7\ex7.gdb"

print "workspace defined"

# able to overwrite.

arcpy.env.overwriteOutput = True

print "Overwrite enabled"

print "Setup Complete"

#defining variables

Mines = "all_mines"

terminals = "rail_termimals"

rails = "rails_wtm"

state = "wi"

# Script created layers below

norails = "mines_norail"

M_type = "type_mines"

active = "active_mines"

norailfinal = "mines_norail_final"

print "Variables defined"

#setting up field delimeters for later SQL statements

#active Mines:

field_AM = arcpy.AddFieldDelimiters(Mines, "Site_Statu")

#mine facility type; used for selecting "mines" and deselecting "rail"

field_FT = arcpy.AddFieldDelimiters(Mines, "Facility_T")

print "Field Delimiters defined"

#SQL statement for selecting acitve mines

activeSQL = field_AM + " = " + "'Active'"

#SQL statement for "mine" in facility type

mineSQL = field_FT + " LIKE " + "'%Mine%'"

#SQL statement for no rails in facility type

norailSQL = "NOT" + field_FT + "LIKE" + "'%Rail%'"

print "SQL statements defined"

#Creating feature layer from SQL statements

# Making feature layer for active mines

arcpy.MakeFeatureLayer_management(Mines, active, activeSQL)

# Making feature layer for mine facility type

arcpy.MakeFeatureLayer_management(active, M_type, mineSQL)

# Making feature layer to exclude Rail in feature type

arcpy.MakeFeatureLayer_management(M_type, norails, norailSQL)

print "Feature layers constructed"

print "Selecting mines"

# removing all mines within 1.5km of a railroad

# selecting all mines in Wisconsin

arcpy.SelectLayerByLocation_management(norails, "COMPLETELY_WITHIN", state)

#removing mines from above selection within 1.5km of railroad

arcpy.SelectLayerByLocation_management(norails, "WITHIN_A_DISTANCE", rails,

    "1.5 KILOMETERS", "REMOVE_FROM_SELECTION")

#printing the number of selected mines

featCount = arcpy.GetCount_management(norails)

print "Number of mines selected: {}".format(featCount)

#Creating new feature class from selected mines/saving selected features

arcpy.CopyFeatures_management(norails, norailfinal)

print "New feature class saved"

print "Script Complete"

Sand Mining in Western Wisconsin: Geocoding Mine Locations

Objective:

This blog is taking a step away from Python coding and back to frac sand mining in Wisconsin. The main purpose of the assignment is to geocode the locations of all sand mines in Wisconsin by using ESRI products such as ArcMap and ArcGIS Online. Once the mine address have been geolocated the results of this project will be compared to the actual locations of the mines and to the results of other individuals doing this same lab.

Methods:

The first step of the project was to normalize a DNR supplied dataset of the location of all mines in Wisconsin. Each student of the class was required to normalize and geocode 16 mines, meaning that each individual mine will be geocoded by at least two people in the class. This means that there will be results for an individual to compare to.

The DNR dataset is in poor shape as its creators did not follow very many conventions of data normalization; the worst offence being multiple attributes located in the same fields. New fields were added and the data was manually sorted out without the aid of any tools. If the dataset had more than 16 mines it would have been more time efficient to use an automated process. 

Figure one. Possible address locations after geocoding function.  

Figure two. Model used to query and 

merge class data into one dataset. 

After the dataset was edited enough to be compatible with ESRI ArcMap it was geocoded via its addresses. As this data set had all but one address present, it almost a simple process to find the correct locations for the points. When an address is geocoded the GIS software attempts to match it with the most correct location that it can. Unfortunately these are estimations and for many addresses there are different probabilities for each point. Figure on is a display of where different address points could exist for this address. The central point is the most true to reality so it was obvious to choose this option as the address location, even if the calculated probability for the other points are higher.

There were several address locations which did not seem to correlate with the location of any mines. It was a tedious, yet achievable, task to find the true locations of the mines with the use of Public Land Survey System (PLSS) data and aerial photography. Google Maps was heavily utilized as well.

Once the points were geocoded it was necessary to compare individual results with the rest of the class. All student results were stored in a shared class folder for all to access. In order to compare results it was necessary to create a new feature class with the use of a SQL statement for each student’s results. All these output files were then combined with the use of a merge tool, see figure two for operation model. This merged feature class was then used in conjunction of the individual geocoding results in ArcMap’s Generate Near Table Tool. A 1 kilometer search area was set for each input mine location and a planar search distance method was used.

Another aspect of accuracy assessment used for this lab was the comparison of individual geocoding results to those of the actual geographic coordinates of the mines. A feature class was created using the newly supplied coordinates for mine locations. This features class was used in the Generate Near Table tool in the same fashion (see tables one and two in results). 

Results:

Map one. Individually geocoded mine locations. 

Table one. Source data for geocoding project. 

Note how the address field is populated with multiple attributes. 

Table two. Source data after some normalization. Note the additional PLSS field. 

Standardizing the source Excel data table was a simple yet tedious exercise of sorting out all the information in the address column. As the data was compiled in far too few columns (See Table One) a new column was required to rectify the issue and prepare the points for mapping within ESRI ArcMap.

After checking and fine tuning the mine address points their locations can be correlated with the locations of several mines that are visible via aerial photography. Others were not so easy to locate as their locations could not be determined via aerial photographs. As mentioned earlier, imagery from Google maps was used extensively to search out the mine’s locations. 

Table three. Distance comparison between individual and

 class geocoding results. Distance in meters. 

Figure three. Statistics and distribution for distance 

measurements between individual and class geocoding results. 

Table four. Distance comparison between individual geocoding 

results and geographic coordinate locations of mines. 

Figure four. Statistics and distribution for distances between individual 

geocoding results and geographic coordinate locations of mines.

Discussions:

From the results section it is easy to see that there are many differences between individual, class, and actual mine location results. While the majority of the individual points were within 200 meters of both class points and actual geographic coordinates for each mine, there were several issues regarding class point outliers reaching 4-8 kilometers from the individual point. The individual points and coordinate points were most often closely associated. The minor issues experienced in almost all of the points was because of how the points were mapped: the coordinate points were at the approximate center of the mines, while the individual points were plotted at the entrance of the mine.

Generally the automate plotting of the points from the geocoding process were not as accurate as one would like; this is simply an inherent issue that has to do with how the geocoding program calculated the general address of points. Several other issues had to do with the quality of the source data. These points were initially placed far off from their actual location meaning they had to be manually relocated. This format translation issue more than likely was caused by the poor quality of normalization for the source data used.

After the addresses were digitized and relocated there were still several very minor and hardly mentionable issues regarding projections of several feature classes. This really paled in comparison to the most difficult aspect to overcome: the temporal accuracy of reference images. There were two mines in particular that could not be immediately located. The first was a relatively new mine as ESRI’s ageing aerial photographs of the area did not show it. The other mine was unable to be diagnosed even with the use of aerial photography from both ESRI and Google and after having gotten the coordinate location of the mine. This case is probably because the registration for the mine was recently added to the DNR database but no mining has yet occurred on location. The point designating the address for this mine is more than likely less correct than the other points in the dataset.

Honestly, the only real way to know if the address locations points are correct is to test them by visiting the locations of every mine in person. This is not a cost effective technique so a good alternative would be to use aerial photography (georectified) to associate the points to the mines. In the above exercise this was a paramount technique to ensure the accuracy of the mines locations.

Conclusions:

Have geocoded and compared 16 mine address with their actual and independently geocoded counter parts it was moderately surprising the amount of variation that was among the chosen locations. When individual results were compared to class results it resulted in most points being quite close to one another. There were a few outliers with distances that were extensive when comparing individual and actual locations. The convention here are that the majority of points were off by a higher amount than class results, but had only a few outliers that were closer to the mean distance than the other comparison.