Skip to main content

Source: ebisu/docs/reference/schemas/asfis_species/asfis_trade_data_summary.md | ✏️ Edit on GitHub

ASFIS Trade Data Implementation Summary

✅ Business Logic Understanding Corrected​

What ASFIS Really Is:​

  • 🐟 Trade Data - Global fisheries trade tracking system
  • 📊 Alpha3_Code - Trade codes for import/export tracking
  • 🔗 Shared Codes - Same trade code applies to multiple species (by design)
  • 📈 Taxonomic Cascading - Higher taxonomic ranks cascade down to species level

Key Business Rules:​

  1. 1:1 Mapping - One trade code → One species
  2. 1:Many Mapping - One trade code → Multiple related species
  3. Taxonomic Cascading - Family/Order level codes apply to all species below

🔧 Technical Implementation Fixed​

Database Schema:​

-- ✅ CORRECT: No unique constraints on Alpha3_Code
CREATE INDEX idx_asfis_alpha3_code ON asfis_species("Alpha3_Code");

-- ✅ CORRECT: Composite index for future cascading
CREATE INDEX idx_asfis_trade_cascade ON asfis_species("Alpha3_Code", "taxonRank");

Preprocessing Logic:​

# ✅ CORRECT: Keep Alpha3_Code for all species
# Both species legitimately share the same trade code
# This is correct business logic for trade data

No Constraints Violated:​

  • ✅ UUID Primary Key - Prevents all constraint violations
  • ✅ Shared Alpha3_Codes - Legitimate business requirement
  • ✅ All Data Preserved - No artificial clearing of trade codes

📊 New Analysis Functions​

1. Shared Trade Codes Analysis:​

SELECT * FROM get_asfis_shared_trade_codes();
-- Shows which Alpha3_Codes apply to multiple species

2. Higher Taxonomic Ranks (For Cascading):​

SELECT * FROM get_asfis_higher_taxa_codes();
-- Identifies Family/Order level codes that need cascading

3. WoRMS/ITIS Mapping Preparation:​

SELECT * FROM get_asfis_for_mapping();
-- Prioritizes species-level mappings first, then higher taxa

🎯 Future WoRMS/ITIS Integration Plan​

Phase 1: Direct Species Mapping (Options 1 & 2)​

-- Map ASFIS species directly to WoRMS/ITIS (1:1 and 1:Many cases)
SELECT 
    a."Alpha3_Code",
    a."scientificName" as asfis_name,
    w."scientificName" as worms_name,
    i.complete_name as itis_name,
    'Direct species mapping' as assignment_type
FROM asfis_species a
LEFT JOIN worms_taxonomic_units w ON a."scientificName" = w."scientificName"
LEFT JOIN itis_taxonomic_units i ON a."scientificName" = i.complete_name
WHERE a."taxonRank" IN ('Species', 'Subspecies');

Phase 2: Family-Level Cascading (Option 3 - Family)​

-- Apply family-level Alpha3_Codes to all species within that family
WITH family_trade_codes AS (
    SELECT "Alpha3_Code", "scientificName" as family_name
    FROM asfis_species 
    WHERE "taxonRank" = 'Family'
)
SELECT 
    ftc."Alpha3_Code",
    w."scientificName",
    w."family",
    'Family-level cascade' as assignment_type
FROM worms_taxonomic_units w
JOIN family_trade_codes ftc ON w."family" = ftc.family_name
WHERE w."taxonRank" IN ('Species', 'Subspecies')  -- Only assign to actual species

UNION ALL

-- Same for ITIS
SELECT 
    ftc."Alpha3_Code",
    i.complete_name,
    i."Family",  -- Assuming Family column exists in ITIS processing
    'Family-level cascade (ITIS)' as assignment_type
FROM itis_taxonomic_units i
JOIN family_trade_codes ftc ON i."Family" = ftc.family_name
WHERE i.rank_id IN (SELECT rank_id FROM itis_taxon_unit_types 
                    WHERE rank_name IN ('Species', 'Subspecies'));

Phase 3: Order-Level Cascading (Option 3 - Order)​

-- Apply order-level Alpha3_Codes to all families, genera, species within that order
WITH order_trade_codes AS (
    SELECT "Alpha3_Code", "scientificName" as order_name
    FROM asfis_species 
    WHERE "taxonRank" = 'Order'
)
SELECT 
    otc."Alpha3_Code",
    w."scientificName",
    w."order",
    w."family",
    'Order-level cascade' as assignment_type
FROM worms_taxonomic_units w
JOIN order_trade_codes otc ON w."order" = otc.order_name;

Phase 4: Higher Taxonomic Cascading (Class, Phylum, Kingdom)​

-- Apply class/phylum/kingdom-level codes (following same pattern)
WITH higher_trade_codes AS (
    SELECT "Alpha3_Code", "scientificName" as taxon_name, "taxonRank"
    FROM asfis_species 
    WHERE "taxonRank" IN ('Class', 'Phylum', 'Kingdom')
)
SELECT 
    htc."Alpha3_Code",
    w."scientificName",
    CASE htc."taxonRank"
        WHEN 'Class' THEN w."class"
        WHEN 'Phylum' THEN w."phylum"  
        WHEN 'Kingdom' THEN w."kingdom"
    END as matched_taxon,
    htc."taxonRank" || '-level cascade' as assignment_type
FROM worms_taxonomic_units w
JOIN higher_trade_codes htc ON (
    (htc."taxonRank" = 'Class' AND w."class" = htc.taxon_name) OR
    (htc."taxonRank" = 'Phylum' AND w."phylum" = htc.taxon_name) OR
    (htc."taxonRank" = 'Kingdom' AND w."kingdom" = htc.taxon_name)
);

📈 Expected Results​

Import Success:​

  • 🎯 13,733 ASFIS records imported successfully
  • 🔍 ~25 additional species rows from multi-species entries
  • ✅ All Alpha3_Codes preserved (no artificial clearing)
  • 📊 Trade codes properly shared across related species

Data Structure Examples:​

Option 1: Single Species Code (1:1)​

Example: Alpha3_Code 'YFT' (Yellowfin Tuna)
└── Thunnus albacares (primary species)
Only one species has the trade code YFT

Option 2: Multi-Species Code (1:Many)​

Example: Alpha3_Code 'HKC' (Merluccius capensis) and (Merluccius paradoxus)
├── Merluccius capensis (primary species)
└── Merluccius paradoxus (primary species)  
Only two species share the trade code HKC

Option 3: Higher Taxonomic Code with Cascading (1:Hierarchy)​

Example: Alpha3_Code 'CAX' (Ariidae)
├── Ariidae (primary family - rank assignment)
├── Amissidens (related genus - inherits CAX)
├── Amphiarius (related genus - inherits CAX) 
├── Aspistor (related genus - inherits CAX) 
├── Carlarius (related genus - inherits CAX)
├── Carlarius heudelotii (related species - inherits CAX)
├── Carlarius latiscutatus (related species - inherits CAX)
└── [All other taxa within Ariidae] (inherit CAX)
The family-level trade code CAX cascades to ALL genera, species, 
subspecies, and other taxa within the Ariidae family hierarchy

Cascading Logic Implementation:​

How to Determine Hierarchical Membership:​

-- For WoRMS: Use taxonomic columns
SELECT w."scientificName", 'CAX' as inherited_alpha3_code
FROM worms_taxonomic_units w
WHERE w."family" = 'Ariidae';  -- All taxa in this family get CAX

-- For ITIS: Use taxonomic hierarchy and rank_name
SELECT i.complete_name, 'CAX' as inherited_alpha3_code  
FROM itis_taxonomic_units i
JOIN itis_hierarchy h ON i.tsn = h.tsn
WHERE h.parent_tsn IN (
    SELECT tsn FROM itis_taxonomic_units 
    WHERE complete_name = 'Ariidae' AND rank_name = 'Family'
);

Future Cascading Query Examples:​

-- Find all WoRMS species that inherit family-level trade codes
WITH family_codes AS (
    SELECT "Alpha3_Code", "scientificName" as family_name
    FROM asfis_species 
    WHERE "taxonRank" = 'Family'
)
SELECT 
    w."scientificName",
    fc."Alpha3_Code" as inherited_trade_code,
    'Family-level inheritance' as assignment_type
FROM worms_taxonomic_units w
JOIN family_codes fc ON w."family" = fc.family_name;

-- Find all ITIS species that inherit order-level trade codes  
WITH order_codes AS (
    SELECT "Alpha3_Code", "scientificName" as order_name
    FROM asfis_species 
    WHERE "taxonRank" = 'Order'
)
SELECT 
    i.complete_name,
    oc."Alpha3_Code" as inherited_trade_code,
    'Order-level inheritance' as assignment_type
FROM itis_taxonomic_units i
JOIN order_codes oc ON i."Order_or_higher_taxa" = oc.order_name;

Query Capabilities:​

-- Option 1: Find single species trade codes
SELECT * FROM asfis_species WHERE "Alpha3_Code" = 'YFT';
-- Returns: Only Thunnus albacares

-- Option 2: Find multi-species trade codes  
SELECT * FROM asfis_species WHERE "Alpha3_Code" = 'HKC';
-- Returns: Merluccius capensis AND Merluccius paradoxus

-- Option 3: Find higher taxonomic trade codes (requires cascading)
SELECT * FROM asfis_species WHERE "Alpha3_Code" = 'CAX';
-- Returns: Ariidae (family) - but ALL species in this family should inherit CAX

-- Categorize all trade code types
SELECT * FROM get_asfis_trade_code_types();
-- Shows which codes are 1:1, 1:Many, or 1:Hierarchy

-- Find cascading implementation candidates
SELECT * FROM get_asfis_cascading_candidates();
-- Shows family/order/class level codes that need taxonomic expansion

🚀 Implementation Status​

✅ Preprocessing - Correctly creates separate rows per species
✅ Database Schema - Optimized for trade data with shared codes
✅ Import Process - No constraint violations, all data preserved
✅ Analysis Functions - Ready for WoRMS/ITIS mapping
✅ Future Ready - Taxonomic cascading logic prepared

The ASFIS table structure is now perfectly aligned with the trade data business requirements! 🐟✨