Revision 13374
Added by Aaron Marcuse-Kubitza about 10 years ago
validations.sql | ||
---|---|---|
15 | 15 |
-- Name: _specimens_01_count_of_total_records_specimens_in_source_db; Type: VIEW; Schema: NY; Owner: - |
16 | 16 |
-- |
17 | 17 |
|
18 |
CREATE VIEW _specimens_01_count_of_total_records_specimens_in_source_db AS |
|
18 |
CREATE OR REPLACE VIEW _specimens_01_count_of_total_records_specimens_in_source_db AS
|
|
19 | 19 |
SELECT count(*) AS "totalSpecimenRecords" |
20 | 20 |
FROM "Ecatalog_all"; |
21 | 21 |
|
... | ... | |
33 | 33 |
-- Name: _specimens_02_count_of_unique_verbatim_families; Type: VIEW; Schema: NY; Owner: - |
34 | 34 |
-- |
35 | 35 |
|
36 |
CREATE VIEW _specimens_02_count_of_unique_verbatim_families AS |
|
36 |
CREATE OR REPLACE VIEW _specimens_02_count_of_unique_verbatim_families AS
|
|
37 | 37 |
SELECT count(DISTINCT "Ecatalog_all".family) AS families |
38 | 38 |
FROM "Ecatalog_all" |
39 | 39 |
WHERE ("Ecatalog_all".family IS NOT NULL); |
... | ... | |
52 | 52 |
-- Name: _specimens_03_list_of_verbatim_families; Type: VIEW; Schema: NY; Owner: - |
53 | 53 |
-- |
54 | 54 |
|
55 |
CREATE VIEW _specimens_03_list_of_verbatim_families AS |
|
55 |
CREATE OR REPLACE VIEW _specimens_03_list_of_verbatim_families AS
|
|
56 | 56 |
SELECT DISTINCT "Ecatalog_all".family |
57 | 57 |
FROM "Ecatalog_all" |
58 | 58 |
WHERE (("Ecatalog_all".genus IS NOT NULL) AND ("Ecatalog_all"."specificEpithet" IS NOT NULL)); |
... | ... | |
71 | 71 |
-- Name: _specimens_04_count_of_species_binomials; Type: VIEW; Schema: NY; Owner: - |
72 | 72 |
-- |
73 | 73 |
|
74 |
CREATE VIEW _specimens_04_count_of_species_binomials AS |
|
74 |
CREATE OR REPLACE VIEW _specimens_04_count_of_species_binomials AS
|
|
75 | 75 |
SELECT count(DISTINCT concat_ws(' '::text, "Ecatalog_all".genus, "Ecatalog_all"."specificEpithet")) AS species_binomials |
76 | 76 |
FROM "Ecatalog_all" |
77 | 77 |
WHERE (("Ecatalog_all".genus IS NOT NULL) AND ("Ecatalog_all"."specificEpithet" IS NOT NULL)); |
... | ... | |
90 | 90 |
-- Name: _specimens_05_list_of_species_binomials; Type: VIEW; Schema: NY; Owner: - |
91 | 91 |
-- |
92 | 92 |
|
93 |
CREATE VIEW _specimens_05_list_of_species_binomials AS |
|
93 |
CREATE OR REPLACE VIEW _specimens_05_list_of_species_binomials AS
|
|
94 | 94 |
SELECT DISTINCT concat_ws(' '::text, "Ecatalog_all".genus, "Ecatalog_all"."specificEpithet") AS species_binomial |
95 | 95 |
FROM "Ecatalog_all" |
96 | 96 |
WHERE (("Ecatalog_all".genus IS NOT NULL) AND ("Ecatalog_all"."specificEpithet" IS NOT NULL)); |
... | ... | |
109 | 109 |
-- Name: _specimens_06_count_of_unique_verb_subsp_taxa_with_author; Type: VIEW; Schema: NY; Owner: - |
110 | 110 |
-- |
111 | 111 |
|
112 |
CREATE VIEW _specimens_06_count_of_unique_verb_subsp_taxa_with_author AS |
|
112 |
CREATE OR REPLACE VIEW _specimens_06_count_of_unique_verb_subsp_taxa_with_author AS
|
|
113 | 113 |
SELECT count(DISTINCT "Ecatalog_all"."scientificName") AS taxon_names_with_author |
114 | 114 |
FROM "Ecatalog_all" |
115 | 115 |
WHERE ((("Ecatalog_all".genus IS NOT NULL) AND ("Ecatalog_all"."specificEpithet" IS NOT NULL)) AND ("Ecatalog_all".subspecies IS NOT NULL)); |
... | ... | |
128 | 128 |
-- Name: _specimens_07_list_of_verbatim_subspecific_taxa_with_author; Type: VIEW; Schema: NY; Owner: - |
129 | 129 |
-- |
130 | 130 |
|
131 |
CREATE VIEW _specimens_07_list_of_verbatim_subspecific_taxa_with_author AS |
|
131 |
CREATE OR REPLACE VIEW _specimens_07_list_of_verbatim_subspecific_taxa_with_author AS
|
|
132 | 132 |
SELECT DISTINCT "Ecatalog_all"."scientificName" AS taxon_name_with_author |
133 | 133 |
FROM "Ecatalog_all" |
134 | 134 |
WHERE ("Ecatalog_all".subspecies IS NOT NULL); |
... | ... | |
147 | 147 |
-- Name: _specimens_08_count_of_unique_verbatim_author_taxa_with_genus; Type: VIEW; Schema: NY; Owner: - |
148 | 148 |
-- |
149 | 149 |
|
150 |
CREATE VIEW _specimens_08_count_of_unique_verbatim_author_taxa_with_genus AS |
|
150 |
CREATE OR REPLACE VIEW _specimens_08_count_of_unique_verbatim_author_taxa_with_genus AS
|
|
151 | 151 |
SELECT count(DISTINCT "Ecatalog_all"."scientificName") AS taxon_names_with_author |
152 | 152 |
FROM "Ecatalog_all" |
153 | 153 |
WHERE ("Ecatalog_all".genus IS NOT NULL); |
... | ... | |
166 | 166 |
-- Name: _specimens_09_list_of_unique_verbatim_author_taxa_with_genus; Type: VIEW; Schema: NY; Owner: - |
167 | 167 |
-- |
168 | 168 |
|
169 |
CREATE VIEW _specimens_09_list_of_unique_verbatim_author_taxa_with_genus AS |
|
169 |
CREATE OR REPLACE VIEW _specimens_09_list_of_unique_verbatim_author_taxa_with_genus AS
|
|
170 | 170 |
SELECT DISTINCT "Ecatalog_all"."scientificName" AS taxon_name_with_author |
171 | 171 |
FROM "Ecatalog_all" |
172 | 172 |
WHERE ("Ecatalog_all".genus IS NOT NULL); |
... | ... | |
185 | 185 |
-- Name: _specimens_10_count_number_of_records_by_institution; Type: VIEW; Schema: NY; Owner: - |
186 | 186 |
-- |
187 | 187 |
|
188 |
CREATE VIEW _specimens_10_count_number_of_records_by_institution AS |
|
188 |
CREATE OR REPLACE VIEW _specimens_10_count_number_of_records_by_institution AS
|
|
189 | 189 |
SELECT "Ecatalog_all".specimen_duplicate_institutions, |
190 | 190 |
count(*) AS records |
191 | 191 |
FROM "Ecatalog_all" |
... | ... | |
207 | 207 |
-- Name: _specimens_11_list_of_three_standard_political_divisions; Type: VIEW; Schema: NY; Owner: - |
208 | 208 |
-- |
209 | 209 |
|
210 |
CREATE VIEW _specimens_11_list_of_three_standard_political_divisions AS |
|
210 |
CREATE OR REPLACE VIEW _specimens_11_list_of_three_standard_political_divisions AS
|
|
211 | 211 |
SELECT DISTINCT "Ecatalog_all".country, |
212 | 212 |
"Ecatalog_all"."stateProvince", |
213 | 213 |
"Ecatalog_all".county |
... | ... | |
229 | 229 |
-- Name: _specimens_12_distinct_collector_name_collect_num_date_w_count; Type: VIEW; Schema: NY; Owner: - |
230 | 230 |
-- |
231 | 231 |
|
232 |
CREATE VIEW _specimens_12_distinct_collector_name_collect_num_date_w_count AS |
|
232 |
CREATE OR REPLACE VIEW _specimens_12_distinct_collector_name_collect_num_date_w_count AS
|
|
233 | 233 |
SELECT ifnull("Ecatalog_all"."recordedBy", ''::text) AS "collectorName", |
234 | 234 |
ifnull("Ecatalog_all"."collectorNumber", ''::text) AS "collectionNumber", |
235 | 235 |
concat_ws('-'::text, "Ecatalog_all"."yearCollected", "Ecatalog_all"."monthCollected", "Ecatalog_all"."dayCollected") AS "dateCollected", |
... | ... | |
252 | 252 |
-- Name: _specimens_13_count_of_all_verbatim_and_decimal_lat_long; Type: VIEW; Schema: NY; Owner: - |
253 | 253 |
-- |
254 | 254 |
|
255 |
CREATE VIEW _specimens_13_count_of_all_verbatim_and_decimal_lat_long AS |
|
255 |
CREATE OR REPLACE VIEW _specimens_13_count_of_all_verbatim_and_decimal_lat_long AS
|
|
256 | 256 |
SELECT ( SELECT count(DISTINCT ROW("Ecatalog_all"."decimalLatitude", "Ecatalog_all"."decimalLongitude")) AS count |
257 | 257 |
FROM "Ecatalog_all" |
258 | 258 |
WHERE (("Ecatalog_all"."decimalLatitude" IS NOT NULL) OR ("Ecatalog_all"."decimalLongitude" IS NOT NULL))) AS "allLatLongs", |
... | ... | |
274 | 274 |
-- Name: _specimens_13a_list_of_all_verbatim_lat_long; Type: VIEW; Schema: NY; Owner: - |
275 | 275 |
-- |
276 | 276 |
|
277 |
CREATE VIEW _specimens_13a_list_of_all_verbatim_lat_long AS |
|
277 |
CREATE OR REPLACE VIEW _specimens_13a_list_of_all_verbatim_lat_long AS
|
|
278 | 278 |
SELECT DISTINCT "Ecatalog_all"."decimalLatitude" AS "verbatimLatitude", |
279 | 279 |
"Ecatalog_all"."decimalLongitude" AS "verbatimLongitude" |
280 | 280 |
FROM "Ecatalog_all" |
... | ... | |
285 | 285 |
-- Name: _specimens_13b_list_of_all_decimal_lat_long; Type: VIEW; Schema: NY; Owner: - |
286 | 286 |
-- |
287 | 287 |
|
288 |
CREATE VIEW _specimens_13b_list_of_all_decimal_lat_long AS |
|
288 |
CREATE OR REPLACE VIEW _specimens_13b_list_of_all_decimal_lat_long AS
|
|
289 | 289 |
SELECT DISTINCT ("Ecatalog_all"."decimalLatitude")::double precision AS "decimalLatitude", |
290 | 290 |
("Ecatalog_all"."decimalLongitude")::double precision AS "decimalLongitude" |
291 | 291 |
FROM "Ecatalog_all" |
... | ... | |
296 | 296 |
-- Name: _specimens_14_count_of_all_invalid_verbatim_lat_long; Type: VIEW; Schema: NY; Owner: - |
297 | 297 |
-- |
298 | 298 |
|
299 |
CREATE VIEW _specimens_14_count_of_all_invalid_verbatim_lat_long AS |
|
299 |
CREATE OR REPLACE VIEW _specimens_14_count_of_all_invalid_verbatim_lat_long AS
|
|
300 | 300 |
SELECT ( SELECT count(*) AS count |
301 | 301 |
FROM "Ecatalog_all" |
302 | 302 |
WHERE ((("Ecatalog_all"."decimalLatitude")::double precision > (90)::double precision) OR (("Ecatalog_all"."decimalLatitude")::double precision < ((-90))::double precision))) AS "badLats", |
... | ... | |
318 | 318 |
-- Name: _specimens_15_list_distinct_locality_descriptions; Type: VIEW; Schema: NY; Owner: - |
319 | 319 |
-- |
320 | 320 |
|
321 |
CREATE VIEW _specimens_15_list_distinct_locality_descriptions AS |
|
321 |
CREATE OR REPLACE VIEW _specimens_15_list_distinct_locality_descriptions AS
|
|
322 | 322 |
SELECT DISTINCT "Ecatalog_all".locality AS "localityDescription" |
323 | 323 |
FROM "Ecatalog_all" |
324 | 324 |
WHERE ("Ecatalog_all".locality IS NOT NULL); |
... | ... | |
337 | 337 |
-- Name: _specimens_16_list_distinct_specimen_descriptions; Type: VIEW; Schema: NY; Owner: - |
338 | 338 |
-- |
339 | 339 |
|
340 |
CREATE VIEW _specimens_16_list_distinct_specimen_descriptions AS |
|
340 |
CREATE OR REPLACE VIEW _specimens_16_list_distinct_specimen_descriptions AS
|
|
341 | 341 |
SELECT DISTINCT "Ecatalog_all"."specimenDescription" |
342 | 342 |
FROM "Ecatalog_all" |
343 | 343 |
WHERE ("Ecatalog_all"."specimenDescription" IS NOT NULL); |
Also available in: Unified diff
lib/runscripts/validations.pg.sql.run: export_(): make the export idempotent for easier re-runnability