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

--

CREATE TABLE taxonverbatim (

    taxonlabel_id int(11) 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: -

--

--

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

--

--

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

--

--

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

--

--

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

--

--

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

--

--

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

--

--

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

--

--

    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], CAST(' ' AS 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 taxonverbatim

    ADD CONSTRAINT taxonverbatim_pkey PRIMARY KEY (taxonlabel_id);

--

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

--

ALTER TABLE taxonverbatim

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

--