Trawllog Main Page

PacFIN Coastwide Trawl Logbook subsystem Documentation

Structure of the Logbook Database

The logbook data is structured quite naturally in the following table hierarchy (actual table names in parenthesis): 

           Trip (lbk_trip)
            |       
            \--- Tow/Haul (lbk_tow)
                  |
                  \--- Catch/Hail (lbk_catch)

With multiple catch records corresponding to one tow record, and multiple tow records corresponding to one trip record.

Additionally there are code list tables which translate the agency codes for gear, port, species, strategy to the corresponding coast wide PacFIN codes (lbk_gr, lbk_pr, lbk_sp, lbk_strategy, respectively), as well as a table which links logbook records to the corresponding fish ticket records (lbk_ftid, this feature is not fully implemented yet). The whole logbook system, and its linkage to the existing PacFIN system, look like this: 

                                  pc
                                 /
                            lbk_pr
                           /  
                     lbk_trip
                     /      \                          gr              strategy
                    /        \                        /                 /
        ft         /          \        block_pos  lbk_gr      lbk_strategy
         \        /            \       /__________/____________/     
          \      /              \     /
          lbk_ftid--------------lbk_tow                sp
                                     \                 /
                                      \              asp
                                       \             /
                                        \       lbk_sp
                                         \      /
                                         lbk_catch

The following tables are related to data management: lbk_ul contains information about the updates/data feeds performed on the logbook system. reject_lbk contains information about all records which were rejected during the loading process.

Detailed column descriptions follow at the end of this document, and are also available online using the show_coldsc script.

Data provided by agencies

As of 11/18/03 the PacFIN logbook database contains logbook records obtained from CDFW (1981-2002), ODFW (1987-2003), and WDFW (1987-2002). Even though trawl vessels are required by law to submit logbook information to the state agencies, the compliance rate is less than 100%. The estimated compliance rate for the agencies are as follows:

That means that the data contained in the logbook database does NOT represent 100% of the trawl catch.

The agencies submit the follwing data:

Ticket adjustments are done differently for each agency:

WDFW's ticket adjusted pounds are not intended to mean the same as CDFW's and ODFW's. Ticket adjusted pounds from WDFW are calculated to also represent the catch caught on trips for which no logbook is available. Such that the sum of all catch in a "strata" will be the same, independent of whether the ticket data or the adjusted logbook data are used. A "strata" is defined by a given Year/month/port/area/trip_type/species. WDFW logbook data still doesn't reflect all trawl catch, as there are strata which don't contain any data from logbooks. The fish ticket only data corresponding to these strata is not included in the PacFIN logbook system. Both CDFW and ODFW give ticket adjusted weights on a trip level (i.e. the adjusted weights are supposed to mean the same. Hailed weights are adjusted using only those fish tickets resulting from the particular trip. However CDFW's and ODFW's methods for arriving at adjusted weights are not the same.

Data Quality Checks and Translations Performed During Loading

The flagging of suspicious data mentioned above is done in the following manner:

Each of the three data tables (lbk_trip, lbk_tow, and lbk_catch) contains a column WARNING which consists of fixed length strings. The n-th charcter in these warning strings is a one character warning flag for the data element in the n-th column of the table (the ordering of columns referred to is the internal Oracle order, as listed when describing a table using desc) A 0 means that there are no warnings, Any other character indicates some data anomaly. The detailed warning codelist follows below. E.g. the Warning for a lbk_trip record might look like this: 

00000000RRR00RRR000000

indicating that there is an anomaly for the data in the 9th-11th and 14th-16th columns. (The return date was before the departure date in this case. All six columns are related to either return or departure time)

Known Bugs and Shortcomings

Table Descriptions

TABLE NAME
COLUMN NAME
DESCRIPTION
lbk_trip Logbook trawl trip information
agid An agency identifier
daysfished Days fished (wdfw only)
daysout Days at sea (wdfw only)
dday Date of departure, Orcale date truncated to 12 am
dmonth Month of departure
dport Departure port
drvid A coastwide vessel identifier. Can be a USCG VID (ex: 1234567 or AK1234nn) or a ZZZ identifier indicating no vessel used, vessel id not provided, or an invalid vessel identifier provided
dtime Departure time (hhmm)
dyear Year of departure
ft_match_flag Boolean (T/F) ='T' Trip has matching fish ticket
ncrew Crewsize, including captain
ngal Fuel consumed in Gallons(Note that only 13% of all trip_id's have a fuel value entered)
ntows Number of tows on trip (wdfw only)
rday Date of return, Oracle date truncated to 12 am
region Region of trip ((wdfw only) 1=Canada, 2=Alaska, 3=Coastal, 4=Straits and Gulf, 5=Puget Sound and Hood Canal)
rmonth Month of return
rport Return port
rtime Return time (hhmm)
ryear Year of return
towsrec Number of tows recorded (wdfw only, prior to 19?? only every 4th tow was recorded)
trip_id Trip id, sequence generated
veid Vessel id as in source data
vidtype Type of veid (see vid-type in table cl)
warning Warning flag, see WARNING in table cl n-th charcter corresponds to flag for n-th column

TABLE NAME
COLUMN NAME
DESCRIPTION
lbk_tow Logbook trawl tow information
adj_towtime Wdfw adjusted tow time, represents missing logs as well, see documentation for details
agid An agency identifier
area Source agency catch area identifier
arid_psmfc PSMFC area ID, might be filled from block_pos table set position
block block number (10x10 minutes): set position
block_or ODFW 5x5 minutes block number: set position
ch_lat 1st Loran radio channel: set position
ch_long 2nd Loran radio channel: set position
depth1 1st depth in fathoms (see depth_type1)
depth2 2nd depth in fathoms (see depth_type1)
depth_type1 Type of depth1 (N=Net depth, B= Bottom depth, A=Average depth, + = Max depth, - = Min depth)
depth_type2 type of depth2 (N=Net depth, B= Bottom depth, A=Average depth, + = Max depth, - = Min depth)
duration Tow duration (up-set time) in hours
latlong_type Source of lat/long info L = Logbook entry C = Center of area, entered by agency staff B = Center position of block, from the block_pos table
lbs_adj Adjusted weight in pounds(California and Oregon) at the trip level
lbs_adjexp WDFW adjusted weight in pounds
lbs_hail Hailed weight in pounds
msec_lat Milliseconds from 1st channel: set position
msec_long Milliseconds from 2nd channel: set position
net_type Net type
pacfin_target Target species (PacFIN code)
ps_grnd_code Puget Sound ground code (see maps in "History of WA State Marine fish management areas" WDFW technical report# MRD 97-04)
ps_mgmt_area Puget Sound management area (see maps in "History of WA State Marine fish management areas" WDFW technical report# MRD 97-04)
ryear Year of return
set_lat Latitude of set position converted from degrees/minutes to degrees.decimal
set_long Longitude of set position converted from degrees/minutes to degrees.decimal
set_time Time net was set (hhmm)
target Target species/strategy code (note that only 48% of all tows have a designated target)
tow_date Date tow occurred
townum Number of tow on trip
trip_id Key to lbk_trip
up_area State area of up position
up_arid_psmfc PSMFC area ID (up position)
up_block Block number (10x10 minutes) (Up position)
up_block_or ODFW 5x5 minutes block number (up position)
up_ch_lat 1st Loran radio channel (up position)
up_ch_long 2nd Loran radio channel (up position)
up_lat Latitude of up position converted from degrees/minutes to degrees.decimal
up_long Longitude of up position converted from degrees/minutes to degrees.decimal
up_msec_lat milliseconds from 1st channel (up position)
up_msec_long Milliseconds from 2nd channel (up position)
up_time Time net was hauled up (hhmm)
warning Warning flag (see WARNING in table cl n-th charcter corresponds to flag for n-th column)

TABLE NAME
COLUMN NAME
DESCRIPTION
lbk_catch Trawl logbook catch records
agid An agency identifier
apounds Adjusted pounds(on trip level) for CDFW, ODFW, and WDFW. Applies to all three states.
apounds_calculated Values = Y/N (Y = calculated using FTL catch; N = apounds set equal to hpounds (no match))
apounds_wdfw Ticket adjusted pounds for WDFW only (representing missing logs as well)
condition Condition (possible future use)
disposition Disposition (possible future use)
grade Grade (possible future use)
hpounds Hailed pounds
source Source of column apounds (values: L = agency LBK datafeed; F = fish-ticket landed-catch using program lbk_set_apounds)
spcode Species code
townum Key to lbk_tow
trip_id Key to lbk_trip
warning Warning flag, see WARNING in table cl n-th charcter corresponds to flag for n-th column

TABLE NAME
COLUMN NAME
DESCRIPTION
lbk_sp Logbook species codes
agid An agency identifier
category Agency category code (link to asp)
created Date code was first created
description description
lbk_spcode Species code used on logbook
modified Date code was last modified

TABLE NAME
COLUMN NAME
DESCRIPTION
lbk_gr Logbook gear/net-type codes
agid An agency identifier
created Date code was first created
description Description
grid PacFIN gear or gear group code
modified Date code was last modified
net_type Gear code used on logbook

TABLE NAME
COLUMN NAME
DESCRIPTION
lbk_pr Logbook port codes
agid An agency identifier
created Date code was first created
description Description
lbk_port Port code used on logbook
modified Date code was last modified
pcid PacFIN port identifier (may be null)

TABLE NAME
COLUMN NAME
DESCRIPTION
lbk_strategy Logbook strategy codes
agid An agency identifier
created Date code was first created
lbk_strategy Strategy code used on logbook
modified date code was last modified
strategy PacFIN strategy code (link to strategy)

TABLE NAME
COLUMN NAME
DESCRIPTION
lbk_ftid Matches fish ticket ids to logbooks
agid An agency identifier
ftid Key to ft and ftl tables in fishticket system
pargrp Key to ft and ftl tables in fishticket system
source Source of tripid-ftid match (values: L = agency LBK datafeed F = FTL table using program lbk_set_apounds)
ticket_date Date on ticket (use for key to ft)
townum Key to lbk_tow in trawl logbook subsystem
trip_id Key to lbk_trip in trawl logbook subsystem

TABLE NAME
COLUMN NAME
DESCRIPTION
lbk_ul Logbook update log
agid An agency identifier
comments Comments supplied by person processng the data
data_year Year of data
datafile Location of input data file as given to loading routine
proctime Time data was loaded in DB (down to the second)
records Number of records of this type loaded
type Record type (A=adjusted pounds (WDFW only) C=catch, F= Fishticket match, H= Haul/Tow, T= Trip)

Table Joining: The following keys join the various tables:

Codelist for WARNING flags

WARNING Explanation
B Data was not supplied, filled from block_pos table
D Duplicate data (usually hail records)
I Invalid Code
L Longitude too far east (on Land)
N Value was set to Null
O Value out of valid range
R Return date before departure date
S latitude given too far south
T Long towing time at shallow depth
V Vessel not in SV table
X Dummy trip