LANSP

Drop landings with non-species codes

Some landings have “species” codes that are not fisheries species. These include codes that refer to unspecific or secondary product states:

-- © Copyright 2026 The Kahawai Collective
-- SPDX-License-Identifier: MIT
CREATE TEMP TABLE _non_species (non_species_code TEXT, description TEXT);

INSERT INTO _non_species VALUES ('BAI', 'Bait');
INSERT INTO _non_species VALUES ('FRA', 'Fragments, Bones');
INSERT INTO _non_species VALUES ('HAH', 'Groper(hapuka) Heads (bait)');
INSERT INTO _non_species VALUES ('LIV', 'Liver');
INSERT INTO _non_species VALUES ('MEA', 'Meal');
INSERT INTO _non_species VALUES ('OFF', 'Offal');
INSERT INTO _non_species VALUES ('OIL', 'Fishoil');
INSERT INTO _non_species VALUES ('ROE', 'Roe');
INSERT INTO _non_species VALUES ('SCL', 'Scales');
INSERT INTO _non_species VALUES ('WAS', 'Fish waste');

or to non-fish:

-- © Copyright 2026 The Kahawai Collective
-- SPDX-License-Identifier: MIT
INSERT INTO _non_species VALUES ('BAL', 'Non-itq Balance');
INSERT INTO _non_species VALUES ('MUD', 'Mud');
INSERT INTO _non_species VALUES ('ROK', 'Rocks / Stones');
INSERT INTO _non_species VALUES ('RUB', 'Rubbish Other Than Fish');
INSERT INTO _non_species VALUES ('SAD', 'Sand');
INSERT INTO _non_species VALUES ('SED', 'Sediment');
INSERT INTO _non_species VALUES ('WOD', 'Wood');
INSERT INTO _non_species VALUES ('ZFT', 'Rubbish
#### Fishing Textiles'); {.unnumbered}
INSERT INTO _non_species VALUES ('ZOO', 'Rubbish
#### Other Use Other'); {.unnumbered}
INSERT INTO _non_species VALUES ('ZHM', 'Rubbish
#### Household Metals'); {.unnumbered}
INSERT INTO _non_species VALUES ('ZOG', 'Rubbish Other Use Glass');
INSERT INTO _non_species VALUES ('ZOM', 'Rubbish Other Use Metals');
INSERT INTO _non_species VALUES ('ZOO', 'Rubbish Other Use Other');
INSERT INTO _non_species VALUES ('ZOP', 'Rubbish Other Use Plastics');
INSERT INTO _non_species VALUES ('ZOT', 'Rubbish Other Use Textiles');

These landings are dropped so that they do not get allocated to fishing events and turn up in fisheries characterisations.

-- © Copyright 2026 The Kahawai Collective
-- SPDX-License-Identifier: MIT
INSERT INTO grooming_rules
VALUES ('LANSP', 'edw', 'landings', 'Drop', 'Drop landings with non-species codes');

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

INSERT INTO groomed.checks(code, "table", "column", id)
SELECT 'LANSP', 'landings', 'species_code', l.id
  FROM groomed.landings l
 WHERE l.species_code IN (SELECT non_species_code FROM _non_species);

UPDATE groomed.landings
   SET checks = array_append(checks, 'LANSP'), dropped=TRUE
  FROM groomed.checks u
 WHERE u.code = 'LANSP'
   AND groomed.landings.id = u.id;

$$ LANGUAGE SQL;

SELECT LANSP();
Effects of grooming
Figure 1: The number of records affected by the LANSP rule, by year and source.