SELECT util.search_path_append('util');

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

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

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

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

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

-- remove frameshifted rows

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

DELETE FROM :table

WHERE

    acronym IS NULL

OR  coll_year !~ E'^(?:1[7-9]|20)\\d{2}$'

OR  country_verbatim ~ E'\\d'

OR  (longitude_deg_verbatim ~ E'[[:alpha:]]' AND longitude_deg_verbatim NOT IN ('RESTRINGIDO'))

;

-- 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

*/

;