| 1 |
10811
|
aaronmk
|
SELECT util.search_path_append('util');
|
| 2 |
|
|
|
| 3 |
10943
|
aaronmk
|
SELECT create_if_not_exists($$ALTER TABLE $$||:table_str||$$ ADD PRIMARY KEY ("locationID")$$); -- runtime: 0.2 s ("Time: 238.474 ms") @starscream
|
| 4 |
10936
|
aaronmk
|
|
| 5 |
|
|
|
| 6 |
11487
|
aaronmk
|
-- remove institutions that we have direct data for
|
| 7 |
|
|
|
| 8 |
|
|
-- CVS
|
| 9 |
|
|
DELETE FROM :table
|
| 10 |
12758
|
aaronmk
|
WHERE "locationName" IN (SELECT "locationName" FROM "CVS".plot_)
|
| 11 |
11487
|
aaronmk
|
-- removes 2323 of 7079 CVS plots (75340 - 73017)
|
| 12 |
12776
|
aaronmk
|
; -- runtime: 1 min ("48999.044 ms") @starscream
|
| 13 |
11487
|
aaronmk
|
|
| 14 |
|
|
|
| 15 |
10936
|
aaronmk
|
-- map_*() derived cols
|
| 16 |
10937
|
aaronmk
|
-- runtime: 2 s @starscream
|
| 17 |
10936
|
aaronmk
|
|
| 18 |
|
|
CREATE OR REPLACE FUNCTION map_access_rights(value integer)
|
| 19 |
|
|
RETURNS text AS
|
| 20 |
|
|
$BODY$
|
| 21 |
|
|
/*
|
| 22 |
|
|
"closed List of Values:
|
| 23 |
|
|
Value Description Sorting
|
| 24 |
|
|
0 Public 1
|
| 25 |
|
|
1 1 km radius (nearest 0.01 degree) 2
|
| 26 |
|
|
2 10 km radius (nearest 0.1 degree) 3
|
| 27 |
|
|
3 100 km radius (nearest degree) 4
|
| 28 |
|
|
4 Location embargo 5
|
| 29 |
|
|
5 Public embargo on data 6
|
| 30 |
|
|
6 Full embargo on data 7
|
| 31 |
|
|
" (http://vegbank.org/vegbank/views/dba_fielddescription_detail.jsp?view=detail&wparam=1581&entity=dba_fielddescription)
|
| 32 |
|
|
*/
|
| 33 |
|
|
SELECT util._map('0=>public, 1=>public, 2=>public, 3=>public, 4=>public, 5=>"data by request", 6=>private', $1::text)
|
| 34 |
|
|
$BODY$
|
| 35 |
|
|
LANGUAGE sql IMMUTABLE
|
| 36 |
|
|
COST 100;
|
| 37 |
|
|
|
| 38 |
11488
|
aaronmk
|
SELECT mk_derived_col((:table_str, 'accessRights'), $$map_access_rights("accessRights_verbatim")$$); -- runtime: 1 s ("Time: 1167.547 ms") @starscream
|
| 39 |
10936
|
aaronmk
|
|
| 40 |
|
|
CREATE OR REPLACE FUNCTION map_fuzzing(value integer)
|
| 41 |
|
|
RETURNS double precision AS
|
| 42 |
|
|
$BODY$
|
| 43 |
|
|
/*
|
| 44 |
|
|
"closed List of Values:
|
| 45 |
|
|
Value Description Sorting
|
| 46 |
|
|
0 Public 1
|
| 47 |
|
|
1 1 km radius (nearest 0.01 degree) 2
|
| 48 |
|
|
2 10 km radius (nearest 0.1 degree) 3
|
| 49 |
|
|
3 100 km radius (nearest degree) 4
|
| 50 |
|
|
4 Location embargo 5
|
| 51 |
|
|
5 Public embargo on data 6
|
| 52 |
|
|
6 Full embargo on data 7
|
| 53 |
|
|
" (http://vegbank.org/vegbank/views/dba_fielddescription_detail.jsp?view=detail&wparam=1581&entity=dba_fielddescription)
|
| 54 |
|
|
*/
|
| 55 |
13357
|
aaronmk
|
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)
|
| 56 |
10936
|
aaronmk
|
$BODY$
|
| 57 |
|
|
LANGUAGE sql IMMUTABLE
|
| 58 |
|
|
COST 100;
|
| 59 |
|
|
|
| 60 |
11488
|
aaronmk
|
SELECT mk_derived_col((:table_str, 'coordinateUncertaintyInMeters__from_fuzzing'), $$map_fuzzing("accessRights_verbatim")$$); -- runtime: 1 s ("Time: 1064.189 ms") @starscream
|
| 61 |
10938
|
aaronmk
|
|
| 62 |
|
|
|
| 63 |
|
|
SELECT mk_derived_col((:table_str, 'coordinateUncertaintyInMeters'),
|
| 64 |
10943
|
aaronmk
|
$$GREATEST("coordinateUncertaintyInMeters__from_fuzzing", "coordinateUncertaintyInMeters__from_coords")$$)
|
| 65 |
11488
|
aaronmk
|
; -- runtime: 0.5 s ("Time: 586.453 ms") @starscream
|
| 66 |
11801
|
aaronmk
|
|
| 67 |
|
|
SELECT mk_derived_col((:table_str, 'locality'),
|
| 68 |
|
|
$$_join("directions_to_place", "site_name")$$)
|
| 69 |
|
|
; -- runtime: 4 s ("Time: 3651.355 ms") @starscream
|
