Lesson 5: Working with Tables and Performing Queries

Jim Kompanek

Reflection on Lesson 5

The primary goal of Lesson 5 was to gain an understanding on how to manipulate ArcMap tables as well as their underlying data. This included both using queries to select records, as well as adding, deleting, and updating records. Up until this point, the class has focused on interface customization as well as fundamentals of working with ArcObjects and the VB language. This was our first opportunity to automate some very simple data analysis, in this case a series of queries between two data files.We were presented with two shapefiles (a counties and cities file) which had a series of demographic attributes. We were instructed to query for:

Greater than 500 farms for milk production
A labor pool of at least 25,000 individuals between the ages of 18 and 64 years
A low crime index (less than or equal to 0.02)
A population of less than 150 individuals per square mile
Located near a university or college
In addition, we were also presented with two queries for additional credit:
Interstate within 20 miles
At least one recreation area within 10 miles

Based upon the lesson material, it wasn’t terribly difficult to come up with the code to automate the first five queries; though, in reality it probably would have been quicker for me to perform them manually than come up with the code to do it. But, I would imagine if I had to process large amounts of data, automating the queries would have been significantly simpler. The most challenging portion of this project involved “combining” the counties layer together to query against the cities layer. Though we were presented with code in the lesson to accomplish this, I still found the task very difficult, and ultimately modified it slightly to accomplish my goal. For the above and beyond (extra credit) queries, I followed numerous false-trails to attempt to automate the buffer task but could ultimately not get it to work.

I did, however modify the code slightly to utilize a module I wrote in an earlier lesson. It simply automated the task of adding the county and city layers to the Table of Contents. It wasn’t entirely necessary for this lesson but it did reinforce the importance of using modules for these kinds of rudimentary tasks which may be useful in many different programs. Secondly, I added a toolbar and button to the document to “trigger” the program. I found this to be somewhat less cumbersome than manually selecting the Macro (Figures 1 and 2).

One requirement of this lesson is that the procedure should be easily modifiable. At first, I had planned on simply declaring all of the variables for the queries in the beginning of the procedure. After doing so, I found it needlessly complicated the code. Because each individual query was already delineated in the code, I chose to simply provide instructions in the beginning of the code for making any changes, and also provided comments when each query was conducted. I also considered making each query a separate module, as well as constructing a form to enter in all of the information. In the future, I may attempt to do this to automate some basic tasks at work. I found this lesson to be both very challenging yet rewarding. Between this lesson and the previous, I look forward to actually implementing some of these procedures into my own work.

Figure 1. ArcMap showing the Ice Cream Tool prior to triggering the query procedure.

Figure 2. ArcMap showing the results of the query procedure.

This document is published in fulfillment of an assignment by a student enrolled in an educational offering of The Pennsylvania State University. The student, named above, retains all rights to the document and responsibility for its accuracy and originality.