Database Management - David Court

Current Tasks

WORK-WEEK 12. - 16. JANUARY 2015

I. Assist Nathalie with new SeaBASS submission requirements SeaBASS now requires that IOP submissions include absorption calculations

1. Status: complete
2. New script (iop2sb-with-abs-calculations) was completed on Friday 16. January
3. Script can be found in /home/oceancolor/Plumes_and_Blooms/processing_scripts

OUTLINE OF THE NEW GLOBAL DATABASE: HOW IS IT BUILT?

I. Import ALL radiometric, pigment, and absorption data from SeaBASS

1. Use the Bio-Optical Search Tool
2. Search by product: Ed, Lu, CHL, ap, ag, ...

For Radiometric Data (AOPs):

II. Extract the data from its SeaBASS format (PERL).

1. Save as hyperspectral values - but round to nearest nm
   1. Lu412.2 = Lu412
   2. Es682.6 = Es683
   3. Separate values into evenly spaced averaged bins
      1. Lu412 = average of Lu411, Lu412, Lu413
      2. Es684 = average of Es683, Es684, Es685

III. Map data to ensure the measurements are from appropriate locations

1. Eliminate data where water depth and/or secchi depth are too shallow
2. Eliminate data from estuaries, bays, etc

IV. Prioritize data that include a 'surface' (0-) value

V. Create a separate file for profiles that do not include surface values

1. Perform regression to calculate surface values (IDL)
   1. Plot each profile - but separate by appropriate channels (UV, Visible, NIR) to save time
   2. Verify maximum depth for the regression input
   3. Calculate below surface (0-) values with regression script - using ROBUST REGRESSION to compensate for any outliers
2. Calculate above surface (0+) values (example: Ed(0+) = Ed(0-)/0.98 Lw = Lu(0-) * 0.543)
3. Calculate reflectance ratios (RRS) (example: Rrs = Lw/Ed)

VI. QC results

1. Compare surface ED values with surface irradiance (ES) (example: ED(0-) < ES)
2. Compare relationship between Rrs and Chlorophyll

VII. Transfer data to relational database

---

02.01.2012: Absorption Figures for Our Global Dataset

---

News & Updates - January 2012:
01.30.2012: The Global Database has been updated Global_Data_2012_01_28.xls
1) Plumes & Blooms chlorophyll values corrected (cruises pb168 and pb213)
2) New data added - NSF-BWZ (chlorophyll, reflectance and absorption)
3) Recommend omitting data from AMT19. Reflectance values appear to be too high for wavelengths gt 490.

---

News & Updates - December 2011:
12.16.2011: BBOP data processed. See notes below in BBOP section

12.14.2011: The global database has been updated. /home/dcourt/SeaBASS_Data/Global_Data/Global_Data_2011_12_14.xls

PLEASE NOTE that I will be making some major improvements over the winter break. I will be re-doing the database so that it includes hyper-spectral measurements for each parameter. This will allow the user to choose a much wider range of options and it will also allow for better updates in the future.

---

News & Updates - November 2011:

11.30.2011: I've reprocessed the BBOP MicroPro data with the calibrations listed below - as specified in the timeline.
I then compared the newly reprocessed profiles (where different calibration files were used) with the former data.

The figure below illustrates how each calibration was done in correlation with the cruise schedule:

BBOP RE-PROCESSING RESULTS: a comparison of the data - REPROCESSED/ORIGINAL (2009 - 2010)

- RE-PROCESSING HISTOGRAMS

BBOP CALIBRATION HISTORY: a closer look at the changes that have occurred with the MicroPro (2008 - 2011)

- Calibration History Figures: MicroPro-113 Irradiance

- Calibration History Figures: MicroPro-113 Radiance

- Calibration History: Reference Irradiance

- Calibration History Spreadsheet

---

News & Updates - October 2011:

10.01.2011: I will be in Switzerland from Dec 20th - Jan 7th.
I will be working some days while I am away and I will almost always be available via email

09.14.2011: DATA EDITS AND SUBMISSIONS
- The sample times for all the MicroPro data have been edited. All times are GMT.
- All MicroPro data have been RE-submitted to SeaBASS to ensure correct sample times. Jeremy has been notified
- PAR values have been calculated from all in-water SPMR & MicroPro irradiance profile data 18.10.2012
- I've asked for a Microsoft operating system to be installed - along with VMware Fusion 02.10.2012
- I've found the "underway" irradiance data associated with the MER.
- I've extracted (and formatted) these Es data and have calculated daily PAR values based on the sampling interval of 1.4 seconds.
- These daily PAR values are now available on-line: ftp://ftp.eri.ucsb.edu/pub/org/oceancolor/BBOP/BBOP_PAR/BBOP_daily_PAR_1992_2011.txt

---

Ongoing:

Global Database of In-Situ Matchups
- Adding new data as it becomes available from SeaBASS
- Applying QC/QA measures to updates
- Most recent update: March 20, 2012
- Need to redevelop MySQL tools for on-line access

BBOP
- WEBPAGE: http://www.icess.ucsb.edu/bbop/
- Processing BBOP MicroPro data
- Formatting data for SeaBASS submissions
- Latest cruise: B280
- Processed: YES:
- Latest data submitted to SeaBASS: B280 (05.03.2012)
- Data available on-line: YES

Plumes & Blooms
- WEBPAGE: http://www.icess.ucsb.edu/PnB/PnB.html
- Formatting processed CTD for SeaBASS submissions
- Processing and formatting PRR data for SeaBASS submissions
- Latest cruise: PB236
- Latest data submitted to SeaBASS:

 CTD: PB234

 PRR: PB234

- Data available on-line: YES

CLIVAR
- Process MicroPro radiometric data as it becomes available
- Matchup data products (AOPs, IOPs & pigments)
- Last cruise: P6 - April 2010
- Submitted to SeaBASS: YES
- Additional cruises: A20 A22 AMMA I08S I09N P16S P16N P18

Global Matchups (In-Situ Data + Remotely Sensed Data Products)
- WEBPAGE: http://www.icess.ucsb.edu/~dcourt/MATCHUPS/

MEaSUREs
- Plan to build some web-tools per Stéphane's needs

Current Projects:

Re-organization of the BBOP database
Purpose:
- To increase the performance of the (in-situ) matchups
Methods:
- Imported all pigment measurements from SeaBASS
- Organized all radiometric data (MER, SPMR, & MicrPro)
- Organized all absorption data (ag, ad, ap & aph)
- Cross-referencing all measurements with BATS log sheets
Issues:
- The chlorophyll values in the original database were incorrect. The chlorophyll values were taken from the IOP bottle-files that had been submitted to SeaBASS. These were labeled as "chl_a", which is defined by SeaBASS as an HPLC product. However, these are Flourometric measurements. At some point, a script needs to be written to re-format these files and a they must then be re-submitted to SeaBASS.
- Many of the sample times are incorrect. This creates inaccuracies when trying to match-up the different measurements. Most of the problems are a result of submitting the data in local (Bermuda) time rather than GMT - as required by SeaBASS. Other issues exist as a result of the technician's computer clock being incorrectly set - sometimes by an order of years. Again, these data will ultimately have to edited and re-submitted to SeaBASS.