CREATE INDEX ON analytical_stem ("datasource", "specimenHolderInstitutions", "collection", "accessionNumber");

    "accessionNumber" text,

    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."individualCode", analytical_stem."elevationInMeters", analytical_stem."plotArea_ha", analytical_stem."samplingProtocol", 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."higherPlantGroup_bien", analytical_stem.family, analytical_stem.genus, analytical_stem."speciesBinomialWithMorphospecies", analytical_stem."taxonName", analytical_stem."scientificNameAuthorship", analytical_stem."growthForm", analytical_stem."reproductiveCondition", analytical_stem.threatened_bien, 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."authorStemCode", analytical_stem."specimenHolderInstitutions", analytical_stem.collection, analytical_stem."accessionNumber", analytical_stem."occurrenceID" FROM analytical_stem;

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

    SELECT source.shortname AS datasource, sourcelist.name AS "specimenHolderInstitutions", specimenreplicate.collectioncode_dwc AS collection, specimenreplicate.catalognumber_dwc AS "accessionNumber", specimenreplicate.sourceaccessioncode AS "occurrenceID", COALESCE(geoscrub_output."acceptedCountry", place.country) AS country, COALESCE(geoscrub_output."acceptedStateProvince", place.stateprovince) AS "stateProvince", COALESCE(geoscrub_output."acceptedCounty", place.county) AS county, location.locationnarrative AS locality, CASE WHEN (coordinates.latitude_deg IS NOT NULL) THEN coordinates.latitude_deg ELSE county_centroids.latitude END AS "decimalLatitude", CASE WHEN (coordinates.latitude_deg IS NOT NULL) THEN coordinates.longitude_deg ELSE county_centroids.longitude END AS "decimalLongitude", CASE WHEN (coordinates.latitude_deg IS NOT NULL) THEN coordinates.coordsaccuracy_m ELSE _km_to_m(county_centroids.error_km) END AS "coordinateUncertaintyInMeters", CASE WHEN (coordinates.latitude_deg IS NOT NULL) THEN 'source data'::coordinatesource WHEN (county_centroids.row_num IS NOT NULL) THEN 'georeferencing'::coordinatesource ELSE NULL::coordinatesource END AS "coordinateSource_bien", CASE WHEN (coordinates.latitude_deg IS NOT NULL) THEN NULL::text WHEN (county_centroids.row_num IS NOT NULL) THEN 'county centroid'::text ELSE NULL::text END AS "georeferenceProtocol_bien", (geoscrub_output.geovalid)::integer AS geovalid_bien, ("newWorldCountries"."isNewWorld")::integer AS "isNewWorld_bien", COALESCE(project.sourceaccessioncode, project.projectname) AS "projectID", COALESCE(location.sourceaccessioncode, NULLIF(pg_catalog.concat_ws('; '::text, COALESCE(parent_location.sourceaccessioncode, parent_location.authorlocationcode), location.authorlocationcode), ''::text)) AS "locationID", COALESCE(parent_location.authorlocationcode, location.authorlocationcode) AS "locationName", CASE WHEN (parent_location.location_id IS NOT NULL) THEN location.authorlocationcode ELSE NULL::text END AS subplot, plantobservation.authorplantcode AS "individualCode", COALESCE(location.elevation_m, parent_location.elevation_m) AS "elevationInMeters", _m2_to_ha(COALESCE(location.area_m2, parent_location.area_m2)) AS "plotArea_ha", method.name AS "samplingProtocol", COALESCE(locationevent.temperature_c, parent_event.temperature_c) AS "temperature_C", COALESCE(locationevent.precipitation_m, parent_event.precipitation_m) AS precipitation_m, stratum.stratumname AS stratum__name, COALESCE(locationevent__communities__array(locationevent.locationevent_id), locationevent__communities__array(parent_event.locationevent_id)) AS communities, COALESCE(locationevent__contributors__array(locationevent.locationevent_id), locationevent__contributors__array(parent_event.locationevent_id)) AS plot__collectors, collector.fullname AS "recordedBy", plantobservation.authorplantcode AS "recordNumber", COALESCE(locationevent.obsstartdate, parent_event.obsstartdate, aggregateoccurrence.collectiondate) 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", "ScrubbedTaxon"."acceptedFamily" AS family, "ScrubbedTaxon"."acceptedGenus" 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, location.iscultivated)))::integer AS cultivated_bien, CASE WHEN (taxonoccurrence.iscultivated IS NOT NULL) THEN taxonoccurrence.cultivatedbasis WHEN (location.iscultivated 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", stemobservation.authorstemcode AS "authorStemCode" FROM (((((((((((((((((((((((((((((source JOIN location USING (source_id)) LEFT JOIN locationevent USING (location_id)) LEFT JOIN location parent_location ON ((parent_location.location_id = location.parent_id))) LEFT JOIN locationplace ON ((locationplace.location_id = COALESCE(parent_location.location_id, location.location_id)))) LEFT JOIN place USING (place_id)) LEFT JOIN coordinates USING (coordinates_id)) LEFT JOIN geoscrub.geoscrub_output ON ((((((ARRAY[geoscrub_output."decimalLatitude"] = ARRAY[coordinates.latitude_deg]) AND (ARRAY[geoscrub_output."decimalLongitude"] = ARRAY[coordinates.longitude_deg])) AND (ARRAY[geoscrub_output.country] = ARRAY[place.country])) AND (ARRAY[geoscrub_output."stateProvince"] = ARRAY[place.stateprovince])) AND (ARRAY[geoscrub_output.county] = ARRAY[place.county])))) LEFT JOIN "newWorld".iso_code_gadm ON ((iso_code_gadm."*GADM country" = COALESCE(geoscrub_output."acceptedCountry", place.country)))) LEFT JOIN "newWorld"."newWorldCountries" ON (("newWorldCountries"."*isoCode" = iso_code_gadm."*2-digit iso code"))) LEFT JOIN geoscrub.county_centroids ON ((((place.country = 'United States'::text) AND (county_centroids.state = COALESCE(geoscrub_output."acceptedStateProvince", place.stateprovince))) AND (county_centroids.county = COALESCE(geoscrub_output."acceptedCounty", place.county))))) LEFT JOIN taxonoccurrence USING (locationevent_id)) LEFT JOIN locationevent parent_event ON ((parent_event.locationevent_id = locationevent.parent_id))) LEFT JOIN project ON ((project.project_id = COALESCE(locationevent.project_id, parent_event.project_id)))) LEFT JOIN stratum ON ((stratum.stratum_id = COALESCE(locationevent.stratum_id, parent_event.stratum_id)))) LEFT JOIN method ON ((method.method_id = COALESCE(locationevent.method_id, parent_event.method_id)))) 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.is_datasource_current))) 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 = COALESCE(geoscrub_output."acceptedCountry", place.country))))) LEFT JOIN threatened_taxonlabel USING (taxonlabel_id)) ORDER BY source.shortname;

CREATE INDEX "analytical_stem_datasource_specimenHolderInstitutions_colle_idx" ON analytical_stem USING btree (datasource, "specimenHolderInstitutions", collection, "accessionNumber");