Hello,
I’ve got problems to understand, where species designations are kept in Recorder.
I’m running Recorder6.26.2.286, dictionary 0000003X. I have rebuilt the indexes in the given order (that means in particular I rebuilt the ‘Taxon_Designation_Index’ after all the other ones).
My understanding was that the index tables are a shortcut to aggregate several tables into one without losing information. So I thought I can get all designations for all species from the table ‘Index_Taxon_designations’. Therefor I extracted designations for species from Recorder with the following query:
SELECT DISTINCT INDEX_TAXON_NAME.PREFERRED_NAME, Index_Taxon_Designation.Taxon_List_Item_Key, Index_Taxon_Designation.Taxon_Designation_Type_Key, TAXON_DESIGNATION_TYPE.SHORT_NAME FROM (Index_Taxon_Designation LEFT JOIN TAXON_DESIGNATION_TYPE ON Index_Taxon_Designation.Taxon_Designation_Type_Key = TAXON_DESIGNATION_TYPE.TAXON_DESIGNATION_TYPE_KEY) LEFT JOIN INDEX_TAXON_NAME ON Index_Taxon_Designation.Taxon_List_Item_Key = INDEX_TAXON_NAME.TAXON_LIST_ITEM_KEY ORDER BY INDEX_TAXON_NAME.PREFERRED_NAME;
I also used the Recorder report ‘SxBRCDesignationsForTaxon’ and that showed some designations, which I couldn’t see with the query. Therefor I created a query based on the SxBRC report:
SELECT INDEX_TAXON_NAME.TAXON_LIST_ITEM_KEY, INDEX_TAXON_NAME.PREFERRED_NAME, INDEX_TAXON_NAME1.COMMON_NAME, TAXON_GROUP.TAXON_GROUP_NAME, TAXON_DESIGNATION_TYPE.KIND, TAXON_DESIGNATION_TYPE.SHORT_NAME, TAXON_DESIGNATION_TYPE.LONG_NAME, TAXON_DESIGNATION_TYPE.DESCRIPTION, TAXON_DESIGNATION.STATUS_GEOGRAPHIC_AREA, TAXON_DESIGNATION.DATE_FROM, TAXON_DESIGNATION.DATE_TO FROM (((((((INDEX_TAXON_NAME INNER JOIN INDEX_TAXON_GROUP ON INDEX_TAXON_NAME.TAXON_LIST_ITEM_KEY = INDEX_TAXON_GROUP.CONTAINED_LIST_ITEM_KEY) INNER JOIN INDEX_TAXON_NAME AS INDEX_TAXON_NAME1 ON INDEX_TAXON_GROUP.TAXON_LIST_ITEM_KEY = INDEX_TAXON_NAME1.TAXON_LIST_ITEM_KEY) INNER JOIN INDEX_TAXON_NAME AS INDEX_TAXON_NAME2 ON INDEX_TAXON_NAME1.RECOMMENDED_TAXON_LIST_ITEM_KEY = INDEX_TAXON_NAME2.RECOMMENDED_TAXON_LIST_ITEM_KEY) INNER JOIN TAXON_DESIGNATION ON INDEX_TAXON_NAME2.TAXON_LIST_ITEM_KEY = TAXON_DESIGNATION.TAXON_LIST_ITEM_KEY) INNER JOIN TAXON_DESIGNATION_TYPE ON TAXON_DESIGNATION.TAXON_DESIGNATION_TYPE_KEY = TAXON_DESIGNATION_TYPE.TAXON_DESIGNATION_TYPE_KEY) INNER JOIN TAXON_LIST_ITEM ON INDEX_TAXON_NAME.TAXON_LIST_ITEM_KEY = TAXON_LIST_ITEM.TAXON_LIST_ITEM_KEY) INNER JOIN TAXON_VERSION ON TAXON_LIST_ITEM.TAXON_VERSION_KEY = TAXON_VERSION.TAXON_VERSION_KEY) INNER JOIN TAXON_GROUP ON TAXON_VERSION.OUTPUT_GROUP_KEY = TAXON_GROUP.TAXON_GROUP_KEY;
This showed more species with designations which I didn’t get from my first query (one example: Tortula atrovirens; TLIK: NHMSYS0000309047). Now I wonder why that is the case. The principal difference between these two queries is that my query uses the table ‘Index_Taxon_Designation’ while the Sussex query uses the tables ‘Taxon_Designation’ and ‘Taxon_Designation_Type’.
I now have the following questions:
1) Do others have the same differences, when they use these different approaches to extract designations?
2) Is my understanding of the ‘Index_Taxon_Designation’ table as a summary table for all designations of all species wrong?
3) If others see the same differences and my understanding of the index table is correct, has then the process which updates the ‘Index_Taxon_Designation’ table not working properly?
These issues made me have a look into the table ‘Taxon_Designation_Type’. There are some things, which I don’t understand. The table has some designations twice or more, but with slightly different names (‘Badgers-1992’ and ‘Protection_of_Badgers_Act_1992’ (I’ve just seen, that has already been mentioned on the forum, but not solved?), ‘NR’ and ‘NR- Nationally Rare’ and ‘NR-excludes’ and ‘NR-includes’). The ‘Index_Taxon_Designation’ table doesn’t use quite a lot of the designations in the table ‘Taxon_Designation_Type’ especially not the ones which seem to be twice or more in the table.
That might mean I should use the index table and ignore the non-index table if I want to extract designations for species. But then I seem to lose some designations. E.g.: Tortula atrovirens; TLIK: NHMSYS000030904 has the designation ‘NS-excludes’ if I don’t use the index tables and no designation if I use the index tables. If I then compare it to the most recent JNCC list (‘Taxon_designations_20161124.zip’ on http://jncc.defra.gov.uk/page-3408), I don’t find any designation for this species and when I then check the most recent (?) publication about the status of bryophytes in Britain (http://rbg-web2.rbge.org.uk/bbs/Activit … reList.pdf), I find again the designation ‘National Scarce’.
That leads to the second part of my question: How up to date are the designations in Recorder? I know, that there is probably not enough funding to keep the designations in Recorder updated immediately after a change is published by JNCC or somewhere else, but I wouldn’t mind to know at least, how up to date the designations are. I couldn’t find anything about that in Recorder or on the forum.
At the end of the day I want to extract species records from Recorder with the most recent or at least well documented designations. How do other people do that? Do you use the designations in Recorder and if yes, how do you do that? Or do you keep your own designation tables?
Thanks,
Wolfgang