--### FILE5

--### Clean-up.  Returns selected records from bins to source table

--paramo, after stage4, before epiphytes BUT REALLY WHO CARES? lots of order changes already

insert into NSet select * from shrub2 where description regexp 'shrub(by)? paramo';

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

insert into NSet select * from shrub where description regexp '^[ \t]*[[:graph:]]+[ \t]+paramo';

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

insert into NSet select * from shrub3 where description regexp 'shrub(by)? paramo';

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

insert into NSet select *  from shrub4 where description regexp '^[ \t]*([[:graph:]]+[ \t]+){4}paramo';

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

insert into NSet select *   from grass4 where description regexp '^[ \t]*([[:graph:]]+[ \t]+){4}paramo';

delete    from grass4 where description regexp '^[ \t]*([[:graph:]]+[ \t]+){4}paramo';

insert into NSet select *   from grass3 where description regexp '^[ \t]*([[:graph:]]+[ \t]+){3}paramo';

delete    from grass3 where description regexp '^[ \t]*([[:graph:]]+[ \t]+){3}paramo';

insert into NSet select *    from grass2 where description regexp '^[ \t]*([[:graph:]]+[ \t]+){2}paramo';

delete    from grass2 where description regexp '^[ \t]*([[:graph:]]+[ \t]+){2}paramo';

insert into NSet select *     from grass where description regexp '^[ \t]*([[:graph:]]+[ \t]+){1}paramo';

delete from grass  where description regexp '^[ \t]*([[:graph:]]+[ \t]+){1}paramo';

insert into NSet select * from shrub4 where description regexp 'Fels, Erde, Mauer, Strauch|strauch-paramo';

delete from shrub4 where description regexp 'Fels, Erde, Mauer, Strauch|strauch-paramo';

--blechn

insert into NSet select * from shrub where description regexp 'blechnum-shrub assoc';

delete  from shrub where description regexp 'blechnum-shrub assoc';

---all this stuff done...5/3?  maybe should undo and take care of the paramos first

create table epiclimber select * From climber2 where description regexp '^[ \t]*([[:graph:]]+[ \t]+){2,4}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

delete from  climber2 where description regexp '^[ \t]*([[:graph:]]+[ \t]+){2,4}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

insert into epiclimber select * From climber where description regexp '^[ \t]*([[:graph:]]+[ \t]+){1}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

delete from climber where description regexp '^[ \t]*([[:graph:]]+[ \t]+){1}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

--herb

create table epiherb select * From herb where description regexp '^[ \t]*([[:graph:]]+[ \t]+){3,5}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

delete from  herb where description regexp '^[ \t]*([[:graph:]]+[ \t]+){3,5}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

insert into epiherb select * From herb2 where description regexp '^[ \t]*([[:graph:]]+[ \t]+){2,5}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

delete  From herb2 where description regexp '^[ \t]*([[:graph:]]+[ \t]+){2,5}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

--lian

create table epilian select * From lian where description regexp '^[ \t]*([[:graph:]]+[ \t]+){1}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

delete  From lian where description regexp '^[ \t]*([[:graph:]]+[ \t]+){1}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

insert into epilian select * From lian where description regexp '^[ \t]*([[:graph:]]+[ \t]+){2}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

delete From lian where description regexp '^[ \t]*([[:graph:]]+[ \t]+){2}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

insert into epilian select * From lian where description regexp '^[ \t]*([[:graph:]]+[ \t]+){3,5}(hemi|semi|sub)?-?epi(f|ph)(i|y)' and not description regexp 'not hemi';

delete  From lian where description regexp '^[ \t]*([[:graph:]]+[ \t]+){3,5}(hemi|semi|sub)?-?epi(f|ph)(i|y)' and not description regexp 'not hemi';

INSERT INTO epilian select * From lian2 where description regexp '^[ \t]*([[:graph:]]+[ \t]+){2,}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

 delete  From lian2 where description regexp '^[ \t]*([[:graph:]]+[ \t]+){2,}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

--moss

create table epimoss select * From moss where description regexp '^[ \t]*([[:graph:]]+[ \t]+){1}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

delete  From moss where description regexp '^[ \t]*([[:graph:]]+[ \t]+){1}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

--parasite

create table episite select * From parasit where description regexp '^[ \t]*([[:graph:]]+[ \t]+){0,5}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

delete From parasit where description regexp '^[ \t]*([[:graph:]]+[ \t]+){0,5}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

--vine

create table epivine select * From vins where description regexp '^[ \t]*([[:graph:]]+[ \t]+){1}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

 insert into epivine select * From vins where description regexp '^[ \t]*([[:graph:]]+[ \t]+){2}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

 insert into epivine select * From vins where description regexp '^[ \t]*([[:graph:]]+[ \t]+){3,}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

delete  From vins where description regexp '^[ \t]*([[:graph:]]+[ \t]+){1,}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

 insert into epivine select * From vine2 where description regexp '^[ \t]*([[:graph:]]+[ \t]+){3}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

delete from vine2 where description regexp '^[ \t]*([[:graph:]]+[ \t]+){3}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

 insert into epivine select * From vine2 where description regexp '^[ \t]*([[:graph:]]+[ \t]+){2}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

 delete  From vine2 where description regexp '^[ \t]*([[:graph:]]+[ \t]+){2}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

--shrub

create table epishrub select * From shrub2 where description regexp '^[ \t]*([[:graph:]]+[ \t]+){2}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

delete  From shrub2 where description regexp '^[ \t]*([[:graph:]]+[ \t]+){2}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

 insert into epishrub select * From shrub2 where description regexp '^[ \t]*([[:graph:]]+[ \t]+){3}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

delete  From shrub2 where description regexp '^[ \t]*([[:graph:]]+[ \t]+){3}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

insert into epishrub select * From shrub2 where description regexp '^[ \t]*([[:graph:]]+[ \t]+){4,6}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

delete  From shrub2 where description regexp '^[ \t]*([[:graph:]]+[ \t]+){4,6}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

insert into epishrub select * From shrub where description regexp '^[ \t]*([[:graph:]]+[ \t]+){1}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

alter table epishrub add index (recordID);

delete from shrub where recordID in (select recordID from epishrub);

insert into epishrub select * From shrub where description regexp '^[ \t]*([[:graph:]]+[ \t]+){3}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

delete  From shrub where description regexp '^[ \t]*([[:graph:]]+[ \t]+){3}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

insert into epishrub select * From shrub where description regexp '^[ \t]*([[:graph:]]+[ \t]+){4,5}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

delete  From shrub where description regexp '^[ \t]*([[:graph:]]+[ \t]+){4,5}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

--tree

create table treeepi select * From tree where description regexp '^[ \t]*([[:graph:]]+[ \t]+){1}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

alter table treeepi add index (recordID);

delete from tree where recordID in (select recordID from treeepi);

insert into treeepi select * From tree where description regexp '^[ \t]*([[:graph:]]+[ \t]+){2}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

 delete from  tree where description regexp '^[ \t]*([[:graph:]]+[ \t]+){2}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

insert into treeepi select * From tree where description regexp '^[ \t]*([[:graph:]]+[ \t]+){3}(hemi|semi|sub)?-?epi(f|ph)(i|y)' and not description regexp 'with epi';

delete From tree where description regexp '^[ \t]*([[:graph:]]+[ \t]+){3}(hemi|semi|sub)?-?epi(f|ph)(i|y)' and not description regexp 'with epi';

insert into treeepi select  * From tree where description regexp '^[ \t]*([[:graph:]]+[ \t]+){4}(hemi|semi|sub)?-?epi(f|ph)(i|y)' and not description regexp 'with epi';

delete From tree where description regexp '^[ \t]*([[:graph:]]+[ \t]+){4}(hemi|semi|sub)?-?epi(f|ph)(i|y)' and not description regexp 'with epi';

insert into treeepi select  * From tree where description regexp '^[ \t]*([[:graph:]]+[ \t]+){5}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

delete from tree where recordID in (select recordID from treeepi);

insert into treeepi select  * From tree where description regexp '^[ \t]*([[:graph:]]+[ \t]+){6}(hemi|semi|sub)?-?epi(f|ph)(i|y)' and not description regexp ' no epif';

delete   From tree where description regexp '^[ \t]*([[:graph:]]+[ \t]+){6}(hemi|semi|sub)?-?epi(f|ph)(i|y)' and not description regexp ' no epif';

insert into treeepi select * From tree2 where description regexp '^[ \t]*([[:graph:]]+[ \t]+){3}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

delete From tree2 where description regexp '^[ \t]*([[:graph:]]+[ \t]+){3}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

insert into treeepi select * From tree2 where description regexp '^[ \t]*([[:graph:]]+[ \t]+){4}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

delete  From tree2 where description regexp '^[ \t]*([[:graph:]]+[ \t]+){4}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

insert into treeepi select * From tree2 where description regexp '^[ \t]*([[:graph:]]+[ \t]+){5}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

delete  From tree2 where description regexp '^[ \t]*([[:graph:]]+[ \t]+){5}(hemi|semi|sub)?-?epi(f|ph)(i|y)';

insert into treeepi select * From tree3 where description regexp '^[ \t]*tree (like|trunk|limb)[ \t]+(hemi|semi|sub)?-?epi(f|ph)(i|y)';

delete From tree3 where description regexp '^[ \t]*tree (like|trunk|limb)[ \t]+(hemi|semi|sub)?-?epi(f|ph)(i|y)';

--###FILE6

--###Returns bad hits from bins to source table

---note how these are returned after already sorted

 insert into NSet select * from epi2prep;

 insert into NSet select * from epi4prep;

 /*

 insert into NSet select * from fern2prep;

 insert into NSet select * from fern3prep;

 insert into NSet select * from fern4prep;

 */

 insert into NSet select * from grass2prep;

 insert into NSet select * from grass3prep;

 insert into NSet select * from grass4prep;

 insert into NSet select * from herb2prep;

 insert into NSet select * from herb4prep;

 insert into NSet select * from lian2prep;

 insert into NSet select * from lian3prep;

 insert into NSet select * from lian4prep;

 insert into NSet select * from shrub2prep;

 insert into NSet select * from shrub3prep;

 insert into NSet select * from shrub4prep;

 insert into NSet select * from tree2prep;

 insert into NSet select * from tree3prep;

 insert into NSet select * from tree4prep;

 insert into NSet select * from vine2prep;

 insert into NSet select * from vine3prep;

insert into NSet select * from arborgroup;

--##File7

--##Consolidates bins down to 12, one for each habit

--make epi overall table

rename table epis to epi1;

create table epi select * from epi1;

insert into epi select * from epiherb;

insert into epi select * from epiclimber;

insert into epi select * from epilian;

insert into epi select * from epimoss;

insert into epi select * from epishrub;

insert into epi select * from episite;

insert into epi select * from epivine;

insert into epi select * from treeepi;

insert into  epi select * from epi2;

insert into  epi select * from epi3;

insert into  epi select * from epi4to6;

--climber

create table climber1 select * from climber;

insert into climber select * from climber2;

insert into climber select * from climber3;

insert into climber select * from climber4;

--erva

insert into herb select * from erva;

insert into herb select * from erva2;

--grass

create table grass1 select * from grass;

insert into  grass select * from grass2;

insert into  grass select * from grass3;

insert into  grass select * from grass4;

--herb

create table herb1 select * from herb;

insert into herb  select * from herb2;

insert into herb  select * from herb3;

insert into herb  select * from herb4;

--lian

create table lian1 select * from lian;

insert into lian select * from lian2;

insert into lian select * from lian3;

insert into lian select * from lian4;

--hyrdo

create table hydro1 select * from hydrophyte;

rename table hydrophyte to hydro;

insert into hydro select * from hydro2;

--paras

create table parasit1 select * from parasit;

insert into parasit select * from parasit2;

--shrub

create table shrub1 select * from shrub;

insert into shrub select * from shrub2;

insert into shrub select * from shrub3;

insert into shrub select * from shrub4;

--tree

create table tree1 select * from tree;

insert into tree select * from tree2;

insert into  tree select * from tree3;

insert into  tree select * from tree4;

--suff

create table suff1 select * from suffrut;

rename table suffrut to suff;

insert into suff select * from suff2;

insert into suff select * from suff3;

insert into suff select * from suff4;

--vine

create table vine1 select * from vins;

rename table vins to vine;

insert into vine select * from vine2;

insert into vine select * from vine3;

--##FILE1

--##Inspects word position #1 for keywords; bins hits by habit; removes hits from source table 

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

alter table epis add index (recordID);

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

create table lian select * from NSet where description regexp '^[[:punct:]0-9]*lian|[[:graph:]]*(cipo|bejuc)';

alter table lian add index (recordID);

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

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

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

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

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

alter table vins add index (recordID);

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

create table grass  select * from NSet where description regexp '^[ \t]*([[:graph:]]+[ \t]+){0}([[: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]+){0}[[:punct:]0-9]*bambucea';

--missing semicolons discovered 6-07; semicolons added 6-07

alter table grass add index (recordID);

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

create table erva select * from NSet where description regexp '^[[:punct:]]*erva';

alter table erva add index (recordID);

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

create table moss select * from NSet where description regexp '^[[:graph:]]*(moss|musgo)';

alter table moss add index (recordID);

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

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

alter table hydrophyte add index (recordID);

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

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

alter table herb add index (recordID);

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

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

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

alter table parasit add index (recordID);

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

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

alter table suffrut add index (recordID);

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

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

alter table climber add index (recordID);

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

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

alter table tree add index (recordID);

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

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

alter table shrub add index (recordID);

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

create table arborgroup select * from NSet where description regexp '[[:<:]]arbore?(s|a|o)?[[:>:]]' ;

alter table arborgroup add index (recordID);

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

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