enzh-CNfrdeeliwitjakorues

The CTD Database is currently under reconstruction. The files on this web page are database-import-ready csvs which combine 1m bin-averaged CTD sensor data with rosette bottle data. The addition of data-quality code columns requires older (than 2013) archived cruises be reformatted with data code columns added. Currently, the original CTD+bottle data 62-column data files (YY-YYMMSS_CTDFinal.zip) are being reprocessed to insure consistency of the csv format. Data codes (see the CTD Database Index for key) are added to the QC columns to flag bad or questionable CTD sensor data. These data-quality codes may be used to exclude bad or questionable data during queries.  Original data values should not change, only the format from 62 to 82 columns.

CTD data archives labeled with "QC" (YY-YYMMSS_FinalQC.zip) or "DB" (YY-YYMMSS_FinalDB.zip) are the latest format. Individual database-ready csvs will be available for each cruise so users can update an existing database on whatever platform they prefer. The addition of these new columns should not change the data presented in the earlier formats. But when a sensor measurement is data coded "9", data derived for it will change to "NaN" - estimated NO3 from the ISUS sensor, for example. Refer to the CTD Database Index and the CTD.csv with QC Index pages for columns and descriptions. As older CTD data are reprocessed to add the QC columns and data codes, the list of database-ready csvs will be updated.

 Please note that filenames have or will change to match the current CTD data distribution format. CTD.csvs processed with IEH-formatted bottle data (all cruises prior to 2012) will have an "I" denoting "IEH" in the filenames before "D.csv" or "U.csv": e.g. 20-1104SH_093301200_049Id.csv.
Preliminary versions of the 2017 database csvs are available by request (This email address is being protected from spambots. You need JavaScript enabled to view it.) but this page only lists final versions.

Two versions of the CTD final data for each cruise (YY-YY is year, MM is month, SS is ship code):

  • YY-YYMMSS_CTDFinalDB = two file subset of CTDFinalQC complete archive: datacoded downcast bottle-corrected CTD data with bottle data csv & upcast bottle-corrected CTD data plus bottle data csv.
  • YY-YYMMSS_CTDFinalQC = standard final CTD & CTD+Btl data files available on Cruise Summary pages and Recent CTD Data page. It is also made available here since it includes the database-ready CTD+Btl csvs plus plots, CTD asc & hdr files and metadata. Individual station CTD+Btl.csvs, same database-ready format, are in the csv subdirectory. 
  • Under revision - YYYY_CTDFinalQC: includes all CTD+Btl csvs for the year in two files - upcast+bottle data, downcast+bottle data - these are currently being rebuilt, stay tuned
Year Winter Cruise Spring Cruise Summer Cruise Autumn Cruise
2017  20-1701RL_CTDFinalDB
20-1701RL_CTDFinalQC
     
2016  20-1601RL_CTDFinalDB
20-1601RL_CTDFinalQC
20-1604SH_CTDFinalDB
20-1604SH_CTDFinalQC
20-1607OS_CTDFinalDB
20-1607OS_CTDFinalQC

20-1611SR_CTDFinalDB
20-1611SR_CTDFinalQC

2015  20-1501NH_CTDFinalDB
20-1501NH_CTDFinalQC
20-1504NH_CTDFinalDB
20-1504NH_CTDFinalQC
20-1507OC_CTDFinalDB
20-1507OC_CTDFinalQC
20-1511OC_CTDFinalDB
20-1511OC_CTDFinalQC
2014  20-1402SH_CTDFinalDB
20-1402SH_CTDFinalQC
20-1404OS_CTDFinalDB
20-1404OS_CTDFinalQC
20-1407NH_CTDFinalDB
20-1407NH_CTDFinalQC
20-1411NH_CTDFinalDB
20-1411NH_CTDFinalQC
2013  20-1301SH_CTDFinalDB
20-1301SH_CTDFinalQC
20-1304SH_CTDFinalDB
20-1304SH_CTDFinalQC
20-1307NH_CTDFinalDB
20-1307NH_CTDFinalQC
20-1311NH_CTDFinalDB
20-1311NH_CTDFinalQC
2012  20-1202NH_CTDFinalDB
20-1202NH_CTDFinalQC
20-1203SH_CTDFinalDB
20-1203SH_CTDFinalQC
20-1207OS_CTDFinalDB
20-1207OS_CTDFinalQC
20-1210NH_CTDFinalDB
20-1210NH_CTDFinalQC
2011 20-1101NH_CTDFinalDB
20-1101NH_CTDFinalQC
20-1104SH_CTDFinalDB
20-1104SH_CTDFinalQC
20-1108NH_CTDFinalDB
20-1108NH_CTDFinalQC
20-1110NH_CTDFinalDB
20-1110NH_CTDFinalQC
2010 20-1001NH_CTDFinalDB
20-1001NH_CTDFinalQC
     
2009        
2008        
2007        
2006        
2005        

 

CTD Database Development Notes

Please note as of mid-2015, a revision of the CTD database is currently underway. With additional data quality code columns added to the CTD+bottle csvs offered as final processed data, new files are being created by re-processing the CTD archives. Measurement values should remain unchanged but the format is being updated.

Older information about database development is described below:

Starting with CalCOFI 0907M2, the BTLvsCTD program (developed by SIO-CalCOFI) generates several database-friendly csvs. With Seasave Data Processing software v7.19, sensor calibration coefficients are embedded as xml data in the .hdr file.  BTLvsCTD.exe parses the .hdr for all stations into three csvs: hdr.csv, xmlcoeff.csv, & span.csv.

  • The .hdr csv includes the cast information such as date, time, line, sta, lat & lon, sensor configuration, and all Seasave data processing modules and coefficients applied.
  • The span.csv is a tabulation of the min and max values for each sensor, extracted from the .hdr file. This file/table will be useful for automating basic data-quality controls and sensor behavior assessments, allowing data processors to query data ranges for validity.
  • The last file is xmlcoeff.csv, a tabulation of the coefficients for sensor.

Starting with 0907, the first cruise to use dual SBE43 O2 sensors so the data tables will have these columns by default. Earlier cruises, using single O2 sensors will auto-fill the tables, leaving the second O2 columns blank. 
Since sensor installation may vary from cruise to cruise and sensors may occupy different voltage channels, the voltage references v1 v2 v3... are replaced by TransV FluorV AltV... whenever possible.This requirement required a revision of BTLvsCTD.exe dated 9 Nov 2009 since I had originally maintained the voltage # in respect to sensor.  But this creates a database import problem; v0_Trans, v1_Fluor will not integrate with v0_Fluor, v1_Trans so both are renamed TransV FluorV, removing the specific voltage # reference.

Currently, relabeling of the voltage channels is offline since it creates data reprocessing issues. You will have to refer to the hdr file to determine which sensor occupies the 8 voltage channels (V0-V7). We try to keep the channels consistent unless deploying test sensors for performance assessment. Like the RINKO Oxygen Optode on 1210NH.

  • V0 = Transmissometer
  • V1 = Fluorometer
  • V2 = PAR
  • V3 = Altimeter
  • V4 = Primary Oxygen
  • V5 = Secondary Oxygen
  • V6 = ISUS Nitrate
  • V7 = pH
  • V15 = Surface PAR, when made available by the vessel; SIO's was retired

BTLvsCTD.exe's main module also generates bottle-corrected upcast and downcast csvs, merging all the individual station up and down csvs into two files.  A dbcoeff.csv file is generated tabulating the cruise-ave bottle vs CTD coefficients as well as the station regression coefficients (each station's CTD data are regressed against bottle samples from that station, reducing the station-to-station variability witnessed using the cruise-ave coefficients).  If bottle data are not available then only cruise-ave coefficients are used to correct the CTD data.

BTLvsCTD.exe has a module to automatically relabel the v0 v1 v2... to the sensor voltage in all .asc files. (I plan on using the hdr to automate this but currently the table in BTLvsCTD tabulating voltages vs sensor is used.)

BTLvsCTD.exe has a file renaming module that renames all dYYMM###.asc & .hdr, uYYMM###.asc & .hdr files to web-format YYMM_LLLLSSSS_###d.asc & .hdr, YYMM_LLLLSSSS_###u.asc & u.hdr.

The CalCOFI CTD Database format is currently being revised (Sept 2015). Data quality columns for each sensors are being added to the database tables to flag questionable or bad data. Until this version is available, the original CTD database links will be available below.

Review the database index for column descriptions.
2005-2012 MS Access database - 2005 - 2012 final bottle-corrected CTD database

The CTD database is constructed or updated from each cruises' CTD cast files after bottle-corrections have been applied. Two tables - upcast & downcast - are in the database along with bottle data used to calibrate the sensors. Note that typically three columns are available for each sensor: Seasave-only processed sensor data - no bottle correction applied; cruise-corrected ("CruiseCorr") = bottle-corrected sensor data using coefficients derived from regressing all bottle data to sensor data; station-corrected ("StaCorr") = bottle-corrected sensor data using coefficients derived from regressing only the current station's bottle data to sensor data. Station-corrected sensor data is considered the best when 20 or more bottle samples are available to calibrate the sensor measurements. Station-correcting helps eliminate the station-to-station variability in certain sensor, particularly the ISUS when a new lamp is installed and experiencing "burn-in" drift.

CalCOFI CTD Database Data Summary
Column Header Units Description
ID Number/Long Integer Database assigned index
Project Text CalCOFI
Study Text Cruise YYMMSS
Ord_Occ Number/Integer Station order
Event_Num Number/Integer CTD-at-depth Event Number
Cast_ID Text Cruise_OrderOccupied_Up or Down
Date_Time_UTC Date/Time CTD-At-Depth GPS UTC
Date_Time_PST Date/Time CTD-At-Depth Computer PST
Lat_Dec Number/Double GPS Decimal Latitude (always N)
Lon_Dec Number/Double GPS Decimal Longitude (always W)
Sta_ID Text CalCOFI Line Station
Line Number/Double CalCOFI Line
Sta Number/Double CalCOFI Station
Depth Number/Double Depth in meters
Pressure Number/Double Pressure in decibars
Temp1 Number/Double CTD Sensor 1 Temperature deg C
Temp2 Number/Double CTD Sensor 2 Temperature deg C
TempAve Number/Double Ave CTD Sensor 1+2 Temperature deg C
Salt1 Number/Double CTD Temp 1+Conductivity 1 Salinity PSU
Salt1_Corr Number/Double CTD Temp 1+Conductivity 1 Salinity PSU Bottle Offset Corrected
Salt2 Number/Double CTD Temp 2+Conductivity 2 Salinity PSU
Salt2_Corr Number/Double CTD Temp 2+Conductivity 2 Salinity PSU Bottle Offset Corrected
SaltAve_Corr Number/Double CTD Primary+Secondary Salinity Bottle-corrected Average
Ox1 Number/Double CTD Primary Oxygen mL/L
Ox1_CruiseCorr Number/Double CTD Primary Oxygen mL/L, regression corrected using all O2 bottles
Ox1_StaCorr Number/Double CTD Primary Oxygen mL/L, regression corrected using matching station O2 bottles
Ox2 Number/Double CTD Secondary Oxygen mL/L
Ox2_CruiseCorr Number/Double CTD Secondary Oxygen mL/L, regression corrected using all O2 bottles
Ox2_StaCorr Number/Double CTD Secondary Oxygen mL/L, regression corrected using matching station O2 bottles
OxAve_StaCorr Number/Double CTD Station-corrected Primary+Secondary Oxygen Average mL/L
FluorV Number/Double Fluorometer Voltage
EstChl_CruiseCorr Number/Double Fluorometer Estimated Chl, regression calc'd using all cruise chlorophyll-a samples
EstChl_StaCorr Number/Double Fluorometer Estimated Chl, regression calc'd using matching sta chlorophyll-a samples
ISUSV Number/Double ISUS Nitrate Sensor Voltage
EstNO3_CruiseCorr Number/Double ISUS Estimated NO3, regression calc'd using all cruise NO3 samples
EstNO3_StaCorr Number/Double ISUS Estimated NO3, regression calc'd using matching sta NO3 samples
SigThetaTS1 Number/Double Sigma-theta, primary sensors, Seabird algorithm
SigThetaTS2 Number/Double Sigma-theta, secondary sensors, Seabird algorithm
BAT Number/Double Transmissometer Beam Attenuation Coefficient, Seabird algorithm
XMiss Number/Double Transmissometer %Transmission, Seabird algorithm
SPAR Number/Double Surface Photoradiometer Irradiance (unavailable on NOAA vessels)
PAR Number/Double CTD Insitu Photoradiometer Irradiance, Seabird algorithm
PoT1 Number/Double Primary Potential Temperature, Seabird algorithm
PoT2 Number/Double Secondary Potential Temperature , Seabird algorithm
DynHt Number/Double Dynamic Height, Seabird algorithm
SVA Number/Double Specific Volume Anomaly, Seabird algorithm
OxSat1 Number/Double Primary O2 Saturation, Seabird algorithm
OxSat2 Number/Double Secondary O2 Saturation, Seabird algorithm
BTL_Depth Number/Single Bottle Depth
BTL_Temp Number/Single Bottle Temperature (should match CTD Primary Temperature)
SaltB Number/Single Bottle Salinity
OxB Number/Single Bottle Oxygen
Chl-a Number/Single Bottle Chlorophyll-a
Phaeo Number/Single Bottle Phaeopigments
NO3 Number/Single Bottle Nitrate
NO2 Number/Single Bottle Nitrite
NH4 Number/Single Bottle Ammonium
PO4 Number/Single Bottle Phosphate
SIL Number/Single Bottle Silicate
Go to top
DMC Firewall is developed by Dean Marshall Consultancy Ltd