Project

General

Profile

« Previous | Next » 

Revision 12223

Added by Aaron Marcuse-Kubitza almost 11 years ago

validation/aggregating/*/*.sql: renamed views to match the current naming scheme in the DB. these files still need to be kept up-to-date because there are some queries that haven't been implemented yet, and therefore exist only in these files, not the DB.

View differences:

trunk/validation/aggregating/plots/bien3_validations_salvias_vegbien.sql
18 18 
-- Currently configured for SALVIAS
19 19 
-- ------------------
20 20 

  		




  21
  
  
    
SET search_path TO public_validations_plots, public;


  		




  
  21
  
    
SET search_path TO public_validations, public;


  		




  22
  22
  
    

  		




  23
  23
  
    
-- ------------------


  		




  24
  24
  
    
-- 1. Count of projects


  		




  25
  25
  
    
-- Check: identical count in source db


  		




  26
  26
  
    
-- ------------------


  		




  27
  
  
    
CREATE OR REPLACE VIEW _01_count_of_projects AS


  		




  
  27
  
    
CREATE OR REPLACE VIEW plots_01_count_of_projects AS


  		




  28
  28
  
    
SELECT COUNT(*) AS projects


  		




  29
  29
  
    
FROM project p JOIN source s 


  		




  30
  30
  
    
ON p.source_id=s.source_id


  		




  ......




  34
  34
  
    
-- 2. List of project names


  		




  35
  35
  
    
-- Check: join to source db returns same number of rows


  		




  36
  36
  
    
-- ------------------


  		




  37
  
  
    
CREATE OR REPLACE VIEW _02_list_of_project_names AS


  		




  
  37
  
    
CREATE OR REPLACE VIEW plots_02_list_of_project_names AS


  		




  38
  38
  
    
SELECT p.projectname


  		




  39
  39
  
    
FROM project p JOIN source s 


  		




  40
  40
  
    
ON p.source_id=s.source_id


  		




  ......




  44
  44
  
    
-- 3. Count of all plots in this source


  		




  45
  45
  
    
-- Check: identical count in source db


  		




  46
  46
  
    
-- ------------------


  		




  47
  
  
    
CREATE OR REPLACE VIEW _03_count_of_all_plots_in_this_source AS


  		




  
  47
  
    
CREATE OR REPLACE VIEW plots_03_count_of_all_plots_in_this_source AS


  		




  48
  48
  
    
SELECT COUNT(*) AS plots


  		




  49
  49
  
    
FROM location l JOIN locationevent le


  		




  50
  50
  
    
ON l.location_id=le.location_id


  		




  ......




  57
  57
  
    
-- ------------------


  		




  58
  58
  
    
-- 4. Count of plots in each project in this source


  		




  59
  59
  
    
-- ------------------


  		




  60
  
  
    
CREATE OR REPLACE VIEW _04_count_of_plots_in_each_project_in_this_source AS


  		




  
  60
  
    
CREATE OR REPLACE VIEW plots_04_count_of_plots_in_each_project_in_this_source AS


  		




  61
  61
  
    
SELECT p.projectname, COUNT(DISTINCT l.location_id) AS plots


  		




  62
  62
  
    
FROM location l JOIN locationevent le


  		




  63
  63
  
    
ON l.location_id=le.location_id


  		




  ......




  72
  72
  
    
-- 5.List of plot codes by project


  		




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


  		




  74
  74
  
    
-- ------------------


  		




  75
  
  
    
CREATE OR REPLACE VIEW _05_list_of_plot_codes_by_project AS


  		




  
  75
  
    
CREATE OR REPLACE VIEW plots_05_list_of_plot_codes_by_project AS


  		




  76
  76
  
    
SELECT p.projectname, l.authorlocationcode AS "plotCode"


  		




  77
  77
  
    
FROM location l JOIN locationevent le


  		




  78
  78
  
    
ON l.location_id=le.location_id


  		




  ......




  85
  85
  
    
-- ------------------


  		




  86
  86
  
    
-- 6. List of plots with stem measurements


  		




  87
  87
  
    
-- ------------------


  		




  88
  
  
    
CREATE OR REPLACE VIEW _06_list_of_plots_with_stem_measurements AS


  		




  
  88
  
    
CREATE OR REPLACE VIEW plots_06_list_of_plots_with_stem_measurements AS


  		




  89
  89
  
    

  		




  90
  90
  
    
SELECT authorlocationcode


  		




  91
  91
  
    
FROM top_plot


  		




  ......




  107
  107
  
    
-- ------------------


  		




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


  		




  109
  109
  
    
-- ------------------


  		




  110
  
  
    
CREATE OR REPLACE VIEW _07_list_of_plots_with_counts_of_individuals_per_species AS


  		




  
  110
  
    
CREATE OR REPLACE VIEW plots_07_list_of_plots_with_counts_of_individuals_per_species AS


  		




  111
  111
  
    

  		




  112
  112
  
    

  		




  113
  113
  
    

  		




  114
  114
  
    
-- ------------------


  		




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


  		




  116
  116
  
    
-- ------------------


  		




  117
  
  
    
CREATE OR REPLACE VIEW _08_list_of_plots_which_use_percent_cover AS


  		




  
  117
  
    
CREATE OR REPLACE VIEW plots_08_list_of_plots_which_use_percent_cover AS


  		




  118
  118
  
    

  		




  119
  119
  
    

  		




  120
  120
  
    

  		




  ......




  122
  122
  
    
-- ------------------


  		




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


  		




  124
  124
  
    
-- ------------------


  		




  125
  
  
    
CREATE OR REPLACE VIEW _09_list_of_plots_which_use_line_intercept AS


  		




  
  125
  
    
CREATE OR REPLACE VIEW plots_09_list_of_plots_which_use_line_intercept AS


  		




  126
  126
  
    

  		




  127
  127
  
    

  		




  128
  128
  
    
-- ------------------


  		




  ......




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


  		




  135
  135
  
    
--       subplots within plots?


  		




  136
  136
  
    
-- ------------------


  		




  137
  
  
    
CREATE OR REPLACE VIEW _10_count_of_individuals_per_plot_in_each_project AS


  		




  
  137
  
    
CREATE OR REPLACE VIEW plots_10_count_of_individuals_per_plot_in_each_project AS


  		




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


  		




  139
  139
  
    
from project p join source s 


  		




  140
  140
  
    
on p.source_id=s.source_id


  		




  ......




  152
  152
  
    
-- 


  		




  153
  153
  
    
-- Method: count records in stemobservation table


  		




  154
  154
  
    
-- ------------------


  		




  155
  
  
    
CREATE OR REPLACE VIEW _11_count_of_stems_per_plot_in_each_project AS


  		




  
  155
  
    
CREATE OR REPLACE VIEW plots_11_count_of_stems_per_plot_in_each_project AS


  		




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


  		




  157
  157
  
    
from project p join source s 


  		




  158
  158
  
    
on p.source_id=s.source_id


  		




  ......




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


  		




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


  		




  175
  175
  
    
-- ------------------


  		




  176
  
  
    
CREATE OR REPLACE VIEW _12_count_of_verbatim_taxa_per_plot_in_each_project AS


  		




  
  176
  
    
CREATE OR REPLACE VIEW plots_12_count_of_verbatim_taxa_per_plot_in_each_project AS


  		




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


  		




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


  		




  179
  179
  
    
as taxa


  		




  ......




  194
  194
  
    
-- 


  		




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


  		




  196
  196
  
    
-- ------------------


  		




  197
  
  
    
CREATE OR REPLACE VIEW _13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_project AS


  		




  
  197
  
    
CREATE OR REPLACE VIEW plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_each_project AS


  		




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


  		




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


  		




  200
  200
  
    
from project p join source s 


  		




  ......




  213
  213
  
    
-- 


  		




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


  		




  215
  215
  
    
-- ------------------


  		




  216
  
  
    
CREATE OR REPLACE VIEW _14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_project AS


  		




  
  216
  
    
CREATE OR REPLACE VIEW plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_each_project AS


  		




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


  		




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


  		




  219
  219
  
    
sum(ao.count) as individuals


  		




  ......




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


  		




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


  		




  239
  239
  
    
-- ------------------


  		




  240
  
  
    
CREATE OR REPLACE VIEW _15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_project AS


  		




  
  240
  
    
CREATE OR REPLACE VIEW plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_project AS


  		




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


  		




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


  		




  243
  243
  
    
sum(ci.coverpercent) as totalpercentcover


  		




  ......




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


  		




  269
  269
  
    
--       per species per plot.


  		




  270
  270
  
    
-- ------------------


  		




  271
  
  
    
CREATE OR REPLACE VIEW _16_intercepts_for_each_verb_taxon_in_each_plot_in_each_project AS


  		




  
  271
  
    
CREATE OR REPLACE VIEW plots_16_intercepts_for_each_verb_taxon_in_each_plot_in_each_project AS


  		




  272
  272
  
    

  		




  273
  273
  
    

  		




  274
  274
  
    

  		




  ......




  278
  278
  
    
-- ------------------


  		




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


  		




  280
  280
  
    
-- ------------------


  		




  281
  
  
    
CREATE OR REPLACE VIEW _17_count_of_subplots_per_plot_for_each_project AS


  		




  
  281
  
    
CREATE OR REPLACE VIEW plots_17_count_of_subplots_per_plot_for_each_project AS


  		




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


  		




  283
  283
  
    
count(distinct sub_locationevent.locationevent_id) as subplots


  		




  284
  284
  
    
from project p join source s 


  		




  ......




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


  		




  295
  295
  
    
-- DOESN'T YET WORK PROPERLY


  		




  296
  296
  
    
-- ------------------


  		




  297
  
  
    
CREATE OR REPLACE VIEW _18_list_of_subplots_codes_for_each_plot_for_each_project AS


  		




  
  297
  
    
CREATE OR REPLACE VIEW plots_18_list_of_subplots_codes_for_each_plot_for_each_project AS


  		




  298
  298
  
    
select p.projectname, 


  		




  299
  299
  
    
l.authorlocationcode as plotcode, 


  		




  300
  300
  
    
sub_locationevent.authoreventcode as "subplotCode"


  		












  

    
      trunk/validation/aggregating/specimens/qualitative_validations_specimens.sql
    
  





  7
  7
  
    
-- Host: vegbiendev.nceas.ucsb.edu


  		




  8
  8
  
    
-- -------------------------------------------------------------------------


  		




  9
  9
  
    

  		




  10
  
  
    
SET search_path TO public_validations_specimens, public;


  		




  
  10
  
    
SET search_path TO public_validations, public;


  		




  11
  11
  
    

  		




  12
  12
  
    
-- -------------------------------


  		




  13
  13
  
    
-- 1. Count of total records (specimens) in source db


  		




  14
  14
  
    
-- Check: full join against equivalent query on BIEN3 db should return 1 row


  		




  15
  15
  
    
-- -------------------------------


  		




  16
  
  
    
CREATE OR REPLACE VIEW _01_count_of_total_records_specimens_in_source_db AS


  		




  
  16
  
    
CREATE OR REPLACE VIEW specimens_01_count_of_total_records_specimens_in_source_db AS


  		




  17
  17
  
    

  		




  18
  18
  
    
-- -------------------------------


  		




  19
  19
  
    
-- 2. Count of unique (verbatim) non-null families


  		




  20
  20
  
    
-- Check: Full inner join to equivalent query on BIEN3 db should return 1 row


  		




  21
  21
  
    
-- -------------------------------


  		




  22
  
  
    
CREATE OR REPLACE VIEW _02_count_of_unique_verbatim_non_null_families AS


  		




  
  22
  
    
CREATE OR REPLACE VIEW specimens_02_count_of_unique_verbatim_non_null_families AS


  		




  23
  23
  
    

  		




  24
  24
  
    
-- -------------------------------


  		




  25
  25
  
    
-- 3. List of verbatim families


  		




  26
  26
  
    
-- Check: Full inner join to equivalent query on BIEN3 db should return same number of rows


  		




  27
  27
  
    
-- -------------------------------


  		




  28
  
  
    
CREATE OR REPLACE VIEW _03_list_of_verbatim_families AS


  		




  
  28
  
    
CREATE OR REPLACE VIEW specimens_03_list_of_verbatim_families AS


  		




  29
  29
  
    

  		




  30
  30
  
    
-- -------------------------------


  		




  31
  31
  
    
-- 4. Count of unique (verbatim) non-null species, without author


  		




  32
  32
  
    
-- Check: Full inner join to equivalent query on BIEN3 db should return 1 row


  		




  33
  33
  
    
-- -------------------------------


  		




  34
  
  
    
CREATE OR REPLACE VIEW _04_count_of_unique_verbatim_non_null_species_without_author AS


  		




  
  34
  
    
CREATE OR REPLACE VIEW specimens_04_count_of_unique_verbatim_non_null_species_without_author AS


  		




  35
  35
  
    

  		




  36
  36
  
    
-- -------------------------------


  		




  37
  37
  
    
-- 5. List of verbatim species, excluding author


  		




  38
  38
  
    
-- Check: Full inner join to equivalent query on BIEN3 db should return 3335 rows


  		




  39
  39
  
    
-- -------------------------------


  		




  40
  
  
    
CREATE OR REPLACE VIEW _05_list_of_verbatim_species_excluding_author AS


  		




  
  40
  
    
CREATE OR REPLACE VIEW specimens_05_list_of_verbatim_species_excluding_author AS


  		




  41
  41
  
    

  		




  42
  42
  
    
-- -------------------------------


  		




  43
  43
  
    
-- 6. Count of unique (verbatim) non-null subspecific taxa, without author


  		




  44
  44
  
    
-- Check: Full inner join to equivalent query on BIEN3 db should return 1 row


  		




  45
  45
  
    
-- -------------------------------


  		




  46
  
  
    
CREATE OR REPLACE VIEW _06_count_of_unique_verbatim_non_null_subspecific_taxa_without_author AS


  		




  
  46
  
    
CREATE OR REPLACE VIEW specimens_06_count_of_unique_verbatim_non_null_subspecific_taxa_without_author AS


  		




  47
  47
  
    

  		




  48
  48
  
    
-- -------------------------------


  		




  49
  49
  
    
-- 7. List of verbatim subspecific taxa, without author


  		




  50
  50
  
    
-- Check: Full inner join to equivalent query on BIEN3 db should return 40145 rows


  		




  51
  51
  
    
-- -------------------------------


  		




  52
  
  
    
CREATE OR REPLACE VIEW _07_list_of_verbatim_subspecific_taxa_without_author AS


  		




  
  52
  
    
CREATE OR REPLACE VIEW specimens_07_list_of_verbatim_subspecific_taxa_without_author AS


  		




  53
  53
  
    

  		




  54
  54
  
    
-- -------------------------------


  		




  55
  55
  
    
-- 8. Count of unique (verbatim) taxa including author, for all taxa identified at least to genus


  		




  56
  56
  
    
-- Check: Full inner join to equivalent query on BIEN3 db should return 1 row


  		




  57
  57
  
    
-- -------------------------------


  		




  58
  
  
    
CREATE OR REPLACE VIEW _08_count_of_unique_verbatim_taxa_including_author_for_all_taxa_identified_at_least_to_genus AS


  		




  
  58
  
    
CREATE OR REPLACE VIEW specimens_08_count_of_unique_verbatim_taxa_including_author_for_all_taxa_identified_at_least_to_genus AS


  		




  59
  59
  
    

  		




  60
  60
  
    
-- -------------------------------


  		




  61
  61
  
    
-- 9. List of unique (verbatim) taxa including author, for all taxa identified at least to genus


  		




  62
  62
  
    
-- Check: Full inner join to equivalent query on BIEN3 db should return 45997 rows


  		




  63
  63
  
    
-- -------------------------------


  		




  64
  
  
    
CREATE OR REPLACE VIEW _09_list_of_unique_verbatim_taxa_including_author_for_all_taxa_identified_at_least_to_genus AS


  		




  
  64
  
    
CREATE OR REPLACE VIEW specimens_09_list_of_unique_verbatim_taxa_including_author_for_all_taxa_identified_at_least_to_genus AS


  		




  65
  65
  
    

  		




  66
  66
  
    
-- -------------------------------


  		




  67
  67
  
    
-- 10. Count number of records by institution


  		




  68
  68
  
    
-- Check: Full inner join to equivalent query on BIEN3 db should return 6 rows


  		




  69
  69
  
    
-- Note: Majority should be from 'NY'; these are standard herbarium acronyms


  		




  70
  70
  
    
-- -------------------------------


  		




  71
  
  
    
CREATE OR REPLACE VIEW _10_count_number_of_records_by_institution AS


  		




  
  71
  
    
CREATE OR REPLACE VIEW specimens_10_count_number_of_records_by_institution AS


  		




  72
  72
  
    

  		




  73
  73
  
    
-- -------------------------------


  		




  74
  74
  
    
-- 11. List of three standard political divisions


  		




  ......




  76
  76
  
    
-- Note: character set issues may cause mis-matches. This query is a good way to reveal


  		




  77
  77
  
    
-- character set issues, either in source db or in BIEN


  		




  78
  78
  
    
-- -------------------------------


  		




  79
  
  
    
CREATE OR REPLACE VIEW _11_list_of_three_standard_political_divisions AS


  		




  
  79
  
    
CREATE OR REPLACE VIEW specimens_11_list_of_three_standard_political_divisions AS


  		




  80
  80
  
    

  		




  81
  81
  
    
-- -------------------------------


  		




  82
  82
  
    
-- 12. Check distinct Collector names + collection numbers + collection dates, 


  		




  83
  83
  
    
-- plus total records


  		




  84
  84
  
    
-- Check: Full inner join to equivalent query on BIEN3 db should return 309396 rows


  		




  85
  85
  
    
-- -------------------------------


  		




  86
  
  
    
CREATE OR REPLACE VIEW _12_check_distinct_collector_names_collection_numbers_collection_dates AS


  		




  
  86
  
    
CREATE OR REPLACE VIEW specimens_12_check_distinct_collector_names_collection_numbers_collection_dates AS


  		




  87
  87
  
    

  		




  88
  88
  
    
-- -------------------------------


  		




  89
  89
  
    
-- 13. Count of all  verbatim Latitude and Longitude values, as well as all 


  		




  90
  90
  
    
-- latitude and longitude values that are decimals numbers


  		




  91
  91
  
    
-- Check: full join to equivalent query against BIEN3 should return 1 row


  		




  92
  92
  
    
-- -------------------------------


  		




  93
  
  
    
CREATE OR REPLACE VIEW _13_count_of_all_verbatim_latitude_and_longitude_values_as_well_as_all AS


  		




  
  93
  
    
CREATE OR REPLACE VIEW specimens_13_count_of_all_verbatim_latitude_and_longitude_values_as_well_as_all AS


  		




  94
  94
  
    

  		




  95
  95
  
    
-- -------------------------------


  		




  96
  96
  
    
-- 14. Count of all verbatim Latitude and Longitude values that are not valid values


  		




  97
  97
  
    
-- of decimal latitude or decimal longitude


  		




  98
  98
  
    
-- Check: full join to equivalent query against BIEN3 should return 1 row


  		




  99
  99
  
    
-- -------------------------------


  		




  100
  
  
    
CREATE OR REPLACE VIEW _14_count_of_all_verbatim_latitude_and_longitude_values_that_are_not_valid_values AS


  		




  
  100
  
    
CREATE OR REPLACE VIEW specimens_14_count_of_all_verbatim_latitude_and_longitude_values_that_are_not_valid_values AS


  		




  101
  101
  
    

  		




  102
  102
  
    
-- -------------------------------


  		




  103
  103
  
    
-- 15. List distinct non-null locality descriptions


  		




  104
  104
  
    
-- Check: full join to equivalent query against BIEN3 should return 125592 records


  		




  105
  105
  
    
-- -------------------------------


  		




  106
  
  
    
CREATE OR REPLACE VIEW _15_list_distinct_non_null_locality_descriptions AS


  		




  
  106
  
    
CREATE OR REPLACE VIEW specimens_15_list_distinct_non_null_locality_descriptions AS


  		




  107
  107
  
    

  		




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


  		




  109
  109
  
    
-- 16. List distinct non-null specimen descriptions


  		




  110
  110
  
    
-- Check: full join to equivalent query against BIEN3 should return 158460 records


  		




  111
  111
  
    
-- Note: specimens descriptions in nybg extract is in column PlantFungusDescription


  		




  112
  112
  
    
-- -------------------------------


  		




  113
  
  
    
CREATE OR REPLACE VIEW _16_list_distinct_non_null_specimen_descriptions AS


  		




  
  113
  
    
CREATE OR REPLACE VIEW specimens_16_list_distinct_non_null_specimen_descriptions AS


  		












  

    
      trunk/validation/aggregating/traits/bien3_validations_traits_bien3.sql
    
  





  5
  5
  
    
-- Host: vegbiendev.nceas.ucsb.edu


  		




  6
  6
  
    
-- -------------------------------------------------------------------------------


  		




  7
  7
  
    

  		




  8
  
  
    
SET search_path TO public_validations_traits, public;


  		




  
  8
  
    
SET search_path TO public_validations, public;


  		




  9
  9
  
    

  		




  10
  10
  
    
-- ------------------


  		




  11
  11
  
    
-- 1. Count records


  		




  12
  12
  
    
-- ------------------


  		




  13
  
  
    
CREATE OR REPLACE VIEW _01_count_records AS


  		




  
  13
  
    
CREATE OR REPLACE VIEW traits_01_count_records AS


  		




  14
  14
  
    
SELECT COUNT(*) AS "totalRecords"


  		




  15
  15
  
    
FROM trait


  		




  16
  16
  
    
JOIN taxonoccurrence USING (taxonoccurrence_id)


  		




  ......




  20
  20
  
    
-- ------------------


  		




  21
  21
  
    
-- 2. Count trait names


  		




  22
  22
  
    
-- ------------------


  		




  23
  
  
    
CREATE OR REPLACE VIEW _02_count_trait_names AS


  		




  
  23
  
    
CREATE OR REPLACE VIEW traits_02_count_trait_names AS


  		




  24
  24
  
    
SELECT COUNT(DISTINCT name) AS traits


  		




  25
  25
  
    
FROM trait


  		




  26
  26
  
    
JOIN taxonoccurrence USING (taxonoccurrence_id)


  		




  ......




  29
  29
  
    
-- ------------------


  		




  30
  30
  
    
-- 3. List trait names


  		




  31
  31
  
    
-- ------------------


  		




  32
  
  
    
CREATE OR REPLACE VIEW _03_list_trait_names AS


  		




  
  32
  
    
CREATE OR REPLACE VIEW traits_03_list_trait_names AS


  		




  33
  33
  
    
SELECT DISTINCT name AS trait


  		




  34
  34
  
    
FROM trait


  		




  35
  35
  
    
JOIN taxonoccurrence USING (taxonoccurrence_id)


  		




  ......




  40
  40
  
    
-- ------------------


  		




  41
  41
  
    
-- 4. Count records per trait


  		




  42
  42
  
    
-- ------------------


  		




  43
  
  
    
CREATE OR REPLACE VIEW _04_count_records_per_trait AS


  		




  
  43
  
    
CREATE OR REPLACE VIEW traits_04_count_records_per_trait AS


  		




  44
  44
  
    
SELECT name AS trait, COUNT(*) AS measurements


  		




  45
  45
  
    
FROM trait


  		




  46
  46
  
    
JOIN taxonoccurrence USING (taxonoccurrence_id)


  		




  ......




  55
  55
  
    
-- Note: No morphospecies in trait table, therefore count


  		




  56
  56
  
    
-- 		taxon + authority only


  		




  57
  57
  
    
-- ------------------


  		




  58
  
  
    
CREATE OR REPLACE VIEW _05_count_taxa AS


  		




  
  58
  
    
CREATE OR REPLACE VIEW traits_05_count_taxa AS


  		




  59
  59
  
    
SELECT COUNT(DISTINCT (taxonname, author)) AS taxa


  		




  60
  60
  
    
FROM taxonverbatim


  		




  61
  61
  
    
WHERE source_id = source_by_shortname(current_schema)


  		




  ......




  68
  68
  
    
-- Note 2: Note formation of taxonCorrected: includes family ONLY if


  		




  69
  69
  
    
-- taxon is not determined at least to genus


  		




  70
  70
  
    
-- ------------------


  		




  71
  
  
    
CREATE OR REPLACE VIEW _06_list_taxa AS


  		




  
  71
  
    
CREATE OR REPLACE VIEW traits_06_list_taxa AS


  		




  72
  72
  
    
SELECT DISTINCT concat_ws(' ', taxonname, author) AS taxonwithauthor


  		




  73
  73
  
    
FROM taxonverbatim


  		




  74
  74
  
    
WHERE source_id = source_by_shortname(current_schema)


  		




  ......




  78
  78
  
    
-- ------------------


  		




  79
  79
  
    
-- 7. Trait, value and units for first 5000 records 


  		




  80
  80
  
    
-- ------------------


  		




  81
  
  
    
CREATE OR REPLACE VIEW _07_trait_value_and_units_for_first_5000_records AS


  		




  
  81
  
    
CREATE OR REPLACE VIEW traits_07_trait_value_and_units_for_first_5000_records AS


  		




  82
  82
  
    
SELECT name AS trait, value, units


  		




  83
  83
  
    
FROM trait


  		




  84
  84
  
    
JOIN taxonoccurrence USING (taxonoccurrence_id)


  		




  ......




  90
  90
  
    
-- ------------------


  		




  91
  91
  
    
-- 8. Taxon, trait and value for first 5000 records 


  		




  92
  92
  
    
-- ------------------


  		




  93
  
  
    
CREATE OR REPLACE VIEW _08_taxon_trait_and_value_for_first_5000_records AS


  		




  
  93
  
    
CREATE OR REPLACE VIEW traits_08_taxon_trait_and_value_for_first_5000_records AS


  		




  94
  94
  
    
SELECT concat_ws(' ', taxonname, author) AS taxonwithauthor, name AS trait, value


  		




  95
  95
  
    
FROM taxonverbatim


  		




  96
  96
  
    
JOIN taxondetermination ON taxondetermination.taxonverbatim_id = taxonverbatim.taxonverbatim_id AND taxondetermination.iscurrent


  		












Also available in:  Unified diff