|
1
|
--
|
|
2
|
-- PostgreSQL database dump
|
|
3
|
--
|
|
4
|
|
|
5
|
SET statement_timeout = 0;
|
|
6
|
SET client_encoding = 'UTF8';
|
|
7
|
SET standard_conforming_strings = off;
|
|
8
|
SET check_function_bodies = false;
|
|
9
|
SET client_min_messages = warning;
|
|
10
|
SET escape_string_warning = off;
|
|
11
|
|
|
12
|
--
|
|
13
|
-- Name: TNRS; Type: SCHEMA; Schema: -; Owner: bien
|
|
14
|
--
|
|
15
|
|
|
16
|
CREATE SCHEMA "TNRS";
|
|
17
|
|
|
18
|
|
|
19
|
ALTER SCHEMA "TNRS" OWNER TO bien;
|
|
20
|
|
|
21
|
SET search_path = "TNRS", pg_catalog;
|
|
22
|
|
|
23
|
SET default_tablespace = '';
|
|
24
|
|
|
25
|
SET default_with_oids = false;
|
|
26
|
|
|
27
|
--
|
|
28
|
-- Name: tnrs; Type: TABLE; Schema: TNRS; Owner: bien; Tablespace:
|
|
29
|
--
|
|
30
|
|
|
31
|
CREATE TABLE tnrs (
|
|
32
|
"Name_number" text,
|
|
33
|
"Name_submitted" text NOT NULL,
|
|
34
|
"Overall_score" text,
|
|
35
|
"Name_matched" text,
|
|
36
|
"Name_matched_rank" text,
|
|
37
|
"Name_score" text,
|
|
38
|
"Name_matched_author" text,
|
|
39
|
"Name_matched_url" text,
|
|
40
|
"Author_matched" text,
|
|
41
|
"Author_score" text,
|
|
42
|
"Family_matched" text,
|
|
43
|
"Family_score" text,
|
|
44
|
"Name_matched_accepted_family" text,
|
|
45
|
"Genus_matched" text,
|
|
46
|
"Genus_score" text,
|
|
47
|
"Specific_epithet_matched" text,
|
|
48
|
"Specific_epithet_score" text,
|
|
49
|
"Infraspecific_rank" text,
|
|
50
|
"Infraspecific_epithet_matched" text,
|
|
51
|
"Infraspecific_epithet_score" text,
|
|
52
|
"Infraspecific_rank_2" text,
|
|
53
|
"Infraspecific_epithet_2_matched" text,
|
|
54
|
"Infraspecific_epithet_2_score" text,
|
|
55
|
"Annotations" text,
|
|
56
|
"Unmatched_terms" text,
|
|
57
|
"Taxonomic_status" text,
|
|
58
|
"Accepted_name" text,
|
|
59
|
"Accepted_name_author" text,
|
|
60
|
"Accepted_name_rank" text,
|
|
61
|
"Accepted_name_url" text,
|
|
62
|
"Accepted_name_species" text,
|
|
63
|
"Accepted_name_family" text,
|
|
64
|
"Selected" text,
|
|
65
|
"Source" text,
|
|
66
|
"Warnings" text,
|
|
67
|
"Accepted_name_lsid" text
|
|
68
|
);
|
|
69
|
|
|
70
|
|
|
71
|
ALTER TABLE "TNRS".tnrs OWNER TO bien;
|
|
72
|
|
|
73
|
--
|
|
74
|
-- Name: TABLE tnrs; Type: COMMENT; Schema: TNRS; Owner: bien
|
|
75
|
--
|
|
76
|
|
|
77
|
COMMENT ON TABLE tnrs IS 'tnrs_accepted_names sorts accepted names first (note that false sorts before true). Accepted names are defined as names that scrub to themselves.
|
|
78
|
|
|
79
|
Accepted names must be processed before any names that resolve to them, because the entry for the accepted name contains all the ranks parsed out but the resolved name of another entry contains just some ranks and the taxonomic name. Column-based import will do this automatically when the total # of rows is <= the partition_size (because _taxonconcept_set_matched_concept_id()''s accepted taxonconcept is created after the main taxonconcept), but TNRS has more rows than this so sorting is needed to ensure that all the accepted names are processed in the first partitions.';
|
|
80
|
|
|
81
|
|
|
82
|
--
|
|
83
|
-- Data for Name: tnrs; Type: TABLE DATA; Schema: TNRS; Owner: bien
|
|
84
|
--
|
|
85
|
|
|
86
|
COPY tnrs ("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") FROM stdin;
|
|
87
|
1 Poa annua L. 1 Poa annua species 1 L. http://www.tropicos.org/Name/25509881;http://plants.usda.gov/java/profile?symbol=POAN L. 1 \N \N Poaceae Poa 1 annua 1 \N \N \N \N \N \N \N \N Accepted Poa annua L. species http://www.tropicos.org/Name/25509881;http://plants.usda.gov/java/profile?symbol=POAN Poa annua Poaceae true tropicos;usda \N \N
|
|
88
|
1 Poa annua var. annua 1 Poa annua var. annua variety 1 \N http://www.tropicos.org/Name/25517736 \N \N \N \N Poaceae Poa 1 annua 1 var. annua 1 \N \N \N \N \N Accepted Poa annua var. annua \N variety http://www.tropicos.org/Name/25517736 Poa annua Poaceae true tropicos \N \N
|
|
89
|
2 Poa infirma Kunth 1 Poa infirma species 1 Kunth http://www.tropicos.org/Name/25514158;http://plants.usda.gov/java/profile?symbol=POIN30 Kunth 1 \N \N Poaceae Poa 1 infirma 1 \N \N \N \N \N \N \N \N Accepted Poa infirma Kunth species http://www.tropicos.org/Name/25514158;http://plants.usda.gov/java/profile?symbol=POIN30 Poa infirma Poaceae true tropicos;usda \N \N
|
|
90
|
0 Asteraceae Bercht. & J. Presl 0.4 Asteraceae family 0.5 \N http://compositae.landcareresearch.co.nz/default.aspx;http://plants.usda.gov/java/nameSearch \N \N Asteraceae 1 \N \N \N \N \N \N \N \N \N \N \N \N Bercht. & J. Presl Accepted Asteraceae \N family http://compositae.landcareresearch.co.nz/default.aspx;http://plants.usda.gov/java/nameSearch \N \N true gcc;usda [Partial match] \N
|
|
91
|
14 Compositae indet. sp.1 0.9 Compositae family 1 Giseke http://www.tropicos.org/Name/50255940 \N \N Compositae 1 Compositae \N \N \N \N \N \N \N \N \N \N \N indet. sp.1 Synonym Asteraceae Bercht. & J. Presl family http://www.tropicos.org/Name/50307371 \N Asteraceae true tropicos \N \N
|
|
92
|
12 Fabaceae Boyle#6500 0.9 Fabaceae family 1 Lindl. http://www.tropicos.org/Name/42000184 \N \N Fabaceae 1 Fabaceae \N \N \N \N \N \N \N \N \N \N \N Boyle#6500 Accepted Fabaceae Lindl. family http://www.tropicos.org/Name/42000184 \N Fabaceae true tropicos \N \N
|
|
93
|
10 Fabaceae Inga "fuzzy leaf" 0.9 Inga genus 1 Mill. http://www.tropicos.org/Name/40031040 \N \N Fabaceae 1 Fabaceae Inga 1 \N \N \N \N \N \N \N \N \N "fuzzy leaf" Accepted Inga Mill. genus http://www.tropicos.org/Name/40031040 \N Fabaceae true tropicos \N \N
|
|
94
|
9 Fabaceae Inga sp.3 0.9 Inga genus 1 Mill. http://www.tropicos.org/Name/40031040 \N \N Fabaceae 1 Fabaceae Inga 1 \N \N \N \N \N \N \N \N \N sp.3 Accepted Inga Mill. genus http://www.tropicos.org/Name/40031040 \N Fabaceae true tropicos \N \N
|
|
95
|
11 Fabaceae unknown #2 0.9 Fabaceae family 1 Lindl. http://www.tropicos.org/Name/42000184 \N \N Fabaceae 1 Fabaceae \N \N \N \N \N \N \N \N \N \N \N unknown #2 Accepted Fabaceae Lindl. family http://www.tropicos.org/Name/42000184 \N Fabaceae true tropicos \N \N
|
|
96
|
0 Poa annua 1 Poa annua species 1 L. http://www.tropicos.org/Name/25509881;http://plants.usda.gov/java/profile?symbol=POAN \N \N \N \N Poaceae Poa 1 annua 1 \N \N \N \N \N \N \N \N Accepted Poa annua L. species http://www.tropicos.org/Name/25509881;http://plants.usda.gov/java/profile?symbol=POAN Poa annua Poaceae true tropicos;usda \N \N
|
|
97
|
6 Poa annua fo. lanuginosa 1 Poa annua fo. lanuginosa forma 1 Sennen http://www.tropicos.org/Name/50267771 \N \N \N \N Poaceae Poa 1 annua 1 fo. lanuginosa 1 \N \N \N \N \N Synonym Poa annua var. annua \N variety http://www.tropicos.org/Name/25517736 Poa annua Poaceae true tropicos \N \N
|
|
98
|
4 Poa annua ssp. exilis 1 Poa annua subsp. exilis subspecies 1 (Tomm. ex Freyn) Asch. & Graebn. http://www.tropicos.org/Name/50063800 \N \N \N \N Poaceae Poa 1 annua 1 subsp. exilis 1 \N \N \N \N \N Synonym Poa infirma Kunth species http://www.tropicos.org/Name/25514158 Poa infirma Poaceae true tropicos \N \N
|
|
99
|
3 Poa annua subsp. exilis 1 Poa annua subsp. exilis subspecies 1 (Tomm. ex Freyn) Asch. & Graebn. http://www.tropicos.org/Name/50063800 \N \N \N \N Poaceae Poa 1 annua 1 subsp. exilis 1 \N \N \N \N \N Synonym Poa infirma Kunth species http://www.tropicos.org/Name/25514158 Poa infirma Poaceae true tropicos \N \N
|
|
100
|
5 Poa annua subvar. minima 1 Poa annua subvar. minima subvariety 1 (Schur) Asch. & Graebn. http://www.tropicos.org/Name/50158097 \N \N \N \N Poaceae Poa 1 annua 1 subvar. minima 1 \N \N \N \N \N Accepted Poa annua subvar. minima (Schur) Asch. & Graebn. subvariety http://www.tropicos.org/Name/50158097 Poa annua Poaceae true tropicos \N \N
|
|
101
|
2 Poa annua var. eriolepis 1 Poa annua var. eriolepis variety 1 E. Desv. http://www.tropicos.org/Name/50119145 \N \N \N \N Poaceae Poa 1 annua 1 var. eriolepis 1 \N \N \N \N \N Synonym Poa annua L. species http://www.tropicos.org/Name/25509881 Poa annua Poaceae true tropicos \N \N
|
|
102
|
8 Silene scouleri Hook. subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire var. grisea C.L. Hitchc. & Maguire 0.77 Silene scouleri subsp. pringlei subspecies 0.77 (S. Watson) C.L. Hitchc. & Maguire http://www.tropicos.org/Name/6303627 \N \N \N \N Caryophyllaceae Silene 1 scouleri 1 subsp. pringlei 1 \N \N \N \N var. grisea Accepted Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire subspecies http://www.tropicos.org/Name/6303627 Silene scouleri Caryophyllaceae true tropicos [Partial match] \N
|
|
103
|
7 Silene scouleri subsp. pringlei var. grisea C.L. Hitchc. & Maguire 0.77 Silene scouleri subsp. pringlei subspecies 0.77 (S. Watson) C.L. Hitchc. & Maguire http://www.tropicos.org/Name/6303627 \N \N \N \N Caryophyllaceae Silene 1 scouleri 1 subsp. pringlei 1 \N \N \N \N var. grisea Accepted Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire subspecies http://www.tropicos.org/Name/6303627 Silene scouleri Caryophyllaceae true tropicos [Partial match] \N
|
|
104
|
13 Fam_indet. Boyle#6501 0 No suitable matches found. \N 0 \N \N \N 0 \N 0 \N \N 0 \N 0 \N \N 0 \N \N 0 \N \N \N \N \N \N \N \N \N true \N \N \N
|
|
105
|
3 No suitable matches found. 0 No suitable matches found. \N 0 \N \N \N 0 \N 0 \N \N 0 \N 0 \N \N 0 \N \N 0 \N \N \N \N \N \N \N \N \N true \N \N \N
|
|
106
|
\.
|
|
107
|
|
|
108
|
|
|
109
|
--
|
|
110
|
-- Name: tnrs_pkey; Type: CONSTRAINT; Schema: TNRS; Owner: bien; Tablespace:
|
|
111
|
--
|
|
112
|
|
|
113
|
ALTER TABLE ONLY tnrs
|
|
114
|
ADD CONSTRAINT tnrs_pkey PRIMARY KEY ("Name_submitted");
|
|
115
|
|
|
116
|
|
|
117
|
--
|
|
118
|
-- Name: tnrs_accepted_names; Type: INDEX; Schema: TNRS; Owner: bien; Tablespace:
|
|
119
|
--
|
|
120
|
|
|
121
|
CREATE INDEX tnrs_accepted_names ON tnrs USING btree (((NOT ("Name_submitted" = ("Accepted_name" || COALESCE((' '::text || "Accepted_name_author"), ''::text))))), "Name_submitted");
|
|
122
|
|
|
123
|
ALTER TABLE tnrs CLUSTER ON tnrs_accepted_names;
|
|
124
|
|
|
125
|
|
|
126
|
--
|
|
127
|
-- PostgreSQL database dump complete
|
|
128
|
--
|
|
129
|
|
