|
1
|
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
|
SELECT create_if_not_exists($$CREATE INDEX "Specimen.infrarank" ON $$||:table_str||$$ ("infrarank")$$); -- runtime: 0.1 s ("Time: 135.984 ms") @starscream
|
|
10
|
|
|
11
|
-- remove mismapped values
|
|
12
|
UPDATE :table SET infrarank = NULL WHERE infrarank = 'L.'; -- runtime: ~0 ("Time: 2.370 ms") @starscream
|
|
13
|
|
|
14
|
|
|
15
|
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
|
SELECT mk_derived_col((:table_str, 'cultivated__campus'),
|
|
21
|
/* "this refers to occurrence on the UNCC campus, but I do not know the meaning
|
|
22
|
of the codes. Presumably C = cultivated"
|
|
23
|
(https://projects.nceas.ucsb.edu/nceas/attachments/download/723/UNCC-validate_rkp_bb_rkp.xlsx#Campus)
|
|
24
|
*/
|
|
25
|
$$_map('*=>true', "campus")$$)
|
|
26
|
; -- runtime: 0.5 s ("Time: 353.373 ms") @starscream
|
|
27
|
|
|
28
|
SELECT mk_derived_col((:table_str, 'cultivated__main'),
|
|
29
|
-- for our purposes, anything non-NULL should be assumed to be cultivated
|
|
30
|
$$_map('*=>true', "*cultivated")$$)
|
|
31
|
; -- runtime: 0.5 s ("Time: 403.916 ms") @starscream
|
|
32
|
|
|
33
|
|
|
34
|
CREATE OR REPLACE FUNCTION map_reproductive_condition(value text)
|
|
35
|
RETURNS text AS
|
|
36
|
$BODY$
|
|
37
|
/*
|
|
38
|
"
|
|
39
|
Most are typos
|
|
40
|
|
|
41
|
A = absent
|
|
42
|
|
|
43
|
I = immature
|
|
44
|
|
|
45
|
M = mature
|
|
46
|
" (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))
|
|
47
|
*/
|
|
48
|
SELECT util._map('A=>absent, I=>immature, M=>mature, *=>NULL', $1)
|
|
49
|
$BODY$
|
|
50
|
LANGUAGE sql IMMUTABLE
|
|
51
|
COST 100;
|
|
52
|
|
|
53
|
SELECT mk_derived_col((:table_str, 'flower'), $$map_reproductive_condition("*flower")$$); -- runtime: 0.5 s ("Time: 635.054 ms") @starscream
|
|
54
|
SELECT mk_derived_col((:table_str, 'fruit' ), $$map_reproductive_condition("*fruit" )$$); -- runtime: 0.5 s ("Time: 591.854 ms") @starscream
|
