California Cooperative Oceanic Fisheries Investigations

The CalCOFI Hydrographic Database contains formatted IEH ascii data plus additional derived data published in the CalCOFI Hydrographic Data Reports. The legacy IEH format removes all decimal points and places data in specific columns. These data are re-formatted back into decimal form and imported into an Access database. Data column format & units are described in the IEH format description.

CalCOFI Hydrographic Data are available as downloadable files in several formats (mdb, xml, csv, sql dump) All online versions of the database includes final, published data only. A research version with preliminary data is available by request from the CalCOFI data manager.:

• 1949-201202 CalCOFI Hydrographic Data Report Database (mirror; MS Access 2003 version; last update 04Oct2012 zip) - Includes final hydrographic data from 1949-2012 (Through 1202NH).
• CSV - 194903 to 201202 Cast & Bottle Tables as CSV (mirror; 04Oct2012 zip)
• XML - 194903 to 201202 Cast and Bottle XML files (mirror;  04Oct2012  zip) - Cast and Bottle tables as separate XML-XSD files
• SQL - 194903 to 201202 Access to MySQL 'dump' SQL file (mirror;  04Oct2012  zip) - Complete web version, includes Cast, Bottle, Cruise, Standard Station, Standard Depth, and metadata tables, uncompresses to over 870mb

Newest fixes and additions:

04 Oct 2012

• CalCOFI 1110NH final hydrographic data imported into database
• CalCOFI 1202NH final hydrographic data imported into database

• CalCOFI 1104NH final hydrographic data imported into database
• CalCOFI 1108NH final hydrographic data imported into database

 17 Apr 2012

• Missing O2 saturation in hydrographic data added
• Missing decimal (tenth) value in O2 ml/L added

• CalCOFI 1011NH final hydrographic data imported into database
• CalCOFI 1101NH final hydrographic data imported into database
• Updated the Units Table

• Added final 1008NH CalCOFI data
• Added final 1009SH CalCOFI data

17 Nov 2010

• Fixed ship codes: David Phillip Dolphin changed from 31DP to 33DP; Alejandro de Humboldt changed from 65AH to 57VA; Wecoma changed from Westwind, 31WE to 32WC; Sproul changed from 31QU to 32QU.
• Fixed same ship codes in Cruise_ID field and a few others parsed incorrectly or misspelled.

1 Sept 2010

• Fixed date issue on 200104.  All dates were off by -24 hours.
• Added missing stations 80.0 100.0 and 90.0 100.0 to 200911.
• Added finalized data from cruises 201001 and 201004.

08 Jul 2010

• Please refer to the Work_Done table in the database for the latest corrections, changes, & additions.
• Added content to Cruise_ID column: YYYY-MM-DD-Project Code-Ship Code
• Changed ShipName to Ship_Name in Cast table to match the 0-Ships table and establish a relational link
• Changed the Ship_Code to 4 characters only; UNOLS 2 digit country code + 2 char ship code
• Fixed 191001 glitch to 201001 in preliminary database

18 Mar 2010

• Added Cruise_ID, YYYY-MM-DD-ProjectCode-ShipCode, column to cast table
• Changed the Ship_Code column which has historically been Country Code + Institution Code + Ship Code to Country Code + Ship Code because Institution is missing in most of the IEH archives.
• Revised the Ship Table: original two char ShipCode is now Ship, ShipCode is new standard country code+ship code
• Added 0911 final hydrographic data to the database

17 Dec 2009

• Salinity decimal accuracy set to 3 sig figs
• Sigma-theta decimal accuracy set to 3 sig figs
• Cast_ID Julian Day, Time, Data_Type corrected on several casts
• Depth_ID corrected to match Cast_ID corrections
• Prodo_Cast table - removed Cast_ID, not needed since Cast_Cnt relation links Cast table Cast_ID

07 Dec 2009

• Cruise 198107 changed to 198108 as reported by NMFS ichthyoplankton database
• Cruise 199007 changed to 199008 as reported by NMFS  ichthyoplankton database
• Shipcode edit; removed two spaces between country code and ship code

18 November 2009

• Decimal Latitude and Longitude decimal values recalculated to 5 decimal places from Lat_Deg+(Lat_Min/60) and Lon_Deg+(Lon_Min/60).
• Updated online versions of database & csvs with recalculated lat & lon columns
• Added xml and mysql dump download links.

21 October 2009

• Cast_ID column in the Cast Table has been synchronized with the Ship_Code column from the Cast Table.  Depth_ID column in the Bottle table has been synchronized with the Ship_Code column from the Cast Table.

14 May 2009

• Correction were made to several 0707 bottle data records after comparison plots with CTD profiles identified flyer

1 Jan 2008

• CalCOFI Data Reports and IEH ascii files include data above line 76.7 when collected (usually during the winter and spring CalCOFIs)

8 Sep 2006

• Cruz_Sta value does not include Ship Code. Current format is CCYYMMLLLLSSSS where CC is century (19 or 20); YYMM is the usual year-month cruise designation (ex 0602); LLLL is Line; SSSS is Sta. Line & Sta format include the "tenths" value ie Lines are not rounded: line 93 is reported as 93.3, line 87 is 86.7. Santa Barbara Basin is 81.8 46.9, not 82 47. These changes were necessary to distinguish SCCOOS stations from standard stations. This change is reflected in the 0602 IEH file. Please note that pre-2008 IEH data does not include data collected above line 76.7. This allows data collected on different ships occupying the same station to link. Some older cruises performed the hydrographic work and biological work on separate ships.

• Order_Occ column added to Cast table. This will be backfilled from the zooplankton database since IEH data does not include this value until 2000.

20 Jan 2006

• Added a standard station field to the Station_ID table. ST in the Stnd_Sta column flags the current 66 quarterly-sampled stations.
• Fixed Cast_Data Table: Cruz_Num & Int_Chl errors fixed. Parsing error combined the fields for several 80s cruises
• Metafile Description - definitions and format
• Design Notes - comments on establishing a standard format and tables for the 50+ years of data

Comments -

CalCOFI 1949 - 2009 IEH-formatted ascii files are sorted by cruise, parsed into two comma-delimited ascii files (cast information; bottle data), combined with CalCOFI Data Report calculated values, then imported into Access 2003. Some common corrections are required: the cruise YYMM designation such as 9801 are extracted from the IEH header. If that field is empty (common in early IEHs) the cruise designation (variable Cruz_Nam) is generated from Year and Month automatically. Any query that uses the cruise designation (Cruz_Nam) may not pull all data collected from early cruises so use date and ship instead.  Another note - Gulf of California station latitudes & longitudes generate negative Line.Sta numbers using the CalCOFI Line.Sta from Lat.Lon algorithm.  These data are not excluded so the Line & Sta fields are not always numeric but text since a “G” designation is reported in many IEHs for those stations.
JRW 2009