SQL for generating taxonomic units, synonyms, and vernaculars from ITIS database

synonyms_export.sql

# Generate synonyms from specified kingdom in ITIS file format:
# [SY] || tsn | tsn_accepted |
# Contact: Jason Best - jbest@brit.org
SELECT '[SY]', '',synonym_links.tsn, synonym_links.tsn_accepted
FROM synonym_links
INNER JOIN taxonomic_units on synonym_links.tsn=taxonomic_units.tsn 
WHERE taxonomic_units.kingdom_id = 3 #3=plantae
# Delete lines below if export is performed separately in GUI etc
INTO OUTFILE '/var/lib/mysql-files/itis_synonyms.csv' 
# saving to file may require modification of MySQL --secure-file-priv parameter
# OUTFILE can not exist or query will fail
FIELDS TERMINATED BY '|'
ENCLOSED BY ''
LINES TERMINATED BY '|\n';

taxon_authors_export.sql

# Generate taxonomic author records from specified kingdom in ITIS file format:
# https://www.itis.gov/download_format.html
# [TA] | taxon_author_id | taxon_author | kingdom_id |
# Contact: Jason Best - jbest@brit.org
SELECT '[TA]', taxon_author_id , taxon_author , kingdom_id
FROM taxon_authors_lkp
WHERE kingdom_id = 3 #3=plantae
# Delete lines below if export is performed separately in GUI etc
INTO OUTFILE '/var/lib/mysql-files/itis_taxon_authors.csv' 
#saving to file may require modification of MySQL --secure-file-priv parameter
# OUTFILE can not exist or query will fail
FIELDS TERMINATED BY '|'
ENCLOSED BY ''
LINES TERMINATED BY '|\n';

taxonomic_units_export.sql

# Generate taxonomic units from specified kingdom in ITIS file format:
# [TU] | tsn | unit_ind1 | unit_name1 | unit_ind2 | unit_name2 | unit_ind3 | unit_name3 | unit_ind4 | unit_name4 | unnamed_taxon_ind | usage | unacceptability_reason | taxonomic_credibility_rating | taxonomic_completeness_rating | currency_rating | phylo_sort_sequence | initial_time_stamp | parent_tsn || taxon_author_id || hybrid_author_id | kingdom_id | rank_id | uncertain_parent_ind |
# Contact: Jason Best - jbest@brit.org
# Using 'ifnull' to make sure NULL values are exported as blanks, not as 'NULL' or '/N'
SELECT '[TU]', tsn , ifnull(unit_ind1,'') , ifnull(unit_name1,'') , ifnull(unit_ind2,'') , ifnull(unit_name2,'') , ifnull(unit_ind3,'') , ifnull(unit_name3,'') , ifnull(unit_ind4,'') , ifnull(unit_name4,'') , ifnull(unnamed_taxon_ind,'') , ifnull(name_usage,'') , ifnull(unaccept_reason,'') , ifnull(credibility_rtng,'') , ifnull(completeness_rtng,'') , ifnull(currency_rating,'') , ifnull(phylo_sort_seq,'') , ifnull(initial_time_stamp,'') , ifnull(parent_tsn,'') ,'', ifnull(taxon_author_id,'') ,'', ifnull(hybrid_author_id,'') , ifnull(kingdom_id,'') , ifnull(rank_id,'') , ifnull(uncertain_prnt_ind,'') from taxonomic_units
WHERE kingdom_id = 3 #3=plantae
# Delete lines below if export is performed separately in GUI etc
INTO OUTFILE '/var/lib/mysql-files/itis_taxonomic_units.csv' 
# saving to file may require modification of MySQL --secure-file-priv parameter
# OUTFILE can not exist or query will fail
FIELDS TERMINATED BY '|'
ENCLOSED BY ''
LINES TERMINATED BY '|\n';

vernaculars_export.sql

# Generate vernacular records from specified kingdom in ITIS file format:
# [VR] || tsn | vernacular_name | vern_id | language | approved_ind |
# Contact: Jason Best - jbest@brit.org
SELECT '[VR]','', vernaculars.tsn, vernaculars.vernacular_name, vernaculars.vern_id, vernaculars.language, vernaculars.approved_ind
FROM vernaculars
INNER JOIN taxonomic_units on vernaculars.tsn=taxonomic_units.tsn 
WHERE taxonomic_units.kingdom_id = 3 #3=plantae
# Delete lines below if export is performed separately in GUI etc
INTO OUTFILE '/var/lib/mysql-files/itis_vernaculars.csv' 
# saving to file may require modification of MySQL --secure-file-priv parameter
# OUTFILE can not exist or query will fail
FIELDS TERMINATED BY '|'
ENCLOSED BY ''
LINES TERMINATED BY '|\n';