schemas/public_.sql: iucn_red_list_view: documented how to regenerate iucn_red_list from this

bugfix: schemas/public_.sql: 2014-6-4.Iara_Lacher.reserve_prioritization: fixed slowdown in materializing the view, which was caused by source__observation_type(), by replacing this with a LEFT JOIN as was done for is_threatened_iucn

fix: schemas/public_.sql: 2014-6-4.Iara_Lacher.reserve_prioritization: is_threatened_iucn: generate this much faster by doing a LEFT JOIN, which can reuse the same in-memory hash index for every row

bugfix: schemas/public_.sql: wrapper functions can't use util-schema types because these will cause the wrapper function to be cascadingly dropped when the util schema is reinstalled

bugfix: schemas/public_.sql: source__observation_type(): don't display NOTICEs about the search_path, because this function will be called millions of times

bugfix: schemas/public_.sql: in_iucn_red_list(): don't display NOTICEs about the search_path, because this function will be called millions of times

schemas/public_.sql: 2014-6-4.Iara_Lacher.reserve_prioritization: added is_threatened_iucn

schemas/public_.sql: added in_iucn_red_list()

schemas/public_.sql: added iucn_red_list, materialized from iucn_red_list_view

bugfix: schemas/vegbien.sql: iucn_red_list_view: need to include only names with an accepted name, and distinctify on the accepted names

schemas/public_.sql: added iucn_red_list

schemas/public_.sql: iucn_red_list: renamed to iucn_red_list_view since this will be materialized

schemas/public_.sql: added iucn_red_list

schemas/public_.sql: 2014-6-4.Iara_Lacher.reserve_prioritization: added collection_type (using source__observation_type())

schemas/public_.sql: added source__observation_type()

schemas/public_.sql: 2014-6-4.Iara_Lacher.reserve_prioritization: added accepted_subspecies, and removed accepted_taxon_name_with_author which had been substituting for it

schemas/public_.sql: added subspecies() wrapper

schemas/public_.sql: 2014-6-4.Iara_Lacher.reserve_prioritization: include only coordinates in South America

schemas/util.sql: added south_america(), in_south_america()

schemas/util.sql: lat_long_in_new_world(): renamed to just in_new_world() because the lat/long is implied by the param type

schemas/util.sql: lat_long_in_new_world(): take a geocoord param instead of separate lat/long params

schemas/public_.sql: 2014-6-4.Iara_Lacher.reserve_prioritization: include only georeferenced occurrences (lat/long NOT NULL)

schemas/public_.sql: 2014-6-4.Iara_Lacher.reserve_prioritization: added functional traits that we have 1st-class columns for (dbh_cm, height_m)

schemas/public_.sql: analytical_stem_view and related views: updated COMMENTs from data dictionary spreadsheet, using the steps at wiki.vegpath.org/VegBIEN_schema_refactoring#copy-data-dictionary-definitions-to-database

schemas/public_.sql: ran analytical_stem_view_modify(), which transfers the column COMMENTs from analytical_stem_view to analytical_stem

bugfix: schemas/public_.sql: analytical_stem_view_modify(): updated to new analytical_stem_view column names

fix: schemas/public_.sql: analytical_stem_view derived and related views: applied data dictionary renamings, using the steps at wiki.vegpath.org/VegBIEN_schema_refactoring#apply-data-dictionary-renamings-to-database but with the current columns of analytical_stem as the left-hand column

bugfix: schemas/public_.sql: analytical_stem_view_modify(): updated to new analytical_stem_view column names

schemas/public_.sql: analytical_stem_view: applied data dictionary renamings, using the steps at wiki.vegpath.org/VegBIEN_schema_refactoring#apply-data-dictionary-renamings-to-database

schemas/public_.sql: added 2014-6-4.Iara_Lacher.reserve_prioritization

schemas/public_.sql: 2014-6-12.Jeff_Ott.climatic_range_determinants: also include New World occurrences by coordinates, using new lat_long_in_new_world(). this modification (as requested by Jeff) will help reduce the false negatives filtered out by including only data with placenames.

schemas/public_.sql: added lat_long_in_new_world() wrapper around util.lat_long_in_new_world()

schemas/public_.sql, inputs/.TNRS/schema.sql: upgraded to Postgres 9.3.4 format, which removes trailing " "

schemas/public_.sql: 2014-6-12.Jeff_Ott.climatic_range_determinants: allow geovalid_bien = NULL to enable filtering New World status by coordinates rather than country

schemas/public_.sql: 2014-6-12.Jeff_Ott.climatic_range_determinants: added geovalid_bien

schemas/vegbien.sql: added 2014-6-12.Jeff_Ott.climatic_range_determinants (wiki.vegpath.org/Data_requests)

schemas/public_.sql: analytical_stem_view: applied data dictionary renamings, using the steps at wiki.vegpath.org/VegBIEN_schema_refactoring#apply-data-dictionary-renamings-to-database

schemas/public_.sql: analytical_stem_view: applied data dictionary renamings, using the steps at wiki.vegpath.org/VegBIEN_schema_refactoring#apply-data-dictionary-renamings-to-database

schemas/public_.sql: analytical_stem_view: example terms from each source: added location in the VegBIEN core DB

schemas/public_.sql: analytical_stem_view: terms in normalized schemas: put the source name right next to the term name (instead of after the table name) so it's easy to find

schemas/public_.sql: analytical_stem_view: VegX terms: populated definitions

schemas/public_.sql: analytical_stem_view: VegX terms: referenced to the specific table/subpath that they are in

bugfix: schemas/public_.sql: analytical_stem_view: locationName: re-sourced to VegX because this is actually not a DwC term

schemas/public_.sql: analytical_stem_view: populated definitions of linked DwC terms from DwC data dictionary

schemas/public_.sql: analytical_stem_view: renamed terms to link URLs, using the steps at wiki.vegpath.org/VegBIEN_schema_refactoring#data-dictionary-rename-terms-to-link-URLs

schemas/public_.sql: analytical_stem_view: populated links from unrenamed term names, using the steps at wiki.vegpath.org/VegBIEN_schema_refactoring#data-dictionary-populate-links-from-unrenamed-term-names

fix: schemas/public_.sql: analytical_stem_view: links: removed = before them because the link is in fact one member in a list of definitions (the = would apply to all definitions, not just the link, and since it apply to all definitions, it's redundant because of course the comment is what the column equals)

fix: schemas/public_.sql: analytical_stem_view: vegpath.org links: use ; instead of : between link and definition, because it makes more grammatical sense

schemas/public_.sql: analytical_stem_view: vegpath.org links: removed no longer needed // suffix

schemas/public_.sql: analytical_stem_view: added links and definitions for half a dozen of the terms from various sources

schemas/public_.sql: analytical_stem_view: indicated provenance of the rest of the terms

schemas/public_.sql: analytical_stem_view: flagged the DwC terms

schemas/public_.sql: added plot.**.modify()

fix: schemas/public_.sql: tnrs_input_name: added ORDER BY to ensure a stable sort order. this also creates better query plans by enabling merge joins.

bugfix: schemas/public_.sql: tnrs_input_name: need to exclude unsafe taxon names as a workaround to #935 (TNRS crashes when scrubbing names with two infraspecific epithets)

schemas/public_.sql: taxonlabel: added index on taxonomicname to facilitate retrieving rows from tnrs_input_name

schemas/public_.sql: sync_*(): renamed to *_modify() to facilitate finding these functions when modifying the corresponding view (using the new naming convention for a view's on-modify function)

fix: schemas/public_.sql: sync_*(): use util.copy() instead of CREATE TABLE AS so that table and column comments are also copied. this avoids the need to separately add the same comments to the view and its materialized table.

bugfix: schemas/public_.sql: sync_geoscrub_input_to_view(): `CREATE TABLE geoscrub_input AS __`: needs `LIMIT 0`

schemas/public_.sql: sync_analytical_stem_to_view(): removed DROP TABLE IF EXISTS because this is now done automatically by util.recreate()

schemas/vegbien.sql: tnrs_input_name: don't scrub accepted names, as using multiple matches per name no longer provides a single accepted name to scrub. instead, the Accepted_* fields can be whitespace-split to generate the same columns that would have been generated by the scrubbing (and without the overhead of the extra TNRS call).

schemas/public_.sql: added 2014-3-11.Jeff_Ott.climatic_range_determinants

schemas/public_.sql: analytical_stem_view: added scrubbed_taxon_name_with_author, needed by Jeff Ott's analysis (wiki.vegpath.org/Data_requests)

schemas/public_.sql: added scrubbed_specific_epithet, scrubbed_species_binomial, which are needed by Jeff Ott's analysis (wiki.vegpath.org/Data_requests)

fix: schemas/public_.sql: sync_analytical_stem_to_view(): removed fkey to source.shortname because this prevents reloading individual datasources

schemas/public_.sql: added scrubbed_specific_epithet, scrubbed_species_binomial, which are needed by Jeff Ott's analysis (wiki.vegpath.org/Data_requests)

schemas/public_.sql: analytical_stem_view: scrubbed_morphospecies_binomial: use new taxon_scrub.scrubbed_morphospecies_binomial

bugfix: schemas/public_.sql: _plots_20_tnrs_names: verbatim_name_with_author: use taxonverbatim.taxonomicname rather than taxonlabel.taxonomicname

fix: schemas/util.sql: force_recreate(): renamed to just recreate(), because "force" normally implies that things will be deleted, which this function does not do

fix: schemas/public_.sql: _plots_20_tnrs_names: fixed order of tnrs_taxonomic_status, tnrs_accepted_name_family

schemas/public_.sql: _plots_20_tnrs_names: added tnrs_taxonomic_status, as requested in issue #915

schemas/public_.sql: analytical_stem_view: taxonomic_status: don't filter with map_taxonomic_status() anymore since this is now done by "TNRS"."MatchedTaxon"

bugfix: inputs/.TNRS/schema.sql: map_taxonomic_status(): need to use accepted name instead of scrubbed name (which also includes no-opinion names), as described at http://wiki.vegpath.org/2013-11-14_conference_call#taxonomic-fields. this used to be the accepted name, but got switched when the concatenated name was also used to store the matched name for no-opinion names.

schemas/public_.sql: _plots_20_tnrs_names: use accepted_morphospecies_binomial, which is now available directly in the TNRS results, instead of just the morphospecies_suffix, as requested in issue #915

bugfix: schemas/public_.sql: plots_20_tnrs_names: tnrs_accepted*: use new accepted_*, not scrubbed_*, as scrubbed_* does not contain only the accepted name (as implied by the tnrs_accepted_* column name)

bugfix: schemas/public_.sql: _plots_20_tnrs_names: morphospeciesSuffix must still be called morphospecies_suffix, not taxon_morphospecies, because that column has not been updated to the taxon_morphospecies yet (fixes r13435)

schemas/public_.sql: _plots_20_tnrs_names: changed to set of columns requested by Martha

schemas/public_.sql: _plots_20_tnrs_names: added morphospecies suffix in order to also validate it

schemas/public_.sql: _plots_20_tnrs_names: added verbatim name in order to also validate the formation of the concatenated name

schemas/public_.sql: merged _plots_20_tnrs_input_names, _plots_21_tnrs_output_names into _plots_20_tnrs_names so the input and output names can be compared side-by-side

schemas/public_.sql: _plots_20_tnrs_input_names, _plots_21_tnrs_output_names: added Brad's comments

schemas/vegbien.sql: _plots_21_tnrs_output_names: use scrubbed_unique_taxon_name (concatenated output name) because this is more similar to the concatenated name used in _plots_20_tnrs_input_names

bugfix: schemas/public_.sql: _plots_21_tnrs_output_names: need to use TNRS output rather than input name

schemas/public_.sql: added _plots_20_tnrs_input_names, _plots_21_tnrs_output_names

schemas/public_.sql: public_validations: schema comment: documented how to run just the output queries on a particular datasource

fix: schemas/public_.sql: rm_all_queries(): also remove output-only result tables

schemas/public_.sql: query_view_regexp(): also match auxiliary tables

schemas/public_.sql: added remake_output_tables(datasource_schema, prefix)

schemas/public_.sql: added output_queries(prefix)

schemas/public_.sql: validation_views(): added prefix param, which can be used for additional subsetting

schemas/public_.sql: query_relations(): removed no longer used max_prefix_len default param

schemas/public_.sql: query_relations(): use new public_validations.truncated_prefixed_name_regexp(), which does not require passing in a max_prefix_len

schemas/vegbien.sql: added public_validations.truncated_prefixed_name_regexp(), which does not require passing in a max_prefix_len

schemas/public_.sql: rm_output_queries(), validation_views(): use public_validations.query_view_regexp()

schemas/public_.sql: added public_validations.query_view_regexp()

bugfix: schemas/public_.sql: public_validations.rm_output_queries(): default regexp needs to be anchored at beginning of string, and should match the regexp in public_validations.validation_views()

schemas/public_.sql: _km_to_m(): made it a wrapper around util._km_to_m() so this function is only in one place

schemas/Makefile: vegbien.sql: also include geoscrub, TNRS schemas, as requested in the 2014-04-10 conference call (wiki.vegpath.org/2014-04-10_conference_call#VegBIEN-schema). this involves having a separate public_.sql file for restoring the public schema.