Project

General

Profile

« Previous | Next » 

Revision 12064

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 29 
SELECT COUNT(*) AS projects
29 30 
FROM project p JOIN source s
30 31 
ON p.source_id=s.source_id
......
34 35 
-- 2. List of project names
35 36 
-- Check: join to source db returns same number of rows
36 37 
-- ------------------
38 
CREATE OR REPLACE VIEW traits_2_list_of_project_names AS
37 39 
SELECT p.projectname
38 40 
FROM project p JOIN source s
39 41 
ON p.source_id=s.source_id
......
43 45 
-- 3. Count of all plots in this source
44 46 
-- Check: identical count in source db
45 47 
-- ------------------
48 
CREATE OR REPLACE VIEW traits_3_count_of_all_plots_in_this_source AS
46 49 
SELECT COUNT(*) AS plots
47 50 
FROM location l JOIN locationevent le
48 51 
ON l.location_id=le.location_id
......
55 58 
-- ------------------
56 59 
-- 4. Count of plots in each project in this source
57 60 
-- ------------------
61 
CREATE OR REPLACE VIEW traits_4_count_of_plots_in_each_project_in_this_source AS
58 62 
SELECT p.projectname, COUNT(DISTINCT l.location_id) AS plots
59 63 
FROM location l JOIN locationevent le
60 64 
ON l.location_id=le.location_id
......
69 73 
-- 5.List of plot codes by project
70 74 
-- Check: join to source db by all columns returns same number of rows
71 75 
-- ------------------
76 
CREATE OR REPLACE VIEW traits_5_list_of_plot_codes_by_project AS
72 77 
SELECT p.projectname, l.authorlocationcode AS plotCode
73 78 
FROM location l JOIN locationevent le
74 79 
ON l.location_id=le.location_id
......
81 86 
-- ------------------
82 87 
-- 6. List of plots with stem measurements
83 88 
-- ------------------
89 
CREATE OR REPLACE VIEW traits_6_list_of_plots_with_stem_measurements AS
84 90 

  		




  85
  91
  
    
SELECT authorlocationcode


  		




  86
  92
  
    
FROM top_plot


  		




  ......




  102
  108
  
    
-- ------------------


  		




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


  		




  104
  110
  
    
-- ------------------


  		




  
  111
  
    
CREATE OR REPLACE VIEW traits_7_list_of_plots_with_counts_of_individuals_per_species AS


  		




  105
  112
  
    

  		




  106
  113
  
    

  		




  107
  114
  
    

  		




  108
  115
  
    
-- ------------------


  		




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


  		




  110
  117
  
    
-- ------------------


  		




  
  118
  
    
CREATE OR REPLACE VIEW traits_8_list_of_plots_which_use_percent_cover AS


  		




  111
  119
  
    

  		




  112
  120
  
    

  		




  113
  121
  
    

  		




  ......




  115
  123
  
    
-- ------------------


  		




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


  		




  117
  125
  
    
-- ------------------


  		




  
  126
  
    
CREATE OR REPLACE VIEW traits_9_list_of_plots_which_use_line_intercept AS


  		




  118
  127
  
    

  		




  119
  128
  
    

  		




  120
  129
  
    
-- ------------------


  		




  ......




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


  		




  127
  136
  
    
--       subplots within plots?


  		




  128
  137
  
    
-- ------------------


  		




  
  138
  
    
CREATE OR REPLACE VIEW traits_10_count_of_individuals_per_plot_in_each_project AS


  		




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


  		




  130
  140
  
    
from project p join source s 


  		




  131
  141
  
    
on p.source_id=s.source_id


  		




  ......




  143
  153
  
    
-- 


  		




  144
  154
  
    
-- Method: count records in stemobservation table


  		




  145
  155
  
    
-- ------------------


  		




  
  156
  
    
CREATE OR REPLACE VIEW traits_11_count_of_stems_per_plot_in_each_project AS


  		




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


  		




  147
  158
  
    
from project p join source s 


  		




  148
  159
  
    
on p.source_id=s.source_id


  		




  ......




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


  		




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


  		




  165
  176
  
    
-- ------------------


  		




  
  177
  
    
CREATE OR REPLACE VIEW traits_12_count_of_verbatim_taxa_per_plot_in_each_project AS


  		




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


  		




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


  		




  168
  180
  
    
as taxa


  		




  ......




  183
  195
  
    
-- 


  		




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


  		




  185
  197
  
    
-- ------------------


  		




  
  198
  
    
CREATE OR REPLACE VIEW traits_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_project AS


  		




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


  		




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


  		




  188
  201
  
    
from project p join source s 


  		




  ......




  201
  214
  
    
-- 


  		




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


  		




  203
  216
  
    
-- ------------------


  		




  
  217
  
    
CREATE OR REPLACE VIEW traits_14_count_of_individuals_per_verbatim_taxon_per_plot_in_each_project_ AS


  		




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


  		




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


  		




  206
  220
  
    
sum(ao.count) as individuals


  		




  ......




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


  		




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


  		




  226
  240
  
    
-- ------------------


  		




  
  241
  
    
CREATE OR REPLACE VIEW traits_15_percent_cover_of_each_verbatim_taxon_in_each_plot_in_each_project AS


  		




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


  		




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


  		




  229
  244
  
    
sum(ci.coverpercent) as totalpercentcover


  		




  ......




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


  		




  255
  270
  
    
--       per species per plot.


  		




  256
  271
  
    
-- ------------------


  		




  
  272
  
    
CREATE OR REPLACE VIEW traits_16_intercept_values_for_each_verbatim_taxon_in_each_plot_in_each_project AS


  		




  257
  273
  
    

  		




  258
  274
  
    

  		




  259
  275
  
    

  		




  ......




  263
  279
  
    
-- ------------------


  		




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


  		




  265
  281
  
    
-- ------------------


  		




  
  282
  
    
CREATE OR REPLACE VIEW traits_17_count_of_subplots_per_plot_for_each_project AS


  		




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


  		




  267
  284
  
    
count(distinct sub_locationevent.locationevent_id) as subplots


  		




  268
  285
  
    
from project p join source s 


  		




  ......




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


  		




  279
  296
  
    
-- DOESN'T YET WORK PROPERLY


  		




  280
  297
  
    
-- ------------------


  		




  
  298
  
    
CREATE OR REPLACE VIEW traits_18_list_of_subplots_codes_for_each_plot_for_each_project AS


  		




  281
  299
  
    
select p.projectname, 


  		




  282
  300
  
    
l.authorlocationcode as plotcode, 


  		




  283
  301
  
    
sub_locationevent.authoreventcode as subplotCode


  		












Also available in:  Unified diff