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
10 5183 aaronmk
CREATE TABLE tnrs
11 5110 aaronmk
(
12 5737 aaronmk
  "Time_submitted" timestamp with time zone,
13 5110 aaronmk
  "Name_number" text,
14
  "Name_submitted" text NOT NULL,
15
  "Overall_score" text,
16
  "Name_matched" text,
17
  "Name_matched_rank" text,
18
  "Name_score" text,
19
  "Name_matched_author" text,
20
  "Name_matched_url" text,
21
  "Author_matched" text,
22
  "Author_score" text,
23
  "Family_matched" text,
24
  "Family_score" text,
25
  "Name_matched_accepted_family" text,
26
  "Genus_matched" text,
27
  "Genus_score" text,
28
  "Specific_epithet_matched" text,
29
  "Specific_epithet_score" text,
30
  "Infraspecific_rank" text,
31
  "Infraspecific_epithet_matched" text,
32
  "Infraspecific_epithet_score" text,
33
  "Infraspecific_rank_2" text,
34
  "Infraspecific_epithet_2_matched" text,
35
  "Infraspecific_epithet_2_score" text,
36
  "Annotations" text,
37
  "Unmatched_terms" text,
38
  "Taxonomic_status" text,
39
  "Accepted_name" text,
40
  "Accepted_name_author" text,
41
  "Accepted_name_rank" text,
42
  "Accepted_name_url" text,
43
  "Accepted_name_species" text,
44
  "Accepted_name_family" text,
45
  "Selected" text,
46
  "Source" text,
47
  "Warnings" text,
48
  "Accepted_name_lsid" text,
49 7133 aaronmk
  "Accepted_scientific_name" text,
50 7293 aaronmk
  "Max_score" double precision,
51 5110 aaronmk
  CONSTRAINT tnrs_pkey PRIMARY KEY ("Name_submitted" )
52
)
53
WITH (
54
  OIDS=FALSE
55
);
56 7132 aaronmk
57 7844 aaronmk
CREATE UNIQUE INDEX tnrs_score_ok
58
  ON tnrs
59
  USING btree
60
  ("Name_submitted" )
61
  WHERE score_ok("Max_score");
62
63 7292 aaronmk
CREATE OR REPLACE FUNCTION tnrs_populate_derived_fields()
64 7134 aaronmk
  RETURNS trigger AS
65
$BODY$
66
BEGIN
67 7848 aaronmk
    new."Accepted_scientific_name" = NULLIF(concat_ws(' '
68 7827 aaronmk
        , NULLIF(NULLIF(new."Accepted_name_family", 'Unknown'), new."Accepted_name")
69 7134 aaronmk
        , new."Accepted_name"
70
        , new."Accepted_name_author"
71 7848 aaronmk
    ), '');
72 7293 aaronmk
    new."Max_score" = GREATEST(
73
          new."Overall_score"::double precision
74
        , new."Family_score"::double precision
75
        , new."Genus_score"::double precision
76
        , new."Specific_epithet_score"::double precision
77
    );
78 7134 aaronmk
79
    RETURN new;
80
END;
81
$BODY$
82
  LANGUAGE plpgsql VOLATILE
83
  COST 100;
84
85 7292 aaronmk
CREATE TRIGGER tnrs_populate_derived_fields
86 7134 aaronmk
  BEFORE INSERT OR UPDATE
87
  ON tnrs
88
  FOR EACH ROW
89 7292 aaronmk
  EXECUTE PROCEDURE tnrs_populate_derived_fields();
90 7251 aaronmk
91
92 7823 aaronmk
CREATE OR REPLACE VIEW "MatchedTaxon" AS
93
SELECT
94
  "Time_submitted" AS "*Name_matched.Time_submitted"
95 7830 aaronmk
, "Name_submitted" AS "concatenatedScientificName"
96 7829 aaronmk
, "Name_matched" AS "matchedTaxonName"
97 7823 aaronmk
, "Name_matched_rank" AS "matchedTaxonRank"
98
, "Name_score" AS "*Name_matched.Name_score"
99
, "Name_matched_author" AS "matchedScientificNameAuthorship"
100
, "Name_matched_url" AS "matchedScientificNameID"
101
, "Author_score" AS "*Name_matched.Author_score"
102
, "Family_score" AS "matchedFamilyConfidence_fraction"
103 7831 aaronmk
, "Name_matched_accepted_family" AS "matchedFamily"
104
, "Genus_matched" AS "matchedGenus"
105 7823 aaronmk
, "Genus_score" AS "matchedGenusConfidence_fraction"
106
, "Specific_epithet_matched" AS "matchedSpecificEpithet"
107
, "Specific_epithet_score" AS "matchedSpeciesConfidence_fraction"
108
, "Infraspecific_epithet_matched" AS "matchedInfraspecificEpithet"
109
, "Infraspecific_epithet_score" AS "*Name_matched.Infraspecific_epithet_score"
110
, "Annotations" AS "identificationQualifier"
111
, "Unmatched_terms" AS "morphospeciesSuffix"
112 7833 aaronmk
, "Taxonomic_status" AS "taxonomicStatus"
113 7823 aaronmk
, "Accepted_name" AS "acceptedTaxonName"
114
, "Accepted_name_author" AS "acceptedScientificNameAuthorship"
115
, "Accepted_name_rank" AS "acceptedTaxonRank"
116
, "Accepted_name_url" AS "acceptedScientificNameID"
117
, "Accepted_name_species" AS "*Name_matched.Accepted_name_species"
118
, "Accepted_name_family" AS "acceptedFamily"
119
, "Selected" AS "*Name_matched.Selected"
120 7828 aaronmk
, "Source" AS "*Name_matched.Source"
121 7823 aaronmk
, "Warnings" AS "*Name_matched.Warnings"
122
, "Accepted_name_lsid" AS "*Name_matched.Accepted_name_lsid"
123
, "Accepted_scientific_name" AS "acceptedScientificName"
124
, "Max_score" AS "matchedTaxonConfidence_fraction"
125
FROM tnrs;
126
127
CREATE OR REPLACE VIEW "AcceptedTaxon" AS
128
SELECT
129
  "Time_submitted" AS "*Accepted_name.Time_submitted"
130
, "Name_submitted" AS "acceptedScientificName"
131
, "Genus_matched" AS "acceptedGenus"
132
, "Specific_epithet_matched" AS "acceptedSpecificEpithet"
133
, "Infraspecific_epithet_matched" AS "acceptedInfraspecificEpithet"
134
, "Annotations" AS "*Accepted_name.Annotations"
135
, "Taxonomic_status" AS "acceptedTaxonomicStatus"
136
, "Selected" AS "*Accepted_name.Selected"
137
, "Source" AS "*Accepted_name.Source"
138
, "Warnings" AS "*Accepted_name.Warnings"
139
, "Accepted_name_lsid" AS "*Accepted_name.Accepted_name_lsid"
140
FROM tnrs;
141
142
CREATE OR REPLACE VIEW "ScrubbedTaxon" AS
143
SELECT *
144
FROM "MatchedTaxon"
145
NATURAL LEFT JOIN "AcceptedTaxon"
146 7845 aaronmk
WHERE score_ok("matchedTaxonConfidence_fraction");
147 7823 aaronmk
;