<stemCount>

                <stemCount>1</stemCount>

                <individualplant>

                </individualplant>

                <stemDiameter>662</stemDiameter>

            </stemCount>

            <stemCount>

                <stemCount>1</stemCount>

                <individualplant>

                </individualplant>

                <stemDiameter>215</stemDiameter>

            </stemCount>

            <stemCount>

                <stemCount>1</stemCount>

            </stemCount>

            <stemCount>

                <stemCount>1</stemCount>

            </stemCount>

            <stemCount>

                <individualplant>

                                <tableName>individualplant</tableName>

                                <tableName>individualplant</tableName>

                </individualplant>

                <stemCount>1</stemCount>

            </stemCount>

            <stemCount>

                <stemCount>1</stemCount>

            </stemCount>

NoInd,/collectiveobservation/*/stemCount/_alt/1,"Brad: Incorrect for VegX. This is a count of number of indiiduals for an *aggregate* observation. For VegBank, I'm not sure. Not exactly the same as stemCount. An individual tree could have 3 stems but would still only count as 1. We need to check with Bob on this."

ht_first_branch_m,"/collectiveobservation/stemCount/individualplant/definedValue[*_ID/userDefined[tableName=individualplant,userDefinedName=heightFirstBranchM]:[userDefinedType=varchar]]:[@fkey=tableRecord_ID]/definedValue",Brad: Incorrect for VegBank. This is a measurement applied to a single tree. Check with Bob

tag2,/collectiveobservation/stemCount/individualplant/stemCode/_alt/1,"Brad: See commend for tag1. Your mapping for tag2 looks correct. Probably both values would go here, only nested, with one superceding the other."

tag1,/collectiveobservation/stemCount/individualplant/stemCode/_alt/2,"Brad: Another type of code, typically a number, used by the original data provider to indicate an individual tree. These are numbers on physical tags attached to the tree. Tag2 Is the same thing, only used if the first tag was lost. Obviously not a good system as it's possible a tree tag could be lost and changed more than once."

x_position,/collectiveobservation/stemCount/individualplant/stemXPosition,"Brad: Correct for VegBank. I'm not so sure for VegX. Let's ask Nick about this. These are important, fundamental values of many tree plots, and should be accommodated within VegX."

y_position,/collectiveobservation/stemCount/individualplant/stemYPosition,Brad: See comment above for x_position

temp_dbh,/collectiveobservation/stemCount/stemDiameter,

height_m,/collectiveobservation/stemCount/stemHeight,Brad: Incorrect for VegBank. This is a measurement applied to a single tree. Check with Bob

PlotObsID,":[collectiveobservation/*/stemCount/_alt/2=1]/{taxondetermination[ROLE_ID/aux_Role/roleCode=collector]:[interpretationType=Author,originalInterpretation=true,currentInterpretation=false,interpretationDate=epoch,*_ID/plantConcept:[*_ID/reference],*_ID/party]/museumAccessionNumber,*_ID/plotevent/*_ID/plot:[confidentialityStatus=0]/authorPlotCode/_alt/3}","Brad: Neither is correct; this is just an internal ID for table plotObservations. However, it has the important property of uniquely identifying an ""observation"", which is an individual tree, in the case of an individual observation, or a records of a species with an associated count of individuals or measurement of percent cover, in the case of aggregate observations. Not sure where to store this. Main point is that it is not part of the original data, but an auto_increment added later."

TRUNCATE stemcount CASCADE;

    individualplant_id integer NOT NULL

    covercode character varying(10)

    stemcount_id integer NOT NULL,

    stemcode character varying(20),

    stemxposition double precision,

    stemyposition double precision,

    stemhealth character varying(50),

    emb_stemlocation integer

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

CREATE TABLE stemcount (

    stemcount_id integer NOT NULL,

    collectiveobservation_id integer NOT NULL,

    stemdiameter double precision,

    stemdiameteraccuracy double precision,

    stemheight double precision,

    stemheightaccuracy double precision,

    stemcount integer NOT NULL,

    stemtaxonarea double precision,

    emb_stemcount integer

-- Name: stemcount_stemcount_id_seq; Type: SEQUENCE; Schema: public; Owner: -

--

CREATE SEQUENCE stemcount_stemcount_id_seq

    START WITH 1

    INCREMENT BY 1

    NO MINVALUE

    NO MAXVALUE

    CACHE 1;

--

-- Name: stemcount_stemcount_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -

--

ALTER SEQUENCE stemcount_stemcount_id_seq OWNED BY stemcount.stemcount_id;

--

ALTER SEQUENCE stemlocation_stemlocation_id_seq OWNED BY individualplant.individualplant_id;

    individualplant_id integer,

-- Name: view_all_commnames_code; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_all_commnames_code AS

    SELECT commusage.commconcept_id, commusage.commname FROM commusage WHERE ((commusage.classsystem)::text = 'Code'::text);

--

-- Name: view_all_commnames_common; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_all_commnames_common AS

    SELECT commusage.commconcept_id, commusage.commname FROM commusage WHERE ((commusage.classsystem)::text = 'Common'::text);

--

-- Name: view_all_commnames_sciname; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_all_commnames_sciname AS

    SELECT commusage.commconcept_id, commusage.commname FROM commusage WHERE ((commusage.classsystem)::text = 'Scientific'::text);

--

-- Name: view_all_commnames_translated; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_all_commnames_translated AS

    SELECT commusage.commconcept_id, commusage.commname FROM commusage WHERE ((commusage.classsystem)::text = 'Translated'::text);

--

-- Name: view_all_plantnames_code; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_all_plantnames_code AS

    SELECT plantusage.plantconcept_id, plantusage.plantname FROM plantusage WHERE ((plantusage.classsystem)::text = 'Code'::text);

--

-- Name: view_all_plantnames_common; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_all_plantnames_common AS

    SELECT plantusage.plantconcept_id, plantusage.plantname FROM plantusage WHERE ((plantusage.classsystem)::text = 'English Common'::text);

--

-- Name: view_all_plantnames_sciname; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_all_plantnames_sciname AS

    SELECT plantusage.plantconcept_id, plantusage.plantname FROM plantusage WHERE ((plantusage.classsystem)::text = 'Scientific'::text);

--

-- Name: view_all_plantnames_scinamenoauth; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_all_plantnames_scinamenoauth AS

    SELECT plantusage.plantconcept_id, plantusage.plantname FROM plantusage WHERE ((plantusage.classsystem)::text = 'Scientific without authors'::text);

--

-- Name: view_notemb_observation; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_notemb_observation AS

    SELECT plotevent.plotevent_id, plotevent.previousobs_id, plotevent.plot_id, plotevent.project_id, plotevent.authorobscode, plotevent.dateaccuracy, plotevent.covermethod_id, plotevent.coverdispersion, plotevent.autotaxoncover, plotevent.stratummethod_id, plotevent.methodnarrative, plotevent.taxonobservationarea, plotevent.stemsizelimit, plotevent.stemobservationarea, plotevent.stemsamplemethod, plotevent.originaldata, plotevent.effortlevel, plotevent.plotvalidationlevel, plotevent.floristicquality, plotevent.bryophytequality, plotevent.lichenquality, plotevent.observationnarrative, plotevent.landscapenarrative, plotevent.homogeneity, plotevent.phenologicaspect, plotevent.representativeness, plotevent.standmaturity, plotevent.successionalstatus, plotevent.basalarea, plotevent.hydrologicregime, plotevent.soilmoistureregime, plotevent.soildrainage, plotevent.watersalinity, plotevent.waterdepth, plotevent.shoredistance, plotevent.soildepth, plotevent.organicdepth, plotevent.soiltaxon_id, plotevent.soiltaxonsrc, plotevent.percentbedrock, plotevent.percentrockgravel, plotevent.percentwood, plotevent.percentlitter, plotevent.percentbaresoil, plotevent.percentwater, plotevent.percentother, plotevent.nameother, plotevent.treeht, plotevent.shrubht, plotevent.fieldht, plotevent.nonvascularht, plotevent.submergedht, plotevent.treecover, plotevent.shrubcover, plotevent.fieldcover, plotevent.nonvascularcover, plotevent.floatingcover, plotevent.submergedcover, plotevent.dominantstratum, plotevent.growthform1type, plotevent.growthform2type, plotevent.growthform3type, plotevent.growthform1cover, plotevent.growthform2cover, plotevent.growthform3cover, plotevent.totalcover, plotevent.notespublic, plotevent.notesmgt, plotevent.revisions, plotevent.obsstartdate, plotevent.obsenddate, plotevent.dateentered, plotevent.emb_observation, plotevent.interp_orig_ci_id, plotevent.interp_orig_cc_id, plotevent.interp_orig_sciname, plotevent.interp_orig_code, plotevent.interp_orig_party_id, plotevent.interp_orig_partyname, plotevent.interp_current_ci_id, plotevent.interp_current_cc_id, plotevent.interp_current_sciname, plotevent.interp_current_code, plotevent.interp_current_party_id, plotevent.interp_current_partyname, plotevent.interp_bestfit_ci_id, plotevent.interp_bestfit_cc_id, plotevent.interp_bestfit_sciname, plotevent.interp_bestfit_code, plotevent.interp_bestfit_party_id, plotevent.interp_bestfit_partyname, plotevent.toptaxon1name, plotevent.toptaxon2name, plotevent.toptaxon3name, plotevent.toptaxon4name, plotevent.toptaxon5name, plotevent.numberoftaxa, plotevent.accessioncode FROM plotevent WHERE (plotevent.emb_observation < 6);

--

-- Name: view_browseparty_classcontrib; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_browseparty_classcontrib AS

    SELECT observation_2.plotevent_id, classcontributor.party_id FROM (view_notemb_observation observation_2 JOIN (commclass JOIN classcontributor ON ((commclass.commclass_id = classcontributor.commclass_id))) ON ((observation_2.plotevent_id = commclass.plotevent_id))) GROUP BY observation_2.plotevent_id, classcontributor.party_id;

--

-- Name: view_browseparty_obscontrib; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_browseparty_obscontrib AS

    SELECT observationcontributor.party_id, observationcontributor.plotevent_id FROM (view_notemb_observation plotevent JOIN observationcontributor ON ((plotevent.plotevent_id = observationcontributor.plotevent_id))) GROUP BY observationcontributor.party_id, observationcontributor.plotevent_id;

--

-- Name: view_browseparty_projectcontrib; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_browseparty_projectcontrib AS

    SELECT observation_1.plotevent_id, projectcontributor.party_id FROM ((project JOIN view_notemb_observation observation_1 ON ((project.project_id = observation_1.project_id))) JOIN projectcontributor ON ((project.project_id = projectcontributor.project_id))) GROUP BY observation_1.plotevent_id, projectcontributor.party_id;

--

-- Name: view_browseparty_all; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_browseparty_all AS

    (SELECT view_browseparty_classcontrib.plotevent_id, view_browseparty_classcontrib.party_id FROM view_browseparty_classcontrib UNION SELECT view_browseparty_projectcontrib.plotevent_id, view_browseparty_projectcontrib.party_id FROM view_browseparty_projectcontrib) UNION SELECT view_browseparty_obscontrib.plotevent_id, view_browseparty_obscontrib.party_id FROM view_browseparty_obscontrib;

--

-- Name: view_browseparty_all_count; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_browseparty_all_count AS

    SELECT count(1) AS countallcontrib, view_browseparty_all.party_id FROM view_browseparty_all GROUP BY view_browseparty_all.party_id;

--

-- Name: view_browseparty_classcontrib_count; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_browseparty_classcontrib_count AS

    SELECT count(1) AS countclasscontrib, view_browseparty_classcontrib.party_id FROM view_browseparty_classcontrib GROUP BY view_browseparty_classcontrib.party_id;

--

-- Name: view_browseparty_obscontrib_count; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_browseparty_obscontrib_count AS

    SELECT count(1) AS countobscontrib, view_browseparty_obscontrib.party_id FROM view_browseparty_obscontrib GROUP BY view_browseparty_obscontrib.party_id;

--

-- Name: view_browseparty_projectcontrib_count; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_browseparty_projectcontrib_count AS

    SELECT count(1) AS countprojectcontrib, view_browseparty_projectcontrib.party_id FROM view_browseparty_projectcontrib GROUP BY view_browseparty_projectcontrib.party_id;

--

-- Name: view_browseparty_all_count_combined; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_browseparty_all_count_combined AS

    SELECT view_browseparty_all_count.party_id, view_browseparty_all_count.countallcontrib, view_browseparty_classcontrib_count.countclasscontrib, view_browseparty_obscontrib_count.countobscontrib, view_browseparty_projectcontrib_count.countprojectcontrib FROM (((view_browseparty_all_count LEFT JOIN view_browseparty_classcontrib_count ON ((view_browseparty_all_count.party_id = view_browseparty_classcontrib_count.party_id))) LEFT JOIN view_browseparty_obscontrib_count ON ((view_browseparty_all_count.party_id = view_browseparty_obscontrib_count.party_id))) LEFT JOIN view_browseparty_projectcontrib_count ON ((view_browseparty_all_count.party_id = view_browseparty_projectcontrib_count.party_id))) ORDER BY view_browseparty_all_count.countallcontrib DESC;

--

-- Name: view_busrule_duplcovercode; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_busrule_duplcovercode AS

    SELECT count(1) AS count, coverindex.covermethod_id, coverindex.covercode FROM coverindex GROUP BY coverindex.covermethod_id, coverindex.covercode HAVING (count(1) > 1);

--

-- Name: view_busrule_duplstratumtype; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_busrule_duplstratumtype AS

    SELECT count(1) AS count, stratumtype.stratummethod_id, stratumtype.stratumindex FROM stratumtype GROUP BY stratumtype.stratummethod_id, stratumtype.stratumindex HAVING (count(1) > 1);

--

-- Name: view_busrule_plotsizeshape; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_busrule_plotsizeshape AS

    SELECT plotevent.project_id, (SELECT project.projectname FROM project WHERE (project.project_id = plotevent.project_id)) AS projectname, count(1) AS plotcount FROM (plot JOIN plotevent ON ((plot.plot_id = plotevent.plot_id))) WHERE ((plot.area IS NULL) AND ((plot.shape IS NULL) OR (((plot.shape)::text <> 'Plotless'::text) AND (upper((plot.shape)::text) !~~ 'RELEV%'::text)))) GROUP BY plotevent.project_id;

--

-- Name: view_reference_transl; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_reference_transl AS

    SELECT CASE WHEN (reference.shortname IS NULL) THEN CASE WHEN (reference.title IS NULL) THEN (CASE WHEN (reference.fulltext IS NULL) THEN '[poorly documented reference]'::text ELSE CASE WHEN (length(reference.fulltext) > 35) THEN (substr(reference.fulltext, 1, 32) || '...'::text) ELSE reference.fulltext END END)::character varying ELSE reference.title END ELSE reference.shortname END AS reference_id_transl, reference.reference_id FROM reference;

--

-- Name: view_commconcept_transl; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_commconcept_transl AS

    SELECT commconcept.commconcept_id, commconcept.commname_id, commconcept.commname, commconcept.reference_id, (((commconcept.commname || ' ['::text) || ((SELECT view_reference_transl.reference_id_transl FROM view_reference_transl WHERE (view_reference_transl.reference_id = commconcept.reference_id)))::text) || ']'::text) AS commconcept_id_transl FROM commconcept;

--

-- Name: view_notemb_commclass; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_notemb_commclass AS

    SELECT commclass.commclass_id, commclass.plotevent_id, commclass.inspection, commclass.tableanalysis, commclass.multivariateanalysis, commclass.classpublication_id, commclass.classnotes, commclass.commname, commclass.commcode, commclass.commframework, commclass.commlevel, commclass.classstartdate, commclass.classstopdate, commclass.emb_commclass, commclass.expertsystem, commclass.accessioncode FROM commclass WHERE (commclass.emb_commclass < 6);

--

-- Name: view_comminterp_more; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_comminterp_more AS

    SELECT commclass.plotevent_id, commclass.inspection, commclass.tableanalysis, commclass.multivariateanalysis, commclass.expertsystem, commclass.classpublication_id, commclass.classnotes, commclass.accessioncode, commclass.classstartdate, commclass.classstopdate, comminterpretation.comminterpretation_id, comminterpretation.commclass_id, comminterpretation.commconcept_id, comminterpretation.commcode, comminterpretation.commname, comminterpretation.classfit, comminterpretation.classconfidence, comminterpretation.commauthority_id, comminterpretation.notes, comminterpretation.type, comminterpretation.nomenclaturaltype, comminterpretation.emb_comminterpretation, CASE WHEN ((comminterpretation.classfit)::text = 'Absolutely wrong'::text) THEN 6 WHEN ((comminterpretation.classfit)::text = 'Understandable but wrong'::text) THEN 4 WHEN ((comminterpretation.classfit)::text = 'Reasonable or acceptable answer'::text) THEN 3 WHEN ((comminterpretation.classfit)::text = 'Good answer'::text) THEN 2 WHEN ((comminterpretation.classfit)::text = 'Absolutely correct'::text) THEN 1 ELSE 5 END AS classfitnum, CASE WHEN ((comminterpretation.classconfidence)::text = 'High'::text) THEN 1 WHEN ((comminterpretation.classconfidence)::text = 'Medium'::text) THEN 2 WHEN ((comminterpretation.classconfidence)::text = 'Low'::text) THEN 3 ELSE 4 END AS classconfidencenum FROM view_notemb_commclass commclass, comminterpretation WHERE (commclass.commclass_id = comminterpretation.commclass_id);

--

-- Name: view_csv_taxonimportance_pre; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_csv_taxonimportance_pre AS

    SELECT taxonoccurrence.plotevent_id, taxonoccurrence.authorplantname AS plant, CASE WHEN (collectiveobservation.stratum_id IS NULL) THEN '-all-'::character varying ELSE (SELECT stratumtype.stratumname FROM stratumtype, stratum WHERE ((stratum.stratumtype_id = stratumtype.stratumtype_id) AND (stratum.stratum_id = collectiveobservation.stratum_id))) END AS stratum, collectiveobservation.cover, (SELECT min((coverindex.covercode)::text) AS min FROM coverindex WHERE (((coverindex.coverpercent = collectiveobservation.cover) AND (coverindex.covermethod_id = plotevent.covermethod_id)) AND (plotevent.plotevent_id = taxonoccurrence.plotevent_id))) AS covercode_exact, (SELECT min((coverindex.covercode)::text) AS min FROM coverindex WHERE ((((coverindex.upperlimit >= collectiveobservation.cover) AND (coverindex.lowerlimit <= collectiveobservation.cover)) AND (coverindex.covermethod_id = plotevent.covermethod_id)) AND (plotevent.plotevent_id = taxonoccurrence.plotevent_id))) AS covercode_byrange, collectiveobservation.basalarea, taxonoccurrence.accessioncode FROM taxonoccurrence, collectiveobservation, view_notemb_observation plotevent WHERE ((taxonoccurrence.taxonoccurrence_id = collectiveobservation.taxonoccurrence_id) AND (taxonoccurrence.plotevent_id = plotevent.plotevent_id));

--

-- Name: view_csv_taxonimportance; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_csv_taxonimportance AS

    SELECT view_csv_taxonimportance_pre.plotevent_id, view_csv_taxonimportance_pre.plant, view_csv_taxonimportance_pre.stratum, view_csv_taxonimportance_pre.cover, CASE WHEN (view_csv_taxonimportance_pre.covercode_exact IS NULL) THEN view_csv_taxonimportance_pre.covercode_byrange ELSE view_csv_taxonimportance_pre.covercode_exact END AS covercode, view_csv_taxonimportance_pre.basalarea, view_csv_taxonimportance_pre.accessioncode FROM view_csv_taxonimportance_pre;

--

-- Name: view_dbafielddesc_notimpl; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_dbafielddesc_notimpl AS

    SELECT dba_fielddescription.dba_fielddescription_id, dba_fielddescription.tablename, (SELECT dba_tabledescription.tablelabel FROM dba_tabledescription WHERE ((dba_fielddescription.tablename)::text = (dba_tabledescription.tablename)::text) LIMIT 1) AS tablename_transl, dba_fielddescription.fieldname, dba_fielddescription.fieldlabel, dba_fielddescription.fieldmodel, dba_fielddescription.fieldnulls, CASE WHEN ((dba_fielddescription.fieldnulls)::text = 'no'::text) THEN 'required'::text ELSE 'no'::text END AS fieldnulls_transl, dba_fielddescription.fieldtype, dba_fielddescription.fieldkey, CASE WHEN ((dba_fielddescription.fieldkey)::text = 'PK'::text) THEN dba_fielddescription.fieldkey WHEN ((dba_fielddescription.fieldkey)::text = 'FK'::text) THEN dba_fielddescription.fieldkey WHEN ((dba_fielddescription.fieldmodel)::text = 'denorm'::text) THEN dba_fielddescription.fieldmodel WHEN ((dba_fielddescription.fieldmodel)::text = 'implementation'::text) THEN dba_fielddescription.fieldmodel ELSE 'n/a'::character varying END AS fieldkey_transl, dba_fielddescription.fieldreferences, dba_fielddescription.fieldlist, dba_fielddescription.fieldnotes, dba_fielddescription.fielddefinition, dba_fielddescription.fieldkeywords FROM dba_fielddescription WHERE ((lower((dba_fielddescription.fieldmodel)::text) <> 'implementation'::text) AND (lower((dba_fielddescription.fieldmodel)::text) <> 'denorm'::text)) ORDER BY dba_fielddescription.dba_fielddescription_id;

--

-- Name: view_emb_embargo_currentfullonly; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_emb_embargo_currentfullonly AS

    SELECT embargo.embargo_id, embargo.plot_id, embargo.embargoreason, embargo.defaultstatus, embargo.embargostart, embargo.embargostop FROM embargo WHERE (((embargo.defaultstatus = 6) AND (embargo.embargostart < now())) AND (embargo.embargostop > now()));

--

-- Name: view_emb_embargo_complete; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_emb_embargo_complete AS

    SELECT COALESCE(emb.defaultstatus, 0) AS currentemb, plot.plot_id FROM (plot LEFT JOIN view_emb_embargo_currentfullonly emb ON ((plot.plot_id = emb.plot_id)));

--

-- Name: view_keywprojplaces; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_keywprojplaces AS

    SELECT project.project_id, plot.stateprovince FROM plot, project, plotevent WHERE ((plot.plot_id = plotevent.plot_id) AND (project.project_id = plotevent.project_id)) GROUP BY project.project_id, plot.stateprovince;

--

-- Name: view_kwhelper_projcontrib; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_kwhelper_projcontrib AS

    SELECT project.project_id, concat((((' '::text || (COALESCE(party.givenname, ''::character varying))::text) || ' '::text) || (COALESCE(party.surname, ''::character varying))::text)) AS kw FROM party, project, projectcontributor WHERE ((party.party_id = projectcontributor.party_id) AND (project.project_id = projectcontributor.project_id)) GROUP BY project.project_id;

--

-- Name: view_kwhelper_refjournal; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_kwhelper_refjournal AS

    SELECT reference.reference_id, (((((COALESCE(referencejournal.journal, ''::character varying))::text || ' '::text) || (COALESCE(referencejournal.issn, ''::character varying))::text) || ' '::text) || (COALESCE(referencejournal.abbreviation, ''::character varying))::text) AS kw FROM reference, referencejournal WHERE (reference.referencejournal_id = referencejournal.referencejournal_id) GROUP BY reference.reference_id, referencejournal.journal, referencejournal.issn, referencejournal.abbreviation;

--

-- Name: view_kwhelper_refparty; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_kwhelper_refparty AS

    SELECT reference.reference_id, concat((((((((' '::text || (COALESCE(referenceparty.givenname, ''::character varying))::text) || ' '::text) || (COALESCE(referenceparty.surname, ''::character varying))::text) || ' '::text) || (COALESCE(referenceparty.suffix, ''::character varying))::text) || ' '::text) || (COALESCE(referenceparty.organizationname, ''::character varying))::text)) AS kw FROM referenceparty, reference, referencecontributor WHERE ((referencecontributor.referenceparty_id = referenceparty.referenceparty_id) AND (reference.reference_id = referencecontributor.reference_id)) GROUP BY reference.reference_id;

--

-- Name: view_notemb_classcontributor; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_notemb_classcontributor AS

    SELECT classcontributor.classcontributor_id, classcontributor.commclass_id, classcontributor.party_id, classcontributor.role_id, classcontributor.emb_classcontributor FROM classcontributor WHERE (classcontributor.emb_classcontributor < 6);

--

-- Name: view_notemb_comminterpretation; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_notemb_comminterpretation AS

    SELECT comminterpretation.comminterpretation_id, comminterpretation.commclass_id, comminterpretation.commconcept_id, comminterpretation.commcode, comminterpretation.commname, comminterpretation.classfit, comminterpretation.classconfidence, comminterpretation.commauthority_id, comminterpretation.notes, comminterpretation.type, comminterpretation.nomenclaturaltype, comminterpretation.emb_comminterpretation FROM comminterpretation WHERE (comminterpretation.emb_comminterpretation < 6);

--

-- Name: view_notemb_disturbanceobs; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_notemb_disturbanceobs AS

    SELECT disturbanceobs.disturbanceobs_id, disturbanceobs.plotevent_id, disturbanceobs.disturbancetype, disturbanceobs.disturbanceintensity, disturbanceobs.disturbanceage, disturbanceobs.disturbanceextent, disturbanceobs.disturbancecomment, disturbanceobs.emb_disturbanceobs FROM disturbanceobs WHERE (disturbanceobs.emb_disturbanceobs < 6);

--

-- Name: view_notemb_plot; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_notemb_plot AS

    SELECT plot.plot_id, plot.authorplotcode, plot.reference_id, plot.parent_id, plot.reallatitude, plot.reallongitude, plot.locationaccuracy, plot.confidentialitystatus, plot.confidentialityreason, plot.latitude, plot.longitude, plot.authore, plot.authorn, plot.authorzone, plot.authordatum, plot.authorlocation, plot.locationnarrative, plot.azimuth, plot.dsgpoly, plot.shape, plot.area, plot.standsize, plot.placementmethod, plot.permanence, plot.layoutnarrative, plot.elevation, plot.elevationaccuracy, plot.elevationrange, plot.slopeaspect, plot.minslopeaspect, plot.maxslopeaspect, plot.slopegradient, plot.minslopegradient, plot.maxslopegradient, plot.topoposition, plot.landform, plot.surficialdeposits, plot.rocktype, plot.stateprovince, plot.country, plot.submitter_surname, plot.submitter_givenname, plot.submitter_email, plot.notespublic, plot.notesmgt, plot.revisions, plot.dateentered, plot.emb_plot, plot.plotrationalenarrative, plot.accessioncode FROM plot WHERE (plot.emb_plot < 6);

--

-- Name: view_notemb_soilobs; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_notemb_soilobs AS

    SELECT soilobs.soilobs_id, soilobs.plotevent_id, soilobs.soilhorizon, soilobs.soildepthtop, soilobs.soildepthbottom, soilobs.soilcolor, soilobs.soilorganic, soilobs.soiltexture, soilobs.soilsand, soilobs.soilsilt, soilobs.soilclay, soilobs.soilcoarse, soilobs.soilph, soilobs.exchangecapacity, soilobs.basesaturation, soilobs.soildescription, soilobs.emb_soilobs FROM soilobs WHERE (soilobs.emb_soilobs < 6);

--

-- Name: view_notemb_stemcount; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_notemb_stemcount AS

    SELECT stemcount.stemcount_id, stemcount.collectiveobservation_id, stemcount.stemdiameter, stemcount.stemdiameteraccuracy, stemcount.stemheight, stemcount.stemheightaccuracy, stemcount.stemcount, stemcount.stemtaxonarea, stemcount.emb_stemcount FROM stemcount WHERE (stemcount.emb_stemcount < 6);

--

-- Name: view_notemb_stemlocation; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_notemb_stemlocation AS

    SELECT individualplant.individualplant_id, individualplant.stemcount_id, individualplant.stemcode, individualplant.stemxposition, individualplant.stemyposition, individualplant.stemhealth, individualplant.emb_stemlocation FROM individualplant WHERE (individualplant.emb_stemlocation < 6);

--

-- Name: view_notemb_taxonalt; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_notemb_taxonalt AS

    SELECT taxonalt.taxonalt_id, taxonalt.taxondetermination_id, taxonalt.plantconcept_id, taxonalt.taxonaltfit, taxonalt.taxonaltconfidence, taxonalt.taxonaltnotes, taxonalt.emb_taxonalt FROM taxonalt WHERE (taxonalt.emb_taxonalt < 6);

--

-- Name: view_notemb_taxonimportance; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_notemb_taxonimportance AS

    SELECT collectiveobservation.collectiveobservation_id, collectiveobservation.taxonoccurrence_id, collectiveobservation.stratum_id, collectiveobservation.cover, collectiveobservation.basalarea, collectiveobservation.biomass, collectiveobservation.inferencearea, collectiveobservation.stratumbase, collectiveobservation.stratumheight, collectiveobservation.emb_taxonimportance, collectiveobservation.covercode FROM collectiveobservation WHERE (collectiveobservation.emb_taxonimportance < 6);

--

-- Name: view_notemb_taxoninterpretation; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_notemb_taxoninterpretation AS

    SELECT taxondetermination.taxondetermination_id, taxondetermination.taxonoccurrence_id, taxondetermination.individualplant_id, taxondetermination.plantconcept_id, taxondetermination.plantname_id, taxondetermination.party_id, taxondetermination.role_id, taxondetermination.interpretationtype, taxondetermination.reference_id, taxondetermination.originalinterpretation, taxondetermination.currentinterpretation, taxondetermination.taxonfit, taxondetermination.taxonconfidence, taxondetermination.collector_id, taxondetermination.collectionnumber, taxondetermination.museum_id, taxondetermination.museumaccessionnumber, taxondetermination.grouptype, taxondetermination.notes, taxondetermination.notespublic, taxondetermination.notesmgt, taxondetermination.revisions, taxondetermination.interpretationdate, taxondetermination.collectiondate, taxondetermination.emb_taxoninterpretation, taxondetermination.accessioncode FROM taxondetermination WHERE (taxondetermination.emb_taxoninterpretation < 6);

--

-- Name: view_notemb_taxonobservation; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_notemb_taxonobservation AS

    SELECT taxonoccurrence.taxonoccurrence_id, taxonoccurrence.plotevent_id, taxonoccurrence.authorplantname, taxonoccurrence.reference_id, taxonoccurrence.taxoninferencearea, taxonoccurrence.emb_taxonobservation, taxonoccurrence.int_origplantconcept_id, taxonoccurrence.int_origplantscifull, taxonoccurrence.int_origplantscinamenoauth, taxonoccurrence.int_origplantcommon, taxonoccurrence.int_origplantcode, taxonoccurrence.int_currplantconcept_id, taxonoccurrence.int_currplantscifull, taxonoccurrence.int_currplantscinamenoauth, taxonoccurrence.int_currplantcommon, taxonoccurrence.int_currplantcode, taxonoccurrence.accessioncode FROM taxonoccurrence WHERE (taxonoccurrence.emb_taxonobservation < 6);

--

-- Name: view_observation_transl; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_observation_transl AS

    SELECT plotevent.plotevent_id, COALESCE(plotevent.authorobscode, plot.authorplotcode) AS observation_id_transl FROM view_notemb_observation plotevent, plot WHERE (plotevent.plot_id = plot.plot_id);

--

-- Name: view_party_public; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_party_public AS

    SELECT party.party_id, party.accessioncode, party.salutation, party.surname, party.givenname, party.middlename, party.organizationname, party.contactinstructions, party.email FROM (party LEFT JOIN usr ON ((party.party_id = usr.party_id))) WHERE ((usr.usr_id IS NULL) OR (usr.permission_type > 1));

--

-- Name: view_party_transl; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_party_transl AS

    SELECT CASE WHEN (party.surname IS NULL) THEN CASE WHEN (party.organizationname IS NULL) THEN '[poorly documented party]'::text ELSE ((party.organizationname)::text || ' (organization)'::text) END ELSE ((party.surname)::text || CASE WHEN (party.givenname IS NULL) THEN ''::text ELSE (', '::text || (COALESCE(party.givenname, ''::character varying))::text) END) END AS party_id_transl, party.party_id FROM party;

--

-- Name: view_plantconcept_transl; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_plantconcept_transl AS

    SELECT plantconcept.plantconcept_id, plantconcept.plantname_id, plantconcept.plantname, plantconcept.reference_id, ((((plantconcept.plantname)::text || ' ['::text) || ((SELECT view_reference_transl.reference_id_transl FROM view_reference_transl WHERE (view_reference_transl.reference_id = plantconcept.reference_id)))::text) || ']'::text) AS plantconcept_id_transl FROM plantconcept;

--

-- Name: view_plotall_withembargo; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_plotall_withembargo AS

    SELECT plot.plot_id, plot.authorplotcode, plot.reference_id, plot.parent_id, plot.reallatitude, plot.reallongitude, plot.locationaccuracy, plot.confidentialitystatus, plot.confidentialityreason, plot.latitude, plot.longitude, plot.authore, plot.authorn, plot.authorzone, plot.authordatum, plot.authorlocation, plot.locationnarrative, plot.azimuth, plot.dsgpoly, plot.shape, plot.area, plot.standsize, plot.placementmethod, plot.permanence, plot.layoutnarrative, plot.elevation, plot.elevationaccuracy, plot.elevationrange, plot.slopeaspect, plot.minslopeaspect, plot.maxslopeaspect, plot.slopegradient, plot.minslopegradient, plot.maxslopegradient, plot.topoposition, plot.landform, plot.surficialdeposits, plot.rocktype, plot.stateprovince, plot.country, plot.submitter_surname, plot.submitter_givenname, plot.submitter_email, plot.notespublic, plot.notesmgt, plot.revisions, plot.dateentered, plot.emb_plot, plot.plotrationalenarrative, plot.accessioncode, embargo.embargo_id, embargo.embargoreason, embargo.defaultstatus, embargo.embargostart, embargo.embargostop, (embargo.defaultstatus + CASE WHEN (embargo.embargostop < now()) THEN 100 ELSE 0 END) AS embstatusinclexpired FROM (plot LEFT JOIN embargo ON ((plot.plot_id = embargo.plot_id)));

--

-- Name: view_std_commnames_code; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_std_commnames_code AS

    SELECT commusage.commconcept_id, commusage.commname FROM commusage WHERE ((((commusage.classsystem)::text = 'Code'::text) AND (commusage.party_id = (SELECT party.party_id FROM party WHERE ((party.accessioncode)::text = 'VB.Py.512.NATURESERVE'::text)))) AND (commusage.usagestop IS NULL));

--

-- Name: view_std_commnames_common; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_std_commnames_common AS

    SELECT commusage.commconcept_id, commusage.commname FROM commusage WHERE ((((commusage.classsystem)::text = 'Common'::text) AND (commusage.party_id = (SELECT party.party_id FROM party WHERE ((party.accessioncode)::text = 'VB.Py.512.NATURESERVE'::text)))) AND (commusage.usagestop IS NULL));

--

-- Name: view_std_commnames_sciname; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_std_commnames_sciname AS

    SELECT commusage.commconcept_id, commusage.commname FROM commusage WHERE ((((commusage.classsystem)::text = 'Scientific'::text) AND (commusage.party_id = (SELECT party.party_id FROM party WHERE ((party.accessioncode)::text = 'VB.Py.512.NATURESERVE'::text)))) AND (commusage.usagestop IS NULL));

--

-- Name: view_std_commnames_translated; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_std_commnames_translated AS

    SELECT commusage.commconcept_id, commusage.commname FROM commusage WHERE ((((commusage.classsystem)::text = 'Translated'::text) AND (commusage.party_id = (SELECT party.party_id FROM party WHERE ((party.accessioncode)::text = 'VB.Py.512.NATURESERVE'::text)))) AND (commusage.usagestop IS NULL));

--

-- Name: view_std_plantnames_code; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_std_plantnames_code AS

    SELECT plantusage.plantconcept_id, plantusage.plantname FROM plantusage WHERE ((((plantusage.classsystem)::text = 'Code'::text) AND (plantusage.party_id = (SELECT party.party_id FROM party WHERE ((party.accessioncode)::text = 'VB.Py.511.USDANRCSPLANTS2'::text)))) AND (plantusage.usagestop IS NULL));

--

-- Name: view_std_plantnames_common; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_std_plantnames_common AS

    SELECT plantusage.plantconcept_id, plantusage.plantname FROM plantusage WHERE ((((plantusage.classsystem)::text = 'English Common'::text) AND (plantusage.party_id = (SELECT party.party_id FROM party WHERE ((party.accessioncode)::text = 'VB.Py.511.USDANRCSPLANTS2'::text)))) AND (plantusage.usagestop IS NULL));

--

-- Name: view_std_plantnames_sciname; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_std_plantnames_sciname AS

    SELECT plantusage.plantconcept_id, plantusage.plantname FROM plantusage WHERE ((((plantusage.classsystem)::text = 'Scientific'::text) AND (plantusage.party_id = (SELECT party.party_id FROM party WHERE ((party.accessioncode)::text = 'VB.Py.511.USDANRCSPLANTS2'::text)))) AND (plantusage.usagestop IS NULL));

--

-- Name: view_std_plantnames_scinamenoauth; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_std_plantnames_scinamenoauth AS

    SELECT plantusage.plantconcept_id, plantusage.plantname FROM plantusage WHERE ((((plantusage.classsystem)::text = 'Scientific without authors'::text) AND (plantusage.party_id = (SELECT party.party_id FROM party WHERE ((party.accessioncode)::text = 'VB.Py.511.USDANRCSPLANTS2'::text)))) AND (plantusage.usagestop IS NULL));

--

-- Name: view_taxoninterp_more; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_taxoninterp_more AS

    SELECT taxondetermination.taxondetermination_id, taxondetermination.taxonoccurrence_id, taxondetermination.individualplant_id, taxondetermination.plantconcept_id, taxondetermination.plantname_id, taxondetermination.party_id, taxondetermination.role_id, taxondetermination.interpretationtype, taxondetermination.reference_id, taxondetermination.originalinterpretation, taxondetermination.currentinterpretation, taxondetermination.taxonfit, taxondetermination.taxonconfidence, taxondetermination.collector_id, taxondetermination.collectionnumber, taxondetermination.museum_id, taxondetermination.museumaccessionnumber, taxondetermination.grouptype, taxondetermination.notes, taxondetermination.notespublic, taxondetermination.notesmgt, taxondetermination.revisions, taxondetermination.interpretationdate, taxondetermination.collectiondate, taxondetermination.emb_taxoninterpretation, taxondetermination.accessioncode, plantconcept.accessioncode AS pc_accessioncode, plantconcept.plantname AS pc_plantname, taxonoccurrence.plotevent_id FROM view_notemb_taxoninterpretation taxondetermination, taxonoccurrence, plantconcept WHERE ((taxonoccurrence.taxonoccurrence_id = taxondetermination.taxonoccurrence_id) AND (plantconcept.plantconcept_id = taxondetermination.plantconcept_id));

--

-- Name: view_taxonobs_withmaxcover; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_taxonobs_withmaxcover AS

    SELECT taxonoccurrence.taxonoccurrence_id, taxonoccurrence.plotevent_id, taxonoccurrence.authorplantname, taxonoccurrence.reference_id, taxonoccurrence.taxoninferencearea, taxonoccurrence.emb_taxonobservation, taxonoccurrence.int_origplantconcept_id, taxonoccurrence.int_origplantscifull, taxonoccurrence.int_origplantscinamenoauth, taxonoccurrence.int_origplantcommon, taxonoccurrence.int_origplantcode, taxonoccurrence.int_currplantconcept_id, taxonoccurrence.int_currplantscifull, taxonoccurrence.int_currplantscinamenoauth, taxonoccurrence.int_currplantcommon, taxonoccurrence.int_currplantcode, taxonoccurrence.accessioncode, (SELECT max(collectiveobservation.cover) AS max FROM collectiveobservation WHERE (collectiveobservation.taxonoccurrence_id = taxonoccurrence.taxonoccurrence_id)) AS maxcover FROM view_notemb_taxonobservation taxonoccurrence;

--

-- Name: view_taxonobs_distinctid_curr; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_taxonobs_distinctid_curr AS

    SELECT userdatasetitem.userdataset_id, taxonoccurrence.plotevent_id, taxonoccurrence.int_currplantconcept_id AS plantconcept_id, max(taxonoccurrence.maxcover) AS maxplantcover FROM view_taxonobs_withmaxcover taxonoccurrence, userdatasetitem WHERE ((taxonoccurrence.plotevent_id = userdatasetitem.itemrecord) AND ((userdatasetitem.itemtable)::text = 'plotevent'::text)) GROUP BY taxonoccurrence.int_currplantconcept_id, userdatasetitem.userdataset_id, taxonoccurrence.plotevent_id;

--

-- Name: view_taxonobs_distinctid_curr_counts; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_taxonobs_distinctid_curr_counts AS

    SELECT view_taxonobs_distinctid_curr.userdataset_id, view_taxonobs_distinctid_curr.plantconcept_id, count(1) AS countobs, avg(view_taxonobs_distinctid_curr.maxplantcover) AS avgcovernoround, min(view_taxonobs_distinctid_curr.maxplantcover) AS mincovernoround, max(view_taxonobs_distinctid_curr.maxplantcover) AS maxcovernoround FROM view_taxonobs_distinctid_curr GROUP BY view_taxonobs_distinctid_curr.userdataset_id, view_taxonobs_distinctid_curr.plantconcept_id;

--

-- Name: view_taxonobs_distinctid_curr_counts_plants; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW view_taxonobs_distinctid_curr_counts_plants AS

    SELECT view_taxonobs_distinctid_curr_counts.userdataset_id, view_taxonobs_distinctid_curr_counts.plantconcept_id, view_taxonobs_distinctid_curr_counts.countobs, view_taxonobs_distinctid_curr_counts.avgcovernoround, view_taxonobs_distinctid_curr_counts.mincovernoround, view_taxonobs_distinctid_curr_counts.maxcovernoround, round((view_taxonobs_distinctid_curr_counts.avgcovernoround)::numeric, 3) AS avgcover, round((view_taxonobs_distinctid_curr_counts.mincovernoround)::numeric, 3) AS mincover, round((view_taxonobs_distinctid_curr_counts.maxcovernoround)::numeric, 3) AS maxcover, temptbl_std_plantnames.plantname, temptbl_std_plantnames.sciname, temptbl_std_plantnames.scinamenoauth, temptbl_std_plantnames.code, temptbl_std_plantnames.common FROM temptbl_std_plantnames, view_taxonobs_distinctid_curr_counts WHERE (view_taxonobs_distinctid_curr_counts.plantconcept_id = temptbl_std_plantnames.plantconcept_id);

--

ALTER TABLE individualplant ALTER COLUMN individualplant_id SET DEFAULT nextval('stemlocation_stemlocation_id_seq'::regclass);

-- Name: stemcount_id; Type: DEFAULT; Schema: public; Owner: -

ALTER TABLE stemcount ALTER COLUMN stemcount_id SET DEFAULT nextval('stemcount_stemcount_id_seq'::regclass);

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

--

ALTER TABLE ONLY stemcount

    ADD CONSTRAINT stemcount_pkey PRIMARY KEY (stemcount_id);

--

ALTER TABLE ONLY individualplant

    ADD CONSTRAINT stemlocation_pkey PRIMARY KEY (individualplant_id);

-- Name: emb_stemcount_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: 

--

CREATE INDEX emb_stemcount_idx ON stemcount USING btree (emb_stemcount);

--

CREATE INDEX emb_stemlocation_idx ON individualplant USING btree (emb_stemlocation);

-- Name: stemcount_taxonimportance_id_x; Type: INDEX; Schema: public; Owner: -; Tablespace: 

CREATE INDEX stemcount_taxonimportance_id_x ON stemcount USING btree (collectiveobservation_id);

-- Name: stemlocation_stemcount_id_x; Type: INDEX; Schema: public; Owner: -; Tablespace: 

--

CREATE INDEX stemlocation_stemcount_id_x ON individualplant USING btree (stemcount_id);

--

CREATE INDEX taxoninterpretation_stemlocation_id_x ON taxondetermination USING btree (individualplant_id);

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

    ADD CONSTRAINT collection_individualplant_id_fkey FOREIGN KEY (individualplant_id) REFERENCES individualplant(individualplant_id);

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

ALTER TABLE ONLY stemcount

    ADD CONSTRAINT r1stemcount_taxonimportance_id FOREIGN KEY (collectiveobservation_id) REFERENCES collectiveobservation(collectiveobservation_id);

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

--

ALTER TABLE ONLY individualplant

    ADD CONSTRAINT r1stemlocation_stemcount_id FOREIGN KEY (stemcount_id) REFERENCES stemcount(stemcount_id);

--

    ADD CONSTRAINT r2taxoninterpretation_stemlocation_id FOREIGN KEY (individualplant_id) REFERENCES individualplant(individualplant_id);

NoInd,/collectiveobservation/*/stemCount/_alt/1,"Brad: Incorrect for VegX. This is a count of number of indiiduals for an *aggregate* observation. For VegBank, I'm not sure. Not exactly the same as stemCount. An individual tree could have 3 stems but would still only count as 1. We need to check with Bob on this."

ht_first_branch_m,"/collectiveobservation/stemCount/individualplant/definedValue[*_ID/userDefined[tableName=individualplant,userDefinedName=heightFirstBranchM]:[userDefinedType=varchar]]:[@fkey=tableRecord_ID]/definedValue",Brad: Incorrect for VegBank. This is a measurement applied to a single tree. Check with Bob

tag2,/collectiveobservation/stemCount/individualplant/stemCode/_alt/1,"Brad: See commend for tag1. Your mapping for tag2 looks correct. Probably both values would go here, only nested, with one superceding the other."

tag1,/collectiveobservation/stemCount/individualplant/stemCode/_alt/2,"Brad: Another type of code, typically a number, used by the original data provider to indicate an individual tree. These are numbers on physical tags attached to the tree. Tag2 Is the same thing, only used if the first tag was lost. Obviously not a good system as it's possible a tree tag could be lost and changed more than once."

x_position,/collectiveobservation/stemCount/individualplant/stemXPosition,"Brad: Correct for VegBank. I'm not so sure for VegX. Let's ask Nick about this. These are important, fundamental values of many tree plots, and should be accommodated within VegX."

y_position,/collectiveobservation/stemCount/individualplant/stemYPosition,Brad: See comment above for x_position

temp_dbh,/collectiveobservation/stemCount/stemDiameter,

height_m,/collectiveobservation/stemCount/stemHeight,Brad: Incorrect for VegBank. This is a measurement applied to a single tree. Check with Bob

PlotObsID,":[collectiveobservation/*/stemCount/_alt/2=1]/{taxondetermination[ROLE_ID/aux_Role/roleCode=collector]:[interpretationType=Author,originalInterpretation=true,currentInterpretation=false,interpretationDate=epoch,*_ID/plantConcept:[*_ID/reference],*_ID/party]/museumAccessionNumber,*_ID/plotevent/*_ID/plot:[confidentialityStatus=0]/authorPlotCode/_alt/3}","Brad: Neither is correct; this is just an internal ID for table plotObservations. However, it has the important property of uniquely identifying an ""observation"", which is an individual tree, in the case of an individual observation, or a records of a species with an associated count of individuals or measurement of percent cover, in the case of aggregate observations. Not sure where to store this. Main point is that it is not part of the original data, but an auto_increment added later."

/simpleUserdefined[name=count]/value,/collectiveobservation/*/stemCount/_alt/1

/simpleUserdefined[name=habitat]/value,"/collectiveobservation/stemCount/individualplant/definedValue[*_ID/userDefined[tableName=individualplant,userDefinedName=habitat]:[userDefinedType=varchar]]:[@fkey=tableRecord_ID]/definedValue"

/simpleUserdefined[name=htFirstBranchM]/value,"/collectiveobservation/stemCount/individualplant/definedValue[*_ID/userDefined[tableName=individualplant,userDefinedName=heightFirstBranchM]:[userDefinedType=varchar]]:[@fkey=tableRecord_ID]/definedValue"

/simpleUserdefined[name=plantFungusDescription]/value,"/collectiveobservation/stemCount/individualplant/definedValue[*_ID/userDefined[tableName=individualplant,userDefinedName=plantFungusDescription]:[userDefinedType=varchar]]:[@fkey=tableRecord_ID]/definedValue"

/diameterBaseDistance[baseDistance=0]/diameter,"/collectiveobservation/stemCount/individualplant/definedValue[*_ID/userDefined[tableName=individualplant,userDefinedName=stemBasalDiameter]:[userDefinedType=varchar]]:[@fkey=tableRecord_ID]/definedValue"

/simpleUserdefined[name=stemCanopyForm]/value,"/collectiveobservation/stemCount/individualplant/definedValue[*_ID/userDefined[tableName=individualplant,userDefinedName=stemCanopyForm]:[userDefinedType=varchar]]:[@fkey=tableRecord_ID]/definedValue"

/simpleUserdefined[name=stemCanopyPosition]/value,"/collectiveobservation/stemCount/individualplant/definedValue[*_ID/userDefined[tableName=individualplant,userDefinedName=stemCanopyPosition]:[userDefinedType=varchar]]:[@fkey=tableRecord_ID]/definedValue"

/simpleUserdefined[name=stemHeightFirstBranchM]/value,"/collectiveobservation/stemCount/individualplant/definedValue[*_ID/userDefined[tableName=individualplant,userDefinedName=stemHeightFirstBranchM]:[userDefinedType=varchar]]:[@fkey=tableRecord_ID]/definedValue"

/simpleUserdefined[name=stemLianaInfestation]/value,"/collectiveobservation/stemCount/individualplant/definedValue[*_ID/userDefined[tableName=individualplant,userDefinedName=stemLianaInfestation]:[userDefinedType=varchar]]:[@fkey=tableRecord_ID]/definedValue"

/simpleUserdefined[name=substrate]/value,"/collectiveobservation/stemCount/individualplant/definedValue[*_ID/userDefined[tableName=individualplant,userDefinedName=substrate]:[userDefinedType=varchar]]:[@fkey=tableRecord_ID]/definedValue"

/simpleUserdefined[name=vegetation]/value,"/collectiveobservation/stemCount/individualplant/definedValue[*_ID/userDefined[tableName=individualplant,userDefinedName=vegetation]:[userDefinedType=varchar]]:[@fkey=tableRecord_ID]/definedValue"

/*ID->/*s/individualOrganism/identificationLabel,/collectiveobservation/stemCount/individualplant/stemCode

/*ID->/*s/individualOrganism/identificationLabel/_alt/1,/collectiveobservation/stemCount/individualplant/stemCode/_alt/1

/*ID->/*s/individualOrganism/identificationLabel/_alt/2,/collectiveobservation/stemCount/individualplant/stemCode/_alt/2

/simpleUserdefined[name=xPosition]/value,/collectiveobservation/stemCount/individualplant/stemXPosition

/simpleUserdefined[name=yPosition]/value,/collectiveobservation/stemCount/individualplant/stemYPosition

/diameterBaseDistance[baseDistance=1.37]/diameter,/collectiveobservation/stemCount/stemDiameter

/height,/collectiveobservation/stemCount/stemHeight

/simpleUserdefined[name=stemHeightM]/value,/collectiveobservation/stemCount/stemHeight

/*ID->/*s/individualOrganism/*sID->/*s/taxonNameUsageConcept/voucher,":[collectiveobservation/*/stemCount/_alt/2=1]/{taxondetermination[ROLE_ID/aux_Role/roleCode=collector]:[interpretationType=Author,originalInterpretation=true,currentInterpretation=false,interpretationDate=epoch,*_ID/plantConcept:[*_ID/reference],*_ID/party]/museumAccessionNumber,*_ID/plotevent/*_ID/plot:[confidentialityStatus=0]/authorPlotCode/_alt/3}"

"individualplant","stemlocation",

"collectiveobservation","taxonimportance",

Habitat,"/collectiveobservation/stemCount/individualplant/definedValue[*_ID/userDefined[tableName=individualplant,userDefinedName=habitat]:[userDefinedType=varchar]]:[@fkey=tableRecord_ID]/definedValue","Brad: Free-text description of vegetation community where collected, frequently redundane wrt 'Vegetation'. Bob, Nick: keep as user defined or create special element?"

PlantFungusDescription,"/collectiveobservation/stemCount/individualplant/definedValue[*_ID/userDefined[tableName=individualplant,userDefinedName=plantFungusDescription]:[userDefinedType=varchar]]:[@fkey=tableRecord_ID]/definedValue","Brad: Incorrect mapping to VB, also possibly to VX;  this is the specimenDescription field, as used by this institution (NOT a standard DwC element). Very important! Bob, Nick, where would this go in VB and VX?"

Substrate,"/collectiveobservation/stemCount/individualplant/definedValue[*_ID/userDefined[tableName=individualplant,userDefinedName=substrate]:[userDefinedType=varchar]]:[@fkey=tableRecord_ID]/definedValue","Brad: Incorrect mapping to VB and VX; refers to geological substrate or soils where specimen collected. Often concatenated with habitat, or included with specimen description. Bob, Nick, is there an element for this in VB/VX, or shall we just use userDefined?"

Vegetation,"/collectiveobservation/stemCount/individualplant/definedValue[*_ID/userDefined[tableName=individualplant,userDefinedName=vegetation]:[userDefinedType=varchar]]:[@fkey=tableRecord_ID]/definedValue","Brad: Free-text description of vegetation community where collected, frequently redundane wrt 'Habitat'. Bob, Nick: keep as user defined or create special element?"

UniqueNYInternalRecordNumber,":[collectiveobservation/*/stemCount/_alt/2=1]/{taxondetermination[ROLE_ID/aux_Role/roleCode=collector]:[interpretationType=Author,originalInterpretation=true,currentInterpretation=false,interpretationDate=epoch,*_ID/plantConcept:[*_ID/reference],*_ID/party]/museumAccessionNumber,*_ID/plotevent/*_ID/plot:[confidentialityStatus=0]/authorPlotCode/_alt/3}","Brad: OMIT; Aaron: We need this field because it is the only ID column with an entry in every row; Brad: OK, use this as primary key"

NoInd,//*/stemCount/_alt/1,"Brad: Incorrect for VegX. This is a count of number of indiiduals for an *aggregate* observation. For VegBank, I'm not sure. Not exactly the same as stemCount. An individual tree could have 3 stems but would still only count as 1. We need to check with Bob on this."

ht_first_branch_m,"//individualplant/definedValue[*_ID/userDefined[tableName=individualplant,userDefinedName=heightFirstBranchM]]/definedValue",Brad: Incorrect for VegBank. This is a measurement applied to a single tree. Check with Bob

tag2,//individualplant/stemCode/_alt/1,"Brad: See commend for tag1. Your mapping for tag2 looks correct. Probably both values would go here, only nested, with one superceding the other."

tag1,//individualplant/stemCode/_alt/2,"Brad: Another type of code, typically a number, used by the original data provider to indicate an individual tree. These are numbers on physical tags attached to the tree. Tag2 Is the same thing, only used if the first tag was lost. Obviously not a good system as it's possible a tree tag could be lost and changed more than once."

x_position,//individualplant/stemXPosition,"Brad: Correct for VegBank. I'm not so sure for VegX. Let's ask Nick about this. These are important, fundamental values of many tree plots, and should be accommodated within VegX."

y_position,//individualplant/stemYPosition,Brad: See comment above for x_position

temp_dbh,//stemCount/stemDiameter,

height_m,//stemCount/stemHeight,Brad: Incorrect for VegBank. This is a measurement applied to a single tree. Check with Bob

/simpleUserdefined[name=count]/value,//*/stemCount/_alt/1

/simpleUserdefined[name=habitat]/value,"//individualplant/definedValue[*_ID/userDefined[tableName=individualplant,userDefinedName=habitat]]/definedValue"

/simpleUserdefined[name=htFirstBranchM]/value,"//individualplant/definedValue[*_ID/userDefined[tableName=individualplant,userDefinedName=heightFirstBranchM]]/definedValue"

/simpleUserdefined[name=plantFungusDescription]/value,"//individualplant/definedValue[*_ID/userDefined[tableName=individualplant,userDefinedName=plantFungusDescription]]/definedValue"

/diameterBaseDistance[baseDistance=0]/diameter,"//individualplant/definedValue[*_ID/userDefined[tableName=individualplant,userDefinedName=stemBasalDiameter]]/definedValue"

/simpleUserdefined[name=stemCanopyForm]/value,"//individualplant/definedValue[*_ID/userDefined[tableName=individualplant,userDefinedName=stemCanopyForm]]/definedValue"

/simpleUserdefined[name=stemCanopyPosition]/value,"//individualplant/definedValue[*_ID/userDefined[tableName=individualplant,userDefinedName=stemCanopyPosition]]/definedValue"

/simpleUserdefined[name=stemHeightFirstBranchM]/value,"//individualplant/definedValue[*_ID/userDefined[tableName=individualplant,userDefinedName=stemHeightFirstBranchM]]/definedValue"

/simpleUserdefined[name=stemLianaInfestation]/value,"//individualplant/definedValue[*_ID/userDefined[tableName=individualplant,userDefinedName=stemLianaInfestation]]/definedValue"

/simpleUserdefined[name=substrate]/value,"//individualplant/definedValue[*_ID/userDefined[tableName=individualplant,userDefinedName=substrate]]/definedValue"

/simpleUserdefined[name=vegetation]/value,"//individualplant/definedValue[*_ID/userDefined[tableName=individualplant,userDefinedName=vegetation]]/definedValue"

//individualOrganism/identificationLabel,//individualplant/stemCode

//individualOrganism/identificationLabel/_alt/1,//individualplant/stemCode/_alt/1

//individualOrganism/identificationLabel/_alt/2,//individualplant/stemCode/_alt/2

/simpleUserdefined[name=xPosition]/value,//individualplant/stemXPosition

/simpleUserdefined[name=yPosition]/value,//individualplant/stemYPosition

/diameterBaseDistance[baseDistance=1.37]/diameter,//stemCount/stemDiameter

/height,//stemCount/stemHeight

/simpleUserdefined[name=stemHeightM]/value,//stemCount/stemHeight

Habitat,"//individualplant/definedValue[*_ID/userDefined[tableName=individualplant,userDefinedName=habitat]]/definedValue","Brad: Free-text description of vegetation community where collected, frequently redundane wrt 'Vegetation'. Bob, Nick: keep as user defined or create special element?"

PlantFungusDescription,"//individualplant/definedValue[*_ID/userDefined[tableName=individualplant,userDefinedName=plantFungusDescription]]/definedValue","Brad: Incorrect mapping to VB, also possibly to VX;  this is the specimenDescription field, as used by this institution (NOT a standard DwC element). Very important! Bob, Nick, where would this go in VB and VX?"

Substrate,"//individualplant/definedValue[*_ID/userDefined[tableName=individualplant,userDefinedName=substrate]]/definedValue","Brad: Incorrect mapping to VB and VX; refers to geological substrate or soils where specimen collected. Often concatenated with habitat, or included with specimen description. Bob, Nick, is there an element for this in VB/VX, or shall we just use userDefined?"

Vegetation,"//individualplant/definedValue[*_ID/userDefined[tableName=individualplant,userDefinedName=vegetation]]/definedValue","Brad: Free-text description of vegetation community where collected, frequently redundane wrt 'Habitat'. Bob, Nick: keep as user defined or create special element?"

no_of_individuals,//*/stemCount/_alt/1,"Brad: Incorrect for VegX. This is a count of number of indiiduals for an *aggregate* observation. For VegBank, I'm not sure. Not exactly the same as stemCount. An individual tree could have 3 stems but would still only count as 1. We need to check with Bob on this."

ht_first_branch_m,"//individualplant/definedValue[*_ID/userDefined[tableName=individualplant,userDefinedName=heightFirstBranchM]]/definedValue",Brad: Incorrect for VegBank. This is a measurement applied to a single tree. Check with Bob

basal_diam,"//individualplant/definedValue[*_ID/userDefined[tableName=individualplant,userDefinedName=stemBasalDiameter]]/definedValue",

stem_canopy_form,"//individualplant/definedValue[*_ID/userDefined[tableName=individualplant,userDefinedName=stemCanopyForm]]/definedValue",Brad: Should also be userDefined for VegBank. 

stem_canopy_position,"//individualplant/definedValue[*_ID/userDefined[tableName=individualplant,userDefinedName=stemCanopyPosition]]/definedValue",Brad: Should also be userDefined for VegBank. 

stem_height_first_branch_m,"//individualplant/definedValue[*_ID/userDefined[tableName=individualplant,userDefinedName=stemHeightFirstBranchM]]/definedValue","Brad: Should also be userDefined for VegBank. Same as for ht_first_branch_m, but applies to individuals stems, not trees. Rare."

stem_liana_infestation,"//individualplant/definedValue[*_ID/userDefined[tableName=individualplant,userDefinedName=stemLianaInfestation]]/definedValue",Brad: Should also be userDefined for VegBank. 

stem_tag1,//individualplant/stemCode,"Brad: Same as tag1 & tag2, but applied to individual stems. I'm still not clear how to distinguish between methods which tag only individuals trees, and those which tag individual stems."

stem_tag2,//individualplant/stemCode,Brad: see above

tag2,//individualplant/stemCode/_alt/1,"Brad: See commend for tag1. Your mapping for tag2 looks correct. Probably both values would go here, only nested, with one superceding the other."

tag1,//individualplant/stemCode/_alt/2,"Brad: Another type of code, typically a number, used by the original data provider to indicate an individual tree. These are numbers on physical tags attached to the tree. Tag2 Is the same thing, only used if the first tag was lost. Obviously not a good system as it's possible a tree tag could be lost and changed more than once."

x_position,//individualplant/stemXPosition,"Brad: Correct for VegBank. I'm not so sure for VegX. Let's ask Nick about this. These are important, fundamental values of many tree plots, and should be accommodated within VegX."

y_position,//individualplant/stemYPosition,Brad: See comment above for x_position

stem_dbh,//stemCount/stemDiameter,

height_m,//stemCount/stemHeight,Brad: Incorrect for VegBank. This is a measurement applied to a single tree. Check with Bob

stem_height_m,//stemCount/stemHeight,"Brad: Same as for height, but applies to individuals stems, not trees. Rare."

no_of_individuals,/collectiveobservation/*/stemCount/_alt/1,"Brad: Incorrect for VegX. This is a count of number of indiiduals for an *aggregate* observation. For VegBank, I'm not sure. Not exactly the same as stemCount. An individual tree could have 3 stems but would still only count as 1. We need to check with Bob on this."

ht_first_branch_m,"/collectiveobservation/stemCount/individualplant/definedValue[*_ID/userDefined[tableName=individualplant,userDefinedName=heightFirstBranchM]:[userDefinedType=varchar]]:[@fkey=tableRecord_ID]/definedValue",Brad: Incorrect for VegBank. This is a measurement applied to a single tree. Check with Bob

basal_diam,"/collectiveobservation/stemCount/individualplant/definedValue[*_ID/userDefined[tableName=individualplant,userDefinedName=stemBasalDiameter]:[userDefinedType=varchar]]:[@fkey=tableRecord_ID]/definedValue",

stem_canopy_form,"/collectiveobservation/stemCount/individualplant/definedValue[*_ID/userDefined[tableName=individualplant,userDefinedName=stemCanopyForm]:[userDefinedType=varchar]]:[@fkey=tableRecord_ID]/definedValue",Brad: Should also be userDefined for VegBank. 

stem_canopy_position,"/collectiveobservation/stemCount/individualplant/definedValue[*_ID/userDefined[tableName=individualplant,userDefinedName=stemCanopyPosition]:[userDefinedType=varchar]]:[@fkey=tableRecord_ID]/definedValue",Brad: Should also be userDefined for VegBank. 

stem_height_first_branch_m,"/collectiveobservation/stemCount/individualplant/definedValue[*_ID/userDefined[tableName=individualplant,userDefinedName=stemHeightFirstBranchM]:[userDefinedType=varchar]]:[@fkey=tableRecord_ID]/definedValue","Brad: Should also be userDefined for VegBank. Same as for ht_first_branch_m, but applies to individuals stems, not trees. Rare."

stem_liana_infestation,"/collectiveobservation/stemCount/individualplant/definedValue[*_ID/userDefined[tableName=individualplant,userDefinedName=stemLianaInfestation]:[userDefinedType=varchar]]:[@fkey=tableRecord_ID]/definedValue",Brad: Should also be userDefined for VegBank. 

stem_tag1,/collectiveobservation/stemCount/individualplant/stemCode,"Brad: Same as tag1 & tag2, but applied to individual stems. I'm still not clear how to distinguish between methods which tag only individuals trees, and those which tag individual stems."

stem_tag2,/collectiveobservation/stemCount/individualplant/stemCode,Brad: see above

tag2,/collectiveobservation/stemCount/individualplant/stemCode/_alt/1,"Brad: See commend for tag1. Your mapping for tag2 looks correct. Probably both values would go here, only nested, with one superceding the other."

tag1,/collectiveobservation/stemCount/individualplant/stemCode/_alt/2,"Brad: Another type of code, typically a number, used by the original data provider to indicate an individual tree. These are numbers on physical tags attached to the tree. Tag2 Is the same thing, only used if the first tag was lost. Obviously not a good system as it's possible a tree tag could be lost and changed more than once."

x_position,/collectiveobservation/stemCount/individualplant/stemXPosition,"Brad: Correct for VegBank. I'm not so sure for VegX. Let's ask Nick about this. These are important, fundamental values of many tree plots, and should be accommodated within VegX."

y_position,/collectiveobservation/stemCount/individualplant/stemYPosition,Brad: See comment above for x_position

stem_dbh,/collectiveobservation/stemCount/stemDiameter,

height_m,/collectiveobservation/stemCount/stemHeight,Brad: Incorrect for VegBank. This is a measurement applied to a single tree. Check with Bob

stem_height_m,/collectiveobservation/stemCount/stemHeight,"Brad: Same as for height, but applies to individuals stems, not trees. Rare."

OBSERVATION_ID,":[collectiveobservation/*/stemCount/_alt/2=1]/{taxondetermination[ROLE_ID/aux_Role/roleCode=collector]:[interpretationType=Author,originalInterpretation=true,currentInterpretation=false,interpretationDate=epoch,*_ID/plantConcept:[*_ID/reference],*_ID/party]/museumAccessionNumber,*_ID/plotevent/*_ID/plot:[confidentialityStatus=0]/authorPlotCode/_alt/3}","Brad: Neither is correct; this is just an internal ID for table plotObservations. However, it has the important property of uniquely identifying an ""observation"", which is an individual tree, in the case of an individual observation, or a records of a species with an associated count of individuals or measurement of percent cover, in the case of aggregate observations. Not sure where to store this. Main point is that it is not part of the original data, but an auto_increment added later."