Project

General

Profile

« Previous | Next » 

Revision 11367

Added by Aaron Marcuse-Kubitza over 10 years ago

inputs/.geoscrub/geoscrub_output/postprocess.sql: updated runtimes for refreshed data, which now has 4x as many rows (1,707,970->6,747,650)

View differences:

inputs/.geoscrub/geoscrub_output/postprocess.sql
1 1 
SELECT util.search_path_append('util');
2 2 

  		




  3
  
  
    
-- runtime: 40 s ("real 0m21.770s" + "Time: 17304.769 ms") @starscream


  		




  
  3
  
    
-- runtime: 1 min ("67374.447 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;


  		




  14
  
  
    
ALTER TABLE :table ALTER COLUMN "decimalLongitude" SET NOT NULL;


  		




  
  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


  		




  15
  15
  
    

  		




  16
  
  
    
-- runtime: 55 s ("real 0m53.677s")


  		




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


  		




  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: 2 min @starscream


  		




  
  21
  
    
-- runtime: 7 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: 25 s ("Time: 26708.932 ms") @starscream


  		




  
  38
  
    
SELECT mk_derived_col((:table_str, 'latLongDomainValid'), $$map_coord_validity("latLongDomainValid_verbatim")$$); -- runtime: 2 min ("Time: 97649.394 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: 25 s ("Time: 27884.129 ms") @starscream


  		




  58
  
  
    
SELECT mk_derived_col((:table_str, 'latLongInStateProvince'), $$map_geovalidity("latLongInStateProvince_verbatim")$$); -- runtime: 25 s ("Time: 27228.239 ms") @starscream


  		




  59
  
  
    
SELECT mk_derived_col((:table_str, 'latLongInCounty'       ), $$map_geovalidity("latLongInCounty_verbatim"       )$$); -- runtime: 25 s ("Time: 27337.520 ms") @starscream


  		




  
  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


  		












Also available in:  Unified diff