Project

General

Profile

« Previous | Next » 

Revision 10619

Added by Aaron Marcuse-Kubitza about 11 years ago

inputs/UNCC/: switched to new-style import, using the steps at http://wiki.vegpath.org/Adding_new-style_import_to_a_datasource

View differences:

postprocess.sql
1 1 
SELECT util.search_path_append('util');
2 2 

  		




  3
  
  
    
SELECT util.rename_cols(:table_str, $$


  		




  4
  
  
    
cultivated => *cultivated,


  		




  5
  
  
    
flower     => *flower,


  		




  6
  
  
    
fruit      => *fruit,


  		




  7
  
  
    
$$::hstore);


  		




  
  3
  
    
SELECT create_if_not_exists($$CREATE INDEX "Specimen.infrarank" ON $$||:table_str||$$ ("EQUIV#to:usdaRank#infrarank")$$); -- runtime: 0.1 s ("Time: 135.984 ms") @starscream


  		




  8
  4
  
    

  		




  9
  
  
    
SELECT create_if_not_exists($$CREATE INDEX "Specimen.infrarank" ON $$||:table_str||$$ ("infrarank")$$); -- runtime: 0.1 s ("Time: 135.984 ms") @starscream


  		




  10
  
  
    

  		




  11
  5
  
    
-- remove mismapped values


  		




  12
  
  
    
UPDATE :table SET infrarank = NULL WHERE infrarank = 'L.'; -- runtime: ~0 ("Time: 2.370 ms") @starscream


  		




  
  6
  
    
UPDATE :table SET "EQUIV#to:usdaRank#infrarank" = NULL WHERE "EQUIV#to:usdaRank#infrarank" = 'L.'; -- runtime: ~0 ("Time: 2.370 ms") @starscream


  		




  13
  7
  
    

  		




  14
  8
  
    

  		




  15
  9
  
    
SELECT mk_derived_col((:table_str, 'specimenDonorInstitution'),


  		




  16
  10
  
    
-- UNCCD likely = UNCC database


  		




  17
  
  
    
$$_map('UNCCD=>UNCC, *=>*', "herbarium")$$)


  		




  
  11
  
    
$$_map('UNCCD=>UNCC, *=>*', "specimenDonorInstitution_verbatim")$$)


  		




  18
  12
  
    
; -- runtime: 1 s ("Time: 1057.222 ms") @starscream


  		




  19
  13
  
    

  		




  20
  14
  
    
SELECT mk_derived_col((:table_str, 'recordedBy'),


  		




  21
  
  
    
$$_join("collector", "collector1", "collector2", "collector3")$$)


  		




  
  15
  
    
$$_join("recordedBy__primary", "recordedBy__additional__1", "recordedBy__additional__2", "recordedBy__additional__3")$$)


  		




  22
  16
  
    
; -- runtime: 1 s ("Time: 1001.616 ms") @starscream


  		




  23
  17
  
    

  		




  24
  18
  
    
SELECT mk_derived_col((:table_str, 'occurrenceRemarks'),


  		




  25
  
  
    
$$_join("comment1", "comment2", "comment3", "comment4")$$)


  		




  
  19
  
    
$$_join("occurrenceRemarks__1", "occurrenceRemarks__2", "occurrenceRemarks__3", "occurrenceRemarks__4")$$)


  		




  26
  20
  
    
; -- runtime: 1 s ("Time: 996.729 ms") @starscream


  		




  27
  21
  
    

  		




  28
  22
  
    

  		




  ......




  31
  25
  
    
of the codes. Presumably C = cultivated"


  		




  32
  26
  
    
(https://projects.nceas.ucsb.edu/nceas/attachments/download/723/UNCC-validate_rkp_bb_rkp.xlsx#Campus)


  		




  33
  27
  
    
*/


  		




  34
  
  
    
$$_map('*=>true', "campus")$$)


  		




  
  28
  
    
$$_map('*=>true', "cultivated__campus__verbatim")$$)


  		




  35
  29
  
    
; -- runtime: 0.5 s ("Time: 353.373 ms") @starscream


  		




  36
  30
  
    

  		




  37
  31
  
    
SELECT mk_derived_col((:table_str, 'cultivated__main'),


  		




  38
  32
  
    
-- for our purposes, anything non-NULL should be assumed to be cultivated


  		




  39
  
  
    
$$_map('*=>true', "*cultivated")$$)


  		




  
  33
  
    
$$_map('*=>true', "cultivated__main__verbatim")$$)


  		




  40
  34
  
    
; -- runtime: 0.5 s ("Time: 403.916 ms") @starscream


  		




  41
  35
  
    

  		




  42
  36
  
    
SELECT mk_derived_col((:table_str, 'cultivated'),


  		




  ......




  63
  57
  
    
  LANGUAGE sql IMMUTABLE


  		




  64
  58
  
    
  COST 100;


  		




  65
  59
  
    

  		




  66
  
  
    
SELECT mk_derived_col((:table_str, 'flower'), $$map_reproductive_condition("*flower")$$); -- runtime: 0.5 s ("Time: 635.054 ms") @starscream


  		




  67
  
  
    
SELECT mk_derived_col((:table_str, 'fruit' ), $$map_reproductive_condition("*fruit" )$$); -- runtime: 0.5 s ("Time: 591.854 ms") @starscream


  		




  
  60
  
    
SELECT mk_derived_col((:table_str, 'flower'), $$map_reproductive_condition("flower_verbatim")$$); -- runtime: 0.5 s ("Time: 635.054 ms") @starscream


  		




  
  61
  
    
SELECT mk_derived_col((:table_str, 'fruit' ), $$map_reproductive_condition("fruit_verbatim" )$$); -- runtime: 0.5 s ("Time: 591.854 ms") @starscream


  		




  68
  62
  
    

  		




  69
  63
  
    
SELECT mk_derived_col((:table_str, 'reproductiveCondition'),


  		




  70
  64
  
    
-- using the suggested values at DwC.vegpath.org?reproductiveCondition


  		












Also available in:  Unified diff