Backbon Homonym SQL snippets

GENUS_HOMONYMS.sql

-- ALL GENUS HOMONYMS
WITH homs AS (
 SELECT u.rank, n.canonical_name
 FROM name_usage u JOIN name n ON u.name_fk = n.id
 WHERE u.dataset_key = nubkey() AND u.deleted IS NULL AND NOT u.is_synonym AND u.rank='GENUS'::rank
 GROUP BY u.rank, n.canonical_name
 HAVING count(*) > 1
) 
SELECT u.id, n.canonical_name, u.rank, n.scientific_name, u.is_synonym, u.status,
    u.kingdom_fk, u.phylum_fk, u.class_fk, u.order_fk, u.family_fk, u.genus_fk
 FROM name_usage u 
    JOIN name n ON u.name_fk = n.id
    JOIN homs h ON h.rank=u.rank AND h.canonical_name=n.canonical_name
 WHERE u.dataset_key = nubkey() AND u.deleted IS NULL AND NOT u.is_synonym
 ORDER BY canonical_name;




-- GENUS HOMONYMS WITH THE SAME CLASSIFICATION, ALL DOUBTFUL AND AT LEAST ONE MISSING AUTHORSHIP
WITH homs AS (
 SELECT u.rank, n.canonical_name, u.kingdom_fk, u.phylum_fk, u.class_fk, u.order_fk, u.family_fk
 FROM name_usage u JOIN name n ON u.name_fk = n.id
 WHERE u.dataset_key = nubkey() AND u.deleted IS NULL AND NOT u.is_synonym AND u.rank='GENUS'::rank 
 GROUP BY u.rank, n.canonical_name, u.kingdom_fk, u.phylum_fk, u.class_fk, u.order_fk, u.family_fk
 HAVING count(*) > 1 AND count(distinct status)=1 AND bool_or(n.canonical_name=n.scientific_name) 
) 
SELECT u.rank, n.canonical_name, u.id, n.scientific_name, u.status, u.kingdom_fk, u.source_taxon_key, u.constituent_key
 FROM name_usage u 
    JOIN name n ON u.name_fk = n.id
    JOIN homs h ON h.rank=u.rank AND h.canonical_name=n.canonical_name 
        AND u.kingdom_fk=h.kingdom_fk AND u.phylum_fk=h.phylum_fk AND u.class_fk=h.class_fk AND u.order_fk=h.order_fk AND u.family_fk=h.family_fk
 WHERE u.dataset_key = nubkey() AND u.deleted IS NULL AND NOT u.is_synonym AND u.status='DOUBTFUL'::taxonomic_status
 ORDER BY scientific_name;




-- GENUS HOMONYMS WITH THE SAME CLASSIFICATION, ALL DOUBTFUL AND AT LEAST ONE MISSING AUTHORSHIP
-- BUT RETURNING ALL USAGES WITHIN THAT KINGDOM WITH THAT NAME REGARDLESS THEIR CLASSIFICATION
WITH homs AS (
 SELECT u.rank, n.canonical_name, u.kingdom_fk, u.phylum_fk, u.class_fk, u.order_fk, u.family_fk
 FROM name_usage u JOIN name n ON u.name_fk = n.id
 WHERE u.dataset_key = nubkey() AND u.deleted IS NULL AND NOT u.is_synonym AND u.rank='GENUS'::rank 
 GROUP BY u.rank, n.canonical_name, u.kingdom_fk, u.phylum_fk, u.class_fk, u.order_fk, u.family_fk
 HAVING count(*) > 1 AND count(distinct status)=1 AND bool_or(n.canonical_name=n.scientific_name) 
) 
SELECT u.rank, n.canonical_name, u.id, n.scientific_name, u.status, u.kingdom_fk, u.source_taxon_key, u.constituent_key
 FROM name_usage u 
    JOIN name n ON u.name_fk = n.id
    JOIN homs h ON h.rank=u.rank AND h.canonical_name=n.canonical_name AND u.kingdom_fk=h.kingdom_fk
 WHERE u.dataset_key = nubkey() AND u.deleted IS NULL AND NOT u.is_synonym
 ORDER BY scientific_name;