Exercise 7: Network Analysis

Goals and objectives:

      The objectives of this lab were to map and analyze the transportation routes of the frac sand mines to rail station terminals in their close vicinity in order to asses the possible damage to the roads the trucks would be driving on and then how much it would cost each county. To do so we would be using programs such as pyscripter, ArcGIS, and excel. Inside ArcGIS we would be using the Network analysis tool to estimate the hypothetical distance of trips taken by trucks transporting frac sand within a given year.

Methods: 

      First I wrote the python script we were to use for the network analysis of this lab which was meant to select the mines that are active, not have a rail station for the sand to be loaded on, and not be within 1.5 km of a rail station. The script then exported a new copy of the file sifted through the SQL statements. 

Figure 1

      Next I loaded the final mines, rail terminals, and streets feature classes into the network analyst closest facility function with the mines being the incidents and the terminals being the facilities, and after clicking the solve button exported the routes to a new feature class, as seen in figure two along with the mines and rail terminals. 

Figure 2

      Before going on to the model building step I deleted the address field from the final mines feature class in order to avoid future errors it might cause. Then by dragging and dropping in the needed tools from the Arc Toolbox I created a model layout that would result in the automation of making the closest facility layer with the added locations, the solve function to get the route, create a copy of the feature class, project the data into the Wisconsin state plane system (meters), add fields for distance traveled and cost, the field calculations to create the end values (distance_travelled_inmiles = shape_length * 0.00062713 * 50 * 2) and (cost = distance_travelled_inmiles * 2.2/100) , and lastly a join to combine it all with the Wisconsin Counties feature class for the process of map making.


Figure 3

Results and discussion:

      The computed table of values for the effected counties as shown in figure 4 helps convey the cost associated with the road maintenance of each counties due to trucking of frac sand. Counties such as Chippewa, Dunn, and Wood all incur heavy costs from the needed road maintenance in the range of 329-753$ a year. Nearly all counties with suitable mines in them would experience some level  of moderate to high road damage and increased trucking traffic save for counties like Burnett and Winnebago which will be affected very little. Located below are maps (figures 5-6) and graphical representations (figure 7-8) of the road maintenance and distance in miles traveled by frac sand trucks. They directly reflect and create a visualization of the data in Figure 4.

Figure 4

Figure 5

Figure 6

Figure 7

Figure 8

Conclusions:

     

      The impact of frac sand mining on roads used for transportation is a serious issue to the counties involved and can create numerous problems such as increased road traffic, pollution (ground, noise, and air) and the costs of road maintenance and repair have to be covered by either the county or the mining company (else the roads fall in to disrepair and become unusable). The costs to each county vary on how the road networks connect mines to the nearest rail terminals which has been calculated using the ArcGIS network analyst, and in the end several select counties such as Chippewa, Wood, and Dunn simply have more connections to mines within their borders which leads to more traffic costs.  Based off the information I have gathered, depending on who has access to it (be it the county officials or the mine executives) they could use the information to plan the most effective routes that will incur the least costs to themselves or the county, and also figure out where the busiest areas of operation are in order to know where to avoid installing more mining locations.

Exercise 6: Data Normalization, Geocoding, and Error Assessment

Goals and objectives

The goal of this lab was to gain skills in geocoding by using sand mine locations in Wisconsin we acquired through the DNR, which before used in geocoding had to be normalized. This exercise is the first of our class multipart suitability and risk model project, with the study area focusing on Trempealeau county and their surrounding counties. The objective we were to reach during this exercise were to normalize the mines excel file we received, use the ESRI geocoding service to geocode our mines, add the PLSS feature class to use in locating mines, manually locate any remaining mines with the PLSS grid, compare our geocodings with other students and with the actual mine locations, and then write out technical report.

Methods

Before any geocoding could be done we had to normalize the mine location data table given to us (denoted by our professor in the DNR file by our student ID) so I created a new excel document and copied over many of the fields already present, but also split several of them such as the addresses into subcategories in order for ArcMap to be able to read them. After locating as many of the 18 mines to my best ability using the available addresses I then used the Address inspector to check for any inconsistencies in the locational data or if there were any suitable nearby locations. Then for any mines that did not have a suitable street address I had to use the PLSS grid along with a base map and google earth to hone in on which mines were suitable candidates and then manually use the address locator to give them an address value.

Once our individual sets of 18 geocoded mines were finalized we compiled our data in a collaborative class folder that we could all access each other’s mine location shapefiles from in order to use the merge tool to combine them all together into a single shapefile and use them to compare to the actual mine locations just recently given to us. For our distance analysis between our geocoded and the actual locations we used the point distance tool (or spatial join tool) to find the closest mine relative to our geocoded and after using a query SQL statement to just show our own geocoded mines in relation to their nearest actual mine locations I created a table showing the distance between them for use in this report.

After finalizing all the previous steps I created two maps showing the collection of student geocoded mines and the actual mine locations, and then my personal geocoded mines along with the queried actual mine locations in order to visually show how far off our data was from the actual mine locations. 

Results

Figure 1

Figure 2

Figure 3

Figure 4

Figure 5

Discussion

The types of errors that were present in the distances data were both inherent and operational, specifically with the differential age of maps and data points which might cause discrepancies in mines with both location and whether or not they are active or still present in base maps. Also with this project being very user input intensive there was a lot of attribute data input errors in the student made normalized tables along with format translation between the existing DNR excel sheet and the normalized sheet.

To measure whether or not certain mine locational points are accurate and correct it is possible to calculate and use their root mean square error (RMSE) which measures the spatial x,y coordinate discrepancies between the geocoded mines versus the actual mine locations. 

Conclusion

Overall the process of collecting addresses data and geocoding them was quite difficult, complex, and time consuming, mostly due to how jumbled the data table was that we aquired from the DNR and also the fact that our class normalized their sets of mines indipendantly without a standardized format. Working on a team as classmates to normalize and geocode the entirety of the DNR data proved to be quite a test of skill and technique for us all, and I especially saw firsthand how important being thorough and following the standardized format and syntax of working with data is, and am glad I was able to share in this endeavor.