--##FILE2

--##Inspects word position #2 for keywords; bins hits by habit; removes hits from source table; removes "bad" hits from bins.  "Bad" = keyword is part of a prepositional predicate phrase   

--EPI -I

create table epi2 select * from NSet where description regexp '^[ \t]*[[:graph:]]+[ \t]+[[:graph:]]*(sub|semi|hemi)*-?epi(f|ph)(i|y)';

alter table epi2 add index (recordID);

delete from NSet where recordID in (select recordID from epi2);

--prep removal

create table epi2prep select * from epi2 where  description regexp '^[ \t]*([[:graph:]]+[ \t]+){0}[[:punct:]0-9]*(on|in(to)?|under|beneath|beside|among(st)?|between|over|from|(on top )?of|(next )?to|by|near|opposite|along(side)?|amid|with(out)?)[ \t]+';

delete  from epi2 where  description regexp '^[ \t]*([[:graph:]]+[ \t]+){0}[[:punct:]0-9]*(on|in(to)?|under|beneath|beside|among(st)?|between|over|from|(on top )?of|(next )?to|by|near|opposite|along(side)?|amid|with(out)?)[ \t]+';

--LIAN

create table lian2 select * from NSet where description regexp '^[ \t]*[[:graph:]]+[ \t]+([[:punct:]]*lian|[[:graph:]]*cipo|[[:graph:]]*bejuc)';

alter table lian2 add index (recordID);

delete from NSet where recordID in (select recordID from lian2);

--prep removal

create table lian2prep select * from lian2 where  description regexp '^[ \t]*[[:punct:]]*(on|in(to)?|under|beneath|beside|among(st)?|between|over|from|(on top )?of|(next )?to|by|near|opposite|along(side)?|amid|up|with(out)?)[ \t]+li';

alter table lian2prep add index (recordID);

delete from lian2 where recordID in (select recordID from lian2prep);

insert into lian2prep select * from lian2 where  description regexp '^[ \t]*[[:punct:]0-9]*(al|segun|sobre|partir|parte|desde|(en)?cima|en|dentro|(a|de)?bajo|lado|junto|cerca|entre|medio|traves|para)[ \t]+';

delete from  lian2 where  description regexp '^[ \t]*[[:punct:]0-9]*(al|segun|sobre|partir|parte|desde|(en)?cima|en|dentro|(a|de)?bajo|lado|junto|cerca|entre|medio|traves|para)[ \t]+';

insert into lian2prep select * from lian2 where description regexp '^[ \t]*([[:graph:]]+[ \t]+){0}[[:punct:]0-9]*(sur|sous|par?-?(desso?us|derriere)?|aux?|des?|en(tre)?|portee|sommet|pres|le(s|z)?|devant|sans|avec|chez|dedans|hors|pour|proche)[ \t]+';

delete from  lian2 where description regexp '^[ \t]*([[:graph:]]+[ \t]+){0}[[:punct:]0-9]*(sur|sous|par?-?(desso?us|derriere)?|aux?|des?|en(tre)?|portee|sommet|pres|le(s|z)?|devant|sans|avec|chez|dedans|hors|pour|proche)[ \t]+';

delete from lian where recordID in (select recordID from lian2prep);

--FERN

---create table fern2 select * from NSet where description regexp '^[ \t]*[[:graph:]]+[ \t]+[[:punct:]0-9]*((tree)?-?fern([[:>:]]|s)|helech)';

---alter table fern2 add index (recordID);

---delete from NSet where recordID in (select recordID from fern2);

---insert into fern2 select * from tree  where description regexp '^[ \t]*[[:graph:]]*tree fern';

---delete from tree where recordID in (select recordID from fern2);

--prep removal

/*

---create table fern2prep select * from fern2 where  description regexp '^[ \t]*([[:graph:]]+[ \t]+){0}[[:punct:]]*(on|in(to)?|under|beneath|beside|among(st)?|between|over|from|(on top )?of|(next )?to|by|near|opposite|along(side)?|amid|up|with(out)?)[ \t]+';

---delete from fern2  where  description regexp '^[ \t]*([[:graph:]]+[ \t]+){0}[[:punct:]]*(on|in(to)?|under|beneath|beside|among(st)?|between|over|from|(on top )?of|(next )?to|by|near|opposite|along(side)?|amid|up|with(out)?)[ \t]+';

*/

--VINE

create table vine2 select * from NSet where description regexp '^[ \t]*[[:graph:]]+[ \t]+[[:punct:]0-9]*((sub|grape|semi|bush)*-?vine|parra[[:>:]])';

alter table vine2 add index (recordID);

delete from NSet where recordID in (select recordID from vine2);

--prep removal

create table vine2prep select * from vine2 where  description regexp '^[ \t]*[[:punct:]0-9]*(on|in(to)?|under|beneath|beside|among(st)?|between|over|from|(on top )?of|(next )?to|by|near|opposite|along(side)?|amid|with(out)?)[ \t]+';

delete from vine2 where  description regexp '^[ \t]*[[:punct:]0-9]*(on|in(to)?|under|beneath|beside|among(st)?|between|over|from|(on top )?of|(next )?to|by|near|opposite|along(side)?|amid|with(out)?)[ \t]+';

--grass

create table grass2  select * from NSet where description regexp '^[ \t]*([[:graph:]]+[ \t]+){1}([[:punct:]0-9]*(grama|pasto|capim|graminoid|bamb(u|o))|[[:graph:]]*(grass|cesped|tussock|cespitosa|graminea)((es)|s)?[[:>:]])' and not description regexp '^[ \t]*([[:graph:]]+[ \t]+){1}[[:punct:]0-9]*bambucea';

alter table grass2 add index (recordID);

delete from NSet where recordID in (select recordID from grass2);

--prep removal

create table grass2prep select * from grass2 where  description regexp '^[ \t]*([[:graph:]]+[ \t]+){0}[[:punct:]0-9]*(on|in(to)?|under|beneath|beside|among(st)?|between|over|from|(on top )?of|(next )?to|by|near|opposite|along(side)?|amid|with(out)?)[ \t]+';

delete from  grass2 where  description regexp '^[ \t]*([[:graph:]]+[ \t]+){0}[[:punct:]0-9]*(on|in(to)?|under|beneath|beside|among(st)?|between|over|from|(on top )?of|(next )?to|by|near|opposite|along(side)?|amid|with(out)?)[ \t]+';

insert into grass2prep select * from grass2 where  description regexp '^[ \t]*([[:graph:]]+[ \t]+){0}[1-9]*(al?|segun|sobre|partir|parte|desde|(en)?cima|en|dentro|(a|de)?bajo|lado|junto|cerca|entre|medio|de|traves|para)[ \t]+';

delete from grass2 where  description regexp '^[ \t]*([[:graph:]]+[ \t]+){0}[1-9]*(al?|segun|sobre|partir|parte|desde|(en)?cima|en|dentro|(a|de)?bajo|lado|junto|cerca|entre|medio|de|traves|para)[ \t]+';

--erva

create table erva2 select * from NSet where description regexp '^[ \t]*[[:graph:]]+[ \t]+[[:punct:]]*erva([[:>:]]|[^l])';

alter table erva2 add index (recordID);

delete from NSet where recordID in (select recordID from erva2);

--hydro

create table hydro2 select * from NSet where description regexp '^[ \t]*[[:graph:]]+[ \t]+[[:<:]]h(y|i)dro(f|p)';

alter table hydro2 add index (recordID);

delete from NSet where recordID in (select recordID from hydro2);

--HERB

create table herb2 select * from NSet where description regexp '^[ \t]*([[:graph:]]+[ \t]+){1}[[:graph:]]*herb(a|e|s)?c?' and not description regexp '^[ \t]*([[:graph:]]+[ \t]+){1}[[:graph:]]*herba(r|z)'  union select  * from NSet where description regexp '^[ \t]*([[:graph:]]+[ \t]+){1}([[:<:]](hierb|yerba|kraut|forb)|[[:punct:]0-9]*(staude|geo(f|p)|liver|hepatic))';

alter table herb2 add index (recordID);

delete from NSet where recordID in (select recordID from herb2);

--prep removal

create table herb2prep select * from herb2 where  description regexp '^[ \t]*[[:punct:]0-9]*(on|in(to)?|under|beneath|beside|among(st)?|between|over|from|(on top )?of|(next )?to|by|near|opposite|along(side)?|amid|up|with(out)?)[ \t]+';

delete from herb2 where  description regexp '^[ \t]*[[:punct:]0-9]*(on|in(to)?|under|beneath|beside|among(st)?|between|over|from|(on top )?of|(next )?to|by|near|opposite|along(side)?|amid|up|with(out)?)[ \t]+';

--PARASIT

--create table parasit2 select * from NSet where description regexp '^[ \t]*[[:graph:]]+[ \t]+[[:punct:]]*((s|h)?emi)*-?parasit';

 create table parasit2 select * from NSet where description regexp '^[ \t]*[[:graph:]]+[ \t]+[[:punct:]]*((s|h)?emi)*-?parasit' and not description regexp '^[ \t]*[[:graph:]]+[ \t]+[[:punct:]]*((s|h)?emi)*-?parasitic (herb|shrub|strang|vine|lian)' ;

alter table parasit2  add index (recordID);

delete from NSet where recordID in (select recordID from parasit2);

--SUFF

create table suff2 select * from NSet where description regexp '^[ \t]*[[:graph:]]+[ \t]+[[:punct:]]*suf+ruc?t(e|i)';

alter table suff2 add index (recordID);

delete from NSet where recordID in (select recordID from suff2);

--CLIMBER

create table climber2 select * from NSet where description regexp '^[ \t]*[[:graph:]]+[ \t]+([[:graph:]]*-?(creep|climb)er|grimpante|plante rampante|[[:graph:]]*-?(trepad(o|ei)r|endredadera))' and not description regexp '^[ \t]*[[:graph:]]+[ \t]+[[:graph:]]*arbusto-(trepador|endredadera)|trepadores';

alter table climber2 add index (recordID);

delete from NSet where recordID in (select recordID from climber2);

--TREE

create table tree2 select * from NSet where description regexp '^[ \t]*([[:graph:]]+[ \t]+){1}[[:graph:]]*(palm(i|e|a)?(e|i)?(r|t)?|baum(ch|e|[[:>:]])|boom)' and not description regexp  '^[ \t]*([[:graph:]]+[ \t]+){1}[[:graph:]]*palmar' UNION select * from NSet where description regexp '^[ \t]*([[:graph:]]+[ \t]+){1}[[:punct:]0-9]*((citrus|semi|sub)*-?tree(let)?|arbre[[:>:]])' UNION select * from NSet where description regexp '^[ \t]*([[:graph:]]+[ \t]+){1}[[:punct:]0-9]*arbol' and not description regexp '^[ \t]*([[:graph:]]+[ \t]+){1}[[:punct:]0-9]*arbol(e|a)d(o|a)' UNION select * from NSet where description regexp '^[ \t]*([[:graph:]]+[ \t]+){1}[[:punct:]0-9]*([[:<:]]arvore[[:>:]]|sapling|retono)';

alter table tree2 add index (recordID);

delete from NSet where recordID in (select recordID from tree2);

--prep removal

 create table tree2prep select * from tree2 where  description regexp '^[ \t]*(on|in(to)?|under|beneath|beside|among(st)?|between|(on top )?of|(next )?to|by|near|opposite|along(side)?|amid|up|with(out)?)[ \t]+[[:graph:]]*((sub|citrus|semi)*-?tree|sapling|palm(e|a)?t?)';

alter table tree2prep add index (recordID);

delete from tree2 where recordID in (select recordID from tree2prep);

insert into tree2prep select * from tree2 where  description regexp '^[ \t]*(en|al?|segun|sobre|(en)?cima|dentro|(a|de)?bajo|lado|junto|cerca|entre|del?|medio)[ \t]+[[:punct:]]*[1-9]*arbol';

delete from tree2 where recordID in (select recordID from tree2prep);

insert into tree2prep select * from tree2 where description regexp '^[ \t]*(d(e|(o|a)?s?)|(n|pe)((o|a)?s?)|por|a(o|s)?debaixo|e|junto|atraves|sob(re?)|(a)?cerca|dentro|com|entre|(pert|lad)o|proximo|cima|apesar|meio|partir)[ \t]+[[:punct:]]*[[:<:]]arvore[[:>:]]';

delete from tree2 where recordID in (select recordID from tree2prep);

insert into tree2prep select * from tree2 where description regexp '^[ \t]*(i(n|m)|seit|auf|zur?|mit|unter|neben|zwischen|nah|bei|der|an)[ \t]+[[:punct:]]*[[:graph:]]*(baum)';

delete from tree2 where recordID in (select recordID from tree2prep);

insert into tree2prep select * from tree2 where description regexp 'au baum';

delete from tree2 where recordID in (select recordID from tree2prep);

insert into tree2prep select * from tree2 where description regexp '^[ \t]*[[:graph:]]*(climbing|creeping)[ \t]+';

delete from tree2 where recordID in (select recordID from tree2prep);

--SHRUB

create table shrub2 select * from NSet where description regexp '^[ \t]*[[:graph:]]+[ \t]+([[:graph:]]*(shrub|arbrisseau|frijolillo|frutex)|(citrus|ranken|halb)*-?strauch|arvoreta|subfrutic)'  or    (description regexp '^[ \t]*[[:graph:]]+[ \t]+[[:punct:]]*(semi|sub)*-?arb(o|u)st[aeiou]*(v|n|t|l|c|f)*' and not description regexp '^[ \t]*[[:graph:]]+[ \t]+(arbustal|starbust)');

alter table shrub2 add index (recordID);

delete from NSet where recordID in (select recordID from shrub2);

--prep removal

create table shrub2prep select * from shrub2 where description regexp '^[ \t]*(on|in|under|beneath|beside|among(st)?|between|on top of|next to|by|near|opposite|alongside|amid)[ \t]+[[:graph:]]*shrub';

alter table shrub2prep add index (recordID);

delete from shrub2 where recordID in (select recordID from shrub2prep);

insert into shrub2prep select * from shrub2 where description regexp '^[ \t]*(en|al|segun|sobre|(en)?cima|dentro|(a|de)?bajo|lado|junto|cerca|entre|del?|medio)[ \t]+[[:graph:]]*(frijolillo|frutex|subfrutic)'  or    description regexp '^[ \t]*(en|al|segun|sobre|(en)?cima|dentro|(a|de)?bajo|lado|junto|cerca|entre|del?|medio)[ \t]+[[:punct:]]*(semi|sub)*-?arb(o|u)st[aeiou]*(v|n|t|l|c|f)*'; 

delete from shrub2 where recordID in (select recordID from shrub2prep);

insert into shrub2prep select * from shrub2 where description regexp '^[ \t]*(i(n|m)|seit|auf|zur?|mit|unter|neben|zwischen|nah|bei|der|an)[[ \t]+[[:graph:]]*(citrus|ranken|halb)*-?strauch';

delete from shrub2 where recordID in (select recordID from shrub2prep);