Exercise 8: Python Script

The purpose of this python script was to create a weighted index model from raster data. In this exercise I was given the choice as to what variable I wanted to weight. I ended up choosing the 'streams' variable, I would then have to add weight to this variable which is where I multiplied the variable by 1.5. The final step of the scripting process is to add up all of the different variables to come out with the weighted index model.

Raster Modeling

Goal 

The goal of this assignment was for us to use various rater geoprocessing tools to build models for both a sand mining suitability, as well as sand mining impact in the form of environmental and cultural risk in Trempealeau County, WI.

Objectives

There are different objectives listed for each of the models that we needed to build. The first model was the suitability model for mining. The objectives are listed below:

1. Generate a spatial data layer to meet geologic criteria
2. Generate a spatial data layer to meet land use land cover criteria
3. Generate a spatial data layer to meet distance to railroads criteria
4. Generate a spatial data layer to meet the slope criteria
5. Generate a spatial data layer to meet the water - table depth criteria
6. Combine the five criteria into a suitability index model
7. Exclude the non - suitable land cover types

The objectives for creating a model representing the risk for mining are listed below:

1. Generate a spatial data layer to measure impact to streams
2. Generate a spatial data layer to measure impact to prime farmland
3. Generate a spatial data layer to measure impact to residential or populated areas
4. Generate a spatial data layer to measure impact to schools
5. Generate a spatial data layer to measure impact on one variable of your choice 
6. Combine the factors into a risk model
7. Examine the results in proximity to prime recreational areas

Methods

Suitability Model:

To create the suitability model there are five variables that we will be basing the model off of, these variables are listed below:

1. Geology
2. Land Cover
3. Water Table Height
4. DEM (Slope)
5. Rail Terminals

Each of these variables are going to be ranked from values of 1 - 3, 3 being the most suitable and 1 being the least. We used this ranking system because it will best represent what will work the best for frac sand mines. Below is a table that is explaining each of the classifications and rankings of each of the different variables.

(Fig. 1) Suitability Table

1. Geology - Ew/Ej are the most suitable areas for a frac sand mine to find resources which is why those areas are ranked the highest, while the other areas that are ranked the lowest are the least suitable areas for a frac sand mine to find resources. In order to properly rank each of the different types of geology, I had to reclassify the geology feature, where the Ew/Ej formations were in one category which was ranked 3, and all of the other formations were in the category ranked 1. 

2. Land Cover - We needed to go to the NLCD website to find data on the different types of land that are located within the study area. Barren Land, Pastures/Hay are the highest rank because those areas would require the least amount of effort to mine. While the other areas that are ranked lesser than these get more and more difficult to mine, which is why they are lower ranked. To properly distribute the different types of land cover into the correct rank, I used the reclassify tool to differ which types of land were most suitable for frac sand mining, to which ones could be used, and the different land types that could not be used. 

3. Rail Distance - The closer the frac sand mine is to a rail terminal the easier it would be to ship out the materials they mine, and it would also reduce the trucking cost. The farther the distance from the more expensive it is to ship the materials that are mined that is why the longer distances are ranked lower. In order to rank the rail terminals properly I needed to first use the euclidean distance tool so then we could figure out which rail terminals are in Trempealeau county or which rail terminals are closest too Trempealeau county. Once we found which rail terminals were closest or in Trempealeau county we can then rank them according to how far away the terminals are by using the reclassify tool. 

4. Slope - The areas with less elevation is better for mines to be built on, a very steeply sloped area is unsuitable for a mine, therefore the less slope the higher the rank it is given. To find the slope of the study area, we had to use a recent DEM that we had already created in a past assignment. The next step was to convert the DEM so then it would display slope, we utilized the slope tool before reclassifying or ranking anything. Once we had the output from the slope tool we would then use the block statistics tool which is where it groups similar cells with one another so than once we reclassified the output it would be easier to read. 

5. Water -  When looking at the water table we need to assume that the closer the water table is to the surface the higher the rank it gets, meaning the lower the height the higher the rank. In order to find the data needed for the water table we needed to go to a specific website and download the data. We then needed to use the 'topo to raster' tool to convert the contour lines of the water table over to a raster. Once we had all of the data we can now use the reclassify tool so than we can rank the water from how high it is away from the surface. 

After I had classified all of the different features, I used the tool 'raster calculator' to combine all of the data into one map that would provide us with a visual of where the best areas for a sand frac mine to be placed. Below is the Suitability model that was made for this assignment:

(Fig. 2) Suitability Model

Risk Model:

When creating the impact model we need to utilize five different variables than before four of which were chosen for us and the last was our choice.

1. Streams
2. Farm Land
3. Residential Areas
4. Schools 
5. Wildlife Areas

Each of these variables are ranked from 1 to 3, 1 having the largest impact and 3 having the smallest environmental impact. This is opposite from the suitability model because when we combine the two maps it will be easier to see where would be an optimal location for a mine within the study area. In order to reach a ranking of 1 (largest impact) the feature must be less than or equal to 2100 ft. To reach a ranking of 2 (moderate impact) the feature must be within 2101 to 4200 ft, and to reach a ranking of 3 (low impact) the feature must be greater than or equal to 4201 ft away from the mine. Below is a table referring to each of the features:

(Fig.3) Risk Table

1. Streams - The further that a mine is away from a stream the less likely it is to get contaminated by frac sand mining process. In order to classify the distance from potential mines in the county we first need to run an 'Euclidean Distance' which will help us locate all of the streams and the distance from potential frac sand mine sites. We will then use the reclassify tool so then we can classify the distances into the three categories.

2. Farm Land - The reason why we are using farm land as a feature is not because of the possibility of mine locations, but rather mining facilities don't want to contaminate their crops or animals with dust and different types of contaminants. We first need to change this feature into a raster, we do this by using the 'feature to raster' tool, then we reclassify that raster so than we can rank the distances once again.

3. Residential Areas - The distance between a mine and residential areas is very important, because a mine doesn't want to cause local communities any health hazards through the dust and contaminates created by frac sand mines. We located the residential areas in a zoning feature by creating a query statement to select all of the different school district locations. Then we ran the 'euclidean distance' tool so than we can find the distance from the potential mine sites. Then we reclassified the output value and ranked the distances.

4. Schools - The impacts of frac sand mining for schools is the same as listed above in the residential areas section. Before we can reclassify the feature we first need to fun an 'euclidean distance' tool so than we can find the distance between local school districts in the study area, and the potential mines. We would then run the 'reclassify' tool and rank the values by distance.

5. Wildlife Areas - The reason why I chose wildlife areas as my final feature is because just like residential areas, these wildlife areas are at risk of having their habitats contaminated by the dust and different contaminants produced by the mines. The process for this area is the same as the residential area category.

After I had ran all of the tools and classified all of the features I can once again combine all rasters by using the 'raster calculator' tool. This output will provide us with the areas in our study area that are most likely to be impacted by frac sand mining.

(Fig. 4) Risk Model

The final step of the assignment was to combine both the final output from the suitability model with the final output of the risk model. This will allow us to analyze the data so than we can discover the areas where it would be best for a sand mine to be located where it would cause the least environmental damage. I used the 'reclassify' tool for both models so than both outputs would have the same amount of ranks. Once the output from both reclassify tools was complete, we use the 'raster calculator' tool once more to combine both outputs into one map.

(Fig. 5) Combined Model 

Results

(Fig. 6) Suitability Model Maps

(Fig. 7)  Risk Model Maps

(Fig. 8) Ranking of best possible frac sand mine locations

Discussion

After looking at all of the data and all of the maps that were created the main area where there is high risk is more near the northwestern portion of the study area. This indicates that in order for the mine to have a low risk it needs to have the mine located in more the southeastern area of the study area. For the best results I would put a frac sand mine in the portions of the map where the value is greater than or equal to 11. The reason for this is because if a mine was to be constructed in an area where the value is less then 11, that mine has that much more of a chance of negatively impacting the environment. If the mine was to be constructed in an area where the value is greater then 11, that mine would be in one of the more optimal locations for frac sand mining both regarding the suitability for the mine and the risk towards the environment. 

Conclusion

Throughout this assignment many different aspects of it were very useful, and will continue to be very useful in the future. Combining multiple rasters is useful in wanting to combine many different features into a single map while keeping the specific weight of each feature. By creating two seperate models it helped us acquire better database management skills. The final result is a very unique map because it is combining multiple variables into one map and keeping the weight from each variable which allows us to find the most suitable area for a frac sand mine while keeping the environmental risks of the mine in mind. 

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. 

Python Demo

This was the first time that we used python to complete different tools in ArcGIS, python is a skill that is very important in the GIS world and is a skill worth knowing. For this python script we needed to use python to clip, project, and import different rasters into a geodatabase that we had created. The process was pretty straight forward and was nice to get a taste of what coding python is like and how it can be used to complete many different tasks. Below is the python script that was written to complete three tasks regarding different rasters.

Figure 1: Python Script for clip, project, and importing rasters to a geodatabase

Data Gathering

Goal

The goal of this assignment is to become more familiar with the process of collecting data ourselves from different resources, by downloading the data, joining the data, and projecting the data from these different sources into the same coordinate system using python. Also building and designing a geodatabase from scratch to store all of the data in. For this project we are doing all of this to Trempealeau County which is located in Wisconsin. 

Objectives

In this project there were four main objectives that needed to be completed these four objectives are listed below:

Objectives:

1. Download data from several different websites

2. Import data, join tables, merge data and view data

3. Write a Python script to project, clip and load all data into a geodatabase

4. Write a technical report about the data sources and their accuracy

General Methods

In order to obtain all of the data that was needed to complete this project we needed to find different data sets from different sources. In order to collect these data sets we needed to go to several different locations on the internet and download each data set separately. Once we have all of the data downloaded we would then need to unzip all of the data to a certain location or in this case the file that we want all of our data to be located so than it will be easy to find and well organized. Below are all of the site locations for the data that was collected. 

Sources of Data:

US Department of Transportation

http://www.rita.dot.gov/bts/sites/rita.dot.gov.bts/files/publications/national_transportation_atlas_database/index.html 

USGS National Map Viewer

http://viewer.nationalmap.gov/basic/ 

USDA Geospatial Data Gateway

http://datagateway.nrcs.usda.gov/

Trempealeau County Land Records

http://www.tremplocounty.com/landrecords/

USDA NRCS Web Soil Survey

http://websoilsurvey.sc.egov.usda.gov/App/HomePage.htm

After all of the data was collected the next step was to import the SSURGO data that we collected from the NRCS Web Soil Survey. Within this data were a vast amount of tables and we needed to import the data from one of the tables to another by using Microsoft Access, which is an application that helps you edit databases. After this step was complete we then needed to create a python script that would project, clip and import all of the data into the geodatabase that we designed and created. The script that was written imported all of the rasters that was collected from the different sources into the geodatabase, while also clipping them to the borders of  Trempealeau county and giving them the same coordinate system as the study area. Below is the figure 1 which is an image of the python script that was created to project, clip and import the rasters to the study area. 

Figure 1: Python Script

Data Accuracy

Figure 2: Metadata for all the data used

Final Maps

Conclusion

This assignment was very educational, it gave me a good idea of what work in this field is going to be like when having to collect my own data which can be easy at times while also being difficult at other times when trying to organize all of the data and tables. It was also a good experience when creating a geodatabase from scratch and what that process entails. The use of python was also a new experience, it was an eye openeing moment where we used python to complete different tools in ArcGIS. Overall this assignment went well and was a good learning experience. 

Frac Sand Mining

What is Sand Frac Mining?

·         Sand frac mining is used to develop many different resources that we use in everyday life such as oil, natural gas, and natural gas liquids from rock units. Most of frac sand is a natural material that is made up from sandstone that’s known for its highness in purity.

·         This process has been used in Wisconsin for the past 100 years, the sand in Wisconsin is used for a variety of things such as glass manufacturing, foundry molds, and golf course traps, but more importantly the sand has been mined for the petroleum industry. The reason why Wisconsin has been so popular for sand frac mines in the past is because most of the best frac sand is located in the western region of Wisconsin, the image below shows where in the US the best frac sand is located.

Where in Wisconsin?

·         The main areas where these sand frac mines are located is in the western area of Wisconsin. The map below shows all of the active sand frac mines that are in Wisconsin and as you can see there are far more in the western area of Wisconsin than anywhere else in the state.

Issues with Sand Frac Mines

·         Sand frac mines provide our environment with many different issues, some of these issues include air pollution, water pollution, endangering local wildlife, and the quality of life is getting worst for the citizens that live here in Wisconsin.

·         Sand frac mines also use large amounts of groundwater around them, some using up to 2 million gallons a day, this is hurting the water supply in Wisconsin. Creating more problems for the citizens by creating a larger water shortage, and the water they aren’t using is in danger of being contaminated by all of the different chemicals that are being used during the fracking process.

GIS and Sand Fracking


·         GIS and Sand Fracking mines go hand and hand when working with one another, they can use GIS to help them track where the best Frac Sand deposits will be and where would be the best locations to place various mines and wells. Once they have located the best locations for these mines and wells we can use GIS to help track how long there will be available frac sand in these areas and also tell how much groundwater is available for these wells. The important part about keeping our eyes on the wells through GIS is how they can help prevent contamination towards the rest of the groundwater. As you can see GIS is a very helpful tool with most aspects of life especially when mapping different areas of frac sand mines.

Sources

• http://conservationvoters.org/issues/frac-sand-mining/ 

• http://wcwrpc.org/frac-sand-factsheet.pdf

• http://dnr.wi.gov/topic/Mines/documents/SilicaSandMiningFinal.pdf