Project

General

Profile

« Previous | Next » 

Revision 11628

Added by Aaron Marcuse-Kubitza about 11 years ago

inputs/.TNRS/schema.sql: tnrs: added scrubbed_taxon_name_with_author derived column, which uses the matched name when an accepted name is not available

View differences:

schema.sql
83 83 
    LANGUAGE plpgsql
84 84 
    AS $$
85 85 
DECLARE
86 
    "Specific_epithet_is_plant" boolean :=
87 
        (CASE
88 
        WHEN   new."Infraspecific_epithet_matched"   IS NOT NULL
89 
            OR new."Infraspecific_epithet_2_matched" IS NOT NULL
90 
            OR new."Specific_epithet_score" >= 0.9 -- fuzzy match
91 
            THEN true
92 
        ELSE NULL -- ambiguous
93 
        END);
94 
    never_homonym boolean = COALESCE(new."Author_score" >= 0.6, false); -- author disambiguates
95 
    family_is_homonym boolean = NOT never_homonym AND "TNRS".family_is_homonym(new."Family_matched");
96 
    genus_is_homonym  boolean = NOT never_homonym AND "TNRS".genus_is_homonym( new."Genus_matched" );
86 
	"Specific_epithet_is_plant" boolean :=
87 
		(CASE
88 
		WHEN   new."Infraspecific_epithet_matched"	 IS NOT NULL
89 
			OR new."Infraspecific_epithet_2_matched" IS NOT NULL
90 
			OR new."Specific_epithet_score" >= 0.9 -- fuzzy match
91 
			THEN true
92 
		ELSE NULL -- ambiguous
93 
		END);
94 
	never_homonym boolean = COALESCE(new."Author_score" >= 0.6, false);
95 
		-- author disambiguates
96 
	family_is_homonym boolean = NOT never_homonym
97 
		AND "TNRS".family_is_homonym(new."Family_matched");
98 
	genus_is_homonym  boolean = NOT never_homonym
99 
		AND "TNRS".genus_is_homonym(new."Genus_matched");
100 
	matched_taxon_name_with_author text = NULLIF(concat_ws(' '
101 
		, NULLIF(NULLIF(new."Name_matched_accepted_family", 'Unknown'),
102 
			new."Name_matched")
103 
		, NULLIF(new."Name_matched", 'No suitable matches found.')
104 
		, new."Name_matched_author"
105 
		), '');
97 106 
BEGIN
98 
    new."Accepted_scientific_name" = NULLIF(concat_ws(' '
99 
        , NULLIF(NULLIF(new."Accepted_name_family", 'Unknown'), new."Accepted_name")
100 
        , new."Accepted_name"
101 
        , new."Accepted_name_author"
102 
    ), '');
103 
    new."Is_homonym" = COALESCE(genus_is_homonym, family_is_homonym);
104 
    new."Is_plant" = (CASE
105 
        WHEN new."Family_score" = 1 AND NOT family_is_homonym -- exact match
106 
            THEN true
107 
        ELSE -- consider genus
108 
            (CASE
109 
            WHEN new."Genus_score" =  1    -- exact match
110 
                THEN
111 
                (CASE
112 
                WHEN NOT genus_is_homonym THEN true
113 
                ELSE "Specific_epithet_is_plant"
114 
                END)
115 
            WHEN new."Genus_score" >= 0.85 -- fuzzy match
116 
                THEN "Specific_epithet_is_plant"
117 
            ELSE NULL -- ambiguous
118 
            END)
119 
        END);
120 
    /* exclude homonyms because these are not valid matches (i.e. TNRS provides
121 
    a name, but the name is not meaningful because it is not unambiguous). */
122 
    new.is_valid_match = COALESCE(new."Is_plant", false);
123
124 
    RETURN new;
107 
	new."Accepted_scientific_name" = NULLIF(concat_ws(' '
108 
		, NULLIF(NULLIF(new."Accepted_name_family", 'Unknown'),
109 
			new."Accepted_name")
110 
		, new."Accepted_name"
111 
		, new."Accepted_name_author"
112 
		), '');
113 
	new."Is_homonym" = COALESCE(genus_is_homonym, family_is_homonym);
114 
	new."Is_plant" = (CASE
115 
		WHEN new."Family_score" = 1 AND NOT family_is_homonym -- exact match
116 
			THEN true
117 
		ELSE -- consider genus
118 
			(CASE
119 
			WHEN new."Genus_score" =  1	   -- exact match
120 
				THEN
121 
				(CASE
122 
				WHEN NOT genus_is_homonym THEN true
123 
				ELSE "Specific_epithet_is_plant"
124 
				END)
125 
			WHEN new."Genus_score" >= 0.85 -- fuzzy match
126 
				THEN "Specific_epithet_is_plant"
127 
			ELSE NULL -- ambiguous
128 
			END)
129 
		END);
130 
	/* exclude homonyms because these are not valid matches (i.e. TNRS provides
131 
	a name, but the name is not meaningful because it is not unambiguous). */
132 
	new.is_valid_match = COALESCE(new."Is_plant", false);
133 
	new.scrubbed_taxon_name_with_author = COALESCE(
134 
		new."Accepted_scientific_name", matched_taxon_name_with_author);
135
136 
	RETURN new;
125 137 
END;
126 138 
$$;
127 139 

  		




  ......




  186
  198
  
    
    "Accepted_scientific_name" text,


  		




  187
  199
  
    
    "Is_homonym" boolean,


  		




  188
  200
  
    
    "Is_plant" boolean,


  		




  189
  
  
    
    is_valid_match boolean NOT NULL


  		




  
  201
  
    
    is_valid_match boolean NOT NULL,


  		




  
  202
  
    
    scrubbed_taxon_name_with_author text


  		




  190
  203
  
    
);


  		




  191
  204
  
    

  		




  192
  205
  
    

  		












Also available in:  Unified diff