Revision 13083
Added by Aaron Marcuse-Kubitza over 10 years ago
| trunk/validation/aggregating/specimens/qualitative_validations_specimens.sql | ||
|---|---|---|
| 16 | 16 |
|
| 17 | 17 |
-- _specimens_01_count_of_total_records_specimens_in_source_db |
| 18 | 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));
|
|
| 19 |
FROM taxonoccurrence |
|
| 20 |
WHERE (taxonoccurrence.source_id = ( SELECT source_by_shortname((:datasource)::text) AS source_by_shortname)); |
|
| 21 | 21 |
|
| 22 | 22 |
-- _specimens_02_count_of_unique_verbatim_families |
| 23 | 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));
|
|
| 24 |
FROM taxonverbatim |
|
| 25 |
WHERE ((taxonverbatim.source_id = ( SELECT source_by_shortname((:datasource)::text) AS source_by_shortname)) AND (taxonverbatim.family IS NOT NULL)); |
|
| 26 | 26 |
|
| 27 | 27 |
-- _specimens_03_list_of_verbatim_families |
| 28 | 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));
|
|
| 29 |
FROM taxonverbatim |
|
| 30 |
WHERE ((taxonverbatim.source_id = ( SELECT source_by_shortname((:datasource)::text) AS source_by_shortname)) AND (taxonverbatim.family IS NOT NULL)); |
|
| 31 | 31 |
|
| 32 | 32 |
-- _specimens_04_count_of_species_binomials |
| 33 | 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));
|
|
| 34 |
FROM taxonverbatim |
|
| 35 |
WHERE (((taxonverbatim.source_id = ( SELECT source_by_shortname((:datasource)::text) AS source_by_shortname)) AND (taxonverbatim.genus IS NOT NULL)) AND (taxonverbatim.specific_epithet IS NOT NULL)); |
|
| 36 | 36 |
|
| 37 | 37 |
-- _specimens_05_list_of_species_binomials |
| 38 | 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));
|
|
| 39 |
FROM taxonverbatim |
|
| 40 |
WHERE (((taxonverbatim.source_id = ( SELECT source_by_shortname((:datasource)::text) AS source_by_shortname)) AND (taxonverbatim.genus IS NOT NULL)) AND (taxonverbatim.specific_epithet IS NOT NULL)); |
|
| 41 | 41 |
|
| 42 | 42 |
-- _specimens_06_count_of_unique_verb_subsp_taxa_with_author |
| 43 | 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));
|
|
| 44 |
FROM taxonlabel |
|
| 45 |
WHERE ((taxonlabel.source_id = ( SELECT source_by_shortname((:datasource)::text) AS source_by_shortname)) AND (taxonlabel.taxonomicname IS NOT NULL)); |
|
| 46 | 46 |
|
| 47 | 47 |
-- _specimens_07_list_of_verbatim_subspecific_taxa_with_author |
| 48 | 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));
|
|
| 49 |
FROM taxonlabel |
|
| 50 |
WHERE ((taxonlabel.source_id = ( SELECT source_by_shortname((:datasource)::text) AS source_by_shortname)) AND (taxonlabel.taxonomicname IS NOT NULL)); |
|
| 51 | 51 |
|
| 52 | 52 |
-- _specimens_08_count_of_unique_verbatim_author_taxa_with_genus |
| 53 | 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));
|
|
| 54 |
FROM (taxonverbatim |
|
| 55 |
JOIN taxonlabel USING (taxonlabel_id)) |
|
| 56 |
WHERE ((taxonverbatim.source_id = ( SELECT source_by_shortname((:datasource)::text) AS source_by_shortname)) AND (taxonverbatim.genus IS NOT NULL)); |
|
| 57 | 57 |
|
| 58 | 58 |
-- _specimens_09_list_of_unique_verbatim_author_taxa_with_genus |
| 59 | 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));
|
|
| 60 |
FROM (taxonverbatim |
|
| 61 |
JOIN taxonlabel USING (taxonlabel_id)) |
|
| 62 |
WHERE ((taxonverbatim.source_id = ( SELECT source_by_shortname((:datasource)::text) AS source_by_shortname)) AND (taxonverbatim.genus IS NOT NULL)); |
|
| 63 | 63 |
|
| 64 | 64 |
-- _specimens_10_count_number_of_records_by_institution |
| 65 | 65 |
SELECT ( SELECT sourcelist.name |
| 66 |
FROM public.sourcelist
|
|
| 66 |
FROM sourcelist |
|
| 67 | 67 |
WHERE (sourcelist.sourcelist_id = specimenreplicate.duplicate_institutions_sourcelist_id)) AS specimen_duplicate_institutions, |
| 68 | 68 |
count(*) AS records |
| 69 |
FROM public.specimenreplicate
|
|
| 70 |
WHERE (specimenreplicate.source_id = ( SELECT public.source_by_shortname((:datasource)::text) AS source_by_shortname))
|
|
| 69 |
FROM specimenreplicate |
|
| 70 |
WHERE (specimenreplicate.source_id = ( SELECT source_by_shortname((:datasource)::text) AS source_by_shortname)) |
|
| 71 | 71 |
GROUP BY specimenreplicate.duplicate_institutions_sourcelist_id; |
| 72 | 72 |
|
| 73 | 73 |
-- _specimens_11_list_of_three_standard_political_divisions |
| 74 | 74 |
SELECT DISTINCT place.country, |
| 75 | 75 |
place.stateprovince, |
| 76 | 76 |
place.county |
| 77 |
FROM public.place
|
|
| 78 |
WHERE (place.source_id = ( SELECT public.source_by_shortname((:datasource)::text) AS source_by_shortname));
|
|
| 77 |
FROM place |
|
| 78 |
WHERE (place.source_id = ( SELECT source_by_shortname((:datasource)::text) AS source_by_shortname)); |
|
| 79 | 79 |
/* |
| 80 | 80 |
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 |
| 81 | 81 |
*/ |
| 82 | 82 |
|
| 83 | 83 |
-- _specimens_12_distinct_collector_name_collect_num_date_w_count |
| 84 | 84 |
SELECT ( SELECT party.fullname |
| 85 |
FROM public.party
|
|
| 85 |
FROM party |
|
| 86 | 86 |
WHERE (party.party_id = taxonoccurrence.collector_id)) AS "collectorName", |
| 87 | 87 |
specimenreplicate.collectionnumber AS "collectionNumber", |
| 88 | 88 |
aggregateoccurrence.collectiondate AS "dateCollected", |
| 89 | 89 |
count(*) AS "specimenRecords" |
| 90 |
FROM (((public.taxonoccurrence
|
|
| 91 |
JOIN public.aggregateoccurrence USING (taxonoccurrence_id))
|
|
| 92 |
JOIN public.plantobservation USING (aggregateoccurrence_id))
|
|
| 93 |
JOIN public.specimenreplicate USING (plantobservation_id))
|
|
| 94 |
WHERE (taxonoccurrence.source_id = ( SELECT public.source_by_shortname((:datasource)::text) AS source_by_shortname))
|
|
| 90 |
FROM (((taxonoccurrence |
|
| 91 |
JOIN aggregateoccurrence USING (taxonoccurrence_id)) |
|
| 92 |
JOIN plantobservation USING (aggregateoccurrence_id)) |
|
| 93 |
JOIN specimenreplicate USING (plantobservation_id)) |
|
| 94 |
WHERE (taxonoccurrence.source_id = ( SELECT source_by_shortname((:datasource)::text) AS source_by_shortname)) |
|
| 95 | 95 |
GROUP BY ( SELECT party.fullname |
| 96 |
FROM public.party
|
|
| 96 |
FROM party |
|
| 97 | 97 |
WHERE (party.party_id = taxonoccurrence.collector_id)), specimenreplicate.collectionnumber, aggregateoccurrence.collectiondate |
| 98 | 98 |
ORDER BY ( SELECT party.fullname |
| 99 |
FROM public.party
|
|
| 99 |
FROM party |
|
| 100 | 100 |
WHERE (party.party_id = taxonoccurrence.collector_id)), specimenreplicate.collectionnumber, aggregateoccurrence.collectiondate; |
| 101 | 101 |
|
| 102 | 102 |
-- _specimens_13_count_of_all_verbatim_and_decimal_lat_long |
| 103 | 103 |
SELECT ( SELECT count(*) AS count |
| 104 |
FROM public.coordinates
|
|
| 105 |
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 |
FROM coordinates |
|
| 105 |
WHERE ((coordinates.source_id = ( SELECT source_by_shortname((:datasource)::text) AS source_by_shortname)) AND ((coordinates.verbatimlatitude IS NOT NULL) OR (coordinates.latitude_deg IS NOT NULL)))) AS "allLats", |
|
| 106 | 106 |
( SELECT count(*) AS count |
| 107 |
FROM public.coordinates
|
|
| 108 |
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 |
FROM coordinates |
|
| 108 |
WHERE ((coordinates.source_id = ( SELECT source_by_shortname((:datasource)::text) AS source_by_shortname)) AND (coordinates.latitude_deg IS NOT NULL))) AS "decimalLats", |
|
| 109 | 109 |
( SELECT count(*) AS count |
| 110 |
FROM public.coordinates
|
|
| 111 |
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 |
FROM coordinates |
|
| 111 |
WHERE ((coordinates.source_id = ( SELECT source_by_shortname((:datasource)::text) AS source_by_shortname)) AND ((coordinates.verbatimlongitude IS NOT NULL) OR (coordinates.longitude_deg IS NOT NULL)))) AS "allLongs", |
|
| 112 | 112 |
( SELECT count(*) AS count |
| 113 |
FROM public.coordinates
|
|
| 114 |
WHERE ((coordinates.source_id = ( SELECT public.source_by_shortname((:datasource)::text) AS source_by_shortname)) AND (coordinates.longitude_deg IS NOT NULL))) AS "decimalLongs";
|
|
| 113 |
FROM coordinates |
|
| 114 |
WHERE ((coordinates.source_id = ( SELECT source_by_shortname((:datasource)::text) AS source_by_shortname)) AND (coordinates.longitude_deg IS NOT NULL))) AS "decimalLongs"; |
|
| 115 | 115 |
|
| 116 | 116 |
-- _specimens_14_count_of_all_invalid_verbatim_lat_long |
| 117 | 117 |
SELECT ( SELECT count(*) AS count |
| 118 |
FROM public.coordinates
|
|
| 119 |
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 |
FROM coordinates |
|
| 119 |
WHERE ((coordinates.source_id = ( SELECT 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", |
|
| 120 | 120 |
( SELECT count(*) AS count |
| 121 |
FROM public.coordinates
|
|
| 122 |
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";
|
|
| 121 |
FROM coordinates |
|
| 122 |
WHERE ((coordinates.source_id = ( SELECT 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"; |
|
| 123 | 123 |
|
| 124 | 124 |
-- _specimens_15_list_distinct_locality_descriptions |
| 125 | 125 |
SELECT DISTINCT location.locationnarrative AS "localityDescription" |
| 126 |
FROM public.location
|
|
| 127 |
WHERE ((location.source_id = ( SELECT public.source_by_shortname((:datasource)::text) AS source_by_shortname)) AND (location.locationnarrative IS NOT NULL));
|
|
| 126 |
FROM location |
|
| 127 |
WHERE ((location.source_id = ( SELECT source_by_shortname((:datasource)::text) AS source_by_shortname)) AND (location.locationnarrative IS NOT NULL)); |
|
| 128 | 128 |
|
| 129 | 129 |
-- _specimens_16_list_distinct_specimen_descriptions |
| 130 | 130 |
SELECT DISTINCT aggregateoccurrence.notes AS "specimenDescription" |
| 131 |
FROM public.aggregateoccurrence |
|
| 132 |
WHERE ((aggregateoccurrence.source_id = ( SELECT public.source_by_shortname((:datasource)::text) AS source_by_shortname)) AND (aggregateoccurrence.notes IS NOT NULL)); |
|
| 131 |
FROM aggregateoccurrence |
|
| 132 |
WHERE ((aggregateoccurrence.source_id = ( SELECT source_by_shortname((:datasource)::text) AS source_by_shortname)) AND (aggregateoccurrence.notes IS NOT NULL)); |
|
Also available in: Unified diff
validation/aggregating/specimens/qualitative_validations_specimens.sql: removed `public.` prefix to avoid cluttering up the SQL