SELECT util.search_path_append('util');

-- runtime: 1 min ("67374.447 ms") @starscream
SELECT set_col_types(:table_str, ARRAY[
  ('decimalLatitude'                , 'double precision')
, ('decimalLongitude'               , 'double precision')
, ('latLongDomainValid_verbatim'    , 'integer')
, ('latLongInCountry_verbatim'      , 'integer')
, ('latLongInStateProvince_verbatim', 'integer')
, ('latLongInCounty_verbatim'       , 'integer')
]::col_cast[]);

ALTER TABLE :table ALTER COLUMN "decimalLatitude"  SET NOT NULL; -- runtime: 10 s ("11476.920") @starscream
ALTER TABLE :table ALTER COLUMN "decimalLongitude" SET NOT NULL; -- runtime: 2 s ("1536.942") @starscream

-- runtime: 1 min ("49253.507 ms")
SELECT create_if_not_exists($$CREATE INDEX geoscrub_output_input ON geoscrub_output ("decimalLatitude", "decimalLongitude", country, "stateProvince", county)$$);


-- map_*() derived cols
-- runtime: 7 min @starscream

CREATE OR REPLACE FUNCTION map_coord_validity(value integer)
  RETURNS boolean AS
$BODY$
/*
"For latlonvalidity:
-1: Latitude and/or longitude is null
 0: Coordinate is not a valid geographic location
 1: Coordinate is a valid geographic location
" (http://vegpath.org/fs/inputs/.geoscrub/_src/README.TXT)
*/
SELECT util._map('-1=>NULL, 0=>false, 1=>true', $1::text)::boolean
$BODY$
  LANGUAGE sql IMMUTABLE
  COST 100;

SELECT mk_derived_col((:table_str, 'latLongDomainValid'), $$map_coord_validity("latLongDomainValid_verbatim")$$); -- runtime: 2 min ("Time: 97649.394 ms") @starscream

CREATE OR REPLACE FUNCTION map_geovalidity(value integer)
  RETURNS boolean AS
$BODY$
/*
"For countryvalidity/stateprovincevalidity/countyvalidity:
-1: Name is null at this or some higher level
 0: Complete name provided, but couldn't be scrubbed to GADM
 1: Point is >5km from putative GADM polygon
 2: Point is <=5km from putative GADM polygon, but still outside it
 3: Point is in (or on border of) putative GADM polygon
" (http://vegpath.org/fs/inputs/.geoscrub/_src/README.TXT)
*/
SELECT util._map('-1=>NULL, 0=>NULL, 1=>false, 2=>true, 3=>true', $1::text)::boolean
$BODY$
  LANGUAGE sql IMMUTABLE
  COST 100;

SELECT mk_derived_col((:table_str, 'latLongInCountry'      ), $$map_geovalidity("latLongInCountry_verbatim"      )$$); -- runtime: 2 min ("Time: 120695.117 ms") @starscream
SELECT mk_derived_col((:table_str, 'latLongInStateProvince'), $$map_geovalidity("latLongInStateProvince_verbatim")$$); -- runtime: 2 min ("Time: 111719.964 ms") @starscream
SELECT mk_derived_col((:table_str, 'latLongInCounty'       ), $$map_geovalidity("latLongInCounty_verbatim"       )$$); -- runtime: 2 min ("Time: 99485.538 ms") @starscream