Recursive Arrays 101

On 10/25/2015 08:48 AM, David Blomstrom wrote:

I'm creating a website focusing on living things (mostly animals). I
have multiple huge MySQL database tables with animal taxons arranged in
a parent-child relationship. I was trying to figure out how I could
navigate to a URL like MySite/life/mammals and display the number of
children (i.e. orders), grandchildren (families), great grandchildren
(genera) and great great grand children (species).

I was then steered towards some sort of MySQL substitute for a full
outer join (which can apparently only be done in Postgre), followed by
an introduction to stored procedures. Pretty complicated stuff.

Then someone told me it's stupid to jump through all those hoops when
you can easily do that sort of thing with Postgre.

So that's my specific goal - to set up my animals website so it can
quickly and efficiently calculate and display things like grandchildren,
great grandparents, the number of children that are extinct, etc.

My database tables look something like this, where Taxon, Parent and
ParentID are the names of the key fields:

Taxon | Parent | ParentID
Animalia | Life | (NULL)
Chordata | Animalia | (NULL)
Animalia | Chordata | 0
Mammalia | Animalia | 1
Carnivora | Mammalia | 2
Felidae | Carnivora | 3
Panthera | Felidae | 2
Panthera-leo | Panthera | 1
Panthera-tirgis | Panthera | 1

I am not entirely following the above. Could you post the actual table
definitions?

Is that table structure sufficient for PostgreSQL to calculate
grand-children, etc., or will I have to modify it? I think the key words
are "hierarchical query" and/or "nested set." There's a popular tutorial
(though I can't find it at the moment) that illustrates the procedure,
which involves creating TWO numerical fields - a process that I think
would be overwhelming when working with over 50,000 taxonomic names.

So that's my question; can I do all this recursive stuff in Postgre with
the table structure posted above, or will I still have to add a second
numerical column (or otherwise my table)?

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

I'm sorry, I don't know exactly what you mean by "definitions." The fields
Taxon and Parent are both varchar, with a 50-character limit. ParentID is
int(1).

Here's a discussion that describes the table in a little more detail --
http://stackoverflow.com/questions/33248361/hierarchical-query-in-mysql-ii

And this is the discussion where someone suggested I check out PostgreSQL
--
http://stackoverflow.com/questions/33313021/displaying-simple-counts-from-stored-procedure

On Sun, Oct 25, 2015 at 10:59 AM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 10/25/2015 08:48 AM, David Blomstrom wrote:

I'm creating a website focusing on living things (mostly animals). I
have multiple huge MySQL database tables with animal taxons arranged in
a parent-child relationship. I was trying to figure out how I could
navigate to a URL like MySite/life/mammals and display the number of
children (i.e. orders), grandchildren (families), great grandchildren
(genera) and great great grand children (species).

I was then steered towards some sort of MySQL substitute for a full
outer join (which can apparently only be done in Postgre), followed by
an introduction to stored procedures. Pretty complicated stuff.

Then someone told me it's stupid to jump through all those hoops when
you can easily do that sort of thing with Postgre.

So that's my specific goal - to set up my animals website so it can
quickly and efficiently calculate and display things like grandchildren,
great grandparents, the number of children that are extinct, etc.

My database tables look something like this, where Taxon, Parent and
ParentID are the names of the key fields:

Taxon | Parent | ParentID
Animalia | Life | (NULL)
Chordata | Animalia | (NULL)
Animalia | Chordata | 0
Mammalia | Animalia | 1
Carnivora | Mammalia | 2
Felidae | Carnivora | 3
Panthera | Felidae | 2
Panthera-leo | Panthera | 1
Panthera-tirgis | Panthera | 1

I am not entirely following the above. Could you post the actual table
definitions?

Is that table structure sufficient for PostgreSQL to calculate
grand-children, etc., or will I have to modify it? I think the key words
are "hierarchical query" and/or "nested set." There's a popular tutorial
(though I can't find it at the moment) that illustrates the procedure,
which involves creating TWO numerical fields - a process that I think
would be overwhelming when working with over 50,000 taxonomic names.

So that's my question; can I do all this recursive stuff in Postgre with
the table structure posted above, or will I still have to add a second
numerical column (or otherwise my table)?

--
Adrian Klaver
adrian.klaver@aklaver.com

--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

On 10/25/2015 11:12 AM, David Blomstrom wrote:

I'm sorry, I don't know exactly what you mean by "definitions." The
fields Taxon and Parent are both varchar, with a 50-character limit.
ParentID is int(1).

By definition I meant the schema, so from the below:

CREATE TABLE t (
N INT(6) default None auto_increment,
Taxon varchar(50) default NULL,
Parent varchar(25) default NULL,
NameCommon varchar(50) default NULL,
Rank smallint(2) default 0
PRIMARY KEY (N)
) ENGINE=MyISAM

Here's a discussion that describes the table in a little more detail --
http://stackoverflow.com/questions/33248361/hierarchical-query-in-mysql-ii

And this is the discussion where someone suggested I check out
PostgreSQL --
http://stackoverflow.com/questions/33313021/displaying-simple-counts-from-stored-procedure

Seems to me it would be easier to use what already exists:

Kingdom, Phylum, Class, Order, Family, Genus, and Species.

So.

Kingdom table <--> Phylum table <--> Class table <-->, on down the line.

Where the tables are linked by Foreign Keys(something not possible with
MyISAM).

See:

http://www.postgresql.org/docs/9.5/static/sql-createtable.html

"REFERENCES reftable [ ( refcolumn ) ] [ MATCH matchtype ] [ ON DELETE
action ] [ ON UPDATE action ] (column constraint)
FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn
[, ... ] ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE action
] (table constraint)"

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 25 Oct 2015, at 19:38, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 10/25/2015 11:12 AM, David Blomstrom wrote:

I'm sorry, I don't know exactly what you mean by "definitions." The
fields Taxon and Parent are both varchar, with a 50-character limit.
ParentID is int(1).

By definition I meant the schema, so from the below:

CREATE TABLE t (
N INT(6) default None auto_increment,
Taxon varchar(50) default NULL,
Parent varchar(25) default NULL,
NameCommon varchar(50) default NULL,
Rank smallint(2) default 0
PRIMARY KEY (N)
) ENGINE=MyISAM

That can indeed be solved using a hierarchical query (provided you have a suitable table in PG); something akin to:

WITH RECURSIVE taxons AS (
-- Hierarchical root nodes
SELECT N AS id, Taxon, Rank, 1 AS level, '' || N AS Path -- A useful addition explained further down
FROM t
WHERE ParentID IS NULL

-- Child nodes
UNION ALL
SELECT N AS id, Taxon, Rank, taxons.level +1 AS level, taxons.Path || ':' || N AS Path
FROM taxons
JOIN t ON taxons.id = t.ParentID
)
SELECT id, Taxon, Rank, level
FROM taxons
ORDER BY Path
;

The Path-bit looks complicated, but basically that just appends ID's within the same hierarchy such that, when sorted on that field, you get the hierarchy in their hierarchical order. What the hierarchy would look like if it were shown as a file hierarchy with sub-directories expanded, for example. That's pretty much the only viable alternative (alternatives vary on the column used to create the hierarchy), which is why I added it to the example.

The fun thing with hierarchical queries is that you can add all kinds of extra information and make it trickle down to the child nodes, such as the items that make up the root of the hierarchy (pretty useful for grouping), for example or a field that calculates a string to prepend for indentation, etc. Or a computation that depends on values in parent items (I used this successfully in a bill of materials to calculate absolute quantities by volume, quantities by weight and cost of components in the end product where they were given relative to 1 kg of their parent, for example).

It's highly flexible and powerful (and standard SQL), but it takes a bit of time to get in the right mindset.

PS. I usually write my hierarchical queries in Oracle, which isn't quite as good at them as Postgres is, but it's what we have @work. Hence, I'm not sure I got the syntax 100% correct. We're working on getting PG in for a project upgrade (replacing RDB on OpenVMS, which will go EOL in <10 years!) - fingers crossed.

Cheers!

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

@ Adrian Klaver: Oh, so you're suggesting I make separate tables for
kingdoms, classes and on down to species. I'll research foreign keys and
see what I can come up with. I hope I can make separate tables for mammal
species, bird species, fish species, etc. There are just so many species -
especially fish - the spreadsheets I use to organize them are just about
maxed out as it is.

I've been using the Catalogue of Life as a guide, but I'm limited because I
can never get their downloads to work. So all I can do is go to their
website and copy a bunch of genera and species at a time.

However, I did open up some of the tables I downloaded and was amazed at
how apparently amateurish they are. Yet their site works just fine and is
fast enough.

@ Alban Hertroys: What does EOL mean? It reminds me of Encyclopedia of
Life, which is doing what I was attempting to do years ago.

On Sun, Oct 25, 2015 at 2:07 PM, Alban Hertroys <haramrae@gmail.com> wrote:

On 25 Oct 2015, at 19:38, Adrian Klaver <adrian.klaver@aklaver.com>

wrote:

On 10/25/2015 11:12 AM, David Blomstrom wrote:

I'm sorry, I don't know exactly what you mean by "definitions." The
fields Taxon and Parent are both varchar, with a 50-character limit.
ParentID is int(1).

By definition I meant the schema, so from the below:

CREATE TABLE t (
N INT(6) default None auto_increment,
Taxon varchar(50) default NULL,
Parent varchar(25) default NULL,
NameCommon varchar(50) default NULL,
Rank smallint(2) default 0
PRIMARY KEY (N)
) ENGINE=MyISAM

That can indeed be solved using a hierarchical query (provided you have a
suitable table in PG); something akin to:

WITH RECURSIVE taxons AS (
-- Hierarchical root nodes
SELECT N AS id, Taxon, Rank, 1 AS level, '' || N AS Path -- A
useful addition explained further down
FROM t
WHERE ParentID IS NULL

-- Child nodes
UNION ALL
SELECT N AS id, Taxon, Rank, taxons.level +1 AS level,
taxons.Path || ':' || N AS Path
FROM taxons
JOIN t ON taxons.id = t.ParentID
)
SELECT id, Taxon, Rank, level
FROM taxons
ORDER BY Path
;

The Path-bit looks complicated, but basically that just appends ID's
within the same hierarchy such that, when sorted on that field, you get the
hierarchy in their hierarchical order. What the hierarchy would look like
if it were shown as a file hierarchy with sub-directories expanded, for
example. That's pretty much the only viable alternative (alternatives vary
on the column used to create the hierarchy), which is why I added it to the
example.

The fun thing with hierarchical queries is that you can add all kinds of
extra information and make it trickle down to the child nodes, such as the
items that make up the root of the hierarchy (pretty useful for grouping),
for example or a field that calculates a string to prepend for indentation,
etc. Or a computation that depends on values in parent items (I used this
successfully in a bill of materials to calculate absolute quantities by
volume, quantities by weight and cost of components in the end product
where they were given relative to 1 kg of their parent, for example).

It's highly flexible and powerful (and standard SQL), but it takes a bit
of time to get in the right mindset.

PS. I usually write my hierarchical queries in Oracle, which isn't quite
as good at them as Postgres is, but it's what we have @work. Hence, I'm not
sure I got the syntax 100% correct. We're working on getting PG in for a
project upgrade (replacing RDB on OpenVMS, which will go EOL in <10 years!)
- fingers crossed.

Cheers!

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

On 10/25/2015 6:10 PM, David Blomstrom wrote:

What does EOL mean?

"End of Life"

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 10/25/2015 06:10 PM, David Blomstrom wrote:

@ Adrian Klaver: Oh, so you're suggesting I make separate tables for
kingdoms, classes and on down to species. I'll research foreign keys and
see what I can come up with. I hope I can make separate tables for
mammal species, bird species, fish species, etc. There are just so many
species - especially fish - the spreadsheets I use to organize them are
just about maxed out as it is.

If you go here:

http://www.catalogueoflife.org/col/browse/classification?71dd35ed0e10acf939d0123cdbf9ce57

that is how you can drill down to a species in the CoL.

It just seems to follow what is already there. No doubt, there are a lot
of species. What is probably more important is that the relationships
have changed over time and can be expected to change more, as genetic
testing for the purpose of taxonomic classification becomes more prevalent.

I've been using the Catalogue of Life as a guide, but I'm limited
because I can never get their downloads to work. So all I can do is go
to their website and copy a bunch of genera and species at a time.

Well I downloaded the 2015 snapshot and it turns out it is MySQL
specific. Recently upgraded this computer, will have to see if
MySQL/Mariadb survived the process before I can go any further. It would
be interesting to see how they tackled the relationships.

However, I did open up some of the tables I downloaded and was amazed at
how apparently amateurish they are. Yet their site works just fine and
is fast enough.

@ Alban Hertroys: What does EOL mean? It reminds me of Encyclopedia of
Life, which is doing what I was attempting to do years ago.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

What was amazed me is the HUGE tables (as in too big to work with or
publish online) that, as near as I can remember, have rows like this...

panthera-leo (lion) | Panthera | Felidae | Carnivora | Mammalia | Chordata
| Animalia

cramming virtually the entire hierarchy into every single row. Some of my
tables have extra columns listing every species family and order, which
most people would consider sloppy. But that's tame compared to how they do
it.

I've never been able to make their downloads work on my Mac laptop, and the
PHP is too complex for me to figure out. Nor have they ever replied to my
e-mails. But the websites using their scheme include the Encyclopedia of
Life (EOL).

I'm focusing on creating a polished database focusing on vertebrates, along
with select invertebrates and plants. After I get that squared away, I'd
like to try adding the Catalogue of Life's entire database. The
Encyclopedia of Life and WIkipedia are both enormous projects, but there
are some amazing gaps in both projects that I hope to fill.

On Sun, Oct 25, 2015 at 8:51 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 10/25/2015 06:10 PM, David Blomstrom wrote:

@ Adrian Klaver: Oh, so you're suggesting I make separate tables for
kingdoms, classes and on down to species. I'll research foreign keys and
see what I can come up with. I hope I can make separate tables for
mammal species, bird species, fish species, etc. There are just so many
species - especially fish - the spreadsheets I use to organize them are
just about maxed out as it is.

If you go here:

http://www.catalogueoflife.org/col/browse/classification?71dd35ed0e10acf939d0123cdbf9ce57

that is how you can drill down to a species in the CoL.

It just seems to follow what is already there. No doubt, there are a lot
of species. What is probably more important is that the relationships have
changed over time and can be expected to change more, as genetic testing
for the purpose of taxonomic classification becomes more prevalent.

I've been using the Catalogue of Life as a guide, but I'm limited
because I can never get their downloads to work. So all I can do is go
to their website and copy a bunch of genera and species at a time.

Well I downloaded the 2015 snapshot and it turns out it is MySQL specific.
Recently upgraded this computer, will have to see if MySQL/Mariadb survived
the process before I can go any further. It would be interesting to see how
they tackled the relationships.

However, I did open up some of the tables I downloaded and was amazed at
how apparently amateurish they are. Yet their site works just fine and
is fast enough.

@ Alban Hertroys: What does EOL mean? It reminds me of Encyclopedia of
Life, which is doing what I was attempting to do years ago.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

It's also interesting that some entities (e.g. EOL) are now using something
called Life Science ID's (or something like that) in lieu of traditional
scientific names. It sounds like a cool idea, but some of the LSID's seem
awfully big and complex to me. I haven't figured out exactly what the codes
mean.

Then again, when I navigate to the Encyclopedia of Life's aardvark page @
http://www.eol.org/pages/327830/overview the code is actually amazingly
short.

On Sun, Oct 25, 2015 at 9:04 PM, David Blomstrom <david.blomstrom@gmail.com>
wrote:

What was amazed me is the HUGE tables (as in too big to work with or
publish online) that, as near as I can remember, have rows like this...

panthera-leo (lion) | Panthera | Felidae | Carnivora | Mammalia | Chordata
| Animalia

cramming virtually the entire hierarchy into every single row. Some of my
tables have extra columns listing every species family and order, which
most people would consider sloppy. But that's tame compared to how they do
it.

I've never been able to make their downloads work on my Mac laptop, and
the PHP is too complex for me to figure out. Nor have they ever replied to
my e-mails. But the websites using their scheme include the Encyclopedia of
Life (EOL).

I'm focusing on creating a polished database focusing on vertebrates,
along with select invertebrates and plants. After I get that squared away,
I'd like to try adding the Catalogue of Life's entire database. The
Encyclopedia of Life and WIkipedia are both enormous projects, but there
are some amazing gaps in both projects that I hope to fill.

On Sun, Oct 25, 2015 at 8:51 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 10/25/2015 06:10 PM, David Blomstrom wrote:

@ Adrian Klaver: Oh, so you're suggesting I make separate tables for
kingdoms, classes and on down to species. I'll research foreign keys and
see what I can come up with. I hope I can make separate tables for
mammal species, bird species, fish species, etc. There are just so many
species - especially fish - the spreadsheets I use to organize them are
just about maxed out as it is.

If you go here:

http://www.catalogueoflife.org/col/browse/classification?71dd35ed0e10acf939d0123cdbf9ce57

that is how you can drill down to a species in the CoL.

It just seems to follow what is already there. No doubt, there are a lot
of species. What is probably more important is that the relationships have
changed over time and can be expected to change more, as genetic testing
for the purpose of taxonomic classification becomes more prevalent.

I've been using the Catalogue of Life as a guide, but I'm limited
because I can never get their downloads to work. So all I can do is go
to their website and copy a bunch of genera and species at a time.

Well I downloaded the 2015 snapshot and it turns out it is MySQL
specific. Recently upgraded this computer, will have to see if
MySQL/Mariadb survived the process before I can go any further. It would be
interesting to see how they tackled the relationships.

However, I did open up some of the tables I downloaded and was amazed at
how apparently amateurish they are. Yet their site works just fine and
is fast enough.

@ Alban Hertroys: What does EOL mean? It reminds me of Encyclopedia of
Life, which is doing what I was attempting to do years ago.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

Making it more confusing, I believe there are several different series of
numerical ID's. See this page, for example...
https://www.wikidata.org/wiki/Q46212

On Sun, Oct 25, 2015 at 9:10 PM, David Blomstrom <david.blomstrom@gmail.com>
wrote:

It's also interesting that some entities (e.g. EOL) are now using
something called Life Science ID's (or something like that) in lieu of
traditional scientific names. It sounds like a cool idea, but some of the
LSID's seem awfully big and complex to me. I haven't figured out exactly
what the codes mean.

Then again, when I navigate to the Encyclopedia of Life's aardvark page @
http://www.eol.org/pages/327830/overview the code is actually amazingly
short.

On Sun, Oct 25, 2015 at 9:04 PM, David Blomstrom <
david.blomstrom@gmail.com> wrote:

What was amazed me is the HUGE tables (as in too big to work with or
publish online) that, as near as I can remember, have rows like this...

panthera-leo (lion) | Panthera | Felidae | Carnivora | Mammalia |
Chordata | Animalia

cramming virtually the entire hierarchy into every single row. Some of my
tables have extra columns listing every species family and order, which
most people would consider sloppy. But that's tame compared to how they do
it.

I've never been able to make their downloads work on my Mac laptop, and
the PHP is too complex for me to figure out. Nor have they ever replied to
my e-mails. But the websites using their scheme include the Encyclopedia of
Life (EOL).

I'm focusing on creating a polished database focusing on vertebrates,
along with select invertebrates and plants. After I get that squared away,
I'd like to try adding the Catalogue of Life's entire database. The
Encyclopedia of Life and WIkipedia are both enormous projects, but there
are some amazing gaps in both projects that I hope to fill.

On Sun, Oct 25, 2015 at 8:51 PM, Adrian Klaver <adrian.klaver@aklaver.com

wrote:

On 10/25/2015 06:10 PM, David Blomstrom wrote:

@ Adrian Klaver: Oh, so you're suggesting I make separate tables for
kingdoms, classes and on down to species. I'll research foreign keys and
see what I can come up with. I hope I can make separate tables for
mammal species, bird species, fish species, etc. There are just so many
species - especially fish - the spreadsheets I use to organize them are
just about maxed out as it is.

If you go here:

http://www.catalogueoflife.org/col/browse/classification?71dd35ed0e10acf939d0123cdbf9ce57

that is how you can drill down to a species in the CoL.

It just seems to follow what is already there. No doubt, there are a lot
of species. What is probably more important is that the relationships have
changed over time and can be expected to change more, as genetic testing
for the purpose of taxonomic classification becomes more prevalent.

I've been using the Catalogue of Life as a guide, but I'm limited
because I can never get their downloads to work. So all I can do is go
to their website and copy a bunch of genera and species at a time.

Well I downloaded the 2015 snapshot and it turns out it is MySQL
specific. Recently upgraded this computer, will have to see if
MySQL/Mariadb survived the process before I can go any further. It would be
interesting to see how they tackled the relationships.

However, I did open up some of the tables I downloaded and was amazed at
how apparently amateurish they are. Yet their site works just fine and
is fast enough.

@ Alban Hertroys: What does EOL mean? It reminds me of Encyclopedia of
Life, which is doing what I was attempting to do years ago.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

My ultimate goal is to have separate fields for 1) traditional scientific
names, 2) LSID's and 3) common names, which are the most confusing thing of
all. Some common names are relatively simple and more stable than
scientific names - e.g. aardvark and polar bear. The URL
MySite/life/polar-bear will always point to the same species, even if
scientists reclassified it as a plant or fungus.

But others are more confusing. For example, bison and beaver are both
common names and genus names. (Scientists now recognize two separate
species of beaver, both in the genus Castor.)

I also have to learn how to use the new search function, Elastic, or
whatever it's called. Speaking of which, I just discovered the new Russian
and Chinese search engines, Yandex and Baidu. They have some interesting
possibilities, too. ;)

On Sun, Oct 25, 2015 at 9:12 PM, David Blomstrom <david.blomstrom@gmail.com>
wrote:

Making it more confusing, I believe there are several different series of
numerical ID's. See this page, for example...
https://www.wikidata.org/wiki/Q46212

On Sun, Oct 25, 2015 at 9:10 PM, David Blomstrom <
david.blomstrom@gmail.com> wrote:

It's also interesting that some entities (e.g. EOL) are now using
something called Life Science ID's (or something like that) in lieu of
traditional scientific names. It sounds like a cool idea, but some of the
LSID's seem awfully big and complex to me. I haven't figured out exactly
what the codes mean.

Then again, when I navigate to the Encyclopedia of Life's aardvark page @
http://www.eol.org/pages/327830/overview the code is actually amazingly
short.

On Sun, Oct 25, 2015 at 9:04 PM, David Blomstrom <
david.blomstrom@gmail.com> wrote:

What was amazed me is the HUGE tables (as in too big to work with or
publish online) that, as near as I can remember, have rows like this...

panthera-leo (lion) | Panthera | Felidae | Carnivora | Mammalia |
Chordata | Animalia

cramming virtually the entire hierarchy into every single row. Some of
my tables have extra columns listing every species family and order, which
most people would consider sloppy. But that's tame compared to how they do
it.

I've never been able to make their downloads work on my Mac laptop, and
the PHP is too complex for me to figure out. Nor have they ever replied to
my e-mails. But the websites using their scheme include the Encyclopedia of
Life (EOL).

I'm focusing on creating a polished database focusing on vertebrates,
along with select invertebrates and plants. After I get that squared away,
I'd like to try adding the Catalogue of Life's entire database. The
Encyclopedia of Life and WIkipedia are both enormous projects, but there
are some amazing gaps in both projects that I hope to fill.

On Sun, Oct 25, 2015 at 8:51 PM, Adrian Klaver <
adrian.klaver@aklaver.com> wrote:

On 10/25/2015 06:10 PM, David Blomstrom wrote:

@ Adrian Klaver: Oh, so you're suggesting I make separate tables for
kingdoms, classes and on down to species. I'll research foreign keys
and
see what I can come up with. I hope I can make separate tables for
mammal species, bird species, fish species, etc. There are just so many
species - especially fish - the spreadsheets I use to organize them are
just about maxed out as it is.

If you go here:

http://www.catalogueoflife.org/col/browse/classification?71dd35ed0e10acf939d0123cdbf9ce57

that is how you can drill down to a species in the CoL.

It just seems to follow what is already there. No doubt, there are a
lot of species. What is probably more important is that the relationships
have changed over time and can be expected to change more, as genetic
testing for the purpose of taxonomic classification becomes more prevalent.

I've been using the Catalogue of Life as a guide, but I'm limited
because I can never get their downloads to work. So all I can do is go
to their website and copy a bunch of genera and species at a time.

Well I downloaded the 2015 snapshot and it turns out it is MySQL
specific. Recently upgraded this computer, will have to see if
MySQL/Mariadb survived the process before I can go any further. It would be
interesting to see how they tackled the relationships.

However, I did open up some of the tables I downloaded and was amazed
at
how apparently amateurish they are. Yet their site works just fine and
is fast enough.

@ Alban Hertroys: What does EOL mean? It reminds me of Encyclopedia of
Life, which is doing what I was attempting to do years ago.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

On 10/25/2015 09:10 PM, David Blomstrom wrote:

It's also interesting that some entities (e.g. EOL) are now using
something called Life Science ID's (or something like that) in lieu of
traditional scientific names. It sounds like a cool idea, but some of
the LSID's seem awfully big and complex to me. I haven't figured out
exactly what the codes mean.

Aah, the natural key vs surrogate key conversation rears its head.

Then again, when I navigate to the Encyclopedia of Life's aardvark page
@ http://www.eol.org/pages/327830/overview the code is actually
amazingly short.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

LOL - I don't think there are any natural keys here. Traditional scientific
names are amazingly flaky. I guess I shouldn't call them flaky; it's just
that no one has ever figured out a way do deal with all the complexities of
classification. The new LSID's might be more stable - but which LSID does
one choose? But it's amazing how many "aliases" are attached to many
taxonomic names; utterly bewildering.

On Sun, Oct 25, 2015 at 10:09 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 10/25/2015 09:10 PM, David Blomstrom wrote:

It's also interesting that some entities (e.g. EOL) are now using
something called Life Science ID's (or something like that) in lieu of
traditional scientific names. It sounds like a cool idea, but some of
the LSID's seem awfully big and complex to me. I haven't figured out
exactly what the codes mean.

Aah, the natural key vs surrogate key conversation rears its head.

Then again, when I navigate to the Encyclopedia of Life's aardvark page
@ http://www.eol.org/pages/327830/overview the code is actually
amazingly short.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org

I would suggest that you use int or bigint for primary keys, and have
mapping tables to convert the scientific term to the surrogate primary key.

If the mapping table has additional attributes, like date of change &
reason, then you can also print a history of changes.

Then the relationships between tables will be more isolated from changes
in scientific nomenclature! Plus if the same animals known by different
scientific names at different times, you can have several mappings to
the same animal. Also if an organism is moved from one phylum to
another, you can find the organism via either new or old references.
I've heard of cases were one species, is suddenly found to be 2 or
distinct species!

Cheers,
Gavin

On 26/10/15 18:19, David Blomstrom wrote:

LOL - I don't think there are any natural keys here. Traditional
scientific names are amazingly flaky. I guess I shouldn't call them
flaky; it's just that no one has ever figured out a way do deal with
all the complexities of classification. The new LSID's might be more
stable - but which LSID does one choose? But it's amazing how many
"aliases" are attached to many taxonomic names; utterly bewildering.

On Sun, Oct 25, 2015 at 10:09 PM, Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:

On 10/25/2015 09:10 PM, David Blomstrom wrote:

It's also interesting that some entities (e.g. EOL) are now using
something called Life Science ID's (or something like that) in
lieu of
traditional scientific names. It sounds like a cool idea, but
some of
the LSID's seem awfully big and complex to me. I haven't
figured out
exactly what the codes mean.

Aah, the natural key vs surrogate key conversation rears its head.

Then again, when I navigate to the Encyclopedia of Life's
aardvark page
@ http://www.eol.org/pages/327830/overview the code is actually
amazingly short.

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org <http://www.geobop.org&gt;

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 10/25/15 8:10 PM, David Blomstrom wrote:

@ Adrian Klaver: Oh, so you're suggesting I make separate tables for
kingdoms, classes and on down to species. I'll research foreign keys and
see what I can come up with. I hope I can make separate tables for
mammal species, bird species, fish species, etc. There are just so many
species - especially fish - the spreadsheets I use to organize them are
just about maxed out as it is.

The suggestion is simply to have 7 tables:

CREATE TABLE kingdom(
kingdom_id serial PRIMARY KEY
, kingdom_name text NOT NULL
, ...
);
CREATE TABLE phylum(
phylum_id serial PRIMARY KEY
, kingdom_id int NOT NULL REFERENCES kingdom
, ...
);
CREATE TABLE class(
...
);

and so-on.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Alban Hertroys schrieb am 25.10.2015 um 22:07:

WITH RECURSIVE taxons AS (
-- Hierarchical root nodes
SELECT N AS id, Taxon, Rank, 1 AS level, '' || N AS Path -- A useful addition explained further down
FROM t
WHERE ParentID IS NULL

-- Child nodes
UNION ALL
SELECT N AS id, Taxon, Rank, taxons.level +1 AS level, taxons.Path || ':' || N AS Path
FROM taxons
JOIN t ON taxons.id = t.ParentID
)
SELECT id, Taxon, Rank, level
FROM taxons
ORDER BY Path
;

The Path-bit looks complicated, but basically that just appends ID's within the same hierarchy such that,
when sorted on that field, you get the hierarchy in their hierarchical order.

I always wonder whether it's more efficient to aggregate this path using an array rather than a varchar. Mainly because representing the numbers as varchars will require more memory than as integer, but then I don't know the overhead of an array structure and whether appending to an array doesn't actually copy it.

So "array[n] as path" in the root query and "taxons.path||n" in the recursive part.

Any ideas?

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 10/26/2015 08:43 AM, Jim Nasby wrote:

On 10/25/15 8:10 PM, David Blomstrom wrote:

@ Adrian Klaver: Oh, so you're suggesting I make separate tables for
kingdoms, classes and on down to species. I'll research foreign keys and
see what I can come up with. I hope I can make separate tables for
mammal species, bird species, fish species, etc. There are just so many
species - especially fish - the spreadsheets I use to organize them are
just about maxed out as it is.

The suggestion is simply to have 7 tables:

CREATE TABLE kingdom(
kingdom_id serial PRIMARY KEY
, kingdom_name text NOT NULL
, ...
);
CREATE TABLE phylum(
phylum_id serial PRIMARY KEY
, kingdom_id int NOT NULL REFERENCES kingdom
, ...
);
CREATE TABLE class(
...
);

and so-on.

Seems to me that if life boils down to four attributes one would have a
single table with those four attributes on the particular life form.
Now, the four attributes could be ids into definitional tables but I
suspect the querying will be done string/name so why complicate the
lookups: make the names a foreign key in the defs if necessary.

Personally I think the recursive structure is the way to go.

On 10/26/2015 08:12 AM, Rob Sargent wrote:

On 10/26/2015 08:43 AM, Jim Nasby wrote:

On 10/25/15 8:10 PM, David Blomstrom wrote:

@ Adrian Klaver: Oh, so you're suggesting I make separate tables for
kingdoms, classes and on down to species. I'll research foreign keys and
see what I can come up with. I hope I can make separate tables for
mammal species, bird species, fish species, etc. There are just so many
species - especially fish - the spreadsheets I use to organize them are
just about maxed out as it is.

The suggestion is simply to have 7 tables:

CREATE TABLE kingdom(
kingdom_id serial PRIMARY KEY
, kingdom_name text NOT NULL
, ...
);
CREATE TABLE phylum(
phylum_id serial PRIMARY KEY
, kingdom_id int NOT NULL REFERENCES kingdom
, ...
);
CREATE TABLE class(
...
);

and so-on.

Seems to me that if life boils down to four attributes one would have a
single table with those four attributes on the particular life form.

Out of curiosity what are those four attributes? It would have made
memorizing all those organisms a lot easier when I was in school:)

Now, the four attributes could be ids into definitional tables but I
suspect the querying will be done string/name so why complicate the
lookups: make the names a foreign key in the defs if necessary.

Personally I think the recursive structure is the way to go.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 10/26/2015 09:22 AM, Adrian Klaver wrote:

On 10/26/2015 08:12 AM, Rob Sargent wrote:

On 10/26/2015 08:43 AM, Jim Nasby wrote:

On 10/25/15 8:10 PM, David Blomstrom wrote:

@ Adrian Klaver: Oh, so you're suggesting I make separate tables for
kingdoms, classes and on down to species. I'll research foreign
keys and
see what I can come up with. I hope I can make separate tables for
mammal species, bird species, fish species, etc. There are just so
many
species - especially fish - the spreadsheets I use to organize them
are
just about maxed out as it is.

The suggestion is simply to have 7 tables:

CREATE TABLE kingdom(
kingdom_id serial PRIMARY KEY
, kingdom_name text NOT NULL
, ...
);
CREATE TABLE phylum(
phylum_id serial PRIMARY KEY
, kingdom_id int NOT NULL REFERENCES kingdom
, ...
);
CREATE TABLE class(
...
);

and so-on.

Seems to me that if life boils down to four attributes one would have a
single table with those four attributes on the particular life form.

Out of curiosity what are those four attributes? It would have made
memorizing all those organisms a lot easier when I was in school:)

kingdom phylum class genus as attributes in species table. Talk about
your "natural key". The hibernate boys would love it :)

Now, the four attributes could be ids into definitional tables but I
suspect the querying will be done string/name so why complicate the
lookups: make the names a foreign key in the defs if necessary.

Personally I think the recursive structure is the way to go.

Jtbc, I'm not advocating this structure but it may suit the OP's usage
patterns.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general