Designing a Database

In designing a GIS data base, the first decision is the geometry to use.  In part this will depend on the scale; something that would be a point or line at small scale could become a polygon at large scale.  Most databases can hold only a single geometry type, and data symbolization would become very difficult when mixing multiple geometries.  KML files in Google Earth can mix geometries, but have little flexibility in changing the symbolization.

A point database can be much simpler, as a single, simple table can hold both the coordinates and their attributes.  It can be edited in a spreadsheet with little chance of corruption.  In contrast, a line or area database usually requires multiple files because each record can have wildly different number of coordinate pairs, from two for a simple straight line or three for a triangular polygon, to tens or hundreds or thousands of points for complicated feature like the outline of a coastal state.  Because there are multiple files, you cannot simply edit the database with impunity because deleting a record, inserting a record, or reordering the records must include changes to all the files; all edits must be done in a GIS program which coordinates all changes in each of the files.

The most common format for GIS databases, the shapefile, uses at least three files.

If you have a choice, picking a point database will make your life simpler.  The coordinates can be included in the dBase table, and if required, the GIS software can easily turn that file into a complete shapefile.

Polygons can be even more complex if donuts are allowed; these will have exterior boundaries, and interior outlines of the "holes".  Most programs keep track of this by the direction of digitizing, clockwise or counterclockwise.

Once the geometry is chosen, the user must define the fields.  Considerations here include:

• Computer data types.  This determines what operations you can do.
• Coding--you can use integer codes  (e.g. 1=asphalt, 2=concrete, 3=dirt).  These will conserve storage space, but are not very friendly for people looking at the data tables.  Storage space, unless you have a great number of very large databases, is no longer a significant concern.  If you use string codes, and avoid spelling inconsistencies, the table is much friendlier for humans. For color you could use a number (1=red, 2=green, etc.), an abbreviation (GR=green, BR=brown, etc.), or fully spelled out (Green, Brown, etc.).  You choice will determine the amount of storage required, the degree of human readability, and the effort required to insure that you do not record the same information in multiple ways.  This becomes even more critical if you will not be doing all the data collection yourself, and have to insure accurate data collection by others.
• Categories to use: allowing unrestricted choice can lead to confusion.  A database tracking terrorists had problems because different analysts coded the same vehicle as "SUV", "Jeep", "Vehicle", and it becomes very difficult to identify all the records showing the same vehicle.
• Fine or coarse granularity: if you wanted to add color to the terrorist vehicles, do you want one field with "Gray SUV", or do you want to have separate "VEH_TYPE" and "VEH_COLOR"?  If you want to be able to search for the separate attributes, the second option is better; filters can readily find matches at the beginning of the strings, but in the middle or end it becomes much harder.
• Length of each field: saving a few bytes on each record will blow up if your data is truncated with no warning; unless your databases will be truly huge, skimping here can be a false economy.
• Field names.  You are likely to have a limited number of characters to choose from, and relatively short limits.

 Newer databases do not have some of these limitations, but because databases are stuck with the dBase table used in shapefile, any attempt to use a new standard will encounter huge incompatability issues.

Last revision 8/29/2015