• Table of contents
• TNRS derived columns
  • TNRS.taxon_match
    • is_valid_match
    • scrubbed_unique_taxon_name
  • TNRS.MatchedTaxon
    • taxonomic_status
    • TNRS.map_taxonomic_status():
  • TNRS.taxon_scrub
    • scrubbed_morphospecies_binomial
  • public.analytical_stem_view
    • scientificName_verbatim

TNRS derived columns¶

TNRS.taxon_match¶

is_valid_match¶

DECLARE
    "Specific_epithet_is_plant" boolean :=
        (CASE
        WHEN   new."Infraspecific_epithet_matched"     IS NOT NULL
            OR new."Infraspecific_epithet_2_matched" IS NOT NULL
            OR new."Specific_epithet_score" >= 0.9 -- fuzzy match
            THEN true
        ELSE NULL -- ambiguous
        END);
    never_homonym boolean = COALESCE(new."Author_score" >= 0.6, false);
        -- author disambiguates
    family_is_homonym boolean = NOT never_homonym
        AND "TNRS".family_is_homonym(new."Family_matched");
    genus_is_homonym  boolean = NOT never_homonym
        AND "TNRS".genus_is_homonym(new."Genus_matched");
BEGIN
    /* exclude homonyms because these are not valid matches (TNRS provides a
    name, but the name is not meaningful because it is not unambiguous) */
    new.is_valid_match = new."Taxonomic_status" != 'Invalid'
        AND COALESCE(CASE
        WHEN new."Family_score" = 1 AND NOT family_is_homonym -- exact match
            THEN true
        ELSE -- consider genus
            (CASE
            WHEN new."Genus_score" =  1       -- exact match
                THEN
                (CASE
                WHEN NOT genus_is_homonym THEN true
                ELSE "Specific_epithet_is_plant" 
                END)
            WHEN new."Genus_score" >= 0.85 -- fuzzy match
                THEN "Specific_epithet_is_plant" 
            ELSE NULL -- ambiguous
            END)
        END, false);
END;

scrubbed_unique_taxon_name¶

-- variables
matched_taxon_name_with_author text = NULLIF(concat_ws(' '
    , NULLIF(NULLIF(new."Name_matched_accepted_family", 'Unknown'),
        new."Name_matched")
    , NULLIF(new."Name_matched", 'No suitable matches found.')
    , new."Name_matched_author" 
    ), '');
accepted_taxon_name_with_author text = NULLIF(concat_ws(' '
    , NULLIF(NULLIF(new."Accepted_name_family", 'Unknown'),
        new."Accepted_name")
    , new."Accepted_name" 
    , new."Accepted_name_author" 
    ), '');

-- formula
new.scrubbed_unique_taxon_name = COALESCE(
    accepted_taxon_name_with_author, matched_taxon_name_with_author);

TNRS.MatchedTaxon¶

taxonomic_status¶

"TNRS".map_taxonomic_status(tnrs."Taxonomic_status", tnrs."Accepted_name")

TNRS.map_taxonomic_status():¶

CREATE OR REPLACE FUNCTION "TNRS".map_taxonomic_status(taxonomic_status_verbatim text, accepted_taxon_name_no_author text)
  RETURNS text AS
$BODY$
/* from http://vegbiendev.nceas.ucsb.edu/wiki/2013-11-14_conference_call#taxonomic-fields:
"taxonomic_status should be accepted instead of synonym when an accepted name is
available (this is not always the case when a name is marked as a synonym)" */
SELECT CASE WHEN $2 IS NOT NULL THEN 'accepted' ELSE lower($1) END
$BODY$
  LANGUAGE sql IMMUTABLE
  COST 100;

TNRS.taxon_scrub¶

scrubbed_morphospecies_binomial¶

CASE
WHEN scrubbed_taxon_rank = 'family'::text THEN concat_ws(' '::text, scrubbed_family, "morphospeciesSuffix")
WHEN scrubbed_taxon_rank = 'genus'::text THEN concat_ws(' '::text, scrubbed_taxon_name_no_author, "morphospeciesSuffix")
ELSE (scrubbed_genus || ' '::text) || scrubbed_specific_epithet
END

public.analytical_stem_view¶

scientificName_verbatim¶

COALESCE(taxonverbatim.taxonomicname, taxonverbatim.taxonname || COALESCE(' '::text || taxonverbatim.author, ''::text), taxonlabel.taxonomicname)