SELECT util.search_path_append('util');

-- runtime: 30 s ("36510.730 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: 5 s ("5248.843") @starscream
ALTER TABLE :table ALTER COLUMN "decimalLongitude" SET NOT NULL; -- runtime: 1 s ("1064.650") @starscream

SELECT create_if_not_exists($$CREATE UNIQUE INDEX $$||:table_str||%'__unique'||$$ ON $$||:table_str||$$ (
(array["decimalLatitude"]), (array["decimalLongitude"]), (array[country]), (array["stateProvince"]), (array[county])
)$$); -- runtime: 2 min ("106693.873") @starscream


-- map_*() derived cols
-- runtime: 6 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: 1.5 min ("Time: 92913.814 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: 1.5 min ("Time: 95967.016 ms") @starscream
SELECT mk_derived_col((:table_str, 'latLongInStateProvince'), $$map_geovalidity("latLongInStateProvince_verbatim")$$); -- runtime: 1.5 min ("Time: 95003.576 ms") @starscream
SELECT mk_derived_col((:table_str, 'latLongInCounty'       ), $$map_geovalidity("latLongInCounty_verbatim"       )$$); -- runtime: 1.5 min ("Time: 94405.944 ms") @starscream

SELECT mk_derived_col((:table_str, 'geovalid'),
$$_and("latLongDomainValid", "latLongInCountry", "latLongInStateProvince", "latLongInCounty")$$
); -- runtime: 3 min ("Time: 173062.394 ms") @starscream