COMMENT ON SCHEMA public_validations IS '

aggregating validations queries

';

COMMENT ON TYPE coordinatesource IS '

From <https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/VegCore_data_dictionary#coordinateSource>

';

COMMENT ON TYPE establishmentmeans_dwc IS '

See <http://rs.tdwg.org/dwc/terms/#establishmentMeans>

';

COMMENT ON TYPE placerank IS '

county = parish, canton

From <http://rs.tdwg.org/dwc/terms/#dcindex#dcterms:Location>, <http://vegbank.org/vegbank/views/dba_fielddescription_detail.jsp?view=detail&wparam=1415&entity=dba_fielddescription&params=1415>

';

COMMENT ON TYPE taxonrank IS '

From <http://www.tdwg.org/standards/117/download/>

';

COMMENT ON TYPE role IS '

From <http://vegbank.org/vegbank/views/dba_fielddescription_detail.jsp?view=detail&wparam=1331&entity=dba_fielddescription&params=1331>, <http://vegbank.org/vegbank/views/dba_tabledescription_detail.jsp?view=detail&wparam=projectcontributor&entity=dba_tabledescription&where=where_tablename#ROLE_ID>, and misc terms.

';

COMMENT ON TYPE sourcetype IS '

From <https://projects.nceas.ucsb.edu/nceas/attachments/download/428/bien_web_datasource_schema.sql>, <http://vegbank.org/vegbank/views/dba_fielddescription_detail.jsp?view=detail&wparam=1300&entity=dba_fielddescription&params=1300>, <http://vegbiendev.nceas.ucsb.edu/phppgadmin/display.php?server=localhost%3A5432%3Aallow&database=vegbien&schema=VegBank&table=source&subject=table#sourcetype>

';

COMMENT ON TYPE taxonclass IS '

= growthform + establishmentmeans_dwc + some others

';

COMMENT ON TYPE taxonomic_status IS '

See <http://tnrs.iplantcollaborative.org/instructions.html#Status>

';

COMMENT ON FUNCTION _or("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean) IS '

_or() ignores NULL values, while OR combines them with the other values to potentially convert false to NULL. OR should be used with required fields, and _or() with optional fields.

';

COMMENT ON FUNCTION datasource_publish(datasource text, schema_null anyelement) IS '

secure against renamings of the public schema.

COMMENT ON FUNCTION datasource_rename(old text, new text, schema_null anyelement) IS '

secure against renamings of the public schema.

COMMENT ON FUNCTION datasource_rm(datasource text, schema_null anyelement) IS '

secure against renamings of the public schema.

COMMENT ON FUNCTION datasource_unpublish(datasource text, schema_null anyelement) IS '

secure against renamings of the public schema.

COMMENT ON FUNCTION location__pull_forward_from_parent() IS '

see wiki.vegpath.org/Pull-forward

';

COMMENT ON FUNCTION location_set_top_plot() IS '

see wiki.vegpath.org/Pull-forward

';

COMMENT ON FUNCTION locationevent_pull_forward_from_parent() IS '

see wiki.vegpath.org/Pull-forward

';

COMMENT ON FUNCTION locationevent_pull_forward_from_parent_for_stratum() IS '

see wiki.vegpath.org/Pull-forward

';

COMMENT ON TABLE place IS '

The full path to a place. Can be either verbatim or accepted. For accepted names, points to the identified place.

To use a custom hierarchy of placenames with no explicit column, point to the lowest-rank placename in placename_id.

';

COMMENT ON COLUMN place.matched_place_id IS '

The closest match to this place. Places should be linked in a three-level hierarchy of datasource place -> verbatim place -> accepted place.

An accepted place should point to itself in this field. This will happen automatically by setting it to the special value 0.

';

COMMENT ON FUNCTION project_contributors(project_id integer) IS '

usage: array(SELECT project_contributors(...))

';

COMMENT ON FUNCTION publish(schema_null anyelement) IS '

publishes this schema.

COMMENT ON FUNCTION rm(schema_null anyelement) IS '

deletes this schema.

COMMENT ON TABLE taxonlabel IS '

A taxon label defined by an entity. Can be at any level in the taxonomic hierarchy. Can be either verbatim or accepted.

Equivalent to VegBank''s plantConcept and plantName tables, plus plantParent_ID and plantLevel from plantStatus.

';

COMMENT ON COLUMN taxonlabel.source_id IS '

The entity that created the taxon label.

';

COMMENT ON COLUMN taxonlabel.sourceaccessioncode IS '

The datasource''s identifier for the taxonlabel.

';

COMMENT ON COLUMN taxonlabel.canon_label_id IS '

The canonical taxonlabel for this taxonlabel.

';

COMMENT ON COLUMN taxonlabel.matched_label_id IS '

The taxonlabel containing the closest match to this taxonlabel. taxonlabels should be linked in a four-level hierarchy of datasource label -> parsed label -> matched label -> accepted label. A previously-accepted name''s label should be further linked to the synonym that has replaced it.

An accepted name should point to itself in this field. This will happen automatically by setting it to the special value 0.

';

COMMENT ON COLUMN taxonlabel.parent_id IS '

The parent taxonlabel. Note that while a taxon *name* may have multiple parents, a taxonlabel *instance* has only one, based on the creator''s opinion of where that taxonlabel goes in the taxonomic hierarchy.

';

COMMENT ON COLUMN taxonlabel.taxonepithet IS '

The epithet of the taxon within its parent taxon. This is the lowest-rank portion of this taxonlabel''s full taxonomic name, if it has one.

';

COMMENT ON COLUMN taxonlabel.rank IS '

The taxon''s level in the taxonomic hierarchy, standardized to a closed list. Even if you specify a custom verbatimrank in taxonverbatim, you should also specify a closest-match rank from the taxonrank closed list.

';

COMMENT ON COLUMN taxonlabel.taxonomicname IS '

The concatenated taxonomic name which uniquely identifies this taxon, including the family and author of that name. Must be unique within the datasource.

Equivalent to Darwin Core''s scientificName.

';

COMMENT ON TABLE aggregateoccurrence IS '

Equivalent to VegBank''s taxonimportance table.

';

COMMENT ON COLUMN aggregateoccurrence.linecover_m IS '

The distance in m along which this occurrence intercepts a line subplot.

';

COMMENT ON COLUMN aggregateoccurrence.occurrencestatus_dwc IS '

The extent to which the taxon is present. See <http://code.google.com/p/darwincore/wiki/Occurrence#occurrenceStatus>.

';

COMMENT ON COLUMN coordinates.footprintgeometry_dwc IS '

DwC''s footprintWKT field. "A Well-Known Text (WKT) representation of the shape (footprint, geometry) that defines the Location" (http://rs.tdwg.org/dwc/terms/#footprintWKT).

';

COMMENT ON COLUMN coordinates.coordsaccuracy_m IS '

Accuracy of latitude/longitude or footprint geometry, in meters. This should generally be at least 1 m, which is the accuracy of the best GPSes.

';

COMMENT ON TABLE cultivated_family_locations IS '

from sftp://nimoy.nceas.ucsb.edu/home/bien/bien2_scripts/geoscrub/cultivated/cult_by_taxon/flag_by_taxa.inc

';

COMMENT ON TABLE location IS '

Equivalent to VegBank''s plot table.

';

COMMENT ON COLUMN location.top_plot IS '

autopopulated

';

COMMENT ON TABLE locationevent IS '

Equivalent to VegBank''s observation table.

';

COMMENT ON TABLE method IS '

A method for sampling and aggregating plants. Replaces VegBank''s stratummethod and stratumtype tables.

Important: *All* length- or area-related measurements throughout VegBIEN must be converted to SI base units, e.g. cm -> m, ha -> m^2.**

';

COMMENT ON COLUMN method.source_id IS '

Use the source table (e.g. source.url) to store a link to the original plain text description.

';

COMMENT ON COLUMN method.name IS '

A short name for the set of methods used. Although there is no existing standard, many names are widely used, and could be useful for finding plots with similar methodology.

';

COMMENT ON COLUMN method.description IS '

Additional metadata helpful for understanding how the data were collected during the observation event.

';

COMMENT ON COLUMN method.diameterheight_m IS '

The height in m at which the diameter is measured. e.g. 1.37 m for breast height (DBH).

';

COMMENT ON COLUMN method.mindiameter_m IS '

Lower diameter limit in m for inclusion of a tree.

';

COMMENT ON COLUMN method.maxdiameter_m IS '

Upper diameter limit in m for inclusion of a tree.

';

COMMENT ON COLUMN method.minheight_m IS '

Lower height limit in m for inclusion of a tree.

';

COMMENT ON COLUMN method.maxheight_m IS '

Upper height limit in m for inclusion of a tree.

';

COMMENT ON COLUMN method.observationtype IS '

values: aggregate, individual, both

';

COMMENT ON COLUMN method.observationmeasure IS '

e.g. count, cover, presence, points-intercepted, distance-intercepted

';

COMMENT ON COLUMN method.samplingfactor IS '

Here, we could explicitly say that we are sampling a particular area by a different size representative sample area. Simply divide the number of plants connected to this record by this value to get the extrapolated (or interpolated) number of plants in the area in question. This explicitly notes a subsample or supersample.

';

COMMENT ON COLUMN method.coverbasis IS '

Were cover values for the total taxon list collected from one contiguous area or dispersed subplots?

COMMENT ON COLUMN method.stemsamplemethod IS '

The method used to obtain basal area or tree stem data (e.g., full census, point quarter, random pairs, Bitterlich, other).

Subsample census

';

COMMENT ON COLUMN method.shape IS '

e.g. square, rectangle, circle, line, point, other

';

COMMENT ON COLUMN method.samplearea_m2 IS '

The total surface area used for cover estimates and for which a complete species list is provided. If subplots were used, this would be the total area of the subplots without interstitial space.

';

COMMENT ON COLUMN method.subplotspacing_m IS '

Spacing in m between adjacent subplots, lines (line-intercept data), or points (point-intercept data).

';

COMMENT ON COLUMN method.subplotmethod_id IS '

Method to use for each subplot/line/point, which will specify subplot size, line length, etc.

';

COMMENT ON COLUMN method.pointsperline IS '

The number of points sampled on each line subplot for point-intercept data.

';

COMMENT ON TABLE plantobservation IS '

Equivalent to VegBank''s stemcount table.

';

COMMENT ON COLUMN plantobservation.authorplantcode IS '

The number of the organism within the data collection or event.

';

COMMENT ON TABLE specimenreplicate IS '

A herbarium''s replicate of a specimen. Contains Darwin Core specimen data.

';

COMMENT ON COLUMN specimenreplicate.institution_id IS '

The institution (such as a museum) that the specimenreplicate is from.

';

COMMENT ON COLUMN specimenreplicate.collectioncode_dwc IS '

The code for the collection that the specimenreplicate is from.

';

COMMENT ON TABLE stemobservation IS '

Equivalent to VegBank''s stemlocation table.

';

COMMENT ON COLUMN stemobservation.tags IS '

Stores all tags when multiple tags provided. Older tags go first.

';

COMMENT ON TABLE taxondetermination IS '

Equivalent to VegBank''s taxoninterpretation table.

';

COMMENT ON TABLE taxonoccurrence IS '

Equivalent to VegBank''s taxonobservation table.

';

COMMENT ON COLUMN taxonoccurrence.iscultivated IS '

cultivated or wild

';

COMMENT ON COLUMN taxonoccurrence.cultivatedbasis IS '

The reason why a taxonoccurrence was marked as cultivated (or not).

';

COMMENT ON COLUMN taxonoccurrence.isnative IS '

native or exotic

';

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.

';

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.

';

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.

';

COMMENT ON COLUMN taxonverbatim.taxonname IS '

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

';

COMMENT ON COLUMN taxonverbatim.author IS '

The author of the taxonomic name.

Equivalent to Darwin Core''s scientificNameAuthorship.

';

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.

';

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.

';

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.

';

COMMENT ON COLUMN taxonverbatim.morphospecies IS '

The morphospecies suffix.

';

COMMENT ON VIEW analytical_stem_view IS '

after updating this:

add applicable columns to analytical_specimen, analytical_plot

';

COMMENT ON TABLE locationplace IS '

Equivalent to VegBank''s place table.

';

COMMENT ON COLUMN methodtaxonclass.included IS '

Whether the method includes or excludes this taxon class.

';

COMMENT ON COLUMN methodtaxonclass.submethod_id IS '

The submethod used to sample just this taxon class, distinct from the main method.

';

COMMENT ON TABLE placename IS '

An official, named placename. Can be at any level in the geographic hierarchy. Note that the placename stores only one rank (e.g. country) of the full path to the placename. The higher-level ranks are stored in the placename''s chain of parent_id ancestors.

Equivalent to VegBank''s namedPlace table.

';

COMMENT ON TABLE placename_ancestor IS '

placename''s ancestor cross link table.

';

COMMENT ON TABLE plant IS '

A physical, tagged plant.

';

COMMENT ON TABLE specimen IS '

A physical specimen collected from a plant. Used to link replicates of the same specimen together.

';

COMMENT ON TABLE taxon_trait IS '

to export:

select * from taxon_trait order by "scientificName", "measurementType"

';

COMMENT ON TABLE taxonconcept IS '

A circumscribed taxon concept. This extends taxonlabel with information about who the taxon concept is according to.

';

COMMENT ON COLUMN taxonconcept.concept_reference_id IS '

The entity that defined the taxon concept. This is who the taxon concept is according to.

Equivalent to "Name sec. x".

';

COMMENT ON TABLE taxonlabel_relationship IS '

Stores the accepted ancestors of a taxonlabel. Auto-populated, so should not be manually modified.

';

COMMENT ON COLUMN taxonlabel_relationship.relationship IS '

The type of relationship.

';

COMMENT ON VIEW top_plot IS '

when updating, use * as the column list

';

COMMENT ON VIEW _plots_10_count_of_individuals_per_plot_in_each_project IS '

Method: count taxonObservations

COMMENT ON VIEW _plots_11_count_of_stems_per_plot_in_each_project IS '

Method: count records in stemobservation table

COMMENT ON VIEW _plots_12_count_of_verbatim_taxa_per_plot_in_each_project IS '

Check: join to source db by all columns, returns same number of rows

COMMENT ON VIEW _plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_p IS '

Note: Must perform equivalent concatenation of taxonomic field in source db.

COMMENT ON VIEW _plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pr IS '

Note: Must do equivalent concatenation of taxonomic field in source db.

COMMENT ON VIEW _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro IS '

Applies to: aggregate-cover plots only

COMMENT ON VIEW _plots_18_list_of_subplots_codes_for_each_plot_for_each_project IS '

DOESN''T YET WORK PROPERLY

COMMENT ON VIEW _traits_05_count_taxa IS '

Note: No morphospecies in trait table, therefore count taxon + authority only

COMMENT ON VIEW _traits_06_list_taxa IS '

Note 1: No morphospecies in trait table, use taxon + authority