-- Name: taxonverbatim; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE taxonverbatim (

    taxonlabel_id integer NOT NULL,

    verbatimrank text,

    taxonomicname text,

    binomial text,

    author text,

    family text,

    genus text,

    species text,

    morphospecies text,

    description text

);

--

-- Name: TABLE taxonverbatim; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON TABLE taxonverbatim IS 'Component parts of the taxonlabel. Contains the datasource''s original taxonomic name components, as well as any parsed components produced by name resolution.';

--

-- Name: COLUMN taxonverbatim.verbatimrank; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN taxonverbatim.verbatimrank IS 'The taxonlabel''s verbatim level in the taxonomic hierarchy. Does not need to be in the taxonrank closed list.';

--

-- Name: COLUMN taxonverbatim.taxonomicname; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN taxonverbatim.taxonomicname IS 'The concatenated taxonomic name which uniquely identifies this taxon, including the author of that name.

Equivalent to Darwin Core''s scientificName.';

--

-- Name: COLUMN taxonverbatim.binomial; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN taxonverbatim.binomial IS 'The taxonomic name without the author.';

--

-- Name: COLUMN taxonverbatim.author; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN taxonverbatim.author IS 'The author of the taxonomic name.

Equivalent to Darwin Core''s scientificNameAuthorship.';

--

-- Name: COLUMN taxonverbatim.family; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN taxonverbatim.family IS 'The family of the taxonomic name. This is a cached field for easy querying; the identifying version of this field is stored in the chain of parent_id ancestors.';

--

-- Name: COLUMN taxonverbatim.genus; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN taxonverbatim.genus IS 'The genus portion of the taxonomic name. This is a cached field for easy querying; the identifying version of this field is stored in the chain of parent_id ancestors.';

--

-- Name: COLUMN taxonverbatim.species; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN taxonverbatim.species IS 'The species portion of the taxonomic name. This is a cached field for easy querying; the identifying version of this field is stored in the chain of parent_id ancestors.';

--

    SELECT datasource.organizationname AS "dataSourceName", taxonverbatim.family, taxonverbatim.genus, taxonverbatim.species, COALESCE(taxonverbatim.taxonomicname, taxonverbatim.binomial) AS taxon, taxonverbatim.author AS "taxonAuthor", taxonverbatim.morphospecies AS "taxonMorphospecies", placepath.country, placepath.stateprovince AS "stateProvince", placepath.county AS "countyParish", taxonoccurrence.verbatimcollectorname AS collector, plantobservation.collectionnumber AS "collectionNumber", array_to_string(ARRAY[identifiedby.givenname, identifiedby.middlename, identifiedby.surname], ' '::text) AS "identifiedBy", aggregateoccurrence.collectiondate AS "observationDate", location.authorlocationcode AS "plotCode", functions._m2_to_ha(location.area_m2) AS "plotAreaHa", method.name AS "plotMethod", locationcoords.latitude_deg AS latitude, locationcoords.longitude_deg AS longitude, location.elevation_m, taxonoccurrence.iscultivated AS "isCultivated", taxonoccurrence.cultivatedbasis AS "isCultivatedReason", _fraction_to_percent(aggregateoccurrence.cover_fraction) AS "pctCover" FROM (((((((((((((((location JOIN party datasource ON (((datasource.party_id = location.creator_id) AND (datasource.organizationname IS NOT NULL)))) LEFT JOIN locationcoords USING (location_id)) LEFT JOIN locationplace USING (location_id)) LEFT JOIN placepath USING (placepath_id)) JOIN locationevent USING (location_id)) LEFT JOIN method USING (method_id)) JOIN taxonoccurrence USING (locationevent_id)) JOIN taxondetermination USING (taxonoccurrence_id)) LEFT JOIN party identifiedby ON ((identifiedby.party_id = taxondetermination.party_id))) JOIN taxonlabel datasource_taxonlabel USING (taxonlabel_id)) JOIN taxonlabel accepted_taxonlabel ON ((accepted_taxonlabel.taxonlabel_id = datasource_taxonlabel.canon_label_id))) JOIN taxonverbatim ON ((taxonverbatim.taxonlabel_id = accepted_taxonlabel.taxonlabel_id))) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN specimenreplicate USING (plantobservation_id));

-- Name: taxonverbatim_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 

--

ALTER TABLE ONLY taxonverbatim

    ADD CONSTRAINT taxonverbatim_pkey PRIMARY KEY (taxonlabel_id);

--

-- Name: taxonverbatim_taxonlabel_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

--

ALTER TABLE ONLY taxonverbatim

    ADD CONSTRAINT taxonverbatim_taxonlabel_id_fkey FOREIGN KEY (taxonlabel_id) REFERENCES taxonlabel(taxonlabel_id) ON UPDATE CASCADE ON DELETE CASCADE;

--