Lesson 5: Working with Tables and Performing Queries
GEOG 485
Brian Buchanan
The goal of Project 5 was to revisit the Jen and Barry scenario from the first GIS analysis we did in GEOG 483. The goal of the lesson was to write a procedure that automated the site selection process by writing a program that would meet Jen and Barry’s selection criteria. Their criteria was:
Greater than 500 farms for milk production
A labor pool of at least 25,000 individuals between the ages of 18 and 64 years
Interstate within 20 miles
A low crime index (less than or equal to 0.02)
At least one recreation area within 10 miles
A population of less than 150 individuals per square mile
Located near a university or college
Because finding distances between features was not covered within the lesson, enabling the search criteria to search for recreation areas and interstates was not considered for this project. These criteria could be examined for extra credit.
I decided to create a Tool that the user could click and then enter the data. The Ice Cream Tool is shown in Figure 1. I began writing the code for the click event by selecting the county level criteria. I used a query filter and .whereclause to search for at least 500 farms, a labor pool between the ages of 18 and 64 that numbered 25,000, and a population density of less than 150 individuals per square mile. Once this was written and tested, I bound the selected features into a single geometry. This single geometry was then used by a spatial filter and .whereclause to search for cities within the selected counties that had a low crime index and were near a university/college. According to the project guidelines, nine cities should be represented be selected by automated site selection.
After I figured out the queryfiler, spatialfiler, and geometry bind, I programmed four InputBoxes into the code so that a user could enter different values and be able to still use the tool. For example, a user could search for at least 200 farms, a labor pool or 50000, etc. This makes the tool much more user friendly and modified when needed. The results are shown in the following Figures.
Figure 1: Map of the Counties and Cities showing the Ice Cream Tool Bar. This map was created using ArcView 9.2 on September 24, 3008.
Figure 2: Map showing what happens after you click the Site Analysis button on the Ice Cream Toolbar. An InputBox asking for the maximum population density opens. The user data entered into the InputBox is then used by the QueryFilter to help select the counties where Jen and Barry will put an Ice Cream Store. This map was created using ArcView 9.2 on September 24, 2008.
Figure 3: After you enter the maximum population density, three other InputBoxes consecutively appear asking for the number of farms, the size of the workforce, and the crime index. If the user enters 150 for the population density, 500 for the farms, 25000 for the size of the workforce, and 0.02 for the crime index, nine cities are selected. The results are shown in Figure 3. This map was created using ArcView 9.2 on September 24, 2008.
Figure 4: Map showing different cities selected. The different data entered was 500 for population density, 500 farms, 500 population workforce, and 5 crime index. This map shows that the code allows the user to use the toolbar to enter different data to select different cities for ice cream stores if Jen and Barry's criteria changed. This map was created using ArcView 9.2 on September 24, 2008.
I would like to apologize for the lateness of this report. I have struggled with this project for 3 weeks, and was ready to give up last night, when I finally moved the one line of code that I had not touched, and it worked! Unfortunately, due to the time constraints, I was unable to attempt the extra credit assignment. I hope that I will be able to figure this out later. I also would have liked to try to program message boxes telling the user to enter data instead of getting an error message. Finally, I would have liked to program a clear selection button. Unfortunately I was already overdue, and decided to turn everything in while my code still worked!
I found this project, and the course as a whole, to be very challenging. Lesson 5, in particular, had me stumped. I spent more hours then I can count reading the lesson and writing different codes. In Lesson 5, I had no problems using the query filter to get the counties I needed. The headaches started when I tried to select the cities within the selected counties. This took me many more hours than was probably necessary to figure out. The solution, moving one line of code, was much simpler than the many, many other solutions I tried. This was a good lesson for me to learn: the simplest solutions in programming are often the best ones!
I entered this course to try something new. Although it has been very difficult and stressful, I know that if I encounter programming in the future, I now have a background to draw upon in order to try and solve the problem. As an arachaeologist, the ability to program and create different tools to solve different problems in ArcView will be invaluable. This will come in handy for my doctoral dissertation someday!