CREATE INDEX ON analytical_stem ("institutionCode", "occurrenceID" );

CREATE INDEX ON analytical_stem ("institutionCode", "subInstitutionCode", "collectionCode", "catalogNumber" );

CREATE INDEX ON analytical_stem ("institutionCode", "projectID", "plotName", "dateCollected" );

    "subInstitutionCode" text,

    "collectionCode" text,

    "catalogNumber" text,

    "occurrenceID" text,

    "projectID" integer,

    "speciesBinomialWithMorphospecies" text,

    "recordedBy" text,

    "recordNumber" text,

    "taxonOccurrenceID" text,

    "authorTaxonCode" text,

    "individualID" text,

    "authorStemCode" text

    SELECT analytical_stem."institutionCode", analytical_stem.country, analytical_stem."stateProvince", analytical_stem.county, analytical_stem."decimalLatitude", analytical_stem."decimalLongitude", analytical_stem."coordinateUncertaintyInMeters", analytical_stem.geovalid, analytical_stem."isNewWorld", analytical_stem."plotName", analytical_stem."elevationInMeters", analytical_stem."plotArea_ha", analytical_stem."samplingProtocol", analytical_stem."dateCollected", analytical_stem."higherPlantGroup", analytical_stem.family, analytical_stem.genus, analytical_stem."speciesBinomial", analytical_stem."scientificName", analytical_stem."scientificNameAuthorship", analytical_stem."scientificNameWithMorphospecies", analytical_stem.threatened, analytical_stem."identifiedBy", analytical_stem."growthForm", analytical_stem.cultivated, analytical_stem."cultivatedBasis", sum(analytical_stem."coverPercent") AS "coverPercent", count(analytical_stem."diameterBreastHeight_cm") AS "individualCount", count((analytical_stem."diameterBreastHeight_cm" >= _cm_to_m((1)::double precision))) AS "individualCount_1cm_or_more", count((analytical_stem."diameterBreastHeight_cm" >= _cm_to_m((2.5)::double precision))) AS "individualCount_2_5cm_or_more", count((analytical_stem."diameterBreastHeight_cm" >= _cm_to_m((10)::double precision))) AS "individualCount_10cm_or_more" FROM analytical_stem GROUP BY analytical_stem."institutionCode", analytical_stem.country, analytical_stem."stateProvince", analytical_stem.county, analytical_stem."decimalLatitude", analytical_stem."decimalLongitude", analytical_stem."coordinateUncertaintyInMeters", analytical_stem.geovalid, analytical_stem."isNewWorld", analytical_stem."plotName", analytical_stem."elevationInMeters", analytical_stem."plotArea_ha", analytical_stem."samplingProtocol", analytical_stem."dateCollected", analytical_stem."higherPlantGroup", analytical_stem.family, analytical_stem.genus, analytical_stem."speciesBinomial", analytical_stem."scientificName", analytical_stem."scientificNameAuthorship", analytical_stem."scientificNameWithMorphospecies", analytical_stem.threatened, analytical_stem."identifiedBy", analytical_stem."growthForm", analytical_stem.cultivated, analytical_stem."cultivatedBasis";

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

--

CREATE TABLE project (

    project_id integer NOT NULL,

    source_id integer NOT NULL,

    sourceaccessioncode text,

    projectname text,

    projectdescription text,

    startdate date,

    stopdate date,

    accessioncode text,

    CONSTRAINT project_required_key CHECK (((sourceaccessioncode IS NOT NULL) OR (projectname IS NOT NULL)))

);

--

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

--

CREATE TABLE sourcename (

    sourcename_id integer NOT NULL,

    source_id integer NOT NULL,

    system text,

    name text NOT NULL,

    matched_source_id integer

);

--

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

--

CREATE TABLE specimenreplicate (

    specimenreplicate_id integer NOT NULL,

    source_id integer NOT NULL,

    sourceaccessioncode text,

    plantobservation_id integer,

    institution_id integer,

    collectioncode_dwc text,

    catalognumber_dwc text,

    description text,

    specimen_id integer,

    accessioncode text,

    CONSTRAINT specimenreplicate_required_key CHECK ((((plantobservation_id IS NOT NULL) OR (sourceaccessioncode IS NOT NULL)) OR (catalognumber_dwc IS NOT NULL)))

);

--

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

--

COMMENT ON TABLE specimenreplicate IS 'A herbarium''s replicate of a specimen. Contains Darwin Core specimen data.';

--

-- Name: COLUMN specimenreplicate.institution_id; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN specimenreplicate.institution_id IS 'The institution (such as a museum) that the specimenreplicate is from.';

--

-- Name: COLUMN specimenreplicate.collectioncode_dwc; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN specimenreplicate.collectioncode_dwc IS 'The code for the collection that the specimenreplicate is from.';

--

    SELECT source.shortname AS "institutionCode", sourcename.name AS "subInstitutionCode", specimenreplicate.collectioncode_dwc AS "collectionCode", specimenreplicate.catalognumber_dwc AS "catalogNumber", specimenreplicate.sourceaccessioncode AS "occurrenceID", canon_place.country, canon_place.stateprovince AS "stateProvince", datasource_place.county, coordinates.latitude_deg AS "decimalLatitude", coordinates.longitude_deg AS "decimalLongitude", coordinates.coordsaccuracy_m AS "coordinateUncertaintyInMeters", canon_place.geovalid, "newWorldCountries"."isNewWorld", locationevent.project_id AS "projectID", COALESCE(location.sourceaccessioncode, location.authorlocationcode) AS "plotName", location.elevation_m AS "elevationInMeters", _m2_to_ha(location.area_m2) AS "plotArea_ha", method.name AS "samplingProtocol", COALESCE(locationevent.obsstartdate, aggregateoccurrence.collectiondate) AS "dateCollected", family_higher_plant_group.higher_plant_group AS "higherPlantGroup", accepted_taxonverbatim.family, accepted_taxonverbatim.genus, ((accepted_taxonverbatim.genus || ' '::text) || accepted_taxonverbatim.specific_epithet) AS "speciesBinomial", COALESCE(accepted_taxonverbatim.taxonomicname, accepted_taxonverbatim.taxonname) AS "scientificName", accepted_taxonverbatim.author AS "scientificNameAuthorship", NULLIF(array_to_string(ARRAY[COALESCE(accepted_taxonverbatim.genus, accepted_taxonverbatim.family), COALESCE(accepted_taxonverbatim.specific_epithet, parsed_taxonverbatim.morphospecies)], ' '::text), ''::text) AS "speciesBinomialWithMorphospecies", NULLIF(array_to_string(ARRAY[accepted_taxonverbatim.taxonomicname, parsed_taxonverbatim.morphospecies], ' '::text), ''::text) AS "scientificNameWithMorphospecies", (threatened_taxonlabel.taxonlabel_id IS NOT NULL) AS threatened, identifiedby.fullname AS "identifiedBy", taxonoccurrence.growthform AS "growthForm", ((cultivated_family_locations.country IS NOT NULL) OR _or(taxonoccurrence.iscultivated, location.iscultivated)) AS cultivated, 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", collector.fullname AS "recordedBy", plantobservation.collectionnumber AS "recordNumber", _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", plantobservation.sourceaccessioncode AS "individualID", stemobservation.authorstemcode AS "authorStemCode" FROM ((((((((((((((((((((((((((((source JOIN location USING (source_id)) LEFT JOIN locationplace USING (location_id)) LEFT JOIN place datasource_place USING (place_id)) LEFT JOIN place canon_place ON ((canon_place.place_id = datasource_place.canon_place_id))) LEFT JOIN coordinates ON ((coordinates.coordinates_id = canon_place.coordinates_id))) LEFT JOIN "newWorld".iso_code_gadm ON ((iso_code_gadm."GADM country" = canon_place.country))) LEFT JOIN "newWorld"."newWorldCountries" ON (("newWorldCountries"."isoCode" = iso_code_gadm."2-digit iso code"))) LEFT JOIN locationevent USING (location_id)) LEFT JOIN project USING (project_id)) LEFT JOIN method USING (method_id)) LEFT JOIN taxonoccurrence USING (locationevent_id)) LEFT JOIN party collector ON ((collector.party_id = taxonoccurrence.collector_id))) LEFT JOIN taxondetermination USING (taxonoccurrence_id)) LEFT JOIN party identifiedby ON ((identifiedby.party_id = taxondetermination.party_id))) LEFT JOIN taxonverbatim datasource_taxonverbatim USING (taxonverbatim_id)) LEFT JOIN taxonlabel datasource_taxonlabel USING (taxonlabel_id)) LEFT JOIN taxonlabel parsed_taxonlabel ON ((parsed_taxonlabel.taxonlabel_id = datasource_taxonlabel.matched_label_id))) LEFT JOIN taxonverbatim parsed_taxonverbatim ON ((parsed_taxonverbatim.taxonlabel_id = parsed_taxonlabel.taxonlabel_id))) LEFT JOIN taxonlabel accepted_taxonlabel ON ((accepted_taxonlabel.taxonlabel_id = datasource_taxonlabel.canon_label_id))) LEFT JOIN taxonverbatim accepted_taxonverbatim ON ((accepted_taxonverbatim.taxonlabel_id = accepted_taxonlabel.taxonlabel_id))) LEFT JOIN threatened_taxonlabel ON ((threatened_taxonlabel.taxonlabel_id = accepted_taxonlabel.taxonlabel_id))) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN family_higher_plant_group ON ((family_higher_plant_group.family = accepted_taxonverbatim.family))) LEFT JOIN cultivated_family_locations ON (((cultivated_family_locations.family = accepted_taxonverbatim.family) AND (cultivated_family_locations.country = canon_place.country)))) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN stemobservation USING (plantobservation_id)) LEFT JOIN specimenreplicate USING (plantobservation_id)) LEFT JOIN sourcename ON ((sourcename.sourcename_id = specimenreplicate.institution_id)));

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

CREATE INDEX "analytical_stem_institutionCode_occurrenceID_idx" ON analytical_stem USING btree ("institutionCode", "occurrenceID");

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

--

CREATE INDEX "analytical_stem_institutionCode_projectID_plotName_dateColl_idx" ON analytical_stem USING btree ("institutionCode", "projectID", "plotName", "dateCollected");

--

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

--

CREATE INDEX "analytical_stem_institutionCode_subInstitutionCode_collecti_idx" ON analytical_stem USING btree ("institutionCode", "subInstitutionCode", "collectionCode", "catalogNumber");

--

-- Name: project; Type: ACL; Schema: public; Owner: -

--

REVOKE ALL ON TABLE project FROM PUBLIC;

REVOKE ALL ON TABLE project FROM bien;

GRANT ALL ON TABLE project TO bien;

GRANT SELECT ON TABLE project TO bien_read;

--

-- Name: sourcename; Type: ACL; Schema: public; Owner: -

--

REVOKE ALL ON TABLE sourcename FROM PUBLIC;

REVOKE ALL ON TABLE sourcename FROM bien;

GRANT ALL ON TABLE sourcename TO bien;

GRANT SELECT ON TABLE sourcename TO bien_read;

--

-- Name: specimenreplicate; Type: ACL; Schema: public; Owner: -

--

REVOKE ALL ON TABLE specimenreplicate FROM PUBLIC;

REVOKE ALL ON TABLE specimenreplicate FROM bien;

GRANT ALL ON TABLE specimenreplicate TO bien;

GRANT SELECT ON TABLE specimenreplicate TO bien_read;

--