<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<body>
<div class="" style="word-wrap:break-word">Hi,
<div class=""><br class="">
</div>
<div class="">based on the CLB data model <a href="https://github.com/gbif/checklistbank/blob/master/docs/schema.pdf" class="">https://github.com/gbif/checklistbank/blob/master/docs/schema.pdf</a></div>
<div class="">I have created a tab delimited text dump (1.2GB uncompressed) from the following statement:</div>
<div class=""><br class="">
</div>
<div class="">
<div class="">SELECT u.id, u.parent_fk, u.basionym_fk, <a href="http://u.is" class="">
u.is</a>_synonym, u.status, u.rank, u.nom_status, u.constituent_key, u.origin, u.source_taxon_key,</div>
<div class=""> u.kingdom_fk, u.phylum_fk, u.class_fk, u.order_fk, u.family_fk, u.genus_fk, u.species_fk,</div>
<div class=""> n.id as name_id, n.scientific_name, n.canonical_name, </div>
<div class=""> n.genus_or_above, n.specific_epithet, n.infra_specific_epithet, n.notho_type, n.authorship, n.year, n.bracket_authorship, n.bracket_year, </div>
<div class=""> cpi.citation as name_published_in, u.issues </div>
<div class="">FROM name_usage u </div>
<div class=""> JOIN name n ON u.name_fk=n.id </div>
<div class=""> LEFT JOIN citation cpi ON u.name_published_in_fk=cpi.id </div>
<div class="">WHERE u.dataset_key=nubKey() and u.deleted IS NULL;</div>
<div class=""><br class="">
</div>
<div class="">The gzipped tab file is hosted here:</div>
<div class=""><a href="http://rs.gbif.org/datasets/backbone/backbone-current.txt.gz" class="">http://rs.gbif.org/datasets/backbone/backbone-current.txt.gz</a></div>
<div class=""><br class="">
</div>
<div class="">You should be able to import that into most relational dbs but for sure into postgres with a table DDL like this (also attached):</div>
<div class=""><br class="">
</div>
<div class="">
<div class="">CREATE TABLE backbone (</div>
<div class=""> id int PRIMARY KEY,</div>
<div class=""> parent_key int,</div>
<div class=""> basionym_key int,</div>
<div class=""> is_synonym boolean,</div>
<div class=""> status text,</div>
<div class=""> rank text,</div>
<div class=""> nom_status text[],</div>
<div class=""> constituent_key text,</div>
<div class=""> origin text,</div>
<div class=""> source_taxon_key int,</div>
<div class=""><br class="">
</div>
<div class=""> kingdom_key int,</div>
<div class=""> phylum_key int,</div>
<div class=""> class_key int,</div>
<div class=""> order_key int,</div>
<div class=""> family_key int,</div>
<div class=""> genus_key int,</div>
<div class=""> species_key int,</div>
<div class=""><br class="">
</div>
<div class=""> name_id int,</div>
<div class=""> scientific_name text,</div>
<div class=""> canonical_name text,</div>
<div class=""> genus_or_above text,</div>
<div class=""> specific_epithet text,</div>
<div class=""> infra_specific_epithet text,</div>
<div class=""> notho_type text,</div>
<div class=""> authorship text,</div>
<div class=""> year text,</div>
<div class=""> bracket_authorship text,</div>
<div class=""> bracket_year text,</div>
<div class=""> </div>
<div class=""> name_published_in text,</div>
<div class=""> issues text[]</div>
<div class="">)</div>
</div>
<div class=""><br class="">
</div>
<div class=""><br class="">
</div>
<div class=""><br class="">
</div>
<div class="">
<div class="">If this format is of interest to a wider community I will make it available for every new backbone version we produce.</div>
</div>
<div class=""><br class="">
</div>
<div class="">Best,</div>
<div class="">Markus</div>
<div class=""><br class="">
</div>
<div class=""><br class="">
</div>
<div class=""></div>
</div>
</div>
<div class="" style="word-wrap:break-word">
<div class="">
<div class=""></div>
<div class=""><br class="">
</div>
<div>
<blockquote type="cite" class="">
<div class="">On 16 Nov 2016, at 11:34, Markus Döring <<a href="mailto:mdoering@gbif.org" class="">mdoering@gbif.org</a>> wrote:</div>
<br class="x_Apple-interchange-newline">
<div class="">
<div class="" style="word-wrap:break-word">Hi,
<div class="">if there is a demand in having the backbone as a SQL file we should maybe consider to provide one directly from GBIF ChecklistBanks relational model.</div>
<div class="">Would a single table with all taxa be sufficient as a start or are vernacular names, images and other additional information key?</div>
<div class="">I can probably supply a sql dump for the core taxonomy quickly, let me try.</div>
<div class=""><br class="">
</div>
<div class="">Markus</div>
<div class=""><br class="">
</div>
<div class=""><br class="">
<div class="">
<blockquote type="cite" class="">
<div class="">On 16 Nov 2016, at 11:01, Köhler Christian <<a href="mailto:C.Koehler@zfmk.de" class="">C.Koehler@zfmk.de</a>> wrote:</div>
<br class="x_Apple-interchange-newline">
<div class="">
<div bgcolor="#FFFFFF" class="">
<div class="x_moz-cite-prefix">Hi,<br class="">
<br class="">
I already looked at both of the git project. Looks interesting. Is I am more "pythonic" the idb-backend is easier to adapt for me.
<br class="">
Thanks for showing me. I was hoping that someone already addressed the question as it seemed to me quite obviously to provide the data in a widely used format like sql. I have the feeling I am reenventing the wheel ;-)
<br class="">
<br class="">
The "B-HIT" tool seems to be a reasonable alternative to get a the desired data into an sql data base. In case you are interested, have a look here:<br class="">
<a class="x_moz-txt-link-freetext" href="https://www.researchgate.net/publication/283543150_B-HIT_-_A_Tool_for_Harvesting_and_Indexing_Biodiversity_Data">https://www.researchgate.net/publication/283543150_B-HIT_-_A_Tool_for_Harvesting_and_Indexing_Biodiversity_Data</a><br class="">
<a class="x_moz-txt-link-freetext" href="https://www.ncbi.nlm.nih.gov/pmc/articles/PMC4636251/">https://www.ncbi.nlm.nih.gov/pmc/articles/PMC4636251/</a><br class="">
<br class="">
Cheers<br class="">
Chris<br class="">
<br class="">
<br class="">
Am 14.11.2016 um 17:40 schrieb Scott Chamberlain:<br class="">
</div>
<blockquote type="cite" class="">
<div dir="ltr" class="">Hi Chris, I started something a while back to automate building a SQLite version of the backbone taxonomy (<a href="https://github.com/ropensci/gbif-backbone-sql" class="">https://github.com/ropensci/gbif-backbone-sql</a>) but it's not
quite done yet. Idea is to run on Heroku (e.g., once a day), resulting in a fresh SQLite version of the backbone taxonomy on Amazon S3.
<div class=""><br class="">
</div>
<div class="">Scott</div>
</div>
<br class="">
<div class="x_gmail_quote">
<div dir="ltr" class="">On Mon, Nov 14, 2016 at 7:50 AM Markus Döring <<a href="mailto:mdoering@gbif.org" class="">mdoering@gbif.org</a>> wrote:<br class="">
</div>
<blockquote class="x_gmail_quote" style="margin:0 0 0
.8ex; border-left:1px #ccc solid; padding-left:1ex">
<div class="x_gmail_msg" style="word-wrap:break-word">Hi Chris,
<div class="x_gmail_msg">the latest GBIF backbone is always available as a Darwin Core archive. This is mostly a collection of tab delimited text files with the accepted and synonym names at its core.</div>
<div class="x_gmail_msg">You can find the latest and previous, archived versions here:</div>
<div class="x_gmail_msg"><a href="http://rs.gbif.org/datasets/backbone/" class="x_gmail_msg" target="_blank">http://rs.gbif.org/datasets/backbone/</a></div>
<div class="x_gmail_msg"><br class="x_gmail_msg">
</div>
<div class="x_gmail_msg">Best,</div>
<div class="x_gmail_msg">Markus</div>
<div class="x_gmail_msg"><br class="x_gmail_msg">
</div>
<div class="x_gmail_msg"><br class="x_gmail_msg">
<div class="x_gmail_msg">
<div class="x_gmail_msg" style="letter-spacing:normal; text-align:start; text-indent:0px; text-transform:none; white-space:normal; word-spacing:0px; word-wrap:break-word">
--<br class="x_gmail_msg">
Markus Döring</div>
<div class="x_gmail_msg" style="letter-spacing:normal; text-align:start; text-indent:0px; text-transform:none; white-space:normal; word-spacing:0px; word-wrap:break-word">
Software Developer<br class="x_gmail_msg">
Global Biodiversity Information Facility (GBIF)<br class="x_gmail_msg">
<div class="x_gmail_msg"><a href="mailto:mdoering@gbif.org" class="x_gmail_msg" target="_blank">mdoering@gbif.org</a></div>
<div class="x_gmail_msg"><a href="http://www.gbif.org/" class="x_gmail_msg" target="_blank">http://www.gbif.org</a></div>
<div class="x_gmail_msg"><br class="x_gmail_msg">
</div>
<div class="x_gmail_msg"><br class="x_gmail_msg">
</div>
<div class="x_gmail_msg"><br class="x_gmail_msg">
</div>
</div>
<br class="x_gmail_msg x_m_6703429565373641011Apple-interchange-newline">
</div>
</div>
</div>
<div class="x_gmail_msg" style="word-wrap:break-word">
<div class="x_gmail_msg"><br class="x_gmail_msg">
<div class="x_gmail_msg">
<blockquote type="cite" class="x_gmail_msg">
<div class="x_gmail_msg">On 14 Nov 2016, at 16:42, Köhler Christian <<a href="mailto:C.Koehler@zfmk.de" class="x_gmail_msg" target="_blank">C.Koehler@zfmk.de</a>> wrote:</div>
<br class="x_gmail_msg x_m_6703429565373641011Apple-interchange-newline">
<div class="x_gmail_msg">
<div class="x_gmail_msg">Hi,<br class="x_gmail_msg">
<br class="x_gmail_msg">
we are developing an application to curate taxonomic and morphological<br class="x_gmail_msg">
data for scientists. At the moment we are evaluating different taxonomic<br class="x_gmail_msg">
backbones to be used within our application. The GIBF taxonomic backbone<br class="x_gmail_msg">
seems to be an good choice in regards to quality, number of entries and<br class="x_gmail_msg">
acceptance.<br class="x_gmail_msg">
<br class="x_gmail_msg">
Due to the nature of our application, a web service to browse the<br class="x_gmail_msg">
taxonomy will not fulfil our requirements. A local copy of the GIBF data<br class="x_gmail_msg">
as SQL would be an ideal solution. I looked for this data publicly<br class="x_gmail_msg">
available to no avail. "Harvesting" the GBIF rest api seems not a good<br class="x_gmail_msg">
option. Are there plans to provide current taxonomic backbone data in<br class="x_gmail_msg">
the future? Maybe the data is already available, but I failed to find it<br class="x_gmail_msg">
yet.<br class="x_gmail_msg">
<br class="x_gmail_msg">
Regards<br class="x_gmail_msg">
Chris<br class="x_gmail_msg">
<br class="x_gmail_msg">
--<br class="x_gmail_msg">
Christian Köhler<br class="x_gmail_msg">
Tel.: 0228 9122-434<br class="x_gmail_msg">
<br class="x_gmail_msg">
Zoologisches Forschungsmuseum Alexander Koenig<br class="x_gmail_msg">
Leibniz-Institut für Biodiversität der Tiere<br class="x_gmail_msg">
Adenauerallee 160, 53113 Bonn, Germany<br class="x_gmail_msg">
<a href="http://www.zfmk.de/" class="x_gmail_msg" target="_blank">www.zfmk.de</a><br class="x_gmail_msg">
<br class="x_gmail_msg">
Stiftung des öffentlichen Rechts<br class="x_gmail_msg">
Direktor: Prof. J. Wolfgang Wägele<br class="x_gmail_msg">
Sitz: Bonn<br class="x_gmail_msg">
--<br class="x_gmail_msg">
Zoologisches Forschungsmuseum Alexander Koenig<br class="x_gmail_msg">
- Leibniz-Institut für Biodiversität der Tiere -<br class="x_gmail_msg">
Adenauerallee 160, 53113 Bonn, Germany<br class="x_gmail_msg">
<a href="http://www.zfmk.de/" class="x_gmail_msg" target="_blank">www.zfmk.de</a><br class="x_gmail_msg">
<br class="x_gmail_msg">
Stiftung des öffentlichen Rechts; Direktor: Prof. J. Wolfgang Wägele<br class="x_gmail_msg">
Sitz: Bonn<br class="x_gmail_msg">
_______________________________________________<br class="x_gmail_msg">
API-users mailing list<br class="x_gmail_msg">
<a href="mailto:API-users@lists.gbif.org" class="x_gmail_msg" target="_blank">API-users@lists.gbif.org</a><br class="x_gmail_msg">
<a href="http://lists.gbif.org/mailman/listinfo/api-users" class="x_gmail_msg" target="_blank">http://lists.gbif.org/mailman/listinfo/api-users</a><br class="x_gmail_msg">
</div>
</div>
</blockquote>
</div>
<br class="x_gmail_msg">
</div>
</div>
_______________________________________________<br class="x_gmail_msg">
API-users mailing list<br class="x_gmail_msg">
<a href="mailto:API-users@lists.gbif.org" class="x_gmail_msg" target="_blank">API-users@lists.gbif.org</a><br class="x_gmail_msg">
<a href="http://lists.gbif.org/mailman/listinfo/api-users" rel="noreferrer" class="x_gmail_msg" target="_blank">http://lists.gbif.org/mailman/listinfo/api-users</a><br class="x_gmail_msg">
</blockquote>
</div>
</blockquote>
<br class="">
<p class=""><br class="">
</p>
<pre class="x_moz-signature" cols="72">--
Christian Köhler
Tel.: 0228 9122-434
Zoologisches Forschungsmuseum Alexander Koenig
Leibniz-Institut für Biodiversität der Tiere
Adenauerallee 160, 53113 Bonn, Germany
<a class="x_moz-txt-link-abbreviated" href="http://www.zfmk.de/">www.zfmk.de</a>
Stiftung des öffentlichen Rechts
Direktor: Prof. J. Wolfgang Wägele
Sitz: Bonn </pre>
-- <br class="">
Zoologisches Forschungsmuseum Alexander Koenig<br class="">
- Leibniz-Institut für Biodiversität der Tiere -<br class="">
Adenauerallee 160, 53113 Bonn, Germany<br class="">
<a href="http://www.zfmk.de/" class="">www.zfmk.de</a><br class="">
<br class="">
Stiftung des öffentlichen Rechts; Direktor: Prof. J. Wolfgang Wägele<br class="">
Sitz: Bonn<br class="">
</div>
_______________________________________________<br class="">
API-users mailing list<br class="">
<a href="mailto:API-users@lists.gbif.org" class="">API-users@lists.gbif.org</a><br class="">
<a href="http://lists.gbif.org/mailman/listinfo/api-users" class="">http://lists.gbif.org/mailman/listinfo/api-users</a><br class="">
</div>
</blockquote>
</div>
<br class="">
</div>
</div>
_______________________________________________<br class="">
API-users mailing list<br class="">
<a href="mailto:API-users@lists.gbif.org" class="">API-users@lists.gbif.org</a><br class="">
http://lists.gbif.org/mailman/listinfo/api-users<br class="">
</div>
</blockquote>
</div>
<br class="">
</div>
</div>
</body>
</html>