Project

General

Profile

« Previous | Next » 

Revision 9763

Added by Aaron Marcuse-Kubitza about 11 years ago

inputs/.TNRS/schema.sql: added Is_plant derived field, which is populated using the formula at vegpath.org/wiki/Result_filtering#TNRS-results . note that the homonym filtering is currently excluded until we determine whether we can get direct access to the IRMNG homonyms database (http://www.cmar.csiro.au/datacentre/irmng/homonyms.htm). note also that changes to the TNRS schema cannot be fully tested until any TNRS client bugs are fixed, because the data.sql updater requires a working TNRS client to regenerate the sample data.

View differences:

inputs/.TNRS/schema.sql
50 50 
  "Accepted_name_lsid" text,
51 51 
  "Accepted_scientific_name" text,
52 52 
  "Max_score" double precision,
53 
  "Is_plant" boolean,
53 54 
  CONSTRAINT tnrs_pkey PRIMARY KEY ("Name_submitted" )
54 55 
)
55 56 
WITH (
......
65 66 
CREATE OR REPLACE FUNCTION tnrs_populate_fields()
66 67 
  RETURNS trigger AS
67 68 
$BODY$
69 
DECLARE
70 
    "Specific_epithet_is_plant" boolean :=
71 
        (CASE
72 
        WHEN   new."Infraspecific_epithet_matched"   IS NOT NULL
73 
            OR new."Infraspecific_epithet_2_matched" IS NOT NULL
74 
            OR new."Specific_epithet_score"::double precision >= 0.9 -- fuzzy match
75 
            THEN true
76 
        ELSE NULL -- ambiguous
77 
        END);
68 78 
BEGIN
69 79 
    new."Accepted_scientific_name" = NULLIF(concat_ws(' '
70 80 
        , NULLIF(NULLIF(new."Accepted_name_family", 'Unknown'), new."Accepted_name")
......
77 87 
        , new."Genus_score"::double precision
78 88 
        , new."Specific_epithet_score"::double precision
79 89 
    );
90 
    new."Is_plant" = (CASE
91 
        WHEN new."Family_matched" IS NOT NULL THEN true
92 
        ELSE -- Family_matched IS NULL
93 
            (CASE
94 
            WHEN new."Genus_score"::double precision =  1    -- exact match
95 
                THEN "Specific_epithet_is_plant"
96 
            WHEN new."Genus_score"::double precision >= 0.85 -- fuzzy match
97 
                THEN "Specific_epithet_is_plant"
98 
            ELSE NULL -- ambiguous
99 
            END)
100 
        END);
80 101
81 102 
    RETURN new;
82 103 
END;

Also available in: Unified diff