Project

General

Profile

1 9920 aaronmk
SELECT util.search_path_append('util');
2
3 11367 aaronmk
-- runtime: 1 min ("67374.447 ms") @starscream
4 9921 aaronmk
SELECT set_col_types(:table_str, ARRAY[
5 10390 aaronmk
  ('decimalLatitude'                , 'double precision')
6
, ('decimalLongitude'               , 'double precision')
7
, ('latLongDomainValid_verbatim'    , 'integer')
8
, ('latLongInCountry_verbatim'      , 'integer')
9
, ('latLongInStateProvince_verbatim', 'integer')
10
, ('latLongInCounty_verbatim'       , 'integer')
11 9921 aaronmk
]::col_cast[]);
12
13 11367 aaronmk
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 6658 aaronmk
16 11367 aaronmk
-- runtime: 1 min ("49253.507 ms")
17 10390 aaronmk
SELECT create_if_not_exists($$CREATE INDEX geoscrub_output_input ON geoscrub_output ("decimalLatitude", "decimalLongitude", country, "stateProvince", county)$$);
18 10389 aaronmk
19
20
-- map_*() derived cols
21 11367 aaronmk
-- runtime: 7 min @starscream
22 10389 aaronmk
23
CREATE OR REPLACE FUNCTION map_coord_validity(value integer)
24
  RETURNS boolean AS
25
$BODY$
26
/*
27
"For latlonvalidity:
28
-1: Latitude and/or longitude is null
29
 0: Coordinate is not a valid geographic location
30
 1: Coordinate is a valid geographic location
31
" (http://vegpath.org/fs/inputs/.geoscrub/_src/README.TXT)
32
*/
33
SELECT util._map('-1=>NULL, 0=>false, 1=>true', $1::text)::boolean
34
$BODY$
35
  LANGUAGE sql IMMUTABLE
36
  COST 100;
37
38 11367 aaronmk
SELECT mk_derived_col((:table_str, 'latLongDomainValid'), $$map_coord_validity("latLongDomainValid_verbatim")$$); -- runtime: 2 min ("Time: 97649.394 ms") @starscream
39 10389 aaronmk
40
CREATE OR REPLACE FUNCTION map_geovalidity(value integer)
41
  RETURNS boolean AS
42
$BODY$
43
/*
44
"For countryvalidity/stateprovincevalidity/countyvalidity:
45
-1: Name is null at this or some higher level
46
 0: Complete name provided, but couldn't be scrubbed to GADM
47
 1: Point is >5km from putative GADM polygon
48
 2: Point is <=5km from putative GADM polygon, but still outside it
49
 3: Point is in (or on border of) putative GADM polygon
50
" (http://vegpath.org/fs/inputs/.geoscrub/_src/README.TXT)
51
*/
52
SELECT util._map('-1=>NULL, 0=>NULL, 1=>false, 2=>true, 3=>true', $1::text)::boolean
53
$BODY$
54
  LANGUAGE sql IMMUTABLE
55
  COST 100;
56
57 11367 aaronmk
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