Project

General

Profile

« Previous | Next » 

Revision 7823

inputs/.TNRS/schema.sql: Added MatchedTaxon, AcceptedTaxon, ScrubbedTaxon views, which rename the columns to VegCore names

View differences:

inputs/.TNRS/schema.sql
120 120
LEFT JOIN tnrs tnrs_accepted ON
121 121
tnrs_accepted."Name_submitted" = tnrs."Accepted_scientific_name"
122 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
;

Also available in: Unified diff