1

Topic: preferred common name

Is there an easy way to get the preferred common name out of the BNBData table via SQL? I can't see an option for the preferred common name in any of the tables.

I've just done a lookup and got Bandit Pipistrelle for Pipistrellus pipistrellus and several other non-British names

Craig

Craig Slawson
Staffordshire Ecological Record

2

Re: preferred common name

I too find the taxa tables taxing. I wrote this query a few years ago. At the time I think I understood it but I'm not hugely confident right now without investing a few hours in a quiet room with it. Give it a whirl and let me know if this works for you (or if it doesn't)

SELECT
    [INDEX_TAXON_NAME].[TAXON_LIST_ITEM_KEY]                                                       AS "taxon_key"
    ,[INDEX_TAXON_NAME].[RECOMMENDED_TAXON_LIST_ITEM_KEY]                                          AS "preferred_taxon_key"
    ,CASE
        WHEN [INDEX_TAXON_NAME].[TAXON_LIST_ITEM_KEY] = [INDEX_TAXON_NAME].[RECOMMENDED_TAXON_LIST_ITEM_KEY] THEN '1' ELSE '0'
    END                                                                                            AS "is_preferred_name"
    ,[TAXON_GROUP].[TAXON_GROUP_NAME]                                                              AS "taxon_group"
    ,[INDEX_TAXON_NAME].[ACTUAL_NAME]                                                              AS "mixed_names"
    ,[INDEX_TAXON_NAME].[COMMON_NAME]                                                              AS "preferred_common_name"
    ,[INDEX_TAXON_NAME].[PREFERRED_NAME]                                                           AS "preferred_scientific_name"
    ,CASE
        WHEN [INDEX_TAXON_NAME].[ABBREVIATION] IS NULL THEN '' ELSE [INDEX_TAXON_NAME].[ABBREVIATION]
    END                                                                                            AS "abbreviations"
    ,[TAXON_RANK].[LONG_NAME]                                                                      AS "taxon_rank"
    ,CASE
        WHEN [INDEX_TAXON_NAME].[PREFERRED_NAME_AUTHORITY] IS NULL THEN '' ELSE [INDEX_TAXON_NAME].[PREFERRED_NAME_AUTHORITY]
    END                                                                                            AS "authority"
    ,CASE
        WHEN [TAXON_LIST_ITEM].[PARENT] IS NULL THEN '' ELSE [TAXON_LIST_ITEM].[PARENT]
    END                                                                                            AS "parent_taxon_key"
    ,COUNT( [TAXON_DETERMINATION].[TAXON_DETERMINATION_KEY] )                                      AS "records_count"
    ,[INDEX_TAXON_NAME].[SORT_ORDER]                                                               AS "sort_order"
    ,[LANG_TAXON].[LANGUAGE]                                                                       AS "language"

FROM
    [INDEX_TAXON_NAME]
    LEFT JOIN [TAXON_LIST_ITEM]     ON [INDEX_TAXON_NAME].[RECOMMENDED_TAXON_LIST_ITEM_KEY] = [TAXON_LIST_ITEM].[TAXON_LIST_ITEM_KEY]
    LEFT JOIN [TAXON_RANK]          ON [TAXON_LIST_ITEM].[TAXON_RANK_KEY]                   = [TAXON_RANK].[TAXON_RANK_KEY]
    LEFT JOIN [TAXON_VERSION]       ON [TAXON_LIST_ITEM].[TAXON_VERSION_KEY]                = [TAXON_VERSION].[TAXON_VERSION_KEY]
    LEFT JOIN [TAXON_GROUP]         ON [TAXON_VERSION].[OUTPUT_GROUP_KEY]                   = [TAXON_GROUP].[TAXON_GROUP_KEY]
    LEFT JOIN [TAXON_DETERMINATION] ON [INDEX_TAXON_NAME].[TAXON_LIST_ITEM_KEY]             = [TAXON_DETERMINATION].[TAXON_LIST_ITEM_KEY]

    /* EXTRA BIT FOR OBTAINING THE LANGUAGE */
    LEFT JOIN [TAXON_LIST_ITEM] AS [LANG_TAXON_LIST_ITEM]  ON [INDEX_TAXON_NAME].[TAXON_LIST_ITEM_KEY]   = [LANG_TAXON_LIST_ITEM].[TAXON_LIST_ITEM_KEY]
    LEFT JOIN [TAXON_VERSION]   AS [LANG_TAXON_VERSION]    ON [LANG_TAXON_LIST_ITEM].[TAXON_VERSION_KEY] = [LANG_TAXON_VERSION].[TAXON_VERSION_KEY]
    LEFT JOIN [TAXON]           AS [LANG_TAXON]            ON [LANG_TAXON_VERSION].[TAXON_KEY]           = [LANG_TAXON].[TAXON_KEY]

/* RETURN ONLY LATIN AND ENGLISH NAMES - reduces names from 580,676 rows to 564090 */
WHERE
    [LANG_TAXON].[LANGUAGE] = 'la' OR [LANG_TAXON].[LANGUAGE] = 'en'

GROUP BY
    [INDEX_TAXON_NAME].[TAXON_LIST_ITEM_KEY]
    ,[INDEX_TAXON_NAME].[RECOMMENDED_TAXON_LIST_ITEM_KEY]
    ,[INDEX_TAXON_NAME].[TAXON_LIST_ITEM_KEY]
    ,[TAXON_GROUP].[TAXON_GROUP_NAME]
    ,[INDEX_TAXON_NAME].[ACTUAL_NAME]
    ,[INDEX_TAXON_NAME].[COMMON_NAME]
    ,[INDEX_TAXON_NAME].[PREFERRED_NAME]
    ,[INDEX_TAXON_NAME].[ABBREVIATION]
    ,[TAXON_RANK].[LONG_NAME]
    ,[INDEX_TAXON_NAME].[PREFERRED_NAME_AUTHORITY]
    ,[TAXON_LIST_ITEM].[PARENT]
    ,[INDEX_TAXON_NAME].[SORT_ORDER]
    ,[LANG_TAXON].[LANGUAGE]

ORDER BY
    [TAXON_GROUP].[TAXON_GROUP_NAME]
    ,[INDEX_TAXON_NAME].[COMMON_NAME]
    ,[INDEX_TAXON_NAME].[PREFERRED_NAME]

3

Re: preferred common name

Scrap that, I've just tested it against Willow tit (results below). It identifies the incorrect preferred_taxon_key.

My query says its NHMSYS0001688267 where as running a report using the R6 wizard says the preferred key is NHMSYS0021002757. The R6 key isn't even shown in the results of my query. Can anybody identify what I've overlooked?



taxon_key    preferred_taxon_key    is_preferred_name    taxon_group    mixed_names    preferred_common_name    preferred_scientific_name
NBNSYS0000127037    NHMSYS0001688267    0    bird    Willow Tit    Willow Tit    Parus montanus
NBNSYS0000154118    NHMSYS0001688267    0    bird    Parus montanus    Willow Tit    Parus montanus
NBNSYS0000174169    NHMSYS0001688267    0    bird    Parus montanus    Willow Tit    Parus montanus
NBNSYS0000174893    NHMSYS0001688267    0    bird    Willow Tit    Willow Tit    Parus montanus
NBNSYS0000175586    NHMSYS0001688267    0    bird    Parus montanus    Willow Tit    Parus montanus
NBNSYS0000175795    NHMSYS0001688267    0    bird    Parus montanus    Willow Tit    Parus montanus
NBNSYS0100002003    NHMSYS0001688267    0    bird    Parus montanus    Willow Tit    Parus montanus
NBNSYS0100003210    NHMSYS0001688267    0    bird    Willow Tit    Willow Tit    Parus montanus
NBNSYS0200003405    NHMSYS0001688267    0    bird    Parus montanus    Willow Tit    Parus montanus
NBNSYS0200005882    NHMSYS0001688267    0    bird    Willow Tit    Willow Tit    Parus montanus
NHMSYS0001684354    NHMSYS0001688267    0    bird    Parus montanus    Willow Tit    Parus montanus
NHMSYS0001685234    NHMSYS0001688267    0    bird    Willow Tit    Willow Tit    Parus montanus
NHMSYS0001771234    NHMSYS0001688267    0    bird    Parus montanus    Willow Tit    Parus montanus
NHMSYS0001771419    NHMSYS0001688267    0    bird    Willow Tit    Willow Tit    Parus montanus
NHMSYS0020549890    NHMSYS0001688267    0    bird    Parus montanus    Willow Tit    Parus montanus
NHMSYS0020550836    NHMSYS0001688267    0    bird    Willow Tit    Willow Tit    Parus montanus
NHMSYS0000529603    NHMSYS0001688267    0    bird    Parus montanus    Willow Tit    Poecile montana
NHMSYS0000530080    NHMSYS0001688267    0    bird    Willow Tit    Willow Tit    Poecile montana
NHMSYS0001688267    NHMSYS0001688267    1    bird    Poecile montana    Willow Tit    Poecile montana
NHMSYS0021002758    NHMSYS0001688267    0    bird    Poecile montana    Willow Tit    Poecile montana
NHMSYS0020439927    NHMSYS0001688267    0    bird    Poecile montanus    Willow Tit    Poecile montanus
NHMSYS0020524083    NHMSYS0001688267    0    bird    Poecile montanus    Willow Tit    Poecile montanus
NHMSYS0019998281    NHMSYS0001688267    0    bird    Willow Tit    Willow Tit    Willow Tit
NHMSYS0020301512    NHMSYS0001688267    0    bird    Willow Tit    Willow Tit    Willow Tit

4

Re: preferred common name

Hi Paul,

Thanks that gave me an idea, I'd not used the INDEX_TAXON_NAME table - linking this to the NAMESERVER appears to have worked, I'm actually using the visual builder in Access, but the SQL it creates is:

SELECT NAMESERVER.INPUT_TAXON_VERSION_KEY, TAXON.ITEM_NAME, INDEX_TAXON_NAME.COMMON_NAME
FROM ((TAXON INNER JOIN TAXON_VERSION ON TAXON.TAXON_KEY = TAXON_VERSION.TAXON_KEY) INNER JOIN NAMESERVER ON TAXON_VERSION.TAXON_VERSION_KEY = NAMESERVER.INPUT_TAXON_VERSION_KEY) INNER JOIN INDEX_TAXON_NAME ON NAMESERVER.RECOMMENDED_TAXON_LIST_ITEM_KEY = INDEX_TAXON_NAME.TAXON_LIST_ITEM_KEY;

This appears to work for the preferred taxa, I've checked a few of the ones I was having problems with and they have all matched to the correct/expected common name, i.e. the pip bats, greylag goose and bluebell although Hyacinoides x massartiana came out as "bluebell" instead of "hybrid bluebell", but it is a lot better than my first attempt!

This need to be refined, but it is a start

Cheers
Craig

Craig Slawson
Staffordshire Ecological Record

5

Re: preferred common name

Your query using Nameserver is probably correct, but the usual way to do it is to link Index_Taxon_Name to itself.

The following will give all the Recommended names and their common names.

Select DISTINCT ITN2.Actual_Name,ITN2.Common_Name FROM Index_Taxon_Name ITN2
INNER JOIN Index_Taxon_Name ITN
ON ITN.Recommended_Taxon_List_Item_Key = ITN2.TAXON_LIST_ITEM_KEY   
WHERE ITN2.Actual_Name <>  ITN2.Common_Name

The following will give all the Recommended names and their common names for all species in your database (Common name will be scientific name if there isn't a common name)

Select DISTINCT ITN2.Actual_Name,ITN2.Common_Name FROM Index_Taxon_Name ITN2
INNER JOIN Index_Taxon_Name ITN
ON ITN.Recommended_Taxon_List_Item_Key = ITN2.TAXON_LIST_ITEM_KEY   
AND ITN2.SYSTEM_SUPPLIED_DATA = 1
INNER JOIN TAXON_DETERMINATION TDET ON TDET.TAXON_LIST_ITEM_KEY
= ITN.TAXON_LIST_ITEM_KEY AND TDET.PREFERRED = 1

There can be multiple common names for a Recommended name and the UKSI doesn't identify a recommended common name. To populate Index_Taxon_name there has to be a way of identifying which is the best common name to use. R6 first gets the english common name from the taxon list. If there isn't one it then works out the english common name by looking at each list in the sequence identified in the Taxon_List_Table in column Priority. Priority is given to the preferred lists then the others in an order designed to use the best constructed ones first. The first English common name it finds is the common name which ends up in Index_Taxon_Name.


With Pipistrellus pipistrellus you get two different common names depending on the attribute - try the following 

Select DISTINCT ITN2.Actual_Name,ITN2.Common_Name,ITN2.ACTUAL_NAME_ATTRIBUTE FROM Index_Taxon_Name ITN2
INNER JOIN Index_Taxon_Name ITN
ON ITN.Recommended_Taxon_List_Item_Key = ITN2.TAXON_LIST_ITEM_KEY   
WHERE ITN2.Actual_Name <>  ITN2.Common_Name AND
ITN.Actual_Name =  'Pipistrellus pipistrellus'

Mike Weideli

6

Re: preferred common name

Thanks to you both :-D

I will have a play when I get the chance.

7 (edited by PaulBarrington 11-03-2019 11:55:32)

Re: preferred common name

Hi Mike, I just ran your queries. Willow tit is still outputting with a TVK of NHMSYS0001688267. How would I obtain the preferred TVK which is selected by the report wizard e.g. NHMSYS0021002757 in the case of Willow tit?

I notice the NBN Atlas are also using the same preferred TVK as Recorder's wizard is using (NHMSYS0021002757) and not the one I am selecting (NHMSYS0001688267).

8

Re: preferred common name

Looks like you might be getting Taxon_List_Item_Key and Taxon_Version_Key confused.

NHMSYS0001688267 is not a TVK (Taxon Version key), but a Taxon_List_Item_Key for Poecile montana (Willow Tit). In fact it is the Recommended_Taxon_List_Item key. 


Select DISTINCT TLI.Taxon_List_ITem_Key,TLI.Taxon_Version_Key,ITN2.Actual_Name,ITN2.Common_Name FROM Index_Taxon_Name ITN2
INNER JOIN Index_Taxon_Name ITN
ON ITN.Recommended_Taxon_List_Item_Key = ITN2.TAXON_LIST_ITEM_KEY 
INNER JOIN Taxon_List_Item TLI ON TLI.Taxon_List_Item_Key =
ITN2.Recommended_Taxon_LIst_ITem_Key 
WHERE ITN2.Actual_Name <>  ITN2.Common_Name
AND ITN2.COMMON_NAME = 'Willow Tit'

The TVK for this TLIK is NHMSYS0021002757, but to call it the Recommended TVK of Willow Tit is a bit misleading. It is the TVK of Poecile montana which has a common name of Willow Tit. Willow Tit has only one valid TVK NBNSYS0000171911.

Mike Weideli

9

Re: preferred common name

Thanks for this. You are right re: my confusion. Sorry for the delay, I didn't get a notification about this post. I still have a lot to learn about the organisation of the taxonomy. Glad you are about :-D

Best wishes