SELECT util.search_path_append('util');

SELECT create_if_not_exists($$ALTER TABLE $$||:table_str||$$ ADD PRIMARY KEY ("locationID")$$); -- runtime: 0.2 s ("Time: 238.474 ms") @starscream


-- remove institutions that we have direct data for

-- CVS
DELETE FROM :table
WHERE "locationName" IN (SELECT "locationName" FROM "CVS".plot_)
-- removes 2323 of 7079 CVS plots (75340 - 73017)
; -- runtime: 1 min ("48999.044 ms") @starscream


-- map_*() derived cols
-- runtime: 2 s @starscream

CREATE OR REPLACE FUNCTION map_access_rights(value integer)
  RETURNS text AS
$BODY$
/*
"closed List of Values:
Value  Description                        Sorting
0      Public                             1
1      1 km radius (nearest 0.01 degree)  2
2      10 km radius (nearest 0.1 degree)  3
3      100 km radius (nearest degree)     4
4      Location embargo                   5
5      Public embargo on data             6
6      Full embargo on data               7
" (http://vegbank.org/vegbank/views/dba_fielddescription_detail.jsp?view=detail&wparam=1581&entity=dba_fielddescription)
*/
SELECT util._map('0=>public, 1=>public, 2=>public, 3=>public, 4=>public, 5=>"data by request", 6=>private', $1::text)
$BODY$
  LANGUAGE sql IMMUTABLE
  COST 100;

SELECT mk_derived_col((:table_str, 'accessRights'), $$map_access_rights("accessRights_verbatim")$$); -- runtime: 1 s ("Time: 1167.547 ms") @starscream

CREATE OR REPLACE FUNCTION map_fuzzing(value integer)
  RETURNS double precision AS
$BODY$
/*
"closed List of Values:
Value  Description                        Sorting
0      Public                             1
1      1 km radius (nearest 0.01 degree)  2
2      10 km radius (nearest 0.1 degree)  3
3      100 km radius (nearest degree)     4
4      Location embargo                   5
5      Public embargo on data             6
6      Full embargo on data               7
" (http://vegbank.org/vegbank/views/dba_fielddescription_detail.jsp?view=detail&wparam=1581&entity=dba_fielddescription)
*/
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)
$BODY$
  LANGUAGE sql IMMUTABLE
  COST 100;

SELECT mk_derived_col((:table_str, 'coordinateUncertaintyInMeters__from_fuzzing'), $$map_fuzzing("accessRights_verbatim")$$); -- runtime: 1 s ("Time: 1064.189 ms") @starscream


SELECT mk_derived_col((:table_str, 'coordinateUncertaintyInMeters'),
$$GREATEST("coordinateUncertaintyInMeters__from_fuzzing", "coordinateUncertaintyInMeters__from_coords")$$)
; -- runtime: 0.5 s ("Time: 586.453 ms") @starscream

SELECT mk_derived_col((:table_str, 'locality'),
$$_join("directions_to_place", "site_name")$$)
; -- runtime: 4 s ("Time: 3651.355 ms") @starscream