Revision 13498
Added by Aaron Marcuse-Kubitza over 10 years ago
| trunk/inputs/.TNRS/schema.sql | ||
|---|---|---|
| 224 | 224 |
-- |
| 225 | 225 |
|
| 226 | 226 |
CREATE VIEW "MatchedTaxon" AS |
| 227 |
SELECT tnrs.batch AS "*Name_matched.batch", |
|
| 228 |
tnrs."Name_submitted" AS "concatenatedScientificName", |
|
| 229 |
tnrs."Name_matched" AS "matchedTaxonName", |
|
| 230 |
tnrs."Name_matched_rank" AS "matchedTaxonRank", |
|
| 231 |
tnrs."Name_score" AS "*Name_matched.Name_score", |
|
| 232 |
tnrs."Name_matched_author" AS "matchedScientificNameAuthorship", |
|
| 233 |
tnrs."Name_matched_url" AS "matchedScientificNameID", |
|
| 234 |
tnrs."Author_score" AS "*Name_matched.Author_score", |
|
| 235 |
tnrs."Family_score" AS "matchedFamilyConfidence_fraction", |
|
| 236 |
COALESCE(tnrs."Name_matched_accepted_family", tnrs."Accepted_name_family") AS "matchedFamily", |
|
| 237 |
tnrs."Genus_matched" AS "matchedGenus", |
|
| 238 |
tnrs."Genus_score" AS "matchedGenusConfidence_fraction", |
|
| 239 |
tnrs."Specific_epithet_matched" AS "matchedSpecificEpithet", |
|
| 240 |
tnrs."Specific_epithet_score" AS "matchedSpeciesConfidence_fraction", |
|
| 241 |
tnrs."Infraspecific_epithet_matched" AS "matchedInfraspecificEpithet", |
|
| 242 |
tnrs."Infraspecific_epithet_score" AS "*Name_matched.Infraspecific_epithet_score", |
|
| 243 |
tnrs."Annotations" AS "identificationQualifier", |
|
| 244 |
tnrs."Unmatched_terms" AS "morphospeciesSuffix", |
|
| 245 |
tnrs."Taxonomic_status" AS "taxonomicStatus", |
|
| 246 |
tnrs."Accepted_name" AS accepted_taxon_name_no_author, |
|
| 247 |
tnrs."Accepted_name_author" AS accepted_author, |
|
| 248 |
tnrs."Accepted_name_rank" AS accepted_taxon_rank, |
|
| 249 |
tnrs."Accepted_name_url" AS "acceptedScientificNameID", |
|
| 250 |
tnrs."Accepted_name_species" AS accepted_species_binomial, |
|
| 251 |
tnrs."Accepted_name_family" AS accepted_family, |
|
| 252 |
tnrs."Selected" AS "*Name_matched.Selected", |
|
| 253 |
tnrs."Source" AS "*Name_matched.Source", |
|
| 254 |
tnrs."Warnings" AS "*Name_matched.Warnings", |
|
| 255 |
tnrs."Accepted_name_lsid" AS "*Name_matched.Accepted_name_lsid", |
|
| 256 |
tnrs.is_valid_match AS taxon_scrub__is_valid_match, |
|
| 257 |
tnrs.scrubbed_unique_taxon_name |
|
| 258 |
FROM tnrs; |
|
| 227 |
SELECT s."*Name_matched.batch", |
|
| 228 |
s."concatenatedScientificName", |
|
| 229 |
s."matchedTaxonName", |
|
| 230 |
s."matchedTaxonRank", |
|
| 231 |
s."*Name_matched.Name_score", |
|
| 232 |
s."matchedScientificNameAuthorship", |
|
| 233 |
s."matchedScientificNameID", |
|
| 234 |
s."*Name_matched.Author_score", |
|
| 235 |
s."matchedFamilyConfidence_fraction", |
|
| 236 |
s."matchedFamily", |
|
| 237 |
s."matchedGenus", |
|
| 238 |
s."matchedGenusConfidence_fraction", |
|
| 239 |
s."matchedSpecificEpithet", |
|
| 240 |
s."matchedSpeciesConfidence_fraction", |
|
| 241 |
s."matchedInfraspecificEpithet", |
|
| 242 |
s."*Name_matched.Infraspecific_epithet_score", |
|
| 243 |
s."identificationQualifier", |
|
| 244 |
s."morphospeciesSuffix", |
|
| 245 |
s."taxonomicStatus", |
|
| 246 |
s.accepted_taxon_name_no_author, |
|
| 247 |
s.accepted_author, |
|
| 248 |
s.accepted_taxon_rank, |
|
| 249 |
s."acceptedScientificNameID", |
|
| 250 |
s.accepted_species_binomial, |
|
| 251 |
s.accepted_family, |
|
| 252 |
s."*Name_matched.Selected", |
|
| 253 |
s."*Name_matched.Source", |
|
| 254 |
s."*Name_matched.Warnings", |
|
| 255 |
s."*Name_matched.Accepted_name_lsid", |
|
| 256 |
s.taxon_scrub__is_valid_match, |
|
| 257 |
s.scrubbed_unique_taxon_name, |
|
| 258 |
CASE |
|
| 259 |
WHEN (s.accepted_taxon_rank = 'family'::text) THEN concat_ws(' '::text, s.accepted_family, s."morphospeciesSuffix")
|
|
| 260 |
WHEN (s.accepted_taxon_rank = 'genus'::text) THEN concat_ws(' '::text, s.accepted_taxon_name_no_author, s."morphospeciesSuffix")
|
|
| 261 |
ELSE s.accepted_species_binomial |
|
| 262 |
END AS accepted_morphospecies_binomial |
|
| 263 |
FROM ( SELECT tnrs.batch AS "*Name_matched.batch", |
|
| 264 |
tnrs."Name_submitted" AS "concatenatedScientificName", |
|
| 265 |
tnrs."Name_matched" AS "matchedTaxonName", |
|
| 266 |
tnrs."Name_matched_rank" AS "matchedTaxonRank", |
|
| 267 |
tnrs."Name_score" AS "*Name_matched.Name_score", |
|
| 268 |
tnrs."Name_matched_author" AS "matchedScientificNameAuthorship", |
|
| 269 |
tnrs."Name_matched_url" AS "matchedScientificNameID", |
|
| 270 |
tnrs."Author_score" AS "*Name_matched.Author_score", |
|
| 271 |
tnrs."Family_score" AS "matchedFamilyConfidence_fraction", |
|
| 272 |
COALESCE(tnrs."Name_matched_accepted_family", tnrs."Accepted_name_family") AS "matchedFamily", |
|
| 273 |
tnrs."Genus_matched" AS "matchedGenus", |
|
| 274 |
tnrs."Genus_score" AS "matchedGenusConfidence_fraction", |
|
| 275 |
tnrs."Specific_epithet_matched" AS "matchedSpecificEpithet", |
|
| 276 |
tnrs."Specific_epithet_score" AS "matchedSpeciesConfidence_fraction", |
|
| 277 |
tnrs."Infraspecific_epithet_matched" AS "matchedInfraspecificEpithet", |
|
| 278 |
tnrs."Infraspecific_epithet_score" AS "*Name_matched.Infraspecific_epithet_score", |
|
| 279 |
tnrs."Annotations" AS "identificationQualifier", |
|
| 280 |
tnrs."Unmatched_terms" AS "morphospeciesSuffix", |
|
| 281 |
tnrs."Taxonomic_status" AS "taxonomicStatus", |
|
| 282 |
tnrs."Accepted_name" AS accepted_taxon_name_no_author, |
|
| 283 |
tnrs."Accepted_name_author" AS accepted_author, |
|
| 284 |
tnrs."Accepted_name_rank" AS accepted_taxon_rank, |
|
| 285 |
tnrs."Accepted_name_url" AS "acceptedScientificNameID", |
|
| 286 |
tnrs."Accepted_name_species" AS accepted_species_binomial, |
|
| 287 |
tnrs."Accepted_name_family" AS accepted_family, |
|
| 288 |
tnrs."Selected" AS "*Name_matched.Selected", |
|
| 289 |
tnrs."Source" AS "*Name_matched.Source", |
|
| 290 |
tnrs."Warnings" AS "*Name_matched.Warnings", |
|
| 291 |
tnrs."Accepted_name_lsid" AS "*Name_matched.Accepted_name_lsid", |
|
| 292 |
tnrs.is_valid_match AS taxon_scrub__is_valid_match, |
|
| 293 |
tnrs.scrubbed_unique_taxon_name |
|
| 294 |
FROM tnrs) s; |
|
| 259 | 295 |
|
| 260 | 296 |
|
| 261 | 297 |
-- |
| 298 |
-- Name: VIEW "MatchedTaxon"; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 299 |
-- |
|
| 300 |
|
|
| 301 |
COMMENT ON VIEW "MatchedTaxon" IS ' |
|
| 302 |
to update, use `*, ... AS accepted_morphospecies_binomial` as the column list |
|
| 303 |
'; |
|
| 304 |
|
|
| 305 |
|
|
| 306 |
-- |
|
| 262 | 307 |
-- Name: ValidMatchedTaxon; Type: VIEW; Schema: TNRS; Owner: - |
| 263 | 308 |
-- |
| 264 | 309 |
|
| ... | ... | |
| 293 | 338 |
"MatchedTaxon"."*Name_matched.Warnings", |
| 294 | 339 |
"MatchedTaxon"."*Name_matched.Accepted_name_lsid", |
| 295 | 340 |
"MatchedTaxon".taxon_scrub__is_valid_match, |
| 296 |
"MatchedTaxon".scrubbed_unique_taxon_name |
|
| 341 |
"MatchedTaxon".scrubbed_unique_taxon_name, |
|
| 342 |
"MatchedTaxon".accepted_morphospecies_binomial |
|
| 297 | 343 |
FROM "MatchedTaxon" |
| 298 | 344 |
WHERE "MatchedTaxon".taxon_scrub__is_valid_match; |
| 299 | 345 |
|
| ... | ... | |
| 448 | 494 |
"ValidMatchedTaxon"."*Name_matched.Warnings", |
| 449 | 495 |
"ValidMatchedTaxon"."*Name_matched.Accepted_name_lsid", |
| 450 | 496 |
"ValidMatchedTaxon".taxon_scrub__is_valid_match, |
| 497 |
"ValidMatchedTaxon".accepted_morphospecies_binomial, |
|
| 451 | 498 |
"taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_rank, |
| 452 | 499 |
"taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_family, |
| 453 | 500 |
"taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_genus, |
| trunk/schemas/vegbien.my.sql | ||
|---|---|---|
| 13276 | 13276 |
|
| 13277 | 13277 |
|
| 13278 | 13278 |
-- |
| 13279 |
-- Name: VIEW `MatchedTaxon`; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 13280 |
-- |
|
| 13281 |
|
|
| 13282 |
|
|
| 13283 |
|
|
| 13284 |
|
|
| 13285 |
-- |
|
| 13279 | 13286 |
-- Name: ValidMatchedTaxon; Type: VIEW; Schema: TNRS; Owner: - |
| 13280 | 13287 |
-- |
| 13281 | 13288 |
|
| trunk/schemas/vegbien.sql | ||
|---|---|---|
| 17029 | 17029 |
-- |
| 17030 | 17030 |
|
| 17031 | 17031 |
CREATE VIEW "MatchedTaxon" AS |
| 17032 |
SELECT tnrs.batch AS "*Name_matched.batch", |
|
| 17033 |
tnrs."Name_submitted" AS "concatenatedScientificName", |
|
| 17034 |
tnrs."Name_matched" AS "matchedTaxonName", |
|
| 17035 |
tnrs."Name_matched_rank" AS "matchedTaxonRank", |
|
| 17036 |
tnrs."Name_score" AS "*Name_matched.Name_score", |
|
| 17037 |
tnrs."Name_matched_author" AS "matchedScientificNameAuthorship", |
|
| 17038 |
tnrs."Name_matched_url" AS "matchedScientificNameID", |
|
| 17039 |
tnrs."Author_score" AS "*Name_matched.Author_score", |
|
| 17040 |
tnrs."Family_score" AS "matchedFamilyConfidence_fraction", |
|
| 17041 |
COALESCE(tnrs."Name_matched_accepted_family", tnrs."Accepted_name_family") AS "matchedFamily", |
|
| 17042 |
tnrs."Genus_matched" AS "matchedGenus", |
|
| 17043 |
tnrs."Genus_score" AS "matchedGenusConfidence_fraction", |
|
| 17044 |
tnrs."Specific_epithet_matched" AS "matchedSpecificEpithet", |
|
| 17045 |
tnrs."Specific_epithet_score" AS "matchedSpeciesConfidence_fraction", |
|
| 17046 |
tnrs."Infraspecific_epithet_matched" AS "matchedInfraspecificEpithet", |
|
| 17047 |
tnrs."Infraspecific_epithet_score" AS "*Name_matched.Infraspecific_epithet_score", |
|
| 17048 |
tnrs."Annotations" AS "identificationQualifier", |
|
| 17049 |
tnrs."Unmatched_terms" AS "morphospeciesSuffix", |
|
| 17050 |
tnrs."Taxonomic_status" AS "taxonomicStatus", |
|
| 17051 |
tnrs."Accepted_name" AS accepted_taxon_name_no_author, |
|
| 17052 |
tnrs."Accepted_name_author" AS accepted_author, |
|
| 17053 |
tnrs."Accepted_name_rank" AS accepted_taxon_rank, |
|
| 17054 |
tnrs."Accepted_name_url" AS "acceptedScientificNameID", |
|
| 17055 |
tnrs."Accepted_name_species" AS accepted_species_binomial, |
|
| 17056 |
tnrs."Accepted_name_family" AS accepted_family, |
|
| 17057 |
tnrs."Selected" AS "*Name_matched.Selected", |
|
| 17058 |
tnrs."Source" AS "*Name_matched.Source", |
|
| 17059 |
tnrs."Warnings" AS "*Name_matched.Warnings", |
|
| 17060 |
tnrs."Accepted_name_lsid" AS "*Name_matched.Accepted_name_lsid", |
|
| 17061 |
tnrs.is_valid_match AS taxon_scrub__is_valid_match, |
|
| 17062 |
tnrs.scrubbed_unique_taxon_name |
|
| 17063 |
FROM tnrs; |
|
| 17032 |
SELECT s."*Name_matched.batch", |
|
| 17033 |
s."concatenatedScientificName", |
|
| 17034 |
s."matchedTaxonName", |
|
| 17035 |
s."matchedTaxonRank", |
|
| 17036 |
s."*Name_matched.Name_score", |
|
| 17037 |
s."matchedScientificNameAuthorship", |
|
| 17038 |
s."matchedScientificNameID", |
|
| 17039 |
s."*Name_matched.Author_score", |
|
| 17040 |
s."matchedFamilyConfidence_fraction", |
|
| 17041 |
s."matchedFamily", |
|
| 17042 |
s."matchedGenus", |
|
| 17043 |
s."matchedGenusConfidence_fraction", |
|
| 17044 |
s."matchedSpecificEpithet", |
|
| 17045 |
s."matchedSpeciesConfidence_fraction", |
|
| 17046 |
s."matchedInfraspecificEpithet", |
|
| 17047 |
s."*Name_matched.Infraspecific_epithet_score", |
|
| 17048 |
s."identificationQualifier", |
|
| 17049 |
s."morphospeciesSuffix", |
|
| 17050 |
s."taxonomicStatus", |
|
| 17051 |
s.accepted_taxon_name_no_author, |
|
| 17052 |
s.accepted_author, |
|
| 17053 |
s.accepted_taxon_rank, |
|
| 17054 |
s."acceptedScientificNameID", |
|
| 17055 |
s.accepted_species_binomial, |
|
| 17056 |
s.accepted_family, |
|
| 17057 |
s."*Name_matched.Selected", |
|
| 17058 |
s."*Name_matched.Source", |
|
| 17059 |
s."*Name_matched.Warnings", |
|
| 17060 |
s."*Name_matched.Accepted_name_lsid", |
|
| 17061 |
s.taxon_scrub__is_valid_match, |
|
| 17062 |
s.scrubbed_unique_taxon_name, |
|
| 17063 |
CASE |
|
| 17064 |
WHEN (s.accepted_taxon_rank = 'family'::text) THEN concat_ws(' '::text, s.accepted_family, s."morphospeciesSuffix")
|
|
| 17065 |
WHEN (s.accepted_taxon_rank = 'genus'::text) THEN concat_ws(' '::text, s.accepted_taxon_name_no_author, s."morphospeciesSuffix")
|
|
| 17066 |
ELSE s.accepted_species_binomial |
|
| 17067 |
END AS accepted_morphospecies_binomial |
|
| 17068 |
FROM ( SELECT tnrs.batch AS "*Name_matched.batch", |
|
| 17069 |
tnrs."Name_submitted" AS "concatenatedScientificName", |
|
| 17070 |
tnrs."Name_matched" AS "matchedTaxonName", |
|
| 17071 |
tnrs."Name_matched_rank" AS "matchedTaxonRank", |
|
| 17072 |
tnrs."Name_score" AS "*Name_matched.Name_score", |
|
| 17073 |
tnrs."Name_matched_author" AS "matchedScientificNameAuthorship", |
|
| 17074 |
tnrs."Name_matched_url" AS "matchedScientificNameID", |
|
| 17075 |
tnrs."Author_score" AS "*Name_matched.Author_score", |
|
| 17076 |
tnrs."Family_score" AS "matchedFamilyConfidence_fraction", |
|
| 17077 |
COALESCE(tnrs."Name_matched_accepted_family", tnrs."Accepted_name_family") AS "matchedFamily", |
|
| 17078 |
tnrs."Genus_matched" AS "matchedGenus", |
|
| 17079 |
tnrs."Genus_score" AS "matchedGenusConfidence_fraction", |
|
| 17080 |
tnrs."Specific_epithet_matched" AS "matchedSpecificEpithet", |
|
| 17081 |
tnrs."Specific_epithet_score" AS "matchedSpeciesConfidence_fraction", |
|
| 17082 |
tnrs."Infraspecific_epithet_matched" AS "matchedInfraspecificEpithet", |
|
| 17083 |
tnrs."Infraspecific_epithet_score" AS "*Name_matched.Infraspecific_epithet_score", |
|
| 17084 |
tnrs."Annotations" AS "identificationQualifier", |
|
| 17085 |
tnrs."Unmatched_terms" AS "morphospeciesSuffix", |
|
| 17086 |
tnrs."Taxonomic_status" AS "taxonomicStatus", |
|
| 17087 |
tnrs."Accepted_name" AS accepted_taxon_name_no_author, |
|
| 17088 |
tnrs."Accepted_name_author" AS accepted_author, |
|
| 17089 |
tnrs."Accepted_name_rank" AS accepted_taxon_rank, |
|
| 17090 |
tnrs."Accepted_name_url" AS "acceptedScientificNameID", |
|
| 17091 |
tnrs."Accepted_name_species" AS accepted_species_binomial, |
|
| 17092 |
tnrs."Accepted_name_family" AS accepted_family, |
|
| 17093 |
tnrs."Selected" AS "*Name_matched.Selected", |
|
| 17094 |
tnrs."Source" AS "*Name_matched.Source", |
|
| 17095 |
tnrs."Warnings" AS "*Name_matched.Warnings", |
|
| 17096 |
tnrs."Accepted_name_lsid" AS "*Name_matched.Accepted_name_lsid", |
|
| 17097 |
tnrs.is_valid_match AS taxon_scrub__is_valid_match, |
|
| 17098 |
tnrs.scrubbed_unique_taxon_name |
|
| 17099 |
FROM tnrs) s; |
|
| 17064 | 17100 |
|
| 17065 | 17101 |
|
| 17066 | 17102 |
-- |
| 17103 |
-- Name: VIEW "MatchedTaxon"; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 17104 |
-- |
|
| 17105 |
|
|
| 17106 |
COMMENT ON VIEW "MatchedTaxon" IS ' |
|
| 17107 |
to update, use `*, ... AS accepted_morphospecies_binomial` as the column list |
|
| 17108 |
'; |
|
| 17109 |
|
|
| 17110 |
|
|
| 17111 |
-- |
|
| 17067 | 17112 |
-- Name: ValidMatchedTaxon; Type: VIEW; Schema: TNRS; Owner: - |
| 17068 | 17113 |
-- |
| 17069 | 17114 |
|
| ... | ... | |
| 17098 | 17143 |
"MatchedTaxon"."*Name_matched.Warnings", |
| 17099 | 17144 |
"MatchedTaxon"."*Name_matched.Accepted_name_lsid", |
| 17100 | 17145 |
"MatchedTaxon".taxon_scrub__is_valid_match, |
| 17101 |
"MatchedTaxon".scrubbed_unique_taxon_name |
|
| 17146 |
"MatchedTaxon".scrubbed_unique_taxon_name, |
|
| 17147 |
"MatchedTaxon".accepted_morphospecies_binomial |
|
| 17102 | 17148 |
FROM "MatchedTaxon" |
| 17103 | 17149 |
WHERE "MatchedTaxon".taxon_scrub__is_valid_match; |
| 17104 | 17150 |
|
| ... | ... | |
| 17253 | 17299 |
"ValidMatchedTaxon"."*Name_matched.Warnings", |
| 17254 | 17300 |
"ValidMatchedTaxon"."*Name_matched.Accepted_name_lsid", |
| 17255 | 17301 |
"ValidMatchedTaxon".taxon_scrub__is_valid_match, |
| 17302 |
"ValidMatchedTaxon".accepted_morphospecies_binomial, |
|
| 17256 | 17303 |
"taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_rank, |
| 17257 | 17304 |
"taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_family, |
| 17258 | 17305 |
"taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_genus, |
Also available in: Unified diff
inputs/.TNRS/schema.sql: MatchedTaxon, etc.: added accepted_morphospecies_binomial derived field