GIS II-Exercise 7: Network Analysis

Goals of Exercise 7
In Exercise Seven we split the lab up into two different parts. The first part of the lab, the goal was to be able write a Python Script (see blog post below to view python script). After running the script we were left with 41 mines to use for part two.

In part two of the lab, we were to use Network Analysis tools to figure out the distance from the mines to the closest rail terminal, using roadways. Finally, after accumulating the routes, we were to find how much it cost each individual county to have these trucks running on the roads (equation further down on blog).

Data sets we will be using include, Wisconsin County Map, ESRI street map of the United States, and the points we found in the previous part.

Lastly, in order to calculate the cost we used the equation of 50 trucks per year drive down the roads at 2.2 cents per mile. The trucks will also have to make the trip back from the train rail to the sand mine. In order to calculate this I used an equation of 100 trucks times the 2.2 cents per mile.

Methods

The model above shows the step by step process to figure out the routes needed for the mines to get to the closest rail terminal. 

This involved using the streets layer, and the network analysis to find the closest facility. Next we had to add the mines we found in the previous part of the lab and again find the closest facility. Lastly we added the rail terminals and found the closest facility, and solved. Next we selected our data, this would be the routes we made, and copied the features, and thus we could have them saved as the final results.

The above image is the final result of the above blog post, after a select by attribute was applied in order to see how many counties were effected by routes.

From there we would move on to the next part of the exercise, trying to figure out how much money it cost each county.

This model shows the steps taken in order to calculate the total cost per county. It involved creating many new fields, such as miles and kilometers fields (kilometers field was created to make the conversion easier since the data was saved in meters). The last field we calculated was created using the equation mentioned above in order to calculate the final cost. Using the cost data we were able to create the graphs seen below.

After calculating the cost per county and summarizing the data we were able to create a chart of how much it was costing each county to have trucks run on their roads. 

The two images above, top side, shows the table used to calculate the cost and how much it is going to cost to each county, the image on the bottom was a graph made on excel to show the cost in a lowest to highest price. 

CONCLUSION

Overall we were able to see which counties were receiving the most traffic and cost on them. The two highest I found in my data were Chippewa and Wood Counties. While Trempealeau County having the most mines on the map, they were all relatively close to the rail stations.

Exercise 7-Pyscript 2

For this exercise we are using Pyscripter to create a script which will be used to select a certain number of mine locations which meet the following standards;

-Currently Active Mines
-Are not connected to a rail loading station, or is a rail loading station
      *no trucks are needed to get the sands to the trains
-Mines must be more than 1.5 kilometers away from the nearest rail

The above script was created, debugged, and ran to produce a total of 41 mines that mean the above standards.

Below is a map of the selected maps (in yellow) which meet the above standards.

Exercise 6-Data Normalization, Geocoding, and Error Assessment

Goals

The goal of this lab is to use data provided by the DNR to find the exact locations of several different fracking facilities. With the given spreadsheet we are to normalize the data and then plot it on a map and compare it to fellow classmates and see how closely they match up.

Objectives

The objectives for this exercise are to obtain the database of the mines in Western Wisconsin. Next we are to geocode the mines using street addresses. After doing the second objective we will find the data given to us has errors in it and we are then to normalize the table and find the addresses for our selected mines. The next objective is to geocode the mines. After the class has finished geocoding their selected mines we are to compare them with our own to see how closely they match up. Lastly, we are to make a map showing our mines relative to our colleagues' mines.

Methods

In this lab we are given many different locations of several sand mines in Western Wisconsin. After selecting our specific mines, we instantly tried to geocode them in ArcMap, only to find out the points would not be processed. After opening up the spreadsheet again we changed the addresses to be normalized. In order to normalize the data, we would have to change the excel file layout.

In the image above you can see the not normalized excel spread sheet. In order to normalize the data we would have to go through the addresses given and separate them out into the entire address, which would then break down into the following categories;

• Street Number
• Street Prefix
• Street Name
• Street Suffix
• State
• City
• Zip Code
• Town/City/Village
• County

Other categories included in the excel spread sheets to help with the normalizing include Mine UNIQUE ID, which will be the most important category later on in the lab, facility name, operator, landowner, status, and size of the property.

Not all of the entries had all the data needed to normalize the table which resulted in having to use two different methods of normalizing. The first was taking the data straight from the spread sheet and breaking it up into the categories listed above. But others did not have all the data needed and would have to use the method of picking it out on a map. In this case I used Google Maps, World View, to figure out the locations as well as Public Land Survey System data to find exact locations.

After finalizing the normalization we got a table that would be usable in ArcMap.

The figure above shows the normalized data in the excel spread sheet to be used in the plotting of the mine locations.

After completion of the spread sheet, we imported them into ArcMap and found any errors with the data. When I added my data to ArcMap I did not come across any errors, but some errors which may occur could be not typing the data right, or having fields missing.

The next step after making sure all your data is right, is to use a base layer data map to make sure your points are in their proper locations. If they were not then you should move them over to a more exact location. After completing this step we will import everyone else's data points. After they were all imported together we will merge the data points together to make one shapefile. A few of the shapefiles had errors when trying to merge with the larger shapefile. In this case I made two separate merge shapefiles to use. After all the mines have been imported onto your map we will extract the ones that share the same unique mine ID as the ones I had to map. This part was a little tricky since there were a few which kept the unique mine ID under a different category. In this case I would have to individually go through the attribute tables to find what the ID was kept under. The unique mine ID was kept under four different categories including; unique mine ID, mine ID, F1, and mine.

After extracting all the mines I compared my distances (using point distance tool) to other people's distances to the mines. (Results below)

Results/Errors

The image on the left is the final map showing my mine points with the mines of the same locations. The points I plotted are the white circles with the mine axes inside them, while the yellow circles are the mines plotted by my class colleagues who had the same mines.

Looking at the map you can see there are some points that look out of place, and some that look like they are overlapping or slightly away from the actual location of the other mine spots.

Some errors which may have occurred would be

• Incorrect addresses entered
• Mistyping the data
• Entering the wrong data for the mine
• Map Projections
• Labeling one point with the wrong ID
• Using the office buildings instead of mine area
• User neglect
• Which could include not actually finding the right location

In this image we can see two separate points which are supposed to be the same one. This is one type of error which sprang up from the data we were given from the DNR. On the spread sheet we had two different addresses to choose from. One was a residential looking area, while the other was a forested looking area. Due to the lack of up to date imagery on Google World View neither site looked like a mine. Although the yellow dot chosen on the image represents the office/house most likely owned by the operator while the other represents the actual soon to be mine location.



Conclusion


In conclusion most of the points were fairly close to one another, while others were quite a far distance away. This lab showed how important it is to make sure all your data is normalized, and if you are going to be sharing the data with someone else, make sure everyone knows what coordinate system to use and what coordinate system you are using to plot your data, as well as the proper projections to use.

The image on the right shows how far each point was from the other points, the distance is in meters.

Lastly, the data in the image is arranged from the first mine location to each of the other mine locations, hence four zero distances.


Sources
Bing Maps-Provided world/terrain base map
Drake Bortolameolli-Map Creator
ESRI ArcMap-Software Provider
Google Maps World View-Searching Software to find Locations
Wisconsin DNR-Provider of the mining facilities