ESTGT

Estimated catch of target species is given as event total estimated catch

In addition to per species estimated catches, fishers are asked to report a total (aggregated species) catch weight for each fishing event. For fishing events where there are no species-specific estimated catches, but where the total catch for the event is greater than zero, it is assumed that the total catch is the estimated catch of the target species.

-- © Copyright 2026 The Kahawai Collective
-- SPDX-License-Identifier: MIT
INSERT INTO grooming_rules
VALUES ('ESTGT', 'edw', 'est_catch', 'Fix', 'Create estimated catch records for events with a total catch weight only');

CREATE TEMP TABLE _estgt AS
WITH _max_id AS
  (SELECT MAX(id) AS max_existing_id
     FROM groomed.est_catch),

    _catch_no_est AS
  (SELECT e.event_key,
          e.source_identifier_key,
          e.fishing_event_key,
          e.target_species,
          e.catch_weight,
          COUNT(c.*) AS est_catch_recs
     FROM groomed.effort e
LEFT JOIN groomed.est_catch c USING (event_key)
    WHERE e.catch_weight > 0
 GROUP BY e.event_key,
          e.source_identifier_key,
          e.fishing_event_key,
          e.target_species,
          e.catch_weight
   HAVING COUNT(c.*) = 0)

   SELECT m.max_existing_id + row_number() OVER () AS id,
          e.event_key,
          e.source_identifier_key,
          e.fishing_event_key,
          e.target_species AS species_code,
          e.catch_weight,
          ARRAY['ESTGT']::char(5)[] AS checks
     FROM _max_id m, _catch_no_est e;


CREATE OR REPLACE FUNCTION ESTGT() RETURNS VOID AS $$

INSERT INTO groomed.est_catch(id, event_key, source_identifier_key, fishing_event_key, species_code, catch_weight, checks)
SELECT id,
       event_key,
       source_identifier_key,
       fishing_event_key,
       species_code,
       catch_weight,
       checks
  FROM _estgt;


INSERT INTO groomed.checks(code, "table", "column", id, new)
SELECT 'ESTGT',
       'est_catch',
       'species_code',
        id,
       species_code
  FROM _estgt;

INSERT INTO groomed.checks(code, "table", "column", id, new)
SELECT 'ESTGT',
       'est_catch',
       'catch_weight',
        id,
       catch_weight::TEXT
  FROM _estgt;

$$ LANGUAGE SQL;

SELECT ESTGT();

Effects of grooming

Figure 1: The number of records affected by the ESTGT rule, by year and source.

Figure 2: The proportion of records affected by the ESTGT rule, by form type, for all form types where some records are groomed by the ESTGT rule.