STEMgis Database Design/Purpose

Before setting up a STEMgis database it is essential to consider what you wish to achieve with STEMgis in terms of integration, analysis and visualization. Many features may be stored in the database in different ways and this will affect the way features and attributes are presented and the tools that may be used to explore the data associated with them.

It will help to answer the following before you begin:

• What type of spatial feature is required?
• How should the features be grouped together?
• What is the most efficient method of storage?
• How will the attribute be defined?
• How will the attributes be grouped together?

Defining features

In technical terms a feature may be stored as a point, line, polygon (or area), polypolygon (multiple areas), surface, or grid.

Taking a farm as an example of a spatial feature, its features may be represented in many different ways. For instance, a study of crop yield might incorporate some fixed point locations where soil moisture content was measured, different tracks (lines) in a field where different seeding concentrations may have been used, or different fields (polygons and/or polypolygons) within which different fertilizer applications have been made. In addition to this, the farm grounds may need to be represented as an aerial photograph (grid) or the topography of the farm represented as a surface.

As you can see from this example, a feature such as a farm may be represented in many ways and it is the application and the required visualization and analysis tools that determine how to define a feature.

Defining feature types

Many features may be entered into the database. For ease of retrieval these features should be grouped into feature types. There is no fixed way to do this, it is entirely at the user's discretion. For instance, the soil moisture samples may be grouped into a feature type where the name or classification could be as varied as:

• Field A, Site 1
• Giles' Farm
• Devon Soil Moisture Monitoring Sites
• Soil Sample Sites
• Terrestrial Information System

The variation in feature classification can be related to spatial extent and/or level of generalization.

The definition of a feature type is also strongly related to the definition of attributes. For instance, you may wish to define some feature types relating to biological classifications, e.g. mammals and invertebrates. However, another way of classifying the data might be to have a feature type called Biodiversity and then add attributes such as anemone count, anemone size variation, which are then classified into a dictionary called invertebrates.

Data may be used for many different purposes and classifying a feature within a feature type does not necessarily preclude it from being compared to features and attributes elsewhere. The STEMgis Viewer allows the user to select up to five feature types in any single query provided that the feature types have the same attributes and spatial feature types.

In conclusion, various applications are possible dependent on the desired perception of, and access to, the data.

Efficiency Issues

Each feature is stored in the database with several indices. Each index is used to link the spatial definitions with attribute data, both of which vary through time. They are essential to the working of STEMgis. To minimize database size and increase retrieval times there are several tips that can be followed:

• Store elevation data as surfaces rather than individual points - this can increase retrieval times by up to 200 times.
• Store depth profiles as 3D lines and attach attribute data to individual elements of the line.
• When loading line features, try to avoid too many individual segments. Join the segments together to make a larger continuous set of co-ordinates. However, this needs to be balanced against the level at which attributes might be attached to the line. For instance, you might want to apply an attribute to an entire river or road or attach the attribute to smaller, say kilometer, length segments. If you plan to do the latter then you should load the lines as many individual kilometer length features rather than a full length line feature.
• Similarly with polygons, you may wish to load a group of polygon features as a single polypolygon feature, but only if you do not wish to load attributes onto the feature or if you wish to apply attributes to the polypolygon feature as a whole.

Defining attributes

When defining attributes you must consider what type of data is to be loaded. Each different type of data is loaded into a different table in the database so it is not possible to change the data type once data has already been loaded onto an attribute (although of course the data may be deleted, the attribute redefined and then the data reloaded. There are a number of different types listed below:

• Char - used for text descriptions, names or textual classifications (limit is 255 characters)
• Integer - used for counts, sales or other attributes that use whole number (i.e. -2,147,483,648 to 2,147,483,647)
• Real - used for single precision decimal numbers (i.e. stores numbers from -3.402823E38 to -1.401298E–45 for negative values and from 1.401298E–45 to 3.402823E38 for positive values.
• Double - used for double precision decimal numbers (i.e. stores numbers from -1.79769313486231E308 to -4.94065645841247E–324 for negative values and from 1.79769313486231E308 to 4.94065645841247E–324 for positive values.
• Gridc - used for loading image attributes. The images must be byte format bitmap images formatted either as windows bitmap (BMP), portable network graphics (PNG), or as JPEG format.
• Gridr - used for loading other raster data as attributes. The images must be in STEMgis Image Processing System (SIPS) format, which enables multi-band data of byte, integer and real binary type.
• Movie - used for loading movie attributes. The images must be formatted as AVI movie files.
• Binary - used to load binary files such as Word documents or Excel spreadsheets associated with spatial features. The user of the STEM database must have the appropriate program to view the file type loaded.
• Array - this data type is used when two attributes are inextricably linked and need to be plotted one versus the other. The data types are variant, i.e. they can be char, integer, real or double. An example might include a cumulative frequency graph where one attribute is plotted against percentage. Another example might be a spectral curve where the radiance or reflectance is plotted against wavelength. In these cases the 'percentage' and 'wavelength' data types should be defined as an ARRAY data type while the related attribute is defined using one of the options above.

Note: Each attribute has a unit associated with it. It is often possible to measure an attribute using different units (e.g. the attribute 'visitor numbers' might have the units 'count per day' or 'count per year'. Make sure that before loading the data into the STEMgis database that the input files use the same units as the attribute definition.

Defining dictionaries

A dictionary provides a means of grouping different attributes into a user defined classification. This aids the user when searching for the data they require and provides different ways of presenting the data to the user. Dictionaries can therefore be defined to suit the needs of the application.