schemas/vegbien.sql: public_validation_plots: added Brad's notes as comments
schemas/vegbien.sql: public_validation_plots: populated queries from validation/aggregating/plots/bien3_validations_salvias_vegbien.sql
schemas/vegbien.sql: public_validation_traits: added Brad's notes as comments
schemas/vegbien.sql: public_validation_traits: populated queries from validation/aggregating/traits/bien3_validations_traits_bien3.sql
schemas/vegbien.sql: analytical_stem.datasource: removed fkey to source.shortname because this prevents individually-reloaded datasources from being published
schemas/vegbien.sql: added datasource_unpublish()
schemas/vegbien.sql: public_validation: split into separate subschemas for plots, specimens, traits
schemas/vegbien.sql: added rm(), which deletes this schema
schemas/vegbien.sql: added publish(), which publishes this schema
schemas/vegbien.sql: added public_schema_publish()
bugfix: schemas/Makefile,vegbien.sql: renamed validation to public_validation to match the suffixed name applied by `make schemas/public/install`
schemas/vegbien.sql: added validation schema, which contains queries used in the aggregating validations. schemas/Makefile: include schemas that depend on `public` (eg. `validation`) so they are restored along with it.
schemas/vegbien.sql: added top_plot view
schemas/vegbien.sql: location: added autopopulated top_plot
schemas/vegbien.sql: locationevent: added locationevent_pull_forward_from_parent() trigger which populates project_id from the parent locationevent
schemas/vegbien.sql: locationevent__pull_forward_from_parent(): renamed to locationevent_pull_forward_from_parent_for_stratum() since it actually has a filter that causes it to apply only to stratum events
bugfix: schemas/vegbien.sql: commconcept: added missing unique constraint on commdescription, which is also an identifying field in addition to the ID (which is in commname.commname)
schemas/vegbien.sql: projectcontributor: added missing unique constraint
moved everything into /trunk/ to create the standard svn layout, for use with tools that require this (eg. git-svn). IMPORTANT: do NOT do an `svn up`. instead, re-use your working copy's existing files with `svn switch` (http://svnbook.red-bean.com/en/1.6/svn.ref.svn.c.switch.html).
schemas/vegbien.sql: plot.**, analytical_stem_view, analytical_plot: added project_contributors
schemas/vegbien.sql: added project_contributors()
bugfix: schemas/vegbien.sql: location: use the place_id from the parent location when no place_id is specified. this fixes a bug in analytical_stem_view where the parent location's place_id was used because it was sometimes missing from the sublocation, but the parent place_id itself was sometimes missing instead if sublocations each had their own place information. this way, it is always available directly in the sublocation, populated from the parent location if needed.
bugfix: schemas/vegbien.sql: location: added place_id which is autopopulated from the current locationplace. join on this in plot.**, to avoid a 1:many join when a location has multiple locationplaces.
schemas/vegbien.sql: include the family_higher_plant_group lookup table values so that these don't need to be regenerated from the NCBI nodes whenever the DB is reloaded
schemas/vegbien.sql: removed unused function _taxonlabel_set_matched_label_id(), which refers to obsolete fields
schemas/vegbien.sql: collected_dates: documented runtime (2.5 min)
schemas/vegbien.sql: collected_date_min: replaced with collected_dates view that lists all dates we have, so that we can determine which of these may be valid. it turns out that we have data collected from very far back (to the year 1), which are not merely 2-digit years because PostgreSQL will only parse early years when there are 4 digits.
schemas/vegbien.sql: added collected_date_min view
schemas/vegbien.sql: analytical_stem_view: added taxonomic_status. notice that PostgreSQL 9.3 puts each view column on a separate line, making it much easier to review the svn diff!
schemas/*.sql: updated for PostgreSQL 9.3. this reorders some functions, adds empty comment headers for omitted SEQUENCE SET commands, and (best of all) finally splits view columns onto multiple lines, so that changes in the columns are actually legible (and produce their own svn diff!)
schemas/vegbien.sql: analytical_stem_view, etc.: renamed scrubbed fields with the scrubbed_* prefix, to clearly distinguish these from the equivalent fields for other taxon names
schemas/vegbien.sql: analytical_plot: documented that this contains all of the analytical_stem columns, minus specimenHolderInstitutions, collection, accessionNumber, occurrenceID
schemas/vegbien.sql: analytical_stem_view: added individualCount
schemas/vegbien.sql: plot.**, analytical_stem_view: added slopeAspect, slopeGradient
schemas/vegbien.sql: taxondetermination.isoriginal: made it nullable like iscurrent__verbatim, because this is populated from the datasource. taxondetermination_set_iscurrent() now supports isoriginal=NULL, so this is not a problem.
schemas/vegbien.sql: taxondetermination.is_datasource_current: renamed to iscurrent__verbatim and made it nullable, so that this can be used to store the verbatim iscurrent status
schemas/vegbien.sql: added 2013-7-10.Naia.range_limiting_factors
schemas/vegbien.sql: sync_analytical_stem_to_view(): row_num: renamed to taxon_occurrence__pkey because previous taxon determinations have been removed, so each row is in fact a taxon_occurrence (~= VegCore.vegpath.org?ERD.taxon_occurrence)
schemas/vegbien.sql: analytical_stem: added row_num, which can serve as the taxon_observation ID (DwC occurrenceID)
schemas/vegbien.sql: analytical_stem: locationID... index: use eventDate instead of dateCollected since it's now eventDate that identifies the locationevent
schemas/vegbien.sql: analytical_stem_view: use plot.** to obtain plot-related fields, so that the same code does not need to be maintained in both analytical_stem_view and plot.**
schemas/vegbien.sql: analytical_stem_view: moved specimen-specific fields to occurrence section
schemas/vegbien.sql: analytical_stem_view, plot.**: added separate location__cultivated__bien
schemas/vegbien.sql: added separate eventDate, in addition to dateCollected
schemas/vegbien.sql: analytical_stem_view: changed column order, etc. to match plot.**
schemas/vegbien.sql: added 2013-10-18.Brian_Enquist.Canadensys view
schemas/vegbien.sql: sync_analytical_stem_to_view(): added index on speciesBinomialWithMorphospecies for Brian Enquist's Canadensys request
fix: schemas/vegbien.sql: analytical_stem_view: renamed specimens columns to use the VegCore names, where these differ from DwC, so that the now-VegCore staging table column names are the same as the analytical_stem_view column names
schemas/vegbien.sql: regenerated using `make schemas/remake`. note that analytical_stem_view column renamings need this step after a search-and-replace of the column names, in order to remove excess "" around all-lowercase names and reset generated index names.
schemas/vegbien.sql: added plot.** subset view of analytical_stem_view, used for VegBank validation
schemas/vegbien.sql: analytical_stem_view: added instructions for what to do after updating this
schemas/vegbien.sql: analytical_stem_view, analytical_plot: added communities using new locationevent__communities__array()
schemas/vegbien.sql: added locationevent__communities*()
bugfix: schemas/vegbien.sql: locationevent: added pull-forward trigger to populate location_id from the parent event. a pull-forward trigger must be used for this (not a COALESCE in analytical_stem_view) because the locationevent cannot even be found by analytical_stem_view if it does not have an associated location.
bugfix: schemas/vegbien.sql: locationeventcontributor: added missing unique constraint (locationeventcontributor_unique)
bugfix: schemas/vegbien.sql: locationeventcontributor.role: made it optional because some locationeventcontributors may not have an associated role
schemas/vegbien.sql: analytical_stem_view, analytical_plot: added plot__collectors, which uses locationevent__contributors__array()
schemas/vegbien.sql: locationevent__contributors(): split into set-returning and array-returning functions. sort results by fullname so there is a predictable ordering (VegBank does not include a sort_order in the observationContributor table).
schemas/vegbien.sql: added locationevent__contributors() aggregating function for use in analytical_stem_view
schemas/vegbien.sql: analytical_stem_view: added stratum table (stratum__name)
schemas/vegbien.sql: stratum: added unique constraint on source_id/stratumname, and require stratumname, so that strata are identified by name within their datasource
schemas/vegbien.sql: added source_id to allow different datasources to have their own strata
schemas/vegbien.sql: link locationevent to stratum instead of the other way around (similar to how it's represented in normalized VegCore as stratum_event->stratum, rather than stratum->taxa_sampling_event). this works around column-based import's lack of support for linking two tables together via two separate routes (in this case locationevent->taxonoccurrence->stratum and locationevent->stratum).
schemas/vegbien.sql: stratum: added stratumname
bugfix: schemas/vegbien.sql: source: removed testing row that had gotten in during `make schemas/remake`
bugfix: schemas/vegbien.sql: datasource_publish(): need to remove the current live datasource instead of the datasource to publish. note that datasource_rename() does not currently generate an error if the specified datasource doesn't exist.
bugfix: schemas/vegbien.sql: datasource_publish(): run it in a nested transaction so that there is always one published copy of the datasource. (note that a nested transaction is not automatically created for each function, http://stackoverflow.com/questions/6274457/set-isolation-level-for-postgresql-stored-procedures?In_PG_your_procedures_aren%27t_separate_transactions#answer-6283201 .)
schemas/vegbien.sql: added datasource_publish()
schemas/vegbien.sql: added datasource_rename()
schemas/vegbien.sql: added rm_version_suffix()
schemas/vegbien.sql: taxon_trait: added query to use to export
schemas/vegbien.sql: added datasource_rm(). this uses an internal schema-scoping parameter to ensure that the function always operates on tables in the schema it was defined in, rather than tables in the search_path. this ensures that when the public schema is renamed (e.g. from an imported version), the function will continue to operate on its own schema rather than whichever schema happens to be called public. this avoids any surprises if you are trying to remove a datasource in one schema, and don't want it to unintentionally be removed in another schema instead.
schemas/vegbien.sql: added covering indexes on foreign keys where needed. this enables rows to be cascadingly deleted without a full table scan.
schemas/vegbien.sql: source.revision: renamed to import_revision for clarity
schemas/vegbien.my.sql: updated with `make schemas/remake`
schemas/vegbien.sql: analytical_stem: synced with analytical_stem_view using sync_analytical_stem_to_view()
schemas/vegbien.my.sql: updated for new bin/repl text mode matching, which also affects non-regexps. this causes the replacement of a few more occurrences of PostgreSQL-only one-word typenames with their MySQL equivalents.
schemas/vegbien.sql: re-ran `make schemas/public/reinstall; make schemas/remake` cycle, which apparently changed sort order of statements
schemas/vegbien.sql: split geoscrub_input_view's new-row-only filtering into separate view geoscrub_input_new, so that the full geoscrub_input rows are still available. the reduction in geoscrub_input from eliminating the already-scrubbed rows was only 280,000 (5076500 - 4799173) out of a possible 1.7 million (1707970), so it makes sense to just run geoscrubbing on the full input. (the lower-than-expected reduction is most likely due to rows from pre-refresh data being present in the original geoscrub_output table, which have been replaced by different, post-refresh input rows.)
schemas/vegbien.sql: higher_plant_group_nodes: ferns and allies: added Lycopodiophyta node, as requested by Brad in the conference call (wiki.vegpath.org/2013-06-13_conference_call)
schemas/vegbien.sql: allow public_ to view lookup tables (cultivated_family_locations, higher_plant_group_nodes)
schemas/vegbien.sql: added _filter_genus()
schemas/vegbien.sql: cultivated_family_locations: documented that table is from sftp://nimoy.nceas.ucsb.edu/home/bien/bien2_scripts/geoscrub/cultivated/cult_by_taxon/flag_by_taxa.inc (i.e. not generated by a function)
schemas/vegbien.sql: place.geovalid: require it to be NOT NULL so that it's always a 2-valued boolean (but default it to false since it's not a required field)
schemas/vegbien.sql: added range_modeling_input view
schemas/vegbien.sql: Removed unused analytical_aggregate table, because analytical_stem provides much more detailed, higher-quality data, both in terms of the number or of rows and the number of columns. analytical_aggregate has also long been out of sync with the analytical DB schema, and it doesn't make sense to spend processing time in make_analytical_db to perform the DISTINCT ON if the table isn't being used. We may revisit analytical_aggregate later once we have ID fields for each entity in the DISTINCT ON and can avoid DISTINCTing on all analytical_aggregate columns.
schemas/vegbien.sql: analytical_stem_view: Merged taxonName_verbatim and scientificNameAuthorship_verbatim into scientificName_verbatim
schemas/vegbien.sql: Removed no longer needed unscrubbed_taxondetermination_view. Use taxonlabel joined to ScrubbedTaxon instead.
*.sql: Replaced concat_delim() with concat_ws(), which is built-in as of PostgreSQL 9.1 (http://stackoverflow.com/questions/1943433/postgresql-concat-ws-like-function)
schemas/vegbien.sql: analytical_stem_view: Added temperature_C, precipitation_m for possible use in John Wiens' plant climatic niche evolution project ("how fast is the rate of climatic niche evolution among species compared to projected changes in climate over the next 100 years? How do these rates compare for temperature and precipitation variables?")
schemas/vegbien.sql: analytical_specimen/analytical_plot: Added occurrenceRemarks
schemas/vegbien.sql: analytical_stem_view: Added occurrenceRemarks
Moved compass_dir from schemas/vegbien.sql to schemas/functions.sql so it can be used by _dms_to_dd()
Moved _dms_to_dd() from schemas/vegbien.sql to schemas/functions.sql so it can use functions schema functions
schemas/vegbien.sql: Added compass_dir enum
schemas/vegbien.sql: Added _dms_to_dd()
schemas/vegbien.sql: analytical_stem_view: scientificName_verbatim: Use the taxonname (which omits the family and author) instead when available, at Brad's request
schemas/vegbien.sql: provider_count_view: Group data by dataprovider and dataset, rather than by each separately, to satisfy the GBIF attribution requirements as described by Brad
schemas/vegbien.sql: analytical_stem_view: Moved threatened_bien after growthForm as requested by Brad <https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/Spot-checking#SpeciesLink>
schemas/vegbien.sql: Added analytical_plot view
