fix: schemas/public_.sql: native_status_resolver: don't include rows with New World coordinates that don't also have New World country names, since the NSR only uses the country name

schemas/public_.sql: native_status_resolver: removed rows with is_geovalid NULL, at Brad's request. note that this removes valid rows with standardized country names.

fix: schemas/public_.sql: native_status_resolver: added country IS NOT NULL filter requested by Brad

fix: schemas/public_.sql: native_status_resolver: remove the id because this prevents SELECT DISTINCT from having the desired effect. instead, the results will be joined back using the other columns.

schemas/public_.sql: added native_status_resolver view, requested by Brad (wiki.vegpath.org/Data_requests)

schemas/public_.sql: viewFullOccurrence_*: renamed to view_full_occurrence_* at Brian M's and Martha's request (e-mails from Martha on 2014-8-12 at 17:37PT, and from Brian M on 2014-8-13 at 16:21PT). note that this change has already been made on vegbiendev.

schemas/public_.sql: added view_full_occurrence_individual_*GBIF, analytical_stem_*GBIF requested by Mark. note that this change has already been made on vegbiendev.

schemas/public_.sql: view_full_occurrence_individual, analytical_stem: renamed columns to shortened names at Brian M's request (e-mail from Martha on 2014-8-12 at 17:37PT). note that this change has already been made on vegbiendev.

schemas/public_.sql: analytical_stem_view: renamed to analytical_stem since this replaces the materialized analytical_stem table. note that this change has already been made on vegbiendev.

schemas/public_.sql: viewFullOccurrence_*: renamed to view_full_occurrence_* at Brian M's request (e-mail from Martha on 2014-8-12 at 17:37PT). note that this change has already been made on vegbiendev.

schemas/public_.sql: viewFullOccurrence_individual: re-ran viewFullOccurrence_individual_view_modify() to udpate this

schemas/public_.sql: viewFullOccurrence_individual_view: CREATE INDEX runtime: documented 2-column runtime (~2 h)

schemas/public_.sql: viewFullOccurrence_individual_view: added "after updating this" instructions

schemas/public_.sql: viewFullOccurrence_individual_view: documented CREATE INDEX runtime (10 min - 1.5 h depending on the datatype and % populated)

bugfix: schemas/public_.sql: *_view_modify(): need to set the search_path so that tables are created in the same schema as the function, rather than whichever schema happens to be at the beginning of the search_path

bugfix: schemas/public_.sql: analytical_stem_view_modify(): updated to support being used directly instead of via a materialized table

schemas/public_.sql: removed no longer used analytical_stem. use analytical_stem_view or viewFullOccurrence_individual instead.

fix: schemas/public_.sql: viewfulloccurrence_individual_view_modify(): fixed capitalization in function name (needs "" to preserve case)

schemas/public_.sql: other derived views: use viewFullOccurrence_individual instead of analytical_stem because analytical_stem is no longer materialized (viewFullOccurrence_individual_view is materialized instead)

schemas/public_.sql: validation views: use analytical_stem_view instead of analytical_stem because analytical_stem is no longer materialized (viewFullOccurrence_individual_view is materialized instead)

schemas/public_.sql: derived views: use analytical_stem_view instead of analytical_stem because analytical_stem is no longer materialized (viewFullOccurrence_individual_view is materialized instead)

fix: schemas/public_.sql: analytical_stem_view: regenerated columns list for viewFullOccurrence_individual (instead of viewFullOccurrence_individual_view)

schemas/public_.sql: removed no longer used threatened_taxonlabel_view. use iucn_red_list_view instead.

schemas/public_.sql: viewFullOccurrence_individual_view: documented materialize time (22 h)

schemas/public_.sql: viewFullOccurrence_*: documented materialize time

schemas/public_.sql: analytical_stem_view: use new materialized viewFullOccurrence_individual

schemas/public_.sql: added viewFullOccurrence_individual by running viewFullOccurrence_individual_view_modify()

schemas/public_.sql: added viewFullOccurrence_individual_view_modify(), analogous to analytical_stem_view_modify()

schemas/public_.sql: iucn_red_list_view: use taxon_scrub instead of ValidMatchedTaxon since they are equivalent

fix: inputs/.TNRS/schema.sql: taxon_scrub: merged synonymous columns

bugfix: schemas/public_.sql: tnrs_input_name: use taxon_match instead of taxon_best_match because there is no index on taxon_match that includes just the filters used by taxon_best_match

schemas/public_.sql: tnrs_input_name: use taxon_best_match now that it's equivalent to MatchedTaxon

fix: inputs/.TNRS/schema.sql: MatchedTaxon: merged synonymous columns

schemas/public_.sql: added viewFullOccurrence_{CVS,VegBank,NCU} for Bob

schemas/public_.sql: *_view_modify(): added repopulate param, which is passed to rematerialize_view()

schemas/public_.sql: *_view_modify(): use util.rematerialize_view(), which now supports table mods

schemas/public_.sql: analytical_stem_view: use viewFullOccurrence_individual_view to avoid duplicated code between the views. this moves the stem-related columns to the end of the view.

schemas/public_.sql: added viewFullOccurrence_individual_view

schemas/public_.sql: analytical_stem: synced to analytical_stem_view

schemas/util.sql: added copy_struct(from_ regtype, to_ text) and copy_data(from_ regtype, to_ text), and use these in a general-purpose copy()

fix: inputs/.TNRS/schema.sql: taxon_match: added back * prefixes on TNRS-namespace column names

inputs/.TNRS/schema.sql: MatchedTaxon: renamed output columns to match input columns, so that the nested select would not be performing any renamings

schemas/public_.sql: removed no longer used threatened_taxonlabel. use iucn_red_list instead.

fix: schemas/public_.sql: analytical_stem_view: don't use threatened_taxonlabel, which was never populated correctly. instead, this can eventually be rewritten to use new iucn_red_list.

bugfix: schemas/public_.sql: analytical_stem_view_modify(): added columns: need to include COMMENT statements

schemas/public_.sql: added iucn_red_list_view_modify() and use it in iucn_red_list_view's "after updating this" instructions

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.