LAGWO
Identify and fix order of magnitude errors in landings
Landings data are the key source of information on catches because, where practicable, the fish are weighed. However, some trips have recorded exceptionally large landings of some species that are likely to be errors. A plausible cause for such errors, particularly for data collected on paper forms, is an order of magnitude error (i.e., a misplaced decimal point). If these errors in the landings data are not corrected they may create bias in fisheries characterisations and CPUE analyses.
The LAWGO rule aims to identify cases where the reported landings do not have support. Support for the recorded landings is provided either: - by recorded estimated or processed catch data for the species; or - by the fact that the catch per fishing event record is consistent with that of trips with better support.
Where estimated or processed catches for a species are reported on a trip, then these data should provide support for the reported landings, within the limits of the reporting framework (which include restrictions on the number of species included in the estimated catch and whether daily processing reports are required).
As a further check, the reported greenweight can be compared with the greenweight calculated by multiplying average container weights by the number of containers, and taking account of the conversion factor for the landed state. This calculated greenweight should be similar to the reported greenweight, although such similarity cannot be regarded as providing the same degree of support for landings as estimated or processed catch data provide.
For species landed on a trip, calculate:
- the sum of the landed greenweight, L (before applying any conversion factor conversions);
- the sum of the estimated weights from all fishing events, E;
- the sum of the processed weights from each day, P; and also
- the greenweight calculated as the sum of
container_weight*number of containers*conversion factor, Lc, noting that container weights are actual weights of fish in a particular processed state.
We exclude trips where the calculated and reported greenweights are not similar, requiring 0.95 <= Lc/L <= 1.05; then assess the 10th and 90th quantiles for E/L and P/L, noting that only trips with reported estimated and/or processed catches contribute to defining these quantities. Catches from trips where - 0.95 <= Lc/L <= 1.05; and - E/L or P/L lie within the range of defined by the 10th and 90th quantiles for these ratios; are considered to have support for their landed catches L.
The acceptable range for E/L and P/L must be assessed on a species and form/method specific basis, because the different reporting forms provide different opportunities for a species to appear in the estimated and processed catch, and for the multi-method CELR form it is likely that catches will vary widely between methods. The choice of quantiles used to define the acceptable range is somewhat arbitrary; here, the assumption is that order of magnitude records are relatively rare so are unlikely to be affecting these ratios for at least the core 80% of trips.
For trips where E/L and P/L do not support the landings, either because they are out of the acceptable range or #### more commonly - because estimated or processed catch data is lacking, we assess whether the form-specific landings per fishing event record is greater than that seen for any of the supported landings, and also assess the order of magnitude of the landing, Loom = round(log10(L/Lmed)), relative to the median landing weight for the supported landings (by form type). {.unnumbered}
In calculating the maximum landings per fishing event record for trips with supported landings we include only those trips where the number of fishing event records was between the 10th and 90th percentiles of the number of events (per form type).
For landings where there is no support from the processed or estimated catch, the catch per record is higher than of any of the supported catches, and the order of magnitude of the landing, Loom, is greater than one, the landings are adjusted by dividing by the order of magnitude, Ladj = L/(10^Loom).
-- © Copyright 2026 The Kahawai Collective
-- SPDX-License-Identifier: MIT
CREATE TEMP TABLE _compare_catch AS
WITH _landings_by_trip_species AS
(SELECT trip,
species_code,
SUM(green_weight) AS sum_green_weight,
SUM(CASE WHEN state_code = 'MEA' THEN NULL
ELSE unit_num * unit_weight * conv_factor
END) AS calc_green_weight
FROM groomed.landings
GROUP BY trip,
species_code),
_processed_by_trip_species AS
(SELECT trip,
CASE WHEN species_code IN ('BFL', 'BRI', 'GFL', 'LSO', 'ESO', 'SFL', 'TUR', 'YBF') THEN 'FLA'
WHEN species_code IN ('BAS', 'HAP') THEN 'HPB'
ELSE species_code
END AS species_code,
sum(green_weight) AS sum_green_weight
FROM groomed.processed_catch
WHERE trip IN (SELECT trip FROM groomed.landings)
GROUP BY trip,
species_code),
_estimated_by_trip_species AS
(SELECT e.trip,
CASE WHEN c.species_code IN ('BFL', 'BRI', 'GFL', 'LSO', 'ESO', 'SFL', 'TUR', 'YBF') THEN 'FLA'
WHEN c.species_code IN ('BAS', 'HAP') THEN 'HPB'
ELSE c.species_code
END AS species_code,
SUM(c.catch_weight) AS sum_green_weight
FROM groomed.effort e
JOIN groomed.est_catch c USING (event_key)
GROUP BY e.trip,
c.species_code),
_effort_by_trip AS
(SELECT trip,
COUNT(event_key) AS effort_records,
MODE() WITHIN GROUP (ORDER BY form_type) AS modal_form,
MODE() WITHIN GROUP (ORDER BY primary_method) AS modal_method
FROM groomed.effort
GROUP BY trip)
SELECT l.trip,
t.modal_form,
t.modal_method,
t.effort_records,
l.species_code,
l.sum_green_weight AS landed,
l.calc_green_weight::REAL/l.sum_green_weight::REAL AS calc_to_land,
p.sum_green_weight::REAL/l.sum_green_weight::REAL AS proc_to_land,
e.sum_green_weight::REAL/l.sum_green_weight::REAL AS est_to_land,
CASE WHEN COALESCE(t.effort_records,0) > 0 THEN l.sum_green_weight::REAL/t.effort_records::REAL
ELSE 'Infinity'
END AS land_per_record
FROM _landings_by_trip_species l
LEFT JOIN _processed_by_trip_species p USING (trip, species_code)
LEFT JOIN _estimated_by_trip_species e USING (trip, species_code)
LEFT JOIN _effort_by_trip t USING (trip)
WHERE l.sum_green_weight > 0; Create a persistent table with the trip statistics that are employed in the LAGWO rule.
-- © Copyright 2026 The Kahawai Collective
-- SPDX-License-Identifier: MIT
CREATE TABLE groomed.species_form_catch_ratios AS
WITH _catch_ratio_quantiles AS
(SELECT species_code,
modal_form,
modal_method,
percentile_cont(0.1) WITHIN GROUP (ORDER BY est_to_land) AS est_to_land_lb,
percentile_cont(0.9) WITHIN GROUP (ORDER BY est_to_land) AS est_to_land_ub,
percentile_cont(0.1) WITHIN GROUP (ORDER BY proc_to_land) AS proc_to_land_lb,
percentile_cont(0.9) WITHIN GROUP (ORDER BY proc_to_land) AS proc_to_land_ub,
percentile_cont(0.1) WITHIN GROUP (ORDER BY effort_records) AS effort_records_lb,
percentile_cont(0.9) WITHIN GROUP (ORDER BY effort_records) AS effort_records_ub
FROM _compare_catch
WHERE calc_to_land BETWEEN 0.95 AND 1.05
GROUP BY species_code,
modal_form,
modal_method),
_supported_catches AS
(SELECT c.trip,
c.modal_form,
c.modal_method,
c.effort_records,
c.species_code,
c.landed,
c.land_per_record
FROM _compare_catch c
LEFT JOIN _catch_ratio_quantiles q USING(species_code, modal_form, modal_method)
WHERE c.calc_to_land BETWEEN 0.95 AND 1.05
AND (c.est_to_land BETWEEN q.est_to_land_lb AND q.est_to_land_ub
OR c.proc_to_land BETWEEN q.proc_to_land_lb AND q.proc_to_land_ub)),
_supported_stats AS
(SELECT s.modal_form,
s.modal_method,
s.species_code,
MAX(s.land_per_record) AS cr_max,
percentile_cont(0.5) WITHIN GROUP (ORDER BY s.landed) AS median_landed
FROM _supported_catches s
LEFT JOIN _catch_ratio_quantiles q USING(species_code, modal_form, modal_method)
WHERE s.effort_records BETWEEN q.effort_records_lb AND q.effort_records_ub
GROUP BY s.modal_form,
s.modal_method,
s.species_code)
SELECT q.modal_form,
q.modal_method,
q.species_code,
q.est_to_land_lb,
q.est_to_land_ub,
q.proc_to_land_lb,
q.proc_to_land_ub,
s.cr_max,
s.median_landed
FROM _catch_ratio_quantiles q
LEFT JOIN _supported_stats s USING(species_code, modal_form, modal_method)
WHERE s.median_landed IS NOT NULL;-- © Copyright 2026 The Kahawai Collective
-- SPDX-License-Identifier: MIT
INSERT INTO grooming_rules
VALUES ('LAGWO', 'edw', 'landings', 'Fix', 'Identify and fix order of magnitude errors in landings');
CREATE OR REPLACE FUNCTION LAGWO() RETURNS VOID AS $$
WITH _trip_species_stats AS
(SELECT c.trip,
c.modal_form,
c.modal_method,
c.species_code,
c.landed,
(c.est_to_land BETWEEN r.est_to_land_lb AND r.est_to_land_ub OR c.proc_to_land BETWEEN r.proc_to_land_lb AND r.proc_to_land_ub) AS ratio_support,
c.land_per_record <= r.cr_max AS in_range,
ROUND(LOG(c.landed::REAL/r.median_landed::REAL)::NUMERIC,0)::INT AS land_oom
FROM _compare_catch c
LEFT JOIN groomed.species_form_catch_ratios r USING(species_code, modal_form, modal_method)),
_lagwo AS
(SELECT trip,
species_code,
land_oom
FROM _trip_species_stats
WHERE NOT (ratio_support IS NOT NULL AND ratio_support)
AND NOT (in_range IS NOT NULL AND in_range)
AND land_oom > 1)
INSERT INTO groomed.checks(code, "table", "column", id, orig, new)
SELECT 'LAGWO',
'landings',
'green_weight',
l.id,
l.green_weight::TEXT,
(l.green_weight::NUMERIC/(10^c.land_oom)::NUMERIC)::TEXT
FROM groomed.landings l
JOIN _lagwo c USING (trip, species_code);
UPDATE groomed.landings
SET checks = array_append(checks, 'LAGWO'),
green_weight = u.new::NUMERIC
FROM groomed.checks u
WHERE u.code = 'LAGWO'
AND groomed.landings.id = u.id;
$$ LANGUAGE SQL;
SELECT LAGWO();Effects of grooming
