Project

General

Profile

« Previous | Next » 

Revision 10936

Added by Aaron Marcuse-Kubitza about 11 years ago

inputs/VegBank/plot_/: translated single-column filters to postprocessing derived columns, using the steps at http://wiki.vegpath.org/Adding_new-style_import_to_a_datasource#Translating-filters-to-postprocessing-derived-columns

View differences:

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.2 s ("Time: 238.474 ms") @starscream


  		




  
  4
  
    

  		




  
  5
  
    

  		




  
  6
  
    
-- map_*() derived cols


  		




  
  7
  
    
-- runtime: 2 min @starscream


  		




  
  8
  
    

  		




  
  9
  
    
CREATE OR REPLACE FUNCTION map_access_rights(value integer)


  		




  
  10
  
    
  RETURNS text AS


  		




  
  11
  
    
$BODY$


  		




  
  12
  
    
/*


  		




  
  13
  
    
"closed List of Values:


  		




  
  14
  
    
Value  Description                        Sorting


  		




  
  15
  
    
0      Public                             1


  		




  
  16
  
    
1      1 km radius (nearest 0.01 degree)  2


  		




  
  17
  
    
2      10 km radius (nearest 0.1 degree)  3


  		




  
  18
  
    
3      100 km radius (nearest degree)     4


  		




  
  19
  
    
4      Location embargo                   5


  		




  
  20
  
    
5      Public embargo on data             6


  		




  
  21
  
    
6      Full embargo on data               7


  		




  
  22
  
    
" (http://vegbank.org/vegbank/views/dba_fielddescription_detail.jsp?view=detail&wparam=1581&entity=dba_fielddescription)


  		




  
  23
  
    
*/


  		




  
  24
  
    
SELECT util._map('0=>public, 1=>public, 2=>public, 3=>public, 4=>public, 5=>"data by request", 6=>private', $1::text)


  		




  
  25
  
    
$BODY$


  		




  
  26
  
    
  LANGUAGE sql IMMUTABLE


  		




  
  27
  
    
  COST 100;


  		




  
  28
  
    

  		




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


  		




  
  30
  
    

  		




  
  31
  
    
CREATE OR REPLACE FUNCTION map_fuzzing(value integer)


  		




  
  32
  
    
  RETURNS double precision AS


  		




  
  33
  
    
$BODY$


  		




  
  34
  
    
/*


  		




  
  35
  
    
"closed List of Values:


  		




  
  36
  
    
Value  Description                        Sorting


  		




  
  37
  
    
0      Public                             1


  		




  
  38
  
    
1      1 km radius (nearest 0.01 degree)  2


  		




  
  39
  
    
2      10 km radius (nearest 0.1 degree)  3


  		




  
  40
  
    
3      100 km radius (nearest degree)     4


  		




  
  41
  
    
4      Location embargo                   5


  		




  
  42
  
    
5      Public embargo on data             6


  		




  
  43
  
    
6      Full embargo on data               7


  		




  
  44
  
    
" (http://vegbank.org/vegbank/views/dba_fielddescription_detail.jsp?view=detail&wparam=1581&entity=dba_fielddescription)


  		




  
  45
  
    
*/


  		




  
  46
  
    
SELECT util._map('0=>NULL, 1=>1, 2=>10, 3=>100, 4=>NULL, 5=>NULL, 6=>NULL', $1::text)::double precision


  		




  
  47
  
    
$BODY$


  		




  
  48
  
    
  LANGUAGE sql IMMUTABLE


  		




  
  49
  
    
  COST 100;


  		




  
  50
  
    

  		




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


  		












Also available in:  Unified diff