CBIBS QC Flags

qcflags.sql

-- get_bulk_qc_flags(site_code, start_time, end_time)
SELECT
    DISTINCT ON (o.measure_ts, v.actual_name, l.elevation)
    o.measure_ts,
    s.site_code,
    v.actual_name,
    u.netcdf,
    l.elevation,
    o.obs_value,
    qc.qa_code as "Primary QC",
    range.qc_code_id as "Gross Range Test",
    spike.qc_code_id as "Spike Test",
    rate.qc_code_id as "Rate of Change Test",
    flat.qc_code_id as "Flat Line Test",
    atten.qc_code_id as "Attenuated Signal Test",
    gap.qc_code_id as "Gap Test"
FROM cbibs.f_observation o
JOIN cbibs.d_location l ON (o.d_location_id = l.id)
JOIN cbibs.d_station s ON s.id=o.d_station_id
JOIN cbibs.d_variable v ON v.id=o.d_variable_id
JOIN cbibs.d_units u on u.id = v.d_units_id
JOIN cbibs.d_qa_code_primary qc ON qc.id=o.d_qa_code_primary_id
LEFT JOIN cbibs.j_qa_code_secondary_fob range ON range.f_observation_id=o.id AND range.qartod_code=4
LEFT JOIN cbibs.j_qa_code_secondary_fob spike ON spike.f_observation_id=o.id AND spike.qartod_code=6
LEFT JOIN cbibs.j_qa_code_secondary_fob rate ON rate.f_observation_id=o.id AND rate.qartod_code=7
LEFT JOIN cbibs.j_qa_code_secondary_fob flat ON flat.f_observation_id=o.id AND flat.qartod_code=8
LEFT JOIN cbibs.j_qa_code_secondary_fob atten ON atten.f_observation_id=o.id AND atten.qartod_code=10
LEFT JOIN cbibs.j_qa_code_secondary_fob gap ON gap.f_observation_id=o.id AND gap.qartod_code=1
WHERE s.site_code='44041'
    AND o.measure_ts > '2015-10-01'
    AND v.actual_name = 'sea_water_salinity'
ORDER BY o.measure_ts;