Project

General

Profile

1
CREATE TABLE tnrs
2
(
3
  "Time_submitted" timestamp with time zone,
4
  "Name_number" text,
5
  "Name_submitted" text NOT NULL,
6
  "Overall_score" text,
7
  "Name_matched" text,
8
  "Name_matched_rank" text,
9
  "Name_score" text,
10
  "Name_matched_author" text,
11
  "Name_matched_url" text,
12
  "Author_matched" text,
13
  "Author_score" text,
14
  "Family_matched" text,
15
  "Family_score" text,
16
  "Name_matched_accepted_family" text,
17
  "Genus_matched" text,
18
  "Genus_score" text,
19
  "Specific_epithet_matched" text,
20
  "Specific_epithet_score" text,
21
  "Infraspecific_rank" text,
22
  "Infraspecific_epithet_matched" text,
23
  "Infraspecific_epithet_score" text,
24
  "Infraspecific_rank_2" text,
25
  "Infraspecific_epithet_2_matched" text,
26
  "Infraspecific_epithet_2_score" text,
27
  "Annotations" text,
28
  "Unmatched_terms" text,
29
  "Taxonomic_status" text,
30
  "Accepted_name" text,
31
  "Accepted_name_author" text,
32
  "Accepted_name_rank" text,
33
  "Accepted_name_url" text,
34
  "Accepted_name_species" text,
35
  "Accepted_name_family" text,
36
  "Selected" text,
37
  "Source" text,
38
  "Warnings" text,
39
  "Accepted_name_lsid" text,
40
  "Accepted_scientific_name" text,
41
  "Max_score" double precision,
42
  CONSTRAINT tnrs_pkey PRIMARY KEY ("Name_submitted" )
43
)
44
WITH (
45
  OIDS=FALSE
46
);
47

    
48
CREATE OR REPLACE FUNCTION tnrs_populate_derived_fields()
49
  RETURNS trigger AS
50
$BODY$
51
BEGIN
52
    new."Accepted_scientific_name" = NULLIF(array_to_string(ARRAY[
53
        NULLIF(NULLIF(new."Accepted_name_family", 'Unknown'), new."Accepted_name")
54
        , new."Accepted_name"
55
        , new."Accepted_name_author"
56
    ], ' '), '');
57
    new."Max_score" = GREATEST(
58
          new."Overall_score"::double precision
59
        , new."Family_score"::double precision
60
        , new."Genus_score"::double precision
61
        , new."Specific_epithet_score"::double precision
62
    );
63
    
64
    RETURN new;
65
END;
66
$BODY$
67
  LANGUAGE plpgsql VOLATILE
68
  COST 100;
69

    
70
CREATE TRIGGER tnrs_populate_derived_fields
71
  BEFORE INSERT OR UPDATE
72
  ON tnrs
73
  FOR EACH ROW
74
  EXECUTE PROCEDURE tnrs_populate_derived_fields();
75

    
76

    
77
CREATE OR REPLACE VIEW "tnrs+accepted" AS
78
SELECT
79
tnrs.*
80
, tnrs_accepted."Time_submitted"                  AS "Accepted_name.Time_submitted"
81
, tnrs_accepted."Name_number"                     AS "Accepted_name.Name_number"
82
, tnrs_accepted."Name_submitted"                  AS "Accepted_name.Name_submitted"
83
, tnrs_accepted."Overall_score"                   AS "Accepted_name.Overall_score"
84
, tnrs_accepted."Name_matched"                    AS "Accepted_name.Name_matched"
85
, tnrs_accepted."Name_matched_rank"               AS "Accepted_name.Name_matched_rank"
86
, tnrs_accepted."Name_score"                      AS "Accepted_name.Name_score"
87
, tnrs_accepted."Name_matched_author"             AS "Accepted_name.Name_matched_author"
88
, tnrs_accepted."Name_matched_url"                AS "Accepted_name.Name_matched_url"
89
, tnrs_accepted."Author_matched"                  AS "Accepted_name.Author_matched"
90
, tnrs_accepted."Author_score"                    AS "Accepted_name.Author_score"
91
, tnrs_accepted."Family_matched"                  AS "Accepted_name.Family_matched"
92
, tnrs_accepted."Family_score"                    AS "Accepted_name.Family_score"
93
, tnrs_accepted."Name_matched_accepted_family"    AS "Accepted_name.Name_matched_accepted_family"
94
, tnrs_accepted."Genus_matched"                   AS "Accepted_name.Genus_matched"
95
, tnrs_accepted."Genus_score"                     AS "Accepted_name.Genus_score"
96
, tnrs_accepted."Specific_epithet_matched"        AS "Accepted_name.Specific_epithet_matched"
97
, tnrs_accepted."Specific_epithet_score"          AS "Accepted_name.Specific_epithet_score"
98
, tnrs_accepted."Infraspecific_rank"              AS "Accepted_name.Infraspecific_rank"
99
, tnrs_accepted."Infraspecific_epithet_matched"   AS "Accepted_name.Infraspecific_epithet_matched"
100
, tnrs_accepted."Infraspecific_epithet_score"     AS "Accepted_name.Infraspecific_epithet_score"
101
, tnrs_accepted."Infraspecific_rank_2"            AS "Accepted_name.Infraspecific_rank_2"
102
, tnrs_accepted."Infraspecific_epithet_2_matched" AS "Accepted_name.Infraspecific_epithet_2_matched"
103
, tnrs_accepted."Infraspecific_epithet_2_score"   AS "Accepted_name.Infraspecific_epithet_2_score"
104
, tnrs_accepted."Annotations"                     AS "Accepted_name.Annotations"
105
, tnrs_accepted."Unmatched_terms"                 AS "Accepted_name.Unmatched_terms"
106
, tnrs_accepted."Taxonomic_status"                AS "Accepted_name.Taxonomic_status"
107
, tnrs_accepted."Accepted_name"                   AS "Accepted_name.Accepted_name"
108
, tnrs_accepted."Accepted_name_author"            AS "Accepted_name.Accepted_name_author"
109
, tnrs_accepted."Accepted_name_rank"              AS "Accepted_name.Accepted_name_rank"
110
, tnrs_accepted."Accepted_name_url"               AS "Accepted_name.Accepted_name_url"
111
, tnrs_accepted."Accepted_name_species"           AS "Accepted_name.Accepted_name_species"
112
, tnrs_accepted."Accepted_name_family"            AS "Accepted_name.Accepted_name_family"
113
, tnrs_accepted."Selected"                        AS "Accepted_name.Selected"
114
, tnrs_accepted."Source"                          AS "Accepted_name.Source"
115
, tnrs_accepted."Warnings"                        AS "Accepted_name.Warnings"
116
, tnrs_accepted."Accepted_name_lsid"              AS "Accepted_name.Accepted_name_lsid"
117
, tnrs_accepted."Accepted_scientific_name"        AS "Accepted_name.Accepted_scientific_name"
118
, tnrs_accepted."Max_score"                       AS "Accepted_name.Max_score"
119
FROM tnrs
120
LEFT JOIN tnrs tnrs_accepted ON
121
tnrs_accepted."Name_submitted" = tnrs."Accepted_scientific_name"
122
;
123

    
124

    
125
CREATE OR REPLACE VIEW "MatchedTaxon" AS
126
SELECT
127
  "Time_submitted" AS "*Name_matched.Time_submitted"
128
, "Name_matched" AS "concatenatedScientificName"
129
, "Name_matched_rank" AS "matchedTaxonRank"
130
, "Name_score" AS "*Name_matched.Name_score"
131
, "Name_matched_author" AS "matchedScientificNameAuthorship"
132
, "Name_matched_url" AS "matchedScientificNameID"
133
, "Author_score" AS "*Name_matched.Author_score"
134
, "Family_score" AS "matchedFamilyConfidence_fraction"
135
, "Name_matched_accepted_family" AS family
136
, "Genus_matched" AS genus
137
, "Genus_score" AS "matchedGenusConfidence_fraction"
138
, "Specific_epithet_matched" AS "matchedSpecificEpithet"
139
, "Specific_epithet_score" AS "matchedSpeciesConfidence_fraction"
140
, "Infraspecific_epithet_matched" AS "matchedInfraspecificEpithet"
141
, "Infraspecific_epithet_score" AS "*Name_matched.Infraspecific_epithet_score"
142
, "Annotations" AS "identificationQualifier"
143
, "Unmatched_terms" AS "morphospeciesSuffix"
144
, "Taxonomic_status" AS "taxonomicStatus"
145
, "Accepted_name" AS "acceptedTaxonName"
146
, "Accepted_name_author" AS "acceptedScientificNameAuthorship"
147
, "Accepted_name_rank" AS "acceptedTaxonRank"
148
, "Accepted_name_url" AS "acceptedScientificNameID"
149
, "Accepted_name_species" AS "*Name_matched.Accepted_name_species"
150
, "Accepted_name_family" AS "acceptedFamily"
151
, "Selected" AS "*Name_matched.Selected"
152
, "Source" AS source
153
, "Warnings" AS "*Name_matched.Warnings"
154
, "Accepted_name_lsid" AS "*Name_matched.Accepted_name_lsid"
155
, "Accepted_scientific_name" AS "acceptedScientificName"
156
, "Max_score" AS "matchedTaxonConfidence_fraction"
157
FROM tnrs;
158

    
159
CREATE OR REPLACE VIEW "AcceptedTaxon" AS
160
SELECT
161
  "Time_submitted" AS "*Accepted_name.Time_submitted"
162
, "Name_submitted" AS "acceptedScientificName"
163
, "Genus_matched" AS "acceptedGenus"
164
, "Specific_epithet_matched" AS "acceptedSpecificEpithet"
165
, "Infraspecific_epithet_matched" AS "acceptedInfraspecificEpithet"
166
, "Annotations" AS "*Accepted_name.Annotations"
167
, "Taxonomic_status" AS "acceptedTaxonomicStatus"
168
, "Selected" AS "*Accepted_name.Selected"
169
, "Source" AS "*Accepted_name.Source"
170
, "Warnings" AS "*Accepted_name.Warnings"
171
, "Accepted_name_lsid" AS "*Accepted_name.Accepted_name_lsid"
172
FROM tnrs;
173

    
174
CREATE OR REPLACE VIEW "ScrubbedTaxon" AS
175
SELECT *
176
FROM "MatchedTaxon"
177
NATURAL LEFT JOIN "AcceptedTaxon"
178
;
(3-3/3)