Project

General

Profile

1 9920 aaronmk
SELECT util.search_path_append('util');
2
3 11369 aaronmk
-- runtime: 30 s ("36510.730 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 11369 aaronmk
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 6658 aaronmk
16 11388 aaronmk
SELECT create_if_not_exists($$CREATE UNIQUE INDEX $$||:table_str||%'__unique'||$$ ON $$||:table_str||$$ (
17
(array["decimalLatitude"]), (array["decimalLongitude"]), (array[country]), (array["stateProvince"]), (array[county])
18
)$$); -- runtime: 2 min ("106693.873") @starscream
19 10389 aaronmk
20
21
-- map_*() derived cols
22 11369 aaronmk
-- runtime: 6 min @starscream
23 10389 aaronmk
24
CREATE OR REPLACE FUNCTION map_coord_validity(value integer)
25
  RETURNS boolean AS
26
$BODY$
27
/*
28
"For latlonvalidity:
29
-1: Latitude and/or longitude is null
30
 0: Coordinate is not a valid geographic location
31
 1: Coordinate is a valid geographic location
32
" (http://vegpath.org/fs/inputs/.geoscrub/_src/README.TXT)
33
*/
34
SELECT util._map('-1=>NULL, 0=>false, 1=>true', $1::text)::boolean
35
$BODY$
36
  LANGUAGE sql IMMUTABLE
37
  COST 100;
38
39 11369 aaronmk
SELECT mk_derived_col((:table_str, 'latLongDomainValid'), $$map_coord_validity("latLongDomainValid_verbatim")$$); -- runtime: 1.5 min ("Time: 92913.814 ms") @starscream
40 10389 aaronmk
41
CREATE OR REPLACE FUNCTION map_geovalidity(value integer)
42
  RETURNS boolean AS
43
$BODY$
44
/*
45
"For countryvalidity/stateprovincevalidity/countyvalidity:
46
-1: Name is null at this or some higher level
47
 0: Complete name provided, but couldn't be scrubbed to GADM
48
 1: Point is >5km from putative GADM polygon
49
 2: Point is <=5km from putative GADM polygon, but still outside it
50
 3: Point is in (or on border of) putative GADM polygon
51
" (http://vegpath.org/fs/inputs/.geoscrub/_src/README.TXT)
52
*/
53
SELECT util._map('-1=>NULL, 0=>NULL, 1=>false, 2=>true, 3=>true', $1::text)::boolean
54
$BODY$
55
  LANGUAGE sql IMMUTABLE
56
  COST 100;
57
58 11369 aaronmk
SELECT mk_derived_col((:table_str, 'latLongInCountry'      ), $$map_geovalidity("latLongInCountry_verbatim"      )$$); -- runtime: 1.5 min ("Time: 95967.016 ms") @starscream
59
SELECT mk_derived_col((:table_str, 'latLongInStateProvince'), $$map_geovalidity("latLongInStateProvince_verbatim")$$); -- runtime: 1.5 min ("Time: 95003.576 ms") @starscream
60
SELECT mk_derived_col((:table_str, 'latLongInCounty'       ), $$map_geovalidity("latLongInCounty_verbatim"       )$$); -- runtime: 1.5 min ("Time: 94405.944 ms") @starscream
61 11375 aaronmk
62
SELECT mk_derived_col((:table_str, 'geovalid'),
63
$$_and("latLongDomainValid", "latLongInCountry", "latLongInStateProvince", "latLongInCounty")$$
64
); -- runtime: 3 min ("Time: 173062.394 ms") @starscream