Exercise 7 - Network Analysis

Introduction - This exercise was focusing on network analysis. We used the example of sand mine locations and finding the fastest route to the closest rail terminal for transportation. We then wanted to estimate the cost that moving all that sand would have on local counties.

Methods - We first wrote a script which can be explained in detail in the python script blog post from October 2014, as well as below. We used this script to select the mines that were farther than 1.5 kilometer from a railroad because those are the mines that we would be trucking their sand to rail terminals.

Python script to select mines farther than 1.5 km away from railroads.

Next we went into ArcGIS and turned on Network Analysis to find the routes between mines and rail terminals. We took a feature class of rail terminals in the country and selected only the ones that used truck and rail. We then took a feature class of street across the country and used Network Analysis to find a route from each mine to the closest terminal. To do this we set the mines as the accidents and rail terminals as the facilities. Once I have the routes as shown in the map in the results section of this blog, I can make a model to find the distance and cost of each route. The model shown below first makes the routes for each mine and then I project the map into 1983 Wisconsin HARN TM (meters) and add the counties feature class. I then use intersect to find the distance of roads traveled by sand-bearing trucks and using summary statistics I find the distance for each county. Next I add and calculate a field to switch the distance from meters to miles. Assuming that each mine makes 50 round trips a year and that each mile costs the county 2.2 cents I add and calculate a field that gives me the cost for each county (100 trips * 2.2 cents * distance traveled in each county). The model is shown below.

Model giving the cost of sand transportation for each county.

Results - The map below shows the locations of the mines in blue circles and rail terminals in green triangles. The routes are the quickest road that trucks can take to go from the mines to the rail terminals.

Map of mine and rail terminal locations with routes

The table below shows each county and the meters and miles traveled by trucks hauling sand and the estimated cost for the county to fix these roads. The cost was found by multiplying the road length in miles by 100 trips and 2.2 cents per mile.

Table of each county with meters and miles traveled by sand trucks and the estimated cost

The table below show the cost for each county with Chippewa County leading by quite a bit.

Discussion & Conclusions - I think that Chippewa County had the most cost due its location in the driftless region that is known for its great sand. Counties that are greatly affected by these trucks and the damage they cause to their roads should think about implementing a tax for these companies to pay in order to use these roads and offset the cost for the counties. However, the cost doesn't seem to high even for counties that are most affected.

Sources
Street data from ESRI streetmap USA
Online Conversion to help with the equation for converting meters to miles.

(See Python Blog Post for Python potion of this exercise.)

Exercise 6 - Data Normalization, Geocoding, & Error Assessment

Introduction - In this exercise we used data from a excel spreadsheet and normalize the data. We then learned how to geocode the data and assess our errors based on our classmates results. The data we were using was addresses of sand mines in Wisconsin for our semester long project on frac sand mining in Wisconsin.

Methods - We first tried to geocode the data using ERSI ArcGIS Online's World Geocode Service. However, since our data wasn't normalized we weren't able geocode any of the addresses. So, we normalized the data by finding the addresses, zip codes, and town for each mine. Many of the sand mines had PLSS addresses so we had to use a school server to find street addresses for those mines that didn't have one. Once we had the data normalized we were able to successfully geocode the address using the World Geocode Service.

Results - Once we geocoded the sand mine locations we were able to map them out in ArcGIS.

The original data before normalization

The data after normalization, ready for geocoding

Map of the geocoded sand mine locations in Wisconsin

Each of us in the class was given about 20 mines to geocode and at the end of the exercise we compared our geocoded mines with those of classmate's who had the same mines. Below is a table of the mines that I geocoded, their name and the distance from my classmate's geocoded location of the same mine.

Mine ID	Facility Name	Distance of my geocoded location from closest classmate's (rounded to the nearest meter)
109	MIDWEST FRAC AND SANDS	0
111	SIOUX CREEK SILICA	0
125	RIVER VALLEY SANDS	14313
136	MUSKIE PROPPANTS	0
138	PREFERRED SANDS	0
151	FG MINERALS (WISCONSIN INDUSTRIAL SAND)	0
152	FG MINERALS LLC	0
163	ATLAS RESIN PROPPANTS, LLC	11383
165	BADGER MINING CORP-TAYLOR PLANT	10603
178	TOWN OF BROCKWAY MINE	0
179	WESTAR PROPPANTS LLC	246
190	BLACK CREEK LIMESTONE CO	3044
192	DIAMOND BLUFF INDUSTRIAL SAND	239
205	ARCARDIA SAND CO	0
206	D95 NORTH SITE - SPARTAN SAND, LLC	1614
218	PATZNER SAND PIT	0
219	PREFERRED SANDS OF WISCONSIN, LLC	2920
232	VERNON BUE SAND MINE	4850
233	FML SAND - READFIELD	3187
245	NORTHERN FRAC SAND LLC	604
246	CHOPPER FARMS	128

Discussion - My data and my classmates' data have low positional accuracy due the uncertainty of certain address were we had rely on PLSS interpretation in order to assign an address to some of the sand mines. There is a inherent error in the data automation and compilation. In some cases there is no knowing which location is correct, however in other cases which show large differences in the data you can see that one or the other point is more accurate based on the PLSS address.

Conclusion - It isn't easy to normalize and geocode addresses especially with a PLSS address instead of a street address.