-- Name: sync_darwin_core_to_view(); Type: FUNCTION; Schema: public; Owner: -

--

--

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

--

CREATE TABLE darwin_core (

    `institutionCode` varchar(255),

    `subInstitutionCode` varchar(255),

    `collectionCode` varchar(255),

    `catalogNumber` varchar(255),

    `occurrenceID` varchar(255),

    country varchar(255),

    `stateProvince` varchar(255),

    county varchar(255),

    `decimalLatitude` double precision,

    `decimalLongitude` double precision,

    `coordinateUncertaintyInMeters` double precision,

    geovalid int(1),

    `isNewWorld` int(1),

    `projectID` int(11),

    `plotName` varchar(255),

    `elevationInMeters` double precision,

    `plotArea_ha` double precision,

    `samplingProtocol` varchar(255),

    `dateCollected` date,

    `higherPlantGroup` varchar(255),

    family varchar(255),

    genus varchar(255),

    `speciesBinomial` varchar(255),

    `scientificName` varchar(255),

    `scientificNameAuthorship` varchar(255),

    `speciesBinomialWithMorphospecies` varchar(255),

    `scientificNameWithMorphospecies` varchar(255),

    threatened int(1),

    `identifiedBy` varchar(255),

    `growthForm` varchar(255),

    cultivated int(1),

    `cultivatedBasis` varchar(255),

    `recordedBy` varchar(255),

    `recordNumber` varchar(255),

    `coverPercent` double precision,

    `diameterBreastHeight_cm` double precision,

    height_m double precision,

    tag varchar(255),

    `organismX_m` double precision,

    `organismY_m` double precision,

    `taxonOccurrenceID` varchar(255),

    `authorTaxonCode` varchar(255),

    `individualID` varchar(255),

    `authorStemCode` varchar(255)

);

--

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

--

--

-- Data for Name: darwin_core; Type: TABLE DATA; Schema: public; Owner: -

--

--

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

--

CREATE INDEX `darwin_core_institutionCode_catalogNumber_idx` ON darwin_core  (`institutionCode`, `catalogNumber`);

--

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

--

CREATE INDEX `darwin_core_institutionCode_occurrenceID_idx` ON darwin_core  (`institutionCode`, `occurrenceID`);

--

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

--

CREATE INDEX `darwin_core_institutionCode_projectID_plotName_dateCollecte_idx` ON darwin_core  (`institutionCode`, `projectID`, `plotName`, `dateCollected`);

--

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

--

CREATE INDEX `darwin_core_institutionCode_subInstitutionCode_collectionCo_idx` ON darwin_core  (`institutionCode`, `subInstitutionCode`, `collectionCode`, `catalogNumber`);

--

-- Name: sync_darwin_core_to_view(); Type: FUNCTION; Schema: public; Owner: -

--

CREATE FUNCTION sync_darwin_core_to_view() RETURNS void

    LANGUAGE sql

    AS $$

DROP TABLE IF EXISTS darwin_core;

CREATE TABLE darwin_core AS SELECT * FROM darwin_core_view;

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

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

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

CREATE INDEX ON darwin_core ("institutionCode", "catalogNumber" );

$$;

--

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

--

CREATE TABLE darwin_core (

    "institutionCode" text,

    "subInstitutionCode" text,

    "collectionCode" text,

    "catalogNumber" text,

    "occurrenceID" text,

    country text,

    "stateProvince" text,

    county text,

    "decimalLatitude" double precision,

    "decimalLongitude" double precision,

    "coordinateUncertaintyInMeters" double precision,

    geovalid boolean,

    "isNewWorld" boolean,

    "projectID" integer,

    "plotName" text,

    "elevationInMeters" double precision,

    "plotArea_ha" double precision,

    "samplingProtocol" text,

    "dateCollected" date,

    "higherPlantGroup" higher_plant_group,

    family text,

    genus text,

    "speciesBinomial" text,

    "scientificName" text,

    "scientificNameAuthorship" text,

    "speciesBinomialWithMorphospecies" text,

    "scientificNameWithMorphospecies" text,

    threatened boolean,

    "identifiedBy" text,

    "growthForm" growthform,

    cultivated boolean,

    "cultivatedBasis" text,

    "recordedBy" text,

    "recordNumber" text,

    "coverPercent" double precision,

    "diameterBreastHeight_cm" double precision,

    height_m double precision,

    tag text,

    "organismX_m" double precision,

    "organismY_m" double precision,

    "taxonOccurrenceID" text,

    "authorTaxonCode" text,

    "individualID" text,

    "authorStemCode" text

);

--

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

--

CREATE VIEW darwin_core_view AS

    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, parent_plot_event.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 locationevent parent_plot_event ON ((parent_plot_event.locationevent_id = locationevent.parent_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)));

--

-- Data for Name: darwin_core; Type: TABLE DATA; Schema: public; Owner: -

--

--

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

--

CREATE INDEX "darwin_core_institutionCode_catalogNumber_idx" ON darwin_core USING btree ("institutionCode", "catalogNumber");

--

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

--

CREATE INDEX "darwin_core_institutionCode_occurrenceID_idx" ON darwin_core USING btree ("institutionCode", "occurrenceID");

--

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

--

CREATE INDEX "darwin_core_institutionCode_projectID_plotName_dateCollecte_idx" ON darwin_core USING btree ("institutionCode", "projectID", "plotName", "dateCollected");

--

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

--

CREATE INDEX "darwin_core_institutionCode_subInstitutionCode_collectionCo_idx" ON darwin_core USING btree ("institutionCode", "subInstitutionCode", "collectionCode", "catalogNumber");

--

$(mk_analytical_table darwin_core)