Revision 7151
Added by Aaron Marcuse-Kubitza almost 12 years ago
| vegbien.sql | ||
|---|---|---|
| 4517 | 4517 |
-- |
| 4518 | 4518 |
|
| 4519 | 4519 |
CREATE VIEW unscrubbed_taxondetermination_view AS |
| 4520 |
SELECT taxondetermination.taxonoccurrence_id, "tnrs+accepted"."Time_submitted", "tnrs+accepted"."Name_number", "tnrs+accepted"."Name_submitted", "tnrs+accepted"."Overall_score", "tnrs+accepted"."Name_matched", "tnrs+accepted"."Name_matched_rank", "tnrs+accepted"."Name_score", "tnrs+accepted"."Name_matched_author", "tnrs+accepted"."Name_matched_url", "tnrs+accepted"."Author_matched", "tnrs+accepted"."Author_score", "tnrs+accepted"."Family_matched", "tnrs+accepted"."Family_score", "tnrs+accepted"."Name_matched_accepted_family", "tnrs+accepted"."Genus_matched", "tnrs+accepted"."Genus_score", "tnrs+accepted"."Specific_epithet_matched", "tnrs+accepted"."Specific_epithet_score", "tnrs+accepted"."Infraspecific_rank", "tnrs+accepted"."Infraspecific_epithet_matched", "tnrs+accepted"."Infraspecific_epithet_score", "tnrs+accepted"."Infraspecific_rank_2", "tnrs+accepted"."Infraspecific_epithet_2_matched", "tnrs+accepted"."Infraspecific_epithet_2_score", "tnrs+accepted"."Annotations", "tnrs+accepted"."Unmatched_terms", "tnrs+accepted"."Taxonomic_status", "tnrs+accepted"."Accepted_name", "tnrs+accepted"."Accepted_name_author", "tnrs+accepted"."Accepted_name_rank", "tnrs+accepted"."Accepted_name_url", "tnrs+accepted"."Accepted_name_species", "tnrs+accepted"."Accepted_name_family", "tnrs+accepted"."Selected", "tnrs+accepted"."Source", "tnrs+accepted"."Warnings", "tnrs+accepted"."Accepted_name_lsid", "tnrs+accepted"."Accepted_scientific_name", "tnrs+accepted"."Accepted_name.Time_submitted", "tnrs+accepted"."Accepted_name.Name_number", "tnrs+accepted"."Accepted_name.Name_submitted", "tnrs+accepted"."Accepted_name.Overall_score", "tnrs+accepted"."Accepted_name.Name_matched", "tnrs+accepted"."Accepted_name.Name_matched_rank", "tnrs+accepted"."Accepted_name.Name_score", "tnrs+accepted"."Accepted_name.Name_matched_author", "tnrs+accepted"."Accepted_name.Name_matched_url", "tnrs+accepted"."Accepted_name.Author_matched", "tnrs+accepted"."Accepted_name.Author_score", "tnrs+accepted"."Accepted_name.Family_matched", "tnrs+accepted"."Accepted_name.Family_score", "tnrs+accepted"."Accepted_name.Name_matched_accepted_family", "tnrs+accepted"."Accepted_name.Genus_matched", "tnrs+accepted"."Accepted_name.Genus_score", "tnrs+accepted"."Accepted_name.Specific_epithet_matched", "tnrs+accepted"."Accepted_name.Specific_epithet_score", "tnrs+accepted"."Accepted_name.Infraspecific_rank", "tnrs+accepted"."Accepted_name.Infraspecific_epithet_matched", "tnrs+accepted"."Accepted_name.Infraspecific_epithet_score", "tnrs+accepted"."Accepted_name.Infraspecific_rank_2", "tnrs+accepted"."Accepted_name.Infraspecific_epithet_2_matched", "tnrs+accepted"."Accepted_name.Infraspecific_epithet_2_score", "tnrs+accepted"."Accepted_name.Annotations", "tnrs+accepted"."Accepted_name.Unmatched_terms", "tnrs+accepted"."Accepted_name.Taxonomic_status", "tnrs+accepted"."Accepted_name.Accepted_name", "tnrs+accepted"."Accepted_name.Accepted_name_author", "tnrs+accepted"."Accepted_name.Accepted_name_rank", "tnrs+accepted"."Accepted_name.Accepted_name_url", "tnrs+accepted"."Accepted_name.Accepted_name_species", "tnrs+accepted"."Accepted_name.Accepted_name_family", "tnrs+accepted"."Accepted_name.Selected", "tnrs+accepted"."Accepted_name.Source", "tnrs+accepted"."Accepted_name.Warnings", "tnrs+accepted"."Accepted_name.Accepted_name_lsid", "tnrs+accepted"."Accepted_name.Accepted_scientific_name" FROM (((taxondetermination JOIN taxonverbatim USING (taxonverbatim_id)) JOIN taxonlabel USING (taxonlabel_id)) JOIN "TNRS"."tnrs+accepted" ON (("tnrs+accepted"."Name_submitted" = taxonlabel.taxonomicname))) WHERE (taxondetermination.iscurrent AND (taxondetermination.source_id <> (SELECT source.source_id FROM source WHERE (source.shortname = 'TNRS'::text)))) ORDER BY taxondetermination.taxonoccurrence_id;
|
|
| 4520 |
SELECT taxondetermination.taxonoccurrence_id, tnrs_canon."Time_submitted", tnrs_canon."Name_number", tnrs_canon."Name_submitted", tnrs_canon."Overall_score", tnrs_canon."Name_matched", tnrs_canon."Name_matched_rank", tnrs_canon."Name_score", tnrs_canon."Name_matched_author", tnrs_canon."Name_matched_url", tnrs_canon."Author_matched", tnrs_canon."Author_score", tnrs_canon."Family_matched", tnrs_canon."Family_score", tnrs_canon."Name_matched_accepted_family", tnrs_canon."Genus_matched", tnrs_canon."Genus_score", tnrs_canon."Specific_epithet_matched", tnrs_canon."Specific_epithet_score", tnrs_canon."Infraspecific_rank", tnrs_canon."Infraspecific_epithet_matched", tnrs_canon."Infraspecific_epithet_score", tnrs_canon."Infraspecific_rank_2", tnrs_canon."Infraspecific_epithet_2_matched", tnrs_canon."Infraspecific_epithet_2_score", tnrs_canon."Annotations", tnrs_canon."Unmatched_terms", tnrs_canon."Taxonomic_status", tnrs_canon."Selected", tnrs_canon."Source", tnrs_canon."Warnings" FROM (((taxondetermination JOIN taxonverbatim USING (taxonverbatim_id)) JOIN taxonlabel USING (taxonlabel_id)) JOIN "TNRS".tnrs_canon ON ((tnrs_canon."Name_submitted" = taxonlabel.taxonomicname))) WHERE (taxondetermination.iscurrent AND (taxondetermination.source_id <> (SELECT source.source_id FROM source WHERE (source.shortname = 'TNRS'::text)))) ORDER BY taxondetermination.taxonoccurrence_id;
|
|
| 4521 | 4521 |
|
| 4522 | 4522 |
|
| 4523 | 4523 |
-- |
Also available in: Unified diff
schemas/vegbien.sql: Use new TNRS.tnrs_canon instead of tnrs+accepted to avoid creating additional taxonlabels for the parsed, matched, and accepted names and instead just use the most-canonicalized name of the names output by TNRS (the accepted name if available, or the matched name otherwise)