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