When should occurrences table be updated to match authorities?

[aazaff]

I have created the following scenarios using collection_no 3000 and collection_no 30001 on the dev server.

SCENARIO 1

I have added the following taxa via the client edit-occurrences to collection 3000.

1. Virgiana decussata
2. Virgiana decussatredux
3. Virgiana sp.
4. Virgiana bobii

Here is how the added occurrences look in the db after adding through the form.

SELECT taxon_no, genus_name, species_name 
FROM occurrences WHERE collection_no = 3000;

occurrence_no	taxon_no	genus_name	species_name	Result
39163	309830	Virgiana	decussata	Virgiana decussata is matched to pre-existing species Virgiana decussata in the Authorities table (taxon_no 309830)
1662178	28329	Virgiana	decussatredux	Virgiana decussataredux does not exist in the database and is matched to closest name - genus Virgiana (taxon_no 28329)
1662179	28329	Virgiana	sp.	Virgiana is matched to genus Virgiana (taxon_no 28329)
1662180	28329	Virgiana	bobii	Virgiana bobii does not exist in the database and is matched to closest name - genus Virgiana (taxon_no 28329).

All of this matches my expectations. Note the following: the species name is NOT changed to sp. for the species only matched to genus. The taxon names (i.e., genus and species fields) retain whatever was originally entered.

SCENARIO 2

I used the edit taxon form to change the name for Virgiana decussata to now be Virgiana decussataredux. This change is reflected by a change in the Authorities table as shown below.

SELECT taxon_no, taxon_name 
FROM authorities WHERE taxon_no = 309830;

taxon_no	taxon_name
309830	Virgiana decussataredux

What you might expect after this change is the following:

1. The taxon_no for occurrence_no 39163 will change from 309830 to 28329 because Virgiana decussata no longer has an authority.
2. The taxon_no for occurrence_no 1662178 will change from 28329 to 309830 because Virgiana decussataredux now has an authority.

However, NEITHER of these things happen and the occurrences table stays exactly as it was before the name change in the authorities table.

SELECT taxon_no, genus_name, species_name 
FROM occurrences WHERE collection_no = 3000;

occurrence_no	taxon_no	genus_name	species_name	notes
39163	309830	Virgiana	decussata	Virgiana decussata is matched to pre-existing species Virgiana decussata in the Authorities table (taxon_no 309830)
1662178	28329	Virgiana	decussatredux	Virgiana decussataredux does not exist in the database and is matched to closest genus Virgiana (taxon_no 28329)
1662179	28329	Virgiana	sp.	Virgiana is matched to genus Virgiana (taxon_no 28329)
1662180	28329	Virgiana	bobii	Virgiana bobii does not exist in the database and is matched to closest genus Virgiana (taxon_no 28329).

SCENARIO 3

I went to collection 3001 and add new occurrences for Virgiana decussata and Virgiana decussataredux.

SELECT occurrence_no, taxon_no, genus_name, species_name 
FROM occurrences WHERE collection_no = 3001;

occurrence_no	taxon_no	genus_name	species_name	notes
39187	309830	Virgiana	decussata	Ignore this, we will come back to this later
39188	28329	Virgiana	bobii	Virgiana bobii does not exist in the database and is matched to closest genus Virgiana (taxon_no 28329)
1662181	309830	Virgiana	decussata	matched to taxon 309830 even though the name no longer has a match in the authorities table
1662182	309830	Virgiana	decussataredux	matched to taxon 309830 based on exact match to authorities table.

The taxon_no matching procedure now matches BOTH Virgiana decussata and Virgiana decussataredux to taxon_no 309830. This seems inconsistent with the behavior observed in SCENARIO 2.

[aazaff]

SCENARIO 4

I have now added a new authority record for previous non-existent species Virgiana bobii using the form. The result can now be seen in the Authorities table.

SELECT taxon_no, taxon_name 
FROM authorities WHERE taxon_name = 'Virgiana bobii';

taxon_no	taxon_name
497313	Virgiana bobii

Looking at how this affected the occurrences of Virgiana bobii in collections 3000 and 3001 we do see that the taxon_no is updated, which is the behavior we would expect.

SELECT occurrence_no, taxon_no, genus_name, species_name 
FROM occurrences WHERE occurrence_no = 1662180 OR occurrence_no = 39188

occurrence_no	taxon_no	genus_name	species_name
39188	497313	Virgiana	bobii
1662180	497313	Virgiana	bobii

[aazaff]

SCENARIO 5

None of the above actions affected the reidentifications table. I experimented with re-identifications by using the Reidentify occurrences form.

I changed Virgiana decussata in collection_no 3000 to Virgiana decussataredux. This change is reflected when viewed in the client as follows.

The way this is reflected in the database is that the name provided in the occurrence record is NOT changed. However, a new FKEY to a new record in the reidentifications table is added.

SELECT occurrence_no, taxon_no, genus_name, species_name, reid_no 
FROM occurrences WHERE collection_no = 3000;

occurrence_no	taxon_no	genus_name	species_name	reid_no
39163	309830	Virgiana	decussata	41941
1662178	28329	Virgiana	decussatredux	NULL
1662179	28329	Virgiana	sp.	NULL
1662180	497313	Virgiana	bobii	NULL

If we interrogate this reidentification record it looks like so.

SELECT reid_no, occurrence_no, taxon_no, genus_name, species_name 
FROM reidentifications WHERE reid_no = 41941;

reid_no	occurrence_no	taxon_no	genus_name	species_name
41941	39163	309830	Virgiana	decussataredux

This behavior makes sense.

SCENARIO 6

Based on the observations in SCENARIO 5, I tried to interrogate how reidentifications play with the addition/revision of authorities. Again I used the Reidentify occurrences form.

I changed Virgiana bobii in collection_no 3000 to a new taxon with no authority - Virgiana winstonii

SELECT reid_no, occurrence_no, taxon_no, genus_name, species_name 
FROM reidentifications WHERE reid_no = 41942;

reid_no	occurrence_no	taxon_no	genus_name	species_name
41942	1662180	28329	Virgiana	winstonii

As expected, the taxon_no matched to Virgiana winstonii is 28329 which is for the genus Virgiana since there is no species-level match in the authorities table.

Next, I see what happens if I now ADD an authority for Virgiana winstonii using the form.

SELECT taxon_no, genus_name, species_name, reid_no 
FROM occurrences WHERE collection_no = 3000;

taxon_no	genus_name	species_name	reid_no	notes
309830	Virgiana	decussata	41941
28329	Virgiana	decussatredux	0
28329	Virgiana	sp.	0
497313	Virgiana	bobii	41942	The occurrence information and taxon_no is unchanged, only the reid_no is added

The occurrence table is completely unchanged, but the taxon_no in reidentification IS updated to the new number. Next we look at the reidentifications table.

SELECT reid_no, occurrence_no, taxon_no, genus_name, species_name 
FROM reidentifications WHERE reid_no = 41942;

reid_no	occurrence_no	taxon_no	genus_name	species_name
41942	1662180	497314	Virgiana	winstonii

This means that that the Authorities endpoint for the API needs to ALSO search/edit the reidentifications table.

[aazaff]

SCENARIO 7

I next attempted to see how resolution modifiers - e.g., [open taxonomy] affected taxon_no assignment. (https://www.palass.org/sites/default/files/media/publications/palaeontology/volume_31/vol31_part1_pp223-227.pdf) and open nomeclature

I created the following taxon list in collection_no 3002.

1. ? Virgiana falseii
2. Virgiana aff. falseii
3. Virgiana cf. falseii
4. Virgiana n. sp.
5. Virgiana sp. nov. error thrown
6. Virgiana stet. error thrown
7. Virgiana ? falseii
8. Virgiana falseii incert. error thrown

SELECT occurrence_no, taxon_no, genus_name, genus_reso, species_name, species_reso 
FROM occurrences WHERE collection_no = 3002;

occurrence_no	taxon_no	genus_name	genus_reso	species_name	species_reso
39189	28329	Virgiana	?	falseii	NULL
39190	28329	Virgiana	NULL	falseii	aff.
1662185	28329	Virgiana	NULL	falseii	cf.
1662186	28329	Virgiana	NULL	falseii	?

Notice that all taxa are assigned to genus Virgiana (taxon_no 28329), which is the expected behavior. The next question is to see how the taxon_no reassignment is affected by creating an authority for Virgiana falseii

SELECT occurrence_no, taxon_no, genus_name, genus_reso, species_name, species_reso 
FROM occurrences WHERE collection_no = 3002;

occurrence_no	taxon_no	genus_name	genus_reso	species_name	species_reso
39189	497315	Virgiana	?	falseii	NULL
39190	497315	Virgiana	NULL	falseii	aff.
1662185	497315	Virgiana	NULL	falseii	cf.
1662186	497315	Virgiana	NULL	falseii	?

Interestingly, the uncertainties/resolutions had NO effect whatsoever. All of the occurrences were reassigned with the new taxon_no for new authority Virgiana falseii (taxon_no 497315)

[aazaff]

SCENARIO 8

I next attempted to see how the application logic handles the case of taxonomic homonyms - Ficus sp. can be the [sea snail](https://en.wikipedia.org/wiki/Ficus_(gastropod) or the fig plant. Both are legitimate taxa, but a user only ever means one of these.

I did the following testing in Collection 3003. I entered Ficus sp. twice. and received an error. However, conceivably (though unlikely) there could be one instance each of the snail and the plant, so this is an edge case not properly handled by the current system.

1. Ficus sp.

The system correctly flags that Ficus cannot be placed because the provided information is not enough to know if it is the plant or the snail. This takes you to an intermediate form where the user is meant to select the correct choice from a dropdown.

Importantly, how this happens operationally is that the occurrence edit goes through but a taxon_no of 0 (NA) is assigned. The user is then redirected to the reclassification form (picture above). This means that we should NOT throw an error but should allow the homonym through but provide a warning message so the client knows this occurrence should be classified manually.

[NoisyFlowers]

I think the surprise result of Scenario 2 is a bug that occurs because of this code for updating occurrences in authority.model.js (this is my translation of the original perl code, the bug exists there):

        const sqlQueries = [
            `SELECT 
                occurrence_no,
                o.taxon_no,
                genus_name,
                subgenus_name,
                species_name,
                taxon_name,
                taxon_rank 
            FROM 
                occurrences o 
                LEFT JOIN 
                    authorities a ON o.taxon_no=a.taxon_no
            WHERE 
                genus_name IN (${higherNames.reduce((nameStr, name, idx) => {
                    if (idx === 0) return `"${name}"`
                    else return `${nameStr}, "${name}"`
                }, "")})`, 
            `SELECT 
                reid_no,
                re.taxon_no,
                genus_name,
                subgenus_name,
                species_name,
                taxon_name,
                taxon_rank            
            FROM 
                reidentifications re 
                LEFT JOIN 
                    authorities a ON re.taxon_no=a.taxon_no
            WHERE 
                genus_name IN (${higherNames.reduce((nameStr, name, idx) => {
                    if (idx === 0) return `"${name}"`
                    else return `${nameStr}, "${name}"`
                }, "")})`,
            `SELECT 
                occurrence_no,
                o.taxon_no,
                genus_name,
                subgenus_name,
                species_name,
                taxon_name,
                taxon_rank 
            FROM 
                occurrences o 
                LEFT JOIN 
                    authorities a ON o.taxon_no=a.taxon_no
            WHERE 
                subgenus_name IN (${higherNames.reduce((nameStr, name, idx) => {
                    if (idx === 0) return `"${name}"`
                    else return `${nameStr}, "${name}"`
                }, "")})`,
            `SELECT 
                reid_no,
                re.taxon_no,
                genus_name,
                subgenus_name,
                species_name,
                taxon_name,
                taxon_rank            
            FROM 
                reidentifications re 
                LEFT JOIN 
                    authorities a ON re.taxon_no=a.taxon_no
            WHERE 
                subgenus_name IN (${higherNames.reduce((nameStr, name, idx) => {
                    if (idx === 0) return `"${name}"`
                    else return `${nameStr}, "${name}"`
                }, "")})`    
        ];
        if (parsedTaxon.species) {
            sqlQueries[0] = `${sqlQueries[0]} AND species_name LIKE "${parsedTaxon.species}"`;
            sqlQueries[1] = `${sqlQueries[1]} AND species_name LIKE "${parsedTaxon.species}"`;
            sqlQueries[2] = `${sqlQueries[2]} AND species_name LIKE "${parsedTaxon.species}"`;
            sqlQueries[3] = `${sqlQueries[3]} AND species_name LIKE "${parsedTaxon.species}"`;
        }

Any occurrences record with the same genus should be checked against the new authority record to see if it is a better match. But that bit near the end adds a comparison with the new species name to the WHERE clause, so all that is checked are records with that name.

[NoisyFlowers]

There's more going on with Scenario 2 than the previous comment.

I commented out the species bit at the end, and that does, indeed, cause all Microtus occurrence records to be checked. But it still does not cause an update to occurrence records that go out of date when a taxon_name is changed in the authorities table. This seems to be because of the matching algorithm in authority.model.js computeMatchLevel (which is a direct translation of computeMatchLevel in the perl code in Taxon.pm).

For instance, given authority and occurrence records for Microtus poopyhead, changing that authority record's taxon_name to Microtus boogerus does cause that occurrence record to be checked, but computeMatchLevel yields a matchValue of 0, so no change is made to the occurrence record.

We expected the taxon_no in the occurrence recode to be changed to that of the Genus. Actually, the more I think about this, I don't see any mechanism in the perl code to do that. It's only checking to see if there are occurrence records to which the authority's taxon_no should be applied, not records from which it should be removed. Are we expecting too much?

[aazaff]

I feel it has to be a bug and/or oversight one way or the other given that the behavior in Scenario 3 doesn't match. Either Scenario 2 is wrong or Scenario 3 is wrong is my feeling. I say we proceed under the assumption Scenario 2 is wrong and that we are going to change it work as we intuitively think it should - i.e., taxon numbers should be disassociated if the authority changes. If they tell us later that it wasn't right then we can change that.

[NoisyFlowers]

For handling of scenario 8, we decided to thrown and error on homonym detection. We also added a bypassOccurrences parameter, which the user can set and resubmit the request. This causes the code to skip the occurrence update. The client code can then take the user wherever it wants to provide occurrence assignment.

Reading the commit comments, I realize they aren't very clear. The code in question looks like this:

            if (!bypassOccurrences) {
                if (await isHomonym(conn, authority)) {
                    const error = new Error(`${authority.taxon_name} is a homonym. Occurrences cannot be updated. If this was intentional, then resubmit with bypassOccurrences set to true and reclassify occurrences manually.`);
                    error.statusCode = 409;
                    throw error;
                } else {
                    await updateOccurrences(conn, authority)
                }
            }

This is in
42d573b
e788abc

[aazaff]

SCENARIO 9

Time to start messing with opinions. First, we try to edit opinions for Virgiana decussata from SCENARIO 1. We get an error that this taxon does not exist. This is consistent with the fact that we renamed the taxon -- through an Authorities change -- to Virgiana decussatredux in SCENARIO 2. If we do an opinion search for V. decussatredux we get a number of hits. This means that the Authorities record rename we made earlier propagated to relevant Opinions.

This makes perfect sense because the Opinions table doesn't record names at all, it just FKEYS to the Authorities table and since we change the Authorities table it updates accordingly.

[aazaff]

SCENARIO 10

The result in SCENARIO 9 shows why users will generally be trained NOT to change taxon names through an edit to the Authorities table. Instead, in the vast majority of cases we will want them to CREATE A NEW TAXON and then create an OPINION that the new name SUPERSEDES the old.

As a test, I create a new opinion that V. bobii is an OBJECTIVE SYNONYM of V. decussataredux -- (see SCENARIO 2 for the first instances of these taxa). This opinion goes through correctly.

From the client side, what happens is that the page for V. bobii disappears and now a note is placed on the V. decussataredux page that V. bobii is an objective synonym.

On the database side, we see the following changes:

1. Authorities Table: NO CHANGE/UPDATES needed
2. Occurrences TABLE: NO CHANGE/UPDATES needed

This is great news because it means that changes to Opinions only affect the Opinions table.