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

--

CREATE TABLE analytical_stem (

    datasource varchar(255) NOT NULL,

    `specimenHolderInstitutions` varchar(255),

    collection varchar(255),

    `accessionNumber` varchar(255),

    `occurrenceID` varchar(255),

    country varchar(255),

    `stateProvince` varchar(255),

    county varchar(255),

    locality varchar(255),

    `decimalLatitude` double precision,

    `decimalLongitude` double precision,

    `coordinateUncertaintyInMeters` double precision,

    `coordinateSource_bien` varchar(255),

    `georeferenceProtocol_bien` varchar(255),

    geovalid_bien int(11),

    `isNewWorld_bien` int(11),

    `projectID` varchar(255),

    `locationID` varchar(255) NOT NULL,

    `locationName` varchar(255),

    subplot varchar(255),

    `individualCode` varchar(255),

    `elevationInMeters` double precision,

    `plotArea_ha` double precision,

    `samplingProtocol` varchar(255),

    `temperature_C` double precision,

    precipitation_m double precision,

    stratum__name varchar(255),

    communities varchar(255),

    plot__collectors varchar(255),

    `recordedBy` varchar(255),

    `recordNumber` varchar(255),

    `dateCollected` date,

    family_verbatim varchar(255),

    `scientificName_verbatim` varchar(255),

    `identifiedBy` varchar(255),

    `dateIdentified` date,

    `identificationRemarks` varchar(255),

    family_matched varchar(255),

    `taxonName_matched` varchar(255),

    `scientificNameAuthorship_matched` varchar(255),

    `higherPlantGroup_bien` varchar(255),

    family varchar(255),

    genus varchar(255),

    `speciesBinomialWithMorphospecies` varchar(255),

    `taxonName` varchar(255),

    `scientificNameAuthorship` varchar(255),

    `growthForm` varchar(255),

    `reproductiveCondition` varchar(255),

    threatened_bien int(11),

    cultivated_bien int(11),

    `cultivatedBasis_bien` varchar(255),

    `occurrenceRemarks` 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),

    `aggregateOrganismObservationID` varchar(255),

    `individualObservationID` varchar(255),

    `authorStemCode` varchar(255)

);

--

-- Name: 2013-10-18.Brian_Enquist.Canadensys; Type: VIEW; Schema: public; Owner: -

--

--

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

-- Name: 2013-10-18.Brian_Enquist.Canadensys; Type: ACL; Schema: public; Owner: -

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

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

--

--

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

--

CREATE TABLE analytical_stem (

    datasource text NOT NULL,

    "specimenHolderInstitutions" text,

    collection text,

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

    stratum__name text,

    communities text[],

    plot__collectors text[],

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

    "aggregateOrganismObservationID" text,

    "individualObservationID" text,

    "authorStemCode" text

);

--

-- Name: 2013-10-18.Brian_Enquist.Canadensys; Type: VIEW; Schema: public; Owner: -

--

CREATE VIEW "2013-10-18.Brian_Enquist.Canadensys" AS

    SELECT analytical_stem."speciesBinomialWithMorphospecies" AS species, analytical_stem."decimalLatitude" AS latitude__deg, analytical_stem."decimalLongitude" AS longitude__deg, analytical_stem."coordinateUncertaintyInMeters" AS coords__uncertainty__m FROM analytical_stem WHERE (((((analytical_stem."speciesBinomialWithMorphospecies" = ANY (ARRAY['Juniperus scopulorum'::text, 'Picea engelmannii'::text, 'Pinus contorta'::text, 'Pinus edulis'::text, 'Pinus ponderosa'::text, 'Populus tremuloides'::text, 'Pseudotsuga menziesii'::text, 'Quercus gambelii'::text])) AND (NOT COALESCE((analytical_stem.cultivated_bien)::boolean, false))) AND COALESCE((analytical_stem.geovalid_bien)::boolean, true)) AND (analytical_stem."decimalLatitude" IS NOT NULL)) AND (analytical_stem."decimalLongitude" IS NOT NULL)) ORDER BY analytical_stem."speciesBinomialWithMorphospecies";

--

-- 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: 2013-10-18.Brian_Enquist.Canadensys; Type: ACL; Schema: public; Owner: -

--

REVOKE ALL ON TABLE "2013-10-18.Brian_Enquist.Canadensys" FROM PUBLIC;

REVOKE ALL ON TABLE "2013-10-18.Brian_Enquist.Canadensys" FROM bien;

GRANT ALL ON TABLE "2013-10-18.Brian_Enquist.Canadensys" TO bien;

GRANT SELECT ON TABLE "2013-10-18.Brian_Enquist.Canadensys" TO bien_read;

--