CSV File Formats

All data are loaded into a STEMgis database using ASCII comma separated value (CSV) files. Spatial and attribute data files are loaded separately into a STEMgis database. A spatial feature or object must exist in the database before any attributes can be loaded onto it, i.e. the spatial CSV file must be loaded before any related attribute CSV files are loaded. A number of sample files have been included with the STEMgis installation. These provide example of most different styles of CSV file that STEMgis uses. They are located in the data\samples sub-directory wherever STEMgis was installed. Please note that these sample files use the United Kingdom date format, i.e. day/month/year (e.g. 14/10/99). If you use a different date format, you may wish to temporarily change your Windows Regional Settings while loading these sample files.

Spatial and attribute CSV files share a common form, i.e. each CSV file consists of a Header Block, a column of Row Headers, two rows of Column Headers and a Data Block containing one cell for each column header / VALUE combination. The final row header must contain the string EOF to terminate the file. This general structure is illustrated in the following table.

The table shows part of a spatial CSV file loaded into a spreadsheet - the actual ASCII file has commas within each row of text to define the positions of the columns. This spatial CSV file illustrates the format for a set of point features. The row following the final VALUE row must containthe string EOF only.

Note: The column separator, in this case a comma, is dependent on your regional settings on your PC. For example, German regional settings use a semi-colon (;) as the column separator. To check or change your regional settings go to the Control Panel and select Regional and Language Options. You will then see an entry for 'List Separator' which can be edited if you wish.

Note: When transferring data between PCs with different regional settings it is therefore advisable to use a binary format such as Microsoft Excel and then re-export the data as CSV format on the new PC. Microsoft Excel will use the appropriate regional setting as the column separator.

Spatial CSV files must contain the following:

Attribute CSV files must contain the following:

Although the only mandatory header for a spatial file is the PROJECTION definition and there are no mandatory row headers for attribute files, the use of non-mandatory header information is encouraged as it often aids clarity.

Prior to loading, both spatial and attribute CSV files must be sorted by UCODE, DATE and/or TIME in that order, i.e. the definition of each feature must be ordered by date and time and not mixed out of chronological order and must not be interspersed with the definition of other features throughout a file. The Manager will warn you of any unsorted data in your file and you will be asked to re-order the data before reloading.

Most software packages will export CSV files of spatial or attribute data - these can be modified to add the necessary header information for STEMgis. A number of samples CSV files are located in the STEMgis installation directory under the sub-directory \samples.

A description of all Header Block, Row Header and Column Header keywords follows:

Header Block

• SCALE - followed by a long integer value (e.g. 50000) indicating the scale of the data (e.g. 1:50,000). This option allows you to store scale information for each new spatial feature.
• TRANDATE - followed by the date of creation of the data file. This header is not essential but provides a useful record of when the data were loaded.
• PROJECTION - followed by the long integer code for the projection used by the input data. This code can be obtained from the Projection List
• MASKC - followed by a long integer value defining the colour to be used for transparency in raster images. (The default value is 0, i.e. black). This option is only used when loading raster spatial features. You calculate a MASKC value using the following formula: red + (green * 256) + (blue * 65536) where red, green and blue have values between 0 and 255.
• ENCODE - followed by a text value defining the encoding method for the storage of binary arrays (e.g. line and polygon co-ordinates). The default value is is RLE_MET which is run-length encoding for spatial data in units of metres, which gives a precision of 1/10th mm in X, Y, & Z. An alternative encoding method can be used when loading latitude/longitude spatial data, RLE_LAT which provides a precision of 1/1,000,000th degree in latitude and longitude and 1/10th mm in elevation. This option is only used when loading line, polygon or surface spatial data which are compressed when stored in the database.
• TOLERANCEZ - followed by a double value defining the tolerance value for which depth attribute data is matched to depth in the spatial definition of the feature. Units are metres and the entered value should reflect realistic world values. (The default value is 0.000001 m, i.e. 1 µm). This option is only used when loading attributes onto line features with depth variation.
• METADATA - followed by a string defining the title of the metadata entry previously stored in the STEMgis database to which the data in the CSV file relates. The METADATA row is equally applicable to spatial and attribute files.

Row Headers

• COLTYPE - heading for a row containing column headers
• COLSUBTYPE - heading for a row containing supplementary column headers
• VALUE - heading for a row containing data values
• EOF - an end-of-file marker which must exist at the end of a file on a separate row

Column Headers

The column headers depend on the input data. A description of the column headers in the example file is given here and a description of all possible column headers is given below.

Three additional COLTYPEs could have been used for POINT features, these are:

Other spatial data is loaded in a similar way, simply change the COLTYPE headers, with each co-ordinate having a separate row but co-ordinates for the same feature instance having the same values for UCODE, FNAME and FTYPE:

One additional COLTYPE can be used for POLYGON features, e.g. the tigers1900polygons.csv sample file:

There are three further spatial data types, correction trapezia, grids and links. Their COLTYPEs are as follows:

For larger images, retrieval performance can be improved by splitting the image into smaller tiles and loading each tile as a separate spatial entity. The recommended maximum tile size is 1500 x 1500 pixels, although you can load larger images if you wish.

Note: At present images can only be displayed in the projection with which they are loaded into the database. As a result, images may be turned off when you switch between different map projections.

LINK features are used to define virtual links between different spatial features and may be used to track attributes across these links. An example of this is points on a river network. If you wish to plot the attribute values as a transect down a river then link the individual points in the order with which they flow. You can then click on any point in the network to produce a transect of all the downstream links from the chosen point. The FROMUCODE and TOUCODE unique codes must already exist in the database.

Note: A link can be made between spatial features within different feature types.

Loading Attributes

Example files for loading attributes are provided in offsets.csv, urban_attribs.csv, swrivers_attribs.csv and tigerpop.csv. Multiple attribute columns may be defined in any input file with the following COLTYPE.

Attributes are defined using the Manager tools and relate to one of the STEMgis data types. If you wish to load an 'ARRAY' data then the CSV file must include two ATTRIB columns, the first of which must refer to an attribute that has been defined as an ARRAY data type.

Loading Attributes with Depth

Example files for loading attributes with depth are provided in points_w_depth.csv, attribs_w_depth.csv. The spatial feature is defined as a 3D line and then attributes are loaded onto the vertices or elements of the line. The element needs to be identified within the CSV file as an additional column and may be defined in one of two ways:

Loading Image/Binary File/Movie Attributes

When loading images, binary files or movies as attributes the value in the ATTRIB column is a filename for the image, binary file or movie, see model_attrib.csv. If images are georeferenced then their associated spatial feature types will be grids. If the images are non-georeferenced then the associated feature types will be something other than grids, e.g. POINTs, LINEs or POLYGONs.

Note: Movie and binary file attributes can only be associated with point, line and polygon spatial features.

Note: The image attribute definitions must be set to GRIDC or GRIDR, the binary file attribute definitions to BINARY and the movie attribute definitions to MOVIE.

Other CSV File Formats

There are several additional CSV file formats that are used within STEMgis. These include:

• KEY files for loading colour and text key information
• PERIOD files which are used to turn spatial features on and off through time
• TIMEINT files that define a time series with regular intervals
• TIMESER files that define a time series with irregular intervals
• ARRAY files for loading pairwise attribute values
• ATTDEF files for loading many attribute definition at once

Loading Image or Character Attribute Keys

User defined keys may be loaded for byte images (i.e. GRIDC attributes) or attributes defined as characters (i.e. CHAR attributes). To load key colour information use the following COLTYPEs and see an example file in modelkey.csv.

For continuous keys only five of the key names will be shown, but it is recommended that you load all of the names in the sequence. If there are more than 30 entries in a key information file relating to an image attribute then they are considered to represent a continuous colour range as in the right-hand example above.

Loading Period Data

Spatial data can change its definition through time by simply loading spatial data with different times for the same UCODE. However, it is sometimes necessary to have spatial features that have a consistent spatial definition through time but that be irrelevant or 'turned off' at certain times. An example of this might be an archaeological site that is only relevant during certain periods of history or a railway line that becomes disused for a while before then being reopened.

To use this functionality within STEMgis you should load a CSV file with UCODE, FTYPE, DATE and PERIOD (see table below) COLTYPEs. There are 3 sample files in the samples directory - TESTONOFF*.csv, which show how to use the PERIOD COLTYPE.

Loading Regular/Irregular Time Series Data

If you have a large amount of attribute data associated with one spatial feature it can be quite expensive, in terms of database size and retrieval times, to store each value in a separate row in the STEMgis database. TIMEINT and TIMESER COLTYPEs can be used to define regular and irregular time series data which are stored as binary arrays in the database. The associated attributes are also stored as compressed binary arrays which saves on space and retrieval times by a factor of more than 100.

To load regular time series data, which might include model type data or regularly measured sites, use the TIMEINT COLTYPE.

Note: At least one of the increment columns is required to define the time increment used within the time series. If more than one increment column is used then each increment value is summed to calculate the interval period.

Note: Any number of TLABELs may be defined in one CSV file.

To load irregular time series data, use the TIMESER COLTYPE.

Note: The individual date/time elements must be in chronological order. You can only have one TLABEL per CSV file.

Once you have loaded a time series definition you can then load related attribute data. This is done using a standard attribute CSV file but by replacing the date and/or time COLTYPEs with ALTDATE COLTYPEs which address a position within a time series (see below).

Note: TPOS starts at 1 not zero.

Examples of the time series and related attribute files are given in the samples directory - TIMESER*.csv and TIMEINT*.csv.

Loading Array Type Data

All attribute data described so far has been loaded relative to date/time and 3D space. You may have data which is inextricably linked to another attribute at any instant in time. Such data might include an attribute versus cumulative percentage or reflectance versus wavelength. This type of data is best loaded as an ARRAY. All ARRAY data is loaded as pairs of attribute values linked by an ARRAY ID number. The additional COLTYPEs that are required are listed below.

An example of ARRAY data is given in samples directory - TestArray.csv

Loading Attribute Definitions

Attribute definitions can be added individually using the user interface method provided under the Tools menu. However, to load many definitions simultaneously the ATTDEF CSV file format can be used. The column definitions are described below.

An example of ATTDEF data is given in samples directory - attdef.csv

Note: Apart from ARRAYs the various groups of COLTYPE headers may not appear in the same file. You must load spatial, attribute, key, period and time series information separately.

Note: Every file MUST end in 'EOF' on a line by itself.

Using the sample files

The sample files are included in the data\samples sub-directory, wherever STEMgis has been installed.

Before loading the sample files, you should define the Feature Types and Attributes defined in each of the files. The following table shows the codes that are used: