Project

General

Profile

1 11727 aaronmk
SELECT util.search_path_append('util');
2
3 11749 aaronmk
SELECT create_if_not_exists($$ALTER TABLE $$||:table_str||$$ ADD PRIMARY KEY ("locationID")$$); -- runtime: ~0 ("78.941 ms") @starscream
4 11729 aaronmk
5
6
-- map_*() derived cols
7
-- runtime: 1 s @starscream
8
9
CREATE OR REPLACE FUNCTION map_access_rights(value integer)
10
  RETURNS text AS
11
$BODY$
12
/*
13
assuming values are the same as for VegBank:
14
"closed List of Values:
15
Value  Description                        Sorting
16
0      Public                             1
17
1      1 km radius (nearest 0.01 degree)  2
18
2      10 km radius (nearest 0.1 degree)  3
19
3      100 km radius (nearest degree)     4
20
4      Location embargo                   5
21
5      Public embargo on data             6
22
6      Full embargo on data               7
23
" (http://vegbank.org/vegbank/views/dba_fielddescription_detail.jsp?view=detail&wparam=1581&entity=dba_fielddescription)
24
*/
25
SELECT util._map('0=>public, 1=>public, 2=>public, 3=>public, 4=>public, 5=>"data by request", 6=>private', $1::text)
26
$BODY$
27
  LANGUAGE sql IMMUTABLE
28
  COST 100;
29
30 11749 aaronmk
SELECT mk_derived_col((:table_str, 'accessRights'), $$map_access_rights("accessRights_verbatim")$$); -- runtime: 0.5 s ("292.078 ms") @starscream
31 11729 aaronmk
32
CREATE OR REPLACE FUNCTION map_fuzzing(value integer)
33
  RETURNS double precision AS
34
$BODY$
35
/*
36
assuming values are the same as for VegBank:
37
"closed List of Values:
38
Value  Description                        Sorting
39
0      Public                             1
40
1      1 km radius (nearest 0.01 degree)  2
41
2      10 km radius (nearest 0.1 degree)  3
42
3      100 km radius (nearest degree)     4
43
4      Location embargo                   5
44
5      Public embargo on data             6
45
6      Full embargo on data               7
46
" (http://vegbank.org/vegbank/views/dba_fielddescription_detail.jsp?view=detail&wparam=1581&entity=dba_fielddescription)
47
*/
48
SELECT public._km_to_m(util._map('0=>NULL, 1=>1, 2=>10, 3=>100, 4=>NULL, 5=>NULL, 6=>NULL', $1::text)::double precision)
49
$BODY$
50
  LANGUAGE sql IMMUTABLE
51
  COST 100;
52
53 11749 aaronmk
SELECT mk_derived_col((:table_str, 'coordinateUncertaintyInMeters__from_fuzzing'), $$map_fuzzing("accessRights_verbatim")$$); -- runtime: 0.2 s ("199.359 ms") @starscream
54 11729 aaronmk
55
56
SELECT mk_derived_col((:table_str, 'coordinateUncertaintyInMeters'),
57 11749 aaronmk
$$GREATEST("coordinateUncertaintyInMeters__from_fuzzing", "coordinateUncertaintyInMeters__from_coords")$$)
58 11729 aaronmk
; -- runtime: 0.2 s ("172.425 ms") @starscream
59 11804 aaronmk
60
SELECT mk_derived_col((:table_str, 'locality'),
61
$$_join("directions_to_place", "site_name")$$)
62
; -- runtime: 4 s ("Time: 3651.355 ms") @starscream