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

--

CREATE TABLE stemobservation (

    stemobservation_id int(11) NOT NULL,

    creator_id int(11) NOT NULL,

    sourceaccessioncode text,

    plantobservation_id int(11) NOT NULL,

    authorstemcode text,

    tag text,

    tags text,

    xposition_m double precision,

    yposition_m double precision,

    diameterbreastheight_m double precision,

    basaldiameter_m double precision,

    diameteraccuracy_m double precision,

    height_m double precision,

    heightfirstbranch_m double precision,

    heightaccuracy_m double precision,

    health text,

    age double precision,

    accessioncode text

);

--

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

--

--

-- Name: COLUMN stemobservation.tags; 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`, stemobservation.xposition_m, stemobservation.yposition_m FROM ((((((((((((((((party datasource JOIN location ON ((location.creator_id = datasource.party_id))) 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 taxonverbatim USING (taxonverbatim_id)) JOIN taxonlabel datasource_taxonlabel USING (taxonlabel_id)) JOIN taxonlabel accepted_taxonlabel ON ((accepted_taxonlabel.taxonlabel_id = datasource_taxonlabel.canon_label_id))) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN stemobservation USING (plantobservation_id)) LEFT JOIN specimenreplicate USING (plantobservation_id)) WHERE (datasource.organizationname IS NOT NULL);

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

--

CREATE TABLE stemobservation (

    stemobservation_id integer NOT NULL,

    creator_id integer NOT NULL,

    sourceaccessioncode text,

    plantobservation_id integer NOT NULL,

    authorstemcode text,

    tag text,

    tags text,

    xposition_m double precision,

    yposition_m double precision,

    diameterbreastheight_m double precision,

    basaldiameter_m double precision,

    diameteraccuracy_m double precision,

    height_m double precision,

    heightfirstbranch_m double precision,

    heightaccuracy_m double precision,

    health text,

    age double precision,

    accessioncode text

);

--

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

--

COMMENT ON TABLE stemobservation IS 'Equivalent to VegBank''s stemlocation table.';

--

-- Name: COLUMN stemobservation.tags; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN stemobservation.tags IS 'Stores all tags when multiple tags provided. Older tags go first.';

--

    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", stemobservation.xposition_m, stemobservation.yposition_m FROM ((((((((((((((((party datasource JOIN location ON ((location.creator_id = datasource.party_id))) 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 taxonverbatim USING (taxonverbatim_id)) JOIN taxonlabel datasource_taxonlabel USING (taxonlabel_id)) JOIN taxonlabel accepted_taxonlabel ON ((accepted_taxonlabel.taxonlabel_id = datasource_taxonlabel.canon_label_id))) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN stemobservation USING (plantobservation_id)) LEFT JOIN specimenreplicate USING (plantobservation_id)) WHERE (datasource.organizationname IS NOT NULL);