FESAI

Statistical area imputation

Starr (2007) suggests to search for missing statistical area fields and substitute the ‘predominant’ (most frequent) statistical area for the trip for trips which report the statistical area fished in other records.

-- © Copyright 2026 The Kahawai Collective
-- SPDX-License-Identifier: MIT
INSERT INTO grooming_rules
VALUES ('FESAI', 'edw', 'effort', 'Fix', 'Substitute the modal statistical area from a trip for missing areas');

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

with _fesai as (
    SELECT DISTINCT ON (trip) trip, start_stats_area_code, count(*)
    FROM groomed.effort
    WHERE start_stats_area_code IS NOT NULL
    AND trip IN (
        SELECT DISTINCT trip
        FROM groomed.effort
        WHERE start_stats_area_code IS NULL AND trip IS NOT NULL
    )
    GROUP BY trip, start_stats_area_code
    ORDER BY trip, count DESC, start_stats_area_code
)
INSERT INTO groomed.checks (code, "table", "column", id, orig, new)
SELECT 'FESAI', 'effort', 'start_stats_area_code', e.id, NULL, _fesai.start_stats_area_code
FROM   _fesai
JOIN   groomed.effort e ON e.trip = _fesai.trip
AND    e.start_stats_area_code IS NULL;

UPDATE groomed.effort
SET checks = checks || '{FESAI}', start_stats_area_code = x.new
FROM groomed.checks x
WHERE x.code = 'FESAI'
AND groomed.effort.id = x.id;

$$ LANGUAGE SQL;
SELECT FESAI();

Effects of grooming

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

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