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: 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
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: 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: 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.
