Project

General

Profile

« Previous | Next » 

Revision 13080

Added by Aaron Marcuse-Kubitza almost 11 years ago

validation/aggregating/specimens/qualitative_validations_specimens.sql: synced to schemas/vegbien.sql so that it can be diffed with it to sync qualitative_validations_specimens.sql to the DB

View differences:

trunk/validation/aggregating/specimens/qualitative_validations_specimens.sql
15 15 
SET join_collapse_limit = 1; -- turn it off
16 16 

  		




  17
  17
  
    
-- _specimens_01_count_of_total_records_specimens_in_source_db


  		




  18
  
  
    
SELECT count(*) AS "totalSpecimenRecords"


  		




  19
  
  
    
FROM taxonoccurrence


  		




  20
  
  
    
WHERE taxonoccurrence.source_id = (SELECT source_by_shortname(:datasource))


  		




  21
  
  
    
;


  		




  
  18
  
    
 SELECT count(*) AS "totalSpecimenRecords"


  		




  
  19
  
    
   FROM public.taxonoccurrence


  		




  
  20
  
    
  WHERE (taxonoccurrence.source_id = ( SELECT public.source_by_shortname((:datasource)::text) AS source_by_shortname));


  		




  22
  21
  
    

  		




  23
  22
  
    
-- _specimens_02_count_of_unique_verbatim_families


  		




  24
  
  
    
SELECT count(DISTINCT family) AS families


  		




  25
  
  
    
FROM taxonverbatim


  		




  26
  
  
    
WHERE taxonverbatim.source_id = (SELECT source_by_shortname(:datasource))


  		




  27
  
  
    
AND family IS NOT NULL


  		




  28
  
  
    
;


  		




  
  23
  
    
 SELECT count(DISTINCT taxonverbatim.family) AS families


  		




  
  24
  
    
   FROM public.taxonverbatim


  		




  
  25
  
    
  WHERE ((taxonverbatim.source_id = ( SELECT public.source_by_shortname((:datasource)::text) AS source_by_shortname)) AND (taxonverbatim.family IS NOT NULL));


  		




  29
  26
  
    

  		




  30
  27
  
    
-- _specimens_03_list_of_verbatim_families


  		




  31
  
  
    
SELECT DISTINCT family


  		




  32
  
  
    
FROM taxonverbatim


  		




  33
  
  
    
WHERE taxonverbatim.source_id = (SELECT source_by_shortname(:datasource))


  		




  34
  
  
    
AND family IS NOT NULL


  		




  35
  
  
    
;


  		




  
  28
  
    
 SELECT DISTINCT taxonverbatim.family


  		




  
  29
  
    
   FROM public.taxonverbatim


  		




  
  30
  
    
  WHERE ((taxonverbatim.source_id = ( SELECT public.source_by_shortname((:datasource)::text) AS source_by_shortname)) AND (taxonverbatim.family IS NOT NULL));


  		




  36
  31
  
    

  		




  37
  32
  
    
-- _specimens_04_count_of_species_binomials


  		




  38
  
  
    
SELECT count(DISTINCT concat_ws(' '::text, genus, specific_epithet)) AS species_binomials


  		




  39
  
  
    
FROM taxonverbatim


  		




  40
  
  
    
WHERE taxonverbatim.source_id = (SELECT source_by_shortname(:datasource))


  		




  41
  
  
    
AND genus IS NOT NULL AND specific_epithet IS NOT NULL;


  		




  42
  
  
    
;


  		




  
  33
  
    
 SELECT count(DISTINCT concat_ws(' '::text, taxonverbatim.genus, taxonverbatim.specific_epithet)) AS species_binomials


  		




  
  34
  
    
   FROM public.taxonverbatim


  		




  
  35
  
    
  WHERE (((taxonverbatim.source_id = ( SELECT public.source_by_shortname((:datasource)::text) AS source_by_shortname)) AND (taxonverbatim.genus IS NOT NULL)) AND (taxonverbatim.specific_epithet IS NOT NULL));


  		




  43
  36
  
    

  		




  44
  37
  
    
-- _specimens_05_list_of_species_binomials


  		




  45
  
  
    
SELECT DISTINCT concat_ws(' '::text, genus, specific_epithet) AS species_binomial


  		




  46
  
  
    
FROM taxonverbatim


  		




  47
  
  
    
WHERE taxonverbatim.source_id = (SELECT source_by_shortname(:datasource))


  		




  48
  
  
    
AND genus IS NOT NULL AND specific_epithet IS NOT NULL;


  		




  49
  
  
    
;


  		




  
  38
  
    
 SELECT DISTINCT concat_ws(' '::text, taxonverbatim.genus, taxonverbatim.specific_epithet) AS species_binomial


  		




  
  39
  
    
   FROM public.taxonverbatim


  		




  
  40
  
    
  WHERE (((taxonverbatim.source_id = ( SELECT public.source_by_shortname((:datasource)::text) AS source_by_shortname)) AND (taxonverbatim.genus IS NOT NULL)) AND (taxonverbatim.specific_epithet IS NOT NULL));


  		




  50
  41
  
    

  		




  51
  42
  
    
-- _specimens_06_count_of_unique_verb_subsp_taxa_with_author


  		




  52
  
  
    
SELECT count(DISTINCT taxonlabel.taxonomicname) AS taxon_names_with_author


  		




  53
  
  
    
FROM taxonlabel


  		




  54
  
  
    
WHERE taxonlabel.source_id = (SELECT source_by_shortname(:datasource))


  		




  55
  
  
    
AND taxonlabel.taxonomicname IS NOT NULL


  		




  56
  
  
    
;


  		




  
  43
  
    
 SELECT count(DISTINCT taxonlabel.taxonomicname) AS taxon_names_with_author


  		




  
  44
  
    
   FROM public.taxonlabel


  		




  
  45
  
    
  WHERE ((taxonlabel.source_id = ( SELECT public.source_by_shortname((:datasource)::text) AS source_by_shortname)) AND (taxonlabel.taxonomicname IS NOT NULL));


  		




  57
  46
  
    

  		




  58
  47
  
    
-- _specimens_07_list_of_verbatim_subspecific_taxa_with_author


  		




  59
  
  
    
SELECT DISTINCT taxonlabel.taxonomicname AS taxon_name_with_author


  		




  60
  
  
    
FROM taxonlabel


  		




  61
  
  
    
WHERE taxonlabel.source_id = (SELECT source_by_shortname(:datasource))


  		




  62
  
  
    
AND taxonlabel.taxonomicname IS NOT NULL


  		




  63
  
  
    
;


  		




  
  48
  
    
 SELECT DISTINCT taxonlabel.taxonomicname AS taxon_name_with_author


  		




  
  49
  
    
   FROM public.taxonlabel


  		




  
  50
  
    
  WHERE ((taxonlabel.source_id = ( SELECT public.source_by_shortname((:datasource)::text) AS source_by_shortname)) AND (taxonlabel.taxonomicname IS NOT NULL));


  		




  64
  51
  
    

  		




  65
  52
  
    
-- _specimens_08_count_of_unique_verbatim_author_taxa_with_genus


  		




  66
  
  
    
SELECT count(DISTINCT taxonlabel.taxonomicname) AS taxon_names_with_author


  		




  67
  
  
    
FROM taxonverbatim


  		




  68
  
  
    
JOIN taxonlabel USING (taxonlabel_id)


  		




  69
  
  
    
WHERE taxonverbatim.source_id = (SELECT source_by_shortname(:datasource))


  		




  70
  
  
    
AND genus IS NOT NULL


  		




  71
  
  
    
;


  		




  
  53
  
    
 SELECT count(DISTINCT taxonlabel.taxonomicname) AS taxon_names_with_author


  		




  
  54
  
    
   FROM (public.taxonverbatim


  		




  
  55
  
    
   JOIN public.taxonlabel USING (taxonlabel_id))


  		




  
  56
  
    
  WHERE ((taxonverbatim.source_id = ( SELECT public.source_by_shortname((:datasource)::text) AS source_by_shortname)) AND (taxonverbatim.genus IS NOT NULL));


  		




  72
  57
  
    

  		




  73
  58
  
    
-- _specimens_09_list_of_unique_verbatim_author_taxa_with_genus


  		




  74
  
  
    
SELECT DISTINCT taxonlabel.taxonomicname AS taxon_name_with_author


  		




  75
  
  
    
FROM taxonverbatim


  		




  76
  
  
    
JOIN taxonlabel USING (taxonlabel_id)


  		




  77
  
  
    
WHERE taxonverbatim.source_id = (SELECT source_by_shortname(:datasource))


  		




  78
  
  
    
AND genus IS NOT NULL


  		




  79
  
  
    
;


  		




  
  59
  
    
 SELECT DISTINCT taxonlabel.taxonomicname AS taxon_name_with_author


  		




  
  60
  
    
   FROM (public.taxonverbatim


  		




  
  61
  
    
   JOIN public.taxonlabel USING (taxonlabel_id))


  		




  
  62
  
    
  WHERE ((taxonverbatim.source_id = ( SELECT public.source_by_shortname((:datasource)::text) AS source_by_shortname)) AND (taxonverbatim.genus IS NOT NULL));


  		




  80
  63
  
    

  		




  81
  64
  
    
-- _specimens_10_count_number_of_records_by_institution


  		




  82
  
  
    
SELECT specimenreplicate.duplicate_institutions_sourcelist_id AS specimen_duplicate_institutions,


  		




  83
  
  
    
count(*) AS records


  		




  84
  
  
    
FROM specimenreplicate


  		




  85
  
  
    
WHERE specimenreplicate.source_id = (SELECT source_by_shortname(:datasource))


  		




  86
  
  
    
GROUP BY specimen_duplicate_institutions


  		




  87
  
  
    
;


  		




  
  65
  
    
 SELECT specimenreplicate.duplicate_institutions_sourcelist_id AS specimen_duplicate_institutions, 


  		




  
  66
  
    
    count(*) AS records


  		




  
  67
  
    
   FROM public.specimenreplicate


  		




  
  68
  
    
  WHERE (specimenreplicate.source_id = ( SELECT public.source_by_shortname((:datasource)::text) AS source_by_shortname))


  		




  
  69
  
    
  GROUP BY specimenreplicate.duplicate_institutions_sourcelist_id;


  		




  88
  70
  
    

  		




  89
  71
  
    
-- _specimens_11_list_of_three_standard_political_divisions


  		




  90
  
  
    
SELECT DISTINCT country, stateprovince, county


  		




  91
  
  
    
FROM place


  		




  92
  
  
    
WHERE place.source_id = (SELECT source_by_shortname(:datasource))


  		




  93
  
  
    
;


  		




  
  72
  
    
 SELECT DISTINCT place.country, 


  		




  
  73
  
    
    place.stateprovince, 


  		




  
  74
  
    
    place.county


  		




  
  75
  
    
   FROM public.place


  		




  
  76
  
    
  WHERE (place.source_id = ( SELECT public.source_by_shortname((:datasource)::text) AS source_by_shortname));


  		




  94
  77
  
    
/*


  		




  95
  78
  
    
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


  		




  96
  79
  
    
*/


  		




  97
  80
  
    

  		




  98
  81
  
    
-- _specimens_12_distinct_collector_name_collect_num_date_w_count


  		




  99
  
  
    
SELECT


  		




  100
  
  
    
  (SELECT fullname FROM party WHERE party.party_id = collector_id) AS "collectorName"


  		




  101
  
  
    
, collectionnumber AS "collectionNumber"


  		




  102
  
  
    
, collectiondate AS "dateCollected"


  		




  103
  
  
    
, count(*) AS "specimenRecords"


  		




  104
  
  
    
FROM taxonoccurrence


  		




  105
  
  
    
JOIN aggregateoccurrence USING (taxonoccurrence_id)


  		




  106
  
  
    
JOIN plantobservation USING (aggregateoccurrence_id)


  		




  107
  
  
    
JOIN specimenreplicate USING (plantobservation_id)


  		




  108
  
  
    
WHERE taxonoccurrence.source_id = (SELECT source_by_shortname(:datasource))


  		




  109
  
  
    
GROUP BY "collectorName", "collectionNumber", "dateCollected"


  		




  110
  
  
    
ORDER BY "collectorName", "collectionNumber", "dateCollected"


  		




  111
  
  
    
;


  		




  
  82
  
    
 SELECT ( SELECT party.fullname


  		




  
  83
  
    
           FROM public.party


  		




  
  84
  
    
          WHERE (party.party_id = taxonoccurrence.collector_id)) AS "collectorName", 


  		




  
  85
  
    
    specimenreplicate.collectionnumber AS "collectionNumber", 


  		




  
  86
  
    
    aggregateoccurrence.collectiondate AS "dateCollected", 


  		




  
  87
  
    
    count(*) AS "specimenRecords"


  		




  
  88
  
    
   FROM (((public.taxonoccurrence


  		




  
  89
  
    
   JOIN public.aggregateoccurrence USING (taxonoccurrence_id))


  		




  
  90
  
    
   JOIN public.plantobservation USING (aggregateoccurrence_id))


  		




  
  91
  
    
   JOIN public.specimenreplicate USING (plantobservation_id))


  		




  
  92
  
    
  WHERE (taxonoccurrence.source_id = ( SELECT public.source_by_shortname((:datasource)::text) AS source_by_shortname))


  		




  
  93
  
    
  GROUP BY ( SELECT party.fullname


  		




  
  94
  
    
   FROM public.party


  		




  
  95
  
    
  WHERE (party.party_id = taxonoccurrence.collector_id)), specimenreplicate.collectionnumber, aggregateoccurrence.collectiondate


  		




  
  96
  
    
  ORDER BY ( SELECT party.fullname


  		




  
  97
  
    
   FROM public.party


  		




  
  98
  
    
  WHERE (party.party_id = taxonoccurrence.collector_id)), specimenreplicate.collectionnumber, aggregateoccurrence.collectiondate;


  		




  112
  99
  
    

  		




  113
  100
  
    
-- _specimens_13_count_of_all_verbatim_and_decimal_lat_long


  		




  114
  
  
    
SELECT


  		




  115
  
  
    
   (SELECT count(*) AS count


  		




  116
  
  
    
	FROM coordinates


  		




  117
  
  
    
	WHERE coordinates.source_id = (SELECT source_by_shortname(:datasource))


  		




  118
  
  
    
	AND (verbatimlatitude IS NOT NULL OR latitude_deg IS NOT NULL))


  		




  119
  
  
    
	AS "allLats"


  		




  120
  
  
    
,  (SELECT count(*) AS count


  		




  121
  
  
    
	FROM coordinates


  		




  122
  
  
    
	WHERE coordinates.source_id = (SELECT source_by_shortname(:datasource))


  		




  123
  
  
    
	AND (latitude_deg IS NOT NULL))


  		




  124
  
  
    
	AS "decimalLats"


  		




  125
  
  
    
,  (SELECT count(*) AS count


  		




  126
  
  
    
	FROM coordinates


  		




  127
  
  
    
	WHERE coordinates.source_id = (SELECT source_by_shortname(:datasource))


  		




  128
  
  
    
	AND (verbatimlongitude IS NOT NULL OR longitude_deg IS NOT NULL))


  		




  129
  
  
    
	AS "allLongs"


  		




  130
  
  
    
,  (SELECT count(*) AS count


  		




  131
  
  
    
	FROM coordinates


  		




  132
  
  
    
	WHERE coordinates.source_id = (SELECT source_by_shortname(:datasource))


  		




  133
  
  
    
	AND (longitude_deg IS NOT NULL))


  		




  134
  
  
    
	AS "decimalLongs"


  		




  135
  
  
    
;


  		




  
  101
  
    
 SELECT ( SELECT count(*) AS count


  		




  
  102
  
    
           FROM public.coordinates


  		




  
  103
  
    
          WHERE ((coordinates.source_id = ( SELECT public.source_by_shortname((:datasource)::text) AS source_by_shortname)) AND ((coordinates.verbatimlatitude IS NOT NULL) OR (coordinates.latitude_deg IS NOT NULL)))) AS "allLats", 


  		




  
  104
  
    
    ( SELECT count(*) AS count


  		




  
  105
  
    
           FROM public.coordinates


  		




  
  106
  
    
          WHERE ((coordinates.source_id = ( SELECT public.source_by_shortname((:datasource)::text) AS source_by_shortname)) AND (coordinates.latitude_deg IS NOT NULL))) AS "decimalLats", 


  		




  
  107
  
    
    ( SELECT count(*) AS count


  		




  
  108
  
    
           FROM public.coordinates


  		




  
  109
  
    
          WHERE ((coordinates.source_id = ( SELECT public.source_by_shortname((:datasource)::text) AS source_by_shortname)) AND ((coordinates.verbatimlongitude IS NOT NULL) OR (coordinates.longitude_deg IS NOT NULL)))) AS "allLongs", 


  		




  
  110
  
    
    ( SELECT count(*) AS count


  		




  
  111
  
    
           FROM public.coordinates


  		




  
  112
  
    
          WHERE ((coordinates.source_id = ( SELECT public.source_by_shortname((:datasource)::text) AS source_by_shortname)) AND (coordinates.longitude_deg IS NOT NULL))) AS "decimalLongs";


  		




  136
  113
  
    

  		




  137
  114
  
    
-- _specimens_14_count_of_all_invalid_verbatim_lat_long


  		




  138
  
  
    
SELECT


  		




  139
  
  
    
  (SELECT count(*) AS count


  		




  140
  
  
    
	FROM coordinates


  		




  141
  
  
    
	WHERE coordinates.source_id = (SELECT source_by_shortname(:datasource))


  		




  142
  
  
    
	AND (((coordinates.latitude_deg)::double precision > (90)::double precision) OR ((coordinates.latitude_deg)::double precision < ((-90))::double precision))) AS "badLats"


  		




  143
  
  
    
, (SELECT count(*) AS count


  		




  144
  
  
    
	FROM coordinates


  		




  145
  
  
    
	WHERE coordinates.source_id = (SELECT source_by_shortname(:datasource))


  		




  146
  
  
    
	AND (((coordinates.longitude_deg)::double precision > (180)::double precision) OR ((coordinates.longitude_deg)::double precision < ((-180))::double precision))) AS "badLongs"


  		




  147
  
  
    
;


  		




  
  115
  
    
 SELECT ( SELECT count(*) AS count


  		




  
  116
  
    
           FROM public.coordinates


  		




  
  117
  
    
          WHERE ((coordinates.source_id = ( SELECT public.source_by_shortname((:datasource)::text) AS source_by_shortname)) AND ((coordinates.latitude_deg > (90)::double precision) OR (coordinates.latitude_deg < ((-90))::double precision)))) AS "badLats", 


  		




  
  118
  
    
    ( SELECT count(*) AS count


  		




  
  119
  
    
           FROM public.coordinates


  		




  
  120
  
    
          WHERE ((coordinates.source_id = ( SELECT public.source_by_shortname((:datasource)::text) AS source_by_shortname)) AND ((coordinates.longitude_deg > (180)::double precision) OR (coordinates.longitude_deg < ((-180))::double precision)))) AS "badLongs";


  		




  148
  121
  
    

  		




  149
  122
  
    
-- _specimens_15_list_distinct_locality_descriptions


  		




  150
  
  
    
SELECT DISTINCT locationnarrative AS "localityDescription"


  		




  151
  
  
    
FROM location


  		




  152
  
  
    
WHERE location.source_id = (SELECT source_by_shortname(:datasource))


  		




  153
  
  
    
AND locationnarrative IS NOT NULL


  		




  154
  
  
    
;


  		




  
  123
  
    
 SELECT DISTINCT location.locationnarrative AS "localityDescription"


  		




  
  124
  
    
   FROM public.location


  		




  
  125
  
    
  WHERE ((location.source_id = ( SELECT public.source_by_shortname((:datasource)::text) AS source_by_shortname)) AND (location.locationnarrative IS NOT NULL));


  		




  155
  126
  
    

  		




  156
  127
  
    
-- _specimens_16_list_distinct_specimen_descriptions


  		




  157
  
  
    
SELECT DISTINCT notes AS "specimenDescription"


  		




  158
  
  
    
FROM aggregateoccurrence


  		




  159
  
  
    
WHERE aggregateoccurrence.source_id = (SELECT source_by_shortname(:datasource))


  		




  160
  
  
    
AND notes IS NOT NULL


  		




  161
  
  
    
;


  		




  
  128
  
    
 SELECT DISTINCT aggregateoccurrence.notes AS "specimenDescription"


  		




  
  129
  
    
   FROM public.aggregateoccurrence


  		




  
  130
  
    
  WHERE ((aggregateoccurrence.source_id = ( SELECT public.source_by_shortname((:datasource)::text) AS source_by_shortname)) AND (aggregateoccurrence.notes IS NOT NULL));


  		












Also available in:  Unified diff