Revision 13159
Added by Aaron Marcuse-Kubitza over 10 years ago
| trunk/validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql | ||
|---|---|---|
| 56 | 56 |
-- _specimens_07_list_of_verbatim_subspecific_taxa_with_author |
| 57 | 57 |
SELECT DISTINCT "Ecatalog_all"."scientificName" AS taxon_name_with_author |
| 58 | 58 |
FROM "Ecatalog_all" |
| 59 |
WHERE ((("Ecatalog_all".genus IS NOT NULL) AND ("Ecatalog_all"."specificEpithet" IS NOT NULL)) AND ("Ecatalog_all".subspecies IS NOT NULL));
|
|
| 59 |
WHERE ("Ecatalog_all".subspecies IS NOT NULL);
|
|
| 60 | 60 |
/* |
| 61 | 61 |
Check: should return 40145 rows |
| 62 | 62 |
*/ |
| 63 | 63 |
|
| 64 | 64 |
-- _specimens_08_count_of_unique_verbatim_author_taxa_with_genus |
| 65 |
SELECT count(DISTINCT btrim(concat_ws(' '::text, btrim(concat_ws(' '::text, "Ecatalog_all".genus, ifnull("Ecatalog_all"."specificEpithet", ''::text), ifnull("Ecatalog_all".subspecies, ''::text))), btrim(ifnull("Ecatalog_all"."scientificNameAuthorship", ''::text))))) AS taxon_names_with_author
|
|
| 65 |
SELECT count(DISTINCT "Ecatalog_all"."scientificName") AS taxon_names_with_author
|
|
| 66 | 66 |
FROM "Ecatalog_all" |
| 67 | 67 |
WHERE ("Ecatalog_all".genus IS NOT NULL);
|
| 68 | 68 |
/* |
| ... | ... | |
| 70 | 70 |
*/ |
| 71 | 71 |
|
| 72 | 72 |
-- _specimens_09_list_of_unique_verbatim_author_taxa_with_genus |
| 73 |
SELECT DISTINCT btrim(concat_ws(' '::text, btrim(concat_ws(' '::text, "Ecatalog_all".genus, ifnull("Ecatalog_all"."specificEpithet", ''::text), ifnull("Ecatalog_all".subspecies, ''::text))), btrim(ifnull("Ecatalog_all"."scientificNameAuthorship", ''::text)))) AS taxon_name_with_author
|
|
| 73 |
SELECT DISTINCT "Ecatalog_all"."scientificName" AS taxon_name_with_author
|
|
| 74 | 74 |
FROM "Ecatalog_all" |
| 75 | 75 |
WHERE ("Ecatalog_all".genus IS NOT NULL);
|
| 76 | 76 |
/* |
| ... | ... | |
| 112 | 112 |
*/ |
| 113 | 113 |
|
| 114 | 114 |
-- _specimens_13_count_of_all_verbatim_and_decimal_lat_long |
| 115 |
SELECT ( SELECT count(*) AS count
|
|
| 115 |
SELECT ( SELECT count(DISTINCT ROW("Ecatalog_all"."decimalLatitude", "Ecatalog_all"."decimalLongitude")) AS count
|
|
| 116 | 116 |
FROM "Ecatalog_all" |
| 117 |
WHERE ("Ecatalog_all"."decimalLatitude" IS NOT NULL)) AS "allLats",
|
|
| 118 |
( SELECT count(*) AS count
|
|
| 117 |
WHERE (("Ecatalog_all"."decimalLatitude" IS NOT NULL) OR ("Ecatalog_all"."decimalLongitude" IS NOT NULL))) AS "allLatLongs",
|
|
| 118 |
( SELECT count(DISTINCT ROW("Ecatalog_all"."decimalLatitude", "Ecatalog_all"."decimalLongitude")) AS count
|
|
| 119 | 119 |
FROM "Ecatalog_all" |
| 120 |
WHERE ("Ecatalog_all"."decimalLatitude" ~ '^(-|\\+){0,1}([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$'::text)) AS "decimalLats",
|
|
| 121 |
( SELECT count(*) AS count |
|
| 122 |
FROM "Ecatalog_all" |
|
| 123 |
WHERE ("Ecatalog_all"."decimalLongitude" IS NOT NULL)) AS "allLongs",
|
|
| 124 |
( SELECT count(*) AS count |
|
| 125 |
FROM "Ecatalog_all" |
|
| 126 |
WHERE ("Ecatalog_all"."decimalLongitude" ~ '^(-|\\+){0,1}([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$'::text)) AS "decimalLongs";
|
|
| 120 |
WHERE (is_castable("Ecatalog_all"."decimalLatitude", NULL::double precision) AND is_castable("Ecatalog_all"."decimalLongitude", NULL::double precision))) AS "decimalLatLongs";
|
|
| 127 | 121 |
/* |
| 128 | 122 |
Check: should return 1 row |
| 129 | 123 |
*/ |
| 130 | 124 |
|
| 125 |
-- _specimens_13a_list_of_all_verbatim_lat_long |
|
| 126 |
SELECT DISTINCT "Ecatalog_all"."decimalLatitude" AS "verbatimLatitude", |
|
| 127 |
"Ecatalog_all"."decimalLongitude" AS "verbatimLongitude" |
|
| 128 |
FROM "Ecatalog_all" |
|
| 129 |
WHERE (("Ecatalog_all"."decimalLatitude" IS NOT NULL) OR ("Ecatalog_all"."decimalLongitude" IS NOT NULL));
|
|
| 130 |
|
|
| 131 |
-- _specimens_13b_list_of_all_decimal_lat_long |
|
| 132 |
SELECT DISTINCT ("Ecatalog_all"."decimalLatitude")::double precision AS "decimalLatitude",
|
|
| 133 |
("Ecatalog_all"."decimalLongitude")::double precision AS "decimalLongitude"
|
|
| 134 |
FROM "Ecatalog_all" |
|
| 135 |
WHERE (is_castable("Ecatalog_all"."decimalLatitude", NULL::double precision) AND is_castable("Ecatalog_all"."decimalLongitude", NULL::double precision));
|
|
| 136 |
|
|
| 131 | 137 |
-- _specimens_14_count_of_all_invalid_verbatim_lat_long |
| 132 | 138 |
SELECT ( SELECT count(*) AS count |
| 133 | 139 |
FROM "Ecatalog_all" |
| ... | ... | |
| 140 | 146 |
*/ |
| 141 | 147 |
|
| 142 | 148 |
-- _specimens_15_list_distinct_locality_descriptions |
| 143 |
SELECT DISTINCT "Ecatalog_all".locality__main AS "localityDescription"
|
|
| 149 |
SELECT DISTINCT "Ecatalog_all".locality AS "localityDescription" |
|
| 144 | 150 |
FROM "Ecatalog_all" |
| 145 |
WHERE ("Ecatalog_all".locality__main IS NOT NULL);
|
|
| 151 |
WHERE ("Ecatalog_all".locality IS NOT NULL);
|
|
| 146 | 152 |
/* |
| 147 | 153 |
Check: should return 125592 records |
| 148 | 154 |
*/ |
Also available in: Unified diff
validation/aggregating/specimens/NY/qualitative_validations_source_db_NYBG.VegCore.sql: updated to inputs/NY/validations.sql