/ - Diff - BIEN 3 - NCEAS Projects

« Previous | Next »

Revision 12608

Added by Aaron Marcuse-Kubitza almost 11 years ago

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

     SET search_path TO public_validations, public;
     CREATE OR REPLACE VIEW _plots_01_count_of_projects AS
     -- _plots_01_count_of_projects
      SELECT count(*) AS projects
        FROM (project p
        JOIN source s ON ((p.source_id = s.source_id)))
       WHERE (s.shortname = ("current_schema"())::text);
     CREATE OR REPLACE VIEW _plots_02_list_of_project_names AS
     -- _plots_02_list_of_project_names
      SELECT p.projectname AS project_name
        FROM (project p
        JOIN source s ON ((p.source_id = s.source_id)))
       WHERE (s.shortname = ("current_schema"())::text);
     CREATE OR REPLACE VIEW _plots_03_count_of_all_plots_in_this_source AS
     -- _plots_03_count_of_all_plots_in_this_source
      SELECT count(*) AS plots
        FROM (((plot l
        JOIN locationevent le ON ((l.location_id = le.location_id)))
-...
        JOIN source s ON ((p.source_id = s.source_id)))
       WHERE (s.shortname = ("current_schema"())::text);
     CREATE OR REPLACE VIEW _plots_04_count_of_plots_in_each_project_in_this_source AS
     -- _plots_04_count_of_plots_in_each_project_in_this_source
      SELECT p.projectname AS project_name,
         count(DISTINCT l.location_id) AS plots
        FROM (((plot l
-...
       WHERE (s.shortname = ("current_schema"())::text)
       GROUP BY p.projectname;
     CREATE OR REPLACE VIEW _plots_05_list_of_plot_codes_by_project AS
     -- _plots_05_list_of_plot_codes_by_project
      SELECT p.projectname AS project_name,
         l.authorlocationcode AS plot_code
        FROM (((plot l
-...
        JOIN source s ON ((p.source_id = s.source_id)))
       WHERE (s.shortname = ("current_schema"())::text);
     CREATE OR REPLACE VIEW _plots_06_list_of_plots_with_stem_measurements AS
     -- _plots_06_list_of_plots_with_stem_measurements
      SELECT project.projectname AS project_name,
         plot.authorlocationcode AS plot_code
        FROM ((plot
-...
     -- ------------------
     -- 7.List of plots with counts of individuals per species
     -- ------------------
     CREATE OR REPLACE VIEW _plots_07_list_of_plots_with_counts_of_individuals_per_species AS
     -- _plots_07_list_of_plots_with_counts_of_individuals_per_species
     -- ------------------
     -- 8.List of plots which use percent cover
     -- ------------------
     CREATE OR REPLACE VIEW _plots_08_list_of_plots_which_use_percent_cover AS
     -- _plots_08_list_of_plots_which_use_percent_cover
-...
     -- ------------------
     -- 9.List of plots which use line-intercept
     -- ------------------
     CREATE OR REPLACE VIEW _plots_09_list_of_plots_which_use_line_intercept AS
     -- _plots_09_list_of_plots_which_use_line_intercept
     CREATE OR REPLACE VIEW _plots_10_count_of_individuals_per_plot_in_each_project AS
     -- _plots_10_count_of_individuals_per_plot_in_each_project
      SELECT p.projectname AS project_name,
         l.authorlocationcode AS plot_code,
         sum(ao.count) AS individuals
-...
     Note 2: Does this method requires that plots have 2-level nesting of subplots within plots?
     */
     CREATE OR REPLACE VIEW _plots_10a_aggregate_observation_individual_counts AS
     -- _plots_10a_aggregate_observation_individual_counts
      SELECT p.projectname AS project_name,
         l.authorlocationcode AS plot_code,
         o.sourceaccessioncode AS individual_id,
-...
     Note 1: `individuals` should be NULL for plots which use percent cover or line-intercept methods.
     */
     CREATE OR REPLACE VIEW _plots_11_count_of_stems_per_plot_in_each_project AS
     -- _plots_11_count_of_stems_per_plot_in_each_project
      SELECT p.projectname AS project_name,
         l.authorlocationcode AS plot_code,
         count(DISTINCT so.stemobservation_id) AS stems
-...
     Method: count records in stemobservation table
     */
     CREATE OR REPLACE VIEW _plots_12_count_of_verbatim_taxa_per_plot_in_each_project AS
     -- _plots_12_count_of_verbatim_taxa_per_plot_in_each_project
      SELECT p.projectname AS project_name,
         l.authorlocationcode AS plot_code,
         count(DISTINCT btrim((((((((''::text || COALESCE(tv.family, ''::text)) || ' '::text) || COALESCE(tv.genus, ''::text)) || ' '::text) || COALESCE(tv.specific_epithet, ''::text)) || ' '::text) || COALESCE(tv.morphospecies, ''::text)))) AS taxa
-...
     NOT YET READY, NEED TO INCLUDE SUBSPECIES, IF ANY
     */
     CREATE OR REPLACE VIEW _plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_p AS
     -- _plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_p
      SELECT DISTINCT p.projectname AS project_name,
         l.authorlocationcode AS plot_code,
         btrim((((((((''::text || COALESCE(tv.family, ''::text)) || ' '::text) || COALESCE(tv.genus, ''::text)) || ' '::text) || COALESCE(tv.specific_epithet, ''::text)) || ' '::text) || COALESCE(tv.morphospecies, ''::text))) AS taxon
-...
     Note: Must perform equivalent concatenation of taxonomic field in source db.
     */
     CREATE OR REPLACE VIEW _plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pr AS
     -- _plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_pr
      SELECT DISTINCT p.projectname AS project_name,
         l.authorlocationcode AS plot_code,
         btrim((((((((''::text || COALESCE(tv.family, ''::text)) || ' '::text) || COALESCE(tv.genus, ''::text)) || ' '::text) || COALESCE(tv.specific_epithet, ''::text)) || ' '::text) || COALESCE(tv.morphospecies, ''::text))) AS taxon,
-...
     Note: Must do equivalent concatenation of taxonomic field in source db.
     */
     CREATE OR REPLACE VIEW _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro AS
     -- _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro
      SELECT DISTINCT p.projectname AS project_name,
         l.authorlocationcode AS plot_code,
         btrim((((((((''::text || COALESCE(tv.family, ''::text)) || ' '::text) || COALESCE(tv.genus, ''::text)) || ' '::text) || COALESCE(tv.specific_epithet, ''::text)) || ' '::text) || COALESCE(tv.morphospecies, ''::text))) AS taxon,
-...
     --       stratum) with plots without subplots or strata (one cover measure
     --       per species per plot.
     -- ------------------
     CREATE OR REPLACE VIEW _plots_16_intercepts_for_each_verb_taxon_in_each_plot_in_each_project AS
     -- _plots_16_intercepts_for_each_verb_taxon_in_each_plot_in_each_project
     CREATE OR REPLACE VIEW _plots_17_count_of_subplots_per_plot_for_each_project AS
     -- _plots_17_count_of_subplots_per_plot_for_each_project
      SELECT p.projectname AS project_name,
         l.authorlocationcode AS plot_code,
         count(DISTINCT sub_locationevent.locationevent_id) AS subplots
-...
       GROUP BY p.projectname, l.authorlocationcode
       ORDER BY p.projectname, l.authorlocationcode;
     CREATE OR REPLACE VIEW _plots_18_list_of_subplots_codes_for_each_plot_for_each_project AS
     -- _plots_18_list_of_subplots_codes_for_each_plot_for_each_project
      SELECT p.projectname AS project_name,
         l.authorlocationcode AS plot_code,
         sub_locationevent.authoreventcode AS subplot_code

Also available in: Unified diff

Project

General

Profile

Revision 12608

Added by Aaron Marcuse-Kubitza almost 11 years ago