Exercise 7 Network Analysis

Background:

In exercise seven we where running a continuation to the original topic we had discussed in exercise five when we looked at the frac sand mining in Wisconsin. Our initial objective for the lab was to locate all of the mines recognized by the WDNR and analyze the overall impact the trucking had on the county roads. Before we could do this we needed to gain some background knowledge to the situation. To obtain this synopsis we where given a recent study conducted that was very similar to our project but specifically located in the Chippewa county of Wisconsin. The study was run by Maria Hart et. al. through the university of Wisconsin Madison. They wanted to find the impact on the transportation roads the trucking had and if the counties would be able to afford the turmoil the constant running of sand would cause. They found that the entire process itself is very demanding on roads for the process requires a lot of water, sand, chemicals, and equipment. Also since most of the mines are located in farm land and generally away from local towns it requires lots of trucking to mine locations. In one of the papers that they had found a conservative estimate of amount of trips could be upwards of 2700 times for one mine fracturing. and if the mine has different oil it can lead to multiple mine fractures (White paper). In total they found that both in bound and out bound truck tons would equate in Chippewa county alone to be about 2.2 million tons. Considering this it is easy to see the implications that amount of weight on the same road over and over could cause a lot of damage. Since the trucks are always taking the shortest route to the rail terminal to off load the product.

Although most of the local governments for Chippewa county and other counties throughout the state have conducted studies to find the most suitable route for the trucks to travel based off of shortest route optimal pavement conditions and other factors the notion that maintenance solely relies on the counties is appalling. Take for example Chippewa county, which has a median income of only 26,000 they are now going to be asked to raise their own taxes to upkeep and repair the roads that is only making profit for most companies that are not even locally based. Income that they may not ever see once it is collected. I hope that through more studies and hypothetical situations as the one we have conducted government agencies will see that they need to step in and regulate and aid these small communities to protect not only their road systems but also there environments as well. 

Methods:

For our own hypothetical exercise we now had the necessary information needed to understand what analysis we where looking for, but our next task was to run the process. We already had our previously placed geocoded mines and where now given the actual mine location but we needed to query out some specific aspects of these mines and mine terminals to run our transportation analysis. Starting we needed to build a python script which could be used to query out and make the feature classes that selected only active mines, all mines that only had mine in type and to remove all mines that where within 1.5 km of a rail terminal. The full script can be found within my stand alone blog post of all python scripts made over the semester. Once the script had successfully run we where able to utilize these newly created feature classes for our network analysis. 

The analysis would be run using ArcMap by placing the mine final mine locations which consisted of all active mines that where fully labeled as mines only and not within 1.5 km of a rail line. This feature class would act as out location in our analysis. We then needed to place all rail terminals in our map and use them as our facilities. However, we only needed the terminals that where rail and rail compatible so we needed to manually query out all other terminals like air and make a separate feature class. Once here we where now ready to run the analysis for the nearest m terminal from each mine. The outcome would be our route needed in our calculations. 

From here we needed to build a model to run our feature classes and calculate our analysis. We built a model that would be able to run all of the features through the analysis and would calculate our own new fields which would show our total mileage of roads travel per county and cost per county. The model (figure 1.1) has a couple of logistics that are worth noting are that in order to calculate mileage and cost we needed to project the map into a useful units. I had chose to use Wisconsin state coordinate system which used feet as the linear units.Next we needed to summarize the counties based on length to determine the total feet traveled in each county. 

The last steps where very quick but essential for figuring out how much each county would hypothetically need to pay in to maintain their roads. We needed to add two fields to our summarized table the first being total miles traveled hypothetically saying each truck would travel 100 round trip times in a year. The last was a cost per county field which multiplied the amount traveled by .022 which was the hypothetical amount said it would cost per mile traveled. 

Figure 1.1: Model used to generate final table showing cost in US dollars each county would need to pay to maintain there roads. 

The final table that was generated from the map and then displayed within my results is as follows.

Final table generated form the model that shows the total length of road traveled in miles and cost per county in US dollars.

Results:

The results of the study where very intriguing for I would have imagined that the mines would have effected more counties within Wisconsin after visually seeing the extent that the frac sand is located in the state. The results that I had generated although completely hypothetical showed that the two counties most greatly effected by trucking of material for mining where Chippewa county and Wood county. Both would need more than 300 dollars per year greatly differing from the other counties of Wisconsin. The graph below (figure 1.2) shows the full results in form of dollar values. One aspect that needs to be considered when looking at these results where the routes of the trucking. As figure 1.3 shows in some cases the mines needed to truck a long distance and even in some cases across counties boundaries to reach a rail terminal. It is this fact that in my opinion leads to many issues within governmental cost, for how do counties determine how needs to cover the cost of the destruction if the mine is in one county but the terminal unfortunately is in another. This hopefully can be solved peacefully or can eventually be subsidized by federal agencies. 

Figure 1.2: showing the total cost per each county effected by trucking of frac sand in Wisconsin.

Figure 1.3: shows a graduated scale of the counties cost and the routes taken by the trucking throughout the state of Wisconsin. All numbers for cost are hypothetical and do not represent true values.  

Conclusion:

In the end although the numbers where completely hypothetical this project was very eye opening to a real life problem that is occurring as we are running these tests. This lab was very helpful to not only gain a better understanding of the programs Arc offers but to also see how scientist are utilizing these exact methods in the field to solve a real world issue. After completing the exercise I now have gained a better understanding for some of the programs but also have a deeper compassion for searching for solutions to such issues that effect me as a Wisconsinite. It is issues like these that will directly effect my taxes and infrastructures within this beautiful state I love. I only can hope that soon more people will run similar test with true data to display to multiple federal agencies and finally gain support for maintenance of our important roadways. 

sources:

ESRI geodataset

University of Wisconsin Eau Claire Geography Department

National Center for Freight and Infrastructure research and education White Paper : Maria Hart et al. UW Madison

Raster Maps created from Pythin script

After completing the Python script we where then able to utilize our three DEM's that have been produced. The maps where representations of three different raster's including, elevation, vegetation, and boundary data. (figure 1.1). We also where tasked with finding and displaying metadata form each of the sources that we collected the original zip file. With the metadata we where to place them in a table to show lineage, scale, and accuracy of the data and attributes. (figure 1.2)

Figure 1.1: shows the county boundaries in Wisconsin highlighting Trempealeau county in red and the three clipped raster's.

Metadata from the sources of all maps collected

Exercise 6: Geocoding and normalization of data

Goal:

The goal of this project was to illustrate to our class the difficulties that can arise when trying to normalize data that is collaborated form outside sources. It was also designed to show how challenging geocoding can be depending on the information provided in the data set. For the assignment we where tasked to normalize a set of mine addresses given to us through a Wisconsin DNR dataset. The original dataset had come to us in a way where we needed to extract key features from the dataset like the mine unique id field, the mine name, and any information given about the address. In some cases it was a street address other times it was only stated using the PLSS form of surveying. In either case we found that we needed to put a lot of effort into keeping consistent extraction into our own designed tables (figure 1.1 will show the normalized table). Once normalized we could then create a shape file which could later be used to geocode to the actual location. Finally we then needed to compare our results to our classmates to see how much variation occurred.

Methods:

To start the assignment we first needed to normalize the data. For most of us we decided to use a similar format as the one shown to us in the demonstration by Dr. Hupy. We where also advised to create a separate column for the PLSS address making it easier to find and be able to portray that information if needed. We then needed to manually go through each unique mine id and find all of the mines that had been assigned to us with a code. Once acquired we just needed to type in all information provided to our table and complete the table. The final product is shown below.

Figure 1.1: normalized table of the 21 mine sites given to me to geocode from the WDNR table

After we then could extract our table into Arcmap to produce a shape file. With this shape file now imported into Arcmap we then needed to geocode all of the addresses. To accomplish this we first needed to open our geocoding toolbar. From there we could process our table. With the original process I received 14 matches one tie and 6 unmatched. Now although this seems like a good start I was quick to find out this was not the case. From here we needed to use a lot of mental skill and tools to find the actual mine. To start I first went to all of the mines that did not match and needed to place a location of the mine. I used the PLSS finder and a imagery base map to find the locations I thought where the mine sites. To accomplish this I would enter all of the PLSS information into the database and then get an idea of where the location may be. After by overlaying the PLSS quarter quarter sections onto my map over the imagery I was able to get within the area of the mine and visually look for the location to match an address. Although this only solved the issues for the unmatched mines. I then went back through all of the mines to make sure the where properly placed. I found that 15 of my 21 mines where not in the right location and needed to be manually moved in the same process.

After the geocoding was completed I then could extract my properly placed points into a shared file for all of our classmates to use.

For the next objective we where then asked to compare our results to two things. We where asked to find the variation between groups to see how much our mine sites varied and we where also to compare how far off our personally placed mines where to the actual mines. I had found the distance variation between groups and between my own mines and the actual using a couple of steps. First I needed to query out all mines that where not applicable to the situation and queried all mines that had the same mine id as my own mines. With the new selected feature class I was able to then project both sets into UTM to make sure the units where in meters and run a near tool to find the distance from each map to the nearest mine between groups and mine in one data frame and between the actual site and my own in another data frame.

Results:

After running the queries and the near tools the results where shocking. I had found that only a few of my mine location had been close to my classmates groups by distance of meters. However, majority of them where significantly off. This was perplexing to me since we where all given the same datset and yet we where that far off in our findings. For the second near table I was a little closer I had majority of my mines near the actual mine, however, there was about 7 mines that I was not even within the right ball park of correct location. I am not sure as to why this may be the case, might have to do with outdated bsaemap or with improper data normalizing. Either way the results are shown below in figure 1.2-1.4.




Figure 1.2: location of my maps in comparison to the other three classmates with the same location geocoded.

Figure 1.3: near table tool showing the closest mine synced with distance away in meters

Figure 1.3: comparison of actual miens to my personally geocoded mines

Figure 1.4: displaying the distance from my mine locations to the actual mine in meters.



 Discussion/Conclusion:

This lab was very helpful to my skills in GIS I was bale to witness first hand the complications that can arise from improperly entered data, and how normalization of a dataset can be key to display of data and geocoding in future applications. Since you are not always given consistent data or data that is complete this lab is a great representation of real world issues that can arise in a job, field work. It developed and introduced all of us to the skills we will need and the mind set we need to have when setting up data collection to save from future headaches of normalizing.
There was a few errors that had arises throughout this activity that are worth noting. First was the inherent error that is very relevant to this lab. If the person who had collected the data would have used more domains and subtypes along with creating a few more columns for some of the information the data would not have needed to be normalized to the extent that it was by us. Also With the class being trained through a demo on how to normalize and some tips on how to arrange the data you can say there was some gross error involved later on when some groups didn't have a mine unique value and the data needed to be manipulated to be compared in the near tool.

I would have to say if we where asked to achieve a hundred percent accuracy with this data and be hundred percent consistent between our classmates placement we would have needed to communicate and agree on a standard way of normalization of data along with acquiring a more consistent and completed data set that would have provided better location for the actual mine site.

Altogether the activity was very useful and helpful in understanding all of the complications that can arise from data normalization and inherent error. I also enjoyed being able to work with other classmates to see how each person can have a different view on the same task. I always appreciate seeing the work of fellow peers for it allows me to asses my own efforts and skills and see what I need to improve on and what I can capitalize on and help others improve in their own skills.

Python Scripts

Script 1:

For the first python script that we ran we where tasked to design a script to clip our Trempealeau county Wisconsin area with three different raster models. The script that we where to design was made by importing arcpy and all environments into PyScripter for Python 3.2. After the environments where in we then needed to establish a list of all the three TIF raster's that we where using. Once we have produced a list of raster's we then needed to create a for loop for the raster's so all three of the raster's we had downloaded , including the DEM from USGS, the NLCD, and the NASS, it was these three raster's that we wanted to run through our clip. By creating this for loop we then could write a code for the raster's to run through a projection getting all of them to be projected into the same coordinate system which was NAD 1983 HARN Wis Trempealeau county. By projecting all three raster's in this projection it allows for minimal distortion and can display all mines in the appropriate location. Once they where in the proper projection we then could run a clip for the raster to only give us a print out of the county we were interested in, last we then extracted the new clipped map to our geo-database and could use the new maps to display our mining areas. The full script that was created is in figure 1.1.


Figure 1.1: Python script for exercise five, script is a representation of how to use a list and for loop to run a clip function on Trempealeau county Wisconsin

Python Script II (selecting active mines for analysis):

The second python script we where tasked to design was a script for selecting and narrowing our total mines in Wisconsin. The script was aimed to select out all mines that where a)active mines (currently in use), b) where all mines that where completely a mine ( not a processing plant or loading station), and c) all mines that where not adjacent to a rail line. Last we needed to write a code that excluded all of the mines that could be found within one and a half kilometers of a rail line. By querying out these three selection we where able to make a complete feature class that would later be used in our data analysis for calculating the total impact on the county road systems that the truckers from the mine to the rail station where causing.

Figure 1.1: displays the python script used to query out all mines needed in exercise 7