[Taxacom] recursive SQL database for taxonomy available?

[=?iso-8859-1?Q?Markus_D=F6ring?=]

Hi Kim & Erik,

I've dumped the GBIF backbone from our postgres 9.3 server and placed it here (427MB):
https://dl.dropboxusercontent.com/u/457027/nub.sql.zip

The tables should be fairly straight forward with taxa being in "name_usage" and names in "name".
A simple join to get the core properties for a taxon would be this sql:

	select u.id, parent_fk,n.scientific_name, rank, status, p.citation as publlishedIn from name_usage u join name n on name_fk=n.id left join citation p on name_published_in_fk=p.id order by rank limit 100;

The backbone is the current live one with the higher classification coming from the Catalog of Life.
Quick facts:

  taxa incl synonyms:  4.416.347
  synonyms:  1.143.026
  names: 4.410.899
  accepted taxa by rank:
	KINGDOM: 9
	PHYLUM: 111
	CLASS: 272
	ORDER: 1183
	SUPERFAMILY: 356
	FAMILY: 20760
	GENUS: 390790
	SPECIES: 2497114
	SUBSPECIES: 171473
	VARIETY: 149011
	SUBVARIETY: 1070
	FORM: 40473


best,
Markus


--
Markus D?ring
Software Developer
Global Biodiversity Information Facility (GBIF)
mdoering at gbif.org
http://www.gbif.org


On 15 Jan 2014, at 10:59, Markus D?ring <mdoering at gbif.org> wrote:

> Ok, I will prepare a dump over the next days then.
> Regular dumps are currently out of scope I am afraid as I manually need to remove other data which we keep in the same database. But if postgres dumps would be a useful thing for more people we can try to setup a process in the long run.
> 
> best,
> Markus
> 
> 
> On 15 Jan 2014, at 01:39, Erik Rijkers <er at xs4all.nl> wrote:
> 
>> On Wed, January 15, 2014 01:20, Markus D?ring wrote:
>>> Hi Kim,
>>> if useful I could also provide a postgresql dump of the GBIF backbone in addition to the dwc archive already available.
>>> We use a parent child relationship in GBIF with an additional nested set index to support quick descendants queries.
>>> 
>> 
>> Good to hear GBIF is using a real database system (postgres) :-)
>> 
>> But seriously, I would be very interested to be able to use dowload such a dump and use it locally.  And it would be
>> absolutely fantastic if some sort of regular download of updated dumps could be made available.   But I would already be
>> very thankful for a one-off download.
>> 
>> thanks,
>> 
>> Erik Rijkers
>> Utrecht, Netherlands
>> 
>> 
>