Exercise 6: Querying Tables and Summarizing Records
In this exercise, you will query a data set on the location of dams in Wisconsin. You will select dams based on their location in a county, generate summary statistics for the selected dams, and create a summary table of the incidence of dams in each county.
Step 1 – Start ArcView and open a project
If necessary, start ArcView. From the File menu, choose Open Project. Double-click the directory folders and navigate to c:\esritrn\arcview\wiscexer. Open the project wiscexer6.apr by clicking on the project name, then clicking OK.
Step 2 – Query a table and display the selected set
When the project opens, you see a view called Wisconsin Dams. The view contains a theme called Dams.
With the Dams theme active, click on the Open Theme Table button to open the Attributes of Dams table. Scroll through the table.
The fields in the table include a dam identification number (dam_id), dam name (dam_name), stream name (strm_name), dam size (size), type of ownership (owner_type), regulatory agency (reg_agency), basin size in square miles (dm_bsn_sq), impoundment size in acres (imp_acres), and county name (cty_name).
The first thing you are tasked to do is find out the number of large dams in Wisconsin. Use the Query builder to do this.
With the table active, click the Query Builder button.
The Query Builder dialog box displays.
Note: You can access the Query Builder from a table or from the view. The name at the top of the dialog box changes according to the name of the table or the name of the active theme.
You will use the Query Builder to select records in the theme table and the corresponding features in the view.
In the Fields scrolling list, double-click on [Size] to add it to the query text box.
From the logical operators, click the “=” button.
From the Values scrolling list, double click “LARGE.”
Your expression should look like this:
Click the New Set button to select all land parcels that satisfy the expression.
The dams are highlighted in the view and the corresponding records are highlighted in the table.
Move the Query Builder out of the way.
On the left side of the tool bar, you see that 1161 of 4637 dams in your database are classified as large.
Make the table active by clicking on its title bar, then click the Promote button to move the selected records to the top of the table.
Step 3 – Get statistics on the selected set
It is easy to get summary statistics on any numeric field on a table. Suppose you want to know the sizes of the impoundments in your selected set.
With the table active, click on the Imp_acres field to highlight it.
From the Field menu, select Statistics.
The statistics for the Imp_acres field are displayed in a message box.
ArcView tells you the total impoundment area in acres for all the dams it counted; the number of dams it counted; the average, largest, and smallest impoundment areas; and the difference between the largest and smallest impoundments areas. It also gives you the variance and standard deviation values. Note that the statistics are given for the selected records only. If no records are selected, the statistics are given for all records. You can cut and paste the statistics values from the message box into another application such as Microsoft Word.
Click OK to dismiss the message box.
Step 4 – Modify the selection
You know which dams in your database are classified as large. Your supervisor would like to know which dams are under the regulatory authority of Wisconsin Department of Natural Resources. To get this information, you will go back to the Query Builder and refine your selection.
Click on the Query Builder to make it active.
In the query text box, click and drag to highlight the expression, then press Backspace to delete it.
From the Fields scrolling list, double-click [Reg_agency] to add it to the query text box. From the logical operators, click the “=” button. From the Values scrolling list, double-click “WIDNR.”
Your expression should look like this:
Click the Select From Set button.
A total of 936 features and their corresponding records should now be selected. They represent the dams classified as large and under the regulatory authority of the Wisconsin Department of Natural Resource.
Close the Query Builder.
Step 5 – Summarize a table
By summarizing a table, you count the occurrences of each unique value in a specified field. Optionally, you can get statistical information on another (numeric) field in the table. The results of the summary are stored in a new table that ArcView creates.
Suppose the state is planning to provide money to counties to replace or remove dams. In order to make some rough calculations, your supervisor asks you to find out the number of dams in each county and determine the average basin size in square miles and the total size of all impoundments in acres for each county.
Click on the Attributes of Dams table to make it active; click the Select None button to clear the previously selected records.
In the table, click on the Cty_name filed to make it active. Summarizing the table on this field will tell you how many dams there are in each county.
Click the Summarize button to open the Summary Table Definition dialog box.
In the filename input box, change the pathname to the c:\esritrn\arcview\student directory. Change the file name from sum1.dbf to damcount.dbf.
You also want to know the average basin size and impoundment area in each county.
From the Field drop-down list, select Drn_bsn_sq.
In the Summarize by drop-down list, select Average (the defauld selection).
Click the Add button to add the expression Ave_Drn_bsn_sq to the text box
From the Field drop-down list, select Imp_acres.
In the Summarize by drop-down list, select Sum.
Click the Add button to add the expression Sum_Imp_acres to the text box.
The Summary Table Definition dialog box should look like this
Click OK.
A new table, called damcount.dbf, displays.
This table shows you the number of dams in each county, together with the average basin size in square miles and the total size of the impoundments in acres. This information should provide your supervisor with some information to begin assessing the impacts of the program. Note that the top value for county name is 880. This represents a miscoded county name and should be removed or corrected.
Step 6 – Close the project
Click on the Project window to make it active (or choose wiscexer6.apr from the Window menu). From the File menu, choose Close Project.
Click No when ArcView asks if you want to save your project.
END
8
Wisconsin ArcView Exercise #6 - 03/16/00