Suitability Model of Sand Mines

Goals and Objectives

For the last part of our sand mine project we built a suitability model using various raster geoprocessing tools to place sand mines in the most ideal place in Trempealeau County, Wisconsin.With this model people will be able to look at a map and easily tell where the best placement of a sand mine would be based on geology, land cover, distance to railroads, slope and a water table.

Methods

Creating a suitability model based on the following five criteria

1. Geology
2. Land Use/Land Cover
3. Distance to railroads
4. Slope
5. Water Table

Figure 1: Ranking of the five criteria for creating a suitability Model. The higher the ranking the more suitable for a sand mine.

Figure 2: Model Builder of the tools and steps I took to get to the final suitability model

Many more tools were actually run than shown in the above model maker. For example the water table had to be downloaded from a website and was originally a coverage file. This had to be converted to a raster using the tool "import from e00."

Figure 3: All Maps showing each individual criteria including the excluding map. Light blue indicates the best areas for sand mines.

The final suitability map was supposed to be all of the above criteria added together. The raster calculator tool unfortunately did not work. If you were to add all of the values above the highest values would be considered the best place for a sand mine. That would mean that a value of 13 would be the highest and best value/place for a sand mine. As you can see in the water table map there is a value of 187. I attempted to fix this many times, even by running the reclassify tool over, but for some reason the value stayed there. This value does not appear to show up on the map and therefor can be ignored.

Network Analysis

Goals and Objectives

The goal of this assignment was to assess the damage on roads contributed from trucks, driving from the sand mine to the rail terminals. Case studies have been done to analyze the damage done by these trucks as mentioned in the white paper "Transportation Impacts of Frac Sand Mining in the MAFC Region: Chippewa County Case Study." In order to do this there were many steps:

• Set up a python script to query out the correct mines that met the following criteria
• Only active mines
• Mine must have rail loading station on site
• Mine must not be within 1.5 kilometers of a rail line
• Build a Model to calculate the closest facility route
• Calculate the cost of sand truck travel on the roads by county

Methods

Below is a model of all the steps and tools I used to reach my results.

Figure 1: Model builder of process I went through to answer the question at hand

In this model you can see my work flow through this situation. I first started by using the "Make closest facility layer." In order for that tool to function I had to use the tool "Add Locations" to add the Mines as the incidents and the rail terminals as the facilities. From there the closest facility was solved using the solve tool, where you see "solve succeed" is where the routes were calculated using the road system layer that Esri provided us with.

Next I used two tools to export the resulting route that had been calculated. To do this I first used the "Select Data" tool and then the "Copy Features" tool. This saved the routes as a layer in my geodatabase. Next was to project the data, I chose to project it in a Wisconsin state plane coordinate system that used US feet as its measurement. This would play an important role later on in the process.

After this is when I had to figure out how I would use the information I had to find the distance these trucks were traveling and then the cost  of damage the trucks were causing to each county. I first started with the "Intersect" tool. I intersected the feature classes county boundaries, and routes. This way the feature classes were combined together so I could use the next tool of "summary statistics." The summary that was reported in the attribute table gave the distance of the routes for each county by US feet. I then added a field so US feet could be converted to miles, this field was called "Length_mi." Next I calculated the field by using the simple equation "SUM_Shape_length * 0.000189" This gave the length of the routes in miles. Then I added a field called cost where the cost of damage would be calculated, this was calculated by the equation "Length_mi * 100 * 0.022." It was assumed that each truck would take 50 trips per year, but that only includes one way, the round trip had not been factored which is why the mile field was multiplied by 100 (50 round trips = 100 trips total). The number 0.022 is the hypothetical cost per truck mile is 2.2 cents. Below is a table showing the results of the calculations and a graph displaying the counties with the highest cost from sand trucks and the damage they cause.

Figure 2: Attributes containing the length in miles field called "Length_mi" and the cost field indicated by "cost"

Results

Here you will see the chart I have created that shows the cost of sand transportation of each county and a few maps that illustrate the routes taken by the trucks and what counties they need to go through in order to get the sand from the mine to the rail terminal.

Figure 3: Graph displaying the hypothetical expenses for each county in Wisconsin due to trucks driving sand from the mine to the rail terminal.

These results show that the counties with the highest expenses are Chippewa county, Barron county, Eau Claire and Wood county. The results make sense because the amount of miles driven in a county directly correlates with the cost that each county would incur. In order for these counties to lower the damage on the roads they need to drive less. Maybe they should think about creating their own rail terminal near by as to limit the amount of driving and therefor damage on the roads.

Figure 4: Map of Wisconsin with railroads, railroad facilities and the mines.

Just by looking at this map you can crate a ball game estimate of what is the nearest rail terminal that the sand will be delivered to from the sand mine. Below is a more specific route of the routes taken by this trucks as calculated by the "Make closest Facility Layer" and "Solve."

Figure 5: Map of Wisconsin showing the shortest routes taken by sand mine trucks to the closest rail terminal.

You can see that many routes go through Chippewa and Eau Claire counties.

Conclusion

It was very interesting to find the actual cost that these truck cause on each counties roads. The cost seemed relatively lower than what I thought it would be and makes me worried a calculation may have been done wrong. Overall you can see the top 5 counties with the most driving done in them and subsequently the most expenses for their roads due to the damage by the trucks.

A few problems with this model are that the network analysis tool only calculates the shortest route from sand mine to rail terminal. This does not mean it is the fastest or best way to take. In reality the trucks may be taking a slightly different way than that mapped. Also the trucks are heavier as they take the sand to the rail terminal, likely causing more damage than when the truck is empty coming back from the rail terminal.

Sources

• Street map of USA: From Esri
• White paper "Transportation Impacts of Frac Sand Mining in the MAFC Region: Chippewa County Case Study"

Data Normalization, Geocoding and Error Assessment: Sand Mining Suitability Project

Goals and Objectives

The goal of this lab was to become familiar with the process of normalizing a table given to us from a professional source, in this case the DNR. Unfortunately most companies and organizations do not know the proper format that a table needs to be in in order to work with it in ArcMap or other programs. Our job was to learn and execute the proper format of the table in order to later on geocode the addresses of sand mines. The purpose of normalizing the data was to then learn the geocoding process, and fix any addresses that were unmatched or tied. Later on we would look at the types of errors occurring throughout the entire process and how those errors can be eliminated and/or checked.

Methods

Geocoding is the process of taking existing addresses and turning them into features on a map. There were two methods we went about this. One method for addresses given in the standard way (street address, city, state, zip code) and the other method for addresses given in PLSS (Public Land Survey System) which divides the state into a grid and gives the township, range and section.

Addresses given in the standard way were slightly easier to work with but still required some maintenance. The addresses were given in one column, so we had to separate the sand mine addresses by street address in one column, city in another column, state in another and zip code in another column. This is what is required in order for geocoding to work and make a match. If a match was made it was good practice to make sure the address was correct and fix it, this was done by checking other sources such as google maps. If the address was unmatched you would physically go into the map and pick the correct address by using other sources. And if the match was tied you would still go in and pick the correct option or create the correct address by picking it yourself.

If the Address was given in the PLSS format a little more work and investigating had to be done because geocoding does not work with PLSS addresses. The DNR geodatabase was used to add the feature classes of townships, ranges and sections. The county feature class was also added to double check I was in the correct vicinity of the sand mine. Using these feature classes the area was narrowed down to where the sand mine should be, if it wasn't there (because the basemap imagery is old and sand mines are developing very quickly across the state of Wisconsin) then google maps was used to locate the sand mine. Once the sand mine was located in the area the address was placed.

After all addresses were finalized this point feature class was exported as a shapefile to the share folder where other students geocoding the same sand mines could compare and measure the distance between their own geocoded mine, other student's geocoded mine and the actual address of the mine. The way I chose to measure the distance was to add one student's geocoded points at a time, label them by the field "unique mine ID" and then take the measure tool and measure the distance between the two mines that had the same unique mine ID label. The results of these distances are in Table 3.

Results

Table 1: Sand mine addresses given to us from the DNR before normalization took place.

Notice in the above table (table 1) that the addresses of the sand mines were simply put into one column with the street address and/or the PLSS address. These had to be separated out as shown below in Table 2.

Table 2: Sand mine addresses after normalization

Now columns have been added to this table for street address, city, state and zip code. Making this all separate allows there to be a match when geocoding.

Table 3: Error table showing the distance from my geocoded mines to the same mines geocoded by other students and showing the distance from my geocoded mines to the actual location of those mines

Table 3 shows just how difficult geocoding can be. After geocoding each mine by looking into other sources and double checking what I thought would be the correct address I would still be off by a certain number of meters. The closest I came to geocoding to the correct location was 44 meters. This sounds like a large distance but in fact we were told to geocode the address to the entrance of the sand mine and the actual addresses were located in the center of the mine. So all addresses will be off by at least 40 meters. The largest distance between my geocoded mine and the actual location was 25,794 meters. This is a tremendous distance and unacceptable if the addresses I geocoded needed to be used for a professional source. I found that if the distance was at 1,000 meters or less my geocoded mine was actually fairly close. Anything over 1,000 meters could be of no use to anyone. 

Figure 1: The 16 mines I was assigned to geocode

In figure 1 are the 16 mine addresses I geocoded. Each mine address was either matched, unmatched or tied. For those mines unmatched and tied the address was fixed by picking the address and placing it at the entrance of the mine itself. For the mines that were matched it was still good practice to go in and make sure that they were matched to the actual location of the mine, if not the same procedure was done to pick the address at the entrance of the sand mine. 

Figure 2: Proportional symbol map showing the error distance between the mines I geocoded and the actual location of those mines

Figure 2 maps the error distance from my geocoded mine to the actual location of that mine. The smaller circles indicating the smaller distance and therefore less error and the larger circles indicating a larger distance, therefore indicating a larger error. This map just illustrates how difficult it is to geocode to the correct location. 

Discussion

Error is defined as "the deviation between the measured value and the real world feature" (Lo, Data Quality and Data Standards). And there are many different types of errors such as gross errors, systematic errors and random errors. Some or all of these errors may have occurred through this entire process. Gross errors are simply mistakes. These errors could have happened if I typed the address in wrong as I was normalizing the table. Systematic errors are caused from many things, in this case it could have been how I chose to measure the distance between two points. The way I measured is probably different than how many other people went about this process. And the accuracy may not be as precise as other methods. How I then rounded the number to a whole number may also have caused minor errors to the data. These same errors of measurement and rounding numbers could also be considered random errors which are, "those discrepancies in the measurements that remain after gross and systematic errors have been eliminated" (Lo, Data Quality and Data Standards).

Errors in geographic data can then be split into two categories, inherent errors and operational errors. Inherent errors are errors that inevitably happen when trying to represent real world objects on a map, something is always distorted, scales change and nothing can remain perfect through all of this. Operational errors are errors that occur during the collecting, managing and using geographic data (Lo, Data Quality and Data Standards). 

The original data had inherent and operational errors. After it was given to me to normalize the data it retained even more inherent and operational errors. So the question is how are these points that have been geocoded correct at all? Accuracy measurements can be taken to see which points are actually reliable. These may include equations such as Root Mean Square Error and a Matrix table. These values will allow the user to know if the data given to them is reliable and true. 

Conclusion

Geocoding can be very difficult when given a table that has not been normalized in the proper way. Many mistakes and errors can be made along the way that may be your own fault (operational) or because maps can never be the true representation of the real world (inherent). Luckily there are ways to measure the accuracy of the data and see just how much error has come with the data by running equations such as Root Mean Square Error and a Matrix table. 

Sources

• Lo, Data Quality and Data Standards (reading on D2L)
• Wisconsin DNR geodatabase (WiDNR2014.gdb)
• Esri basemaps

Post 3:Data Gathering

Goals and objectives

The goal of this assignment was to download data from multiple sources, merge the data, and project it all with the same coordinate system. We also became familiar with python scripting, so we were able to project, clip and load the data into our geodatabase. Below are maps of Trempealeau County located in Wisconsin. These maps are meant to show sand mining in the county and the possible risks with activities such as transportation by rail line.

Methods

Data was obtained from various websites, that were downloaded into a temporary folder and then unzipped into our own folder. Data sets came from US Department of Transportation, USGS National map viewer, USDA Geospatial Data Gateway, Trempealeau County Records, and the USDA NRCS Web Soil Survey. With this data we were able to map land cover, transportation, crop cover, elevation and the location of the sand frac mining.

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://nationalmap.gov/about.html

USDA Geospatial Data Gateway

https://gdg.sc.egov.usda.gov/

Trempealeau County Land Records

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

USDA NRCS Web Soil Survey

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



Figure 1: Crops and tree cover of Trempealeau County

Figure 2: Land cover of Trempealeau County, showing where sand frac mining takes place and the railroads that carry the sand.

Figure 3: Elevation of Trempealeau County, showing where sand frac mining takes place and the railroads that carry the sand.

Data Accuracy

Conclusions

Not only is metadata difficult to find but sometimes there are pieces missing. This is very worrisome because we don't know how accurate the data actually is. The only other way to figure out the values that are marked "Not Available (N/A) would be to call someone in that department and ask them about the information. This is time consuming for small details. Companies/corporations/government programs should make metadata easier to obtain. Without metadata how do we even know what we are looking at? This project entailed a lot of data to download and unzip. It was very important to stay organized and know exactly where each piece of data would be located.

Post 2: Python Script

Introduction

The purpose of this post is to show the skills I have learned using python script. 

Figure 1: First Python Script

Figure 1 shows the python script I used to list out the raster datasets I had, put them all into one geodatabase (TMP.gdb) project them all into the same coordinate system and extract each of the datasets from the Trempealeau county boundary.

 Figure 2: Second Python Script

Figure 2 shows the python script I used to set up SQL statements that would select mines that were:

• Active
• Had "Mine" in the facility type field
• All mines that did not have the word "rail" in the facility type
• All mines that are 1.5 Kilometers or more from a rail road

This selected 44 mines out of the original 129 mines, that met this criteria.

Sand Mining in Western Wisconsin

What is sand frac mining? Where is it in Wisconsin?

• It is the mining of sand to be used for hydrofracking, "a technique used to extract natural gas and crude oil from rock formations in other states" (WI DNR). The sand frac mining mainly takes place in western Wisconsin, as we can see in the map below. Wisconsin has a high quantity of sand resources.

Figure 1: Map of Wisconsin showing where sand frac mining takes place in the state.

What are some of the issues associated with sand frac mining in western Wisconsin?

• The silica sand has health impacts on mine workers and neighboring communities. Silica sand is known to cause a number of diseases such as silicosis, which is fibrotic scarring of the lungs. Air quality must be constantly checked for these reasons. 
• There are many toxic chemicals associated with the sand and the process of excavating the sand, that must be disposed of properly. For example polyacrylamide "is used to clarify frac sand wash water and contains residual amounts of acrylamide, a neurotoxin linked to cancer and infertility" (Startribune). This polyacrylamide must be disposed in a lined pond to avoid the toxin from absorbing into the ground and other possible water sources. 

Overview of how GIS will be used to further explore some of these issues as part of a class project

• GIS could be used in a number of ways for sand frac mining and already is in some cases. Obviously air quality is important to the state and the people of Wisconsin, so levels of the sand in the air could be geographically mapped. 
• Minnesota has embraced this technology to map the state in many ways involving sand frac mining, below you will see some examples. 
• What I'm trying to show with these maps is that the possibilities are endless. You can overlay maps that show sand frac mining and pollution, or sand frac mining and health impacts etc. 

Figure 2: Map of Minnesota showing silica sand resources and Railroads

Figure 3: Map of Minnesota showing intermodel freight terminals

Figure 4: Minnesota map showing silica sand resources and ecological subsections. 

Sources 

• http://dnr.wi.gov/topic/Mines/Sand.html
• http://dnr.wi.gov/topic/Mines/ISMMap.html (Map)
• http://www.startribune.com/wisconsin-county-shuts-down-frac-sand-operation-running-wild/278463561/ (News Article on proper disposing)
• file:///Users/rachelhopps/Downloads/23.%20March%20Final%20Silica%20Sand%20report.pdf (case study)
• http://midwestadvocates.org/news-events/news/frac-sand-mine-now-must-monitor-dust-air-quality/ (News Article on air quality)
• file:///Users/rachelhopps/Downloads/23.%20March%20Final%20Silica%20Sand%20report.pdf (Maps of Minnesota)