Revision 11729
Added by Aaron Marcuse-Kubitza about 11 years ago
| postprocess.sql | ||
|---|---|---|
| 1 | 1 |
SELECT util.search_path_append('util');
|
| 2 | 2 |
|
| 3 | 3 |
SELECT create_if_not_exists($$ALTER TABLE $$||:table_str||$$ ADD PRIMARY KEY ("PLOT_ID")$$); -- runtime: ~0 ("78.941 ms") @starscream
|
| 4 |
|
|
| 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 |
SELECT mk_derived_col((:table_str, 'accessRights'), $$map_access_rights("confidentialityStatus")$$); -- runtime: 0.5 s ("292.078 ms") @starscream
|
|
| 31 |
|
|
| 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 |
SELECT mk_derived_col((:table_str, 'coordinateUncertaintyInMeters__from_fuzzing'), $$map_fuzzing("confidentialityStatus")$$); -- runtime: 0.2 s ("199.359 ms") @starscream
|
|
| 54 |
|
|
| 55 |
|
|
| 56 |
SELECT mk_derived_col((:table_str, 'coordinateUncertaintyInMeters'), |
|
| 57 |
$$GREATEST("coordinateUncertaintyInMeters__from_fuzzing", "locationAccuracy")$$)
|
|
| 58 |
; -- runtime: 0.2 s ("172.425 ms") @starscream
|
|
Also available in: Unified diff
inputs/CVS/plot_/: translated column filters to postprocessing derived columns, using the steps at http://wiki.vegpath.org/Adding_new-style_import_to_a_datasource#1-Translate-filters-to-postprocessing-derived-columns