Project

General

Profile

1 7844 aaronmk
CREATE OR REPLACE FUNCTION score_ok(score double precision)
2
  RETURNS boolean AS
3
$BODY$
4
SELECT $1 >= 0.8
5
$BODY$
6
  LANGUAGE sql VOLATILE
7
  COST 100;
8
9 9756 aaronmk
/* IMPORTANT: when changing this table's schema, you must regenerate data.sql:
10
$ <this_file>/../test_taxonomic_names/test_scrub
11
*/
12 5183 aaronmk
CREATE TABLE tnrs
13 5110 aaronmk
(
14 9513 aaronmk
  "Time_submitted" timestamp with time zone DEFAULT now(),
15 5110 aaronmk
  "Name_number" text,
16
  "Name_submitted" text NOT NULL,
17
  "Overall_score" text,
18
  "Name_matched" text,
19
  "Name_matched_rank" text,
20
  "Name_score" text,
21
  "Name_matched_author" text,
22
  "Name_matched_url" text,
23
  "Author_matched" text,
24
  "Author_score" text,
25
  "Family_matched" text,
26
  "Family_score" text,
27 9529 aaronmk
  "Name_matched_accepted_family" text,
28 5110 aaronmk
  "Genus_matched" text,
29
  "Genus_score" text,
30
  "Specific_epithet_matched" text,
31
  "Specific_epithet_score" text,
32
  "Infraspecific_rank" text,
33
  "Infraspecific_epithet_matched" text,
34
  "Infraspecific_epithet_score" text,
35
  "Infraspecific_rank_2" text,
36
  "Infraspecific_epithet_2_matched" text,
37
  "Infraspecific_epithet_2_score" text,
38
  "Annotations" text,
39
  "Unmatched_terms" text,
40
  "Taxonomic_status" text,
41
  "Accepted_name" text,
42
  "Accepted_name_author" text,
43
  "Accepted_name_rank" text,
44
  "Accepted_name_url" text,
45 9762 aaronmk
  "Accepted_name_species" text,
46
  "Accepted_name_family" text,
47 5110 aaronmk
  "Selected" text,
48
  "Source" text,
49
  "Warnings" text,
50
  "Accepted_name_lsid" text,
51 7133 aaronmk
  "Accepted_scientific_name" text,
52 7293 aaronmk
  "Max_score" double precision,
53 9763 aaronmk
  "Is_plant" boolean,
54 5110 aaronmk
  CONSTRAINT tnrs_pkey PRIMARY KEY ("Name_submitted" )
55
)
56
WITH (
57
  OIDS=FALSE
58
);
59 7132 aaronmk
60 7844 aaronmk
CREATE UNIQUE INDEX tnrs_score_ok
61
  ON tnrs
62
  USING btree
63
  ("Name_submitted" )
64
  WHERE score_ok("Max_score");
65
66 9765 aaronmk
/* IMPORTANT: when changing this function, you must regenerate the derived cols:
67
UPDATE tnrs SET "Name_submitted" = "Name_submitted"
68 9767 aaronmk
runtime: 17 min ("4992166 rows affected, 1019907 ms execution time")
69 9765 aaronmk
*/
70 9512 aaronmk
CREATE OR REPLACE FUNCTION tnrs_populate_fields()
71 7134 aaronmk
  RETURNS trigger AS
72
$BODY$
73 9763 aaronmk
DECLARE
74
    "Specific_epithet_is_plant" boolean :=
75
        (CASE
76
        WHEN   new."Infraspecific_epithet_matched"   IS NOT NULL
77
            OR new."Infraspecific_epithet_2_matched" IS NOT NULL
78
            OR new."Specific_epithet_score"::double precision >= 0.9 -- fuzzy match
79
            THEN true
80
        ELSE NULL -- ambiguous
81
        END);
82 7134 aaronmk
BEGIN
83 7848 aaronmk
    new."Accepted_scientific_name" = NULLIF(concat_ws(' '
84 9762 aaronmk
        , NULLIF(NULLIF(new."Accepted_name_family", 'Unknown'), new."Accepted_name")
85 7134 aaronmk
        , new."Accepted_name"
86
        , new."Accepted_name_author"
87 7848 aaronmk
    ), '');
88 7293 aaronmk
    new."Max_score" = GREATEST(
89
          new."Overall_score"::double precision
90
        , new."Family_score"::double precision
91
        , new."Genus_score"::double precision
92
        , new."Specific_epithet_score"::double precision
93
    );
94 9763 aaronmk
    new."Is_plant" = (CASE
95 9764 aaronmk
        WHEN new."Family_score"::double precision = 1 THEN true -- exact match
96 9763 aaronmk
        ELSE -- Family_matched IS NULL
97
            (CASE
98
            WHEN new."Genus_score"::double precision =  1    -- exact match
99
                THEN "Specific_epithet_is_plant"
100
            WHEN new."Genus_score"::double precision >= 0.85 -- fuzzy match
101
                THEN "Specific_epithet_is_plant"
102
            ELSE NULL -- ambiguous
103
            END)
104
        END);
105 7134 aaronmk
106
    RETURN new;
107
END;
108
$BODY$
109
  LANGUAGE plpgsql VOLATILE
110
  COST 100;
111
112 9512 aaronmk
CREATE TRIGGER tnrs_populate_fields
113 7134 aaronmk
  BEFORE INSERT OR UPDATE
114
  ON tnrs
115
  FOR EACH ROW
116 9512 aaronmk
  EXECUTE PROCEDURE tnrs_populate_fields();
117 7251 aaronmk
118
119 9759 aaronmk
CREATE OR REPLACE VIEW "MatchedTaxon" AS
120 7823 aaronmk
SELECT
121
  "Time_submitted" AS "*Name_matched.Time_submitted"
122 7830 aaronmk
, "Name_submitted" AS "concatenatedScientificName"
123 7829 aaronmk
, "Name_matched" AS "matchedTaxonName"
124 7823 aaronmk
, "Name_matched_rank" AS "matchedTaxonRank"
125
, "Name_score" AS "*Name_matched.Name_score"
126
, "Name_matched_author" AS "matchedScientificNameAuthorship"
127
, "Name_matched_url" AS "matchedScientificNameID"
128
, "Author_score" AS "*Name_matched.Author_score"
129
, "Family_score" AS "matchedFamilyConfidence_fraction"
130 9762 aaronmk
, COALESCE("Name_matched_accepted_family", "Accepted_name_family") AS "matchedFamily"
131 7831 aaronmk
, "Genus_matched" AS "matchedGenus"
132 7823 aaronmk
, "Genus_score" AS "matchedGenusConfidence_fraction"
133
, "Specific_epithet_matched" AS "matchedSpecificEpithet"
134
, "Specific_epithet_score" AS "matchedSpeciesConfidence_fraction"
135
, "Infraspecific_epithet_matched" AS "matchedInfraspecificEpithet"
136
, "Infraspecific_epithet_score" AS "*Name_matched.Infraspecific_epithet_score"
137
, "Annotations" AS "identificationQualifier"
138
, "Unmatched_terms" AS "morphospeciesSuffix"
139 7833 aaronmk
, "Taxonomic_status" AS "taxonomicStatus"
140 7823 aaronmk
, "Accepted_name" AS "acceptedTaxonName"
141
, "Accepted_name_author" AS "acceptedScientificNameAuthorship"
142
, "Accepted_name_rank" AS "acceptedTaxonRank"
143
, "Accepted_name_url" AS "acceptedScientificNameID"
144 9762 aaronmk
, "Accepted_name_species" AS "*Name_matched.Accepted_name_species"
145
, "Accepted_name_family" AS "acceptedFamily"
146 7823 aaronmk
, "Selected" AS "*Name_matched.Selected"
147 7828 aaronmk
, "Source" AS "*Name_matched.Source"
148 7823 aaronmk
, "Warnings" AS "*Name_matched.Warnings"
149
, "Accepted_name_lsid" AS "*Name_matched.Accepted_name_lsid"
150
, "Accepted_scientific_name" AS "acceptedScientificName"
151
, "Max_score" AS "matchedTaxonConfidence_fraction"
152 9616 aaronmk
FROM tnrs
153
;
154 7823 aaronmk
155 9759 aaronmk
CREATE OR REPLACE VIEW "ValidMatchedTaxon" AS
156
SELECT *
157
FROM "MatchedTaxon"
158
WHERE score_ok("matchedTaxonConfidence_fraction")
159
;
160
161 7823 aaronmk
CREATE OR REPLACE VIEW "AcceptedTaxon" AS
162
SELECT
163
  "Time_submitted" AS "*Accepted_name.Time_submitted"
164
, "Name_submitted" AS "acceptedScientificName"
165
, "Genus_matched" AS "acceptedGenus"
166
, "Specific_epithet_matched" AS "acceptedSpecificEpithet"
167
, "Infraspecific_epithet_matched" AS "acceptedInfraspecificEpithet"
168
, "Annotations" AS "*Accepted_name.Annotations"
169
, "Taxonomic_status" AS "acceptedTaxonomicStatus"
170
, "Selected" AS "*Accepted_name.Selected"
171
, "Source" AS "*Accepted_name.Source"
172
, "Warnings" AS "*Accepted_name.Warnings"
173
, "Accepted_name_lsid" AS "*Accepted_name.Accepted_name_lsid"
174 9760 aaronmk
FROM tnrs
175
;
176 7823 aaronmk
177
CREATE OR REPLACE VIEW "ScrubbedTaxon" AS
178
SELECT *
179 9758 aaronmk
FROM "ValidMatchedTaxon"
180 7823 aaronmk
NATURAL LEFT JOIN "AcceptedTaxon"
181
;