Project

General

Profile

1
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 IMMUTABLE STRICT
7
  COST 100;
8

    
9
CREATE OR REPLACE FUNCTION family_is_homonym(family text)
10
  RETURNS boolean AS
11
$BODY$
12
SELECT EXISTS(SELECT * FROM "IRMNG".family_homonym_epithet WHERE "taxonNameOrEpithet" = $1)
13
$BODY$
14
  LANGUAGE sql STABLE STRICT
15
  COST 100;
16

    
17
CREATE OR REPLACE FUNCTION genus_is_homonym(genus text)
18
  RETURNS boolean AS
19
$BODY$
20
SELECT EXISTS(SELECT * FROM "IRMNG".genus_homonym_epithet WHERE "taxonNameOrEpithet" = $1)
21
$BODY$
22
  LANGUAGE sql STABLE STRICT
23
  COST 100;
24

    
25
/* IMPORTANT: when changing this table's schema, you must regenerate data.sql:
26
$ <this_file>/../test_taxonomic_names/test_scrub
27
you must also make the same changes on vegbiendev: e.g.
28
$ make backups/TNRS.backup-remake # runtime: 3 min ("real 2m44.277s")
29
SELECT util.set_col_types('"TNRS".tnrs', ARRAY[
30
  ('col', 'new_type')
31
]::util.col_cast[]);
32
runtime: 4 min ("242100 ms")
33
*/
34
CREATE TABLE tnrs
35
(
36
  "Time_submitted" timestamp with time zone NOT NULL DEFAULT now(),
37
  "Name_number" integer NOT NULL,
38
  "Name_submitted" text NOT NULL,
39
  "Overall_score" double precision,
40
  "Name_matched" text,
41
  "Name_matched_rank" text,
42
  "Name_score" double precision,
43
  "Name_matched_author" text,
44
  "Name_matched_url" text,
45
  "Author_matched" text,
46
  "Author_score" double precision,
47
  "Family_matched" text,
48
  "Family_score" double precision,
49
  "Name_matched_accepted_family" text,
50
  "Genus_matched" text,
51
  "Genus_score" double precision,
52
  "Specific_epithet_matched" text,
53
  "Specific_epithet_score" double precision,
54
  "Infraspecific_rank" text,
55
  "Infraspecific_epithet_matched" text,
56
  "Infraspecific_epithet_score" double precision,
57
  "Infraspecific_rank_2" text,
58
  "Infraspecific_epithet_2_matched" text,
59
  "Infraspecific_epithet_2_score" double precision,
60
  "Annotations" text,
61
  "Unmatched_terms" text,
62
  "Taxonomic_status" text,
63
  "Accepted_name" text,
64
  "Accepted_name_author" text,
65
  "Accepted_name_rank" text,
66
  "Accepted_name_url" text,
67
  "Accepted_name_species" text,
68
  "Accepted_name_family" text,
69
  "Selected" text,
70
  "Source" text,
71
  "Warnings" text,
72
  "Accepted_name_lsid" text,
73
  "Accepted_scientific_name" text,
74
  "Max_score" double precision,
75
  "Is_homonym" boolean,
76
  "Is_plant" boolean,
77
  CONSTRAINT tnrs_pkey PRIMARY KEY ("Time_submitted" , "Name_number" ),
78
  CONSTRAINT "tnrs_Name_submitted_key" UNIQUE ("Name_submitted" )
79
)
80
WITH (
81
  OIDS=FALSE
82
);
83

    
84
CREATE UNIQUE INDEX tnrs_score_ok
85
  ON tnrs
86
  USING btree
87
  ("Name_submitted" )
88
  WHERE score_ok("Max_score");
89

    
90
/* IMPORTANT: when changing this function, you must regenerate the derived cols:
91
UPDATE "TNRS".tnrs SET "Name_submitted" = "Name_submitted"
92
runtime: 16 min ("5052509 rows affected, 980692 ms execution time")
93
VACUUM ANALYZE "TNRS".tnrs -- to remove previous rows
94
runtime: 50 s ("48264 ms")
95
*/
96
CREATE OR REPLACE FUNCTION tnrs_populate_fields()
97
  RETURNS trigger AS
98
$BODY$
99
DECLARE
100
    "Specific_epithet_is_plant" boolean :=
101
        (CASE
102
        WHEN   new."Infraspecific_epithet_matched"   IS NOT NULL
103
            OR new."Infraspecific_epithet_2_matched" IS NOT NULL
104
            OR new."Specific_epithet_score" >= 0.9 -- fuzzy match
105
            THEN true
106
        ELSE NULL -- ambiguous
107
        END);
108
    never_homonym boolean = COALESCE(new."Author_score" >= 0.6, false); -- author disambiguates
109
    family_is_homonym boolean = NOT never_homonym AND "TNRS".family_is_homonym(new."Family_matched");
110
    genus_is_homonym  boolean = NOT never_homonym AND "TNRS".genus_is_homonym( new."Genus_matched" );
111
BEGIN
112
    new."Accepted_scientific_name" = NULLIF(concat_ws(' '
113
        , NULLIF(NULLIF(new."Accepted_name_family", 'Unknown'), new."Accepted_name")
114
        , new."Accepted_name"
115
        , new."Accepted_name_author"
116
    ), '');
117
    new."Max_score" = GREATEST(
118
          new."Overall_score"
119
        , new."Family_score"
120
        , new."Genus_score"
121
        , new."Specific_epithet_score"
122
    );
123
    new."Is_homonym" = COALESCE(genus_is_homonym, family_is_homonym);
124
    new."Is_plant" = (CASE
125
        WHEN new."Family_score" = 1 AND NOT family_is_homonym -- exact match
126
            THEN true
127
        ELSE -- consider genus
128
            (CASE
129
            WHEN new."Genus_score" =  1    -- exact match
130
                THEN
131
                (CASE
132
                WHEN NOT genus_is_homonym THEN true
133
                ELSE "Specific_epithet_is_plant"
134
                END)
135
            WHEN new."Genus_score" >= 0.85 -- fuzzy match
136
                THEN "Specific_epithet_is_plant"
137
            ELSE NULL -- ambiguous
138
            END)
139
        END);
140
    
141
    RETURN new;
142
END;
143
$BODY$
144
  LANGUAGE plpgsql VOLATILE
145
  COST 100;
146

    
147
CREATE TRIGGER tnrs_populate_fields
148
  BEFORE INSERT OR UPDATE
149
  ON tnrs
150
  FOR EACH ROW
151
  EXECUTE PROCEDURE tnrs_populate_fields();
152

    
153

    
154
CREATE OR REPLACE VIEW "MatchedTaxon" AS
155
SELECT
156
  "Time_submitted" AS "*Name_matched.Time_submitted"
157
, "Name_submitted" AS "concatenatedScientificName"
158
, "Name_matched" AS "matchedTaxonName"
159
, "Name_matched_rank" AS "matchedTaxonRank"
160
, "Name_score" AS "*Name_matched.Name_score"
161
, "Name_matched_author" AS "matchedScientificNameAuthorship"
162
, "Name_matched_url" AS "matchedScientificNameID"
163
, "Author_score" AS "*Name_matched.Author_score"
164
, "Family_score" AS "matchedFamilyConfidence_fraction"
165
, COALESCE("Name_matched_accepted_family", "Accepted_name_family") AS "matchedFamily"
166
, "Genus_matched" AS "matchedGenus"
167
, "Genus_score" AS "matchedGenusConfidence_fraction"
168
, "Specific_epithet_matched" AS "matchedSpecificEpithet"
169
, "Specific_epithet_score" AS "matchedSpeciesConfidence_fraction"
170
, "Infraspecific_epithet_matched" AS "matchedInfraspecificEpithet"
171
, "Infraspecific_epithet_score" AS "*Name_matched.Infraspecific_epithet_score"
172
, "Annotations" AS "identificationQualifier"
173
, "Unmatched_terms" AS "morphospeciesSuffix"
174
, "Taxonomic_status" AS "taxonomicStatus"
175
, "Accepted_name" AS "acceptedTaxonName"
176
, "Accepted_name_author" AS "acceptedScientificNameAuthorship"
177
, "Accepted_name_rank" AS "acceptedTaxonRank"
178
, "Accepted_name_url" AS "acceptedScientificNameID"
179
, "Accepted_name_species" AS "*Name_matched.Accepted_name_species"
180
, "Accepted_name_family" AS "acceptedFamily"
181
, "Selected" AS "*Name_matched.Selected"
182
, "Source" AS "*Name_matched.Source"
183
, "Warnings" AS "*Name_matched.Warnings"
184
, "Accepted_name_lsid" AS "*Name_matched.Accepted_name_lsid"
185
, "Accepted_scientific_name" AS "acceptedScientificName"
186
, "Max_score" AS "matchedTaxonConfidence_fraction"
187
FROM tnrs
188
;
189

    
190
CREATE OR REPLACE VIEW "ValidMatchedTaxon" AS
191
SELECT *
192
FROM "MatchedTaxon"
193
WHERE score_ok("matchedTaxonConfidence_fraction")
194
;
195

    
196
CREATE OR REPLACE VIEW "AcceptedTaxon" AS
197
SELECT
198
  "Time_submitted" AS "*Accepted_name.Time_submitted"
199
, "Name_submitted" AS "acceptedScientificName"
200
, "Genus_matched" AS "acceptedGenus"
201
, "Specific_epithet_matched" AS "acceptedSpecificEpithet"
202
, "Infraspecific_epithet_matched" AS "acceptedInfraspecificEpithet"
203
, "Taxonomic_status" AS "acceptedTaxonomicStatus"
204
, "Selected" AS "*Accepted_name.Selected"
205
, "Source" AS "*Accepted_name.Source"
206
, "Warnings" AS "*Accepted_name.Warnings"
207
, "Accepted_name_lsid" AS "*Accepted_name.Accepted_name_lsid"
208
FROM tnrs
209
;
210

    
211
CREATE OR REPLACE VIEW "ScrubbedTaxon" AS
212
SELECT *
213
FROM "ValidMatchedTaxon"
214
NATURAL LEFT JOIN "AcceptedTaxon"
215
;
(4-4/4)