Data Quality Control Flags for Ship and ROV
Intro
Core RovCtd, RovNav and ShipNav data in the Expd database can have QC flags associated at the individual record (row) level. For Rovctd data it is even more granular - typ. down to the individual field within a record.
But keep in mind that QC flags are really only useful if two things happen:
- Data produces reliably maintain the flags.
- The sql query-writers choose to utilize the flags when selecting data.
** Both ** of the above criteria have seldom been put into practice with the exception of rovctd O2 (beginning in about 2015 when Ed Peltzer started doing routine O2 quality assessment and calibration work.)
The MBARI Engineering group does not do routine QC evaluation on data. We are responsible for the data load paths and post-processing software. We also maintain and monitor the nightly load scripts and will set flags and handle ctd data reprocessing if Science groups tell us what is needed. The science groups (including sometime the Video Lab personnel) perform the QC work and inform us when they see problems.
Historically, the SE group has done the work of loading/unloading/flag setting etc. Typically QC type problems are few and far between and also somewhat unpredictable. So at times manual intervention using direct SQL modifications to the expd database base-tables is required. We have taken on this roll so that we can limit our exposure to potential database corruption mistakes.
QC Flag Meanings
QC flags can have the following meanings:
- 0 = Data known to be bad. ex. Sensor malfunctioned
- 1 = Questionable ex. Data contains some spikes but no known problems with the sensor.
- 2 = Auto-loaded ex. Data has been automatically processed and loaded but not checked by human eyes
- 3 = Sensors appear to be operating as specified ex. Checked by the CTD technician, data looks reasonable
- 4 = Data is great. ex. Scientist familiar with the data has verified the validity.
Navigation Flags
For Ship and ROV raw and edited navigation data, the flag is a single bit-field (QCFlag) that applies to each full row of data.
The flags have never been used that I can recall. So I wont say much about them other than they are there as empty columns in the tables.
No 'canned' stored procedures exist to aid in setting/clearing them.
If they were to be put into use in the future it would take some additional effort to write develop the apps/queries/procedures to utilize them.
ROVCTD Flags
Ventana and DocRicketts Flags
Rovctd data flags for Ventana and DocRicketts typically set = 2 for every record by automated processing during the initial load If initial processing detected a spike or out-of-range value the relevant flag may be downgrade to 'questionable' or 'bad'.
Minirov Flags
Rovctd O2 flags for minirov are automatically set = 1 (Questionable/Suspect) because the sensors are:
- Mounted on the clump weight, not the vehicle so they are not in close proximity to the vehicle and the PTC(S) sensor.
- Long periods where the sensors are possibly not properly stored or maintained due to shipping and low frequency of use.
- Vehicle not configured for dual sensors.
- Not subject to winkler titrated water samples.
More About ROVCTD Flags in General
For rovctd, each data row has flags for either individual fields or logical groupings of fields.
For both our 'raw' 1Hz and 15-second 'binned' ROVCTD data, any the following flags can be set.
- ptsFlag (pressure, temperature, salinity)
- o2Flag (primary sensor)
- o2altFlag (secondary sensor)
- lightFlag (very rarely used)
- latlonFlag (this is lat/lon in the CTD tables - not nav tables)
- analog1Flag (very rarely used)
- analog2Flag (very rarely used)
- analog3Flag (very rarely used)
- analog4Flag (very rarely used)
O2 flags are the most heavily used. ptsFlags are common to see set bad by initial processing near the surface zone where bubbles play havoc with conductivity. That results in errors in salinity calculation. Cascade continues since o2, density and sound speed calculations require accurate salinity.
Note
It is important if you need to mark the ptsFlag as bad or suspect, you should also set
o2flag and o2altFlag as bad or suspect.
Also - o2 and transmissometer sensors come to us as analog voltages on one of the four analog channels. They are immediately converted to derived O2, xmissometer etc on load using calibrations for each sensor. We then set the flags on those derived values.
Note
We do not try to cascade the O2 flags down to the associated analog channel.
How to set O2 Flags
- Ed Peltzer typically evaluates O2 dive-by-dive is small batches. He then sends an email instructing us on how he wants O2 flags set for both the primary and secondary sensors.
- Typically he specifies the flag settings for the entire dive.
- Occasionally he will specify a depth range from with a dive that must be flagged differently from the 'rest of the dive'. When this happens, you must examine the raw logged ctd data files to determine the time range during descent or ascent that corespond to the problem area.
O2 Flags are set routinely enough that there are stored procedures in Expd written to assist in setting the flags. These first two are what 'the user' should invoke.
Warning
These stored proceedures can not update across year boundaries. If that were ever needed, run them twice!
Warning
FlagByDive will fail if the DiveNumber , start and end dateimes have not been enter by the scientists or video lab personnel!
- AdminSetRovCtdFlagByDive - set the specified flag for entire dive (based on dive start/end time)
- AdminSetRovCtdFlagByTime - set the specified flag over a range of datetime
Two additional stored procedures are called by the. above to do the actual work
- AdminSetRovctdFlag - Users should not exec this directly
- AdminSetRovctdBinFlag - Users should not exec this directly
The Gory Details
Database tables involved in this process are:
- Base tables for data in expd partitioned by year.
- For CTD there are partitioned
- 'raw data' tables,
- 'processed' data (typ. at the base 1hz rate)
- 'bin-averaged' (process data bin-averaged to 15 seconds)
- The Dive table - where the dive number and start/end datetime
- The the DiveSummary table - which gets start/end from the Dive table and holds other important statistics and tracking info.
- The History_Dive and History_DiveSummary tables - the latter being very useful to us in this context.
The Dive_Summary table is were the above stored procedures look to get DiveStart/End times and also where they record what was done and by whom. They also set thier own flags (the dirty-bits) that can signal other tasks what processing might need to follow.
I keep an SQL query text available that I edit to execute the main AdminSetRovCtdFlagByDive stored procedure via Aquadata Studio.
Note
The @rovid parameter is the SSDS device ID for the vehicle. It can be found in the Expd database PlatformLookup table.
--- Use this query to set primary and secondary
--- o2 sensor QC flags by dive number
declare @rovid int
declare @divenumber int
declare @flagname_1 varchar(30)
declare @value_1 int
declare @why_1 varchar(2500)
declare @flagname_2 varchar(30)
declare @value_2 int
declare @why_2 varchar(2500)
declare @who varchar(50)
--- 988=vnta 989=tibr 1672=docr
set @rovid = 1672
set @divenumber = 1320
set @who = 'rich'
--- o2Flag o2altFlag
set @flagname_1 = 'o2Flag'
set @value_1 = 3 --- 0,1,2,3,4
set @why_1 = 'O2 looks good. Per Ed Peltzer email 14-Dec-2020'
set @flagname_2 = 'o2AltFlag'
set @value_2 = 3 --- 0,1,2,3,4
set @why_2 = 'O2 looks good. Per Ed Peltzer email 14-Dec-2020'
EXECUTE [dbo].[AdminSetRovCtdFlagByDive] @rovid, @divenumber, @flagname_1, @value_1, @who, @why_1
EXECUTE [dbo].[AdminSetRovCtdFlagByDive] @rovid, @divenumber, @flagname_2, @value_2, @who, @why_2
And this query shows an example of calling the AdminSetRovCtdFlagByTime procedure (but for only a single flag).
declare @rovid int
declare @flagname_1 varchar(30)
declare @value_1 int
declare @why_1 varchar(2500)
declare @who varchar(50)
--- 988=vnta 989=tibr 1672=docr
set @rovid = 988
set @who = 'rich'
declare @startdatetime datetime
declare @enddatetime datetime
set @startdatetime = '03/10/2021 05:54:25 PM'
set @enddatetime = '03/10/2021 05:54:26 PM'
--- o2Flag o2altFlag
set @flagname_1 = 'o2Flag'
set @value_1 = 3 --- 0,1,2,3,4
set @why_1 = 'testing'
EXECUTE [dbo].[AdminSetRovCtdFlagByTime] @rovid, @flagname_1, @value_1, @startdatetime, @enddatetime, @who, @why_1
Note
The above examples are setting the o2Flag and/or the o2altFlag. They can also be used to set any of the rovctd QC flags as needed (such as ptsFlag, latlonFlag).
Suggested Record Keeping.
I strongly recommend you:
- Archive all emails from Ed
- Typ. you will end up with a batch of dives to do as Ed tends to do QC work in batches.
- Print hard copies of emails and store in a binder for each vehicle in ascending order.
- Review batches of work to be done by vehicle and review emails in reverse order. ie. newest dive first because you may discover Ed has changed his mind on dives he previously sent emails on.
- I like to note on scratch paper a list of all dive numbers to be QC flagged along with flag both values. This is where you may discover a newer email overrides what Ed had sent in an older. Possibly note on the older message, the date of the email that overides it.
- Work off of the above scratch list, to set and exec the query text parameters one dive at a time. I like to reference the date of the email as the 'why'
- Once all dives for an 'email' have been done, annotate the hard-copy email the current data and 'done'
- I also like to maintain a (O2Flags.xlsx) spreadsheet of flags and values for quick discovery of what O2 flags have been set to and why for each dive so I dont have to search too hard if asked.
Other points...
-
For the 'use case' mentioned above where a portion of a dive needs to be marked bad but the rest of the dive is to be marked 'good'...
- review the raw logged ctd data to find the start/end of the bad data.
- run the 'ByDive' procedure to mark all records as good
- then run the 'ByTime' procedure to re-flag the only the bad section.
-
If for any reason the yearday raw data for any period requires unload/reload/reprocessing, any flags previously set will be over-written. This will require you to reset them. Most likely you will want Ed to re-evaluate the QC prior to doing that.