LASCD

State codes for secondary product states

Starr (2007) suggested dropping “landings where state code==FIN|==FLP|==SHF|==ROE and there is more than one record for the trip/Fishstock combination.”

The current implementation drops any record of a secondary product state, based on the list of secondary state codes maintained in the public.state_codes table from published Regulations and Circulars.

Landings of secondary states are only dropped where primary states for the species exist on a trip, to guard against cases where the secondary state code is used in error and, therefore, be retained as the best record of catch from the trip.

-- © Copyright 2026 The Kahawai Collective
-- SPDX-License-Identifier: MIT
CREATE TEMP TABLE _trip_species_principal AS
SELECT DISTINCT trip, species_code
  FROM groomed.landings
 WHERE state_code IN (SELECT code FROM public.state_codes WHERE state_type = 'principal');
                
INSERT INTO grooming_rules
VALUES ('LASCD', 'edw', 'landings', 'Drop', 'Drop landings of secondary product states');

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

INSERT INTO groomed.checks(code, "table", "column", id)
SELECT 'LASCD', 'landings', 'state_code', l.id
  FROM groomed.landings l
  JOIN _trip_species_principal p USING (trip, species_code)
 WHERE l.state_code IN (SELECT code FROM public.state_codes WHERE state_type = 'secondary');

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

$$ LANGUAGE SQL;

SELECT LASCD();

Effects of grooming

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