ESCWN
Check for estimated catch entered as weight instead of numbers
For a few species, and specific forms and methods, estimated catch should be recorded in numbers rather than weight. This check is designed to find and change those records where the weight was recorded instead of numbers.
This is done by comparing the estimated catch with the landings for each trip for each species. If the ratio of landings to estimated catch is less than a specified threshold then the estimated catch is assumed to have been misreported as a catch weight and is adjusted by dividing by a specified average weight.
The check is restricted to those situations where estimated catch in numbers is expected and is only applied to those cases where only an estimate of numbers is available.
First, create a (persistent) table of trip-level summary information. This should be done after effort grooming but is done before estimated catch grooming as the information is used in that process.
-- © Copyright 2026 The Kahawai Collective
-- SPDX-License-Identifier: MIT
\echo 'Trip information'
DROP TABLE IF EXISTS groomed.trip_info;
CREATE TABLE groomed.trip_info AS
WITH expanded_events AS
(SELECT unnest(array_fill(f.event_key, array[utils.event_num(f.effort_num, f.form_type, f.primary_method)])) AS event_key,
f.trip,
f.vessel_id,
f.form_type,
f.primary_method,
f.target_species,
f.start_stats_area_code,
f.start_datetime,
f.end_datetime
FROM groomed.effort f),
effort_summary AS
(SELECT DISTINCT
x.trip,
x.vessel_id,
MIN(DATE(x.start_datetime)) AS first_date_fished,
MAX(DATE(COALESCE(x.end_datetime,x.start_datetime))) AS last_date_fished,
MAX(COALESCE(x.end_datetime,x.start_datetime)) AS last_fishing_datetime,
MODE() WITHIN GROUP (ORDER BY x.form_type) AS modal_form,
array_agg(DISTINCT x.form_type) AS forms,
MODE() WITHIN GROUP (ORDER BY x.primary_method) AS modal_method,
array_agg(DISTINCT x.primary_method) AS methods,
MODE() WITHIN GROUP (ORDER BY x.target_species) AS modal_target,
array_agg(DISTINCT x.target_species) AS targets,
MODE() WITHIN GROUP (ORDER BY x.start_stats_area_code) AS modal_stat_area,
array_agg(DISTINCT x.start_stats_area_code) AS stat_areas_fished,
COUNT(DISTINCT DATE(x.start_datetime)) AS days_fished
FROM expanded_events x
WHERE x.trip IS NOT NULL
GROUP BY x.trip,
x.vessel_id),
landing_summary AS
(SELECT DISTINCT
l.trip,
l.vessel_id,
MIN(DATE(l.landing_datetime)) AS first_date_landed,
MAX(DATE(l.landing_datetime)) AS last_date_landed,
MAX(l.landing_datetime) AS last_landing_datetime
FROM groomed.landings l
WHERE l.trip is not NULL
GROUP BY l.trip,
l.vessel_id),
derived_trip_info AS
(SELECT DISTINCT trip,
vessel_id,
fishyear(COALESCE(l.last_landing_datetime,e.last_fishing_datetime)) AS fishyear,
EXTRACT(MONTH FROM COALESCE(l.last_landing_datetime,e.last_fishing_datetime)) AS month,
e.first_date_fished,
e.last_date_fished,
l.first_date_landed,
l.last_date_landed,
e.days_fished,
e.modal_form,
e.forms,
e.modal_method,
e.methods,
e.modal_target,
e.targets,
e.modal_stat_area,
e.stat_areas_fished
FROM effort_summary e
FULL OUTER JOIN landing_summary l
USING (trip, vessel_id))
SELECT d.*,
t.trip_start_datetime AS mpi_trip_start,
t.trip_end_datetime AS mpi_trip_end
FROM derived_trip_info d
LEFT JOIN groomed.raw_trip_details t USING (trip);
CREATE INDEX ON groomed.trip_info (trip, vessel_id);-- © Copyright 2026 The Kahawai Collective
-- SPDX-License-Identifier: MIT
INSERT INTO grooming_rules
VALUES ('ESCWN', 'edw', 'est_catch', 'Fix', 'Correct cases where estimated catch is recorded in weight but number of fish is expected');
DROP TABLE IF EXISTS groomed.est_catch_grooming;
CREATE TABLE groomed.est_catch_grooming AS
SELECT * FROM (
VALUES
-- ('ZZZ', 1, 1),
('ALB', 1.5, 5, 20)
) AS t(species_code, minimum, average, maximum);For the species of interest, calculate the mean weight of fish by method and year for trips where the mean weight does not indicate a weight/numbers reporting problem.
-- © Copyright 2026 The Kahawai Collective
-- SPDX-License-Identifier: MIT
DROP TABLE IF EXISTS groomed.est_catch_grooming_meanwt;
CREATE TABLE groomed.est_catch_grooming_meanwt AS
WITH trip_species_gwt AS (
SELECT trip,
species_code,
SUM(green_weight) AS sum_green_weight
FROM groomed.landings
WHERE species_code IN (SELECT species_code FROM groomed.est_catch_grooming)
AND trip IS NOT NULL
AND green_weight IS NOT NULL
GROUP BY trip,
species_code
), trip_species_est_subcatch AS (
SELECT e.trip,
c.species_code,
SUM(c.catch_number) AS sum_catch_number
FROM groomed.effort e
JOIN groomed.est_catch c USING (event_key)
WHERE e.trip IS NOT NULL
AND c.species_code IN (SELECT species_code FROM groomed.est_catch_grooming)
GROUP BY e.trip,
c.species_code
), trip_species_gwt_cn AS (
SELECT g.trip,
g.species_code,
g.sum_green_weight,
c.sum_catch_number
FROM trip_species_gwt g
JOIN trip_species_est_subcatch c USING (trip, species_code)
)
SELECT i.fishyear,
t.species_code,
i.modal_method,
SUM(t.sum_green_weight)/SUM(t.sum_catch_number) AS meanwt,
COUNT(t.trip) AS n_trips
FROM trip_species_gwt_cn t
JOIN groomed.trip_info i USING (trip)
JOIN groomed.est_catch_grooming g USING (species_code)
WHERE t.sum_green_weight > t.sum_catch_number * g.minimum
AND t.sum_green_weight < t.sum_catch_number * g.maximum
GROUP BY i.fishyear,
t.species_code,
i.modal_method
ORDER BY i.fishyear,
t.species_code,
i.modal_method;
CREATE OR REPLACE FUNCTION ESCWN()
RETURNS VOID AS $$
WITH trip_species_gwt AS (
SELECT trip,
fishyear(MAX(landing_datetime)) AS fishyear,
species_code,
sum(green_weight) AS sum_green_weight
FROM groomed.landings
WHERE species_code IN (SELECT species_code FROM groomed.est_catch_grooming)
AND trip IS NOT NULL
AND green_weight IS NOT NULL
GROUP BY trip,
species_code
), trip_species_est_subcatch AS (
SELECT e.trip,
c.species_code,
sum(c.catch_weight) AS sum_catch_weight,
sum(c.catch_number) AS sum_catch_number
FROM groomed.effort e
JOIN groomed.est_catch c USING (event_key)
WHERE e.trip IS NOT NULL
AND c.species_code IN (SELECT species_code FROM groomed.est_catch_grooming)
GROUP BY e.trip,
c.species_code
), trip_species_gwt_cwtn AS (
SELECT g.trip,
g.fishyear,
g.species_code,
g.sum_green_weight,
c.sum_catch_weight,
c.sum_catch_number
FROM trip_species_gwt g
JOIN trip_species_est_subcatch c USING (trip, species_code)
)
INSERT INTO groomed.checks (code, "table", "column", id, orig, new, details)
SELECT 'ESCWN',
'est_catch',
'catch_number',
c.id,
c.catch_number::TEXT AS orig,
CASE WHEN ROUND(c.catch_number / a.meanwt)> 0 THEN ROUND(c.catch_number / a.meanwt)::TEXT
ELSE '1'
END AS new,
e.trip
FROM groomed.est_catch c
JOIN groomed.est_catch_grooming g USING (species_code)
JOIN groomed.effort e USING (event_key)
JOIN groomed.est_catch_grooming_meanwt a ON fishyear(e.start_datetime) = a.fishyear
AND e.primary_method = a.modal_method
AND c.species_code = a.species_code
WHERE e.trip IN (
SELECT trip
FROM trip_species_gwt_cwtn
JOIN groomed.est_catch_grooming USING (species_code)
WHERE sum_green_weight < sum_catch_number * minimum
AND trip IS NOT NULL
)
AND utils.expected_quantity(e.form_type, e.primary_method, c.species_code) = 'number'
AND c.catch_weight IS NULL;
UPDATE groomed.est_catch
SET checks = checks || '{ESCWN}', catch_number = u.new::NUMERIC
FROM groomed.checks u
WHERE u.code = 'ESCWN'
AND groomed.est_catch.id = u.id;
$$ LANGUAGE SQL;
SELECT ESCWN();Effects of grooming
