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

--

CREATE TABLE analytical_stem (

    datasource text NOT NULL,

    "institutionCode" text,

    "collectionCode" text,

    "catalogNumber" text,

    "occurrenceID" text,

    country text,

    "stateProvince" text,

    county text,

    locality text,

    "decimalLatitude" double precision,

    "decimalLongitude" double precision,

    "coordinateUncertaintyInMeters" double precision,

    "coordinateSource_bien" coordinatesource,

    "georeferenceProtocol_bien" text,

    geovalid_bien integer,

    "isNewWorld_bien" integer,

    "projectID" text,

    "locationID" text NOT NULL,

    "locationName" text,

    subplot text,

    "individualCode" text,

    "elevationInMeters" double precision,

    "plotArea_ha" double precision,

    "samplingProtocol" text,

    "temperature_C" double precision,

    precipitation_m double precision,

    "recordedBy" text,

    "recordNumber" text,

    "dateCollected" date,

    family_verbatim text,

    "scientificName_verbatim" text,

    "identifiedBy" text,

    "dateIdentified" date,

    "identificationRemarks" text,

    family_matched text,

    "taxonName_matched" text,

    "scientificNameAuthorship_matched" text,

    "higherPlantGroup_bien" higher_plant_group,

    family text,

    genus text,

    "speciesBinomialWithMorphospecies" text,

    "taxonName" text,

    "scientificNameAuthorship" text,

    "growthForm" growthform,

    "reproductiveCondition" text,

    threatened_bien integer,

    cultivated_bien integer,

    "cultivatedBasis_bien" text,

    "occurrenceRemarks" 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,

    "individualObservationID" text,

    "authorStemCode" text

);

--

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

--

CREATE TABLE provider_count (

    dataprovider text NOT NULL,

    dataset text NOT NULL,

    records bigint,

    sourcetype sourcetype,

    observationtype observationtype

);

--

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

--

CREATE TABLE taxon_trait (

    "scientificName" text NOT NULL,

    "measurementType" text NOT NULL,

    "measurementValue" text,

    "measurementUnit" text

);

--

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

--

CREATE VIEW geoscrub_input_new AS

    SELECT geoscrub_input.country, geoscrub_input."stateProvince", geoscrub_input.county, geoscrub_input."decimalLatitude", geoscrub_input."decimalLongitude" FROM (geoscrub_input LEFT JOIN geoscrub.geoscrub_output ON ((((((geoscrub_output.decimallatitude = geoscrub_input."decimalLatitude") AND (geoscrub_output.decimallongitude = geoscrub_input."decimalLongitude")) AND (geoscrub_output.country = geoscrub_input.country)) AND (geoscrub_output.stateprovince = geoscrub_input."stateProvince")) AND (geoscrub_output.county = geoscrub_input.county)))) WHERE (geoscrub_output.row_num IS NULL);

--

    SELECT DISTINCT place.country, place.stateprovince AS "stateProvince", place.county, coordinates.latitude_deg AS "decimalLatitude", coordinates.longitude_deg AS "decimalLongitude" FROM (place JOIN coordinates USING (coordinates_id)) WHERE (place.country IS NOT NULL);

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

--

REVOKE ALL ON TABLE analytical_stem FROM PUBLIC;

REVOKE ALL ON TABLE analytical_stem FROM bien;

GRANT ALL ON TABLE analytical_stem TO bien;

GRANT SELECT ON TABLE analytical_stem TO bien_read;

--

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

--

REVOKE ALL ON TABLE provider_count FROM PUBLIC;

REVOKE ALL ON TABLE provider_count FROM bien;

GRANT ALL ON TABLE provider_count TO bien;

GRANT SELECT ON TABLE provider_count TO bien_read;

GRANT SELECT ON TABLE provider_count TO public_;

--

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

--

REVOKE ALL ON TABLE taxon_trait FROM PUBLIC;

REVOKE ALL ON TABLE taxon_trait FROM bien;

GRANT ALL ON TABLE taxon_trait TO bien;

GRANT SELECT ON TABLE taxon_trait TO bien_read;

--

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

--

REVOKE ALL ON TABLE geoscrub_input_new FROM PUBLIC;

REVOKE ALL ON TABLE geoscrub_input_new FROM bien;

GRANT ALL ON TABLE geoscrub_input_new TO bien;

GRANT SELECT ON TABLE geoscrub_input_new TO bien_read;

--