Exercise 6- Data Normalization, Geocoding, and Error Assessment

Goals:


The goal of exercise six is to geocode the locations of sixteen different mines from excel spreadsheet that was received from the Wisconsin DNR. First the data had to be normalized in order to work with the data effectively. From there comparisons are made between the locations I found and the locations other classmates recorded and then from there compared to the actual locations of the mines that was provided by the instructor.




Objectives:


The objectives of this lab are to get the data for the sand mines in Western Wisconsin from the DNR and connect to the geocoding service from ESRI online and to then normalize the table. Next using the street addresses, the mines are geocoded to there location on a map. If a street address was not given, the mine was found by using the PLSS and google maps. From there the locations found for the mines is compared to other classmates.




Methods:


Using the Unique mine ID field in Microsoft Excel, we were assigned sixteen mines in Western Wisconsin. The first step of the exercise was to try and open the data in ArcMap to find the locations, only to find that there was obvious errors in opening them immediately. Closer inspection of the table reveals that the data is not uniform, some mines did not have street addresses and others were just a jumble of PLSS( Public Land Survey System) data and other did not have full street addresses. The process of normalizing the data was essential to success in this exercise. To normalize the data the data was broken down into different categories as described in the list below.

•  PLSS

• City

• State

• Zip Code

• Address

• Town/ City/ Village

• County

Figure 1: WI DNR Raw Data Table

Figure 1 above is an example table of the data that was received from the WI DNR. The actual table obviously contained much more data but for lab purposes only a sample portion was used to show what we had to work with in the beginning.


Other information that was given included the Operator, Facility Name, Site Status, Facility Type, Property Size, the Facilities Contact Information and finally the Mine Unique ID. Of the information listed above the most essential information in normalizing the table was the Mine Unique ID. Not all of the maps were easily found even after normalizing the data, many required the use of Google Maps. Mines are growing in popularity so there are mines popping up all over, hence why the use of google maps was essential because it is more up to date than ArcMap.
 The zip code and city were used to help get close to the location and then from there the mines were found manually using satellite imagery. 

Below Figure 2 illustrates the final product of the table that was used to geocode the mines. The headings near the top show all the columns that had to be added and all the information that had to be separated to make the Excel sheet user friendly.

Figure 2: WI DNR Normalized Data Table

After the data was normalized, it was then brought into ArcMap, immediately upon bringing the data into Arc, there was a few errors still with the data. A few of my columns had missing fields because there was no data for those mines, to remedy this a blank field was inserted. Next, a base map of world imagery was added, this gave us the first real sense of where the mines were that we were to geocode. Of the sixteen mines to be geocoded, only six of the mines had the correct address, the rest had to be found manually. Following this step, the other classmates data was added as well. From here using the select by attributes feature in ArcMap, to select by Unique Mine ID, the other students sixteen mines were selected and placed into the same geodatabase. Only one copy of each mine was used for reference, the reason there was multiple people assigned to each mine was in case someone did not get it done or placed a point in the wrong area. After the data was in the same geodatabase the projection was changed to UTM Zone 15 North. After the three separate shapefiles being worked with from other students was merged together, this make the process of finding distances between mines easier.



Results/Discussion:


The map below is a map that shows how my mines geocoded related spatially to the other students who also had to geocode the mines. The red circles are the mines that I worked with, while the green are my classmates. It is easy to see that some mines are not near any other mines, this can be attributed to the fact that there are so many mines in western Wisconsin. When having to hunt and peck for a mine on google maps, it was easy to select mines that were not the ones you intended to geocode.

Figure 3: My Mine Locations Compared to Classmates

Along with the error mentioned in the above paragraph of simply selecting the wrong mine, there were many other opportunities for error to occur in this exercise. The map projections used when geocoding, entering data from one mine into another, selecting the wrong unique mine ID, using a gravel pit rather than a mining site, also if the user got frustrated and decided to just drop the location somewhere in the right area and give up. Finally there was no real description on where the geocoded point should be placed, meaning that some people may have dropped the location inside the mine, some may have been on the road in, while other could have done it like myself, placing the point on the road where the mine meets blacktop.

Figure 4: My Mines Compared to Actual Mine Locations

Above, Figure 4 illustrates my geocoded mine locations in comparison to the actual mine location that was found using the latitude and longitude. In order for the map viewer to see how my mines related to the actual location, I made my points slightly larger and added a little transparency to the actual mine locations. Some of the locations are very close while other differ considerably.

Figure 5: Comparison of Mine Locations

Above is a map that really gives a good idea into how the locations of mines can differ based on who is doing the geocoding. The red circle at the bottom of the map is the location that I selected as the location of the mine, the way I thought about it was that, that would be where the trucks would be going in and out and often Google Maps takes you to the driveway rather than the center of your destination. My classmate selected the road on the way in and the actual location of the mine is indicated by the yellow circle at the top of the map.



Conclusion:



In conclusion, this assignment tested patience to say the least. At times it was challenging to get the data normalized and then to merge the data, and from there figure out the best way to figure out the distances apart. I used a spatial join to calculate the distances. The tables below show the distances away from where I plotted my mine. I was within twelve meters of one of my classmates points, but in contrast I was over seventy kilometers from the actual location of one mine. That is attributed to some of the user errors listed above. This exercise was tough, but served as a good problem solving and skill building lab.

Figure 6: Distance From my Mine Locations Compared to Classmates

Figure 7: Distance From my Mine Locations Compared to the Actual Location

Sources:


Wisconsin DNR- http://dnr.wi.gov/

Google Maps- https://www.google.com/maps

Public Land Survey System- http://nationalmap.gov/small_scale/a_plss.html

ESRI Online- http://www.esri.com/software/arcgis/arcgisonline

Exercise 5: Data Downloading, Interoperabiliy, and Working with Projections in Python

Goals and Objectives
This exercise had a few goals.  Becoming familiar with downloading data from varies sites on the internet, learning how to import the data into ArcGIS, joining certain data sets with tables and then projecting data from different coordinate systems into one. From there the task entailed building a geodatabase and finally building and running a script in python to manipulate the data. In post 2 is the python script that was used during this exercise.

General Methods
The data for this lab was obtained on the internet from several different websites. There was data used from major data suppliers such as the USGS, USDA, USDA NRCS, and the Trempealeau county land records along with a few other sites. Prior to the process of downloading data, a folder was created in the temporary folder on the Q drive. This is done because the data that was downloaded required a lot of space, and the Temp folder is emptied every month. From the Temp folder the zip files were unzipped into the working folder that was previously made in the Q drive in the class specific folder. Once in the working folder, the data is now able to be worked with. The railways were clipped to be the ones just inside Trempealeau county. The second part of this exercise was to build the Python script to that manipulated the data into the maps below. Figure 1 is a table of the sources that were used, there uses for this exercise and there web addresses. Figure 3 and 4 are just a few sample maps of the finished product geodatabase for this exercise.

Figure 1

Figure 2

Figure 3

Figure 4

Data Accuracy
The Metadata provides an insight into what the data can do in a way, it is very effective in explaining the limitations of the data. Below in Figure 5 is the table that displays the metadata, if there was no data then the cell is designated with N/A.

Figure 5

 The amount of cells that are not filled and display the N/A are a pretty good hint that maybe the data we received is not as reliable as initially thought.

Conclusion
The skills that were learned in this lab are crucial to be a productive member of the workforce. Each step that was done has a real world application that will be utilized in the future. Downloading, joining, clipping and organizing data in a geodatabase is an essential skill to have. At times this exercise seemed impossible, the python script is not easy and requires patience, not only did the lab teach us the skills but it also offered a valuable lesson in time management.

Post 2: Python Scripts

Python is a programming system that is designed to help user deliver more concepts in fewer lines of text. Python makes geoproccessing in ArcGIS more simple, this is the system that ESRI chooses to use as its base platform for open source distribution. Python has many operations that it can do including frameworks, advanced content management, web development and many other uses. In comparison to other similar software it is widely considered to be more user friendly. Knowing how to work with a program such as python helps to diversify your skills and is becoming a larger part of the GIS industry each year.


 Below is the Python script that was written for exercise 5, the data was taken from multiple websites online, organized in Arc catalog and then the script was written to do the operations that are described in the script.

Figure 1: First attempt into writing our own script

Below in Figure 2 is a Python script that was designed to preform network analysis to help find a way to accurately tell the impact of trucking sand to rail terminals by the use of roads. The script below was made to include certain details about the mines. The mines had to be active and more than 1.5 KM away from a railway. That is done because some sand companies build there own private railway to hook up with the big rail system. The script below produced a total of 44 mines. 

Figure 2: Sand Mines Script

Post 1- A Closer Look at Frac Sand Mining in Wisconsin

     The type of sand that is being targeted in Wisconsin is quartz sand, also known as silica sand. Being that the sand is from quartz, it is very strong, meaning that it will not break apart under pressure, also the sand particles are very uniform, close to being circular. This sand has a real value and is used in many applications that one might not typically think about. First and foremost it is used in the making of glass, basically every type. This sand is also used to clean casting surfaces at factories such as foundries. The sand is also used in ceramics, the construction industry, and in adhesives.

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

     Pictured above is a map of the state of Wisconsin, the map illustrates where the sand mines are located and where the sand deposits are. As the map shows, there is sand in southern Wisconsin, but it is more scattered and there is not as much, making western Wisconsin  the obvious place to mine. One thing to take into consideration when looking at this map is the fact that it was made in 2011, the number of frac plants has increased. There is economic benefits that come along with the mines, and that has brought with it a good deal of support.

     Like any topic, there is people on both sides of the issue, there are some concerns about the negative affects of the mines. The possibility of contaminated ground water, nearby residents are also concerned about the air borne affects, for example respiratory issues. Another large concern is the affect that the dump trucks have on the roads, country roads are not made to withstand the constant loads of sand that are being transported on them.

Sources

1. http://wcwrpc.org/frac-sand-factsheet.pdf
2. http://dnr.wi.gov/topic/mines/sand.html
3. http://dnr.wi.gov/topic/Mines/ISMMap.html