SELECT *

FROM nodes

JOIN names USING (tax_id)

WHERE "name class" = 'scientific name'

;

ALTER TABLE :table ALTER COLUMN tax_id TYPE integer

USING tax_id::integer;

ALTER TABLE :table ALTER COLUMN "parent tax_id" TYPE integer

USING "parent tax_id"::integer;

ALTER TABLE :table ADD PRIMARY KEY (tax_id);

tax_id,parent tax_id,rank,embl code,4,5,6,7,8,9,name_txt,unique name,name class

NCBI,VegCore,Filter,Comments

tax_id,taxonID,,

parent tax_id,parentTaxonID,,

rank,taxonRank,,

embl code,*embl code,,

4,*4,,

5,*5,,

6,*6,,

7,*7,,

8,*8,,

9,*9,,

name_txt,taxonName,,

unique name,*unique name,,

name class,*name class,,

NCBI,VegBIEN:/_simplifyPath:[next=parent_id]/path,Comments

parent tax_id,"/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonconcept/_if[@name=""if has explicit parent""]/cond/_exists",

rank,"/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonconcept/_if[@name=""if has explicit parent""]/else/_if[@name=""if has verbatim name""]/{then/.,else/matched_concept_id/taxonconcept:[creator_id/party:[creator_id=0]/organizationname=TNRS]}/identifyingtaxonomicname/_alt/2/_join_words/1/_alt/2/_join_words/5/_join_words/1/_if[@name=""if has infraspecificEpithet""]/then",

name_txt,"/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonconcept/_if[@name=""if has explicit parent""]/else/_if[@name=""if has verbatim name""]/{then/.,else/matched_concept_id/taxonconcept:[creator_id/party:[creator_id=0]/organizationname=TNRS]}/identifyingtaxonomicname/_alt/2/_join_words/1/_alt/2/_join_words/6",

parent tax_id,"/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonconcept/parent_id/taxonconcept/_if[@name=""if has explicit parent""]/cond/_exists",

rank,"/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonconcept/parent_id/taxonconcept/_if[@name=""if has explicit parent""]/else/rank",

rank,"/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonconcept/parent_id/taxonconcept/_if[@name=""if has explicit parent""]/else/verbatimrank",

parent tax_id,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonconcept/parent_id/taxonconcept/sourceaccessioncode,

rank,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonconcept/rank,

tax_id,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonconcept/sourceaccessioncode,

name_txt,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonconcept/taxonname,

rank,/location/locationevent/taxonoccurrence/taxondetermination[!isoriginal]/*_id/taxonconcept/verbatimrank,

4,,** No join mapping for *4 ** 

5,,** No join mapping for *5 ** 

6,,** No join mapping for *6 ** 

7,,** No join mapping for *7 ** 

8,,** No join mapping for *8 ** 

9,,** No join mapping for *9 ** 

embl code,,** No join mapping for *embl code ** 

name class,,** No join mapping for *name class ** 

unique name,,** No join mapping for *unique name ** 

Put template:

<VegBIEN>

    <_simplifyPath id="-1">

        <next>parent_id</next>

        <path>

            <location>

                <locationevent>

                    <taxonoccurrence>

                        <taxondetermination>

                            <taxonconcept_id>

                                <taxonconcept>

                                    <parent_id><taxonconcept><sourceaccessioncode>$parent tax_id</sourceaccessioncode></taxonconcept></parent_id>

                                    <rank>$rank</rank>

                                    <sourceaccessioncode>$tax_id</sourceaccessioncode>

                                    <taxonname>$name_txt</taxonname>

                                    <verbatimrank>$rank</verbatimrank>

                                </taxonconcept>

                            </taxonconcept_id>

                        </taxondetermination>

                    </taxonoccurrence>

                </locationevent>

            </location>

        </path>

    </_simplifyPath>

</VegBIEN>

Inserted 7 new rows into database

tax_id,taxonID,,

parent tax_id,parentTaxonID,,

rank,taxonRank,,

name_txt,taxonName,,

*embl code

*4

*5

*6

*7

*8

*9

*unique name

*name class

tax_id	|	name_txt	|	unique name	|	name class	|

higher_taxa

ftp://ftp.ncbi.nlm.nih.gov/pub/taxonomy/taxdmp.zip

ftp://ftp.ncbi.nlm.nih.gov/pub/taxonomy/

http://www.ncbi.nlm.nih.gov/guide/taxonomy/

link taxdmp/readme.txt

*.dmp files are bcp-like dump from GenBank taxonomy database.

General information.

Field terminator is "\t|\t"

Row terminator is "\t|\n"

nodes.dmp file consists of taxonomy nodes. The description for each node includes the following

fields:

	tax_id					-- node id in GenBank taxonomy database

 	parent tax_id				-- parent node id in GenBank taxonomy database

 	rank					-- rank of this node (superkingdom, kingdom, ...) 

 	embl code				-- locus-name prefix; not unique

 	division id				-- see division.dmp file

 	inherited div flag  (1 or 0)		-- 1 if node inherits division from parent

 	genetic code id				-- see gencode.dmp file

 	inherited GC  flag  (1 or 0)		-- 1 if node inherits genetic code from parent

 	mitochondrial genetic code id		-- see gencode.dmp file

 	inherited MGC flag  (1 or 0)		-- 1 if node inherits mitochondrial gencode from parent

 	GenBank hidden flag (1 or 0)            -- 1 if name is suppressed in GenBank entry lineage

 	hidden subtree root flag (1 or 0)       -- 1 if this subtree has no sequence data yet

 	comments				-- free-text comments and citations

Taxonomy names file (names.dmp):

	tax_id					-- the id of node associated with this name

	name_txt				-- name itself

	unique name				-- the unique variant of this name if name not unique

	name class				-- (synonym, common name, ...)

Divisions file (division.dmp):

	division id				-- taxonomy database division id

	division cde				-- GenBank division code (three characters)

	division name				-- e.g. BCT, PLN, VRT, MAM, PRI...

	comments

Genetic codes file:

	genetic code id				-- GenBank genetic code id

	abbreviation				-- genetic code name abbreviation

	name					-- genetic code name

	cde					-- translation table for this genetic code

	starts					-- start codons for this genetic code

Deleted nodes file (delnodes.dmp):

	tax_id					-- deleted node id

Merged nodes file (merged.dmp):

	old_tax_id                              -- id of nodes which has been merged

	new_tax_id                              -- id of nodes which is result of merging

Citations file (citations.dmp):

	cit_id					-- the unique id of citation

	cit_key					-- citation key

	pubmed_id				-- unique id in PubMed database (0 if not in PubMed)

	medline_id				-- unique id in MedLine database (0 if not in MedLine)

	url					-- URL associated with citation

	text					-- any text (usually article name and authors).

						-- The following characters are escaped in this text by a backslash:

						-- newline (appear as "\n"),

						-- tab character ("\t"),

						-- double quotes ('\"'),

						-- backslash character ("\\").

	taxid_list				-- list of node ids separated by a single space

tax_id	|	parent tax_id	|	rank	|	embl code	|	4	|	5	|	6	|	7	|	8	|	9	|

subdirs := .NCBI/ .TNRS/ $(wildcard */)