|
1
|
SELECT util.search_path_append('util');
|
|
2
|
|
|
3
|
-- runtime: 1 min ("67374.447 ms") @starscream
|
|
4
|
SELECT set_col_types(:table_str, ARRAY[
|
|
5
|
('decimalLatitude' , 'double precision')
|
|
6
|
, ('decimalLongitude' , 'double precision')
|
|
7
|
, ('latLongDomainValid_verbatim' , 'integer')
|
|
8
|
, ('latLongInCountry_verbatim' , 'integer')
|
|
9
|
, ('latLongInStateProvince_verbatim', 'integer')
|
|
10
|
, ('latLongInCounty_verbatim' , 'integer')
|
|
11
|
]::col_cast[]);
|
|
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
|
|
15
|
|
|
16
|
-- runtime: 1 min ("49253.507 ms")
|
|
17
|
SELECT create_if_not_exists($$CREATE INDEX geoscrub_output_input ON geoscrub_output ("decimalLatitude", "decimalLongitude", country, "stateProvince", county)$$);
|
|
18
|
|
|
19
|
|
|
20
|
-- map_*() derived cols
|
|
21
|
-- runtime: 7 min @starscream
|
|
22
|
|
|
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
|
SELECT mk_derived_col((:table_str, 'latLongDomainValid'), $$map_coord_validity("latLongDomainValid_verbatim")$$); -- runtime: 2 min ("Time: 97649.394 ms") @starscream
|
|
39
|
|
|
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
|
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
|
