bugfix: schemas/vegbien.sql, inputs/NY/validations.sql, validation/aggregating/specimens/qualitative_validations_specimens.sql: _specimens_12_distinct_collector_name_collect_num_date_w_count: dateCollected: cast this to text rather than date because some values for this field are not valid dates and will throw an error if cast to date
validation/aggregating/pipeline/aggregating_validations_pipeline.odg: show that the staging table(s) are denormalized before running the input queries on them. clarified that what is compared are the input and output query results, not the queries themselves.
validation/aggregating/specimens/qualitative_validations_specimens.sql: removed `public.` prefix to avoid cluttering up the SQL
bugfix: schemas/vegbien.sql, validation/aggregating/specimens/qualitative_validations_specimens.sql: _specimens_10_count_number_of_records_by_institution: need to dereference specimenreplicate.duplicate_institutions_sourcelist_id to the corresponding sourcelist.name
validation/aggregating/specimens/qualitative_validations_specimens.sql: synced to schemas/vegbien.sql so that it can be diffed with it to sync qualitative_validations_specimens.sql to the DB
validation/aggregating/pipeline/validations_on_sparse_datasources.odg: not applicable "✓": increased font size so the size of the character matches the surrounding text
validation/aggregating/pipeline/validations_on_sparse_datasources.odg: removed = lines for each input query, because they clutter up the diagram and the "same, so don't need to rewrite" message now shows this as well
validation/aggregating/pipeline/validations_on_sparse_datasources.odg: added the denormalized VegCore schema approach for comparison, as requested by Mark
validation/aggregating/specimens/qualitative_validations_specimens.sql: removed DDL statements, using the steps at wiki.vegpath.org/Aggregating_validations_refactoring#remove-DDL-statements
validation/aggregating/specimens/qualitative_validations_specimens.sql: parameterize queries by datasource
validation/aggregating/**.sql output queries: use `SET join_collapse_limit = 1;` to match public_validations.rematerialize_out_view()
validation/aggregating/specimens/qualitative_validations_specimens.sql: _specimens_12_distinct_collector_name_collect_num_date_w_count: turn off join_collapse_limit instead of enable_mergejoin/enable_hashjoin, because join_collapse_limit is something that we will eventually want to turn off for all queries, which would avoid this query needing special handling. (on the other hand, enable_mergejoin/enable_hashjoin may be necessary for some queries and we probably won't turn them off for all queries.)
added validation/aggregating/pipeline/validations_on_sparse_datasources.odg
validation/aggregating/specimens/qualitative_validations_specimens.sql: implemented _specimens_14_count_of_all_invalid_verbatim_lat_long
validation/aggregating/specimens/qualitative_validations_specimens.sql: implemented _specimens_12_distinct_collector_name_collect_num_date_w_count
validation/aggregating/specimens/qualitative_validations_specimens.sql: _specimens_13_count_of_all_verbatim_and_decimal_lat_long: fixed whitespace
validation/aggregating/specimens/qualitative_validations_specimens.sql: removed trailing whitespace
validation/aggregating/specimens/qualitative_validations_specimens.sql: implemented _specimens_13_count_of_all_verbatim_and_decimal_lat_long
validation/aggregating/specimens/qualitative_validations_specimens.sql: implemented _specimens_11_list_of_three_standard_political_divisions
validation/aggregating/specimens/qualitative_validations_specimens.sql: *_of_species_binomials: switched back to the old queries that use the split-apart ranks instead of the concatenated taxon name. note that these will not work on all specimens datasources, but now that #6,7 were selected to use the concatenated taxon name, this isn't a problem.
validation/aggregating/specimens/qualitative_validations_specimens.sql, NY/qualitative_validations_source_db_NYBG.VegCore.sql, inputs/NY/validations.sql: *_of_species_binomials: renamed columns to species_binomial to reflect reverted query name
validation/aggregating/specimens/qualitative_validations_specimens.sql, NY/qualitative_validations_source_db_NYBG.VegCore.sql, inputs/NY/validations.sql: *_of_verbatim_species_excluding_author: renamed to *_species_binomials for clarity
validation/aggregating/specimens/qualitative_validations_specimens.sql, NY/qualitative_validations_source_db_NYBG.VegCore.sql, inputs/NY/validations.sql: _specimens_04_count_of_unique_verbatim_species_with_author, _specimens_05_list_of_unique_verbatim_species_with_author: switched back to original names because #6,7 now do the same thing as #4,5, so we should include the differing result set of #4,5 for datasources that provide it
validation/aggregating/specimens/qualitative_validations_specimens.sql: implemented _specimens_10_count_number_of_records_by_institution
validation/aggregating/specimens/qualitative_validations_specimens.sql, NY/qualitative_validations_source_db_NYBG.VegCore.sql, inputs/NY/validations.sql: use taxon_name*_with_author everywhere instead of custom column names, for consistency
validation/aggregating/specimens/qualitative_validations_specimens.sql, NY/qualitative_validations_source_db_NYBG.VegCore.sql, inputs/NY/validations.sql: *_of_verbatim_subspecific_taxa_without_author, etc.: renamed to *_with_author because these now use the concatenated name, rather than the without-author name that only some specimens datasources provide
validation/aggregating/specimens/qualitative_validations_specimens.sql: implemented _specimens_06_count_of_unique_verb_subsp_taxa_without_author, _specimens_07_list_of_verbatim_subspecific_taxa_without_author
validation/aggregating/specimens/qualitative_validations_specimens.sql, NY/qualitative_validations_source_db_NYBG.VegCore.sql, inputs/NY/validations.sql: *_verbatim_species_without_author, etc.: renamed to *_with_author because these now use the concatenated name, rather than the without-author name that only some specimens datasources provide
validation/aggregating/specimens/qualitative_validations_specimens.sql: removed extra ; at ends of queries
validation/aggregating/specimens/qualitative_validations_specimens.sql: use the concatenated taxon name instead of concatenating the ranks, as decided in the 2014-03-27 conference call (wiki.vegpath.org/2014-03-27_conference_call#aggregating-validations)
validation/aggregating/specimens/qualitative_validations_specimens.sql: use taxonoccurrence instead of location as the table that all specimens should have, as decided in the 2014-03-27 conference call (wiki.vegpath.org/2014-03-27_conference_call#aggregating-validations)
validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: stage I: clarified that the database input is intended to be a normalized input, and its corresonding output is intended to be denormalized
bugfix: validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: _specimens_16_list_distinct_specimen_descriptions: should use DISTINCT
validation/aggregating/specimens/qualitative_validations_specimens.sql: implemented _specimens_16_list_distinct_specimen_descriptions
validation/aggregating/specimens/qualitative_validations_specimens.sql: implemented _specimens_15_list_distinct_locality_descriptions
validation/aggregating/specimens/qualitative_validations_specimens.sql: implemented _specimens_09_list_of_unique_verbatim_author_taxa_with_genus
validation/aggregating/specimens/qualitative_validations_specimens.sql: implemented _specimens_08_count_of_unique_verbatim_author_taxa_with_genus
validation/aggregating/specimens/qualitative_validations_specimens.sql: implemented _specimens_05_list_of_verbatim_species_excluding_author
validation/aggregating/specimens/qualitative_validations_specimens.sql: implemented _specimens_04_count_of_unique_verbatim_species_without_author
validation/aggregating/specimens/qualitative_validations_specimens.sql: implemented _specimens_03_list_of_verbatim_families
validation/aggregating/specimens/qualitative_validations_specimens.sql: implemented _specimens_02_count_of_unique_verbatim_families
validation/aggregating/specimens/qualitative_validations_specimens.sql: implemented _specimens_01_count_of_total_records_specimens_in_source_db
validation/aggregating/specimens/qualitative_validations_specimens.sql: added config statements for datasource and query planner
fix: validation/aggregating/specimens/qualitative_validations_specimens.sql: use pg_dump's formatting for COMMENT ON to facilitate diffing against a pg_dump export of the DDL statements
validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: removed DDL statements so that running the query file does not alter the database, using the steps at wiki.vegpath.org/Aggregating_validations_refactoring#remove-DDL-statements
validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: updated to DB, which pg_dump-formats the views
validation/**.sql: replaced CREATE OR REPLACE VIEW with CREATE VIEW to match pg_dump output for diffing
fix: validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: use pg_dump's formatting for COMMENT ON to facilitate diffing against a pg_dump export of the DDL statements
fix: validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: decimalLatitude/decimalLongitude: need to cast to double precision for numeric comparisons
fix: validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: CollectedDate: updated for refreshed NY data
validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: fixed typos in column aliases
validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: translated column names to VegCore, using `bin/in_place validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql env text=1 bin/repl inputs/NY/Ecatalog_all/map.csv` from the steps at wiki.vegpath.org/Aggregating_validations_refactoring#translate-to-Postgres
bugfix: validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: need to enclose additional mixed-case identifiers in "", using the steps at wiki.vegpath.org/Aggregating_validations_refactoring#translate-to-Postgres
validation/aggregating/specimens/qualitative_validations_specimens.sql, NY/qualitative_validations_source_db_NYBG.VegCore.sql: abbreviated view names longer than 63 chars to prevent them from being truncated
validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: escape any ' inside '...' by doubling them
validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: translated SQL to Postgres
validation/aggregating/specimens/qualitative_validations_specimens.sql, NY/qualitative_validations_source_db_NYBG.VegCore.sql: changed /* */ comments to COMMENT ON comments, using the steps at wiki.vegpath.org/Aggregating_validations_refactoring#prepend-CREATE-VIEW
validation/aggregating/specimens/qualitative_validations_specimens.sql, NY/qualitative_validations_source_db_NYBG.VegCore.sql: removed no longer needed -- comments containing the query name, using the steps at wiki.vegpath.org/Aggregating_validations_refactoring#prepend-CREATE-VIEW
validation/aggregating/specimens/qualitative_validations_specimens.sql: moved notes to comments to after the query
validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: moved notes to comments to after the query
validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: moved "Check" comments to after the query, using the steps at wiki.vegpath.org/Aggregating_validations_refactoring#translate-to-Postgres
validation/aggregating/specimens/qualitative_validations_specimens.sql: removed "Check: should return [#] rows" comments because these only apply to the NY results, not to all specimens datasources
validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: prepended CREATE VIEW, using the steps at wiki.vegpath.org/Aggregating_validations_refactoring#prepend-CREATE-VIEW and the same abbreviations as the output queries (validation/aggregating/specimens/qualitative_validations_specimens.sql)
validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: synced "Check" comments to output queries validation/aggregating/specimens/qualitative_validations_specimens.sql
validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: enclosed mixed-case identifiers in "" using the steps at wiki.vegpath.org/Aggregating_validations_refactoring#translate-to-Postgres
validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: updated to use column names for refreshed NY data
validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: use our staging tables instead of the BIEN2 MySQL staging tables
validation/aggregating/specimens/**.sql: removed trailing whitespace, using the steps at wiki.vegpath.org/Aggregating_validations_refactoring#translate-to-Postgres
archived validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.sql
added validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql, copied from qualitative_validations_source_db_NYBG.sql
validation/aggregating/specimens/qualitative_validations_specimens.sql: added ; at end of `CREATE OR REPLACE VIEW` statements
validation/aggregating/specimens/qualitative_validations_specimens.sql: removed input-query-specific comments
validation/aggregating/specimens/qualitative_validations_specimens.sql: reworded rowcount check comments to apply to the output queries
validation/aggregating/specimens/qualitative_validations_specimens.sql: shortened view names to fit within the 63-char limit without truncation
fix: validation/aggregating/specimens/qualitative_validations_specimens.sql: changed "Full inner join" to "Full outer join" because a FULL JOIN is a type of outer join, not inner join
validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: updated to DB
fix: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: removed `public.` qualifier
validation/aggregating/plots/FIA/bien3_validations_fia_input.sql: _plots_19_count_of_inventories_per_plot_in_each_project: renamed to _plots_19_count_of_censuses_per_plot_in_each_project for clarity
validation/aggregating/plots/FIA/bien3_validations_fia_input.sql*: updated from Brad's latest e-mail
validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: added `SET enable_seqscan = off;` to match what is done by rematerialize_out_view() to run the queries properly
bugfix: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: need to escape the quotes in \set ... 'SALVIAS'
validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: removed `SET search_path TO public;` since this is the default
validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: search_path: removed public_validations since we are not creating views
validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: use psql var :datasource instead of current_schema() so that the queries are runnable without special configuration of the search_path
validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: removed `CREATE OR REPLACE VIEW` so the validations views are not unintentionally replaced when running this file
validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: updated from DB
bugfix: *.sql: public.source_by_shortname(): need to wrap it in a nested SELECT because Postgres incorrectly does not constant-fold (inline) it, leading to a slowdown when it is therefore run many times. this is done using the steps at wiki.vegpath.org/Postgres_queries#wrap-function-call-in-nested-SELECT .
fix: validation/aggregating/plots/SALVIAS/bien3_validations_salvias_db_original.VegCore.sql: renamed truncated views to match the output queries
bugfix: validation/aggregating/plots/SALVIAS/bien3_validations_salvias_db_original.VegCore.sql: can't double-JOIN like in MySQL (`JOIN a JOIN b ON a_conds AND b_conds`), so split double-JOIN into two JOINs w/ own ON clauses
