Update butterfly species list for Japan - revise taxon sort order

[DavidRoy]

Suggested ordering for Japan but a wider issue as the list of species increases

ebms_butterflylist 20230515Revised.xlsx

[andrewvanbreda]

Note the 11 rows labels as Add New Species have been added as part of https://github.com/BiologicalRecordsCentre/ABLE/issues/602

Note also: the japanese specific taxonomic sort order has not been imported as cannot do that as part of the Warehouse UI, I think requires direct database alteration.

[JimBacon]

@DavidRoy please could you clarify what needs to be done here.

The file contains a Revised Taxonomic Sort Order column and a Japanese Species Name Order Revised column.

Currently all taxa with the same taxon_meaning_id in a list have the same taxonomic_sort_order. Is the task to update the existing taxonomic_sort_order values with that in the Revised Taxonomic Sort Order column?

There must be more to it than this as that would be a universal change affecting all languages. Do you, in addition, want the taxonomic_sort_order for Japanese names to be changed to the value in the Japanese Species Name Order Revised column?

I'm not sure what the effect of the latter would be. I should think there would be a high risk of it being overwritten if the taxon is edited in the warehouse user interface as it only allows one value for the sort order.

[JimBacon]

Additional information received by email

Firstly, in order to put Japanese species in the taxonomic order among European species, we multiplied the number of "taxonomic_sort_order" in the existing European list by 100 to increase the number. Then I inserted the Japanese species in the appropriate place on that list. And the result is the "Revised Taxonomic_sort_order" column. Therefore, we ask that you use this numerical order in your sorting order.

Next, regarding the Japanese list, there are some species that I would like to add, and there are some that have to be corrected in terms of their scientific names and common names in the same way as in the European list. Therefore, please correct the characters in this red part and add species.

In addition, the addition of the species requires a change in the order of the Japanese names, so I added a new order to the "Japanese Species Name Order Revised" section. Use this order for sorting by common name on the data entry page.

The list is an excel file. The part in red is the part that needs to be changed.

[JimBacon]

Having imported the spreadsheet in to a temporary table, the following query was run to update the taxonomic_sort_order for preferred species names.

update taxa_taxon_lists ttl
set taxonomic_sort_order = jtl.revised_order, updated_by_id = 2, updated_on = now()
from temp_japanese_sort_order jtl
where jtl.id = ttl.id
and not (revised_order is null and existing_order is null)

750 records were updated. The cache table automatically updated.

[andrewvanbreda]

Hi @JimBacon Thanks for this. I will check to see if any reports needs updating to take advantage of the change, and will inform the Japan Butterfly Conservation Society.

[JimBacon]

The following amendments were made using the warehouse UI

id	old taxon	new taxon
549526	Papilio sp.	Papilio protenor/others
549508	Parnassius sp.	Parnassius citrinarius/stubbendorfii
549949	Thymelicus sp.	Thymelicus leonina/sylvatica
549963	Parnara/Pelopidas sp.	Parnara/Pelopidas/Zinaida sp.
549547	Leptidea sp.	Leptidea morsei/amurensis
549561	Gonepteryx sp.	Gonepteryx maxima/aspasia
549567	Catopsilia sp.	Catopsilia pomona/pyranthe
549555	Eurema sp.	Eurema mandarina/others
549587	Pieris sp.	Pieris melete/nesis/dulcinea
549579	Appias sp.	Appias lyncida/paulina/albina
549627	Japonica sp.	Japonica lutea/onoi/saepestriata
549657	Neozephyrus sp.	Neozephyrus japonica/others
549669	Satyrium sp.	Satyrium w-album/mera/iyonis
549707	Celastrina sp.	Celastrina/Acytolepis/Udara sp.
549731	Plebejus sp.	Plebejus argus/argyrognomon/subsolanus
549801	Neptis sp.	Neptis sappho/others
549787	Limenitis sp.	Limenitis camilla/glorifica
552219	Araschnia sp.	Araschnia burejana/levana
549817	Polygonia sp.	Polygonia c-aureum/c-album
549839	Hypolimnas sp.	Hypolimnas bolina/misippus
549871	Neope sp.	Neope goschkevitschii/niphonica
549883	Melanitis sp.	Melanitis phedima/leda
549899	Ypthima sp.	Ypthima argus/others
549887	Erebia sp.	Erebia ligea/neriene

[JimBacon]

The following query was run to update the taxa_taxon_list.taxonomic_sort_order of Japanese common names with the value from the "Japanese Species Name Order Revised" column of the spreadsheet

update taxa_taxon_lists ttljap
set taxonomic_sort_order = jtl.japanese_order, updated_by_id = 2, updated_on = now()
from temp_japanese_sort_order jtl, taxa_taxon_lists ttlpref, taxa t
where ttljap.taxon_meaning_id = ttlpref.taxon_meaning_id and
    jtl.japanese_order is not null and
    ttlpref.id = jtl.id and
    t.id = ttljap.taxon_id and 
    t.language_id = 22 -- Japanese

295 records were updated. I manually updated the newly added taxa as they were not in my temporary table.

This will not appear in the cache_taxa_taxon_list table. The cache assigns the taxonomic sort order of the preferred name to all the common names.

@andrewvanbreda you may be able to exploit this to order Japanese names on the recording form in the desired manner.

[JimBacon]

I've identified a few issues:

• Two records (ids 549163 and 617367) have the same taxonomic sort order (26780)
• Argynnis vorax and Argynnis nagiae are in the spreadsheet but are not present on the warehouse.
• What is the intention of Brenthis sp1, Brenthis sp2 and Brenthis sp1/sp2 in the spreadsheet? We have Brenthis sp. (id 549765) with 3 Japanese names.

Can you clear these up when you contact the Japanese Society @andrewvanbreda ?

[andrewvanbreda]

Hi @JimBacon I have contacted them about progress and the questions, I will let you know what they say.

[andrewvanbreda]

Hi @JimBacon I have sent you an email with their responses and Excel sheet they have sent.

[JimBacon]

The above issues were resolved as follows:

1. Gave Argynnis vorax/nagiae the sort order 26750 and left Phalanta phalantha (549163) unchanged
2. Modified as follows
   1. Added Argynnis vorax Butler, 1871 (id 629116) サトウラギンヒョウモン Sort order: 26720 Japanese sort order: 1440 Present in Japan
   2. Added Argynnis nagiae (id 629118) ヤマウラギンヒョウモン Sort order: 26740 Japanese sort order: 2830 Present in Japan
   3. Removed Japanese common names from Fabriciana adippe (id 432394) and removed presence from Japan.
   4. Updated the one occurrence (id 29157566) with the Japanese name, ヤマウラギンヒョウモン, to refer to its new taxa_taxon_list_id (id 629119)
3. Modified as follows:
   1. Changed Brenthis sp. (id 549765) to Brenthis daphne/ino with Sort order: 26060 Japanese sort order: 2290 Removed Japanese common names キタヒョウモン and コウゲンヒョウモン. There were no occurrences recorded against either of these names.
   2. Addded Brenthis daphne 1 (id 629121) キタヒョウモン Sort order: 26020 Japanese sort order: 950 Present in Japan
   3. Added Brenthis daphne 2 (629123) コウゲンヒョウモン Sort order: 26040 Japanese sort order: 1250 Present in Japan

[JimBacon]

@andrewvanbreda the revisions to the Japanese species list are complete. Can we close this issue?

[andrewvanbreda]

@JimBacon I will check the reports are working with it first as they may need some changes. I will assign to myself.

Thanks for doing this