Revision 13439
Added by Aaron Marcuse-Kubitza over 10 years ago
| vegbien.sql | ||
|---|---|---|
| 17048 | 17048 |
tnrs."Annotations" AS "identificationQualifier", |
| 17049 | 17049 |
tnrs."Unmatched_terms" AS "morphospeciesSuffix", |
| 17050 | 17050 |
tnrs."Taxonomic_status" AS "taxonomicStatus", |
| 17051 |
tnrs."Accepted_name" AS "acceptedTaxonName",
|
|
| 17052 |
tnrs."Accepted_name_author" AS "acceptedScientificNameAuthorship",
|
|
| 17053 |
tnrs."Accepted_name_rank" AS "acceptedTaxonRank",
|
|
| 17051 |
tnrs."Accepted_name" AS scrubbed_taxon_name_no_author,
|
|
| 17052 |
tnrs."Accepted_name_author" AS scrubbed_author,
|
|
| 17053 |
tnrs."Accepted_name_rank" AS scrubbed_taxon_rank,
|
|
| 17054 | 17054 |
tnrs."Accepted_name_url" AS "acceptedScientificNameID", |
| 17055 | 17055 |
tnrs."Accepted_name_species" AS "*Name_matched.Accepted_name_species", |
| 17056 |
tnrs."Accepted_name_family" AS "acceptedFamily",
|
|
| 17056 |
tnrs."Accepted_name_family" AS scrubbed_family,
|
|
| 17057 | 17057 |
tnrs."Selected" AS "*Name_matched.Selected", |
| 17058 | 17058 |
tnrs."Source" AS "*Name_matched.Source", |
| 17059 | 17059 |
tnrs."Warnings" AS "*Name_matched.Warnings", |
| ... | ... | |
| 17087 | 17087 |
"MatchedTaxon"."identificationQualifier", |
| 17088 | 17088 |
"MatchedTaxon"."morphospeciesSuffix", |
| 17089 | 17089 |
"MatchedTaxon"."taxonomicStatus", |
| 17090 |
"MatchedTaxon"."acceptedTaxonName",
|
|
| 17091 |
"MatchedTaxon"."acceptedScientificNameAuthorship",
|
|
| 17092 |
"MatchedTaxon"."acceptedTaxonRank",
|
|
| 17090 |
"MatchedTaxon".scrubbed_taxon_name_no_author,
|
|
| 17091 |
"MatchedTaxon".scrubbed_author,
|
|
| 17092 |
"MatchedTaxon".scrubbed_taxon_rank,
|
|
| 17093 | 17093 |
"MatchedTaxon"."acceptedScientificNameID", |
| 17094 | 17094 |
"MatchedTaxon"."*Name_matched.Accepted_name_species", |
| 17095 |
"MatchedTaxon"."acceptedFamily",
|
|
| 17095 |
"MatchedTaxon".scrubbed_family,
|
|
| 17096 | 17096 |
"MatchedTaxon"."*Name_matched.Selected", |
| 17097 | 17097 |
"MatchedTaxon"."*Name_matched.Source", |
| 17098 | 17098 |
"MatchedTaxon"."*Name_matched.Warnings", |
| ... | ... | |
| 17197 | 17197 |
|
| 17198 | 17198 |
CREATE VIEW "taxon_scrub.scrubbed_unique_taxon_name.*" AS |
| 17199 | 17199 |
SELECT tnrs."Name_submitted" AS scrubbed_unique_taxon_name, |
| 17200 |
tnrs."Name_matched_rank" AS scrubbed_taxon_rank, |
|
| 17201 |
COALESCE(tnrs."Name_matched_accepted_family", tnrs."Family_matched") AS scrubbed_family, |
|
| 17200 |
tnrs."Name_matched_rank" AS scrubbed_parsed_taxon_rank,
|
|
| 17201 |
COALESCE(tnrs."Name_matched_accepted_family", tnrs."Family_matched") AS scrubbed_parsed_family,
|
|
| 17202 | 17202 |
tnrs."Genus_matched" AS scrubbed_genus, |
| 17203 | 17203 |
tnrs."Specific_epithet_matched" AS scrubbed_specific_epithet, |
| 17204 | 17204 |
tnrs."Infraspecific_rank" AS scrubbed_infraspecific_rank, |
| 17205 | 17205 |
tnrs."Infraspecific_epithet_matched" AS scrubbed_infraspecific_epithet, |
| 17206 |
tnrs."Name_matched_author" AS scrubbed_author, |
|
| 17207 |
tnrs."Name_matched" AS scrubbed_taxon_name_no_author |
|
| 17206 |
tnrs."Name_matched_author" AS scrubbed_parsed_author,
|
|
| 17207 |
tnrs."Name_matched" AS scrubbed_parsed_taxon_name_no_author
|
|
| 17208 | 17208 |
FROM tnrs; |
| 17209 | 17209 |
|
| 17210 | 17210 |
|
| ... | ... | |
| 17240 | 17240 |
"ValidMatchedTaxon"."identificationQualifier", |
| 17241 | 17241 |
"ValidMatchedTaxon"."morphospeciesSuffix", |
| 17242 | 17242 |
"ValidMatchedTaxon"."taxonomicStatus", |
| 17243 |
"ValidMatchedTaxon"."acceptedTaxonName",
|
|
| 17244 |
"ValidMatchedTaxon"."acceptedScientificNameAuthorship",
|
|
| 17245 |
"ValidMatchedTaxon"."acceptedTaxonRank",
|
|
| 17243 |
"ValidMatchedTaxon".scrubbed_taxon_name_no_author,
|
|
| 17244 |
"ValidMatchedTaxon".scrubbed_author,
|
|
| 17245 |
"ValidMatchedTaxon".scrubbed_taxon_rank,
|
|
| 17246 | 17246 |
"ValidMatchedTaxon"."acceptedScientificNameID", |
| 17247 | 17247 |
"ValidMatchedTaxon"."*Name_matched.Accepted_name_species", |
| 17248 |
"ValidMatchedTaxon"."acceptedFamily",
|
|
| 17248 |
"ValidMatchedTaxon".scrubbed_family,
|
|
| 17249 | 17249 |
"ValidMatchedTaxon"."*Name_matched.Selected", |
| 17250 | 17250 |
"ValidMatchedTaxon"."*Name_matched.Source", |
| 17251 | 17251 |
"ValidMatchedTaxon"."*Name_matched.Warnings", |
| 17252 | 17252 |
"ValidMatchedTaxon"."*Name_matched.Accepted_name_lsid", |
| 17253 | 17253 |
"ValidMatchedTaxon".taxon_scrub__is_valid_match, |
| 17254 |
"taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_rank, |
|
| 17255 |
"taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_family, |
|
| 17254 |
"taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_parsed_taxon_rank,
|
|
| 17255 |
"taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_parsed_family,
|
|
| 17256 | 17256 |
"taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_genus, |
| 17257 | 17257 |
"taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_specific_epithet, |
| 17258 | 17258 |
"taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_infraspecific_rank, |
| 17259 | 17259 |
"taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_infraspecific_epithet, |
| 17260 |
"taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_author, |
|
| 17261 |
"taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_name_no_author |
|
| 17260 |
"taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_parsed_author,
|
|
| 17261 |
"taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_parsed_taxon_name_no_author
|
|
| 17262 | 17262 |
FROM ("ValidMatchedTaxon"
|
| 17263 | 17263 |
LEFT JOIN "taxon_scrub.scrubbed_unique_taxon_name.*" USING (scrubbed_unique_taxon_name)); |
| 17264 | 17264 |
|
Also available in: Unified diff
fix: inputs/.TNRS/schema.sql: taxon_scrub, etc.: scrubbed_*: use columns from MatchedTaxon whenever possible, to as much as possible avoid the need to join to taxon_scrub.scrubbed_unique_taxon_name.*