|
|
5 plankton Tables
The following are listings of the tables in the plankton database,
including attribute names, data types (and any range restrictions),
and comments.
See Appendix 1 for attributes that have comments referring to the
Trawl Instructions (unpub. NIWA report).
5.1 Table 1: t_trip
Comment: ;Profile information on all trips held in this
database.
|
Attributes
|
Data Type
|
Null?
|
Comment
|
|
trip_code
|
character(7,1)
|
No
|
Trip code - 3 char vessel name, 2 digit year and 2 digit trip
number
|
|
|
smatch "[a-z0-9][a-z0-9][a-z0-9][6-9][0-9][0-3][0-9]"
|
|
proj_code
|
character(6,1)
|
|
Project or programme code for this trip as in the management
database
|
|
|
smatch "[A-Z][A-Z][A-Z][A-Z][0-9][0-9]"
|
|
date_s
|
date(5)
|
|
Start date for the trip.
|
|
date_f
|
date(5)
|
|
Finish date for the trip
|
|
leader
|
character(20,1)
|
|
Name of trip leader
|
|
master
|
character(30,1)
|
|
Name of trip master(s)
|
|
areas
|
character(24,1)
|
|
Codes of area(s) surveyed separated by commas (,)
|
|
mainspp
|
character(15,1)
|
|
Target species code(s) separated by commas
|
|
|
smatch "{[A-Z,]}"
|
|
|
|
gear1
|
character(29,2)
|
|
Codend, liner & cover mesh sizes (mm), ground rope, sweep
& bridle lengths (m) separated by commas for 1st gear
code used
|
|
|
match "{[0-9,. ]}"
|
|
|
|
gear2
|
character(29,2)
|
|
Codend, liner & cover mesh sizes (mm), ground rope, sweep
& bridle lengths (m) separated by commas for 2nd gear
code used
|
|
|
match "{[0-9,. ]}"
|
|
|
|
gear3
|
character(29,2)
|
|
Codend, liner & cover mesh sizes (mm), ground rope, sweep
& bridle lengths (m) separated by commas for 3rd
|
|
gear code used
|
|
|
|
|
|
match "{[0-9,. ]}"
|
|
|
|
gear4
|
character(29,2)
|
|
Codend, liner & cover mesh sizes (mm), ground rope, sweep
& bridle lengths (m) separated by commas for 4th gear
code used
|
|
|
match "{[0-9,. ]}"
|
|
|
|
staff
|
text(20,60,20,1)
|
|
Name(s) of all staff on the trip
|
|
Creator:
|
dba
|
|
|
|
Indices:
|
UNIQUE trip_key BTREE ON (trip_code)
|
5.2 Table 2: t_trip_comm
|
Comment:
|
Comments for a particular trip.
|
|
Attributes
|
Data Type
|
Null?
|
Comment
|
|
trip_code
|
character(7,1)
|
No
|
Trip code as defined in the trip table
|
|
comments
|
text(60,120,60,1)
|
|
Any comments about this trip e.g., details about gear used
apart from those recorded in the trip table
|
|
Creator:
|
dba
|
|
|
|
Referential:
|
invalid trip_code (trip_code) INSERT t_trip (trip_code)
|
|
Indices:
|
NORMAL (2, 15) BTREE ON (trip_code)
|
5.3 Table 3: t_stratum
|
Comment:
|
Table of strata surveyed in all trips.
|
|
Attributes
|
Data Type
|
Null?
|
Comment
|
|
trip_code
|
character(7,1)
|
No
|
Trip code as defined in the trip table
|
|
stratum
|
character(4,1)
|
No
|
Stratum code - unique within a trip
|
|
area_km2
|
decimal(8,2)
|
|
Size of a stratum in square kilometres (km2) - must be greater
than 0 km2. > 0.00
|
|
descrptn
|
character(50,1)
|
|
Short description of the stratume.g., location, depth ranges
|
|
Creator:
|
dba
|
|
|
|
Referential:
|
invalid trip_code (trip_code) INSERT t_trip (trip_code)
|
|
Indices:
|
NORMAL (2, 15) BTREE ON (stratum) NORMAL (2, 15) BTREE ON
(area_km2) UNIQUE stra_key BTREE ON (trip_code, stratum)
|
5.4 Table 4: t_station
|
Comment:
|
Data on location, gear used and environmental conditions at
each station during a trip.
|
|
Attributes
|
Data Type
|
Null?
|
Comment
|
|
trip_code
|
character(7,1)
|
No
|
Trip code as defined in the trip table
|
|
station_no
|
integer
|
No
|
Station number - unique in a trip
|
|
categories
|
character(2,1)
|
|
Two separate 1-character user defined categories definitions
should be in trip comments
|
|
area
|
character(4,1)
|
|
Code describing area, refer to rdb:area_codes
|
|
stn_code
|
character(4,1)
|
|
Code for a permanent station occupied repeatedly
|
|
stratum
|
character(4,1)
|
|
Stratum number if the trip was a stratified survey, else a
transect code
|
|
course
|
integer
|
|
Course of vessel during the shot (course-made-good)
|
|
|
range 0 - 359
|
|
|
|
date_shot
|
date(5)
|
|
Date when gear is first deployed (dd Mmm yy format)
|
|
time_shot
|
integer
|
|
Time (24hr,NZST) when gear is first deployed
|
|
|
range 0 - 2359
|
|
|
|
fix_shot
|
character(2,1)
|
|
Method of fixing position at gear deployment. Refer to
rdb:t_fix_meth_codes
|
|
timefix_shot
|
integer
|
|
Time (in minutes) elapsed since last position fix at gear
deployment
|
|
lat_shot
|
longinteger
|
|
Latitude of vessel at gear deployment (ddmmmm format, d=deg.,
m=min. To 2 implied dec. pl.)
|
|
|
match "[3-6][0-9][0-5][0-9][0-9][0-9]"
|
|
NorS_shot
|
character(1,1)
|
|
Gear deployment position hemisphere smatch "[NS]"
|
|
long_shot
|
longinteger
|
|
Longitude of vessel at gear deployment (dddmmmm format,
d=deg., m=min. To 2 implied dec. pl.)
|
|
|
match "1[7-8][0-9][0-5][0-9][0-9][0-9]"
|
|
EorW_shot
|
character(1,1)
|
|
Gear deployment position meridian smatch "[EW]"
|
|
bot_vshot
|
integer
|
|
Depth (m) of sea bottom at vessel position at deployment
|
|
date_s
|
date(5)
|
|
Starting date of the shot (dd Mmm yy format)
|
|
time_s
|
integer
|
|
Starting time (24hr,NZST) of the shot (hhmm format)
|
|
|
range 0 - 2359
|
|
|
|
fix_s
|
character(2,1)
|
|
Method of fixing position at start of tow, refer
rdb:t_fix_meth_codes.
|
|
timefix_s
|
integer
|
|
Time (in minutes) elapsed since last position fix at the start
of tow
|
|
lat_s l
|
longinteger
|
|
Latitude of vessel at start of tow (ddmmmm format, d=deg.,
m=min. To 2 implied dec. pl.)
|
|
|
match "[3-6][0-9][0-5][0-9][0-9][0-9]"
|
|
NorS_s
|
character(1,1)
|
|
Tow start position hemisphere
|
|
|
smatch "[NS]"
|
|
|
|
long_s
|
longinteger
|
|
Longitude of vessel at start of tow (dddmmmm format, d=deg.,
m=min. To 2 implied dec. pl.)
|
|
|
match "1[7-8][0-9][0-5][0-9][0-9][0-9]"
|
|
EorW_s
|
character(1,1)
|
|
Tow start position meridian smatch "[EW]"
|
|
gear_s
|
integer
|
|
Depth (m) of gear at tow start
|
|
bot_gs
|
integer
|
|
Depth (m) of sea bottom at gear position at tow start
|
|
bot_vs
|
integer
|
|
Depth (m) of sea bottom at vessel position at tow start
|
|
date_f
|
date(5)
|
|
Finishing date of the shot (dd Mmm yy format)
|
|
time_f
|
integer
|
|
Finishing time (24hr,NZST) of the shot (hhmm format)
|
|
|
range 0 - 2359
|
|
|
|
fix_f
|
character(2,1)
|
|
Method of fixing position at finish of tow, refer
rdb:t_fix_meth_codes.
|
|
timefix_f
|
integer
|
|
Time (in minutes) elapsed since last position fix at the
finish of tow
|
|
lat_f
|
longinteger
|
|
Latitude of vessel at finish of tow (ddmmmm format, d=deg.,
m=min. To 2 implied dec. pl.)
|
|
|
match "[3-6][0-9][0-5][0-9][0-9][0-9]"
|
|
NorS_f
|
character(1,1)
|
|
Tow finish position hemisphere smatch "[NS]"
|
|
long_f
|
longinteger
|
|
Longitude of vessel at finish of tow (dddmmmm format, d=deg.,
m=min. To 2 implied dec. pl.)
|
|
|
match "1[7-8][0-9][0-5][0-9][0-9][0-9]"
|
|
EorW_f
|
character(1,1)
|
|
Tow finish position meridian smatch "[EW]"
|
|
gear_f
|
integer
|
|
Depth (m) of gear at tow finish
|
|
bot_gf
|
integer
|
|
Depth (m) of sea bottom at gear position at tow finish
|
|
bot_vf
|
integer
|
|
Depth (m) of sea bottom at vessel position at tow finish
|
|
min_gdepth
|
integer
|
|
Min depth (m) of lowest part of gear during tow
|
|
max_gdepth
|
integer
|
|
Max depth (m) of lowest part of gear during tow
|
|
gear_meth
|
character(2,1)
|
|
Gear method code, descriptions in rdb:meth_codes
|
|
gear_code
|
smallint
|
|
Code for set of gear used, details in trip record. Also used
as a link to t_gear_unit
|
|
gear_units
|
smallint
|
|
Number of units of gear used in the tow
|
|
gear_perf
|
smallint
|
|
Code for performance of gear during the tow, refer to the
trawl instructions
|
|
|
range 1 - 4
|
|
|
|
speed
|
decimal(3,1)
|
|
Average speed through water during shot (knots)
|
|
distance
|
decimal(4,2)
|
|
Distance of gear over bottom (nautical miles)
|
|
warp_lgth
|
integer
|
|
Length of warp during the tow (m)
|
|
wind_dir
|
integer
|
|
Wind (true) direction 999=No wind range 0 - 359, = 999
|
|
wind_force
|
smallint
|
|
Wind force on Beaufort scale range 0 - 12
|
|
air_temp
|
decimal(3,1)
|
|
Air temperature (degrees C)
|
|
air_press
|
decimal(5,1)
|
|
Air pressure (millibars)
|
|
cloud_cov
|
smallint
|
|
Code describing cloud cover during tow refer to trawl
instructions
|
|
|
range 0 - 8
|
|
|
|
sea_cond
|
smallint
|
|
Code describing condition of sea, refer trawl instructions
|
|
|
range 0 - 9
|
|
|
|
sea_col
|
smallint
|
|
Code describing colour of sea, refer trawl instructions
|
|
|
range 1 - 8
|
|
|
|
swell_ht
|
smallint
|
|
Code describing height of swell, refer trawl instructions
|
|
|
range 1 - 3
|
|
|
|
swell_dir
|
integer
|
|
Direction (true) of the swell range 0 - 359, i= 999
|
|
bot_type
|
smallint
|
|
Code describing sea bottom type, refer trawl instructions
|
|
|
range 0 - 9
|
|
|
|
bot_cont
|
smallint
|
|
Code describing sea bottom contour, refer trawl instructions
range 0 - 5
|
|
surf_temp
|
decimal(3,1)
|
|
Surface temperature (degrees C)
|
|
bot_temp
|
decimal(3,1)
|
|
Temperature at bottom (degrees C)
|
|
wind_spd
|
smallint
|
|
Wind speed (m/s) from anemometer (1kt=0.51m/s)
|
|
secchi
|
smallint
|
|
Depth when Secchi disc is invisible (m) other character(6,1)
Any other details, should be fully commented.
|
|
Creator:
|
dba
|
|
|
|
Referential:
|
invalid trip_code (trip_code) INSERT t_trip (trip_code)
invalid area code (area) INSERT rdb : area_codes (code)
invalid fix_s code (fix_s) INSERT rdb : t_fix_meth_codes
(fix_meth_code)
invalid fix_f code (fix_f) INSERT rdb : t_fix_meth_codes
(fix_meth_code)
invalid gear code (gear_meth) INSERT rdb:meth_codes (code)
|
|
Indices:
|
UNIQUE BTREE stat_key ON (trip_code, station_no)
NORMAL (2, 15) BTREE ON (station_no)
|
5.5 Table 5: t_stat_comm
|
Comment:
|
Comments for a station in a trip.
|
|
Attributes
|
Data Type
|
Null?
|
Comment
|
|
trip_code
|
character(7,1)
|
No
|
Trip code as defined in the trip table
|
|
station_no
|
integer
|
No
|
Station number - unique in a trip
|
|
comments
|
text(60,120,60,1)
|
|
Comments for this station - should include comments about
catch data or any special action taken during tow
|
|
Creator:
|
dba
|
|
|
|
Referential:
|
invalid trip_code, station_no (trip_code, station_no) INSERT
t_station (trip_code, station_no)
|
|
Indices:
|
NORMAL (2, 15) BTREE ON (trip_code)
NORMAL (2, 15) BTREE ON (station_no)
|
5.6 Table 6: t_wire_angle
|
Comment:
|
Details of wire angles (from the vertical) of the gear wire
during retrieval. Used prior to depth-meters to estimate the
track of the gear during use.
|
|
Attributes
|
Data Type
|
Null?
|
Comment
|
|
trip_code
|
character(7,1)
|
No
|
Trip code as defined in the trip table
|
|
station_no
|
integer
|
No
|
Station number - unique in a trip
|
|
wire_out
|
integer
|
|
Metres of wire out.
|
|
wire_angle
|
integer
|
|
Angle (from the vertical) of the wire.
|
|
|
Range 0 - 90
|
|
|
|
Creator:
|
dba
|
|
|
|
Referential:
|
invalid trip_code, station_no (trip_code, station_no) INSERT
t_station (trip_code, station_no)
|
|
Indices:
|
UNIQUE BTREE wire_angle_pk ON (trip_code, station_no,
wire_out)
|
5.7 Table 7: t_gear_unit
|
Comment:
|
Details of an individual gear when stations have multiple gear
units.
|
|
Attributes
|
Data Type
|
Null?
|
Comment
|
|
trip_code
|
character(7,1)
|
No
|
Trip code as defined in the trip table
|
|
station_no
|
integer
|
No
|
Station number - unique in a trip
|
|
gear_code
|
smallint
|
No
|
Unique number to identify a gear unit within a station.
|
|
mesh_size
|
decimal(4,3)
|
|
Smallest mesh size (mm) of the gear.
|
|
no_bottles
|
smallint
|
|
Number of sample bottles attached to the gear.
|
|
warp_lgth
|
integer
|
|
Length of warp (m) for gear unit during tow.
|
|
Creator:
|
dba
|
|
|
|
Referential:
|
invalid trip_code, station_no (trip_code, station_no) INSERT
t_station (trip_code, station_no)
|
|
Indices:
|
UNIQUE BTREE gear_unit_pk ON (trip_code, station_no,
gear_unit)
|
5.8 Table 8: t_eggs
|
Comment:
|
Table for egg stages and abundance for each species in each
station on a trip
|
|
Attributes
|
Data Type
|
Null?
|
Comment
|
|
trip_code
|
character(7,1)
|
No
|
Trip code as defined in the trip table
|
|
station_no
|
integer
|
No
|
Station number - unique in a trip
|
|
species
|
character(3,1)
|
No
|
3-char species code, refer to rdb:curr_spp
|
|
samp_no
|
smallint
|
|
Sequential number to identify each sub-sample of a species
taken from the whole catch for that species
|
|
percent_samp
|
smallint
|
|
Sampling percentage associated with this record
|
|
stage
|
integer
|
|
Egg development stage for this species
|
|
no_a
|
longinteger
|
|
Total count of eggs for this stage for this species
|
|
Creator:
|
dba
|
|
|
|
Referential:
|
invalid trip_code, station_no (trip_code, station_no) INSERT
t_station (trip_code, station_no)
invalid species code (species) INSERT rdb:curr_spp (code)
|
|
Indices:
|
NORMAL (2, 15) BTREE ON (station_no)
NORMAL (2, 15) BTREE ON (species)
NORMAL (2, 15) BTREE ON (stage)
NORMAL (2, 15) BTREE ON (trip_code)
|
5.9 Table 9: t_abund
|
Comment:
|
Table for abundance of species taken from samples of catches
from plankton trawls
|
|
Attributes
|
Data Type
|
Null?
|
Comment
|
|
trip_code
|
character(7,1)
|
No
|
Trip code as defined in the trip table
|
|
station_no
|
integer
|
No
|
Station number - unique in a trip
|
|
species
|
character(3,1)
|
No
|
3-char species code, refer to rdb:curr_spp
|
|
samp_no
|
smallint
|
|
Sequential number to identify each sub-sample of a species
taken from the whole catch for that species
|
|
no_a
|
longinteger
|
|
Total count of a species in a sample
|
|
Creator:
|
dba
|
|
|
|
Referential:
|
invalid trip_code, station_no (trip_code, station_no) INSERT
t_station (trip_code, station_no)
invalid species code (species) INSERT rdb:curr_spp (code)
|
|
Indices:
|
NORMAL (2, 15) BTREE ON (trip_code)
NORMAL (2, 15) BTREE ON (station_no)
NORMAL (2, 15) BTREE ON (species)
|
|