higher_plant_group node names in Tropicos/APGIII¶

• see Brad's higherPlantGroup.xlsx

from Brad:

> how you derived the subclass lookup table for APGIII, in case we ever want to regenerate it on an updated version of APGIII

-- descendent lookup for class "Equisetopside" 
-- subclasses only
SELECT nameRank, scientificName
FROM name n JOIN classification c
ON n.nameID=c.nameID
WHERE
leftIndex>=
(
SELECT leftIndex
FROM name n JOIN classification c
ON n.nameID=c.nameID
WHERE scientificName='Equisetopsida'
AND c.sourceID=1
)
AND
rightIndex<=
(
SELECT rightIndex
FROM name n JOIN classification c
ON n.nameID=c.nameID
WHERE scientificName='Equisetopsida'
AND c.sourceID=1
)
AND sourceID=1
AND nameRank IN ('subclass')
ORDER BY leftIndex;

from Brad:

> Could you please translate our NCBI higher_plant_group node names to Tropicos/APGIII?

It *think* you should be able to do this by querying subclasses for source Tropicos (sourceID=1) in the tnrs database. See the attached spreadsheet of mappings between APG III (=Tropicos) subclasses and BIEN higherPlantGroup.

To query higher taxa using the TNRS database, you need join table name to table classification. Be sure to set sourceID=1.

Here is how you would query the ancestor of rank 'subclass' using scientific name. This is for demonstration purposes only: it is not safe to query by scientificName alone due to possible homonyms (each subquery MUST return one value only).

SELECT nameRank, scientificName
FROM name n JOIN classification c
ON n.nameID=c.nameID
WHERE
leftIndex<=
(
SELECT leftIndex
FROM name n JOIN classification c
ON n.nameID=c.nameID
WHERE scientificName='Billia hippocastanum'
AND c.sourceID=1
)
AND
rightIndex>=
(
SELECT rightIndex
FROM name n JOIN classification c
ON n.nameID=c.nameID
WHERE scientificName='Billia hippocastanum'
AND c.sourceID=1
)
AND sourceID=1
AND nameRank='subclass';

+----------+----------------+
| nameRank | scientificName |
+----------+----------------+
| subclass | Magnoliidae    |
+----------+----------------+
1 row in set (0.02 sec)

In general, however, it is safer to use the TNRS nameID:

mysql> SELECT n.nameID FROM name n JOIN name_source ns ON n.nameID=ns.nameID WHERE scientificName='Billia hippocastanum' AND ns.sourceID=1;
+--------+
| nameID |
+--------+
| 263948 |
+--------+
1 row in set (0.29 sec)

SELECT nameRank, scientificName
FROM name n JOIN classification c
ON n.nameID=c.nameID
WHERE
leftIndex<=
(
SELECT leftIndex
FROM classification
WHERE nameID=263948
AND c.sourceID=1
)
AND
rightIndex>=
(
SELECT rightIndex
FROM classification
WHERE nameID=263948
AND c.sourceID=1
)
AND sourceID=1
AND nameRank='subclass';

+----------+----------------+
| nameRank | scientificName |
+----------+----------------+
| subclass | Magnoliidae    |
+----------+----------------+
1 row in set (0.01 sec)

You can look up subclasses for multiple species at once by using a cross-product with a table containing multiple nameIDs. For example, given the following species:
SELECT n.nameID, n.scientificName, n.scientificNameAuthorship
FROM name n JOIN name_source ns
ON n.nameID=ns.nameID
WHERE  ns.sourceID=1
AND n.scientificName LIKE 'Billia%'
AND n.nameRank='species';

+--------+----------------------+-----------------------------------------+
| nameID | scientificName       | scientificNameAuthorship                |
+--------+----------------------+-----------------------------------------+
| 263940 | Billia columbiana    | Planch. & Linden                        |
| 263948 | Billia hippocastanum | Peyr.                                   |
| 875259 | Billia rosea         | (Planch. & Linden) C. Ulloa & P. Jørg.  |
+--------+----------------------+-----------------------------------------+
3 rows in set (0.00 sec)

You can look up subclasses as follows:

SELECT spp.nameID, subcl.scientificName AS subclass
FROM
(
SELECT n.nameID, c.leftIndex, c.rightIndex
FROM name n JOIN classification c
ON n.nameID=c.nameID
WHERE  c.sourceID=1
AND n.scientificName LIKE 'Billia%'
AND n.nameRank='species'
) AS spp,
(
SELECT nameRank, scientificName, leftIndex, rightIndex
FROM name n JOIN classification c
ON n.nameID=c.nameID
WHERE sourceID=1
AND n.nameRank='subclass'
) AS subcl
WHERE spp.leftIndex>subcl.leftIndex
AND spp.rightIndex<subcl.rightIndex;

+--------+-------------+
| nameID | subclass    |
+--------+-------------+
| 263940 | Magnoliidae |
| 263948 | Magnoliidae |
| 875259 | Magnoliidae |
+--------+-------------+
3 rows in set (0.00 sec)

Finally, you can look up the TNRS nameID using either the Tropicos name url or the Tropicos nameID; both are stored in table name_source:

mysql> SELECT n.nameID, n.scientificName, nameSourceOriginalID, nameSourceUrl
    -> FROM name n JOIN name_source ns
    -> ON n.nameID=ns.nameID
    -> WHERE sourceID=1
    -> AND n.nameID=263948;
+--------+----------------------+----------------------+---------------------------------------+
| nameID | scientificName       | nameSourceOriginalID | nameSourceUrl                         |
+--------+----------------------+----------------------+---------------------------------------+
| 263948 | Billia hippocastanum | 15500014             | http://www.tropicos.org/Name/15500014 |
+--------+----------------------+----------------------+---------------------------------------+
1 row in set (0.00 sec)