SET standard_conforming_strings = on;

-- Name: score_ok(double precision); Type: FUNCTION; Schema: TNRS; Owner: bien

CREATE FUNCTION score_ok(score double precision) RETURNS boolean

    LANGUAGE sql

SELECT $1 >= 0.8

ALTER FUNCTION "TNRS".score_ok(score double precision) OWNER TO bien;

--

-- Name: tnrs_populate_derived_fields(); Type: FUNCTION; Schema: TNRS; Owner: bien

--

CREATE FUNCTION tnrs_populate_derived_fields() RETURNS trigger

    LANGUAGE plpgsql

    AS $$

BEGIN

    new."Accepted_scientific_name" = NULLIF(concat_ws(' '

        , NULLIF(NULLIF(new."Accepted_family", 'Unknown'), new."Accepted_name")

        , new."Accepted_name"

        , new."Accepted_name_author"

    ), '');

    new."Max_score" = GREATEST(

          new."Overall_score"::double precision

        , new."Family_score"::double precision

        , new."Genus_score"::double precision

        , new."Specific_epithet_score"::double precision

    );

    RETURN new;

END;

$$;

ALTER FUNCTION "TNRS".tnrs_populate_derived_fields() OWNER TO bien;

    "Accepted_species" text,

    "Accepted_family" text,

    "Accepted_name_lsid" text,

    "Accepted_scientific_name" text,

    "Max_score" double precision

-- Name: AcceptedTaxon; Type: VIEW; Schema: TNRS; Owner: bien

CREATE VIEW "AcceptedTaxon" AS

    SELECT tnrs."Time_submitted" AS "*Accepted_name.Time_submitted", tnrs."Name_submitted" AS "acceptedScientificName", tnrs."Genus_matched" AS "acceptedGenus", tnrs."Specific_epithet_matched" AS "acceptedSpecificEpithet", tnrs."Infraspecific_epithet_matched" AS "acceptedInfraspecificEpithet", tnrs."Annotations" AS "*Accepted_name.Annotations", tnrs."Taxonomic_status" AS "acceptedTaxonomicStatus", tnrs."Selected" AS "*Accepted_name.Selected", tnrs."Source" AS "*Accepted_name.Source", tnrs."Warnings" AS "*Accepted_name.Warnings", tnrs."Accepted_name_lsid" AS "*Accepted_name.Accepted_name_lsid" FROM tnrs;

ALTER TABLE "TNRS"."AcceptedTaxon" OWNER TO bien;

-- Name: MatchedTaxon; Type: VIEW; Schema: TNRS; Owner: bien

CREATE VIEW "MatchedTaxon" AS

    SELECT tnrs."Time_submitted" AS "*Name_matched.Time_submitted", tnrs."Name_submitted" AS "concatenatedScientificName", tnrs."Name_matched" AS "matchedTaxonName", tnrs."Name_matched_rank" AS "matchedTaxonRank", tnrs."Name_score" AS "*Name_matched.Name_score", tnrs."Name_matched_author" AS "matchedScientificNameAuthorship", tnrs."Name_matched_url" AS "matchedScientificNameID", tnrs."Author_score" AS "*Name_matched.Author_score", tnrs."Family_score" AS "matchedFamilyConfidence_fraction", tnrs."Genus_matched" AS "matchedGenus", tnrs."Genus_score" AS "matchedGenusConfidence_fraction", tnrs."Specific_epithet_matched" AS "matchedSpecificEpithet", tnrs."Specific_epithet_score" AS "matchedSpeciesConfidence_fraction", tnrs."Infraspecific_epithet_matched" AS "matchedInfraspecificEpithet", tnrs."Infraspecific_epithet_score" AS "*Name_matched.Infraspecific_epithet_score", tnrs."Annotations" AS "identificationQualifier", tnrs."Unmatched_terms" AS "morphospeciesSuffix", tnrs."Taxonomic_status" AS "taxonomicStatus", tnrs."Accepted_name" AS "acceptedTaxonName", tnrs."Accepted_name_author" AS "acceptedScientificNameAuthorship", tnrs."Accepted_name_rank" AS "acceptedTaxonRank", tnrs."Accepted_name_url" AS "acceptedScientificNameID", tnrs."Accepted_species" AS "*Name_matched.Accepted_species", tnrs."Accepted_family" AS "acceptedFamily", tnrs."Selected" AS "*Name_matched.Selected", tnrs."Source" AS "*Name_matched.Source", tnrs."Warnings" AS "*Name_matched.Warnings", tnrs."Accepted_name_lsid" AS "*Name_matched.Accepted_name_lsid", tnrs."Accepted_scientific_name" AS "acceptedScientificName", tnrs."Max_score" AS "matchedTaxonConfidence_fraction" FROM tnrs;

ALTER TABLE "TNRS"."MatchedTaxon" OWNER TO bien;

-- Name: ScrubbedTaxon; Type: VIEW; Schema: TNRS; Owner: bien

CREATE VIEW "ScrubbedTaxon" AS

    SELECT "MatchedTaxon"."acceptedScientificName", "MatchedTaxon"."*Name_matched.Time_submitted", "MatchedTaxon"."concatenatedScientificName", "MatchedTaxon"."matchedTaxonName", "MatchedTaxon"."matchedTaxonRank", "MatchedTaxon"."*Name_matched.Name_score", "MatchedTaxon"."matchedScientificNameAuthorship", "MatchedTaxon"."matchedScientificNameID", "MatchedTaxon"."*Name_matched.Author_score", "MatchedTaxon"."matchedFamilyConfidence_fraction", "MatchedTaxon"."matchedGenus", "MatchedTaxon"."matchedGenusConfidence_fraction", "MatchedTaxon"."matchedSpecificEpithet", "MatchedTaxon"."matchedSpeciesConfidence_fraction", "MatchedTaxon"."matchedInfraspecificEpithet", "MatchedTaxon"."*Name_matched.Infraspecific_epithet_score", "MatchedTaxon"."identificationQualifier", "MatchedTaxon"."morphospeciesSuffix", "MatchedTaxon"."taxonomicStatus", "MatchedTaxon"."acceptedTaxonName", "MatchedTaxon"."acceptedScientificNameAuthorship", "MatchedTaxon"."acceptedTaxonRank", "MatchedTaxon"."acceptedScientificNameID", "MatchedTaxon"."*Name_matched.Accepted_species", "MatchedTaxon"."acceptedFamily", "MatchedTaxon"."*Name_matched.Selected", "MatchedTaxon"."*Name_matched.Source", "MatchedTaxon"."*Name_matched.Warnings", "MatchedTaxon"."*Name_matched.Accepted_name_lsid", "MatchedTaxon"."matchedTaxonConfidence_fraction", "AcceptedTaxon"."*Accepted_name.Time_submitted", "AcceptedTaxon"."acceptedGenus", "AcceptedTaxon"."acceptedSpecificEpithet", "AcceptedTaxon"."acceptedInfraspecificEpithet", "AcceptedTaxon"."*Accepted_name.Annotations", "AcceptedTaxon"."acceptedTaxonomicStatus", "AcceptedTaxon"."*Accepted_name.Selected", "AcceptedTaxon"."*Accepted_name.Source", "AcceptedTaxon"."*Accepted_name.Warnings", "AcceptedTaxon"."*Accepted_name.Accepted_name_lsid" FROM ("MatchedTaxon" NATURAL LEFT JOIN "AcceptedTaxon") WHERE score_ok("MatchedTaxon"."matchedTaxonConfidence_fraction");

ALTER TABLE "TNRS"."ScrubbedTaxon" OWNER TO bien;

-- Data for Name: tnrs; Type: TABLE DATA; Schema: TNRS; Owner: bien

COPY tnrs ("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", "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_species", "Accepted_family", "Selected", "Source", "Warnings", "Accepted_name_lsid", "Accepted_scientific_name", "Max_score") FROM stdin;

2013-05-21 19:23:15.278517-07	0	Fam_indet. Boyle#6501	0	No suitable matches found.	\N	0	\N	\N	\N	0	\N	0	\N	0	\N	0	\N	\N	0	\N	\N	0	\N	\N	\N	\N	\N	\N	\N	\N	\N	true	\N	 	\N	\N	0

2013-05-21 19:23:15.278517-07	1	Poa annua var. eriolepis	1	Poa annua var. eriolepis	variety	1	E. Desv.	http://www.tropicos.org/Name/50119145	\N	\N	\N	\N	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	Poaceae Poa annua L.	1

2013-05-21 19:23:15.278517-07	2	Poa annua	1	Poa annua	species	1	L.	http://www.tropicos.org/Name/25509881	\N	\N	\N	\N	Poa	1	annua	1	\N	\N	\N	\N	\N	\N	\N	\N	Accepted	Poa annua	L.	species	http://www.tropicos.org/Name/25509881	Poa annua	Poaceae	true	tropicos	 	\N	Poaceae Poa annua L.	1

2013-05-21 19:23:15.278517-07	3	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	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	Caryophyllaceae Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire	1

2013-05-21 19:23:15.278517-07	4	Fabaceae Boyle#6500	0.9	Fabaceae	family	1	Lindl.	http://www.tropicos.org/Name/42000184	\N	\N	Fabaceae	1	\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	Fabaceae Lindl.	1

2013-05-21 19:23:15.278517-07	5	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	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	Poaceae Poa infirma Kunth	1

2013-05-21 19:23:15.278517-07	6	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	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	Poaceae Poa infirma Kunth	1

2013-05-21 19:23:15.278517-07	7	Poa annua subvar. minima	1	Poa annua subvar. minima	subvariety	1	(Schur) Asch. & Graebn.	http://www.tropicos.org/Name/50158097	\N	\N	\N	\N	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	Poaceae Poa annua subvar. minima (Schur) Asch. & Graebn.	1

2013-05-21 19:23:15.278517-07	8	Poa annua L.	1	Poa annua	species	1	L.	http://www.tropicos.org/Name/25509881	L.	1	\N	\N	Poa	1	annua	1	\N	\N	\N	\N	\N	\N	\N	\N	Accepted	Poa annua	L.	species	http://www.tropicos.org/Name/25509881	Poa annua	Poaceae	true	tropicos	 	\N	Poaceae Poa annua L.	1

2013-05-21 19:23:15.278517-07	9	Compositae indet. sp.1	0.9	Compositae	family	1	Giseke	http://www.tropicos.org/Name/50255940	\N	\N	Compositae	1	\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	Asteraceae Bercht. & J. Presl	1

2013-05-21 19:23:15.278517-07	10	Poa annua fo. lanuginosa	1	Poa annua fo. lanuginosa	forma	1	Sennen	http://www.tropicos.org/Name/50267771	\N	\N	\N	\N	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	Poaceae Poa annua var. annua	1

2013-05-21 19:23:15.278517-07	11	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	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	Caryophyllaceae Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire	1

2013-05-21 19:23:15.278517-07	12	Fabaceae Inga "fuzzy leaf"	0.9	Inga	genus	1	Mill.	http://www.tropicos.org/Name/40031040	\N	\N	Fabaceae	1	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	Fabaceae Inga Mill.	1

2013-05-21 19:23:15.278517-07	13	Fabaceae Inga sp.3	0.9	Inga	genus	1	Mill.	http://www.tropicos.org/Name/40031040	\N	\N	Fabaceae	1	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	Fabaceae Inga Mill.	1

2013-05-21 19:23:15.278517-07	14	Fabaceae unknown #2	0.9	Fabaceae	family	1	Lindl.	http://www.tropicos.org/Name/42000184	\N	\N	Fabaceae	1	\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	Fabaceae Lindl.	1

2013-05-21 19:23:19.089938-07	0	Poaceae Poa infirma Kunth	1	Poa infirma	species	1	Kunth	http://www.tropicos.org/Name/25514158	Kunth	1	Poaceae	1	Poa	1	infirma	1	\N	\N	\N	\N	\N	\N	\N	\N	Accepted	Poa infirma	Kunth	species	http://www.tropicos.org/Name/25514158	Poa infirma	Poaceae	true	tropicos	 	\N	Poaceae Poa infirma Kunth	1

2013-05-21 19:23:19.089938-07	1	Fabaceae Lindl.	0.5	Fabaceae	family	0.5	Lindl.	http://www.tropicos.org/Name/42000184	\N	\N	Fabaceae	1	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	Lindl.	Accepted	Fabaceae	Lindl.	family	http://www.tropicos.org/Name/42000184	\N	Fabaceae	true	tropicos	 [Partial match] 	\N	Fabaceae Lindl.	1

2013-05-21 19:23:19.089938-07	2	Poaceae Poa annua subvar. minima (Schur) Asch. & Graebn.	1	Poa annua subvar. minima	subvariety	1	(Schur) Asch. & Graebn.	http://www.tropicos.org/Name/50158097	(Schur) Asch. & Graebn.	1	Poaceae	1	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	Poaceae Poa annua subvar. minima (Schur) Asch. & Graebn.	1

2013-05-21 19:23:19.089938-07	3	Fabaceae Inga Mill.	1	Inga	genus	1	Mill.	http://www.tropicos.org/Name/40031040	Mill.	1	Fabaceae	1	Inga	1	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	Accepted	Inga	Mill.	genus	http://www.tropicos.org/Name/40031040	\N	Fabaceae	true	tropicos	 	\N	Fabaceae Inga Mill.	1

2013-05-21 19:23:19.089938-07	4	Caryophyllaceae Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire	1	Silene scouleri subsp. pringlei	subspecies	1	(S. Watson) C.L. Hitchc. & Maguire	http://www.tropicos.org/Name/6303627	(S. Watson) C.L. Hitchc. & Maguire	1	Caryophyllaceae	1	Silene	1	scouleri	1	subsp.	pringlei	1	\N	\N	\N	\N	\N	Accepted	Silene scouleri subsp. pringlei	(S. Watson) C.L. Hitchc. & Maguire	subspecies	http://www.tropicos.org/Name/6303627	Silene scouleri	Caryophyllaceae	true	tropicos	 	\N	Caryophyllaceae Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire	1

2013-05-21 19:23:19.089938-07	5	Poaceae Poa annua L.	1	Poa annua	species	1	L.	http://www.tropicos.org/Name/25509881	L.	1	Poaceae	1	Poa	1	annua	1	\N	\N	\N	\N	\N	\N	\N	\N	Accepted	Poa annua	L.	species	http://www.tropicos.org/Name/25509881	Poa annua	Poaceae	true	tropicos	 	\N	Poaceae Poa annua L.	1

2013-05-21 19:23:19.089938-07	6	Asteraceae Bercht. & J. Presl	0.4	Asteraceae	family	0.5	Bercht. & J. Presl	http://www.tropicos.org/Name/50307371	\N	\N	Asteraceae	1	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	Bercht. & J. Presl	Accepted	Asteraceae	Bercht. & J. Presl	family	http://www.tropicos.org/Name/50307371	\N	Asteraceae	true	tropicos	 [Partial match] 	\N	Asteraceae Bercht. & J. Presl	1

2013-05-21 19:23:19.089938-07	7	Poaceae Poa annua var. annua	1	Poa annua var. annua	variety	1	\N	http://www.tropicos.org/Name/25517736	\N	\N	Poaceae	1	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	Poaceae Poa annua var. annua	1

-- Name: tnrs_pkey; Type: CONSTRAINT; Schema: TNRS; Owner: bien; Tablespace: 

ALTER TABLE ONLY tnrs

    ADD CONSTRAINT tnrs_pkey PRIMARY KEY ("Name_submitted");

-- Name: tnrs_score_ok; Type: INDEX; Schema: TNRS; Owner: bien; Tablespace: 

CREATE UNIQUE INDEX tnrs_score_ok ON tnrs USING btree ("Name_submitted") WHERE score_ok("Max_score");

-- Name: tnrs_populate_derived_fields; Type: TRIGGER; Schema: TNRS; Owner: bien

CREATE TRIGGER tnrs_populate_derived_fields BEFORE INSERT OR UPDATE ON tnrs FOR EACH ROW EXECUTE PROCEDURE tnrs_populate_derived_fields();

SET standard_conforming_strings = on;

--

-- Name: SCHEMA "public.test_taxonomic_names"; Type: COMMENT; Schema: -; Owner: bien

--

COMMENT ON SCHEMA "public.test_taxonomic_names" IS 'Version: public (2013-5-21 19:23:27 PDT)';

-- Name: accesslevel; Type: TYPE; Schema: public.test_taxonomic_names; Owner: bien

--

CREATE TYPE accesslevel AS ENUM (

    'private',

    'metadata only',

    'data by request',

    'public'

);

ALTER TYPE "public.test_taxonomic_names".accesslevel OWNER TO bien;

--

-- Name: coordinatesource; Type: TYPE; Schema: public.test_taxonomic_names; Owner: bien

--

CREATE TYPE coordinatesource AS ENUM (

    'source data',

    'GPS',

    'georeferencing'

);

ALTER TYPE "public.test_taxonomic_names".coordinatesource OWNER TO bien;

--

-- Name: TYPE coordinatesource; Type: COMMENT; Schema: public.test_taxonomic_names; Owner: bien

--

COMMENT ON TYPE coordinatesource IS 'From <https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/VegCore_data_dictionary#coordinateSource>';

--

-- Name: higher_plant_group; Type: TYPE; Schema: public.test_taxonomic_names; Owner: bien

--

CREATE TYPE higher_plant_group AS ENUM (

    'all',

    'bryophytes',

    'ferns and allies',

    'flowering plants',

    'gymnosperms (conifers)',

    'gymnosperms (non-conifer)'

);

ALTER TYPE "public.test_taxonomic_names".higher_plant_group OWNER TO bien;

--

-- Name: observationtype; Type: TYPE; Schema: public.test_taxonomic_names; Owner: bien

--

CREATE TYPE observationtype AS ENUM (

    'plot',

    'specimen'

);

ALTER TYPE "public.test_taxonomic_names".observationtype OWNER TO bien;

--

    'advisor',

    'author',

    'co-principal investigator',

    'computer',

    'contact',

    'contributor',

    'custodian/steward',

    'editor',

    'originator',

    'performer',

    'principal investigator',

    'recipient',

    'reviewer',

    'technician'

-- Name: TYPE role; Type: COMMENT; Schema: public.test_taxonomic_names; Owner: bien

--

COMMENT ON TYPE role IS 'From <http://vegbank.org/vegbank/views/dba_fielddescription_detail.jsp?view=detail&wparam=1331&entity=dba_fielddescription&params=1331>, <http://vegbank.org/vegbank/views/dba_tabledescription_detail.jsp?view=detail&wparam=projectcontributor&entity=dba_tabledescription&where=where_tablename#ROLE_ID>, and misc terms.';

--

-- Name: sourcetype; Type: TYPE; Schema: public.test_taxonomic_names; Owner: bien

--

CREATE TYPE sourcetype AS ENUM (

    'herbarium',

    'primary database',

    'aggregator',

    'book',

    'article',

    'chapter',

    'conference proceedings',

    'dissertation',

    'edited book',

    'generic',

    'journal',

    'manuscript',

    'personal communication',

    'presentation',

    'report',

    'thesis',

    'website'

);

ALTER TYPE "public.test_taxonomic_names".sourcetype OWNER TO bien;

--

-- Name: TYPE sourcetype; Type: COMMENT; Schema: public.test_taxonomic_names; Owner: bien

--

COMMENT ON TYPE sourcetype IS 'From <https://projects.nceas.ucsb.edu/nceas/attachments/download/428/bien_web_datasource_schema.sql>, <http://vegbank.org/vegbank/views/dba_fielddescription_detail.jsp?view=detail&wparam=1300&entity=dba_fielddescription&params=1300>, <http://vegbiendev.nceas.ucsb.edu/phppgadmin/display.php?server=localhost%3A5432%3Aallow&database=vegbien&schema=VegBank&table=source&subject=table#sourcetype>';

--

-- Name: _km_to_m(double precision); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien

--

CREATE FUNCTION _km_to_m(value double precision) RETURNS double precision

    LANGUAGE sql IMMUTABLE STRICT

    AS $_$

SELECT $1*1000.

$_$;

ALTER FUNCTION "public.test_taxonomic_names"._km_to_m(value double precision) OWNER TO bien;

--

-- Name: _locationnarrative_is_cultivated(text); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien

--

CREATE FUNCTION _locationnarrative_is_cultivated(locationnarrative text) RETURNS boolean

    LANGUAGE sql IMMUTABLE STRICT

    AS $_$

SELECT bool_or(value)

FROM

(VALUES

      (NULLIF($1 LIKE '%cultivated%', false))

    , (NULLIF($1 LIKE '%cultivad%', false))

    , (NULLIF($1 LIKE '%ornamental%', false))

    , (NULLIF($1 LIKE '%garden%', false))

    , (NULLIF($1 LIKE '%jardin%', false))

    , (NULLIF($1 LIKE '%jard?n%', false))

    , (NULLIF($1 LIKE '%jardim%', false))

    , (NULLIF($1 LIKE '%plantation%', false))

    , (NULLIF($1 LIKE '%plantacion%', false))

    , (NULLIF($1 LIKE '%plantaci?n%', false))

    , (NULLIF($1 LIKE '%universit%', false))

    , (NULLIF($1 LIKE '%universidad%', false))

    , (NULLIF($1 LIKE '%campus%' , false))

    , (NULLIF($1 LIKE '%urban%', false))

    , (NULLIF($1 LIKE '%greenhouse%', false))

    , (NULLIF($1 LIKE '%arboretum%', false))

    , (NULLIF($1 LIKE '%farm%', false))

    , (NULLIF($1 LIKE '%weed%', false))

    , (NULLIF($1 LIKE '%corn field%', false))

)

AS v (value)

$_$;

ALTER FUNCTION "public.test_taxonomic_names"._locationnarrative_is_cultivated(locationnarrative text) OWNER TO bien;

--

-- Name: _m_to_km(double precision); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien

--

CREATE FUNCTION _m_to_km(value double precision) RETURNS double precision

    LANGUAGE sql IMMUTABLE STRICT

    AS $_$

SELECT $1/1000.

$_$;

ALTER FUNCTION "public.test_taxonomic_names"._m_to_km(value double precision) OWNER TO bien;

--

-- Name: _mm_to_m(double precision); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien

--

CREATE FUNCTION _mm_to_m(value double precision) RETURNS double precision

    LANGUAGE sql IMMUTABLE STRICT

    AS $_$

SELECT $1/1000.

$_$;

ALTER FUNCTION "public.test_taxonomic_names"._mm_to_m(value double precision) OWNER TO bien;

--

-- Name: _or(boolean, boolean, boolean, boolean, boolean); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien

--

CREATE FUNCTION _or("0" boolean DEFAULT NULL::boolean, "1" boolean DEFAULT NULL::boolean, "2" boolean DEFAULT NULL::boolean, "3" boolean DEFAULT NULL::boolean, "4" boolean DEFAULT NULL::boolean) RETURNS boolean

    LANGUAGE sql IMMUTABLE

    AS $_$

SELECT bool_or(value)

FROM

(VALUES

      ($1)

    , ($2)

    , ($3)

    , ($4)

    , ($5)

)

AS v (value)

$_$;

ALTER FUNCTION "public.test_taxonomic_names"._or("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean) OWNER TO bien;

--

-- Name: FUNCTION _or("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean); Type: COMMENT; Schema: public.test_taxonomic_names; Owner: bien

--

COMMENT ON FUNCTION _or("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean) IS '_or() ignores NULL values, while OR combines them with the other values to potentially convert false to NULL. OR should be used with required fields, and _or() with optional fields.';

--

SELECT (CASE WHEN

    lower($1) LIKE '%aceae'

    OR lower($1) IN (

        'compositae',

        'cruciferae',

        'gramineae',

        'labiatae',

        'leguminosae',

        'palmae',

        'umbelliferae'

    )

THEN $1 ELSE NULL END)

-- Name: delete_scrubbed_taxondeterminations(text); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien

CREATE FUNCTION delete_scrubbed_taxondeterminations(datasource text) RETURNS void

    AS $_$

DELETE FROM taxondetermination

USING taxonoccurrence

WHERE

    taxonoccurrence.source_id = source_by_shortname($1)

AND taxondetermination.taxonoccurrence_id = taxonoccurrence.taxonoccurrence_id

AND taxondetermination.source_id = source_by_shortname('TNRS')

$_$;

ALTER FUNCTION "public.test_taxonomic_names".delete_scrubbed_taxondeterminations(datasource text) OWNER TO bien;

--

-- Name: make_family_higher_plant_group(); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien

--

CREATE FUNCTION make_family_higher_plant_group() RETURNS void

    LANGUAGE plpgsql

DECLARE

    source_id_ integer :=

        (SELECT source_id FROM source WHERE shortname = 'NCBI');

    row_ higher_plant_group_nodes%ROWTYPE;

BEGIN

    TRUNCATE family_higher_plant_group;

    FOR row_ IN

        SELECT * FROM higher_plant_group_nodes

        ORDER BY higher_plant_group, node_name

    LOOP

        DECLARE

            higher_plant_group_id integer := (

                SELECT taxonlabel_id FROM taxonlabel

                WHERE source_id = source_id_

                AND taxonomicname = row_.node_name

            );

            family text;

        BEGIN

            FOR family IN

                SELECT taxonepithet

                FROM taxonlabel_relationship

                JOIN taxonlabel ON taxonlabel_id = descendant_id

                WHERE ancestor_id = higher_plant_group_id

                AND rank = 'family'

                AND source_id = source_id_

                AND taxonepithet IS NOT NULL

            LOOP

                BEGIN

                    INSERT INTO family_higher_plant_group

                    VALUES (family, row_.higher_plant_group)

                    ;

                EXCEPTION

                    WHEN unique_violation THEN NULL;

                END;

            END LOOP;

        END;

    END LOOP;

END;

ALTER FUNCTION "public.test_taxonomic_names".make_family_higher_plant_group() OWNER TO bien;

-- Name: party_source_id_self_ref(); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien

CREATE FUNCTION party_source_id_self_ref() RETURNS trigger

    IF new.source_id = 0 THEN -- make self-source

        new.source_id = new.party_id;

ALTER FUNCTION "public.test_taxonomic_names".party_source_id_self_ref() OWNER TO bien;

    IF new.matched_place_id = 0 THEN -- make self-source

SET default_tablespace = '';

SET default_with_oids = false;

-- Name: place; Type: TABLE; Schema: public.test_taxonomic_names; Owner: bien; Tablespace: 

--

CREATE TABLE place (

    place_id integer NOT NULL,

    source_id integer NOT NULL,

    canon_place_id integer,

    matched_place_id integer,

    coordinates_id integer,

    placename_id integer,

    continent text,

    country text,

    stateprovince text,

    county text,

    description text,

    geovalid boolean DEFAULT false NOT NULL,

    distance_to_country_m double precision,

    distance_to_state_m double precision,

    distance_to_county_m double precision,

    CONSTRAINT place_required_key CHECK ((((((coordinates_id IS NOT NULL) OR (continent IS NOT NULL)) OR (country IS NOT NULL)) OR (stateprovince IS NOT NULL)) OR (county IS NOT NULL)))

);

ALTER TABLE "public.test_taxonomic_names".place OWNER TO bien;

--

-- Name: TABLE place; Type: COMMENT; Schema: public.test_taxonomic_names; Owner: bien

--

COMMENT ON TABLE place IS 'The full path to a place. Can be either verbatim or accepted. For accepted names, points to the identified place.

To use a custom hierarchy of placenames with no explicit column, point to the lowest-rank placename in placename_id.';

--

-- Name: COLUMN place.matched_place_id; Type: COMMENT; Schema: public.test_taxonomic_names; Owner: bien

--

COMMENT ON COLUMN place.matched_place_id IS 'The closest match to this place. Places should be linked in a three-level hierarchy of datasource place -> verbatim place -> accepted place.

An accepted place should point to itself in this field. This will happen automatically by setting it to the special value 0.';

--

-- Name: place_set_canon_place_id(place, integer, boolean); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien

--

CREATE FUNCTION place_set_canon_place_id(new place, old_matched_place_id integer DEFAULT NULL::integer, is_update boolean DEFAULT false) RETURNS place

    LANGUAGE plpgsql

    AS $$

BEGIN

    IF NOT is_update

        OR new.matched_place_id IS DISTINCT FROM old_matched_place_id THEN

        IF new.matched_place_id IS NOT NULL THEN

            IF new.matched_place_id = new.place_id THEN -- self-source

                new.canon_place_id := new.place_id; -- make self-source

            ELSE -- propagate from matched place

                new.canon_place_id := (

                    SELECT canon_place_id

                    FROM place

                    WHERE place_id = new.matched_place_id

                );

            END IF;

            IF is_update THEN

                -- Update canon_place_id on places that resolve to this place

                UPDATE place

                SET canon_place_id = new.canon_place_id

                WHERE matched_place_id = new.place_id

                AND place_id != new.place_id -- no infinite recursion

                ;

            END IF;

        ELSE -- no matched place

            new.canon_place_id := new.place_id; -- make self-source

        END IF;

    END IF;

    RETURN new;

END;

$$;

ALTER FUNCTION "public.test_taxonomic_names".place_set_canon_place_id(new place, old_matched_place_id integer, is_update boolean) OWNER TO bien;

--

-- Name: place_set_canon_place_id_on_insert(); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien

--

CREATE FUNCTION place_set_canon_place_id_on_insert() RETURNS trigger

    LANGUAGE plpgsql

    AS $$

BEGIN

    new := place_set_canon_place_id(new);

    RETURN new;

END;

$$;

ALTER FUNCTION "public.test_taxonomic_names".place_set_canon_place_id_on_insert() OWNER TO bien;

--

-- Name: place_set_canon_place_id_on_update(); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien

--

CREATE FUNCTION place_set_canon_place_id_on_update() RETURNS trigger

    LANGUAGE plpgsql

    AS $$

BEGIN

    new := place_set_canon_place_id(new, old.matched_place_id, true);

    RETURN new;

END;

$$;

ALTER FUNCTION "public.test_taxonomic_names".place_set_canon_place_id_on_update() OWNER TO bien;

--

-- Name: source_by_shortname(text); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien

CREATE FUNCTION source_by_shortname(shortname text) RETURNS integer

    LANGUAGE sql STABLE STRICT

    AS $_$

SELECT source_id FROM source WHERE shortname = $1 LIMIT 1

$_$;

ALTER FUNCTION "public.test_taxonomic_names".source_by_shortname(shortname text) OWNER TO bien;

--

-- Name: sourcename_set_matched_source_id(); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien

--

CREATE FUNCTION sourcename_set_matched_source_id() RETURNS trigger

    LANGUAGE plpgsql

BEGIN

    IF new.matched_source_id IS NULL THEN -- populate matched_source_id

        new.matched_source_id :=

            (SELECT source_id FROM source WHERE shortname = new.name);

    END IF;

    RETURN new;

END;

ALTER FUNCTION "public.test_taxonomic_names".sourcename_set_matched_source_id() OWNER TO bien;

    LANGUAGE plpgsql

DECLARE

    analytical_plot_def text := NULL;

    analytical_specimen_def text := NULL;

    provider_count_view_def text := NULL;

BEGIN

    -- Save and drop dependent objects

    BEGIN

        analytical_plot_def :=

            'CREATE OR REPLACE VIEW analytical_plot AS '

            ||pg_get_viewdef('analytical_plot');

        DROP VIEW analytical_plot;

    EXCEPTION

        WHEN undefined_table THEN NULL;

    END;

    BEGIN

        analytical_specimen_def :=

            'CREATE OR REPLACE VIEW analytical_specimen AS '

            ||pg_get_viewdef('analytical_specimen');

        DROP VIEW analytical_specimen;

    EXCEPTION

        WHEN undefined_table THEN NULL;

    END;

    BEGIN

        provider_count_view_def :=

            'CREATE OR REPLACE VIEW provider_count_view AS '

            ||pg_get_viewdef('provider_count_view');

        DROP VIEW provider_count_view;

    EXCEPTION

        WHEN undefined_table THEN NULL;

    END;

    DROP TABLE IF EXISTS analytical_stem;

    CREATE TABLE analytical_stem AS SELECT * FROM analytical_stem_view LIMIT 0;

    GRANT SELECT ON TABLE analytical_stem TO bien_read;

    GRANT SELECT ON TABLE analytical_stem_view TO bien_read;

    ALTER TABLE analytical_stem ADD FOREIGN KEY (datasource) REFERENCES source (shortname) ON UPDATE CASCADE ON DELETE CASCADE;

    ALTER TABLE analytical_stem ALTER COLUMN "datasource" SET NOT NULL;

    ALTER TABLE analytical_stem ALTER COLUMN "locationID" SET NOT NULL;

    CREATE INDEX ON analytical_stem ("datasource", "occurrenceID");

    CREATE INDEX ON analytical_stem ("datasource", "institutionCode", "collectionCode", "catalogNumber");

    CREATE INDEX ON analytical_stem ("datasource", "projectID", "locationID", "dateCollected");

    -- Re-create dependent objects

    IF analytical_plot_def IS NOT NULL THEN

        EXECUTE analytical_plot_def;

        GRANT SELECT ON TABLE analytical_plot TO bien_read;

    END IF;

    IF analytical_specimen_def IS NOT NULL THEN

        EXECUTE analytical_specimen_def;

        GRANT SELECT ON TABLE analytical_specimen TO bien_read;

    END IF;

    IF provider_count_view_def IS NOT NULL THEN

        EXECUTE provider_count_view_def;

        GRANT SELECT ON TABLE provider_count_view TO bien_read;

        GRANT SELECT ON TABLE provider_count_view TO public_;

    END IF;

END;

-- Name: sync_geoscrub_input_to_view(); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien

CREATE FUNCTION sync_geoscrub_input_to_view() RETURNS void

    LANGUAGE sql

    AS $$

DROP TABLE IF EXISTS geoscrub_input;

CREATE TABLE geoscrub_input AS SELECT * FROM geoscrub_input_view;

GRANT SELECT ON TABLE geoscrub_input TO bien_read;

GRANT SELECT ON TABLE geoscrub_input_view TO bien_read;

$$;

ALTER FUNCTION "public.test_taxonomic_names".sync_geoscrub_input_to_view() OWNER TO bien;

--

-- Name: sync_provider_count_to_view(); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien

--

CREATE FUNCTION sync_provider_count_to_view() RETURNS void

    LANGUAGE sql

    AS $$

DROP TABLE IF EXISTS provider_count;

CREATE TABLE provider_count AS SELECT * FROM provider_count_view LIMIT 0;

GRANT SELECT ON TABLE provider_count TO bien_read;

GRANT SELECT ON TABLE provider_count TO public_;

GRANT SELECT ON TABLE provider_count_view TO bien_read;

GRANT SELECT ON TABLE provider_count_view TO public_;

ALTER TABLE provider_count ADD PRIMARY KEY (dataprovider, dataset);

$$;

ALTER FUNCTION "public.test_taxonomic_names".sync_provider_count_to_view() OWNER TO bien;

--

-- Name: sync_taxon_trait_to_view(); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien

--

CREATE FUNCTION sync_taxon_trait_to_view() RETURNS void

    LANGUAGE sql

    AS $$

DROP TABLE IF EXISTS taxon_trait;

CREATE TABLE taxon_trait AS SELECT * FROM taxon_trait_view LIMIT 0;

GRANT SELECT ON TABLE taxon_trait TO bien_read;

GRANT SELECT ON TABLE taxon_trait_view TO bien_read;

ALTER TABLE taxon_trait ALTER COLUMN "scientificName" SET NOT NULL;

ALTER TABLE taxon_trait ALTER COLUMN "measurementType" SET NOT NULL;

CREATE INDEX ON taxon_trait ("scientificName", "measurementType" );

$$;

ALTER FUNCTION "public.test_taxonomic_names".sync_taxon_trait_to_view() OWNER TO bien;

--

-- Name: taxondetermination_set_iscurrent(integer); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien

--

CREATE FUNCTION taxondetermination_set_iscurrent(taxonoccurrence_id_ integer) RETURNS void

    UPDATE taxondetermination SET iscurrent = false

    WHERE taxonoccurrence_id = taxonoccurrence_id_ AND iscurrent;

    UPDATE taxondetermination SET iscurrent = true

    WHERE taxondetermination_id = (

        SELECT taxondetermination_id FROM taxondetermination

        WHERE taxonoccurrence_id = taxonoccurrence_id_

        ORDER BY

            -- Scrubbed determinations must come first for scrub.make to work

              COALESCE(determinationtype = 'accepted', false) DESC/*true first*/

            , COALESCE(determinationtype = 'matched', false) DESC/*true first*/

            , isoriginal/*false first*/

            , determinationdate DESC NULLS LAST

        LIMIT 1

    );

    UPDATE taxondetermination SET is_datasource_current = false

    WHERE taxonoccurrence_id = taxonoccurrence_id_ AND is_datasource_current;

    UPDATE taxondetermination SET is_datasource_current = true

    WHERE taxondetermination_id = (

        SELECT taxondetermination_id FROM taxondetermination

        WHERE taxonoccurrence_id = taxonoccurrence_id_

        AND determinationtype IS NULL

        ORDER BY

            isoriginal/*false first*/

            , determinationdate DESC NULLS LAST

        LIMIT 1

    );

END;

$$;

ALTER FUNCTION "public.test_taxonomic_names".taxondetermination_set_iscurrent(taxonoccurrence_id_ integer) OWNER TO bien;

--

-- Name: taxondetermination_set_iscurrent_on_delete(); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien

--

CREATE FUNCTION taxondetermination_set_iscurrent_on_delete() RETURNS trigger

    LANGUAGE plpgsql

    AS $$

BEGIN

    BEGIN

        PERFORM taxondetermination_set_iscurrent(old.taxonoccurrence_id);

    EXCEPTION

        WHEN foreign_key_violation THEN NULL; -- cascading delete

    END;

    RETURN old;

END;

$$;

ALTER FUNCTION "public.test_taxonomic_names".taxondetermination_set_iscurrent_on_delete() OWNER TO bien;

--

-- Name: taxondetermination_set_iscurrent_on_insert(); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien

--

CREATE FUNCTION taxondetermination_set_iscurrent_on_insert() RETURNS trigger

    LANGUAGE plpgsql

    AS $$

BEGIN

    PERFORM taxondetermination_set_iscurrent(new.taxonoccurrence_id);

ALTER FUNCTION "public.test_taxonomic_names".taxondetermination_set_iscurrent_on_insert() OWNER TO bien;

-- Name: taxonlabel_0_matched_label_id_self_ref(); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien

CREATE FUNCTION taxonlabel_0_matched_label_id_self_ref() RETURNS trigger

    IF new.taxonlabel_id IS NULL THEN -- prepopulate taxonlabel_id

        new.taxonlabel_id = nextval('taxonlabel_taxonlabel_id_seq'::regclass);

    IF new.matched_label_id = 0 THEN -- make self-source

        new.matched_label_id = new.taxonlabel_id;

    END IF;

ALTER FUNCTION "public.test_taxonomic_names".taxonlabel_0_matched_label_id_self_ref() OWNER TO bien;

    source_id integer NOT NULL,

-- Name: COLUMN taxonlabel.source_id; Type: COMMENT; Schema: public.test_taxonomic_names; Owner: bien

COMMENT ON COLUMN taxonlabel.source_id IS 'The entity that created the taxon label.';

COMMENT ON COLUMN taxonlabel.taxonomicname IS 'The concatenated taxonomic name which uniquely identifies this taxon, including the family and author of that name. Must be unique within the datasource.

    IF NOT is_update

        OR new.matched_label_id IS DISTINCT FROM old_matched_label_id THEN

            IF new.matched_label_id = new.taxonlabel_id THEN -- self-source

                new.canon_label_id := new.taxonlabel_id; -- make self-source

            new.canon_label_id := new.taxonlabel_id; -- make self-source

    street text,

    street2 text,

    stateprovince text,

    source_id integer NOT NULL,

    datasource text NOT NULL,

    "collectionCode" text,

    "catalogNumber" text,

    "occurrenceID" text,

    locality text,

    "coordinateUncertaintyInMeters" double precision,

    "coordinateSource_bien" coordinatesource,

    "georeferenceProtocol_bien" text,

    geovalid_bien integer,

    "isNewWorld_bien" integer,

    "projectID" text,

    "locationID" text NOT NULL,

    "locationName" text,

    subplot text,

    "individualCode" text,

    "temperature_C" double precision,

    precipitation_m double precision,

    "recordedBy" text,

    "recordNumber" text,

    family_verbatim text,

    "scientificName_verbatim" text,

    "identifiedBy" text,

    "dateIdentified" date,

    "identificationRemarks" text,

    family_matched text,

    "taxonName_matched" text,

    "scientificNameAuthorship_matched" text,

    "higherPlantGroup_bien" higher_plant_group,

    "speciesBinomialWithMorphospecies" text,

    "taxonName" text,

    "reproductiveCondition" text,

    threatened_bien integer,

    cultivated_bien integer,

    "cultivatedBasis_bien" text,

    "occurrenceRemarks" text,

    "taxonOccurrenceID" text,

    "authorTaxonCode" text,

    "individualObservationID" text,

    "authorStemCode" text

-- Name: analytical_plot; Type: VIEW; Schema: public.test_taxonomic_names; Owner: bien

CREATE VIEW analytical_plot AS

    SELECT analytical_stem.datasource, analytical_stem.country, analytical_stem."stateProvince", analytical_stem.county, analytical_stem.locality, analytical_stem."decimalLatitude", analytical_stem."decimalLongitude", analytical_stem."coordinateUncertaintyInMeters", analytical_stem."coordinateSource_bien", analytical_stem."georeferenceProtocol_bien", analytical_stem.geovalid_bien, analytical_stem."isNewWorld_bien", analytical_stem."projectID", analytical_stem."locationID", analytical_stem."locationName", analytical_stem.subplot, analytical_stem."individualCode", analytical_stem."elevationInMeters", analytical_stem."plotArea_ha", analytical_stem."samplingProtocol", analytical_stem."recordedBy", analytical_stem."recordNumber", analytical_stem."dateCollected", analytical_stem.family_verbatim, analytical_stem."scientificName_verbatim", analytical_stem."identifiedBy", analytical_stem."dateIdentified", analytical_stem."identificationRemarks", analytical_stem.family_matched, analytical_stem."taxonName_matched", analytical_stem."scientificNameAuthorship_matched", analytical_stem."higherPlantGroup_bien", analytical_stem.family, analytical_stem.genus, analytical_stem."speciesBinomialWithMorphospecies", analytical_stem."taxonName", analytical_stem."scientificNameAuthorship", analytical_stem."growthForm", analytical_stem."reproductiveCondition", analytical_stem.threatened_bien, analytical_stem.cultivated_bien, analytical_stem."cultivatedBasis_bien", analytical_stem."occurrenceRemarks", analytical_stem."coverPercent", analytical_stem."diameterBreastHeight_cm", analytical_stem.height_m, analytical_stem.tag, analytical_stem."organismX_m", analytical_stem."organismY_m", analytical_stem."taxonOccurrenceID", analytical_stem."authorTaxonCode", analytical_stem."individualObservationID", analytical_stem."authorStemCode", analytical_stem."institutionCode", analytical_stem."collectionCode", analytical_stem."catalogNumber", analytical_stem."occurrenceID" FROM analytical_stem;

ALTER TABLE "public.test_taxonomic_names".analytical_plot OWNER TO bien;

-- Name: analytical_specimen; Type: VIEW; Schema: public.test_taxonomic_names; Owner: bien

--

CREATE VIEW analytical_specimen AS

    SELECT analytical_stem.datasource, analytical_stem."institutionCode", analytical_stem."collectionCode", analytical_stem."catalogNumber", analytical_stem."occurrenceID", analytical_stem.country, analytical_stem."stateProvince", analytical_stem.county, analytical_stem.locality, analytical_stem."decimalLatitude", analytical_stem."decimalLongitude", analytical_stem."coordinateUncertaintyInMeters", analytical_stem."coordinateSource_bien", analytical_stem."georeferenceProtocol_bien", analytical_stem.geovalid_bien, analytical_stem."isNewWorld_bien", analytical_stem."elevationInMeters", analytical_stem."recordedBy", analytical_stem."recordNumber", analytical_stem."dateCollected", analytical_stem.family_verbatim, analytical_stem."scientificName_verbatim", analytical_stem."identifiedBy", analytical_stem."dateIdentified", analytical_stem."identificationRemarks", analytical_stem.family_matched, analytical_stem."taxonName_matched", analytical_stem."scientificNameAuthorship_matched", analytical_stem."higherPlantGroup_bien", analytical_stem.family, analytical_stem.genus, analytical_stem."taxonName", analytical_stem."scientificNameAuthorship", analytical_stem."reproductiveCondition", analytical_stem.cultivated_bien, analytical_stem."cultivatedBasis_bien", analytical_stem."occurrenceRemarks" FROM analytical_stem;

ALTER TABLE "public.test_taxonomic_names".analytical_specimen OWNER TO bien;

--

    source_id integer NOT NULL,

    coordsaccuracy_m double precision

-- Name: COLUMN coordinates.coordsaccuracy_m; Type: COMMENT; Schema: public.test_taxonomic_names; Owner: bien

COMMENT ON COLUMN coordinates.coordsaccuracy_m IS 'Accuracy of latitude/longitude or footprint geometry, in meters. This should generally be at least 1 m, which is the accuracy of the best GPSes.';

-- Name: cultivated_family_locations; Type: TABLE; Schema: public.test_taxonomic_names; Owner: bien; Tablespace: 

--