5 rocklob tables

The following are listings of the tables in the rocklob 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_locations

Comment: Lists the location used for the deployment of pueruli collectors, the location code, and the method of collector deployment.

Attributes	Data Type	Null?	Comment

location	character(3,1)	No	3-char code for the location
			of the collector

descrptn	character(25,1)		Location name or description.

deploy_meth	character(5,1)		Method of collector
			deployment used at the
			location.
	smatch 'BOAT\|SHORE'
Creator:	dba
Indices:	UNIQUE BTREE location_codes_pk ON (location)

5.2 Table 2: t_catch

Comment: Shows number of pueruli caught on each collector at each check, with comments.

Attributes	Data Type	Null?	Comment

location	character(3,1)	No	3-char code for the location of the collector
date_checked	date(5)	No	Date the collector was checked
collector_no	smallint	No	Each collector has been given a unique number
no_catch	smallint		Number of pueruli caught
leader	character(20,1)		Name of trip leader
notes	text(50,100,50,1)		Comment for each collector

Creator:	dba
Indices:	NORMAL (2, 15) BTREE catch_location_ndx ON (location)
	NORMAL (2, 15) BTREE catch_date_checked_ndx ON
	(date_checked)
	NORMAL (2, 15) BTREE catch_collector_no_ndx ON
	(collector_no)

5.3 Table 3: t_puer_lfreq

Comment: Table of pueruli length frequency data.

Attributes	Data Type	Null?	Comment

location	character(3,1)	No	3-char code for the location of the collector
date_checked	date(5)	No	Date the collector was checked
collector_no	smallint	No	Each collector has been given a unique number
measurer	character(20,1)		Person who recorded the length of the animals
m_date	date(5)		Measurement date
len_p	decimal(3,1)		Carapace length of the puerulus
no_p	smallint		Number of puerulus
no_pp	smallint		Number of post puerulus
box	character(8,1)		Box and container number where the animals are kept
notes	text(50,0,50,1)

Creator:	dba
Indices:	NORMAL (2, 15) BTREE lfreq_location_ndx ON (location)
	NORMAL (2, 15) BTREE lfreq_date_checked_ndx ON
	(date_checked)
	NORMAL (2, 15) BTREE lfreq_collector_no_ndx ON
	(collector_no)

5.4 Table 4: t_puer_stage

Comment: Table of pueruli life history stage data.

Attributes	Data Type	Null?	Comment

location	character(3,1)	No	3-char code for the location of the collector
date_checked	date(5)	No	Date the collector was checked
collector_no	smallint	No	Each collector has been given a unique number
stage	smallint		Numeric code for life history stage
no_a	integer		Number of animals at this stage.

Creator:	dba
Indices:	NORMAL (2, 15) BTREE puer_stage_location_ndx ON
	(location)
	NORMAL (2, 15) BTREE puer_stage_date_checked_ndx ON
	(date_checked)
	NORMAL (2, 15) BTREE puer_stage_collector_no_ndx ON
	(collector_no)

5.5 Table 5: 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)	No	Project or programme code.
	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 vessel 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, . ]}”
staff	text(20,60,20,1)		Name(s) of all staff on the trip

Creator:	dba
Indices:	UNIQUE BTREE trip_key ON (trip_code)

5.6 Table 6: t_station

Comment: Data on location, gear used and environment at 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 within a trip
categories	character(2,1)		2 separate 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 trip is a stratified survey, else a transect code.
course	integer		Course of vessel during the shot (course-made-good).
	range 0-359
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

lat_s	longinteger		Latitude of vessel at start 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 lowest part of gear (groundrope) at the start of tow.
bot_gs	integer		Depth (m) of sea bottom at gear position at start of the tow.
bot_vs	integer		Depth (m) of sea bottom at vessel position at start of the tow.
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 end of tow, refer rdb:t_fix_meth_codes.
timefix_f	integer		Time (in minutes) elapsed since last position fix at end of the tow.
lat_f	longinteger		Latitude of vessel at end 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 end 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 lowest part of gear (groundrope) at end of the tow.
bot_gf	integer		Depth (m) of sea bottom at gear position at end of tow.
bot_vf	integer		Depth (m) of sea bottom at vessel position at end of tow.
min_gdepth	integer		Minimum depth (m) of lowest part of gear (groundrope) during the tow.
max_gdepth	integer		Maximum depth (m) of lowest part of gear (groundrope) during the 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.
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
path	smallint		Code describing configuration of path of shot, refer to the trawl instructions.
	range 1-8
speed	decimal(3,1)		Average speed through water during shot (knots).
distance	decimal(4,2)		Distance of gear over bottom (nautical miles).
head_ht	decimal(3,1)		Average headline height (m).
head_code	character(1,1)		Code showing how headline height was determined, refer to rdb:t_headline_codes.
dist_wings	decimal(4,1)		Average distance between wings (m).
distwing_code	character(1,1)		Code to indicate how distance between the wings was determined for this tow, refer rdb:t_wing_dist_codes.
dist_doors	decimal(4,1)		Average distance between doors of gear (m).
distdoor_code	character(1,1)		Code to indicate how the distance between the doors was determined for this tow, refer rdb:t_door_dist_codes.
warp_lgth	integer		Length of warp during the tow
wind_dir	integer		Wind direction (degrees true), 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 to trawl instructions.
	range 0-9
sea_col	smallint		Code describing colour of sea, refer to trawl instructions.
	range 1-8
swell_ht	smallint		Code describing height of swell, refer to trawl instructions.
	range 1-3
swell_dir	integer		Direction of the swell (degrees true).
	range 0-359, 999
bot_type	smallint		Code describing sea bottom type, refer to trawl instructions.
	range 0-9
bot_cont	smallint		Code describing sea bottom contour, refer to 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 from anemometer (m/s) (1knot=0.51m/s).
secchi	smallint		Depth at which Secchi disc becomes invisible (m).
moon	integer		Quarters of the moon phase.
	range 1-4
mesh_size	decimal(5,3)		Mesh size (cm) of the gear.
other	character(6,1)		Any other details, should be fully commented.
user1	character(1,1)		User-defined field 1. Field should be defined in t_stat_comm.
user2	character(1,1)		User-defined field 2. Field should be defined in t_stat_comm.
user3	character(1,1)		User-defined field 3. Field should be defined in t_stat_comm.
tot_vol	decimal(4,1)		Total volume (cubic metres) of material caught during tow.
vol_samp	decimal(4,1)		Volume (cubic metres) of material sampled.
phy	integer		Number of phyllosomas caught.
puer	integer		Number of pueruli caught
ibacus	integer		Number of ibacus sp. caught.
scyllarus	integer		Number of scyllarus sp.caught.

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) invalid headline code (head_code) INSERT rdb : t_headline_codes (headline_code) invalid distwing code (distwing_code) INSERT rdb : t_wing_dist_codes (wing_dist_code) invalid distdoor code (distdoor_code) INSERT rdb : t_door_dist_codes (door_code) invalid fish_locn (fish_locn) INSERT rdb : t_fish_obs_codes (fish_obs_code)
Indices:	UNIQUE BTREE stat_key ON (trip_code, station_no) NORMAL (2, 15) BTREE stat_station_no_ndx ON (station_no) NORMAL (2, 15) BTREE stat_gear_meth_ndx ON (gear_meth)

5.7 Table 7: 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 in the trip table

station_no	integer	No	Station number as in station table

comments	text(60,120,60,1)		Comments for this station – should include comments about catch 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 scom_trip_code_ndx ON (trip_code) NORMAL (2, 15) BTREE scom_station_no_ndx ON (station_no)

5.8 Table 8: t_phy_stage

Comment: Phyllosoma life history stage table.

Attributes	Data Type	Null?	Comment

trip_code	character(7,1)	No	Trip code as in the trip table.
station_no	integer	No	Station number as in station table.
species	character(3,1)		3-char species code, refer rdb:curr_spp.
stage	smallint		Numeric code for life history stage
no_a	integer		Number of animals at this stage.

Creator:	dba
Referential:	no trip-code, station (trip_code, station_no) INSERT t_station (trip_code, station_no)
Indices:	NORMAL (2, 15) BTREE phy_stage_trip_code_ndx ON (trip_code) NORMAL (2, 15) BTREE phy_stage_station_no_ndx ON (station_no) NORMAL (2, 15) BTREE phy_stage_stage_ndx ON (stage)

5.8.1 v_scyllarus

Comment: View of all scyllarus (SHL) phyllosoma life history stage data.

View:	select attr 'trip_code', attr 'station_no', attr 'species', attr 'stage', attr ‘no_a’ from 't_phy_stage' where (attr 'species' = 'SHL')

Attributes	Data Type	Null?	Comment

trip_code	character(7,1)	No
station_no	integer	No
species	character(3,1)
stage	smallint
no_a	integer

5.8.2 v_jasus

Comment: View of all jasus (PHY) phyllosoma life history stage data.

View:	select attr 'trip_code', attr 'station_no', attr 'species', attr 'stage', attr "no_a" from 't_phy_stage' where (attr 'species' = 'PHY')

Attributes	Data Type	Null?	Comment

trip_code	character(7,1)	No
station_no	integer	No
species	character(3,1)
stage	smallint
no_a	integer

5.9 Table 9:t_evpsumm

Comment: Table storing start/finish times/positions of stations/envelopes.

Attributes	Data Type	Null?	Comment

trip_code	character(7,1)	No	Standard 7 char code for the trip
evp_no	integer	No	Number to identify an envelope of DAS data
station_no	integer		Number for station assigned to envelope
lat_s	double precision	No	Latitude for start of envelope/station
long_s	double precision	No	Longitude for start of envelope/station
time_s	time(0)	No	Time at start of envelope/station
time_f	time(0)	No	Time at finish of envelope/station

Creator:	dba
Referential:	Invalid trip (trip_code) INSERT t_trip (trip_code)
Indices:	UNIQUE evpsumm_pk ON (trip_code, evp_no)
	NORMAL (2, 15) BTREE evpsumm_evp_no_ndx ON (evp_no)
	NORMAL (2, 15) BTREE evpsumm_station_no_ndx ON
	(station_no)

5.10 Table 10: t_dasdata

Comment: Table storing filtered records from DAS envelopes.

Attributes	Data Type	Null?	Comment

trip_code	character(7,1)	No	Standard 7 char code for the trip
evp_no	integer	No	Number to identify an envelope of DAS data
station_no	integer	No	Number for station assigned to envelope
tally_id	character(12,1)	No	Label assigned by REACT to identify records
timestamp	time(0)	No	Rounded timestamp when observation was made
obs	double precision	No	Value of observation for tally_id at timestamp

Creator:	dba
Referential:	Invalid trip (trip_code) INSERT t_trip (trip_code)

5.11 Table 11: t_stage_codes

Comment: Descriptions of crayfish development stages from phyllosomalarvae to juveniles.

Attributes	Data Type	Null?	Comment

species	character(3,1)	No	3-char species code, refer rdb:curr_spp.
type	character(10,1)	No	Flag to denote whether the stage is for a phyllosoma or a puerulus.
	smatch “PHYLLOSOMA\|PUERULUS”
stage	character(5,1)	No	Code for phyllosoma larvae development stage.
descrptn	text(80,20,20,1)		Description of the puerulus development stage code.
key	character(10,1)		Group number in which a stage Belongs. Used by some researchers to group similar stages together into one ‘super stage’ .
Lesser_stage	character(10,1)		Stage, as defined by Lesser, J.H.R 1978. Phyllosoma larvae of Jasus edwardsii and their distribution off the east coast of North Island. N.Z. Journal of Marine and Freshwater Research 12 (4): 357-70.

Creator:
Indices:	UNIQUE BTREE stage_codes_pk ON (species, type, stage)

5.11.1v_puer_stage_codes

Comment: View of descriptions of development stages of rock lobstersfrom puerulus to juveniles.

View:	select attr ‘species’, attr ‘stage’, attr ‘descrptn’ from
	‘t_stage_codes’ where (attr ‘type’ = ‘PUERULUS’)

Attributes	Data Type	Null?	Comment

species	character(3,1)	No	3-char species code, refer rdb:curr_spp
stage	character(5,1)	No	Development stage code.
descrptn	text(80,20,20,1)		Description of the puerulus development stage code

5.11.2 v_phy_stage_codes

Comment: View of descriptions of development stages of phyllosoma larvae.

View:	select attr ‘species’, attr ‘stage’, attr ‘descrptn’, attr
	‘key’, attr ‘Lesser_stage’ from ‘t_stage_codes’ where
	(attr ‘type’ = ‘PHYLLOSOMA’)

Attributes	Data Type	Null?	Comment

species	character(3,1)	No	3-char species code, refer
stage	character(5,1)	No	Code for phyllosoma larvae development stage.
descrptn	text(80,20,20,1)		Description of the puerulus development stage code.
key	character(10,1)		Group number in which a stage Belongs. Used by some researchers to group similar stages together into one ‘super stage’.
Lesser_stage	character(10,1)		Stage, as defined by Lesser, J.H.R 1978. Phyllosoma larvae of Jasus edwardsii and their distribution off the east coast of North Island. N.Z. Journal of Marine and Freshwater Research 12 (4): 357-70.

Previous | Next

Updated : 16 November 2007