---------- Forwarded message --------- From: Ed Weber - NOAA Federal ed.weber@noaa.gov Date: Tue, Jun 25, 2024 at 4:00 PM Subject: NOAA ichthyoplankton taxonomy To: Ben Best ben@ecoquants.com
Hi Ben, Erin said this might save you a little time. Here is the species info from our db. Species is the table that is foreign-key linked to all the catch info, e.g., the taxa that we have actually captured. The two lookup tables (ITIS and WORMS) have all the intermediate taxa up to Animalia (or Biota for WORMS) to allow for hierarchical queries. The node columns are the text form of SQL Server's hierarchyId, e.g. /, /1/, /1/1/, /1/2/, ... I think the equivalent Postgres data type is a little different but you could probably do a conversion pretty easily from this.
There is one invalid AphiaId in the WORMS lookup. They list Leptocephalus, 269654, as invalid because it's a synonym but haven't moved Leptocephalus giganteus, 314780, which they say is valid. So it's impossible to build a complete valid hierarchy. This broke my script and I had to add an exception until they straighten it out (you'd think a taxonomic authority would get this right. I mean, they had one job...).
Take care, Ed
Edward D. Weber, Ph.D. Research Fisheries Biologist NOAA Southwest Fisheries Science Center 8901 La Jolla Shores Drive La Jolla, CA 92037-1508, U.S.A. (858) 546-5676 ed.weber@noaa.gov