Logbook Documentatio

[Go to PacFIN Home Page] [Go to W-O-C Info Page] [W-O-C Logbook Main Page] [Comments]

PacFIN Coastwide Trawl Logbook subsystem Documentation

Structure of the Logbook Database
Data Provided by the Agencies
Data Quality Checks and Translations Performed During Loading
Known Bugs and Shortcomings
Table Descriptions

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 CDFG (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:

cdfg ~= 85%
odfw ~= 75% (unconfirmed, please confirm if you can)
wdfw ~= 95% (unconfirmed, please confirm if you can)

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

The agencies submit the follwing data:

CDFG provides data for all available logbook records. For some logbook records no corresponding fish ticket could be found. No data is submitted for trawl catch where only fish ticket but no logbook records are available.
ODFW provides only data where logbook and fish ticket data are available. (probably just means that there is always a ticket in OR ???). No data is submitted for trawl catch where only fish ticket but no logbook records are available.
WDFW provides records for which both ticket and logbook data are available, as well as data constructed from fish ticket data only. This data is given aggregated by monthly strata and is not included in the PacFIN logbook system.

Ticket adjustments are done differently for each agency:

WDFW's ticket adjusted pounds are not intended to mean the same as CDFG'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 CDFG 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 CDFG's and ODFW's methods for arriving at adjusted weights are not the same.

Data Quality Checks and Translations Performed During Loading

General rules:
- Orphan records, e.g. tow records without corresponding trip record, are rejected.
- invalid Dates are rejected (e.g. 29-Feb-95).
- values consisting of just spaces are set to NULL.
Trip data:
- set ncrew NULL if it's <1
- set ngallons NULL if it's <1
- validate port (flag if code not valid)
- check that return date after departure date (flag if not)
- validate vessel ID and get drvid from SV table, if that fails check NV table, if that fails too, generate new ZZZ drvid (which is entered into NV)
- FT_MATCH_FLAG is set to 'T'rue for ODFW, and 'F'alse only for WDFW. CDFG supplies that value. WDFW records are set to 'T', once a corresponding record in the expanded file is found.
Tow data:
- check depths > 0 (flag if not)
- depths from 1997 and later data are assumed to be average depth for
  - CDFG, always.
  - ODFW, if net_depth is given as 0.
  - WDFW, if min/max depth are the same.
- check latitude > 25N (flag if not)
- check longitude > 100W (flag if not)
- validate net type (flag if code not valid)
- validate tow duration (flag >16h && ndepth <60 fathoms)
- set duration to NULL if it's 0
- validate target/strategy against list of strategies and species (flag if code not valid)
- convert degrees/minutes to degrees.decimal
- set lat/long to center position of block (CDFG pre 1997 only)
- derive block number from set lat/lon position (CDFG 1997 and later only, except for blocks 208,209, and 210)
- after the load is completed, if PSMFC arid is missing on the record, but block is present, the arid_psmfc column is filled with the appropriate value using table block_pos. The record is flagged accordingly.
Catch data:
- replace disposition = 0 or undefined with U (can't be NULL since it's part of the key. same for condition, grade
- validate spcode (flag if code not valid)
- set 0 hailed weights to NULL
- sum up poundage of non-unique trip-tow-species records (flag)

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

Non-unique hail records: Occasionally there is more than one hail record for a given species on one tow in the data provided. In such instances the weights of all hail records for that species are added and put into one catch record into the PacFIN database, which receives a "D" (for duplicate) flag for the Hpounds column. For WDFW data these occurrences most of the time are related to an artifact in the data files provided, which lists unspecified Rockfish more than once, but with 0 lbs hail weight for the duplicate records. In ODFW these instances are likely to be data errors, but involve less than 500 lbs of catch, and are not corrected due to lack of resources. ODFW, in its internal database, follows the same procedure of adding up duplicate hail records. For CDFG data these instances are likely to be data errors as well, but are quite rare.

No Definition in Database: Code lists for the following data items are not available in the PacFIN database. Their meaning must be obtained from external sources, like agency publications or the user's pre-existing knowledge.

BLOCK_OR (5x5 minute block system employed by ODFW)
PS_MGMT_AREA (Puget Sound Management Area)
PS_GRND_CODE (Puget Sound Ground Code)

Ticket adjustment differs by agency. (see above)

Depth NULL vs. 0: While loading all values consisting exclusively of 0's are assumed to be missing values and set to NULL. However if the value is given as composition of spaces and zeros, (e.g. net_depth =' 0') it is assumed to actually mean 0. For depths ' 0' probably still means missing. However it is put in the database, and flagged as an out of range value. This could lead to a mixture of NULLs and flagged 0's in the depth field, depending on the format the data is submitted in. (That's trivial to change if desired)

0000 vs. midnight: 0000 is assumed to mean midnight when found in a time field. There are records where 0000 was given to mean "missing value". Since there is no way of knowing when this was the case, these records are given a value for midnight as well.

Table Descriptions


table      COLUMN_NAME   DESC_TEXT
---------- ------------- ---------------------------------------------
lbk_trip   ....          TABLE; trawl logbook trips

           agid          Data source agency ID

           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         derived vessel ID, usually CG#

           dtime         departure time (hhmm)

           dyear         Year of departure

           ft_match_flag   Trip has been match(T), has not been matched(F) to tickets

           ncrew         Crewsize, including captain

           ngal          Fuel consumed in Gallons

           ntows         number of tows on trip (wdfw only)

           rday          Date of return, Orcale date
                         truncated to 12 am

           region        region of trip (wdfw only)
                         1=Canada, 2=Alaska, 3=Coastal, 4=Straits&Gulf,
                         5=Puget Sound & Hood Canal

           rmonth        Month of return

           rport         return port

           rtime         return time (hhmm)

lbk_trip   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      COLUMN_NAME   DESC_TEXT
---------- ------------- ----------------------------------------------------
lbk_tow    ....          TABLE; Trawl logbook tows

           adj_towtime   wdfw adjusted tow time, represents
                         missing logs as well, see documentation for details

           agid          Data source agency ID

           area          state area

           arid_psmfc    PSCMFC area ID, might be filled from block_pos table

           block         block number (10x10 minutes)

           block_or      ODFW 5x5 minutes block number

           ch_lat        1st Loran radio channel

           ch_long       2nd Loran radio channel

           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=Logbookentry
                         C=Center of general area, entered by agency staff
                         B=Center position of block from block_pos table

           msec_lat      milli seconds from 1st channel

lbk_tow    msec_long     milli seconds from 2nd channel

           net_type      net type

           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

           set_lat       latitude of set position

           set_long      longitude of set position

           set_time      time net was set (hhmm)

           target        Target species/ strategy code

           tow_date      date tow occurred

           townum        number of tow on trip

           trip_id       Key to lbk_trip

           up_lat        latitude of up position

           up_long       longitude of 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      COLUMN_NAME   DESC_TEXT
---------- ------------- --------------------------------------------------
lbk_catch  ....          TABLE; trawl logbook catch records

           agid          Data source agency ID

           apounds       ticket adjusted pounds (on trip level)

           apounds_wdfw  ticket adjusted pounds (representing missing logs as well)

           condition     condition (possible future use)

           disposition   disposition (possible future use)

           grade         grade (possible future use)

           hpounds       hailed pounds

           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      COLUMN_NAME   DESC_TEXT
---------- ------------- ---------------------------------------------------
lbk_sp     ....          TABLE; logbook species codes

           agid          agency ID

           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      COLUMN_NAME   DESC_TEXT
---------- ------------- -----------------------------------------------
lbk_gr     ....          TABLE; logbook gear/net-type codes

           agid          agency ID

           created       date code was first created

           description   description

           grid          agency gear code (link to gr)

           modified      date code was last modified

           net_type      gear code used on logbook


table      COLUMN_NAME   DESC_TEXT
---------- ------------- -----------------------------------------------
lbk_pr     ....          TABLE; logbook port codes

           agid          agency ID

           created       date code was first created

           description   description

           lbk_port      port code used on logbook

           modified      date code was last modified

           pcid          PacFIN port code (link to pc)


table      COLUMN_NAME   DESC_TEXT
---------- ------------- ---------------------------------------
lbk_strategy  ....       TABLE; strategy codes

           agid          agency ID

           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      COLUMN_NAME   DESC_TEXT
---------- ------------- -----------------------------------------------
lbk_ftid   ....          TABLE; ftid-logbook matching tables

           agid          key to ft

           ftid          key to ft

           pargrp        key to ft

           ticket_date   date on ticket (use for key to ft)

           townum        key to lbk_tow

           trip_id       key to lbk_trip / lbk_tow



table      COLUMN_NAME   DESC_TEXT
---------- ------------- -------------------------------------------------
lbk_ul     ....          TABLE; logbook update log

           agid          data source agency ID

           comments      comments supplied by person processing 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:

  lbk_trip.trip_id --< lbk_tow.trip_id

  lbk_tow.trip_id,townum --< lbk_catch.trip_id,townum

  lbk_ftid.trip_id,townum --< lbk_tow.trip_id,townum

  lbk_trip.agid,rport --- lbk_pr.agid,lbk_port

  lbk_trip.agid,dport --- lbk_pr.agid,lbk_port

  lbk_tow.block --- block_pos.block

  lbk_tow.agid,net_type --- lbk_gr.agid,net_type

  lbk_tow.agid,target --- lbk_sp.agid,lbk_spcode

  lbk_tow.agid,target --- lbk_strategy.agid,lbk_strategy

  lbk_catch.agid,spcode --- lbk_sp.agid,lbk_spcode



Codelist for WARNING flags

WARNING         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

[Back To Logbook Index Page]