Project

General

Profile

« Previous | Next » 

Revision 12907

Added by Aaron Marcuse-Kubitza almost 11 years ago

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

View differences:

trunk/validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql
6 6 

  		




  7
  7
  
    
SET search_path TO "NY";


  		




  8
  8
  
    

  		




  9
  
  
    
-- -------------------------------


  		




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


  		




  11
  
  
    
-- -------------------------------


  		




  12
  9
  
    
CREATE OR REPLACE VIEW _specimens_01_count_of_total_records_specimens_in_source_db AS


  		




  13
  10
  
    
SELECT COUNT(*) "totalSpecimenRecords"


  		




  14
  11
  
    
FROM "Ecatalog_all";


  		




  ......




  16
  13
  
    
Check: should return 1 row


  		




  17
  14
  
    
*/


  		




  18
  15
  
    

  		




  19
  
  
    
-- -------------------------------


  		




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


  		




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


  		




  22
  16
  
    
CREATE OR REPLACE VIEW _specimens_02_count_of_unique_verbatim_families AS


  		




  23
  17
  
    
SELECT COUNT(DISTINCT family) AS families FROM "Ecatalog_all" WHERE family IS NOT NULL;


  		




  24
  18
  
    
/*


  		




  25
  19
  
    
Check: should return 1 row


  		




  26
  20
  
    
*/


  		




  27
  21
  
    

  		




  28
  
  
    
-- -------------------------------


  		




  29
  
  
    
-- 3. List of verbatim families


  		




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


  		




  31
  22
  
    
CREATE OR REPLACE VIEW _specimens_03_list_of_verbatim_families AS


  		




  32
  23
  
    
SELECT DISTINCT CONCAT_WS(' ', genus, "specificEpithet") AS "specificEpithet"


  		




  33
  24
  
    
FROM "Ecatalog_all"


  		




  ......




  36
  27
  
    
Check: should return same number of rows


  		




  37
  28
  
    
*/


  		




  38
  29
  
    

  		




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


  		




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


  		




  41
  
  
    
-- -------------------------------


  		




  42
  30
  
    
CREATE OR REPLACE VIEW _specimens_04_count_of_unique_verbatim_species_without_author AS


  		




  43
  31
  
    
SELECT COUNT(DISTINCT CONCAT_WS(' ', genus, "specificEpithet")) AS subspecies FROM "Ecatalog_all"


  		




  44
  32
  
    
WHERE genus IS NOT NULL AND "specificEpithet" IS NOT NULL AND subspecies IS NOT NULL;


  		




  ......




  46
  34
  
    
Check: should return 1 row


  		




  47
  35
  
    
*/


  		




  48
  36
  
    

  		




  49
  
  
    
-- -------------------------------


  		




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


  		




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


  		




  52
  37
  
    
CREATE OR REPLACE VIEW _specimens_05_list_of_verbatim_species_excluding_author AS


  		




  53
  38
  
    
SELECT DISTINCT CONCAT_WS(' ', genus, "specificEpithet") AS subspecies FROM "Ecatalog_all"


  		




  54
  39
  
    
WHERE genus IS NOT NULL AND "specificEpithet" IS NOT NULL AND subspecies IS NOT NULL;


  		




  ......




  56
  41
  
    
Check: should return 3335 rows


  		




  57
  42
  
    
*/


  		




  58
  43
  
    

  		




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


  		




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


  		




  61
  
  
    
-- -------------------------------


  		




  62
  44
  
    
CREATE OR REPLACE VIEW _specimens_06_count_of_unique_verbatim_subsp_taxa_without_author AS


  		




  63
  45
  
    
SELECT COUNT(DISTINCT CONCAT_WS(' ', genus, "specificEpithet", subspecies)) AS "specificEpithet" FROM "Ecatalog_all"


  		




  64
  46
  
    
WHERE genus IS NOT NULL AND "specificEpithet" IS NOT NULL;


  		




  ......




  66
  48
  
    
Check: should return 1 row


  		




  67
  49
  
    
*/


  		




  68
  50
  
    

  		




  69
  
  
    
-- -------------------------------


  		




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


  		




  71
  
  
    
-- -------------------------------


  		




  72
  51
  
    
CREATE OR REPLACE VIEW _specimens_07_list_of_verbatim_subspecific_taxa_without_author AS


  		




  73
  52
  
    
SELECT DISTINCT CONCAT_WS(' ', genus, "specificEpithet") AS "specificEpithet"


  		




  74
  53
  
    
FROM "Ecatalog_all"


  		




  ......




  77
  56
  
    
Check: should return 40145 rows


  		




  78
  57
  
    
*/


  		




  79
  58
  
    

  		




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


  		




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


  		




  82
  
  
    
-- -------------------------------


  		




  83
  59
  
    
CREATE OR REPLACE VIEW _specimens_08_count_of_unique_verbatim_author_taxa_with_genus AS


  		




  84
  60
  
    
SELECT COUNT(DISTINCT TRIM(CONCAT_WS(' ',


  		




  85
  61
  
    
TRIM(CONCAT_WS(' ', genus, IFNULL("specificEpithet",''), IFNULL(subspecies,''))),


  		




  ......




  91
  67
  
    
Check: should return 1 row


  		




  92
  68
  
    
*/


  		




  93
  69
  
    

  		




  94
  
  
    
-- -------------------------------


  		




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


  		




  96
  
  
    
-- -------------------------------


  		




  97
  70
  
    
CREATE OR REPLACE VIEW _specimens_09_list_of_unique_verbatim_author_taxa_with_genus AS


  		




  98
  71
  
    
SELECT DISTINCT TRIM(CONCAT_WS(' ',


  		




  99
  72
  
    
TRIM(CONCAT_WS(' ', genus, IFNULL("specificEpithet",''), IFNULL(subspecies,''))),


  		




  ......




  105
  78
  
    
Check: should return 45997 rows


  		




  106
  79
  
    
*/


  		




  107
  80
  
    

  		




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


  		




  109
  
  
    
-- 10. Count number of records by institution


  		




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


  		




  111
  81
  
    
CREATE OR REPLACE VIEW _specimens_10_count_number_of_records_by_institution AS


  		




  112
  82
  
    
SELECT specimen_duplicate_institutions, COUNT(*) AS records


  		




  113
  83
  
    
FROM "Ecatalog_all"


  		




  ......




  118
  88
  
    
Check: should return 6 rows


  		




  119
  89
  
    
*/


  		




  120
  90
  
    

  		




  121
  
  
    
-- -------------------------------


  		




  122
  
  
    
-- 11. List of three standard political divisions


  		




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


  		




  124
  91
  
    
CREATE OR REPLACE VIEW _specimens_11_list_of_three_standard_political_divisions AS


  		




  125
  92
  
    
SELECT DISTINCT country, "stateProvince", county


  		




  126
  93
  
    
FROM "Ecatalog_all";


  		




  ......




  130
  97
  
    
Check: should return 5232 rows


  		




  131
  98
  
    
*/


  		




  132
  99
  
    

  		




  133
  
  
    
-- -------------------------------


  		




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


  		




  135
  
  
    
-- plus total records


  		




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


  		




  137
  100
  
    
CREATE OR REPLACE VIEW _specimens_12_distinct_collector_name_collect_num_date_w_count AS


  		




  138
  101
  
    
SELECT IFNULL(recordedBy,'') AS "collectorName", IFNULL("collectorNumber",'') AS "collectionNumber",


  		




  139
  102
  
    
IFNULL("CollectedDate",'') AS "dataCollected", COUNT(*) AS "specimenRecords"


  		




  ......




  144
  107
  
    
Check: should return 309396 rows


  		




  145
  108
  
    
*/


  		




  146
  109
  
    

  		




  147
  
  
    
-- -------------------------------


  		




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


  		




  149
  
  
    
-- latitude and longitude values that are decimals numbers


  		




  150
  
  
    
-- -------------------------------


  		




  151
  110
  
    
CREATE OR REPLACE VIEW _specimens_13_count_of_all_verbatim_and_decimal_lat_long AS


  		




  152
  111
  
    
SELECT


  		




  153
  112
  
    
(


  		




  ......




  174
  133
  
    
Check: should return 1 row


  		




  175
  134
  
    
*/


  		




  176
  135
  
    

  		




  177
  
  
    
-- -------------------------------


  		




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


  		




  179
  
  
    
-- of decimal latitude or decimal longitude


  		




  180
  
  
    
-- -------------------------------


  		




  181
  136
  
    
CREATE OR REPLACE VIEW _specimens_14_count_of_all_invalid_verbatim_lat_long AS


  		




  182
  137
  
    
SELECT


  		




  183
  138
  
    
(


  		




  ......




  194
  149
  
    
Check: should return 1 row


  		




  195
  150
  
    
*/


  		




  196
  151
  
    

  		




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


  		




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


  		




  199
  
  
    
-- -------------------------------


  		




  200
  152
  
    
CREATE OR REPLACE VIEW _specimens_15_list_distinct_locality_descriptions AS


  		




  201
  153
  
    
SELECT DISTINCT locality__main AS "localityDescription"


  		




  202
  154
  
    
FROM "Ecatalog_all"


  		




  ......




  205
  157
  
    
Check: should return 125592 records


  		




  206
  158
  
    
*/


  		




  207
  159
  
    

  		




  208
  
  
    
-- -------------------------------


  		




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


  		




  210
  
  
    
-- -------------------------------


  		




  211
  160
  
    
CREATE OR REPLACE VIEW _specimens_16_list_distinct_specimen_descriptions AS


  		




  212
  161
  
    
SELECT "specimenDescription" AS "specimenDescription"


  		




  213
  162
  
    
FROM "Ecatalog_all"


  		












  

    
      trunk/validation/aggregating/specimens/qualitative_validations_specimens.sql
    
  





  9
  9
  
    

  		




  10
  10
  
    
SET search_path TO public_validations, public;


  		




  11
  11
  
    

  		




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


  		




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


  		




  14
  
  
    
-- -------------------------------


  		




  15
  12
  
    
CREATE OR REPLACE VIEW _specimens_01_count_of_total_records_specimens_in_source_db AS


  		




  16
  13
  
    
;


  		




  17
  14
  
    

  		




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


  		




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


  		




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


  		




  21
  15
  
    
CREATE OR REPLACE VIEW _specimens_02_count_of_unique_verbatim_families AS


  		




  22
  16
  
    
;


  		




  23
  17
  
    

  		




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


  		




  25
  
  
    
-- 3. List of verbatim families


  		




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


  		




  27
  18
  
    
CREATE OR REPLACE VIEW _specimens_03_list_of_verbatim_families AS


  		




  28
  19
  
    
;


  		




  29
  20
  
    

  		




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


  		




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


  		




  32
  
  
    
-- -------------------------------


  		




  33
  21
  
    
CREATE OR REPLACE VIEW _specimens_04_count_of_unique_verbatim_species_without_author AS


  		




  34
  22
  
    
;


  		




  35
  23
  
    

  		




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


  		




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


  		




  38
  
  
    
-- -------------------------------


  		




  39
  24
  
    
CREATE OR REPLACE VIEW _specimens_05_list_of_verbatim_species_excluding_author AS


  		




  40
  25
  
    
;


  		




  41
  26
  
    

  		




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


  		




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


  		




  44
  
  
    
-- -------------------------------


  		




  45
  27
  
    
CREATE OR REPLACE VIEW _specimens_06_count_of_unique_verbatim_subsp_taxa_without_author AS


  		




  46
  28
  
    
;


  		




  47
  29
  
    

  		




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


  		




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


  		




  50
  
  
    
-- -------------------------------


  		




  51
  30
  
    
CREATE OR REPLACE VIEW _specimens_07_list_of_verbatim_subspecific_taxa_without_author AS


  		




  52
  31
  
    
;


  		




  53
  32
  
    

  		




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


  		




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


  		




  56
  
  
    
-- -------------------------------


  		




  57
  33
  
    
CREATE OR REPLACE VIEW _specimens_08_count_of_unique_verbatim_author_taxa_with_genus AS


  		




  58
  34
  
    
;


  		




  59
  35
  
    

  		




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


  		




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


  		




  62
  
  
    
-- -------------------------------


  		




  63
  36
  
    
CREATE OR REPLACE VIEW _specimens_09_list_of_unique_verbatim_author_taxa_with_genus AS


  		




  64
  37
  
    
;


  		




  65
  38
  
    

  		




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


  		




  67
  
  
    
-- 10. Count number of records by institution


  		




  68
  
  
    
-- -------------------------------


  		




  69
  39
  
    
CREATE OR REPLACE VIEW _specimens_10_count_number_of_records_by_institution AS


  		




  70
  40
  
    
;


  		




  71
  41
  
    

  		




  72
  
  
    
-- -------------------------------


  		




  73
  
  
    
-- 11. List of three standard political divisions


  		




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


  		




  75
  42
  
    
CREATE OR REPLACE VIEW _specimens_11_list_of_three_standard_political_divisions AS


  		




  76
  43
  
    
;


  		




  77
  44
  
    
/*


  		




  78
  45
  
    
Note: character set issues may cause mis-matches. This query is a good way to reveal character set issues, either in source db or in BIEN


  		




  79
  46
  
    
*/


  		




  80
  47
  
    

  		




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


  		




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


  		




  83
  
  
    
-- plus total records


  		




  84
  
  
    
-- -------------------------------


  		




  85
  48
  
    
CREATE OR REPLACE VIEW _specimens_12_distinct_collector_name_collect_num_date_w_count AS


  		




  86
  49
  
    
;


  		




  87
  50
  
    

  		




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


  		




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


  		




  90
  
  
    
-- latitude and longitude values that are decimals numbers


  		




  91
  
  
    
-- -------------------------------


  		




  92
  51
  
    
CREATE OR REPLACE VIEW _specimens_13_count_of_all_verbatim_and_decimal_lat_long AS


  		




  93
  52
  
    
;


  		




  94
  53
  
    

  		




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


  		




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


  		




  97
  
  
    
-- of decimal latitude or decimal longitude


  		




  98
  
  
    
-- -------------------------------


  		




  99
  54
  
    
CREATE OR REPLACE VIEW _specimens_14_count_of_all_invalid_verbatim_lat_long AS


  		




  100
  55
  
    
;


  		




  101
  56
  
    

  		




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


  		




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


  		




  104
  
  
    
-- -------------------------------


  		




  105
  57
  
    
CREATE OR REPLACE VIEW _specimens_15_list_distinct_locality_descriptions AS


  		




  106
  58
  
    
;


  		




  107
  59
  
    

  		




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


  		




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


  		




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


  		




  111
  60
  
    
CREATE OR REPLACE VIEW _specimens_16_list_distinct_specimen_descriptions AS


  		




  112
  61
  
    
;


  		












Also available in:  Unified diff