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

--

CREATE FUNCTION sync_analytical_aggregate_to_view() RETURNS void

    LANGUAGE sql

    AS $$

DROP TABLE IF EXISTS analytical_aggregate;

CREATE TABLE analytical_aggregate AS SELECT * FROM analytical_aggregate_view LIMIT 0;

GRANT SELECT ON TABLE analytical_aggregate TO bien_read;

GRANT SELECT ON TABLE analytical_aggregate_view TO bien_read;

ALTER TABLE analytical_aggregate ADD FOREIGN KEY (datasource) REFERENCES source (shortname) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE analytical_aggregate ALTER COLUMN "datasource" SET NOT NULL;

ALTER TABLE analytical_aggregate ALTER COLUMN "locationID" SET NOT NULL;

CREATE INDEX ON analytical_aggregate ("datasource", "locationID", "dateCollected" );

$$;

--

    analytical_aggregate_view_def text := NULL;

        analytical_aggregate_view_def :=

            'CREATE OR REPLACE VIEW analytical_aggregate_view AS '

            ||pg_get_viewdef('analytical_aggregate_view');

        DROP VIEW analytical_aggregate_view;

    EXCEPTION

        WHEN undefined_table THEN NULL;

    END;

    BEGIN

    IF analytical_aggregate_view_def IS NOT NULL THEN

        EXECUTE analytical_aggregate_view_def;

        GRANT SELECT ON TABLE analytical_aggregate_view TO bien_read;

    END IF;

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

--

CREATE TABLE analytical_aggregate (

    datasource text NOT NULL,

    country text,

    "stateProvince" text,

    county text,

    "decimalLatitude" double precision,

    "decimalLongitude" double precision,

    "coordinateUncertaintyInMeters" double precision,

    geovalid_bien integer,

    "isNewWorld_bien" integer,

    "locationID" text NOT NULL,

    "elevationInMeters" double precision,

    "plotArea_ha" double precision,

    "samplingProtocol" text,

    "dateCollected" date,

    "higherPlantGroup_bien" higher_plant_group,

    family text,

    genus text,

    "speciesBinomialWithMorphospecies" text,

    "taxonName" text,

    "scientificNameAuthorship" text,

    threatened_bien integer,

    "identifiedBy" text,

    "growthForm" growthform,

    cultivated_bien integer,

    "cultivatedBasis_bien" text,

    "coverPercent" double precision,

    "individualCount" bigint,

    "individualCount_1cm_or_more" bigint,

    "individualCount_2_5cm_or_more" bigint,

    "individualCount_10cm_or_more" bigint

);

--

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

--

CREATE VIEW analytical_aggregate_view AS

    SELECT analytical_stem.datasource, analytical_stem.country, analytical_stem."stateProvince", analytical_stem.county, analytical_stem."decimalLatitude", analytical_stem."decimalLongitude", analytical_stem."coordinateUncertaintyInMeters", analytical_stem.geovalid_bien, analytical_stem."isNewWorld_bien", analytical_stem."locationID", analytical_stem."elevationInMeters", analytical_stem."plotArea_ha", analytical_stem."samplingProtocol", analytical_stem."dateCollected", analytical_stem."higherPlantGroup_bien", analytical_stem.family, analytical_stem.genus, analytical_stem."speciesBinomialWithMorphospecies", analytical_stem."taxonName", analytical_stem."scientificNameAuthorship", analytical_stem.threatened_bien, analytical_stem."identifiedBy", analytical_stem."growthForm", analytical_stem.cultivated_bien, analytical_stem."cultivatedBasis_bien", 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.datasource, analytical_stem.country, analytical_stem."stateProvince", analytical_stem.county, analytical_stem."decimalLatitude", analytical_stem."decimalLongitude", analytical_stem."coordinateUncertaintyInMeters", analytical_stem.geovalid_bien, analytical_stem."isNewWorld_bien", analytical_stem."locationID", analytical_stem."elevationInMeters", analytical_stem."plotArea_ha", analytical_stem."samplingProtocol", analytical_stem."dateCollected", analytical_stem."higherPlantGroup_bien", analytical_stem.family, analytical_stem.genus, analytical_stem."speciesBinomialWithMorphospecies", analytical_stem."taxonName", analytical_stem."scientificNameAuthorship", analytical_stem.threatened_bien, analytical_stem."identifiedBy", analytical_stem."growthForm", analytical_stem.cultivated_bien, analytical_stem."cultivatedBasis_bien";

--

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

--

--

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

--

CREATE INDEX "analytical_aggregate_datasource_locationID_dateCollected_idx" ON analytical_aggregate USING btree (datasource, "locationID", "dateCollected");

--

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

--

ALTER TABLE ONLY analytical_aggregate

    ADD CONSTRAINT analytical_aggregate_datasource_fkey FOREIGN KEY (datasource) REFERENCES source(shortname) ON UPDATE CASCADE ON DELETE CASCADE;

--

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

--

REVOKE ALL ON TABLE analytical_aggregate FROM PUBLIC;

REVOKE ALL ON TABLE analytical_aggregate FROM bien;

GRANT ALL ON TABLE analytical_aggregate TO bien;

GRANT SELECT ON TABLE analytical_aggregate TO bien_read;

--

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

--

REVOKE ALL ON TABLE analytical_aggregate_view FROM PUBLIC;

REVOKE ALL ON TABLE analytical_aggregate_view FROM bien;

GRANT ALL ON TABLE analytical_aggregate_view TO bien;

GRANT SELECT ON TABLE analytical_aggregate_view TO bien_read;

--