Chapter 15: Tables (part 2)

Now that you know about dBase formats, most of the procedures involved for adding and deleting fields, defining field types, and adding and deleting records in ArcView Table documents should be natural and obvious.  What is perhaps not so obvious is just how powerful ArcView's Field Calculator is.  On this page we show how the Field Calculator can be used to:

Exercise 15b--Editing Tables

In the dark ages of database computing, if you wanted to do some operation on a table of data you had to write computer programs to loop over the records one by one, describing exactly what to do.  Getting work done at this level of detail was a difficult, error prone, time consuming process.

Many people approach Table editing in ArcView the same old-fashioned way.  You can find dozens, if not hundreds, of scripts on ESRI's ArcScripts pages that are little more than glorified loops around very simple calculations.

Looping and script writing are usually not necessary.

ArcView's Field Calculator is the key.  It is very simple in concept: it computes values for all selected records in the active field.  But consider this alternative way of looking at it.  The Field Calculator is a computing facility that

It's perhaps easiest to illustrate the Field Calculator's power rather than to describe it abstractly.  The following practical examples show what you can do.

Tip:    View these pages in your browser on the same computer where you are running ArcView.  You can then copy the Avenue expressions (shown in text like this) from your browser directly into the Field Calculator text box; use ctrl-V in the Field Calculator to paste text from the clipboard.

Example 1: Transform a variable

For statistical, mapping, and visualization purposes it is often desirable to alter values of a field mathematically.  Perhaps the commonest and most useful is the logarithm, but powers, roots, exponentials, trigonometric, and hyperbolic functions have their places, too.  For example, count data are often best analyzed in terms of their square roots.  Data shown using graduated symbols usually should be graduated by the square root of the values, too, so that the areas of the symbols become directly proportional to the values shown.

For this exercise, suppose your table has a field [Z] but what you would like to compute with is its logarithm.

1. Start editing the table.
2. Clear any selection on the table so that the logarithm will be computed for all records.
3. Create a new field for the logarithm.  Name it Log(Z).  (We will be using base-10 logs.)  Give it at least three decimal places (three or four is generally good enough for most logarithms).
4. Use the field calculator to compute Log(Z).  In the Field Calculator dialog's text box type

[Z].Log(10)

5. Press OK.  Save your edits if the result looks good.

Of course you must replace the "Z" in the Field Calculator expression with the name of the field you want to transform.

Try this exercise on the themes you used in Chapter 9.  Think about the additional capabilities it gives you for visualizing data.

Example 2: Obtain and compute properties of geographic features

The Avenue language contains methods for obtaining properties of geographic features and computing additional properties.  For detailed information, search for "Shape (class)" in the help system and follow the pointers to the shape types that interest you.

In this exercise you  will compute three new fields in an attribute table for polygonal features.  You may try this out on the "Attributes of Clayton County" table used in GTKAV Exercise 15b.

1. Start in the view.  Activate the theme for which you want information.  Open its feature table.
2. Begin editing the table.  Create numeric fields [Area], [X], and [Y].  Give them an appropriate number of decimal places for your needs.
3. Clear any selection on the table so that values will be computed for all features.
4. Activate the [Area] field (by pressing on its header in the Table document).
5. Make sure the [Shape] field is visible in the Table.  If not, make it visible in the Table|Properties dialog.
6. In the Field Calculator dialog's text box type

[Shape].ReturnArea

7. Press OK.

After checking your work, proceed immediately to compute the [X] values (the X coordinates of the "centers" of each feature):

1. Activate the [X] field.
2. In the Field Calculator dialog's text box type

[Shape].ReturnCenter.GetX

3. Use the mouse to select this entire expression in the text box.  Press Ctrl-C to copy it to the clipboard.  (This will save time and reduce the potential for syntax errors when you compute the Y coordinate values.)
4. Press OK.

After checking your work, compute the [Y] values in a similar fashion:

1. Activate the [Y] field.
2. Place your cursor in the Field Calculator dialog's text box.  Press Ctrl-V to paste the clipboard expression in.  Change the terminal "X" to a "Y" so it now reads

[Shape].ReturnCenter.GetY

3. Press OK.
4. Save your edits.

Questions (answer these with appropriate experimentation):

1. What units is the area in?  The coordinates? 
2. What units will they be in if you change the View's "distance units" property?
3. How about if you establish a projection when the data are in decimal degrees?

Note the syntax: it's easy to guess the "area" part of "[Shape].ReturnArea" or the "center" and "X" parts of "[Shape].ReturnCenter.GetX".  But what about the "Return" and "Get" parts?  The distinction is made, unfortunately, on the basis of how ArcView represents objects internally.  Essential information (the "attributes") about an object (such as a shape) is stored in RAM.  Other information has to be computed from the attributes.  So, for example, areas and centers must be computed, but once a center has been computed (it is just a point), ArcView already has its coordinates (X and Y) with no further computation needed.  Thus requests for areas and centers use "return" and requests for point coordinates use "get".

This guessing game can be helpful, but in most cases you must simply search the ArcView help system for the proper syntax.

Example 3: Save a selection

Suppose you have just worked hard doing a geographic analysis resulting in some records in a table being selected.  You could save this result (before it is lost) by exporting the table (File|Export), because only the selected records will be exported.  This can be inconvenient, since now you have a separate table to deal with.

Instead, this exercise uses the Field Calculator to save the selection as a Boolean (true/false) field within the original table.  It assumes the selection is "proper"; that is, some but not all records are selected (otherwise there's not much to remember).

1. Start editing the table.
2. Create a new field.  Specify "Boolean" type.  Give it a meaningful name, perhaps indicating the analysis you are trying to save.
3. In the Field Calculator dialog's text box type

true

4. Press OK, then press the switch selection button .
5. In the Field Calculator dialog's text box type

false

6. Press OK, then press the switch selection button once more to restore the original selection.
7. Save your edits.

This procedure makes essential use of the Field Calculator's ability to modify just the selected records.  The first calculation sets the boolean field to true for the selected records.  The second calculation sets the field values to false for the originally unselected records.

You can now recreate your saved selection at any time with a very simple Query.  Remember that query expressions are Avenue statements that result in either a true or false value.  Well, a boolean field is either true or false, so its name alone is sufficient.  For example, if you named your field "Is good", then the appropriate query to recreate the selection is simply

[Is good]

Note that you do not need the redundant "[Is good] = true".  Display your understanding by avoiding such unnecessary constructs.

Example 4: Create shapes from attribute data

Frequently, people gather geographic in numerical form.  A GPS receiver, for example, gathers sequences of point coordinates.  Surveyors can report surveyed points in coordinate form or in range-bearing format (essentially a local polar coordinate system).

In this exercise you will move a collection of features by a fixed amount.  (Such operations are useful for changing datums or  changing the origin of a coordinate system.)

1. In a view, open the shapefile you want to transform.  It is now a theme.
2. Start editing the theme (Theme|Start editing).
3. Select the features to move (or clear the selection if you want to move them all).
4. Open the theme's feature table.
5. Activate the [shape] field.
6. In the Field Calculator dialog's text box type

[Shape].Move(100, -200)

7. Press OK.
8. Activate the view.  Make the theme invisible, then visible again.  This causes ArcView to draw the new, transformed, shapes.
9. If the transformation appears correct, stop editing the theme (Theme|Stop editing) and save the edits when asked.

This particular combination (100, -200) of coordinates moved all shapes 100 units to the right and 200 units down.  Use whatever combination is appropriate for your application.

In a similar fashion you can actually construct shapes from field values.  For example, you can create a grid of rectangles from a spreadsheet of rectangle centers and sizes.  These procedures, though, usually require a join to implement correctly, so we will put off a discussion of this advanced technique until all the basic procedures have been learned.

Example 5: Create keys for multiple-field sorts.

You can easily and quickly sort records in an ArcView table on the values of the active field using the Sort Up and Sort Down buttons.   But what if you want to do a nested sort?  For example, you might want to see the records arranged by [Township] and then, among each group of common [Township] values, to see them ordered by [Car_buyers].  The only way to accomplish this in ArcView is to create a new field containing the combination of these two (their "concatenation") and then to sort on it.  Note that [Township] is a string field and [Car_buyers] is a numeric field.

1. Open the table to sort.  Begin editing it.  Clear any selection on the table.
2. Create a new field.   In most applications it will be a string field.  Make it one character wider than the sum of the widths of the [Township] and [Car_buyers] fields.  (Since you probably don't know their widths, it will do no harm--this is a small table--to make the field as wide as possible, or 254 characters.)  Name this field "Key".
3. In the Field Calculator dialog's text box type

[Township] + "#" + [Car_buyers].AsString

4. Follow the usual procedures to finish up: press OK, check your work, stop editing, save edits.

You can now sort using [Key] to achieve the desired result.

There are several things of note in this exercise:

[Township] + "#" + ("        " + [Car_buyers].AsString).Right(8)

(In this expression, "        " contains at least 8 blanks.)  This will work provided there are no negative values to deal with.