5 acoustic Tables
5.1 algorithms
This table holds information on the algorithms used to obtain the bottom points.
| Column |
Type |
Modifiers |
Description |
| bottom_id |
integer |
not null
default 0 |
Unique identifier to distinguish different algorithms |
| algorithm_name |
character
varying(50)
|
not null |
The name of the algorithm as
named by ESP2 |
| algorithm_settings |
text |
|
The ESP2 settings for the algorithm. |
Index: ”algorithms_pkey” primary key, btree (bottom_id, algorithm_name)
Foreign-key constraint: ”$1” FOREIGN KEY (bottom_id) REFERENCES bottoms(bottom_id) ON DELETE CASCADE DEFERRABLE
5.2 bottompoints
This table holds the bottom points for every bottom that has been defined on an echogram.
| Column |
Type |
Modifiers |
Description |
| bottom_id |
integer |
not null default 0 |
Unique identifiier for the bottom point |
| ping_num |
integer |
not null default 0 |
The ping number that this bottom point
applies to |
| depth |
integer |
default 0 |
The depth of the bottom point in samples |
| previous_depth |
integer |
default 0 |
The depth of the previous bottom point in samples for ping ping_num |
| userset |
boolean |
default false |
Is the bottom point set by the user? |
| bad |
boolean |
default false |
Is the transmit marked as bad? |
Index: ”bottompoints pkey” primary key, btree (bottom_id, ping_num)
Foreign-key constraint: ”$1” FOREIGN KEY (bottom_id) REFERENCES bottoms(bottom_id) ON DELETE CASCADE DEFERRABLE
5.3 bottoms
This table holds information on a bottom that has been defined on an echogram. The actual bottom points are stored in the bottompoints table.
| Column |
Type |
Modifiers |
Description |
| file_id |
integer |
default 0 |
Unique identifier for the file that this
bottom applies to |
| revision |
character
varying(50) |
|
The CVS revision number for the bottom |
| tag |
character
varying(50) |
|
The CVS tag created when committing
the version |
| bottom_id |
integer |
not null
default 0 |
Unique identifier for this bottom |
| last_modified |
timestamp without time
zone |
|
The time in UTC when this bottom was
modifier |
| author |
character
varying(50) |
|
The initials of the person who last modified
this bottom |
| comments |
text |
|
Any comments entered by the person
who last modified this bottom |
Index: ”bottoms_pkey” primary key, btree (bottom_id)
Foreign-key constraint: ”$1” FOREIGN KEY (file_id) REFERENCES files(file_id) ON DELETE CASCADE DEFERRABLE
5.4 clients
This is a reference table that holds information on organisations that have commissioned the collection of acoustic data.
| Column |
Type |
Modifiers |
Description |
| client_code |
character
varying(50) |
not null |
Unique identifier for the client |
| name |
character
varying(255) |
|
The name of the client |
| address |
character
varying(255) |
|
The postal address of the client |
| telephone |
character
varying(50) |
|
The telephone number of the
client |
| website |
character
varying(255) |
|
The website address of the client |
| data_access_conditions |
character
varying(255) |
|
Any conditions on the use of the data collected on behalf of the client |
Index: ”clients_pkey” primary key, btree (client_code)
5.5 componentclassifier
This is a temporary table that provides classifications for components. It is used when populating the components table and provides the value for the component type column.
| Column |
Type |
Modifiers |
Description |
| id |
character
varying(50) |
|
Identifier for the component |
| type |
character
varying(50) |
|
The classification for the component |
5.6 components
This table holds the components that make up a piece of equipment. This includes both physical pieces of equipment, as well as software settings that define the operation of the equipment. It is defined in a sufficiently general manner so that it can contain a variety of information, and can adapt as data from new equipment is added to the database.
| Column |
Type |
Modifiers |
Description |
| equipment_id |
integer |
not null
default 0 |
The equipment identifier that this component is part of |
| component_id |
character
varying(50)
|
|
The name of the component |
| component_type |
character
varying(50)
|
|
The type of the component
|
| component_value |
character
varying(255) |
|
The value of the component |
| component_channel |
integer |
default 0 |
The channel number for the component |
Foreign-key constraints: ”$2” FOREIGN KEY (equipment_id) REFERENCES equipment(equipment_id) ON DELETE CASCADE DEFERRABLE
”$1” FOREIGN KEY (component_type) REFERENCES componenttypes(component_type) DEFERRABLE
5.7 componenttypes
This is a reference table that provides short descriptions for the component type column found in the components table.
| Column |
Type |
Modifiers |
Description |
| component_type |
character
varying(50) |
not null |
Unique identifier for the component
type |
| description |
character
varying(200) |
|
A short description of the component
types |
Index: ”componenttypes pkey” primary key, btree (component_type)
5.8 daporigins
This is a reference table that provides descriptions of the channel numbers found in the Crest formatted raw data files.
| Column |
Type |
Modifiers |
Description |
| channel |
integer |
not null
default 0 |
The channel number
|
| short_desc |
character
varying(50) |
|
A short description of the channel |
| long_desc |
character
varying(255) |
|
A longer description of the channel |
| sb_channel |
integer |
default 0 |
If not null, gives the pseudo channel number for the appropriate splitbeam channel (see table sborigins) |
Index: ”daporigins-pkey” primary key, btree (channel)
Foreign-key constraint: ”$1” FOREIGN KEY (sb_channel) REFERENCES sborigins(sb_channel) DEFERRABLE
5.9 dfilenamelegacycal
This is a temporary table that contains equipment calibration values for particular acoustic data files. It is referred to when inserting or updating rows into the files table.
| Column |
Type |
Modifiers |
| dfilename |
character
varying(255) |
not null |
| cal_id |
integer |
default 0 |
Index: ”dfilenamelegacycal_pkey” primary key, btree (dfilename)
Foreign-key constraint: ”$1” FOREIGN KEY (cal id) REFERENCES legacycal(cal_id) ON DELETE CASCADE DEFERRABLE
5.10 dfilenamewaterproperties
This is a temporary table that contains water property information for particular acoustic data files. It is referred to when inserting or updating rows into the files table
| Column |
Type |
Modifiers |
Description |
| id |
integer |
not null
default 0 |
|
| directory |
character
varying(255) |
|
|
| file_range |
character
varying(50) |
|
in format 100-103,45,50-60, etc |
| absorption |
double precision |
default 0 |
dB/m |
| sound_speed |
double precision |
default 0 |
m/s |
| frequency |
double precision |
default 0 |
Hz |
Index: ”dfilenamewaterproperties_pkey” primary key, btree (id)
5.11 directoryclassifier
This is a temporary table that provides ancillary information on a directory of data during the loading process. A directory of data is closely linked to a row in the experiments table and this table provides much of the information that ends up in the experiments table.
| Column |
Type |
Modifiers |
| directory |
character
varying(255) |
not null |
| client_code |
character
varying(50) |
|
| description |
text |
|
| project_code |
character
varying(50) |
|
| experiment_type |
character
varying(50) |
|
| log_book_filename |
character
varying(255) |
|
Index: ”directoryclassifier_pkey” primary key, btree (directory)
5.12 directoryplatformsystem
This is a temporary table that is used when inserting rows into the equipment table.
| Column |
Type |
Modifiers |
Description |
| directory |
character
varying(255) |
|
The data directory |
| filenumbers |
character
varying(255) |
|
A list of file number ranges e.g.,
200-345,357,340-350 |
| platform |
character
varying(50) |
|
A short description of the platform
(e.g. hull, tb2, frame) |
| system |
character
varying (50) |
|
The name of the system (e.g.,
CREST, EK60, ES60, CRFREDA,
etc) |
5.13 equipment
This table contains information on the piece of equipment used to collect an acoustic data file.
| Column |
Type |
Modifiers |
Description |
| equipment_id |
integer |
not null
default 0
|
Unique identifier for the piece of
equipment |
| platform |
character
varying(50) |
|
The equipment platform (e.g.
hull, tb2, frame). |
| system |
character
varying(50) |
|
The type of system (e.g.
CREST, ES60, EK60, DATASONICS) |
| date_established |
timestamp
without time
zone |
|
The date when the equipment
was first used (UTC) |
| date_withdrawn |
timestamp
without time
zone |
|
The date when the equipment
was last used (UTC) |
| calibration_id |
integer |
default 0 |
The identifier for the calibration
that applies to this piece of
equipment |
Index: ”equipment_pkey” primary key, btree (equipment_id)
Foreign-key constraint: ”$1” FOREIGN KEY (calibration_id) REFERENCES slplussrt(calibration_id) DEFERRABLE
5.14 experiments
This table contains information on the experiment for which acoustic data files were collected.
| Column |
Type |
Modifiers |
Description |
| experiment_id |
integer |
not null
default 0 |
Unique identifier for this experiment |
| project_code |
character
varying(50) |
|
The project codes that this experiment was conducted under. Multiple projects separated by
commas |
| experiment_type |
character
varying(50) |
|
The type of experiment |
| voyage_code |
character
varying(50)
|
|
The voyage code for the voyage
that this experiment was part of
(if there was a voyage) |
| description |
text |
|
A short description of the experiment |
| path_to_collection |
character
varying(255) |
|
The directory path to where the
acoustic data files are stored
|
| client_code |
character
varying(50) |
|
The code for the client that
funded this experiment |
Index: ”experiments pkey” primary key, btree (experiment id)
Foreign-key constraints: ”$2” FOREIGN KEY (experiment type) REFERENCES experimenttypes(experiment type) DEFERRABLE
”$1” FOREIGN KEY (client code) REFERENCES clients(client code) DEFERRABLE
5.15 experimenttypes
This is a reference table that contains descriptions on the various experiment types found in the experiment table.
| Column |
Type |
Modifiers |
Description |
| experiment_type |
character
varying(50) |
not null |
Unique identifier for this experiment
type |
| description |
character
varying(200) |
|
A short description of the experiment
type |
Index: ”experimenttypes pkey” primary key, btree (experiment type)
5.16 filenote
This table contains notes the the acoustic system operator enters from time to time when collecting data.
| Column |
Type |
Modifiers |
Description |
| note_id |
integer |
not null
default 0 |
Unique identifier for the file note |
| file_id |
integer |
not null
default 0 |
The file identifier that this file
note applies to |
| date_time |
timestamp
without time
zone |
|
The date and time that this note
was created (UTC) |
| ping_num |
integer |
default 0 |
The ping number that this note
applies to |
| note |
text |
|
The note as typed in by the user |
Index: ”filenote_pkey” primary key, btree (note_id)
Foreign-key constraint: ”$1” FOREIGN KEY (file_id) REFERENCES files(file_id) ON DELETE CASCADE DEFERRABLE
5.17 files
This table contains information on each acoustic data file that has been collected, as well as links to the equipment and experiments that collected the data.
| Column |
Type |
Modifiers |
Description |
| file_id |
integer |
not null
default 0 |
Unique identifier for the file |
| file_number |
integer |
default 0 |
The number of the file. |
| transect_num |
integer |
default 1 |
The transect number label for
the file |
| stratum |
character
varying(50) |
|
The stratum label for the file |
| snapshot |
integer |
default 0 |
The snapshot label for the file |
| start_date_time |
timestamp
without time
zone |
|
The start date/time for the file
(UTC)
|
| start_lat |
double precision |
default 0 |
The start latitude for the file
(decimal degrees, positive is the
northern hemisphere) |
| start_lon |
double precision |
default 0 |
The start longitude for the file
(decimal degrees, 0 to 360 degrees,
0 is Greenwich, increasing
in an eastward direction) |
| stop_date_time |
timestamp
without time
zone |
|
The stop date/time for the file
(UTC) |
| stop_lat |
double precision |
default 0 |
The stop latitude for the file
(decimal degrees, positive is the
northern hemisphere)
|
| stop_lon |
double precision
|
default 0 |
The stop longitude for the file
(decimal degrees, 0 to 360 degrees,
0 is Greenwich, increasing
in an eastward direction)
|
| num_transmits |
integer |
default 0 |
The number of transmits in this
file |
| min_data_depth |
real |
default 0 |
The minimum range of acoustic
data in this file (metres) |
| max_data_depth |
real |
default 0 |
The maximum range of acoustic
data in this file (metres)
|
| dfile_size |
bigint |
default 0 |
The size of this file in bytes
|
| preview_path |
character varying(50) |
|
The file path to the preview image
|
| preview_name |
character
varying(50)
|
|
The filename of the preview image
for this file
|
| experiment_id |
integer
|
|
The identifier for the experiment
that this file was part of |
| equipment_id |
integer |
default 0 |
The identifier for the equipment
that was used to collect this file
|
| dfilename |
character
varying(255) |
|
The filename of the acoustic data
file, relative to the value of the
datalocation row in the globals
table |
| waterprop_id |
integer |
|
The identifier for the water properties
for the water where this
file was collected |
Index: ”files_pkey” primary key, btree (file_id)
Foreign-key constraints: ”$3” FOREIGN KEY (waterprop_id) REFERENCES waterproperties(waterprop_id) DEFERRABLE
”$2” FOREIGN KEY (experiment_id) REFERENCES experiments(experiment_id) ON DELETE CASCADE DEFERRABLE
”$1” FOREIGN KEY (equipment_id) REFERENCES equipment(equipment_id) ON DELETE CASCADE DEFERRABLE
5.18 files transect detailed
This table contains ping-by-ping position data for each transect.
| Column |
Type |
Modifiers |
Description |
| file_id |
integer |
not null |
Unique identifier for the file |
| transect_geom |
geometry |
|
The PostGIS linestring that contains
the detailed transect path |
Indexes: ”files transect detailed_pkey” primary key, btree (file_id)
”files transect detailed_spatial” gist (transect_geom)
”files transect detailed_spatial” gist (transect_geom)
Check constraints: ”$3” CHECK (geometrytype(transect_geom) = ’LINESTRING’::text OR transect_geom IS NULL)
”$2” CHECK (srid(transect_geom) = 4326)
Foreign-key constraint: ”$1” FOREIGN KEY (file_id) REFERENCES files(file_id) ON DELETE CASCADE DEFERRABLE
5.19 files transect simplified
This table contains position data for each transect that has been derived from the data in files transect detailed by use of the Douglas-Peuker line simplification algorithm.
| Column |
Type |
Modifiers |
Description |
| file_id |
integer |
not null |
Unique identifier for the file |
| transect_geom |
geometry |
|
The PostGIS linestring that contains
the simplified transect path |
Indexes: ”files transect simplified_pkey” primary key, btree (file_id)
”files transect simplified_spatial” gist (transect_geom)
”files transect simplified_spatial” gist (transect_geom)
Check constraints: ”$3” CHECK (geometrytype(transect_geom) = ’LINESTRING’::text OR transect_geom IS NULL)
”$2” CHECK (srid(transect_geom) = 4326)
Foreign-key constraint: ”$1” FOREIGN KEY (file_id) REFERENCES files(file_id) ON DELETE CASCADE DEFERRABLE
5.20 globals
This is a reference table that contains directory paths for files that are stored externally.
| Column |
Type |
Modifiers |
Description |
| name |
character
varying(255) |
not null |
The global variable name
|
| value |
character
varying(255) |
|
The global variable value
|
| comment |
text |
|
A comment to provide context to
the global variable |
Index: ”globals_ pkey” primary key, btree (name)
5.21 legacycal
This is a temporary table that is used when inserting or updating rows to the files and slplussrt tables.
| Column |
Type |
Modifiers |
| cal_id |
integer |
not null default 0 |
| depth |
real |
|
| temperature |
real |
|
| g |
double precision |
|
| v_t |
double precision |
|
| c |
double precision |
|
| transducer |
character
varying(50) |
|
| channel |
integer |
default 0 |
| comment |
character
varying(255) |
|
Index: ”legacycal_pkey” primary key, btree (cal_id)
5.22 logbooks
This table contains links to electronic copies of the acoustic transect logbooks.
| Column |
Type |
Modifiers |
Description |
| experiment_id |
integer |
default 0 |
The experiment for which this
log book was created |
| log_book_filename |
character
varying(255) |
|
The filename of this logbook.
The full pathname is obtained
by preprending the value of the
acousticlogbooklocation value
in the globals table |
Foreign-key constraint: ”$1” FOREIGN KEY (experiment_id) REFERENCES experiments(experiment_id) ON DELETE CASCADE DEFERRABLE
5.23 mbsregionslices
This table contains information on which parts of integration regions are used in an acoustic analysis run.
| Column |
Type |
Modifiers |
Description |
| start_depth |
real |
default 0 |
The start depth in metres of this
region slice |
| end_depth |
real |
default 0 |
The end depth in metres of this
region slice |
| start_vslice |
integer |
default 0 |
The start vertical slice for this region
slice |
| end_vslice |
integer |
default 0 |
The end vertical slice for this region
slice |
| bottom_id |
integer |
default 0 |
The identifier for the bottom that
this region slice applies to |
| region_id |
integer |
default 0 |
The identifier for the region that
this region slice applies to |
| cal_id |
integer |
default 0 |
The identifier of the calibration
to apply to the file that this region
slice is part of |
| channel |
integer |
default 0 |
The channel number that this region
slice applies to |
| file_id |
integer |
default 0 |
The identifier of the file that this
region slice applies to |
| sst_id |
integer |
default 0 |
The identifier of the sst that this
region slice applies to |
| sv_correction_id |
character
varying(50) |
|
The identifier of the sv correction
factor that was applied to
the file that this region slice is
part of |
Foreign-key constraints: ”$3” FOREIGN KEY (sv_correction_id) REFERENCES svcorrectionfactors(revision) ON DELETE CASCADE DEFERRABLE
”$2” FOREIGN KEY (sst_id) REFERENCES mbssst(sst_id) ON DELETE CASCADE DEFERRABLE
”$1” FOREIGN KEY (file_id) REFERENCES files(file_id) DEFERRABLE
5.24 mbsspec
This table contains information on an acoustic analysis run.
| Column |
Type |
Modifiers |
Description |
| spec_id |
integer |
not null default 0 |
Unique identifier for this mbs
specification |
| title |
character
varying(255) |
|
The title for the mbs specification |
| main_species |
character
varying(255)
|
|
The mains species that this msb
specification was created for |
| voyages |
character
varying(255) |
|
The voyages that this mbs specification
applies to. This is manually
entered |
| areas |
character
varying(255) |
|
The areas that this mbs specification
applies to. This is manually
entered |
| author |
character
varying(255)
|
|
The name of the person who created
this mbs specification |
| created |
timestamp
without time
zone |
|
The time/date that this mbs specification
was created (UTC) |
| comments |
text |
|
Comments entered when the
mbs specification was created |
| spec_name |
character
varying(255) |
|
The CVS name for the mbs specification.
Created by esp2 |
| use_exclude_regions |
boolean |
|
Whether to use regions flagged
as exclude in the mbs processing |
| motion_correction |
boolean |
|
Whether to correct for motion of
the transducer in the mbs processing |
| absorption_correction |
boolean |
|
Whether to correct for a different
acoustic absorption in the mbs
processing |
| es60_correction |
boolean |
|
Whether to correct for the systematic
triangle wave error in
Simrad ES60 data in the mbs
processing |
| default_absorption |
real |
default 0 |
The value of acoustic absorption
to use as a default |
| vertical_slice_size |
integer |
default 0 |
The default vertical slice size for
the region slices |
| cvs_revision |
character
varying(50) |
|
The CVS revision text generated
when esp2 committed this version
of the mbs specification |
| cvs_tag |
character
varying(50)
|
|
The CVS tag text generated
when esp2 committed this version
of the mbs specification |
| cvs_comment |
text |
|
The CVS comment entered by
the user when esp2 committed
this version of the mbs specification |
| cvs_modified |
timestamp
without time
zone |
|
The time at which this version of
the mbs specification was modified
(UTC) |
| cvs_author |
character
varying(50) |
|
The person who committed this
version of the mbs specification |
Index: ”mbsspec_pkey” primary key, btree (spec_id)
5.25 mbssst
This table contains information on the snapshot, stratum, and transect names used to label the echo integration data from a single acoustic data file.
| Column |
Type |
Modifiers |
Description |
| sst_id |
integer |
not null
default 0 |
Unique identifier for this sst
(snapshot, stratum, transect) |
| snapshot_integer |
default 0 |
|
The snapshot number for this sst |
| stratum |
character
varying(50) |
|
The stratum label for this sst |
| transect |
integer |
default 0 |
The transect number for this sst |
| length |
real |
default 0 |
The length in nautical miles of
this sst if one wishes to override
the automatic calculation of the
transect length |
| absorption |
real |
default 0 |
The acoustic absorption to use
for files that are part of this sst |
| spec_id |
integer |
default 0 |
The identifier of the mbs specification
that this sst is part of |
Index: ”mbssst_pkey” primary key, btree (sst_id)
Foreign-key constraint: ”$1” FOREIGN KEY (spec_id)
REFERENCES mbsspec(spec_id) ON DELETE CASCADE DEFERRABLE
5.26 padcs
This is a reference table that contains calibration information on the amplifiers in the echosounder.
| Column |
Type |
Modifiers |
Description |
| padc_id |
character
varying(50) |
not null |
Indetifier for this padc calibration |
| alias |
character varying(50) |
|
An alternative identifier for the
padc_id. |
| frequency |
double precision |
|
The frequency that this calibration
was conducted at (Hz) |
| filter_id |
character
varying(50) |
|
The identifier of the DSP filter
that was used during the calibration |
| date_measured |
timestamp
without time
zone |
|
The date during which the calibration
was performed (UTC) |
| multiplier |
integer |
|
The between channel multiplier |
| gain_channel_low |
double precision |
|
The gain of the low gain channel |
| gain_channel_high |
double precision |
|
The gain of the high gain channel |
| comment_text |
|
|
A comment on the calibration |
5.27 ping
This table contains environmental information associated with a ping.
| Column |
Type |
Modifiers |
Description |
| ping_id |
bigint |
not null
default 0 |
Unique identifier for the ping |
| file_id |
integer |
not null
default 0 |
The identifier of the file that this
ping is part of |
| ping_num |
integer |
not null
default 0 |
The ping number |
| lat |
double precision |
default 0 |
The latitude of the equipment
or ship when this ping was sent
(decimal degrees, positive is the
northern hemisphere) |
| lon |
double precision |
default 0 |
decimal degrees |
| speed |
real |
default 0 |
The speed of the vessel when
this ping was sent (knots) |
| heading_true |
real |
default 0 |
The heading of the vessel when
this ping was sent (degrees) |
| heading_apparent |
real |
default 0 |
The direction of motion of the
vessel when this ping was sent
(degrees) |
| transducer_depth |
real |
default 0 |
The depth of the transducer
when this ping was sent (metres) |
| hpr |
character
varying(80) |
|
The raw NMEA string from the
Simrad HPR system when this
ping was sent |
| bottom_distance |
real |
default 0 |
The distance between the trasducer
and the seabed derived
from this ping (metres) |
| wind_speed |
real |
default 0 |
The speed of the wind when this
ping was sent (knots) |
| wind_dir |
real |
default 0 |
The true wind direction when
this ping was sent (degrees) |
| air_temp |
real |
default 0 |
The air temperature when this
ping was sent (degrees Celcius) |
| sea_temp |
real |
default 0 |
The surface water temperature
when this ping was sent (degrees
Celcius) |
| barometric_press |
real |
default 0 |
The barometric pressure when
this ping was sent (bar) |
Index: ”ping_pkey” primary key, btree (ping_id)
Foreign-key constraint: ”$1” FOREIGN KEY (file_id) REFERENCES files(file_id) ON DELETE CASCADE DEFERRABLE
5.28 pingattitude
This table contains transducer attitude information for a given ping.
| Column |
Type |
Modifiers |
Description |
| ping_id |
bigint |
not null
default 0 |
The ping identifier that this set of
ping attitude data applies to |
| sample_num |
integer |
not null
default 0 |
The sample number that this set
of ping attitude data applies to |
| pitch |
real |
default 0 |
The transducer pitch (degrees,
positive is bow up) |
| roll |
real |
default 0 |
The transducer roll (degrees,
positive is to starboard) |
| yaw |
real |
default 0 |
The transducer yaw (degrees,
positive is to starboard) |
| heave |
real |
default 0 |
The transducer heave (metres
relative to some datam) |
Index: ”pingattitude_pkey” primary key, btree (ping_id, sample_num)
Foreign-key constraint: ”$1” FOREIGN KEY (ping_id) REFERENCES ping(ping_id) ON DELETE CASCADE DEFERRABLE
5.29 region
This table contains information about a region.
| Column |
Type |
Modifiers |
Description |
| regionsinfile_id |
integer |
default 0 |
Indentifier for the regions in file
row that this region is part of |
| region_num |
integer |
default 0 |
The region number for this region |
| type |
character
varying(50) |
|
Type of region (bottom/
surface/standalone) |
| classification |
character
varying(50) |
|
Region classification (user supplied) |
| author |
character
varying(50) |
|
Person who originally created
this region |
| slice_size |
real |
default 0 |
The horizontal slice size for this
region (m) |
| vert_slice_size |
integer |
default 0 |
The vertical slice size for this region
(pings) |
| ref_type |
character
varying(50) |
|
Whether the slices in this region
are referrenced to the surface or
the bottom |
| comments |
text |
|
Comments entered by the person
who created this region |
| shape |
character
varying(50) |
|
The shape of this region (rectangular
or polygon) |
| region_id |
integer |
not null
default 0 |
Unique identifier for this region |
| modified |
timestamp
without time
zone |
|
The time/date when this region
was modified (UTC) |
Index: ”region_pkey” primary key, btree (region_id)
Foreign-key constraint: ”$1” FOREIGN KEY (regionsinfile_id) REFERENCES regionsinfile(regionsinfile_id) ON DELETE CASCADE DEFERRABLE
5.30 regionpoints
This table contains the vertices that define a region.
| Column |
Type |
Modifiers |
Description |
| region_id |
integer |
not null
default 0 |
Identifier of the region that this
point is part of |
| ping |
integer |
default 0 |
Ping number of this point |
| depth |
double precision |
default 0 |
Depth (m) of this point |
| sequence_num |
integer |
not null
default 0 |
The sequence number of this
point. Records the sequence of
points required to construct the
region |
Indexes: ”regionpoints_pkey” primary key, btree (region_id, sequence_num)
Foreign-key constraint: ”$1” FOREIGN KEY (region_id) REFERENCES region(region_id) ON DELETE CASCADE DEFERRABLE
5.31 regionsinfile
This table contains information about the regions that have been defined for a specific acoustic data file.
| Column |
Type |
Modifiers |
Description |
| file_id |
integer |
default 0 |
Identifier for the file that the region
in file row applies to |
| author |
character
varying(50) |
|
Name of person who originally
created this regionsinfile row |
| comments |
text |
|
Comments entered when this regionsinfile
row was created |
| regionsinfile_id |
integer |
not null
default 0 |
Unique identifier for this row |
modified |
timestamp
without time
zone |
|
Date/time when this regionsinfile
row was last modified (UTC) |
| revision |
character
varying(50) |
|
CVS revision created when this
regionsinfile row was last saved |
| tag |
character
varying(50) |
|
CVS tag created when this regionsinfile
row was last saved |
Index: ”regionsinfile_pkey” primary key, btree (regionsinfile_id)
Foreign-key constraint: ”$1” FOREIGN KEY (file_id) REFERENCES files(file_id) ON DELETE CASCADE DEFERRABLE
5.32 sborigins
This is a reference table that contains information on how single channels of a splitbeam echsounder system are to be combined to produce a combined single-beam data.
| Column |
Type |
Modifiers |
Description |
| long_desc |
character
varying(255) |
|
A long description about this
channel |
| sb_channel |
integer |
not null
default 0
|
Unique identifier for this pseudo
splitbeam channel |
| ch1 |
integer |
default 0 |
The channel number for channel
1 of this splitbeam system |
| ch2 |
integer |
default 0 |
The channel number for channel
2 of this splitbeam system |
| ch3 |
integer |
default 0 |
The channel number for channel
3 of this splitbeam system |
| ch4 |
integer |
default 0 |
The channel number for channel
4 of this splitbeam system |
| add1 |
boolean |
|
Whether to add or subtract
this channel when created the
summed splitbeam channel |
| add2 |
boolean |
|
Whether to add or subtract
this channel when created the
summed splitbeam channel |
| add3 |
boolean |
|
Whether to add or subtract
this channel when created the
summed splitbeam channel |
| add4 |
boolean |
|
Whether to add or subtract
this channel when created the
summed splitbeam channel |
Index: ”sborigins_pkey” primary key, btree (sb_channel)
5.33 slplussrt
This is a table that contains details on a calibration of a specific piece of echosounding equipment.
| Column |
Type |
Modifiers |
Description |
| calibration_id |
integer |
not null
default 0 |
Unique identifier for this calibration |
| valid_from |
timestamp
without
time zone |
|
Date and time from which this
calibration is valid (UTC) |
| valid_to |
timestamp
without
time zone |
|
Date and time to which this calibration
is valid (UTC) |
| depth |
real |
default 0 |
depth (m) at which V T is valid
|
| temperature |
real |
default 0 |
temperature (degrees C) at
which V T is valid |
| g |
double precision |
default 0 |
receiver gain |
| v_t |
double precision |
default 0 |
voltage at transducer terminals for a target of unit backscattering
cross-section (1m2) at given
temperature |
| c |
double precision |
default 0 |
overall calibration constant |
| channel |
integer |
default 0 |
The Crest channel number that
this calibration applies to |
Index: ”slplussrt_pkey” primary key, btree (calibration_id)
Foreign-key constraint: ”$1” FOREIGN KEY (channel) REFERENCES daporigins(channel) DEFERRABLE
5.34 svcorrectionfactors
This is a reference table that contains a echo integration correction factor.
| Column |
Type |
Modifiers |
Description |
| revision |
character
varying(50) |
not null |
Unique identifier for this sv correction
factor |
| sv_correction_factor |
double precision |
default 0 |
sv correction factor |
| estimated_absorption_coefficient |
double precision
|
default 0 |
The absorption to use for this sv
correction factor (dB/km) |
| channel |
integer |
default 0 |
The channel that this sv correction
factor applies to |
| created |
timestamp
without time
zone |
|
The time/date that this sv correction
factor was created (UTC) |
| comments |
character
varying(255) |
|
User entered comments about
the sv correction factor |
Index: ”svcorrectionfactors_pkey” primary key, btree (revision)
5.35 t trip
This is a temporary table that contains information on a voyage. It is derived from a table of the same name in the Marine Research Trawl database. It is used when populating the voyages table.
| Column |
Type |
Modifiers
|
| trip_code |
character
varying(50) |
not null |
| proj_code |
character
varying(50) |
|
| date_s |
timestamp
without
time zone |
|
| date_f |
timestamp
without
time zone |
|
| leader |
character
varying(50) |
|
| master |
character
varying(50) |
|
| areas |
character
varying(100) |
|
| mainspp |
character
varying(100) |
|
| gear1 |
character
varying(100) |
|
| gear2 |
character
varying(100) |
|
| gear3 |
character
varying(100) |
|
| gear4 |
character
varying(100) |
|
| gear5 |
character
varying(100) |
|
| gear6 |
character
varying(100) |
|
| staff |
text |
|
Index: ”t_trip_pkey” primary key, btree (trip_code)
5.36 transducers
This is a reference table that contains information on acoustic transducers.
| Column |
Type |
Modifiers |
Description |
| transducer_id |
character
varying(50) |
not null |
Unique transducer identifier |
| manufacturer |
character
varying(50) |
|
The manufacturer of the transducer |
| model |
character
varying(50) |
|
The model name of the transducer |
| serial |
character
varying(50) |
|
The serial name of the transducer |
| frequency |
character
varying(50) |
|
The frequency that the transducer
details are applicable to
(kHz) |
| type |
character
varying(50) |
|
The transducer configuration
(dual, single, or split) |
| depth_rating |
real |
default 0 |
The manufacturer supplies maximum
operational depth for the
transducer (m) |
| beam_angle_long |
double precision |
default 0 |
The alongships 3dB beam angle
(degrees)
|
| beam_angle_trans |
double precision |
default 0 |
The athwartships 3dB beam angle
(degrees) |
| directivity |
double precision |
default 0 |
The transducer directivity |
| effective_beam_angle |
double precision |
default 0 |
The effective beam angle (steradians) |
| transmit_response |
double precision |
default 0 |
The transmit repsonse (kPa/V at
1 m) |
| receive_response |
double precision |
default 0 |
The receive response (V/kPa) |
| angle_factor |
double precision |
default 0 |
The conversion factor between
splitbeam electrical phase angle
and physical echo arrival angle |
details_document_filename
|
character
varying(255)
|
|
The filename of the document
that contains manufacturer supplied
details on the transducer.
The transducerdetailslocation
row in the globals table gives
the pathname to go with thei
filename |
| comments |
character
varying(255) |
|
Any miscellaneous comments
about this transducer |
Index: ”transducers_pkey” primary key, btree (transducer_id)
5.37 v files
This view contains all of the columns in the files table, as well as some derived columns that are of interest.
| Column |
Type |
| oid |
integer |
| file_id |
integer |
| voyage_code |
character varying(50) |
| file_number |
integer |
| transect_num |
integer |
| stratum |
character varying(50) |
| snapshot |
integer |
| start_date_time |
timestamp without time zone |
| start_lat |
double precision |
| start_lon |
double precision |
| stop_date_time |
timestamp without time zone |
| stop_lat |
double precision |
| stop_lon |
double precision |
| num_transmits |
integer |
| min_data_depth |
real |
| max_data_depth |
real |
| dfile_size |
bigint |
| preview_path |
character varying(50) |
| preview_name |
character varying(50) |
| experiment_id |
integer |
| equipment_id |
integer |
| dfilename |
character varying(255) |
| waterprop_id |
integer |
| log_book_filename |
character varying(255) |
| start_year |
double precision |
| start_month |
double precision |
| start_day |
double precision |
| start_hour |
double precision |
| start_minute |
double precision |
| start_second |
double precision |
| stop_year |
double precision |
| stop_month |
double precision |
| stop_day |
double precision |
| stop_hour |
double precision |
| stop_minute |
double precision |
| stop_second |
double precision |
| transect_geom |
geometry |
View definition: SELECT files.file id AS oid, files.file id, experiments.voyage code, files.file number, files.transect num, files.stratum, files.snapshot,
files.start date time, files.start lat, files.start lon, files.stop date time, files.stop lat, files.stop lon, files.num transmits, files.min data depth, files.max data depth,
files.dfile size, files.preview path, files.preview name, files.experiment id, files.equipment id, files.dfilename, files.waterprop id, logbooks.log book filename,
date part(’year’::text, files.start date time) AS start year, date part(’month’::text, files.start date time) AS start month,
date part(’day’::text, files.start date time) AS start day, date part(’hour’::text, files.start date time) AS start hour,
date part(’minute’::text, files.start date time) AS start minute, date part(’second’::text, files.start date time) AS start second,
date part(’year’::text, files.stop date time) AS stop year, date part(’month’::text, files.stop date time) AS stop month,
date part(’day’::text, files.stop date time) AS stop day, date part(’hour’::text, files.stop date time) AS stop hour,
date part(’minute’::text, files.stop date time) AS stop minute, date part(’second’::text, files.stop date time) AS stop second,
files.transect geom FROM files, experiments, logbooks WHERE experiments.experiment id = files.experiment id AND experiments.experiment id =
logbooks.experiment id;
5.38 v files detailed
This view contains all of the columns in the v files view, but with the full detailed transect path instead of the path defined by the start and stop positions in the transect.
| Column |
Type |
| oid |
integer |
| file_id |
integer |
| voyage_code |
character
varying(50) |
| file_number |
integer |
| transect_num |
integer |
| stratum |
character
varying(50) |
| snapshot |
integer |
| start_date_time |
timestamp
without
time zone |
| start_lat |
double precision |
| start_lon |
double precision |
| stop_date_time |
timestamp
without
time zone |
| stop_lat |
double precision |
| stop_lon |
double precision |
| num_transmits |
integer |
| min_data_depth |
real |
| max_data_depth |
real |
| dfile_size |
bigint |
| preview_path |
character
varying(50) |
| preview_ name |
character
varying(50) |
| experiment_id |
integer |
| equipment_id |
integer |
| dfilename |
character
varying(255) |
| waterprop_id |
integer |
| log_book_filename |
character
varying(255) |
| start_year |
double precision |
| start_month |
double precision |
| start_day |
double precision |
| start_hour |
double precision |
| start_minute |
double precision |
| start_second |
double precision |
| stop_year |
double precision |
| stop_month |
double precision |
| stop_day |
double precision |
| stop_hour |
double precision |
| stop_minute |
double precision |
| stop_second |
double precision |
| transect_geom |
geometry |
View definition: SELECT files.file id AS oid, files.file id, experiments.voyage code, files.file number, files.transect num, files.stratum, files.snapshot, files.start date time,
files.start lat, files.start lon, files.stop date time, files.stop lat, files.stop, files.num transmits, files.min data depth, files.max data depth, files.dfile size, files.preview path,
files.preview name, files.experiment id, files.equipment id, files.dfilename, files.waterprop id, logbooks.log book filename,
date part(’year’::text, files.start date time) AS start year, date part(’month’::text, files.start date time) AS start month,
date part(’day’::text, files.start date time) AS start day, date part(’hour’::text, files.start date time) AS start hour,
date part(’minute’::text, files.start date time) AS start minute, date part(’second’::text, files.start date time) AS start second,
date part(’year’::text, files.stop date time) AS stop year, date part(’month’::text, files.stop date time) AS stop month,
date part(’day’::text, files.stop date time) AS stop day, date part(’hour’::text, files.stop date time) AS stop hour,
date part(’minute’::text, files.stop date time) AS stop minute, date part(’second’::text, files.stop date time) AS stop second,
files transect detailed.transect geom FROMfiles, files transect detailed, experiments, logbooks WHERE files.file id = files transect detailed.file id AND experiments.
experiment id = files.experiment id AND experiments.experiment id = logbooks.experiment id;
5.39 v files endpoint
This view contains all of the columns in the v files view, but with the transect end point instead of the path defined by the start and stop positions in the transect.
| Column |
Type |
| oid |
integer |
| file_id |
integer |
| voyage_code |
character
varying(50) |
| endpoint |
geometry |
| file_number |
integer |
| transect_num |
integer |
| stratum |
character
varying(50) |
| snapshot |
integer |
| start_date_time |
timestamp
without
time zone |
| start_lat |
double precision |
| start_lon |
double precision |
| stop_date_time |
timestamp
without
time zone |
| stop_lat |
double precision |
| stop_lon |
double precision |
| num_transmits |
integer |
| min_data |
depth real |
| max_data |
depth real |
| dfile_size |
bigint |
| preview_path |
character
varying(50) |
| preview_name |
character
varying(50) |
| experiment_id |
integer |
| equipment_id |
integer |
| dfilename |
character
varying(255) |
| waterprop_id |
integer |
| log book_filename |
character
varying(255) |
| start_year |
double precision |
| start_month |
double precision |
| start_day |
double precision |
| start_hour |
double precision |
| start_minute |
double precision |
| start_second |
double precision |
| stop_year |
double precision |
| stop_month |
double precision |
| stop_day |
double precision |
| stop_hour |
double precision |
| stop_minute |
double precision |
| stop_second |
double precision |
View definition: SELECT files.file id AS oid, files.file id, experiments.voyage code, endpoint(files.transect geom) AS endpoint, files.file number, files.transect num,
files.stratum, files.snapshot, files.start date time, files.start lat, files.start lon, files.stop date time, files.stop lat, files.stop lon, files.num transmits, files.min data depth,
files.max data depth, files.dfile size, files.preview path, files.preview name, files.experiment id, files.equipment id, files.dfilename, files.waterprop id,
logbooks.log book filename, date part(’year’::text, files.start date time) AS start year, date part(’month’::text files.start date time) AS start month,
date part(’day’::text, files.start date time) AS start day, date part(’hour’::text, files.start date time) AS start hour,
date part(’minute’::text, files.start date time) AS start minute, date part(’second’::text, files.start date time) AS start second,
date part(’year’::text, files.stop date time) AS stop year, date part(’month’::text, files.stop date time) AS stop month,
date part(’day’::text, files.stop date time) AS stop day, date part(’hour’::text, files.stop date time) AS stop hour,
date part(’minute’::text, files.stop date time) AS stop minute, date part(’second’::text, files.stop date time) AS stop second FROMfiles,
experiments, logbooks WHERE experiments.experiment id = files.experiment id AND experiments.experiment id = logbooks.experiment id;
5.40 v files simplified
This view contains all of the columns in the v files view, but with the simplified transect path instead of the path defined by the start and stop positions in the transect.
| Column |
Type |
| oid |
integer |
| file_id |
integer |
| voyage_code |
character
varying(50) |
| file_number |
integer |
| transect_num |
integer |
| stratum |
character
varying(50) |
| snapshot |
integer |
| start_date_time |
timestamp
without
time zone |
| start_lat |
double precision |
| start_lon |
double precision |
| stop_date_time |
timestamp
without
time zone |
| stop_lat |
double precision |
| stop_lon |
double precision |
| num_transmits |
integer |
| min_data_depth |
real |
| max_data_depth |
real |
| dfile_size |
bigint |
| preview_path |
character
varying(50) |
| preview_name |
character
varying(50) |
| experiment_id |
integer |
| equipment_id |
integer |
| dfilename |
character
varying(255) |
| waterprop_id |
integer |
| log_book_filename |
character
varying(255) |
| start_year |
double precision |
| start_month |
double precision |
| start_day |
double precision |
| start_hour |
double precision |
| start_minute |
double precision |
| start_second |
double precision |
| stop_year |
double precision |
| stop_month |
double precision |
| stop_day |
double precision |
| stop_hour |
double precision |
| stop_minute |
double precision |
| stop_second |
double precision |
| transect_geom |
geometry |
View definition: SELECT files.file id AS oid, files.file id, experiments.voyage code, files.file number, files.transect num, files.stratum, files.snapshot, files.start date time,
files.start lat, files.start lon, files.stop date time, files.stop lat, files.stop lon, files.num transmits, files.min data depth, files.max data depth, files.dfile size,
files.preview path, files.preview name, files.experiment id, files.equipment id, files.dfilename, files.waterprop id, logbooks.log book filename, date part(’year’::text,
files.start date time) AS start year, date part(’month’::text, files.start date time) AS start month, date part(’day’::text, files.start date time) AS start day,
date part(’hour’::text, files.start date time) AS start hour, date part(’minute’::text, files.start date time) AS start minute,
date part(’second’::text, files.start date time) AS start second, date part(’year’::text, files.stop date time) AS stop year,
date part(’month’::text, files.stop date time) AS stop month, date part(’day’::text, files.stop date time) AS stop day,
date part(’hour’::text, files.stop date time) AS stop hour, date part(’minute’::text, files.stop date time) AS stop minute,
date part(’second’::text, files.stop date time) AS stop second, files transect simplified.transect geom FROMfiles, files transect simplified, experiments,
logbooks WHERE files.file id = files transect simplified.file id AND experiments.experiment id = files.experiment id AND experiments.experiment id = logbooks.experiment id;
5.41 v files startpoint
This view contains all of the columns in the v files view, but with the transect start point instead of the path defined by the start and stop positions in the transect.
| Column |
Type |
| oid |
integer |
| file_id |
integer |
| voyage_code |
character
varying(50) |
| startpoint |
geometry |
| file_number |
integer |
| transect_num |
integer |
| stratum |
character
varying(50) |
| snapshot |
integer |
| start_date_time |
timestamp
without
time zone |
| start_lat |
double precision |
| start_lon |
double precision |
| stop_date_time |
timestamp
without
time zone |
| stop_lat |
double precision |
| stop_lon |
double precision |
| num_transmits |
integer |
| min_data_depth |
real |
| max_data_depth |
real |
| dfile_size |
bigint |
| preview_path |
character
varying(50) |
| preview_name |
character
varying(50) |
| experiment_id |
integer |
| equipment_id |
integer |
| dfilename |
character
varying(255) |
| waterprop_id |
integer |
| log_book_filename |
character
varying(255) |
| start_year |
double precision |
| start_month |
double precision |
| start_day |
double precision |
| start_hour |
double precision |
| start_minute |
double precision |
| start_second |
double precision |
| stop_year |
double precision |
| stop_month |
double precision |
| stop_day |
double precision |
| stop_hour |
double precision |
| stop_minute |
double precision |
| stop_second |
double precision |
View definition: SELECT files.file id AS oid, files.file id, experiments.voyage code, startpoint(files.transect geom) AS startpoint, files.file number, files.transect num,
files.stratum, files.snapshot, files.start date time, files.start lat, files.start lon, files.stop date time, files.stop lat, files.stop lon, files.num transmits, files.min data depth,
files.max data depth, files.dfile size, files.preview path, files.preview name, files.experiment id, files.equipment id, files.dfilename,
files.waterprop id, logbooks.log book filename, date part(’year’::text, files.start date time) AS start year, date part(’month’::text, files.start date time) AS start month,
date part(’day’::text, files.start date time) AS start day, date part(’hour’::text, files.start date time) AS start hour,
date part(’minute’::text, files.start date time) AS start minute, date part(’second’::text, files.start date time) AS start second,
date part(’year’::text, files.stop date time) AS stop year, date part(’month’::text, files.stop date time) AS stop month,
date part(’day’::text, files.stop date time) AS stop day, date part(’hour’::text, files.stop date time) AS stop hour,
date part(’minute’::text, files.stop date time) AS stop minute, date part(’second’::text, files.stop date time) AS stop second FROMfiles,
experiments, logbooks WHERE experiments.experiment id = files.experiment id AND experiments.experiment id = logbooks.experiment id;
5.42 vessels
This is a reference table that contains the name and abbreviation for vessels used to collect acoustic data.
| Column |
Type |
Modifiers |
Description |
| vessel |
character
varying(50) |
not null |
The name of the vessel |
| code |
character
varying(3) |
not null |
The vessel code |
Index: ”vessels pkey” primary key, btree (code)
5.43 voyages
This is a table that contains information on voyages.
| Column |
Type |
Modifiers |
Description |
| voyage_code |
character
varying(50) |
not null |
Unique identifier for the voyage |
| vessel_code |
character
varying(3) |
|
Vessel code for the vessel used
in this voyage |
| staff |
character
varying(255) |
|
Names of scientific staff who
where on this vessel |
| leader |
character
varying(50) |
|
Name of the voyage leader. Not
included in the staff field |
| start_date |
timestamp
without time
zone |
|
The start date of the voyage |
| stop_date |
timestamp
without time
zone |
|
The stop date of the voyage |
| master |
character
varying(50) |
|
The name of the captain |
| main_species |
character
varying(50) |
|
A list of the main fish species
that this voyage was interested in |
| areas |
character
varying(50) |
|
A list of the areas that this voyage
covered |
Index: ”voyages pkey” primary key, btree (voyage code)
Foreign-key constraint: ”$1” FOREIGN KEY (vessel code) REFERENCES vessels(code) DEFERRABLE
5.44 vtvariation
This is a reference table that provides relationships between transducer calibration and temperature and depth.
| Column |
Type |
Modifiers |
Description |
| vt_variation_id |
integer |
not null
default 0 |
Unique identifier for this vt variation row |
| transducer_id |
character
varying(50) |
|
The transducer that this vt variation
applies to |
| variable |
character
varying(50) |
|
The name of the independent
variable (temperature/depth) |
| constant_term |
double
precision |
default 0 |
The constant term in the
quadratic relationship |
| linear_term |
double
precision |
default 0 |
The linear term in the quadratic
relationship
|
| quadratic_term |
double
precision |
default 0 |
The quadratic term in the
quadratic relationship |
| valid_from |
timestamp
without time
zone |
|
The date/time that this vt variation
is valid from |
| valid_to |
timestamp
without time
zone |
|
The date/time that this vt variation
is valid to |
Index: ”vtvariation pkey” primary key, btree (vt variation id)
Foreign-key constraint: ”$1” FOREIGN KEY (transducer id) REFERENCES transducers(transducer id) ON DELETE CASCADE DEFERRABLE
5.45 waterproperties
This is a table that provides water property information for given acoustic data files. This information used to more accurately intrepret the acoustic data files.
| Column |
Type |
Modifiers |
Description
|
| waterprop_id |
integer |
not null
default 0 |
Unique identifier for this water
property |
| absorption |
double
precision
|
default 0 |
Sound absorption in dB/m |
| sound_speed |
double
precision |
default 0 |
Sound speed in m/s |
| frequency |
double
precision |
default 0 |
Acoustic frequency in Hz |
Index: ”waterproperties pkey” primary key, btree (waterprop id)