Project

General

Profile

« Previous | Next » 

Revision 11369

Added by Aaron Marcuse-Kubitza about 11 years ago

inputs/.geoscrub/geoscrub_output/postprocess.sql, run: updated runtimes

View differences:

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

  		




  3
  
  
    
-- runtime: 1 min ("67374.447 ms") @starscream


  		




  
  3
  
    
-- runtime: 30 s ("36510.730 ms") @starscream


  		




  4
  4
  
    
SELECT set_col_types(:table_str, ARRAY[


  		




  5
  5
  
    
  ('decimalLatitude'                , 'double precision')


  		




  6
  6
  
    
, ('decimalLongitude'               , 'double precision')


  		




  ......




  10
  10
  
    
, ('latLongInCounty_verbatim'       , 'integer')


  		




  11
  11
  
    
]::col_cast[]);


  		




  12
  12
  
    

  		




  13
  
  
    
ALTER TABLE :table ALTER COLUMN "decimalLatitude"  SET NOT NULL; -- runtime: 10 s ("11476.920") @starscream


  		




  14
  
  
    
ALTER TABLE :table ALTER COLUMN "decimalLongitude" SET NOT NULL; -- runtime: 2 s ("1536.942") @starscream


  		




  
  13
  
    
ALTER TABLE :table ALTER COLUMN "decimalLatitude"  SET NOT NULL; -- runtime: 5 s ("5248.843") @starscream


  		




  
  14
  
    
ALTER TABLE :table ALTER COLUMN "decimalLongitude" SET NOT NULL; -- runtime: 1 s ("1064.650") @starscream


  		




  15
  15
  
    

  		




  16
  
  
    
-- runtime: 1 min ("49253.507 ms")


  		




  
  16
  
    
-- runtime: 30 s ("29089.750 ms") @starscream


  		




  17
  17
  
    
SELECT create_if_not_exists($$CREATE INDEX geoscrub_output_input ON geoscrub_output ("decimalLatitude", "decimalLongitude", country, "stateProvince", county)$$);


  		




  18
  18
  
    

  		




  19
  19
  
    

  		




  20
  20
  
    
-- map_*() derived cols


  		




  21
  
  
    
-- runtime: 7 min @starscream


  		




  
  21
  
    
-- runtime: 6 min @starscream


  		




  22
  22
  
    

  		




  23
  23
  
    
CREATE OR REPLACE FUNCTION map_coord_validity(value integer)


  		




  24
  24
  
    
  RETURNS boolean AS


  		




  ......




  35
  35
  
    
  LANGUAGE sql IMMUTABLE


  		




  36
  36
  
    
  COST 100;


  		




  37
  37
  
    

  		




  38
  
  
    
SELECT mk_derived_col((:table_str, 'latLongDomainValid'), $$map_coord_validity("latLongDomainValid_verbatim")$$); -- runtime: 2 min ("Time: 97649.394 ms") @starscream


  		




  
  38
  
    
SELECT mk_derived_col((:table_str, 'latLongDomainValid'), $$map_coord_validity("latLongDomainValid_verbatim")$$); -- runtime: 1.5 min ("Time: 92913.814 ms") @starscream


  		




  39
  39
  
    

  		




  40
  40
  
    
CREATE OR REPLACE FUNCTION map_geovalidity(value integer)


  		




  41
  41
  
    
  RETURNS boolean AS


  		




  ......




  54
  54
  
    
  LANGUAGE sql IMMUTABLE


  		




  55
  55
  
    
  COST 100;


  		




  56
  56
  
    

  		




  57
  
  
    
SELECT mk_derived_col((:table_str, 'latLongInCountry'      ), $$map_geovalidity("latLongInCountry_verbatim"      )$$); -- runtime: 2 min ("Time: 120695.117 ms") @starscream


  		




  58
  
  
    
SELECT mk_derived_col((:table_str, 'latLongInStateProvince'), $$map_geovalidity("latLongInStateProvince_verbatim")$$); -- runtime: 2 min ("Time: 111719.964 ms") @starscream


  		




  59
  
  
    
SELECT mk_derived_col((:table_str, 'latLongInCounty'       ), $$map_geovalidity("latLongInCounty_verbatim"       )$$); -- runtime: 2 min ("Time: 99485.538 ms") @starscream


  		




  
  57
  
    
SELECT mk_derived_col((:table_str, 'latLongInCountry'      ), $$map_geovalidity("latLongInCountry_verbatim"      )$$); -- runtime: 1.5 min ("Time: 95967.016 ms") @starscream


  		




  
  58
  
    
SELECT mk_derived_col((:table_str, 'latLongInStateProvince'), $$map_geovalidity("latLongInStateProvince_verbatim")$$); -- runtime: 1.5 min ("Time: 95003.576 ms") @starscream


  		




  
  59
  
    
SELECT mk_derived_col((:table_str, 'latLongInCounty'       ), $$map_geovalidity("latLongInCounty_verbatim"       )$$); -- runtime: 1.5 min ("Time: 94405.944 ms") @starscream


  		












Also available in:  Unified diff