CREATE INDEX ON analytical_stem ("speciesBinomialWithMorphospecies");

    family text,

    genus text,

    "speciesBinomialWithMorphospecies" text,

    "taxonName" text,

    "scientificNameAuthorship" text,

    SELECT analytical_stem."speciesBinomialWithMorphospecies" AS species, analytical_stem."decimalLatitude" AS latitude__deg, analytical_stem."decimalLongitude" AS longitude__deg, analytical_stem."coordinateUncertaintyInMeters" AS coords__uncertainty__m FROM analytical_stem WHERE (((((analytical_stem."speciesBinomialWithMorphospecies" = ANY (ARRAY['Juniperus scopulorum'::text, 'Picea engelmannii'::text, 'Pinus contorta'::text, 'Pinus edulis'::text, 'Pinus ponderosa'::text, 'Populus tremuloides'::text, 'Pseudotsuga menziesii'::text, 'Quercus gambelii'::text])) AND (NOT COALESCE((analytical_stem.cultivated_bien)::boolean, false))) AND COALESCE((analytical_stem.geovalid_bien)::boolean, true)) AND (analytical_stem."decimalLatitude" IS NOT NULL)) AND (analytical_stem."decimalLongitude" IS NOT NULL)) ORDER BY analytical_stem."speciesBinomialWithMorphospecies";

    SELECT analytical_stem.taxon_occurrence__pkey AS occurrence_id, analytical_stem."speciesBinomialWithMorphospecies" AS species, analytical_stem."decimalLatitude" AS latitude__deg, analytical_stem."decimalLongitude" AS longitude__deg FROM analytical_stem WHERE ((((((NOT COALESCE((analytical_stem.cultivated_bien)::boolean, false)) AND COALESCE((analytical_stem.geovalid_bien)::boolean, true)) AND (analytical_stem."speciesBinomialWithMorphospecies" IS NOT NULL)) AND (analytical_stem."decimalLatitude" IS NOT NULL)) AND (analytical_stem."decimalLongitude" IS NOT NULL)) AND COALESCE((analytical_stem."coordinateUncertaintyInMeters" <= _km_to_m((10)::double precision)), true));

    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.location__cultivated__bien, analytical_stem."eventDate", analytical_stem."elevationInMeters", analytical_stem."slopeAspect", analytical_stem."slopeGradient", analytical_stem."plotArea_ha", analytical_stem."samplingProtocol", analytical_stem."temperature_C", analytical_stem.precipitation_m, analytical_stem.stratum__name, analytical_stem.communities, analytical_stem.plot__collectors, 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.family, analytical_stem.genus, analytical_stem."speciesBinomialWithMorphospecies", analytical_stem."taxonName", analytical_stem."scientificNameAuthorship", analytical_stem."growthForm", analytical_stem."reproductiveCondition", 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."aggregateOrganismObservationID", analytical_stem."individualObservationID", analytical_stem."individualCode", analytical_stem."individualCount", analytical_stem."authorStemCode" FROM analytical_stem;

-- Name: VIEW analytical_plot; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON VIEW analytical_plot IS 'contains all of the analytical_stem columns, minus specimenHolderInstitutions, collection, accessionNumber, occurrenceID';

--

    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.location__cultivated__bien, analytical_stem."elevationInMeters", analytical_stem."specimenHolderInstitutions", analytical_stem.collection, analytical_stem."accessionNumber", analytical_stem."occurrenceID", 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.family, analytical_stem.genus, analytical_stem."taxonName", analytical_stem."scientificNameAuthorship", analytical_stem."growthForm", analytical_stem."reproductiveCondition", analytical_stem.cultivated_bien, analytical_stem."cultivatedBasis_bien", analytical_stem."occurrenceRemarks" FROM analytical_stem;

    SELECT "plot.**".datasource, "plot.**".country, "plot.**"."stateProvince", "plot.**".county, "plot.**".locality, "plot.**"."decimalLatitude", "plot.**"."decimalLongitude", "plot.**"."coordinateUncertaintyInMeters", "plot.**"."coordinateSource_bien", "plot.**"."georeferenceProtocol_bien", "plot.**".geovalid_bien, "plot.**"."isNewWorld_bien", "plot.**"."projectID", "plot.**"."locationID", "plot.**"."locationName", "plot.**".subplot, "plot.**".location__cultivated__bien, "plot.**".locationevent__pkey, "plot.**"."eventDate", "plot.**"."elevationInMeters", "plot.**"."slopeAspect", "plot.**"."slopeGradient", "plot.**"."plotArea_ha", "plot.**"."samplingProtocol", "plot.**"."temperature_C", "plot.**".precipitation_m, "plot.**".stratum__name, "plot.**".communities, "plot.**".plot__collectors, sourcelist.name AS "specimenHolderInstitutions", specimenreplicate.collectioncode_dwc AS collection, specimenreplicate.catalognumber_dwc AS "accessionNumber", specimenreplicate.sourceaccessioncode AS "occurrenceID", collector.fullname AS "recordedBy", plantobservation.authorplantcode AS "recordNumber", COALESCE(aggregateoccurrence.collectiondate, "plot.**"."eventDate") AS "dateCollected", taxonverbatim.family AS family_verbatim, COALESCE(taxonverbatim.taxonomicname, NULLIF(pg_catalog.concat_ws(' '::text, taxonverbatim.taxonname, taxonverbatim.author), ''::text), taxonlabel.taxonomicname) AS "scientificName_verbatim", identifiedby.fullname AS "identifiedBy", taxondetermination.determinationdate AS "dateIdentified", taxondetermination.notes AS "identificationRemarks", "ScrubbedTaxon"."matchedFamily" AS family_matched, "ScrubbedTaxon"."matchedTaxonName" AS "taxonName_matched", "ScrubbedTaxon"."matchedScientificNameAuthorship" AS "scientificNameAuthorship_matched", family_higher_plant_group.higher_plant_group AS "higherPlantGroup_bien", COALESCE("ScrubbedTaxon"."acceptedFamily", "ScrubbedTaxon"."matchedFamily") AS family, COALESCE("ScrubbedTaxon"."acceptedGenus", "ScrubbedTaxon"."matchedGenus") AS genus, COALESCE(COALESCE(NULLIF(pg_catalog.concat_ws(' '::text, COALESCE("ScrubbedTaxon"."acceptedGenus", "ScrubbedTaxon"."acceptedFamily"), "ScrubbedTaxon"."acceptedSpecificEpithet", "ScrubbedTaxon"."morphospeciesSuffix"), ''::text), "ScrubbedTaxon"."acceptedTaxonName"), COALESCE(NULLIF(pg_catalog.concat_ws(' '::text, COALESCE("ScrubbedTaxon"."matchedGenus", "ScrubbedTaxon"."matchedFamily"), "ScrubbedTaxon"."matchedSpecificEpithet", "ScrubbedTaxon"."morphospeciesSuffix"), ''::text), "ScrubbedTaxon"."matchedTaxonName"), "ScrubbedTaxon"."concatenatedScientificName") AS "speciesBinomialWithMorphospecies", "ScrubbedTaxon"."acceptedTaxonName" AS "taxonName", "ScrubbedTaxon"."acceptedScientificNameAuthorship" AS "scientificNameAuthorship", taxonoccurrence.growthform AS "growthForm", plantobservation.reproductivecondition AS "reproductiveCondition", ((threatened_taxonlabel.taxonlabel_id IS NOT NULL))::integer AS threatened_bien, (((cultivated_family_locations.country IS NOT NULL) OR _or(taxonoccurrence.iscultivated, "plot.**".location__cultivated__bien)))::integer AS cultivated_bien, CASE WHEN (taxonoccurrence.iscultivated IS NOT NULL) THEN taxonoccurrence.cultivatedbasis WHEN ("plot.**".location__cultivated__bien IS NOT NULL) THEN NULL::text ELSE NULL::text END AS "cultivatedBasis_bien", aggregateoccurrence.notes AS "occurrenceRemarks", _fraction_to_percent(aggregateoccurrence.cover_fraction) AS "coverPercent", _m_to_cm(stemobservation.diameterbreastheight_m) AS "diameterBreastHeight_cm", stemobservation.height_m, stemobservation.tag, stemobservation.xposition_m AS "organismX_m", stemobservation.yposition_m AS "organismY_m", taxonoccurrence.sourceaccessioncode AS "taxonOccurrenceID", taxonoccurrence.authortaxoncode AS "authorTaxonCode", aggregateoccurrence.sourceaccessioncode AS "aggregateOrganismObservationID", plantobservation.sourceaccessioncode AS "individualObservationID", plantobservation.authorplantcode AS "individualCode", aggregateoccurrence.count AS "individualCount", stemobservation.authorstemcode AS "authorStemCode" FROM ((((((((((((((("plot.**" LEFT JOIN taxonoccurrence ON ((taxonoccurrence.locationevent_id = "plot.**".locationevent__pkey))) LEFT JOIN party collector ON ((collector.party_id = taxonoccurrence.collector_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)) LEFT JOIN sourcelist ON ((sourcelist.sourcelist_id = specimenreplicate.institution_id))) LEFT JOIN taxondetermination ON (((taxondetermination.taxonoccurrence_id = taxonoccurrence.taxonoccurrence_id) AND taxondetermination.iscurrent))) LEFT JOIN party identifiedby ON ((identifiedby.party_id = taxondetermination.party_id))) LEFT JOIN taxonverbatim USING (taxonverbatim_id)) LEFT JOIN taxonlabel USING (taxonlabel_id)) LEFT JOIN "TNRS"."ScrubbedTaxon" ON (("ScrubbedTaxon"."concatenatedScientificName" = taxonlabel.taxonomicname))) LEFT JOIN family_higher_plant_group ON ((family_higher_plant_group.family = "ScrubbedTaxon"."acceptedFamily"))) LEFT JOIN cultivated_family_locations ON (((cultivated_family_locations.family = "ScrubbedTaxon"."acceptedFamily") AND (cultivated_family_locations.country = "plot.**".country)))) LEFT JOIN threatened_taxonlabel USING (taxonlabel_id));

    SELECT analytical_stem.datasource AS source, analytical_stem."specimenHolderInstitutions" AS first_publisher, analytical_stem."decimalLatitude" AS latitude_deg, analytical_stem."decimalLongitude" AS longitude_deg, analytical_stem.geovalid_bien AS geovalid, analytical_stem."speciesBinomialWithMorphospecies" AS species_taxon_name, analytical_stem."higherPlantGroup_bien" AS higher_plant_group FROM analytical_stem WHERE (analytical_stem."higherPlantGroup_bien" IS NOT NULL);

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

CREATE INDEX "analytical_stem_speciesBinomialWithMorphospecies_idx" ON analytical_stem USING btree ("speciesBinomialWithMorphospecies");