| 1 |
10604
|
aaronmk
|
SELECT util.search_path_append('util');
|
| 2 |
|
|
|
| 3 |
|
|
SELECT util.rename_cols(:table_str, $$
|
| 4 |
|
|
cultivated => *cultivated,
|
| 5 |
|
|
flower => *flower,
|
| 6 |
|
|
fruit => *fruit,
|
| 7 |
|
|
$$::hstore);
|
| 8 |
|
|
|
| 9 |
10614
|
aaronmk
|
SELECT create_if_not_exists($$CREATE INDEX "Specimen.infrarank" ON $$||:table_str||$$ ("infrarank")$$); -- runtime: 0.1 s ("Time: 135.984 ms") @starscream
|
| 10 |
10604
|
aaronmk
|
|
| 11 |
10614
|
aaronmk
|
-- remove mismapped values
|
| 12 |
|
|
UPDATE :table SET infrarank = NULL WHERE infrarank = 'L.'; -- runtime: ~0 ("Time: 2.370 ms") @starscream
|
| 13 |
|
|
|
| 14 |
|
|
|
| 15 |
10604
|
aaronmk
|
SELECT mk_derived_col((:table_str, 'specimenDonorInstitution'),
|
| 16 |
|
|
-- UNCCD likely = UNCC database
|
| 17 |
|
|
$$_map('UNCCD=>UNCC, *=>*', "herbarium")$$)
|
| 18 |
|
|
; -- runtime: 1 s ("Time: 1057.222 ms") @starscream
|
| 19 |
|
|
|
| 20 |
10618
|
aaronmk
|
SELECT mk_derived_col((:table_str, 'recordedBy'),
|
| 21 |
|
|
$$_join("collector", "collector1", "collector2", "collector3")$$)
|
| 22 |
|
|
; -- runtime: 1 s ("Time: 1001.616 ms") @starscream
|
| 23 |
|
|
|
| 24 |
|
|
SELECT mk_derived_col((:table_str, 'occurrenceRemarks'),
|
| 25 |
|
|
$$_join("comment1", "comment2", "comment3", "comment4")$$)
|
| 26 |
|
|
; -- runtime: 1 s ("Time: 996.729 ms") @starscream
|
| 27 |
|
|
|
| 28 |
|
|
|
| 29 |
10604
|
aaronmk
|
SELECT mk_derived_col((:table_str, 'cultivated__campus'),
|
| 30 |
|
|
/* "this refers to occurrence on the UNCC campus, but I do not know the meaning
|
| 31 |
|
|
of the codes. Presumably C = cultivated"
|
| 32 |
|
|
(https://projects.nceas.ucsb.edu/nceas/attachments/download/723/UNCC-validate_rkp_bb_rkp.xlsx#Campus)
|
| 33 |
|
|
*/
|
| 34 |
|
|
$$_map('*=>true', "campus")$$)
|
| 35 |
|
|
; -- runtime: 0.5 s ("Time: 353.373 ms") @starscream
|
| 36 |
|
|
|
| 37 |
|
|
SELECT mk_derived_col((:table_str, 'cultivated__main'),
|
| 38 |
|
|
-- for our purposes, anything non-NULL should be assumed to be cultivated
|
| 39 |
|
|
$$_map('*=>true', "*cultivated")$$)
|
| 40 |
|
|
; -- runtime: 0.5 s ("Time: 403.916 ms") @starscream
|
| 41 |
|
|
|
| 42 |
10618
|
aaronmk
|
SELECT mk_derived_col((:table_str, 'cultivated'),
|
| 43 |
|
|
$$_alt("cultivated__main", "cultivated__campus")$$)
|
| 44 |
|
|
; -- runtime: 0.5 s ("Time: 394.182 ms") @starscream
|
| 45 |
10604
|
aaronmk
|
|
| 46 |
10618
|
aaronmk
|
|
| 47 |
10604
|
aaronmk
|
CREATE OR REPLACE FUNCTION map_reproductive_condition(value text)
|
| 48 |
|
|
RETURNS text AS
|
| 49 |
|
|
$BODY$
|
| 50 |
|
|
/*
|
| 51 |
|
|
"
|
| 52 |
|
|
Most are typos
|
| 53 |
|
|
|
| 54 |
|
|
A = absent
|
| 55 |
|
|
|
| 56 |
|
|
I = immature
|
| 57 |
|
|
|
| 58 |
|
|
M = mature
|
| 59 |
|
|
" (mailto:jim@habitatassessment.com?Habitat_Assessment.Jim_Matthews.to(uniola@email.unc.edu?Bob_Peet).2010-12-3:17:08.Re:_herbarium_phenology_codes_(fwd))
|
| 60 |
|
|
*/
|
| 61 |
|
|
SELECT util._map('A=>absent, I=>immature, M=>mature, *=>NULL', $1)
|
| 62 |
|
|
$BODY$
|
| 63 |
|
|
LANGUAGE sql IMMUTABLE
|
| 64 |
|
|
COST 100;
|
| 65 |
|
|
|
| 66 |
|
|
SELECT mk_derived_col((:table_str, 'flower'), $$map_reproductive_condition("*flower")$$); -- runtime: 0.5 s ("Time: 635.054 ms") @starscream
|
| 67 |
|
|
SELECT mk_derived_col((:table_str, 'fruit' ), $$map_reproductive_condition("*fruit" )$$); -- runtime: 0.5 s ("Time: 591.854 ms") @starscream
|
| 68 |
10618
|
aaronmk
|
|
| 69 |
|
|
SELECT mk_derived_col((:table_str, 'reproductiveCondition'),
|
| 70 |
|
|
-- using the suggested values at DwC.vegpath.org?reproductiveCondition
|
| 71 |
|
|
$$_join(
|
| 72 |
|
|
_map(('{immature,mature}'::text[] => 'in bloom' ) || '*=>NULL', "flower")
|
| 73 |
|
|
, _map(('{immature,mature}'::text[] => 'fruit-bearing') || '*=>NULL', "fruit" )
|
| 74 |
|
|
)$$)
|
| 75 |
|
|
; -- runtime: 1.5 s ("Time: 1433.204 ms") @starscream
|
