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