Introduction to Tables and Databases

Does GIS uses tables or databases?

• The Elements of a Table
• The Elements of a Database
• What Is a Relate?
• Relate Types
• GIS Data Queries and Selections

Does GIS Use Tables or Databases?

The ability to combine data stored in tables and databases with geography is what makes GIS such a versatile technology. Many technologies are available to display and map natural and man-made geographic features, but it is GIS that relates those features to data stored in tables and databases. In order to make the most of this relationship of geographic feature information and the related tabular data, it is important to understand a few things about tables and databases.

The Elements of a Table

A table consists of rows and columns as in the example below. This table contains information about parcels of land. Within the table are categories of information in each column; each category is described by a column heading. Under the row containing the column headings are rows of information; each contains information about one parcel of land which is identified by the Parcel Number.

A row in a table is also known as a record. Each row, or record, contains a grouping of information about a single subject. The columns of a table may also be known as fields, items, and, commonly in GIS, attributes. Each column describes a certain attribute of the record. The record for Parcel Number 9726301001, read across the table and tells us the owner is J. Kennedy, the Zoning District for the parcel is RLP and the Drainage Basin for the Parcel is #1. In this record you see a very common practice used in tables, the use of abbreviations and keys. Rather than storing the complete, lengthy Zoning District or Storm Drainage Basin titles, a substitution of an acronym or a numbered key is used. This practice may or may not be appropriate depending upon the use or size of the table(s) involved.

The Elements of a Database

A database is simply a collection of tables of interrelated information that is stored and managed as a unit.

A relational database is a set of tables containing data fitted into predefined categories. Each table (which is sometimes called a relation) contains one or more data categories in columns. Each row contains a unique instance of data for the categories defined by the columns. For example, a typical land-record database might include a table that described a parcel with columns for Parcel#, Owner, Zoning District, Storm Drainage Basin #, and so forth. Another table would describe a Zoning District: District Acronym, Full Title, Description of Zone, City Code Reference, and so forth. A user of the database could obtain a view of the database that fitted the user's needs. For example, a Zoning Inspector might like a view or report on all parcels of land owned by a certain person, in a certain Zoning District, listing the full Zoning District information.

What Is a Relate?

A relate is an association or connection between corresponding records in two tables. This association is made possible by having at least one common item in each of the tables. Each column that relates to another column in a different table is know as a "key" and must contain the same data. Each relate is constructed of a "from table" and a "to table." The column which is being used to form the relate in the "from table" is know as the "primary key" or "local key." The column which is being used to form the relate in the "to table" is know as the "foreign key."

The tables below demonstrate a very simple table relate. Each table contains the common field, Parcel#, that is used to relate on. This example is a simplistic representation of how the City GIS Division maintains its' parcel database. The Parcel Table being maintained by GIS contains a record on each parcel in the database. The Larimer County Assessor maintains a record of each parcel and other related information such as valuations and record dates. By maintaining the relate keys in each table, these two tables can be related and used by City staff to submit queries against in order to find valuable information.

Parcel Table

Assessor Valuation Table

Relate Types

One-To One

This is the simplest type of relate. In this type there is only one record in the table that is being related to that corresponds with exactly one record in the table that we are relating from.

Many-To One

In this instance, the table that we are relating from will have occurrences of identical values in the relate field. By having the same value, these fields can only correspond to a single record in the table being related to.

One-To-Many

This situation occurs when each record in the field being related from contains a unique value, but corresponds to multiple records in the table being related to.

Many-To-Many

The last situation occurs when multiple records in the table being related from correspond with multiple records in the table being related to.

GIS Data Queries and Selections

GIS data that is stored in a tabular format can be queried and viewed the same way data is stored in other database systems and applications. However, GIS provides the user with the ability to query that same data using a geographic component. Data can be selected and viewed based upon its proximity to a selected geographic element such as a road, lake, address, zoning district, utility infrastructure, etc… or any combination thereof.