Database Schema

The Cross Section Viewer software uses a local SQLite database to store all survey data and definitions. The figure below shows the individual tables and relationships within the database. A brief overview of each table is provided below.

• PK - primary key
• FK - foreign key
• UK - unique key
• The term Audit Fields refers to four fields used to track who last changed a particular record and when:
  • AddedOn - UTC time stamp of when the record was added.
  • AddedBy - Windows user name of the person that added the record.
  • UpdatedOn - UTC time stamp of when the record was last updated.
  • UpdatedBy - Windows user name of the last person to edit the record.

Tables

• Surveys - A survey represents a collection of cross section measurements, typically collected during a single field campaign.
• SurveyGroups - Multiple surveys can be grouped into a survey group which then acts like a survey in the cross section viewer survey. This is useful if individual surveys capture separate regions of a river system but should be combined together for use in the tools within the cross section viewer.
• SurveyGroupValues - Lists the individual surveys that are part of each survey group.
• CrossSectionSurveys - A survey cross section is a single measurement of a cross section performed during a survey at a particular cross section. In other words, cross sections capture the unchanging definition of a cross section location (river mile etc.), while survey cross sections capture everything measured at a cross section during a particular survey.
• CrossSectionPoints - A cross section point is an actual measurement at a particular cross section station. There are many cross section points in a single cross section survey.
• CrossSections - A cross section refers to a particular river distance where repeated surveys measure the bathymetry of the channel. Each measurement of a cross section is referred to as a “cross section survey”.
• StageValues - Stage types capture reference water stages or important elevations at a cross section. These can be things such as mean August flow or bankfull flow etc. Each cross section can have multiple reference stages. Each reference stage is of a particular type. Note that reference stages are tied to cross sections (as opposed to survey cross sections) and as such do not change with each survey. i.e. they are for reference only. Also, note that CRPs are stored in their own tables (described below).
• StageTypes - Stage types represent special types of water stage, such as mean August flow. When recording a reference stage for a particular cross section it must be associated with a stage type. Stage types are used in the StageValues table. Users can edit or add items to this table.
• StationValues - Reference stations refer to special stations along a cross section that have a particular meaning. For example the sailing line and rectified channel line are reference stations. Each reference station is related to a particular station type. Note that reference stations are tied to cross sections (as opposed to survey cross sections) and as such do not change with each survey. i.e. they are for reference only.
• StationTypes - Station types represent special cross section stations such as a sailing line or rectified channel line. Station types are used in the ReferenceStations table. Users can edit or add items to this table.
• Selections - Selections track collections of cross sections. Note that selections refer to cross section definitions and not to survey cross sections. i.e. a selection refers to positions on the river where cross sections are measured and not to the particular measurements from one survey or other. In this way, selections can be used to retrieve cross sections from any survey. It is recommended that users manage the contents of this table via the cross section viewer software and do not edit its contents directly.
• SelectionValues - This is an intersect table between Selections and CrossSections. This table tracks which cross sections are part of each selection. A cross section can be part of many selections, but can only be used once in each selection. It is recommended that users manage the contents of this table via the cross section viewer software and do not edit its contents directly.
• CRPs - Construction reference planes (CRP) represent a collection of inflection points that are used as a datum on which the river is referenced against. Each CRP is represented by multiple inflection points. Administrators can edit this table and add, remove or rename CRPs as needed. Note that the cross section viewer displays the CRPs in reverse alphabetical order and so it’s recommended that the year of the CRP is always used as the first part of the name. This ensures that the newest (and potentially most relevant) CRP always appears first, and as the default, in the software.
• CRPValues - CRP elevations represent the inflection points along a CRP. Each CRP has multiple CRP elevations. Users can edit this table but should always retain at least two records for each CRP.
• LongProfiles - Long profiles represent a line down the channel. These are typically used for features such as Thalwegs or sailing lines.
• LongProfileValues - Represents individual points that belong to a long profile. Each point is associated with a particular survey cross section. That is, they are tied to a particular point in time and survey. Points are identified by a particular cross section, station along the cross section and elevation.
• RiverDistances - There is one record in this table for each location on the Missouri River that represents a whole river mile. The location is identified by the latitude and longitude in decimal degrees.
• BendNames - Each of the major bends on the Missouri River between river mile zero and 500 have a record in this table.
• Metadata - Stores information about the status of the database including, the date it was released, the latest version number etc. Users should not edit this table since it is used by the software to detect the compatibility of the database with the software itself.