Project

General

Profile

« Previous | Next » 

Revision 10331

Added by Aaron Marcuse-Kubitza almost 11 years ago

bugfix: inputs/REMIB/Specimen/postprocess.sql: switched back to the input column names, since the renaming to *_verbatim is part of a later step

View differences:

postprocess.sql
2 2 

  		




  3
  3
  
    
SELECT create_if_not_exists($$CREATE INDEX "Specimen.acronym" ON $$||:table_str||$$ (acronym)$$);


  		




  4
  4
  
    

  		




  5
  
  
    
SELECT create_if_not_exists($$CREATE INDEX "Specimen.acronym"                ON $$||:table_str||$$ (acronym               )$$);


  		




  6
  
  
    
SELECT create_if_not_exists($$CREATE INDEX "Specimen.coll_year"              ON $$||:table_str||$$ (coll_year             )$$);


  		




  7
  
  
    
SELECT create_if_not_exists($$CREATE INDEX "Specimen.country_verbatim"       ON $$||:table_str||$$ (country_verbatim      )$$);


  		




  8
  
  
    
SELECT create_if_not_exists($$CREATE INDEX "Specimen.longitude_deg_verbatim" ON $$||:table_str||$$ (longitude_deg_verbatim)$$);


  		




  
  5
  
    
SELECT create_if_not_exists($$CREATE INDEX "Specimen.acronym"   ON $$||:table_str||$$ (acronym)$$);


  		




  
  6
  
    
SELECT create_if_not_exists($$CREATE INDEX "Specimen.coll_year" ON $$||:table_str||$$ (coll_year)$$);


  		




  
  7
  
    
SELECT create_if_not_exists($$CREATE INDEX "Specimen.country"   ON $$||:table_str||$$ (country)$$);


  		




  
  8
  
    
SELECT create_if_not_exists($$CREATE INDEX "Specimen.long_deg"  ON $$||:table_str||$$ (long_deg)$$);


  		




  9
  9
  
    

  		




  10
  10
  
    
-- remove frameshifted rows


  		




  11
  11
  
    
-- rerun time: 6 s ("Time: 5537.211 ms") thanks to index


  		




  ......




  13
  13
  
    
WHERE


  		




  14
  14
  
    
    acronym IS NULL


  		




  15
  15
  
    
OR  coll_year !~ E'^(?:1[7-9]|20)\\d{2}$'


  		




  16
  
  
    
OR  country_verbatim ~ E'\\d'


  		




  17
  
  
    
OR  (longitude_deg_verbatim ~ E'[[:alpha:]]' AND longitude_deg_verbatim NOT IN ('RESTRINGIDO'))


  		




  
  16
  
    
OR  country ~ E'\\d'


  		




  
  17
  
    
OR  (long_deg ~ E'[[:alpha:]]' AND long_deg NOT IN ('RESTRINGIDO'))


  		




  18
  18
  
    
;


  		




  19
  19
  
    

  		




  20
  20
  
    
-- Remove institutions that we have direct data for


  		












Also available in:  Unified diff