FLKIN

Fix use of KIN (kingfish) code when SUR (kina) appears more likely

Kingfish reported from diving events are questionable. Although some kingfish are taken by diving when spear fishing, it is also possible that the KIN code was erroneously recorded with reference to kina (which are properly recorded as SUR).

Many of the questionable records are associated with paua diving in KIN 3 during the 1990s, and include cases where fishers have consistently recorded KIN in estimated catches and landings. The MHR data provide an independent check on whether KIN was actually taken, but only from 2001.

For diving events, KIN recorded as the target species or in estimated catches is recoded to SUR if the permit holder has never submitted MHRs for KIN. For landings of KIN from trips where diving took place, these are recoded to SUR if the permit holder has never reported that stock in their MHRs. Section 111 landings are not included in these checks as these would not give rise to MHR records.

The stronger requirement that the permit holder has also submitted MHRs for SUR is not applied because this does not deal with permit holders who exited the fishery prior to the introduction of MHRs.

-- © Copyright 2026 The Kahawai Collective
-- SPDX-License-Identifier: MIT
INSERT INTO grooming_rules
VALUES ('FLKIN', 'edw', 'est_catch', 'Fix', 'Update estimated catch species to SUR when KIN is reported from diving events with no MHR support');

INSERT INTO grooming_rules
VALUES ('FLKIN', 'edw', 'effort', 'Fix', 'Update target species to SUR when KIN is reported from diving events with no MHR support');

INSERT INTO grooming_rules
VALUES ('FLKIN', 'edw', 'landings', 'Fix', 'Update landed species to SUR when KIN is landed from trips with diving events and no MHR support');


CREATE TEMP TABLE _kin_client_stock AS
 SELECT DISTINCT client_number,
        fishstock_code
   FROM groomed.monthly_harvest_returns
  WHERE fishstock_code LIKE 'KIN%';


CREATE TEMP TABLE _sur_no_kin AS
WITH _sur_clients AS
(SELECT DISTINCT client_number
   FROM groomed.monthly_harvest_returns
  WHERE fishstock_code LIKE 'SUR%'),

     _kin_clients AS
(SELECT DISTINCT client_number
   FROM groomed.monthly_harvest_returns
  WHERE fishstock_code LIKE 'KIN%')

   SELECT s.client_number
     FROM _sur_clients s
LEFT JOIN _kin_clients k ON (s.client_number = k.client_number)
    WHERE k.client_number IS NULL;


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

-- Effort
INSERT INTO groomed.checks (code, "table", "column", id, details, orig, new)
SELECT 'FLKIN', 'effort', 'target_species', id, trip::TEXT, target_species, 'SUR'
  FROM groomed.effort e
 WHERE primary_method IN ('DV','DI','UBA','UBS')
   AND target_species = 'KIN'
   AND client_no NOT IN (SELECT client_number FROM _kin_client_stock);
   --AND client_no IN (SELECT client_number FROM _sur_no_kin);

UPDATE groomed.effort SET checks = checks || '{FLKIN}', target_species = 'SUR'
  FROM groomed.checks u
 WHERE u.code = 'FLKIN'
   AND groomed.effort.id = u.id;

-- Landings
INSERT INTO groomed.checks (code, "table", "column", id, details, orig, new)
   SELECT 'FLKIN', 'landings', 'species_code', id, trip::TEXT, species_code, 'SUR'
     FROM groomed.landings l
LEFT JOIN _kin_client_stock k ON (l.client_no = k.client_number) AND (l.fishstock_code = k.fishstock_code)
    WHERE species_code = 'KIN'
      AND k.fishstock_code IS NULL
      AND trip IN (SELECT DISTINCT trip FROM groomed.effort WHERE primary_method IN ('DV','DI','UBA','UBS'));

UPDATE groomed.landings SET checks = checks || '{FLKIN}', species_code = 'SUR', species_name = 'Kina', species_class = 'Echinoderms'
  FROM groomed.checks u
 WHERE u.code = 'FLKIN'
   AND groomed.landings.id = u.id;

INSERT INTO groomed.checks (code, "table", "column", id, details, orig, new)
   SELECT 'FLKIN', 'landings', 'fishstock_code', id, trip::TEXT, l.fishstock_code, replace(l.fishstock_code, 'KIN', 'SUR')
     FROM groomed.landings l
LEFT JOIN _kin_client_stock k ON (l.client_no = k.client_number) AND (l.fishstock_code = k.fishstock_code)
    WHERE species_code = 'KIN'
      AND k.fishstock_code IS NULL
      AND trip IN (SELECT DISTINCT trip FROM groomed.effort WHERE primary_method IN ('DV','DI','UBA','UBS'));

UPDATE groomed.landings SET checks = checks || '{FLKIN}', fishstock_code = u.new
  FROM groomed.checks u
 WHERE u.code = 'FLKIN'
   AND u."column" = 'fishstock_code'
   AND groomed.landings.id = u.id;

-- Estimated catches
INSERT INTO groomed.checks (code, "table", "column", id, details, orig, new)
SELECT 'FLKIN', 'est_catch', 'species_code', c.id, event_key::TEXT, species_code, 'SUR'
  FROM groomed.est_catch c
  JOIN groomed.effort USING (event_key)
 WHERE primary_method IN ('DV','DI','UBA','UBS')
   AND species_code = 'KIN'
   AND client_no NOT IN (SELECT client_number FROM _kin_client_stock);
   --AND client_no IN (SELECT client_number FROM _sur_no_kin);

UPDATE groomed.est_catch SET checks = checks || '{FLKIN}', species_code = 'SUR'
  FROM groomed.checks u
 WHERE u.code = 'FLKIN'
   AND groomed.est_catch.id = u.id;

$$ LANGUAGE SQL;

SELECT FLKIN();

Effects of grooming

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

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