SELECT util.search_path_append('util');

SELECT util.rename_cols(:table_str, $$

country  => country_verbatim,

state    => stateProvince_verbatim,

county   => county_verbatim,

locality => locality_verbatim,

long_deg => longitude_deg_verbatim,

long_min => longitude_min_verbatim,

long_sec => longitude_sec_verbatim,

lat_deg  => latitude_deg_verbatim,

lat_min  => latitude_min_verbatim,

lat_sec  => latitude_sec_verbatim,

habitat  => habitat_verbatim,

$$::hstore);

SELECT create_if_not_exists($$CREATE INDEX "Specimen.acronym" ON $$||:table_str||$$ (acronym)$$);

-- remove frameshifted rows

-- rerun time: 6 s ("Time: 5537.211 ms") thanks to index

DELETE FROM :table WHERE acronym IS NULL;

DELETE FROM :table WHERE coll_year !~ '^(?:1[7-9]|20)\d{2}$';

DELETE FROM :table WHERE country_verbatim ~ '\d';

DELETE FROM :table WHERE longitude_deg_verbatim ~ '[[:alpha:]]' AND longitude_deg_verbatim NOT IN ('RESTRINGIDO');

DELETE FROM :table WHERE longitude_min_verbatim !~ '^\d*$';

DELETE FROM :table WHERE longitude_sec_verbatim !~ '^\d*$';

DELETE FROM :table WHERE latitude_min_verbatim  !~ '^\d*$';

DELETE FROM :table WHERE latitude_sec_verbatim  !~ '^\d*$';

-- Remove institutions that we have direct data for

DELETE FROM :table

WHERE acronym IN (

    -- Comments are from e-mail from Brad Boyle on 2013-1-16

    'MO' -- "all MO records in REMIB are also available from MO's own website"

    --, 'ARIZ' -- Some REMIB ARIZ specimens not yet in ARIZ itself

    --, 'NY' -- Some REMIB NY specimens not yet in NY itself

    , 'TEX'

)

/* list obtained using the following on r9459:

SELECT DISTINCT dataprovider

FROM sourcelist

JOIN provider_count ON provider_count.dataprovider = sourcelist.name

WHERE source_id = source_by_shortname('REMIB')

ORDER BY dataprovider

*/

;

CREATE OR REPLACE FUNCTION map_nulls__text(value text)

  RETURNS text AS

$BODY$

-- "ND = no disponible = not available" (https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/Spot-checking#REMIB)

SELECT util.map_nulls('{ND,NA}', $1)

$BODY$

  LANGUAGE sql IMMUTABLE STRICT

  COST 100;

SELECT mk_derived_col((:table_str, 'country'      ), $$map_nulls__text("country_verbatim"      )$$); -- runtime: 3   min ("Time: 183171.010 ms")

SELECT mk_derived_col((:table_str, 'stateProvince'), $$map_nulls__text("stateProvince_verbatim")$$); -- runtime: 2.5 min ("Time: 159935.791 ms")

SELECT mk_derived_col((:table_str, 'county'       ), $$map_nulls__text("county_verbatim"       )$$); -- runtime: 2.5 min ("Time: 151076.487 ms")

SELECT mk_derived_col((:table_str, 'locality'     ), $$map_nulls__text("locality_verbatim"     )$$); -- runtime: 2.5 min ("Time: 155188.486 ms")

SELECT mk_derived_col((:table_str, 'habitat'      ), $$map_nulls__text("habitat_verbatim"      )$$); -- runtime: 2   min ("Time: 131904.068 ms")

CREATE OR REPLACE FUNCTION map_nulls__coord__longitude(value text)

  RETURNS double precision AS

$BODY$

-- TODO: sometimes also +-99, but not when min/sec are valid

SELECT util.map_nulls('{-999,999,1000}',

       util.map_nulls('{RESTRINGIDO}', $1)::double precision)

$BODY$

  LANGUAGE sql IMMUTABLE STRICT

  COST 100;

CREATE OR REPLACE FUNCTION map_nulls__coord__other(value text)

  RETURNS double precision AS

$BODY$

-- also map nulls that are valid longitudes

SELECT util.map_nulls('{-99,99}', map_nulls__coord__longitude($1))

$BODY$

  LANGUAGE sql IMMUTABLE STRICT

  COST 100;

SELECT mk_derived_col((:table_str, 'longitude_deg'), $$map_nulls__coord__longitude("longitude_deg_verbatim")$$); -- runtime: 6   min ("Time: 367070.877 ms")

SELECT mk_derived_col((:table_str, 'longitude_min'), $$map_nulls__coord__other    ("longitude_min_verbatim")$$);

SELECT mk_derived_col((:table_str, 'longitude_sec'), $$map_nulls__coord__other    ("longitude_sec_verbatim")$$); -- runtime: 8   min ("Time: 473198.465 ms")

SELECT mk_derived_col((:table_str, 'latitude_deg' ), $$map_nulls__coord__other    ("latitude_deg_verbatim" )$$); -- runtime: 8.5 min ("Time: 508835.119 ms")

SELECT mk_derived_col((:table_str, 'latitude_min' ), $$map_nulls__coord__other    ("latitude_min_verbatim" )$$); -- runtime: 8   min ("Time: 484806.499 ms")

SELECT mk_derived_col((:table_str, 'latitude_sec' ), $$map_nulls__coord__other    ("latitude_sec_verbatim" )$$); -- runtime: 8.5 min ("Time: 505171.027 ms")