Network Analysis

Goal

The overall goal of this assignment was for us to perform a network analysis, in this case we are trying to figure out what the total cost and total mileage of certain truck routes that currently exist in Wisconsin. This will allow us to see what impact trucking sand from mines to rail terminals has on the local roads. This entire scenario including the trips and cost of the trips is entirely hypothetical and for practice purposes only. 

Objectives

There were two parts in performing the network analysis, the first part was to create a python script so than we could select all of the mines that are within 1.5 km from a rail road and remove them from the map. Listed below are the objectives for the entire Network Analysis:

1. Set up your script

2. Set up the variables

3. Write Several SQL statements to select the mines based on the above criteria

4. Use the query statement to run the queries

5. Select all mines that are within 1.5 km from a rail road and remove them from your mine

6. Practice with the closest facility solver

7. Build a model to calculate the closest facility route

8. Calculate the cost of sand truck travel on roads by county

Methods

The first step in this process was to set up the script in python, we did this so than we get all of the mines that we are trying to find the routes for below is figure 1 which is a screenshot of the python script that was created for this network analysis. 

Figure 1: Python Script for Mines

Once we had all of the correct mines set up and available for use, we now have to create a model in ArcMap so than we would not only get the routes, but the distance of the routes and the total cost that goes along with them. Below is figure 2 which is an image of the model that was created in model builder which includes all of the tools that was used in this analysis. 

Figure 2: Model for Routes and Cost of Routes

To describe this model we need to start at the beginning, we added three features to start with, the streets, rail only terminals, and the mines that are further than 1.5 km away from the railroads. This is the original data that we will be using, the rail only terminals are the facilities and the mines with no rails near them are the incidents. The next tool was 'solve' this will calculate all of the data that was recently mentioned and create a route for each mine to the closes facility or in this case the rail only terminals. The next tool is 'select data' this allows us to select the data that we want to use or in this case the routes that were just created. Then we go to the 'copy features' and 'project' tool, this will allow us to put the routes in the correct projection so than there aren't any unexpected errors later on in the model. The next tool is the 'tabulate intersection' this is where we added in the counties of Wisconsin and this will clip the routes, facilities, and incidents to the counties. The final few steps is where we start adding fields, in this case we are adding 'Length' and 'Total Cost'. The first calculation field is where we change the distance from meters to miles by dividing the length that we currently have which is in meters by 1609.344 because that is how many meters are in a mile. Then we would multiply that value by 100 in the next calculation field because they make 50 truck trips per year, that's only including one way trip but they do round trips so than 50 turns into 100. The final calculate field tool is where we multiply the distance that is now in miles by the cost per mile which in this case is 2.2 cents per mile. The equation that was used to calculate the total cost for the routes is [Length]*0.22. 

Results

Figure 3: Truck Routes

Figure 4: Total Cost of Routes

As one can see by looking at the figures above some of the mines are using the same routes, this is because they are all going to the same facility because it is the closest facility to that particular mine. Below are figure 5 which represents the table from ArcMap that represents the results from the entire analysis, including the cost and length. 

Figure 5: Final Results Table

The field 'LENGTH' is showing what the distance is for a one way trip, and the field 'Length1' is representing the total distance of a round trip for a year. As you can see Chippewa county has the most expensive route by far compared to the other counties. Below is a figure 6 which is a graph showing the cost of the routes for each county in a different perspective. 

Figure 6: Routes Total Cost per County

Conclusion

After looking at all of the data that was collected we can see that the three most expensive routes are the ones in Eau Claire, Barron, and Chippewa County. This means that they have the longest routes and are driving the most which will lead to higher costs. This assignment was a very good learning experience, not only did we get some experience working with network analyst but we also got to progress our skills with python and using model builder. Once again this assignment is entirely hypothetical both the number of trips and the cost, this assignment was used for practice purposes only. 

Exercise 7: Python Script

The goal of this python script was to create a new feature class based off of already existing features and attributes that were provided. This new feature class needed to fit certain criteria and complete certain objectives at the end. In this part we were expected to prepare the data that was given to us for network analysis by writing our python script so than it would select the mines that are needed for our analysis. 

There was certain criteria that the python script needed to meet, the criteria is listed below;

1. The mine must be active

2. The mine must not also have a rail loading station on-site

3. The mine cannot be within 1.5 kilometers of the railroads. 

There was also five objectives that needed to be met in order for the python script to be correctly written, the objectives are as listed below; 

1. Set up your script

2. Set up the variables

3. Write several SQL statements to select the mines based on the above criteria

4. Use the query statement to run the queries

5. Select all mines that are within 1.5 km from a rail road and remove them from your mines

Once we had all of the criteria and objectives in mind we could know start the script after setting up the connection for the geodatabase where we will find all of the data that we need to use for this project. Then we created a name for each of the variables that we needed to use in the script so than it would be more easily read by the program. The next step was to set up SQL statements so than we would be able to select the mines that are both being active and that do not have a railroad on site. Then we needed to select the mines that were not within 1.5 km of a railroad. After writing the script and running it, my final output came out to 44 mines that were not within 1.5 km of a railroad. Once everything was complete, we opened up ArcMap and looked at the new feature class that was just located. After viewing the new feature class and looking at all of the data that was present it was clear that the data appeared correct and accurate. Below is figure 1 which is an image of the script that was created for this project. 

Figure 1: Python Script 

Data Normalization, Geocoding, and Error Assessment

Goal

The goal of this assignment is to geocode locations of known sand mines located through out Wisconsin and compare them to both our classmates geocoded mine locations but the actual mine locations as well. 

Objectives

In this assignment there were six main objectives that needed to be completed, the objectives are listed below:

1. Normalize the mines MS Excel table
2. Connect to the geocoding service from Esri and geocode your mines
3. Connect to the department ArcGIS server and add the public Land Survey System (PLSS) feature class
4. Manually locate all mines that have a PLSS location
5. Compare your results with the results of your colleagues in class
6. Write a technical report

Methods

In order to complete this assignment the first step was to figure out which sixteen mines located in Wisconsin were my mines to geocode. For each of the mines that was assigned to me I had to normalize all of the information that was listed. To normalize each of the mines I would need to separate all of the data that was originally listed into different columns in an excel spreadsheet. This will help organize the data so than once I need to geocode the mines it will be easier to find the closest mine to the location that will be given to me. The data would be normalized into an extra eight columns for each of the sixteen mines.We would normalize the data by separating out the facility address field. Once that step was completed the next step was to add the table to ArcMap and start geocoding the mines that were assigned to us. In order to start geocoding we need to open up the address locator program that is available in ArcMap, This is where we look to see which mines have a PLSS location or a street address, if it has a PLSS location only then we are going to geocode those locations later in the assignment. The mine locations that have a street address are the first to geocode we find the data point that is represented on the map from the table that we added to ArcMap earlier. For us to begin geocoding we need to use this option in address locator named "Pick Address from Map" this will help us geocode the locations with a street address much easier. All of the initial data points are not located at the mine locations so we need to search for mines that are closest to the initial point or we can use google maps to help locate the mines. The next step was for us to manually locate the mines that just have a PLSS locations listed, in order to better locate the mines that just have a PLSS location we need to add a shapefile named "plss_qq_sects" this shapefile represents all of the different sections that are in Wisconsin. To easily find the PLSS location, it would most likely be placed in the middle of each section. Once we had all of the mines geocoded we would then compare our locations to the actual mine locations, and to our colleagues mines as well to see how accurate and precise our locations were. 

Results

Below are figures 1 and 2, figure 1 is representing four of the mines that needed to be normalized and geocoded. Figure 1 below is the excel file of the mines when they are not normalized. 

Figure 1: Excel file of Mines that are not normalized

Figure 2 represents the same exact mines as  figure 1 but instead it is showing how the mines have been normalized in an excel file. 

Figure 2: Excel file of Mines that are normalized

As you can see some of the mines don't have a PLSS location, while others may only have a PLSS location and some may have both. The next step was to create a map comparing both the real mines to the mines that I geocoded, and another map that is comparing my mines to my classmates who geocoded the same mines, so than we can compare to see how accurate each one of us were. Below are figures 3 and 4, figure 3 is representing the mines that I geocoded to the mines of my colleagues. 

Figure 3: Class Geocoded Mines compared to Individual Geocoded Mines

As you can see by looking at the figure above one can see that most of the mines that were geocoded by the class and the individual are mostly in the same area except for a few outliers that are located more towards the southern area of Wisconsin. Figure 4 represents the mean, standard deviation, maximum and minimum of all the distances from the individual geocoded mines to the class geocoded mines. 

Figure 4: Statistical Information of distance from Individual Geocoded Mines to Class Geocoded Mines

As you can see from the diagram above there were some mines that were extremely close to one another and others that were very far apart. The closest was 78 m and the farthest was 149,244 m but that was probably a different mine that wasn't supposed to be geocoded. Figure 5 is the table of the actual distance for each of the mines that were geocoded. 

Figure 5: Distance from Class Geocoded Mines

The figure above shows the distance between each of the mines, the distance is recorded in meters and is comparing every mine that was individually geocoded and every mine that was geocoded by my colleagues. Figure 6 is a map representing the individual geocoded mines compared to the actual mine locations in Wisconsin.

Figure 6: Real Mines compared to Individual Geocoded Mines

The map above is showing how close the individual geocoded mines were to the actual mines, as you can see most of the mines are in the general area, while others are much further away than expected. Figure 7 and 8 are below, figure 7 represents the statistical information for the distance between the real mines and the individual geocoded mines. While figure 8 is the actual table of the information listed in figure 7.

Figure 7: Statistical Information of distance from Individual Geocoded Mines to the Actual Mine Locations

Figure 8: Distance from Actual Mine Locations

The two figures above represent basically the same information but just show it in different ways, figure 7 shows what the mean/average distance was from the individual geocoded mines to the actual mines which came out to 22,572 m. While figure 8 lists all of the distances for each of the individual geocoded mines to the actual mine locations and lists the distance between the two in meters. 

Discussion

The next step is to discuss why the distance was so great between the individual geocoded mines to both the class's geocoded mines and the actual locations. The reason why there was such a large distance between some of the mines is because of three different things; accuracy, precision, and errors. All three of these things go hand in hand, for example if one is to have high accuracy data or create high accuracy data than it should be free of errors. But in this case we were trying to geocode the locations of these mines by just looking for the closest mines to the data point that were given to us. In other words we were not creating high accuracy data because we didn't have the exact locations of each mine and we were just trying to find the closest mine to the locations that were provided. The errors that most likely occurred during this process of geocoding these mine locations is known as operational errors. These errors are mainly user or processing errors that end up resulting in a imperfection in the data, it mainly occurs during the processing of the geographic data. It would be hard to figure out which points are actually correct and which ones aren't. The main errors that complicated things in this project were that of original source map and data processing and analysis. There were problems in field survey measurements, photogrammetric measurements and image analysis. The reason why these had the most errors is because when geocoding we were analyzing the land around the data points provided surveying for the mines that were closest, that would have caused the most problems when trying to correctly locate the actual mine locations. 

Conclusion

This assignment was the most beneficial assignment yet, because it made us work by ourselves and get a taste of what it is like not to follow step by step directions. Overall it was a great learning experience and gave us some hands on work for geocoding different locations and making our own data points. After completing this assignment I found out how important geocoding is when working on GIS and how important accurate data is and how easy it is not to create accurate data and create errors instead.