2022-05-16 from Marina Frants mfrants@ucsd.edu:
Hi Ben. Â Here's a pg_dump of the current state of my calcofi database. Â A few explanatory notes: The table new_species_codes links to egg_counts and larvae_counts through the spccode field. Â It replaces the species_codes table, and contains additional information such as scientific name and taxonomy rank. The table taxa_hierarchy expands on new_species_codes by adding an ltree index field with the full taxonomy for each entry. Â This allows for hierarchical searches (i.e., search for a given species as well as any family/genus/etc. containing that species.). Â It's connected to the new_species_codes through the tsn and itis_tsn fields. For convenience, there's also a taxon_table, which has short (single-node) paths for every taxonomic level above Species that currently exists in the db. Â This can be useful if a user wants to search for, say, all egg or larvae counts for a given family. Â We can look up the short path for that family in taxon_table, then search taxa_hierarchy for all paths containing that short path. I think everything else is pretty self-explanatory. Â Let me know if you have questions. Cheers, Marina