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
|