Project

General

Profile

« Previous | Next » 

Revision 7128

Added by Aaron Marcuse-Kubitza over 11 years ago

schemas/vegbien.sql: unscrubbed_taxondetermination_view: Use new tnrs+accepted instead of tnrs so that the accepted name can be imported at the same time

View differences:

inputs/.TNRS/public.unscrubbed_taxondetermination_view/header.csv
1 
taxonoccurrence_id,Time_submitted,Name_number,Name_submitted,Overall_score,Name_matched,Name_matched_rank,Name_score,Name_matched_author,Name_matched_url,Author_matched,Author_score,Family_matched,Family_score,Name_matched_accepted_family,Genus_matched,Genus_score,Specific_epithet_matched,Specific_epithet_score,Infraspecific_rank,Infraspecific_epithet_matched,Infraspecific_epithet_score,Infraspecific_rank_2,Infraspecific_epithet_2_matched,Infraspecific_epithet_2_score,Annotations,Unmatched_terms,Taxonomic_status,Accepted_name,Accepted_name_author,Accepted_name_rank,Accepted_name_url,Accepted_name_species,Accepted_name_family,Selected,Source,Warnings,Accepted_name_lsid
1 
taxonoccurrence_id,Time_submitted,Name_number,Name_submitted,Overall_score,Name_matched,Name_matched_rank,Name_score,Name_matched_author,Name_matched_url,Author_matched,Author_score,Family_matched,Family_score,Name_matched_accepted_family,Genus_matched,Genus_score,Specific_epithet_matched,Specific_epithet_score,Infraspecific_rank,Infraspecific_epithet_matched,Infraspecific_epithet_score,Infraspecific_rank_2,Infraspecific_epithet_2_matched,Infraspecific_epithet_2_score,Annotations,Unmatched_terms,Taxonomic_status,Accepted_name,Accepted_name_author,Accepted_name_rank,Accepted_name_url,Accepted_name_species,Accepted_name_family,Selected,Source,Warnings,Accepted_name_lsid,Accepted_name.Time_submitted,Accepted_name.Name_number,Accepted_name.Name_submitted,Accepted_name.Overall_score,Accepted_name.Name_matched,Accepted_name.Name_matched_rank,Accepted_name.Name_score,Accepted_name.Name_matched_author,Accepted_name.Name_matched_url,Accepted_name.Author_matched,Accepted_name.Author_score,Accepted_name.Family_matched,Accepted_name.Family_score,Accepted_name.Name_matched_accepted_family,Accepted_name.Genus_matched,Accepted_name.Genus_score,Accepted_name.Specific_epithet_matched,Accepted_name.Specific_epithet_score,Accepted_name.Infraspecific_rank,Accepted_name.Infraspecific_epithet_matched,Accepted_name.Infraspecific_epithet_score,Accepted_name.Infraspecific_rank_2,Accepted_name.Infraspecific_epithet_2_matched,Accepted_name.Infraspecific_epithet_2_score,Accepted_name.Annotations,Accepted_name.Unmatched_terms,Accepted_name.Taxonomic_status,Accepted_name.Accepted_name,Accepted_name.Accepted_name_author,Accepted_name.Accepted_name_rank,Accepted_name.Accepted_name_url,Accepted_name.Accepted_name_species,Accepted_name.Accepted_name_family,Accepted_name.Selected,Accepted_name.Source,Accepted_name.Warnings,Accepted_name.Accepted_name_lsid
inputs/.TNRS/public.unscrubbed_taxondetermination_view/map.csv
41 41 
Source,*Source,,
42 42 
Warnings,*Warnings,,
43 43 
Accepted_name_lsid,*Accepted_name_lsid,,
44 
Accepted_name.Time_submitted,*Accepted_name.Time_submitted,,
45 
Accepted_name.Name_number,*Accepted_name.Name_number,,
46 
Accepted_name.Name_submitted,*Accepted_name.Name_submitted,,
47 
Accepted_name.Overall_score,*Accepted_name.Overall_score,,
48 
Accepted_name.Name_matched,*Accepted_name.Name_matched,,
49 
Accepted_name.Name_matched_rank,*Accepted_name.Name_matched_rank,,
50 
Accepted_name.Name_score,*Accepted_name.Name_score,,
51 
Accepted_name.Name_matched_author,*Accepted_name.Name_matched_author,,
52 
Accepted_name.Name_matched_url,*Accepted_name.Name_matched_url,,
53 
Accepted_name.Author_matched,*Accepted_name.Author_matched,,
54 
Accepted_name.Author_score,*Accepted_name.Author_score,,
55 
Accepted_name.Family_matched,*Accepted_name.Family_matched,,
56 
Accepted_name.Family_score,*Accepted_name.Family_score,,
57 
Accepted_name.Name_matched_accepted_family,*Accepted_name.Name_matched_accepted_family,,
58 
Accepted_name.Genus_matched,*Accepted_name.Genus_matched,,
59 
Accepted_name.Genus_score,*Accepted_name.Genus_score,,
60 
Accepted_name.Specific_epithet_matched,*Accepted_name.Specific_epithet_matched,,
61 
Accepted_name.Specific_epithet_score,*Accepted_name.Specific_epithet_score,,
62 
Accepted_name.Infraspecific_rank,*Accepted_name.Infraspecific_rank,,
63 
Accepted_name.Infraspecific_epithet_matched,*Accepted_name.Infraspecific_epithet_matched,,
64 
Accepted_name.Infraspecific_epithet_score,*Accepted_name.Infraspecific_epithet_score,,
65 
Accepted_name.Infraspecific_rank_2,*Accepted_name.Infraspecific_rank_2,,
66 
Accepted_name.Infraspecific_epithet_2_matched,*Accepted_name.Infraspecific_epithet_2_matched,,
67 
Accepted_name.Infraspecific_epithet_2_score,*Accepted_name.Infraspecific_epithet_2_score,,
68 
Accepted_name.Annotations,*Accepted_name.Annotations,,
69 
Accepted_name.Unmatched_terms,*Accepted_name.Unmatched_terms,,
70 
Accepted_name.Taxonomic_status,*Accepted_name.Taxonomic_status,,
71 
Accepted_name.Accepted_name,*Accepted_name.Accepted_name,,
72 
Accepted_name.Accepted_name_author,*Accepted_name.Accepted_name_author,,
73 
Accepted_name.Accepted_name_rank,*Accepted_name.Accepted_name_rank,,
74 
Accepted_name.Accepted_name_url,*Accepted_name.Accepted_name_url,,
75 
Accepted_name.Accepted_name_species,*Accepted_name.Accepted_name_species,,
76 
Accepted_name.Accepted_name_family,*Accepted_name.Accepted_name_family,,
77 
Accepted_name.Selected,*Accepted_name.Selected,,
78 
Accepted_name.Source,*Accepted_name.Source,,
79 
Accepted_name.Warnings,*Accepted_name.Warnings,,
80 
Accepted_name.Accepted_name_lsid,*Accepted_name.Accepted_name_lsid,,
44 81 
:computer,identificationType,,
45 82 
:_,dateIdentified,/_now,
46 83 
:0,taxonOccurrenceID,,Enables the taxonoccurrence CHECK constraint to pass
inputs/.TNRS/public.unscrubbed_taxondetermination_view/VegBIEN.csv
73 73 
:_,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/determinationdate/_alt/1/_dateRangeStart/value/_now,
74 74 
:computer,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/determinationtype,
75 75 
taxonoccurrence_id,/location/locationevent/taxonoccurrence/taxonoccurrence_id,
76 
Accepted_name.Accepted_name,,** No join mapping for *Accepted_name.Accepted_name **
77 
Accepted_name.Accepted_name_author,,** No join mapping for *Accepted_name.Accepted_name_author **
78 
Accepted_name.Accepted_name_family,,** No join mapping for *Accepted_name.Accepted_name_family **
79 
Accepted_name.Accepted_name_lsid,,** No join mapping for *Accepted_name.Accepted_name_lsid **
80 
Accepted_name.Accepted_name_rank,,** No join mapping for *Accepted_name.Accepted_name_rank **
81 
Accepted_name.Accepted_name_species,,** No join mapping for *Accepted_name.Accepted_name_species **
82 
Accepted_name.Accepted_name_url,,** No join mapping for *Accepted_name.Accepted_name_url **
83 
Accepted_name.Annotations,,** No join mapping for *Accepted_name.Annotations **
84 
Accepted_name.Author_matched,,** No join mapping for *Accepted_name.Author_matched **
85 
Accepted_name.Author_score,,** No join mapping for *Accepted_name.Author_score **
86 
Accepted_name.Family_matched,,** No join mapping for *Accepted_name.Family_matched **
87 
Accepted_name.Family_score,,** No join mapping for *Accepted_name.Family_score **
88 
Accepted_name.Genus_matched,,** No join mapping for *Accepted_name.Genus_matched **
89 
Accepted_name.Genus_score,,** No join mapping for *Accepted_name.Genus_score **
90 
Accepted_name.Infraspecific_epithet_2_matched,,** No join mapping for *Accepted_name.Infraspecific_epithet_2_matched **
91 
Accepted_name.Infraspecific_epithet_2_score,,** No join mapping for *Accepted_name.Infraspecific_epithet_2_score **
92 
Accepted_name.Infraspecific_epithet_matched,,** No join mapping for *Accepted_name.Infraspecific_epithet_matched **
93 
Accepted_name.Infraspecific_epithet_score,,** No join mapping for *Accepted_name.Infraspecific_epithet_score **
94 
Accepted_name.Infraspecific_rank,,** No join mapping for *Accepted_name.Infraspecific_rank **
95 
Accepted_name.Infraspecific_rank_2,,** No join mapping for *Accepted_name.Infraspecific_rank_2 **
96 
Accepted_name.Name_matched,,** No join mapping for *Accepted_name.Name_matched **
97 
Accepted_name.Name_matched_accepted_family,,** No join mapping for *Accepted_name.Name_matched_accepted_family **
98 
Accepted_name.Name_matched_author,,** No join mapping for *Accepted_name.Name_matched_author **
99 
Accepted_name.Name_matched_rank,,** No join mapping for *Accepted_name.Name_matched_rank **
100 
Accepted_name.Name_matched_url,,** No join mapping for *Accepted_name.Name_matched_url **
101 
Accepted_name.Name_number,,** No join mapping for *Accepted_name.Name_number **
102 
Accepted_name.Name_score,,** No join mapping for *Accepted_name.Name_score **
103 
Accepted_name.Name_submitted,,** No join mapping for *Accepted_name.Name_submitted **
104 
Accepted_name.Overall_score,,** No join mapping for *Accepted_name.Overall_score **
105 
Accepted_name.Selected,,** No join mapping for *Accepted_name.Selected **
106 
Accepted_name.Source,,** No join mapping for *Accepted_name.Source **
107 
Accepted_name.Specific_epithet_matched,,** No join mapping for *Accepted_name.Specific_epithet_matched **
108 
Accepted_name.Specific_epithet_score,,** No join mapping for *Accepted_name.Specific_epithet_score **
109 
Accepted_name.Taxonomic_status,,** No join mapping for *Accepted_name.Taxonomic_status **
110 
Accepted_name.Time_submitted,,** No join mapping for *Accepted_name.Time_submitted **
111 
Accepted_name.Unmatched_terms,,** No join mapping for *Accepted_name.Unmatched_terms **
112 
Accepted_name.Warnings,,** No join mapping for *Accepted_name.Warnings **
76 113 
Accepted_name_lsid,,** No join mapping for *Accepted_name_lsid **
77 114 
Accepted_name_species,,** No join mapping for *Accepted_name_species ** Also contains genus. Omitted because Accepted_name contains more info.
78 115 
Accepted_name_url,,** No join mapping for *Accepted_name_url **
inputs/.TNRS/public.unscrubbed_taxondetermination_view/unmapped_terms.csv
10 10 
*Source
11 11 
*Warnings
12 12 
*Accepted_name_lsid
13 
*Accepted_name.Time_submitted
14 
*Accepted_name.Name_number
15 
*Accepted_name.Name_submitted
16 
*Accepted_name.Overall_score
17 
*Accepted_name.Name_matched
18 
*Accepted_name.Name_matched_rank
19 
*Accepted_name.Name_score
20 
*Accepted_name.Name_matched_author
21 
*Accepted_name.Name_matched_url
22 
*Accepted_name.Author_matched
23 
*Accepted_name.Author_score
24 
*Accepted_name.Family_matched
25 
*Accepted_name.Family_score
26 
*Accepted_name.Name_matched_accepted_family
27 
*Accepted_name.Genus_matched
28 
*Accepted_name.Genus_score
29 
*Accepted_name.Specific_epithet_matched
30 
*Accepted_name.Specific_epithet_score
31 
*Accepted_name.Infraspecific_rank
32 
*Accepted_name.Infraspecific_epithet_matched
33 
*Accepted_name.Infraspecific_epithet_score
34 
*Accepted_name.Infraspecific_rank_2
35 
*Accepted_name.Infraspecific_epithet_2_matched
36 
*Accepted_name.Infraspecific_epithet_2_score
37 
*Accepted_name.Annotations
38 
*Accepted_name.Unmatched_terms
39 
*Accepted_name.Taxonomic_status
40 
*Accepted_name.Accepted_name
41 
*Accepted_name.Accepted_name_author
42 
*Accepted_name.Accepted_name_rank
43 
*Accepted_name.Accepted_name_url
44 
*Accepted_name.Accepted_name_species
45 
*Accepted_name.Accepted_name_family
46 
*Accepted_name.Selected
47 
*Accepted_name.Source
48 
*Accepted_name.Warnings
49 
*Accepted_name.Accepted_name_lsid
schemas/vegbien.sql
4516 4516 
--
4517 4517 

  		




  4518
  4518
  
    
CREATE VIEW unscrubbed_taxondetermination_view AS


  		




  4519
  
  
    
    SELECT taxondetermination.taxonoccurrence_id, tnrs."Time_submitted", tnrs."Name_number", tnrs."Name_submitted", tnrs."Overall_score", tnrs."Name_matched", tnrs."Name_matched_rank", tnrs."Name_score", tnrs."Name_matched_author", tnrs."Name_matched_url", tnrs."Author_matched", tnrs."Author_score", tnrs."Family_matched", tnrs."Family_score", tnrs."Name_matched_accepted_family", tnrs."Genus_matched", tnrs."Genus_score", tnrs."Specific_epithet_matched", tnrs."Specific_epithet_score", tnrs."Infraspecific_rank", tnrs."Infraspecific_epithet_matched", tnrs."Infraspecific_epithet_score", tnrs."Infraspecific_rank_2", tnrs."Infraspecific_epithet_2_matched", tnrs."Infraspecific_epithet_2_score", tnrs."Annotations", tnrs."Unmatched_terms", tnrs."Taxonomic_status", tnrs."Accepted_name", tnrs."Accepted_name_author", tnrs."Accepted_name_rank", tnrs."Accepted_name_url", tnrs."Accepted_name_species", tnrs."Accepted_name_family", tnrs."Selected", tnrs."Source", tnrs."Warnings", tnrs."Accepted_name_lsid" FROM (((taxondetermination JOIN taxonverbatim USING (taxonverbatim_id)) JOIN taxonlabel USING (taxonlabel_id)) JOIN "TNRS".tnrs ON ((tnrs."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;


  		




  
  4519
  
    
    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_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" 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
  4520
  
    

  		




  4521
  4521
  
    

  		




  4522
  4522
  
    
--


  		












Also available in:  Unified diff