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

--

CREATE VIEW iucn_red_list_view AS

 SELECT DISTINCT taxon_scrub."*Accepted_name_family" AS accepted_family,

    taxon_scrub."*Accepted_name_species" AS accepted_species_binomial

   FROM (taxonlabel

   JOIN "TNRS".taxon_scrub ON ((taxon_scrub."*Name_submitted" = taxonlabel.taxonomicname)))

  WHERE (((taxonlabel.source_id = source_by_shortname('IUCN'::text)) AND (taxon_scrub."*Accepted_name_family" IS NOT NULL)) AND (taxon_scrub."*Accepted_name_species" IS NOT NULL));

--

-- Name: VIEW iucn_red_list_view; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON VIEW iucn_red_list_view IS '

after updating this:

SELECT iucn_red_list_view_modify();

';

--

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

--

CREATE TABLE taxondetermination (

    taxondetermination_id integer NOT NULL,

    taxonoccurrence_id integer NOT NULL,

    source_id integer NOT NULL,

    taxonverbatim_id integer NOT NULL,

    party_id integer,

    role role DEFAULT 'unknown'::role NOT NULL,

    determinationtype text,

    reference_id integer,

    isoriginal boolean,

    iscurrent__verbatim boolean,

    iscurrent boolean DEFAULT false NOT NULL,

    taxonfit text,

    taxonconfidence text,

    grouptype text,

    notes text,

    revisions boolean,

    determinationdate date

);

--

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

--

COMMENT ON TABLE taxondetermination IS '

Equivalent to VegBank''s taxoninterpretation table.

';

--

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

--

CREATE TABLE taxonverbatim (

    taxonverbatim_id integer NOT NULL,

    source_id integer NOT NULL,

    taxonlabel_id integer,

    verbatimrank text,

    taxonomicname text,

    taxonname text,

    author text,

    family text,

    genus text,

    specific_epithet text,

    subspecies text,

    morphospecies text,

    morphoname text,

    growthform growthform,

    description text,

    CONSTRAINT taxonverbatim_required_key CHECK (((taxonlabel_id IS NOT NULL) OR (morphoname IS NOT NULL)))

);

--

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

--

COMMENT ON TABLE taxonverbatim IS '

Component parts of the taxonlabel. Contains the datasource''s original taxonomic name components, as well as any parsed components produced by name resolution.

';

--

-- Name: COLUMN taxonverbatim.verbatimrank; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN taxonverbatim.verbatimrank IS '

The taxon''s verbatim level in the taxonomic hierarchy. Does not need to be in the taxonrank closed list.

';

--

-- Name: COLUMN taxonverbatim.taxonomicname; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN taxonverbatim.taxonomicname IS '

The full taxonomic name which uniquely identifies this taxon, including the author of that name. The family should be omitted if possible.

Equivalent to Darwin Core''s scientificName.

';

--

-- Name: COLUMN taxonverbatim.taxonname; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN taxonverbatim.taxonname IS '

The taxonomic name without the author. The family should be omitted if possible.

';

--

-- Name: COLUMN taxonverbatim.author; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN taxonverbatim.author IS '

The author of the taxonomic name.

Equivalent to Darwin Core''s scientificNameAuthorship.

';

--

-- Name: COLUMN taxonverbatim.family; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN taxonverbatim.family IS '

The family of the taxonomic name. This is a cached field for easy querying; this should also be stored in taxonlabel at the appropriate rank.

';

--

-- Name: COLUMN taxonverbatim.genus; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN taxonverbatim.genus IS '

The genus portion of the taxonomic name. This is a cached field for easy querying; this should also be stored in taxonlabel at the appropriate rank.

';

--

-- Name: COLUMN taxonverbatim.specific_epithet; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN taxonverbatim.specific_epithet IS '

The specific epithet portion of the taxonomic name. This is a cached field for easy querying; this should also be stored in taxonlabel at the appropriate rank.

';

--

-- Name: COLUMN taxonverbatim.morphospecies; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN taxonverbatim.morphospecies IS '

The morphospecies suffix.

';

--

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

--

CREATE TABLE trait (

    trait_id integer NOT NULL,

    taxonoccurrence_id integer NOT NULL,

    name text NOT NULL,

    value text,

    units text

);

--

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

--

CREATE VIEW taxon_trait_view AS

 SELECT taxon_scrub."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" AS "scientificName",

    trait.name AS "measurementType",

    trait.value AS "measurementValue",

    trait.units AS "measurementUnit",

    "plot.**".country,

    "plot.**".state_province,

    "plot.**".county,

    "plot.**".locality,

    "plot.**".latitude,

    "plot.**".longitude,

    "plot.**".coord_uncertainty_m

   FROM ((((((trait

   LEFT JOIN taxonoccurrence USING (taxonoccurrence_id))

   LEFT JOIN taxondetermination ON (((taxondetermination.taxonoccurrence_id = taxonoccurrence.taxonoccurrence_id) AND taxondetermination.iscurrent)))

   LEFT JOIN taxonverbatim USING (taxonverbatim_id))

   LEFT JOIN taxonlabel USING (taxonlabel_id))

   LEFT JOIN "TNRS".taxon_scrub ON ((taxon_scrub."*Name_submitted" = taxonlabel.taxonomicname)))

   LEFT JOIN "plot.**" USING (locationevent_id))

  WHERE (taxon_scrub."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" IS NOT NULL);

--

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

CREATE VIEW tnrs_input_name AS

 SELECT DISTINCT taxonlabel.taxonomicname

   FROM (taxonlabel

   LEFT JOIN "TNRS".taxon_match ON ((taxon_match."*Name_submitted" = taxonlabel.taxonomicname)))

  WHERE (((taxonlabel.taxonomicname IS NOT NULL) AND (taxon_match."*Name_submitted" IS NULL)) AND "TNRS".taxon_name_is_safe(taxonlabel.taxonomicname))

  ORDER BY taxonlabel.taxonomicname;

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

--

CREATE VIEW view_full_occurrence_individual_view AS

 SELECT "plot.**".datasource,

    "plot.**".country,

    "plot.**".state_province,

    "plot.**".county,

    "plot.**".locality,

    "plot.**".latitude,

    "plot.**".longitude,

    "plot.**".coord_uncertainty_m,

    "plot.**".georef_sources,

    "plot.**".georef_protocol,

    "plot.**".is_geovalid,

    "plot.**".is_new_world,

    "plot.**".project_id,

    "plot.**".project_contributors,

    "plot.**".location_id,

    "plot.**".plot_name,

    "plot.**".subplot,

    "plot.**".is_location_cultivated,

    "plot.**".locationevent_id,

    "plot.**".event_date,

    "plot.**".elevation_m,

    "plot.**".slope_aspect_deg,

    "plot.**".slope_gradient_deg,

    "plot.**".plot_area_ha,

    "plot.**".sampling_protocol,

    "plot.**".temperature_c,

    "plot.**".precip_mm,

    "plot.**".stratum_name,

    "plot.**".community_concept_name,

    "plot.**".observation_contributors,

    sourcelist.name AS custodial_institution_codes,

    specimenreplicate.collectioncode_dwc AS collection_code,

    specimenreplicate.catalognumber_dwc AS catalog_number,

    specimenreplicate.sourceaccessioncode AS occurrence_id,

    collector.fullname AS recorded_by,

    plantobservation.authorplantcode AS record_number,

    COALESCE(aggregateoccurrence.collectiondate, "plot.**".event_date) AS date_collected,

    taxonverbatim.family AS verbatim_family,

    COALESCE(taxonverbatim.taxonomicname, (taxonverbatim.taxonname || COALESCE((' '::text || taxonverbatim.author), ''::text)), taxonlabel.taxonomicname) AS verbatim_scientific_name,

    identifiedby.fullname AS identified_by,

    taxondetermination.determinationdate AS date_identified,

    taxondetermination.notes AS identification_remarks,

    taxon_scrub."*Name_matched_accepted_family" AS family_matched,

    taxon_scrub."*Name_matched" AS name_matched,

    taxon_scrub."*Name_matched_author" AS name_matched_author,

    family_higher_plant_group.higher_plant_group,

    taxon_scrub."[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" AS taxonomic_status,

    taxon_scrub."[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org" AS scrubbed_family,

    taxon_scrub."[scrubbed_]genus__@DwC__@vegpath.org" AS scrubbed_genus,

    taxon_scrub."[scrubbed_]specificEpithet__@DwC__@vegpath.org" AS scrubbed_specific_epithet,

    ((taxon_scrub."[scrubbed_]genus__@DwC__@vegpath.org" || ' '::text) || taxon_scrub."[scrubbed_]specificEpithet__@DwC__@vegpath.org") AS scrubbed_species_binomial,

    taxon_scrub."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" AS scrubbed_taxon_name_no_author,

    taxon_scrub."[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org" AS scrubbed_author,

    taxon_scrub."[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" AS scrubbed_taxon_name_with_author,

    taxon_scrub."[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" AS scrubbed_species_binomial_with_morphospecies,

    taxonoccurrence.growthform AS growth_form,

    plantobservation.reproductivecondition AS reproductive_condition,

    (((cultivated_family_locations.country IS NOT NULL) OR _or(taxonoccurrence.iscultivated, "plot.**".is_location_cultivated)))::integer AS is_cultivated,

        CASE

            WHEN (taxonoccurrence.iscultivated IS NOT NULL) THEN taxonoccurrence.cultivatedbasis

            WHEN ("plot.**".is_location_cultivated IS NOT NULL) THEN NULL::text

            ELSE NULL::text

        END AS is_cultivated_basis,

    aggregateoccurrence.notes AS occurrence_remarks,

    _fraction_to_percent(aggregateoccurrence.cover_fraction) AS cover_percent,

    taxonoccurrence.sourceaccessioncode AS taxon_observation_id,

    taxonoccurrence.authortaxoncode AS taxon_name_usage_concept_author_code,

    aggregateoccurrence.sourceaccessioncode AS aggregate_organism_observation_id,

    plantobservation.sourceaccessioncode AS individual_organism_observation_id,

    plantobservation.authorplantcode AS individual_id,

    aggregateoccurrence.count AS individual_count,

    plantobservation.plantobservation_id

   FROM ((((((((((((("plot.**"

   LEFT JOIN taxonoccurrence USING (locationevent_id))

   LEFT JOIN party collector ON ((collector.party_id = taxonoccurrence.collector_id)))

   LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id))

   LEFT JOIN plantobservation USING (aggregateoccurrence_id))

   LEFT JOIN specimenreplicate USING (plantobservation_id))

   LEFT JOIN sourcelist ON ((sourcelist.sourcelist_id = specimenreplicate.duplicate_institutions_sourcelist_id)))

   LEFT JOIN taxondetermination ON (((taxondetermination.taxonoccurrence_id = taxonoccurrence.taxonoccurrence_id) AND taxondetermination.iscurrent)))

   LEFT JOIN party identifiedby ON ((identifiedby.party_id = taxondetermination.party_id)))

   LEFT JOIN taxonverbatim USING (taxonverbatim_id))

   LEFT JOIN taxonlabel USING (taxonlabel_id))

   LEFT JOIN "TNRS".taxon_scrub ON ((taxon_scrub."*Name_submitted" = taxonlabel.taxonomicname)))

   LEFT JOIN family_higher_plant_group ON ((family_higher_plant_group.family = taxon_scrub."[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org")))

   LEFT JOIN cultivated_family_locations ON (((cultivated_family_locations.family = taxon_scrub."[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org") AND (cultivated_family_locations.country = "plot.**".country))));

--

-- Name: VIEW view_full_occurrence_individual_view; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON VIEW view_full_occurrence_individual_view IS '

after updating this:

SELECT view_full_occurrence_individual_view_modify()

add applicable columns to analytical_specimen, analytical_plot

materialize time: 2 days (42 h; "152473233.340 ms") @r14645 @vegbiendev

CREATE INDEX runtime:

1-column: 10 min - 1.5 h depending on the datatype and % populated

	(plot_area_ha: "10:45.92"; taxonomic_status: "1:28:16") @r14089 @vegbiendev

2-column: ~2 h ((datasource, scrubbed_species_binomial_with_morphospecies):

	"2:11:31") @r14089 @vegbiendev

';

--

-- Name: COLUMN view_full_occurrence_individual_view.datasource; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.datasource IS 'http://datasource__@Brad__.identifier_examples@vegpath.org;

"the person [or] institution that provided the data [to this database] (the *proximate* data provider)"';

--

-- Name: COLUMN view_full_occurrence_individual_view.country; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.country IS 'http://country__@DwC__@vegpath.org;

http://geoscrub.geoscrub_output.acceptedCountry__@VegBIEN__@vegpath.org;

"The name of the country or major administrative unit in which the Location occurs"';

--

-- Name: COLUMN view_full_occurrence_individual_view.state_province; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.state_province IS 'http://stateProvince__@DwC__@vegpath.org;

"The name of the next smaller administrative region than country (state, province, canton, department, region, etc.) in which the Location occurs"';

--

-- Name: COLUMN view_full_occurrence_individual_view.county; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.county IS 'http://county__@DwC__@vegpath.org;

"The full, unabbreviated name of the next smaller administrative region than stateProvince (county, shire, department, etc.) in which the Location occurs"';

--

-- Name: COLUMN view_full_occurrence_individual_view.locality; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.locality IS 'http://locality__@DwC__@vegpath.org;

"The specific description of the place"';

--

-- Name: COLUMN view_full_occurrence_individual_view.latitude; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.latitude IS 'http://decimalLatitude__@DwC__@vegpath.org;

"The geographic latitude (in decimal degrees, using the spatial reference system given in geodeticDatum) of the geographic center of a Location"';

--

-- Name: COLUMN view_full_occurrence_individual_view.longitude; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.longitude IS 'http://decimalLongitude__@DwC__@vegpath.org;

"The geographic longitude (in decimal degrees, using the spatial reference system given in geodeticDatum) of the geographic center of a Location"';

--

-- Name: COLUMN view_full_occurrence_individual_view.coord_uncertainty_m; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.coord_uncertainty_m IS 'http://coordinateUncertaintyInMeters__@DwC__@vegpath.org;

for a point observation, this is the fuzziness of the coordinates. for a plot (or other shape), this is the radius of the circle that circumscribes the entire plot, or the fuzziness, whichever is greater. note that the DwC definition is confusingly worded: it uses the ambiguous term Location, which sometimes refers to the asserted named place, but which they intended to refer to the plot or point.';

--

-- Name: COLUMN view_full_occurrence_individual_view.georef_sources; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.georef_sources IS 'http://georeferenceSources__@DwC__@vegpath.org;

"A list (concatenated and separated) of maps, gazetteers, or other resources used to georeference the Location, described specifically enough to allow anyone in the future to use the same resources"';

--

-- Name: COLUMN view_full_occurrence_individual_view.georef_protocol; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.georef_protocol IS 'http://georeferenceProtocol__@DwC__@vegpath.org;

"A description or reference to the methods used to determine the spatial footprint, coordinates, and uncertainties"';

--

-- Name: COLUMN view_full_occurrence_individual_view.is_geovalid; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.is_geovalid IS 'whether the coordinates are within the boundary of the asserted named places';

--

-- Name: COLUMN view_full_occurrence_individual_view.is_new_world; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.is_new_world IS 'whether the country is in the Americas';

--

-- Name: COLUMN view_full_occurrence_individual_view.project_id; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.project_id IS 'http://projectID__@VegX__.plotObservation@vegpath.org;

http://project.sourceaccessioncode__@VegBIEN__.public@vegpath.org;

"A reference to a specific ''project''"';

--

-- Name: COLUMN view_full_occurrence_individual_view.project_contributors; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.project_contributors IS 'http://projectContributor[s]__@VegBank__@vegpath.org;

"intersection entit[ies] used to ''link'' a party with a specific project wherein vegetation plots are described"';

--

-- Name: COLUMN view_full_occurrence_individual_view.location_id; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.location_id IS 'http://locationID__@DwC__@vegpath.org;

"An identifier for the set of location information (data associated with dcterms:Location)"';

--

-- Name: COLUMN view_full_occurrence_individual_view.plot_name; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.plot_name IS 'http://plotName__@VegX__.plot@vegpath.org;

"Name or label for a plot"';

--

-- Name: COLUMN view_full_occurrence_individual_view.subplot; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.subplot IS 'http://subplot__@SALVIAS__.Plot_data@vegpath.org;

http://location.authorlocationcode__@VegBIEN__.public@vegpath.org;

"Code for subplot, line, or any other subsample or subdivision of plot"';

--

-- Name: COLUMN view_full_occurrence_individual_view.is_location_cultivated; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.is_location_cultivated IS 'whether the occurrence''s *location* was flagged as cultivated. note that this refers just to the cultivated status of the *location*; the occurrence may be cultivated even if the location isn''t.';

--

-- Name: COLUMN view_full_occurrence_individual_view.locationevent_id; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.locationevent_id IS 'http://locationevent.locationevent_id__@VegBIEN__.public@vegpath.org;

autogenerated ID for locationevent';

--

-- Name: COLUMN view_full_occurrence_individual_view.event_date; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.event_date IS 'http://eventDate__@DwC__@vegpath.org;

"The date-time or interval during which an Event occurred. For occurrences, this is the date-time when the event was recorded."';

--

-- Name: COLUMN view_full_occurrence_individual_view.elevation_m; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.elevation_m IS 'http://(-minimum-)ElevationInMeters__@DwC__@vegpath.org;

the "elevation (altitude, usually above sea level), in meters"';

--

-- Name: COLUMN view_full_occurrence_individual_view.slope_aspect_deg; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.slope_aspect_deg IS 'http://slopeAspect[_deg]__@VegX__.plot@vegpath.org;

"Representative azimuth of slope gradient (0-360 degrees) or as a cardinal direction (e.g. N, S, NE)"';

--

-- Name: COLUMN view_full_occurrence_individual_view.slope_gradient_deg; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.slope_gradient_deg IS 'http://slopeGradient[_deg]__@VegX__.plot@vegpath.org;

"Representative inclination of slope in degrees"';

--

-- Name: COLUMN view_full_occurrence_individual_view.plot_area_ha; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.plot_area_ha IS 'http://plot.area[_ha]__@VegX__@vegpath.org;

"Total area of the plot"';

--

-- Name: COLUMN view_full_occurrence_individual_view.sampling_protocol; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.sampling_protocol IS 'http://samplingProtocol__@DwC__@vegpath.org;

"The name of, reference to, or description of the method or protocol used during an Event"';

--

-- Name: COLUMN view_full_occurrence_individual_view.temperature_c; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.temperature_c IS 'http://temperature[_C]__@VegX__.plotObs.obsCond@vegpath.org;

"Temperature during observation [...] [in] Celsius"';

--

-- Name: COLUMN view_full_occurrence_individual_view.precip_mm; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.precip_mm IS 'http://precip_mm__@SALVIAS__.Plot_metadata@vegpath.org;

"Total annual precipitation, in mm"';

--

-- Name: COLUMN view_full_occurrence_individual_view.stratum_name; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.stratum_name IS 'http://stratumName__@VegX__.stratum@vegpath.org;

"Name associated with this stratum"';

--

-- Name: COLUMN view_full_occurrence_individual_view.community_concept_name; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.community_concept_name IS 'http://communityConcept.name__@VegX__.communityDet@vegpath.org;

"A textual label for a community type. A community type is an abstract grouping of organisms that tend to co-occur on the landscape due to shared ecological requirements or preferences."';

--

-- Name: COLUMN view_full_occurrence_individual_view.observation_contributors; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.observation_contributors IS 'http://observationContributor[s]__@VegBank__@vegpath.org;

"intersection[s] that link[] a party with a specific plot observation event"';

--

-- Name: COLUMN view_full_occurrence_individual_view.custodial_institution_codes; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.custodial_institution_codes IS 'http://[custodial_]institutionCode[s]__@DwC__@vegpath.org;

"The name[s] (or acronym[s]) in use by the institution[s] having custody of the object(s) or information referred to in the record"';

--

-- Name: COLUMN view_full_occurrence_individual_view.collection_code; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.collection_code IS 'http://collectionCode__@DwC__@vegpath.org;

"The name, acronym, coden, or initialism identifying the collection or data set from which the record was derived"';

--

-- Name: COLUMN view_full_occurrence_individual_view.catalog_number; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.catalog_number IS 'http://catalogNumber__@DwC__@vegpath.org;

"An identifier (preferably unique) for the record within the data set or collection"';

--

-- Name: COLUMN view_full_occurrence_individual_view.occurrence_id; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.occurrence_id IS 'http://occurrenceID__@DwC__@vegpath.org;

"An identifier for the Occurrence (as opposed to a particular digital record of the occurrence). In the absence of a persistent global unique identifier, construct one from a combination of identifiers in the record that will most closely make the occurrenceID globally unique."';

--

-- Name: COLUMN view_full_occurrence_individual_view.recorded_by; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.recorded_by IS 'http://recordedBy__@DwC__@vegpath.org;

"A list (concatenated and separated) of names of people, groups, or organizations responsible for recording the original Occurrence. The primary collector or observer, especially one who applies a personal identifier (recordNumber), should be listed first."';

--

-- Name: COLUMN view_full_occurrence_individual_view.record_number; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.record_number IS 'http://recordNumber__@DwC__@vegpath.org;

"An identifier given to the Occurrence at the time it was recorded. Often serves as a link between field notes and an Occurrence record, such as a specimen collector''s number."';

--

-- Name: COLUMN view_full_occurrence_individual_view.date_collected; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.date_collected IS 'http://(-Earliest-)DateCollected__-2007-04-17__@DwC__.hist@vegpath.org;

the "date-time (Common Era calendar) in a date-time period during which an organism or group of organisms was collected or observed"';

--

-- Name: COLUMN view_full_occurrence_individual_view.verbatim_family; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.verbatim_family IS 'http://[verbatim_]family__@DwC__@vegpath.org;

"The full scientific name of the family in which the taxon is classified [*before* any TNRS scrubbing]"';

--

-- Name: COLUMN view_full_occurrence_individual_view.verbatim_scientific_name; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.verbatim_scientific_name IS 'http://[verbatim_]scientificName__@DwC__@vegpath.org;

"The full scientific name [*before* any TNRS scrubbing], with authorship and date information if known. When forming part of an Identification, this should be the name in lowest level taxonomic rank that can be determined."';

--

-- Name: COLUMN view_full_occurrence_individual_view.identified_by; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.identified_by IS 'http://identifiedBy__@DwC__@vegpath.org;

"A list (concatenated and separated) of names of people, groups, or organizations who assigned the Taxon to the subject"';

--

-- Name: COLUMN view_full_occurrence_individual_view.date_identified; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.date_identified IS 'http://dateIdentified__@DwC__@vegpath.org;

"The date on which the subject was identified as representing the Taxon"';

--

-- Name: COLUMN view_full_occurrence_individual_view.identification_remarks; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.identification_remarks IS 'http://identificationRemarks__@DwC__@vegpath.org;

"Comments or notes about the Identification"';

--

-- Name: COLUMN view_full_occurrence_individual_view.family_matched; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.family_matched IS 'http://Family_matched__@TNRS__@vegpath.org;

http://TNRS.taxon_scrub.matchedFamily__@VegBIEN__@vegpath.org;

"The closest matching family in the TNRS database to the family submitted"';

--

-- Name: COLUMN view_full_occurrence_individual_view.name_matched; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.name_matched IS 'http://Name_matched__@TNRS__@vegpath.org;

"Scientific name with the highest match score. May be an exact match or a fuzzy match."';

--

-- Name: COLUMN view_full_occurrence_individual_view.name_matched_author; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.name_matched_author IS 'http://Name_matched_author__@TNRS__@vegpath.org;

"Standard authority for the matched name"';

--

-- Name: COLUMN view_full_occurrence_individual_view.higher_plant_group; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.higher_plant_group IS 'http://[higher_plant_group~]higherClassification__@DwC__@vegpath.org;

closed list derived from higher taxa';

--

-- Name: COLUMN view_full_occurrence_individual_view.taxonomic_status; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.taxonomic_status IS 'http://taxonomicStatus__@DwC__@vegpath.org;

"The status of the use of the scientificName as a label for a taxon. Requires taxonomic opinion to define the scope of a taxon. [...] Examples: "invalid", "misapplied", "homotypic synonym", "accepted"."';

--

-- Name: COLUMN view_full_occurrence_individual_view.scrubbed_family; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.scrubbed_family IS 'the family of the TNRS accepted or matched name';

--

-- Name: COLUMN view_full_occurrence_individual_view.scrubbed_genus; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.scrubbed_genus IS 'the genus of the TNRS accepted or matched name';

--

-- Name: COLUMN view_full_occurrence_individual_view.scrubbed_specific_epithet; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.scrubbed_specific_epithet IS 'the specific epithet of the TNRS accepted or matched name';

--

-- Name: COLUMN view_full_occurrence_individual_view.scrubbed_species_binomial; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.scrubbed_species_binomial IS 'the species binomial of the TNRS accepted or matched name';

--

-- Name: COLUMN view_full_occurrence_individual_view.scrubbed_taxon_name_no_author; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.scrubbed_taxon_name_no_author IS 'the taxon name without author of the TNRS accepted or matched name';

--

-- Name: COLUMN view_full_occurrence_individual_view.scrubbed_author; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.scrubbed_author IS 'the author of the TNRS accepted or matched name';

--

-- Name: COLUMN view_full_occurrence_individual_view.scrubbed_taxon_name_with_author; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.scrubbed_taxon_name_with_author IS 'the taxon name with author of the TNRS accepted or matched name';

--

-- Name: COLUMN view_full_occurrence_individual_view.scrubbed_species_binomial_with_morphospecies; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.scrubbed_species_binomial_with_morphospecies IS 'http://speciesBinomialWithMorphospecies__@VegCore__@vegpath.org;

http://TNRS.taxon_scrub.scrubbed_morphospecies_binomial__@VegBIEN__.public@vegpath.org;

"combine the `taxon` plus `morphospecies` to produce a unique string, `taxonMorphospecies`";

a morphospecies is a custom species name assigned in the field by the collector (which does not validate to anything in TNRS)';

--

-- Name: COLUMN view_full_occurrence_individual_view.growth_form; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.growth_form IS 'http://[growth_form=]habit__@SALVIAS__.Plot_data@vegpath.org;

"Growth form"; "Closed pick list"';

--

-- Name: COLUMN view_full_occurrence_individual_view.reproductive_condition; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.reproductive_condition IS 'http://reproductiveCondition__@DwC__@vegpath.org;

"The reproductive condition of the biological individual(s) represented in the Occurrence. Recommended best practice is to use a controlled vocabulary."';

--

-- Name: COLUMN view_full_occurrence_individual_view.is_cultivated; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.is_cultivated IS 'whether the occurrence was flagged as cultivated';

--

-- Name: COLUMN view_full_occurrence_individual_view.is_cultivated_basis; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.is_cultivated_basis IS 'why the occurrence was flagged as cultivated';

--

-- Name: COLUMN view_full_occurrence_individual_view.occurrence_remarks; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.occurrence_remarks IS 'http://occurrenceRemarks__@DwC__@vegpath.org;

"Comments or notes about the Occurrence"';

--

-- Name: COLUMN view_full_occurrence_individual_view.cover_percent; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.cover_percent IS 'http://coverPercent__@VegX__.attribute.ordinal@vegpath.org;

"Average cover of the index in percent"';

--

-- Name: COLUMN view_full_occurrence_individual_view.taxon_observation_id; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.taxon_observation_id IS 'http://taxonObservation[.id]__@VegBank__@vegpath.org;

"identifier assigned [by the datasource] to each unique observation of a taxon in a plot"';

--

-- Name: COLUMN view_full_occurrence_individual_view.taxon_name_usage_concept_author_code; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.taxon_name_usage_concept_author_code IS 'http://taxonNameUsageConcept.authorCode__@VegX__@vegpath.org;

"Verbat[i]m short code used by the author to signify the species at time of observation"';

--

-- Name: COLUMN view_full_occurrence_individual_view.aggregate_organism_observation_id; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.aggregate_organism_observation_id IS 'http://aggregateOrganismObservation.id__@VegX__@vegpath.org;

the ID for "An observation applying to all occurrences of an organism based on an aggregation factor. It contains an AggregateValue, which is an assessment of the overall occurrence of an organism in a Plot (e.g. number of stems, percentage cover, total biomass, basal area)."';

--

-- Name: COLUMN view_full_occurrence_individual_view.individual_organism_observation_id; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.individual_organism_observation_id IS 'http://individualOrganismObservation.id__@VegX__@vegpath.org;

the ID for "An observation applying to one occurrence of an organism (or part of an organism). It is a container for measurements made on the organism (e.g. diameter, height, crown dimensions, biomass, growth form, number of stems)."';

--

-- Name: COLUMN view_full_occurrence_individual_view.individual_id; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.individual_id IS 'http://individualID__@DwC__@vegpath.org;

"An identifier for an individual or named group of individual organisms represented in the Occurrence"';

--

-- Name: COLUMN view_full_occurrence_individual_view.individual_count; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.individual_count IS 'http://individualCount__@DwC__@vegpath.org;

"The number of individuals represented present at the time of the Occurrence"';

--

-- Name: COLUMN view_full_occurrence_individual_view.plantobservation_id; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN view_full_occurrence_individual_view.plantobservation_id IS 'http://plantobservation."_id__@VegBIEN__.public@vegpath.org;

VegBIEN-autogenerated identifier for the individual';

--

-- Name: _plots_20_tnrs_names; Type: VIEW; Schema: public_validations; Owner: -

--

CREATE VIEW _plots_20_tnrs_names AS

 SELECT taxonverbatim.taxonomicname AS verbatim_name_with_author,

    taxonlabel.taxonomicname AS tnrs_input_name,

    taxon_scrub."[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" AS tnrs_taxonomic_status,

    taxon_scrub."*Accepted_name_family" AS tnrs_accepted_name_family,

    taxon_scrub."*Accepted_name" AS tnrs_accepted_name,

    taxon_scrub."*Accepted_name_author" AS tnrs_accepted_name_author,

    taxon_scrub."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" AS taxon_morphospecies

   FROM ((public.taxonverbatim

   JOIN public.taxonlabel USING (taxonlabel_id))

   JOIN "TNRS".taxon_scrub ON ((taxon_scrub."*Name_submitted" = taxonlabel.taxonomicname)))

  WHERE (taxonverbatim.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname))

  ORDER BY taxonlabel.taxonomicname;

--

-- Name: VIEW _plots_20_tnrs_names; Type: COMMENT; Schema: public_validations; Owner: -

--

COMMENT ON VIEW _plots_20_tnrs_names IS '

The first check is done while checking up on Aaron''s quantitative validations

("comparing the results of the plot output queries run on VegBIEN to the data in

VegBank"). Several input-output queries include verbatim taxon names; if they

pass then Aaron is forming the verbatim name correctly. Even though it concerns

the verbatim name and not the resolved name, this first check is important. If

Aaron is messing up the verbatim name somehow, the resolved name will not be

correct.

The second check should focus on determining if Aaron is reconstructing

morphospecies names correctly. The TNRS stores the morphospecies part of the

name (e.g., "sp.1") in the column "Unmatched_terms"; a bit of additional

manipulation is required to concatenated it with the resolved part of the name

to form the morphospecies string ("Miconia sp.1", "Carex ''fuzzy leaf''"). As I

found out only recently, Aaron does NOT renormalize the resolved names back to

the core database. He simple stores the verbatim TNRS output. Fully formed

morphospecies names are therefore not stored anywhere in the database, but are

reassembled on the fly when generating an analytical extract. Aaron''s rules for

doing this are embedded in a script somewhere, I have no idea where. For Brian

or Bob to assess if Aaron is re-concatenating morphospecies names correctly,

they will need to request analytical extracts for one or two of their plots, and

compare them to the names in their original databases. This second step

shouldn''t take long. If the resolved morphospecies name in Aaron''s analytical

extract looks correct, you''re done.

';

--

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

--

CREATE INDEX taxonlabel_taxonomicname_idx ON taxonlabel USING btree (taxonomicname) WHERE ((taxonomicname IS NOT NULL) AND "TNRS".taxon_name_is_safe(taxonomicname));

--

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

--

REVOKE ALL ON TABLE iucn_red_list_view FROM PUBLIC;

REVOKE ALL ON TABLE iucn_red_list_view FROM bien;

GRANT ALL ON TABLE iucn_red_list_view TO bien;

GRANT SELECT ON TABLE iucn_red_list_view TO bien_read;

--

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

--

REVOKE ALL ON TABLE taxondetermination FROM PUBLIC;

REVOKE ALL ON TABLE taxondetermination FROM bien;

GRANT ALL ON TABLE taxondetermination TO bien;

GRANT SELECT ON TABLE taxondetermination TO bien_read;

--

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

--

REVOKE ALL ON TABLE taxonverbatim FROM PUBLIC;

REVOKE ALL ON TABLE taxonverbatim FROM bien;

GRANT ALL ON TABLE taxonverbatim TO bien;

GRANT SELECT ON TABLE taxonverbatim TO bien_read;

--

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

--

REVOKE ALL ON TABLE trait FROM PUBLIC;

REVOKE ALL ON TABLE trait FROM bien;

GRANT ALL ON TABLE trait TO bien;

GRANT SELECT ON TABLE trait TO bien_read;

--

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

--

REVOKE ALL ON TABLE taxon_trait_view FROM PUBLIC;

REVOKE ALL ON TABLE taxon_trait_view FROM bien;

GRANT ALL ON TABLE taxon_trait_view TO bien;

GRANT SELECT ON TABLE taxon_trait_view TO bien_read;

--

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

REVOKE ALL ON TABLE tnrs_input_name FROM PUBLIC;

REVOKE ALL ON TABLE tnrs_input_name FROM bien;

GRANT ALL ON TABLE tnrs_input_name TO bien;

GRANT SELECT ON TABLE tnrs_input_name TO bien_read;

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

--

REVOKE ALL ON TABLE view_full_occurrence_individual_view FROM PUBLIC;

REVOKE ALL ON TABLE view_full_occurrence_individual_view FROM bien;

GRANT ALL ON TABLE view_full_occurrence_individual_view TO bien;

GRANT SELECT ON TABLE view_full_occurrence_individual_view TO bien_read;

--