-- Name: 2014-10-18.Juergen_Dengler.sPlot; Type: VIEW; Schema: public; Owner: -

--

--

-- Name: 2014-3-11.Jeff_Ott.climatic_range_determinants; Type: VIEW; Schema: public; Owner: -

--

--

-- Name: 2014-6-12.Jeff_Ott.climatic_range_determinants; Type: VIEW; Schema: public; Owner: -

--

--

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

--

CREATE TABLE iucn_red_list (

    accepted_family varchar(255) NOT NULL,

    accepted_species_binomial varchar(255) NOT NULL

);

--

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

--

--

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

-- Name: 2014-10-18.Juergen_Dengler.sPlot; Type: ACL; Schema: public; Owner: -

-- Name: 2014-3-11.Jeff_Ott.climatic_range_determinants; Type: ACL; Schema: public; Owner: -

--

-- Name: 2014-6-12.Jeff_Ott.climatic_range_determinants; Type: ACL; Schema: public; Owner: -

--

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

--

CREATE TABLE source (

    source_id integer NOT NULL,

    matched_source_id integer,

    parent_id integer,

    shortname text NOT NULL,

    citation text,

    sourcetype sourcetype,

    accesslevel accesslevel,

    accessconditions text,

    observationtype observationtype,

    title text,

    titlesuperior text,

    volume text,

    issue text,

    pagerange text,

    totalpages integer,

    publisher text,

    publicationplace text,

    isbn text,

    edition text,

    numberofvolumes integer,

    chapternumber integer,

    reportnumber integer,

    communicationtype text,

    degree text,

    url text,

    doi text,

    additionalinfo text,

    pubdate date,

    accessdate date,

    conferencedate date,

    datecreated date DEFAULT now() NOT NULL,

    createdby text,

    datelastmodified date DEFAULT now() NOT NULL,

    lastmodifiedby text,

    import_revision text

);

--

-- Name: 2014-10-18.Juergen_Dengler.sPlot; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW "2014-10-18.Juergen_Dengler.sPlot" AS

 SELECT analytical_stem.scrubbed_species_binomial AS species,

    analytical_stem.cover_percent,

    analytical_stem.individual_count AS abundance_count,

    analytical_stem.latitude,

    analytical_stem.longitude,

    analytical_stem.coord_uncertainty_m AS coordinate_precision_m,

    analytical_stem.community_concept_name AS formation,

    _ha_to_m2(analytical_stem.plot_area_ha) AS plot_size_m2,

    analytical_stem.date_collected AS date_of_recording,

    analytical_stem.country,

    analytical_stem.slope_gradient_deg AS slope_inclination_deg,

    analytical_stem.slope_aspect_deg,

    analytical_stem.elevation_m AS altitude_m,

    analytical_stem.stem_height_m,

    analytical_stem.datasource,

    analytical_stem.project_id,

    analytical_stem.project_contributors

   FROM (analytical_stem

   JOIN source ON ((source.shortname = analytical_stem.datasource)))

  WHERE ((((analytical_stem.datasource = ANY (public_datasources())) AND (source.observationtype = 'plot'::observationtype)) AND COALESCE((analytical_stem.is_geovalid)::boolean, true)) AND (NOT COALESCE((analytical_stem.is_cultivated)::boolean, false)));

--

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

--

REVOKE ALL ON TABLE source FROM PUBLIC;

REVOKE ALL ON TABLE source FROM bien;

GRANT ALL ON TABLE source TO bien;

GRANT SELECT ON TABLE source TO bien_read;

GRANT SELECT ON TABLE source TO public_;

--

-- Name: 2014-10-18.Juergen_Dengler.sPlot; Type: ACL; Schema: public; Owner: -

--

REVOKE ALL ON TABLE "2014-10-18.Juergen_Dengler.sPlot" FROM PUBLIC;

REVOKE ALL ON TABLE "2014-10-18.Juergen_Dengler.sPlot" FROM bien;

GRANT ALL ON TABLE "2014-10-18.Juergen_Dengler.sPlot" TO bien;

GRANT SELECT ON TABLE "2014-10-18.Juergen_Dengler.sPlot" TO bien_read;

--

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

--

CREATE TABLE source (

    source_id integer NOT NULL,

    matched_source_id integer,

    parent_id integer,

    shortname text NOT NULL,

    citation text,

    sourcetype sourcetype,

    accesslevel accesslevel,

    accessconditions text,

    observationtype observationtype,

    title text,

    titlesuperior text,

    volume text,

    issue text,

    pagerange text,

    totalpages integer,

    publisher text,

    publicationplace text,

    isbn text,

    edition text,

    numberofvolumes integer,

    chapternumber integer,

    reportnumber integer,

    communicationtype text,

    degree text,

    url text,

    doi text,

    additionalinfo text,

    pubdate date,

    accessdate date,

    conferencedate date,

    datecreated date DEFAULT now() NOT NULL,

    createdby text,

    datelastmodified date DEFAULT now() NOT NULL,

    lastmodifiedby text,

    import_revision text

);

--

-- Name: 2014-10-18.Juergen_Dengler.sPlot; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW "2014-10-18.Juergen_Dengler.sPlot" AS

 SELECT analytical_stem.scrubbed_species_binomial AS species,

    analytical_stem.cover_percent,

    analytical_stem.individual_count AS abundance_count,

    analytical_stem.latitude,

    analytical_stem.longitude,

    analytical_stem.coord_uncertainty_m AS coordinate_precision_m,

    analytical_stem.community_concept_name AS formation,

    _ha_to_m2(analytical_stem.plot_area_ha) AS plot_size_m2,

    analytical_stem.date_collected AS date_of_recording,

    analytical_stem.country,

    analytical_stem.slope_gradient_deg AS slope_inclination_deg,

    analytical_stem.slope_aspect_deg,

    analytical_stem.elevation_m AS altitude_m,

    analytical_stem.stem_height_m,

    analytical_stem.datasource,

    analytical_stem.project_id,

    analytical_stem.project_contributors

   FROM (analytical_stem

   JOIN source ON ((source.shortname = analytical_stem.datasource)))

  WHERE ((((analytical_stem.datasource = ANY (public_datasources())) AND (source.observationtype = 'plot'::observationtype)) AND COALESCE((analytical_stem.is_geovalid)::boolean, true)) AND (NOT COALESCE((analytical_stem.is_cultivated)::boolean, false)));

--

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

--

REVOKE ALL ON TABLE source FROM PUBLIC;

REVOKE ALL ON TABLE source FROM bien;

GRANT ALL ON TABLE source TO bien;

GRANT SELECT ON TABLE source TO bien_read;

GRANT SELECT ON TABLE source TO public_;

--

-- Name: 2014-10-18.Juergen_Dengler.sPlot; Type: ACL; Schema: public; Owner: -

--

REVOKE ALL ON TABLE "2014-10-18.Juergen_Dengler.sPlot" FROM PUBLIC;

REVOKE ALL ON TABLE "2014-10-18.Juergen_Dengler.sPlot" FROM bien;

GRANT ALL ON TABLE "2014-10-18.Juergen_Dengler.sPlot" TO bien;

GRANT SELECT ON TABLE "2014-10-18.Juergen_Dengler.sPlot" TO bien_read;

--