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