FEFMA
Mark trips which landed to more than one fishstock for straddling statistical areas
Starr (2007) suggest to mark trips which landed to more than one fishstock for straddling statistical areas. Since this relies on fishing event.start_stats_area_code do this check after grooming on that.
-- © Copyright 2026 The Kahawai Collective
-- SPDX-License-Identifier: MIT
INSERT INTO grooming_rules
VALUES ('FEFMA', 'edw', 'effort', 'Flag', 'Mark trips which landed to more than one fishstock for straddling statistical areas');
-- Create the following table containing species of interest before calling FEFMA():
DROP TABLE IF EXISTS groomed.fefma_species;
CREATE TABLE groomed.fefma_species AS
SELECT unnest(ARRAY['ZZZ']) AS species;
CREATE OR REPLACE FUNCTION FEFMA()
RETURNS VOID AS $$
WITH straddling_statareas AS (
SELECT qma, stat, species FROM qmastats WHERE (stat, species) IN (
SELECT stat, species
FROM qmastats
WHERE species IN (SELECT species FROM groomed.fefma_species)
GROUP BY stat, species
HAVING count(distinct qma) > 1
)
), multiple_stock_trips AS (
SELECT trip, stat, species, array_agg(distinct fishstock_code) AS landed_stocks
FROM groomed.landings l
JOIN straddling_statareas ss ON l.fishstock_code = ss.qma
AND l.species_code = ss.species
GROUP BY trip, stat, species
HAVING count(distinct fishstock_code) > 1
)
INSERT INTO groomed.checks(code, "table", id, details)
SELECT 'FEFMA', 'effort', id, species || ' ' || landed_stocks::TEXT
FROM groomed.effort e
JOIN multiple_stock_trips t ON e.trip = t.trip
AND e.start_stats_area_code = t.stat
WHERE e.start_point IS NULL
OR e.end_point IS NULL;
UPDATE groomed.effort SET checks = checks || '{FEFMA}'
FROM groomed.checks u
WHERE u.code = 'FEFMA'
AND groomed.effort.id = u.id;
$$ LANGUAGE SQL;
SELECT FEFMA();