Project

General

Profile

« Previous | Next » 

Revision 12065

Added by Aaron Marcuse-Kubitza almost 11 years ago

validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: added CREATE VIEW prefixes using the steps at wiki.vegpath.org/Aggregating_validations_refactoring#prepend-CREATE-VIEW

View differences:

trunk/validation/aggregating/plots/bien3_validations_salvias_vegbien.sql
25 25 
-- 1. Count of projects
26 26 
-- Check: identical count in source db
27 27 
-- ------------------
28 
CREATE OR REPLACE VIEW traits_1_count_of_projects AS
28 
CREATE OR REPLACE VIEW plots_1_count_of_projects AS
29 29 
SELECT COUNT(*) AS projects
30 30 
FROM project p JOIN source s
31 31 
ON p.source_id=s.source_id
......
35 35 
-- 2. List of project names
36 36 
-- Check: join to source db returns same number of rows
37 37 
-- ------------------
38 
CREATE OR REPLACE VIEW traits_2_list_of_project_names AS
38 
CREATE OR REPLACE VIEW plots_2_list_of_project_names AS
39 39 
SELECT p.projectname
40 40 
FROM project p JOIN source s
41 41 
ON p.source_id=s.source_id
......
45 45 
-- 3. Count of all plots in this source
46 46 
-- Check: identical count in source db
47 47 
-- ------------------
48 
CREATE OR REPLACE VIEW traits_3_count_of_all_plots_in_this_source AS
48 
CREATE OR REPLACE VIEW plots_3_count_of_all_plots_in_this_source AS
49 49 
SELECT COUNT(*) AS plots
50 50 
FROM location l JOIN locationevent le
51 51 
ON l.location_id=le.location_id
......
58 58 
-- ------------------
59 59 
-- 4. Count of plots in each project in this source
60 60 
-- ------------------
61 
CREATE OR REPLACE VIEW traits_4_count_of_plots_in_each_project_in_this_source AS
61 
CREATE OR REPLACE VIEW plots_4_count_of_plots_in_each_project_in_this_source AS
62 62 
SELECT p.projectname, COUNT(DISTINCT l.location_id) AS plots
63 63 
FROM location l JOIN locationevent le
64 64 
ON l.location_id=le.location_id
......
73 73 
-- 5.List of plot codes by project
74 74 
-- Check: join to source db by all columns returns same number of rows
75 75 
-- ------------------
76 
CREATE OR REPLACE VIEW traits_5_list_of_plot_codes_by_project AS
76 
CREATE OR REPLACE VIEW plots_5_list_of_plot_codes_by_project AS
77 77 
SELECT p.projectname, l.authorlocationcode AS plotCode
78 78 
FROM location l JOIN locationevent le
79 79 
ON l.location_id=le.location_id
......
86 86 
-- ------------------
87 87 
-- 6. List of plots with stem measurements
88 88 
-- ------------------
89 
CREATE OR REPLACE VIEW traits_6_list_of_plots_with_stem_measurements AS
89 
CREATE OR REPLACE VIEW plots_6_list_of_plots_with_stem_measurements AS
90 90 

  		




  91
  91
  
    
SELECT authorlocationcode


  		




  92
  92
  
    
FROM top_plot


  		




  ......




  108
  108
  
    
-- ------------------


  		




  109
  109
  
    
-- 7.List of plots with counts of individuals per species


  		




  110
  110
  
    
-- ------------------


  		




  111
  
  
    
CREATE OR REPLACE VIEW traits_7_list_of_plots_with_counts_of_individuals_per_species AS


  		




  
  111
  
    
CREATE OR REPLACE VIEW plots_7_list_of_plots_with_counts_of_individuals_per_species AS


  		




  112
  112
  
    

  		




  113
  113
  
    

  		




  114
  114
  
    

  		




  115
  115
  
    
-- ------------------


  		




  116
  116
  
    
-- 8.List of plots which use percent cover


  		




  117
  117
  
    
-- ------------------


  		




  118
  
  
    
CREATE OR REPLACE VIEW traits_8_list_of_plots_which_use_percent_cover AS


  		




  
  118
  
    
CREATE OR REPLACE VIEW plots_8_list_of_plots_which_use_percent_cover AS


  		




  119
  119
  
    

  		




  120
  120
  
    

  		




  121
  121
  
    

  		




  ......




  123
  123
  
    
-- ------------------


  		




  124
  124
  
    
-- 9.List of plots which use line-intercept


  		




  125
  125
  
    
-- ------------------


  		




  126
  
  
    
CREATE OR REPLACE VIEW traits_9_list_of_plots_which_use_line_intercept AS


  		




  
  126
  
    
CREATE OR REPLACE VIEW plots_9_list_of_plots_which_use_line_intercept AS


  		




  127
  127
  
    

  		




  128
  128
  
    

  		




  129
  129
  
    
-- ------------------


  		




  ......




  135
  135
  
    
-- Note 2: Does this method requires that plots have 2-level nesting of


  		




  136
  136
  
    
--       subplots within plots?


  		




  137
  137
  
    
-- ------------------


  		




  138
  
  
    
CREATE OR REPLACE VIEW traits_10_count_of_individuals_per_plot_in_each_project AS


  		




  
  138
  
    
CREATE OR REPLACE VIEW plots_10_count_of_individuals_per_plot_in_each_project AS


  		




  139
  139
  
    
select p.projectname, l.authorlocationcode as plotcode, sum(ao.count) as individuals


  		




  140
  140
  
    
from project p join source s 


  		




  141
  141
  
    
on p.source_id=s.source_id


  		




  ......




  153
  153
  
    
-- 


  		




  154
  154
  
    
-- Method: count records in stemobservation table


  		




  155
  155
  
    
-- ------------------


  		




  156
  
  
    
CREATE OR REPLACE VIEW traits_11_count_of_stems_per_plot_in_each_project AS


  		




  
  156
  
    
CREATE OR REPLACE VIEW plots_11_count_of_stems_per_plot_in_each_project AS


  		




  157
  157
  
    
select p.projectname, l.authorlocationcode as plotcode, count(distinct stemobservation_id) as stems


  		




  158
  158
  
    
from project p join source s 


  		




  159
  159
  
    
on p.source_id=s.source_id


  		




  ......




  174
  174
  
    
-- Note: Must perform equivalent concatenation of taxonomic field in source db.


  		




  175
  175
  
    
-- NOT YET READY, NEED TO INCLUDE SUBSPECIES, IF ANY


  		




  176
  176
  
    
-- ------------------


  		




  177
  
  
    
CREATE OR REPLACE VIEW traits_12_count_of_verbatim_taxa_per_plot_in_each_project AS


  		




  
  177
  
    
CREATE OR REPLACE VIEW plots_12_count_of_verbatim_taxa_per_plot_in_each_project AS


  		




  178
  178
  
    
select p.projectname, l.authorlocationcode as plotcode, 


  		




  179
  179
  
    
count(distinct trim('' || coalesce(family::text,'') || ' ' || coalesce(genus::text,'') || ' ' || coalesce(specific_epithet::text,'') || ' ' ||  coalesce(morphospecies::text,'')))


  		




  180
  180
  
    
as taxa


  		




  ......




  195
  195
  
    
-- 


  		




  196
  196
  
    
-- Note: Must perform equivalent concatenation of taxonomic field in source db.


  		




  197
  197
  
    
-- ------------------


  		




  198
  
  
    
CREATE OR REPLACE VIEW traits_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_project AS


  		




  
  198
  
    
CREATE OR REPLACE VIEW plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_project AS


  		




  199
  199
  
    
select distinct p.projectname, l.authorlocationcode as plotcode, 


  		




  200
  200
  
    
trim('' || coalesce(family::text,'') || ' ' || coalesce(genus::text,'') || ' ' || coalesce(specific_epithet::text,'') || ' ' ||  coalesce(morphospecies::text,'')) as taxon


  		




  201
  201
  
    
from project p join source s 


  		




  ......




  214
  214
  
    
-- 


  		




  215
  215
  
    
-- Note: Must do equivalent concatenation of taxonomic field in source db.


  		




  216
  216
  
    
-- ------------------


  		




  217
  
  
    
CREATE OR REPLACE VIEW traits_14_count_of_individuals_per_verbatim_taxon_per_plot_in_each_project_ AS


  		




  
  217
  
    
CREATE OR REPLACE VIEW plots_14_count_of_individuals_per_verbatim_taxon_per_plot_in_each_project_ AS


  		




  218
  218
  
    
select distinct p.projectname, l.authorlocationcode as plotcode, 


  		




  219
  219
  
    
trim('' || coalesce(family::text,'') || ' ' || coalesce(genus::text,'') || ' ' || coalesce(specific_epithet::text,'') || ' ' ||  coalesce(morphospecies::text,'')) as taxon,


  		




  220
  220
  
    
sum(ao.count) as individuals


  		




  ......




  238
  238
  
    
-- Check: join to source db by all columns, returns same number of rows


  		




  239
  239
  
    
-- Note: Must do equivalent concatenation of taxonomic field in source db.


  		




  240
  240
  
    
-- ------------------


  		




  241
  
  
    
CREATE OR REPLACE VIEW traits_15_percent_cover_of_each_verbatim_taxon_in_each_plot_in_each_project AS


  		




  
  241
  
    
CREATE OR REPLACE VIEW plots_15_percent_cover_of_each_verbatim_taxon_in_each_plot_in_each_project AS


  		




  242
  242
  
    
select distinct p.projectname, l.authorlocationcode as plotcode, 


  		




  243
  243
  
    
trim('' || coalesce(family::text,'') || ' ' || coalesce(genus::text,'') || ' ' || coalesce(specific_epithet::text,'') || ' ' ||  coalesce(morphospecies::text,'')) as taxon,


  		




  244
  244
  
    
sum(ci.coverpercent) as totalpercentcover


  		




  ......




  269
  269
  
    
--       stratum) with plots without subplots or strata (one cover measure 


  		




  270
  270
  
    
--       per species per plot.


  		




  271
  271
  
    
-- ------------------


  		




  272
  
  
    
CREATE OR REPLACE VIEW traits_16_intercept_values_for_each_verbatim_taxon_in_each_plot_in_each_project AS


  		




  
  272
  
    
CREATE OR REPLACE VIEW plots_16_intercept_values_for_each_verbatim_taxon_in_each_plot_in_each_project AS


  		




  273
  273
  
    

  		




  274
  274
  
    

  		




  275
  275
  
    

  		




  ......




  279
  279
  
    
-- ------------------


  		




  280
  280
  
    
-- 17. Count of subplots per plot, for each project


  		




  281
  281
  
    
-- ------------------


  		




  282
  
  
    
CREATE OR REPLACE VIEW traits_17_count_of_subplots_per_plot_for_each_project AS


  		




  
  282
  
    
CREATE OR REPLACE VIEW plots_17_count_of_subplots_per_plot_for_each_project AS


  		




  283
  283
  
    
select p.projectname, l.authorlocationcode as plotcode, 


  		




  284
  284
  
    
count(distinct sub_locationevent.locationevent_id) as subplots


  		




  285
  285
  
    
from project p join source s 


  		




  ......




  295
  295
  
    
-- 18. List of subplots codes for each plot for each project


  		




  296
  296
  
    
-- DOESN'T YET WORK PROPERLY


  		




  297
  297
  
    
-- ------------------


  		




  298
  
  
    
CREATE OR REPLACE VIEW traits_18_list_of_subplots_codes_for_each_plot_for_each_project AS


  		




  
  298
  
    
CREATE OR REPLACE VIEW plots_18_list_of_subplots_codes_for_each_plot_for_each_project AS


  		




  299
  299
  
    
select p.projectname, 


  		




  300
  300
  
    
l.authorlocationcode as plotcode, 


  		




  301
  301
  
    
sub_locationevent.authoreventcode as subplotCode


  		












Also available in:  Unified diff