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

--

--

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

--

CREATE TABLE aggregated_analytical_db (

    `institutionCode` text,

    country text,

    `stateProvince` text,

    county text,

    `decimalLatitude` double precision,

    `decimalLongitude` double precision,

    `plotName` text,

    `elevationInMeters` double precision,

    `plotArea_ha` double precision,

    `samplingProtocol` text,

    `dateCollected` date,

    family text,

    genus text,

    `speciesBinomial` text,

    `scientificName` text,

    `scientificNameAuthorship` text,

    `scientificNameWithMorphospecies` text,

    `individualCount` text,

    `individualCount_1_to_2_5cm` text,

    `individualCount_2_5_to_10cm` text,

    `individualCount_10cm_or_more` text

);

--

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

--

CREATE TABLE analytical_db (

    `institutionCode` text,

    country text,

    `stateProvince` text,

    county text,

    `decimalLatitude` double precision,

    `decimalLongitude` double precision,

    `plotName` text,

    `elevationInMeters` double precision,

    `plotArea_ha` double precision,

    `samplingProtocol` text,

    `dateCollected` date,

    family text,

    genus text,

    `speciesBinomial` text,

    `scientificName` text,

    `scientificNameAuthorship` text,

    `scientificNameWithMorphospecies` text,

    `identifiedBy` text,

    `growthForm` text,

    cultivated int(1),

    `cultivatedBasis` text,

    `coverPercent` double precision,

    `diameterBreastHeight_cm` double precision,

    height_m double precision,

    tag text,

    `organismX_m` double precision,

    `organismY_m` double precision,

    `recordedBy` text,

    `recordNumber` text

);

--

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

--

--

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

--

CREATE FUNCTION sync_aggregated_analytical_db_to_view() RETURNS void

    LANGUAGE sql

    AS $$

DROP TABLE IF EXISTS aggregated_analytical_db;

CREATE TABLE aggregated_analytical_db AS SELECT * FROM aggregated_analytical_db_view;

$$;

--

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

--

CREATE TABLE aggregated_analytical_db (

    "institutionCode" text,

    country text,

    "stateProvince" text,

    county text,

    "decimalLatitude" double precision,

    "decimalLongitude" double precision,

    "plotName" text,

    "elevationInMeters" double precision,

    "plotArea_ha" double precision,

    "samplingProtocol" text,

    "dateCollected" date,

    family text,

    genus text,

    "speciesBinomial" text,

    "scientificName" text,

    "scientificNameAuthorship" text,

    "scientificNameWithMorphospecies" text,

    "individualCount" bigint,

    "individualCount_1_to_2_5cm" bigint,

    "individualCount_2_5_to_10cm" bigint,

    "individualCount_10cm_or_more" bigint

);

--

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

--

CREATE TABLE analytical_db (

    "institutionCode" text,

    country text,

    "stateProvince" text,

    county text,

    "decimalLatitude" double precision,

    "decimalLongitude" double precision,

    "plotName" text,

    "elevationInMeters" double precision,

    "plotArea_ha" double precision,

    "samplingProtocol" text,

    "dateCollected" date,

    family text,

    genus text,

    "speciesBinomial" text,

    "scientificName" text,

    "scientificNameAuthorship" text,

    "scientificNameWithMorphospecies" text,

    "identifiedBy" text,

    "growthForm" growthform,

    cultivated boolean,

    "cultivatedBasis" text,

    "coverPercent" double precision,

    "diameterBreastHeight_cm" double precision,

    height_m double precision,

    tag text,

    "organismX_m" double precision,

    "organismY_m" double precision,

    "recordedBy" text,

    "recordNumber" text

);

--

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

--

CREATE VIEW aggregated_analytical_db_view AS

    SELECT analytical_db."institutionCode", analytical_db.country, analytical_db."stateProvince", analytical_db.county, analytical_db."decimalLatitude", analytical_db."decimalLongitude", analytical_db."plotName", analytical_db."elevationInMeters", analytical_db."plotArea_ha", analytical_db."samplingProtocol", analytical_db."dateCollected", analytical_db.family, analytical_db.genus, analytical_db."speciesBinomial", analytical_db."scientificName", analytical_db."scientificNameAuthorship", analytical_db."scientificNameWithMorphospecies", count(analytical_db."diameterBreastHeight_cm") AS "individualCount", count(((analytical_db."diameterBreastHeight_cm" >= _cm_to_m((1)::double precision)) AND (analytical_db."diameterBreastHeight_cm" < _cm_to_m((2.5)::double precision)))) AS "individualCount_1_to_2_5cm", count(((analytical_db."diameterBreastHeight_cm" >= _cm_to_m((2.5)::double precision)) AND (analytical_db."diameterBreastHeight_cm" < _cm_to_m((10)::double precision)))) AS "individualCount_2_5_to_10cm", count((analytical_db."diameterBreastHeight_cm" >= _cm_to_m((10)::double precision))) AS "individualCount_10cm_or_more" FROM analytical_db WHERE (analytical_db."diameterBreastHeight_cm" IS NOT NULL) GROUP BY analytical_db."institutionCode", analytical_db.country, analytical_db."stateProvince", analytical_db.county, analytical_db."decimalLatitude", analytical_db."decimalLongitude", analytical_db."plotName", analytical_db."elevationInMeters", analytical_db."plotArea_ha", analytical_db."samplingProtocol", analytical_db."dateCollected", analytical_db.family, analytical_db.genus, analytical_db."speciesBinomial", analytical_db."scientificName", analytical_db."scientificNameAuthorship", analytical_db."scientificNameWithMorphospecies";

--