| 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
|
inputs/.geoscrub/geoscrub_output/postprocess.sql, run: updated runtimes