Bob Pool, GIS Coordinator
Clark County Washington
Title of Paper:
Database Administration Strategies
Tools for Standardizing and Normalizing Lookup Tables
Abstract
As a GIS grows the need to document and share databases becomes increasingly important. Librarian and ArcStorm provide good tools for storing coverages. The problem for many sites, including ours, is how to standardize and store the lookup tables. A lookup table as defined by Esri includes the relate item from the coverage, an item called symbol, and optionally a description item used in a legend. In order to create a single lookup table that meets everyone's needs a more robust structure was required. This structure needed to handle multiple symbol items, a method for setting up the symbol environment in Arcplot to work with the selected symbol item, the ability to add other items such as a dxf_layer item or an item for reclassifying a field into groups. This flexibility allows for a more normalized data structure, where all of the unique information about a classification is stored in a single shared table. The usage of the lookup table must be easy to use and provide enough benefits that users will want to use a central system. With these guidelines in mind Clark County put together a collection of tools that has made it easier to create maps, easier to edit coverages, and has provided a very useful form of on-line documentation. The most obvious benefit derived from this method is that standardized symbol sets are readily available for every layer. This has dramatically decreased the amount of time and frustration required to create a custom map. Another benefit is that the update menus in Arcedit present the user with the description fields instead of the numeric codes stored in the coverages which has increased the quality of our data. This paper will present the structure of our lookup table database and discuss the atools used to make the system work. The atools will be submitted to the AML Applications Contest so that they are available to other agencies.
Paper
Clark County Washington, located in the south western part of the state is one of the fastest growing counties in the country. The county has funded a mapping department for over twenty years. The mapping department adopted GIS in 1990. Since then the GIS department has grown in size and services provided. As the size and the complexity of the GIS has grown it has become increasingly important to document our database. Clark County has had a meta data application for the past three years which is loosely based on the Federal Meta Data Standards. While meta data is an important part of any database, it is less of a concern in a county where most of the layers are simply the best available data for that geographic entity. A more important concern for the county is to create a meta data system that provides quick and useful information. In the day to day operations of a GIS the questions asked about the data are centered around the attributes more than the source of the information. The purpose of this paper is to explain how we store our attribute information and how it is accessed on a daily basis.
The strength of GIS over CAD is that a relational database is tied to the features. This tie to relational databases suggest that data should be stored with respect to the relational database model described by E. F. Codd. The original form of a lookup table was too constrained to create follow these rules. The biggest constraint is that the symbol item has to be named symbol, which implies that there is one and only one symbol for a given attribute. Other problems working with lookup tables include:
Lookup tables as they are used in ArcPlot obviously present some data management problems. The reason that they exist in this form is that they are a legacy method of defining symbology. The usage of a lookup table predates the relate environment now available in ArcInfo. In Clark County we reviewed the usage of lookup tables and setup standards and tools to make them more useful. The following is a hands on discussion of how we use lookup tables and the tools that we use to facilitate this process.
A traditional lookup table for a roads layer might look like this:
Roads.lut
Arterial Symbol Description 0 0 Unclassified 1 5 State Route 2 8 Principal Arterial 3 9 Minor Arterial 4 68 Urban Collector 5 69 Major Collector 6 70 Minor Collector 7 44 Scenic Highway
The usage of this table in ArcPlot would look like this:
Arclines .roads arterial roads.lut
The Clark County lookup table format makes only minor changes to the standard form. The table is named based on the coverage name and the item defined in the table. In this case the table name would be Roads.Arterial instead of Roads.lut. Several benefits are derived from naming the lookup table using the convention <cover_name>.<item_name>. The name provides more information about what is in the table and the name unique. By having a unique name it is possible to place the lookup tables in a common directory. Having a standard naming convention also makes it easy to write tools that work with the lookup tables.
The traditional method of using lookup tables is very easy to use, because the relate environment is managed by the command. In the example roads.lut is related to the roads.aat on the item arterial. The arclines command also requires that the symbol item be named symbol. Using the relate command to setup a relate to the lookup table provides more flexibility but requires more effort on the part of the user. The first and most important tool in the lookup tool box is the setlup command. This command sets up the relate environment for the user. The following is an example of how a lookup table would be used in Clark County.
Roads.arterial
Arterial Atlasym Symbol Desc 0 101 0 Unclassified 1 104 5 State Route 2 105 8 Principal Arterial 3 106 9 Minor Arterial 4 107 68 Urban Collector 5 108 69 Major Collector 6 109 70 Minor Collector 7 110 44 Scenic Highway
The usage of this table in ArcPlot would look like this
ArcPlot: Setlup
Arcplot: Usage: Setlup <Coverage> <Item> {symbol item} {Library | Local}
Arcplot: setlup roads arterial atlasym
Relate Arterial Defined....
Symbol environment for Atlasym defined...
Arcplot: Arclines .roads arterial//atlasym
Arcplot: Keyline .roads arterial//atlasym arterial//desc
The setlup command performs several functions. First and foremost it sets up the relate between the roads.aat, and the roads.arterial file. It names the relate arterial. The program looks by default in the common directory of lookup tables which is referred to as the library. The option is available to look for the table locally. The setlup command also sets up the symbol environment for the selected item, in this case atlasym. This symbol environment is defined in an.AML in the lookup table library. The symbol environment.AML can be as simple as lineset carto. This information is vital if someone wants to recreate the symbology present in another map product.
The lookup library in Clark County contains a subdirectory named symbols. The symbols directory contains the symbol definition.AMLs. These.AMLs must follow the naming convention <cover>-<item>-<symbol_Item>.AML. For example: Setlup roads arterial atlasym looks for and then runs the aml roads-arterial-atlasym.AML. It is also possible to have a file called roads-arterial-atlasym.lin the symbol set has priority over an.AML.
The setlup command provides useful functionality in all the modules of ArcInfo not just Arcplot. Since setlup removes the tedium of setting up relates users can display descriptions of attributes instead of the codes. This functionality is available through out ArcInfo. For example the following frequency command generates a summary of the total number of feet for each type of arterial classification. The table includes descriptions instead of the number codes for arterial classifications.
setlup roads arterial
frequency roads.aat roads.freq
arterial//desc
end
length
end
List roads.freq
Record frequency desc length 1 44 Major Collector 21137.928438 2 456 Minor Arterial 201174.184633 3 40 Minor Collector 22766.466059 4 344 Principal Arterial 173441.686140 5 92 Scenic Highway 29576.559649 6 302 State Route 326404.215032 7 7064 Unclassified 2583345.451303 8 726 Urban Collector 296411.906584
Another ArcPlot example uses the identity command
setlup roads arterial
setlup roads road-c
identify roads line * arterial//desc roads-c//desc
Record Arterial//desc Roads-c//desc
870 Unclassified Public Roads
In addition to suggested items in the lookup table it is possible to extend the content of lookup table to contain all the unique information associated with a given attribute. In the Clark County GIS database we have added additional items such as the DXF_LAYER name used in data sales. Zoning codes have been grouped into zoning classes. The full descriptions have been supplemented with abbreviations for attributes such as a soil code or zoning. it.
Conclusion
Clearly using these tools makes working with lookup tables very easy and provides the user with more information. The central location and the ability to use lookup tables in all modules of ArcInfo provides a better data management strategy than the simple lookup tables of the past. The extended lookup table structure makes this information readily available and much easier to maintain. The addition of the a lookup library with symbol environment functionality has increased the quality and consistency of our products. The flexibility of having multiple symbol items means that we have the ability to create custom symbology for those clients that require
In addition to the setlup command there are several other tools that have been created to work with the lookup table library. The following commands have been written to utilize the lookup table library. These.AMLs have been included with this document and are also available for review in the AML contest area.
Setlup <Cover> <Item> {Symbol Item} {Library | Local} is used to setup the relate environment for a lookup table. This command works in Arc, ArcEdit, ArcPlot, Grid, and Tables.
Mklup <cover> <item> is used to create and maintain lookup tables. It loads and unloads the tables into ASCII files for easy editing.
Getlup {cover} {item} provides a means to browse the lookup table library for tables. For example getlup roads will display a popup menu of all the lookup tables that exist for the roads layer. Getlup # Arterial will display all the lookup tables for the item artieral.
Listlup <Cover> <Item> {Print} lists a lookup table and optionally sends the listing to a printer.