GIS II Post 4: Network Analysis, Closest Facility Routes for Sand Mining in WI

Goal:

Learn Network analysis and the various solvers within the tool.

Objectives:

The objective for this exercise is to calculate the impact Frac Sand mining has on Wisconsin's roads. Since the primary method of shipping is by dump truck to rail road, the analysis will focus on trips from the sand mine to the rail road loading sites, or "rail terminals."
Outline

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. Claculate the cost of sand truck travel on roads by county

Map 1 Locator map of the Counties in Wisconsin which contain
a Frac Sand Mine and/or Railroad Terminal.

Data Sets:

Railroad Terminals - Point feature class
North America Street Network USA Dataset - Network dataset created by ESRI.
Geocoded Sand Mines - Point feature class, sand mines found and cross checked by GEOG 339 students, 120 total WI mines.


Methods:

TOOLS USED:
Add Locations
Make Closest Facility Layer
Solve
Copy Features
Select Data
Project
Intersect
Summarize
Add Field
Calculate Field

The main tool used during the project was Model Builder within ArcMap. Using the search tool to quickly navigate the ArcToolbox, it was easy to find the tools needed to accomplish the various tasks. Once all the feature classes were created, all that remained was to calculate the total miles each of each trucking route per county; and, ultimately calculate the cost each county would pay for maintenance of roads per year. Model 1 shows the process of adding the various feature classes (in turquoise):

050_000 = Shapefile of Wisconsin
streets = ESRI's Street Network
Mines_AOI = Frac Sand Mines in West Central Wisconsin
Terminals_LoadingSites = Point feature class of Rail terminals & Railroad Loading Sites

Model 1 Model of Closest Facility solver with the end output table showing
cost of trucking Frac Sand across each countys' roads



 

 

To Calculate the Trucking Cost Per County (dollars) / Year

Several assumptions were necessary:

1. Each sand mine would use 50 trucks per year.
2. Each truck would travel from the sand mine to the rail terminal AND back.
3. The cost per truck mile is 2.2 cents.

Final Equation:

Trucking Cost = (SUM_Shape_Length x 0.000621371 x 0.022) x 100

Explaining the variables and numbers:
SUM_Shape_Length : was the total distance of all the routes in that county (map units = meters)
Conversion factor :  1 meter = 0.000621371 miles
Cost of each Truck / mile : 2.2 cents
Number of trucks / year, traveling to and from rail terminal : 100

Results:

The results were pretty straight forward. Certain roads were more highly traveled than others due to the way the road network was layed out. Unfortunately I did not record what the impedance was set to for the routing. However, I can make an informed decision based on the information in the output table. I assume, with confidence, that distance was the cost being calculated and regulating which routes were ideal. Map 2 shows the routes taken (red) from each sand mine (turquoise circles) to its respective rail terminal (mustard R&R circles).

Map 2 Map of West-Central Wisconsin illustrating the sand mines (not an exhaustive
representation) in the region as well as their respective rail terminals and
the roads the trucks traversed to make it there.

After a visual analysis was performed. A more accurate measurement per county was applicable. As Graph 1 shows, for the sand mines and rail terminals included in this study, La Crosse county had the greatest expenditure necessary to maintain it's roads.

The distance traveled in miles per county was also measured, but I found it redundant to include another graph. Instead, Table 1 shows the output statistics created by Model 1 previously shown at the top of the page.

Table 1 Shows the three statistics calculated for this exercise.

Some nnoteworthy observations from Table 1deal with the leaders for each trucking statistic. 1) La Crosse County has the greatest number of total miles travel and road maintenance costs, yet only ranks 4th among Wisconsin counties for the number of trucks that travel its roads each year. The leader for number of trucks transporting frac sand each year is dominated by Trempealeau County, a surprising 150% more trucks travel its roads based on our model.

Discussion:
Although this was a fun and interesting project, it wasn't without its flaws. First of the impact on the roads was calculated to be constant throughout the travels. In reality the trucks weigh much more on the destination trip with a full bed load of sand than they do coming back. Next, the method assumed that the truck would travel to the rail terminal AND back. I imagine that the trucks do not religiously come back to the sand mine after every delivery.  

Conclusions:

The focus of this project was to have a crash course in Network Analysis and by the end feel comfortable with the general procedure. Along the way more routine tasks had to be repeated which helped reinforce the important steps in the process; such as, adding a new solver (i.e. Closest Facility layer, or new route). Continued projects within the Network Analyst are sure to occur given the expanding field of GIS and the amount of applications and power of Network Analysis.

Sources:

ESRI's Street Network
W:\geog\ESRI\streetmap_na\data\streets