search

ArctosDB / arctos

Arctos is a museum collections management system
https://arctos.database.museum
60 stars 13 forks source link

Restore linkage of and search tools for reciprocal relationships between records #6249

Closed campmlc closed 1 year ago

campmlc commented 1 year ago

Our original model developed a decade ago by @dustymc @campmlc and Gordon Jarrell to link host and parasite records in Arctos (NSF funded project for Rausch collection) included the following components: 1) use of other identifiers with relationship other than self (code table controlled) to assert relationships between catalog records within and external to Arctos. 2) Following the model originally developed for reciprocal relationships with GenBank, when these identifiers linked to another Arctos url, a notification would be sent to the contact agent for the relevant collection which provided a mechanism for review and approval, and creation of the reciprocal relationship in the host or parasite record. 3) Pulling of not only the related identifier and asserted relationship, but also the currently accepted identification and collecting event information across collections and institutions, so that this information was visible in the catalog record page of each reciprocallly related record. 4) The ability, as a result of the above, to view the host or parasite currently accepted identification, locality, geography, and date information from the related host or parasite record, to verify that these match, as they should. 5) The creation of a host observation record in a specific Host portal set up explicitly to capture host data and create the reciprocal relationship when the host was either not collected or its disposition was unknown. Unlike use of a parasite attribute such as "verbatim host ID", this record was given a taxonomic identification and associated classification which could be updated with identification history and synonymy. 6) Most importantly, and the point of all of the above: Enable complex searches of related records across taxon name and classification, including geography date and other collecting event information. While we could not search across all classification levels due to resource limitation, we did have the option of searching by identification, family, locality etc. See below. Screenshot 2023-05-05 08 00 53

In the conversion to PG and the subsequent UI change, we have lost the ability to do # 3, 4, and 6 above. I would like to explore what options we have for restoring this previously funded functionality, which is critically important for new initiatives. Thoughts?

dustymc commented 1 year ago

Duplicate of https://github.com/ArctosDB/arctos/issues/5135

campmlc commented 1 year ago

Please leave this open, as it has explicit explanation and justification of what was done and is needed.

campmlc commented 1 year ago

Find all Family Sciuridae from Alaska that have the relationship "host of" to Order Siphonaptera

Jegelewicz commented 1 year ago

Family and Order as asserted by the collection.

campmlc commented 1 year ago

Find all Muridae from Panama with Examined for "Virus:Orthohantavirus" and Detected: "Virus:Orthohantavirus"

Jegelewicz commented 1 year ago

What output is needed? Simple?

host GUID, host identification, host collection locality, parasite GUID, parasite identification, parasite collection locality

dustymc commented 1 year ago

Find all Muridae from Panama with Examined for "Virus:Orthohantavirus" and Detected: "Virus:Orthohantavirus"

https://arctos.database.museum/search.cfm?family=Muridae&attribute_type_1=detected&attribute_value_1=virus%3A%20Orthohantavirus&attribute_type_2=examined%20for&attribute_value_2=virus%3A%20Orthohantavirus&country=Panama

Find all Family Sciuridae from Alaska that have the relationship "host of" to Order Siphonaptera

This is greatly complicated by https://github.com/ArctosDB/arctos/issues/6163, but here's a QnD approximation:

select a.guid,b.guid from flat a
inner join coll_obj_other_id_num on a.collection_object_id=coll_obj_other_id_num.collection_object_id
inner join flat b on coll_obj_other_id_num.other_id_type||':'||coll_obj_other_id_num.display_value=b.guid
where a.family='Sciuridae' and
a.state_prov='Alaska' and
coll_obj_other_id_num.id_references='host of' and
b.phylorder='Siphonaptera'
;

      guid       |      guid       
-----------------+-----------------
 UAM:Mamm:47277  | UAM:Ento:282060
 UAM:Mamm:41610  | UAM:Ento:280803
 UAM:Mamm:35157  | UAM:Ento:276944
 UAM:Mamm:38307  | UAM:Ento:278157
 UAM:Mamm:48320  | UAM:Ento:282514
 UAM:Mamm:48330  | UAM:Ento:282383
 UAM:Mamm:48426  | UAM:Ento:279584
 UAM:Mamm:70880  | UAM:Ento:280675
 UAM:Mamm:48431  | UAM:Ento:277273
 UAM:Mamm:48447  | UAM:Ento:281795
 UAM:Mamm:70918  | UAM:Ento:278825
 UAM:Mamm:51095  | UAM:Ento:279080
 UAM:Mamm:70850  | UAM:Ento:279787
 UAM:Mamm:70892  | UAM:Ento:276625
 UAM:Mamm:70929  | UAM:Ento:276440
 UAM:Mamm:70893  | UAM:Ento:280618
 UAM:Mamm:70943  | UAM:Ento:280049
 UAM:Mamm:70944  | UAM:Ento:280045
 UAM:Mamm:70953  | UAM:Ento:277940
 UAM:Mamm:70951  | UAM:Ento:277376
 UAM:Mamm:70982  | UAM:Ento:279989
 UAM:Mamm:71011  | UAM:Ento:281074
 UAM:Mamm:71011  | UAM:Ento:334630
 UAM:Mamm:71043  | UAM:Ento:278595
 UAM:Mamm:70993  | UAM:Ento:280269
 MSB:Mamm:193566 | MSB:Para:26151
 UAM:Mamm:71205  | UAM:Ento:279511
 UAM:Mamm:86397  | UAM:Ento:282422
 UAM:Mamm:71023  | UAM:Ento:277247
 UAM:Mamm:71025  | UAM:Ento:281866
 UAM:Mamm:93135  | MSB:Para:26445
 MSB:Mamm:136519 | MSB:Para:5967
 MSB:Mamm:192793 | MSB:Para:1412
 MSB:Mamm:192797 | MSB:Para:1624
 MSB:Mamm:198278 | MSB:Para:37250
 MSB:Mamm:192798 | MSB:Para:6094
 MSB:Mamm:192805 | MSB:Para:1756
 MSB:Mamm:193321 | MSB:Para:37179
 MSB:Mamm:193324 | MSB:Para:37317
 MSB:Mamm:198279 | MSB:Para:37266
 MSB:Mamm:198307 | MSB:Para:37003
 MSB:Mamm:198281 | MSB:Para:37255
 MSB:Mamm:198292 | MSB:Para:37260
 MSB:Mamm:198293 | MSB:Para:37267
 MSB:Mamm:198295 | MSB:Para:37269
 MSB:Mamm:198296 | MSB:Para:37257
 MSB:Mamm:198297 | MSB:Para:37268
 MSB:Mamm:198298 | MSB:Para:37253
 MSB:Mamm:198299 | MSB:Para:37275
 MSB:Mamm:198300 | MSB:Para:37273
 MSB:Mamm:232705 | MSB:Para:37065
 UAM:Mamm:111878 | UAM:Ento:279367
 UAM:Mamm:111879 | UAM:Ento:281583
 MSB:Host:715    | MSB:Para:795
 MSB:Mamm:233665 | MSB:Para:37041
 MSB:Mamm:247656 | MSB:Para:36964
 MSB:Host:1498   | MSB:Para:1756
 MSB:Host:7417   | MSB:Para:1624
 MSB:Host:4395   | MSB:Para:5967
 MSB:Host:6922   | MSB:Para:1412
 MSB:Host:10445  | MSB:Para:1460
 MSB:Host:8786   | MSB:Para:6094
 MSB:Host:11229  | MSB:Para:17807
 MSB:Host:10189  | MSB:Para:1757
 MSB:Host:10196  | MSB:Para:1765
 MSB:Host:10703  | MSB:Para:7541
 MSB:Host:16598  | MSB:Para:22159
 MSB:Host:14695  | MSB:Para:14819
 MSB:Host:14697  | MSB:Para:14830
 MSB:Host:19671  | MSB:Para:22160
 MSB:Host:16325  | MSB:Para:22155
 MSB:Host:16326  | MSB:Para:22156
 MSB:Host:19721  | MSB:Para:22115
 UAM:Mamm:127679 | UAM:Ento:443108
 UAM:Mamm:127641 | UAM:Ento:443356
campmlc commented 1 year ago

Can we get the panama search to use "and"? It seems to be using "or"?

On Mon, May 8, 2023, 4:00 PM dustymc @.***> wrote:

  • [EXTERNAL]*

Find all Muridae from Panama with Examined for "Virus:Orthohantavirus" and Detected: "Virus:Orthohantavirus"

https://arctos.database.museum/search.cfm?family=Muridae&attribute_type_1=detected&attribute_value_1=virus%3A%20Orthohantavirus&attribute_type_2=examined%20for&attribute_value_2=virus%3A%20Orthohantavirus&country=Panama

Find all Family Sciuridae from Alaska that have the relationship "host of" to Order Siphonaptera

This is greatly complicated by #6163 https://github.com/ArctosDB/arctos/issues/6163, but here's a QnD approximation:

select a.guid,b.guid from flat a inner join coll_obj_other_id_num on a.collection_object_id=coll_obj_other_id_num.collection_object_id inner join flat b on coll_obj_other_id_num.other_id_type||':'||coll_obj_other_id_num.display_value=b.guid where a.family='Sciuridae' and a.state_prov='Alaska' and coll_obj_other_id_num.id_references='host of' and b.phylorder='Siphonaptera' ;

  guid       |      guid

-----------------+----------------- UAM:Mamm:47277 | UAM:Ento:282060 UAM:Mamm:41610 | UAM:Ento:280803 UAM:Mamm:35157 | UAM:Ento:276944 UAM:Mamm:38307 | UAM:Ento:278157 UAM:Mamm:48320 | UAM:Ento:282514 UAM:Mamm:48330 | UAM:Ento:282383 UAM:Mamm:48426 | UAM:Ento:279584 UAM:Mamm:70880 | UAM:Ento:280675 UAM:Mamm:48431 | UAM:Ento:277273 UAM:Mamm:48447 | UAM:Ento:281795 UAM:Mamm:70918 | UAM:Ento:278825 UAM:Mamm:51095 | UAM:Ento:279080 UAM:Mamm:70850 | UAM:Ento:279787 UAM:Mamm:70892 | UAM:Ento:276625 UAM:Mamm:70929 | UAM:Ento:276440 UAM:Mamm:70893 | UAM:Ento:280618 UAM:Mamm:70943 | UAM:Ento:280049 UAM:Mamm:70944 | UAM:Ento:280045 UAM:Mamm:70953 | UAM:Ento:277940 UAM:Mamm:70951 | UAM:Ento:277376 UAM:Mamm:70982 | UAM:Ento:279989 UAM:Mamm:71011 | UAM:Ento:281074 UAM:Mamm:71011 | UAM:Ento:334630 UAM:Mamm:71043 | UAM:Ento:278595 UAM:Mamm:70993 | UAM:Ento:280269 MSB:Mamm:193566 | MSB:Para:26151 UAM:Mamm:71205 | UAM:Ento:279511 UAM:Mamm:86397 | UAM:Ento:282422 UAM:Mamm:71023 | UAM:Ento:277247 UAM:Mamm:71025 | UAM:Ento:281866 UAM:Mamm:93135 | MSB:Para:26445 MSB:Mamm:136519 | MSB:Para:5967 MSB:Mamm:192793 | MSB:Para:1412 MSB:Mamm:192797 | MSB:Para:1624 MSB:Mamm:198278 | MSB:Para:37250 MSB:Mamm:192798 | MSB:Para:6094 MSB:Mamm:192805 | MSB:Para:1756 MSB:Mamm:193321 | MSB:Para:37179 MSB:Mamm:193324 | MSB:Para:37317 MSB:Mamm:198279 | MSB:Para:37266 MSB:Mamm:198307 | MSB:Para:37003 MSB:Mamm:198281 | MSB:Para:37255 MSB:Mamm:198292 | MSB:Para:37260 MSB:Mamm:198293 | MSB:Para:37267 MSB:Mamm:198295 | MSB:Para:37269 MSB:Mamm:198296 | MSB:Para:37257 MSB:Mamm:198297 | MSB:Para:37268 MSB:Mamm:198298 | MSB:Para:37253 MSB:Mamm:198299 | MSB:Para:37275 MSB:Mamm:198300 | MSB:Para:37273 MSB:Mamm:232705 | MSB:Para:37065 UAM:Mamm:111878 | UAM:Ento:279367 UAM:Mamm:111879 | UAM:Ento:281583 MSB:Host:715 | MSB:Para:795 MSB:Mamm:233665 | MSB:Para:37041 MSB:Mamm:247656 | MSB:Para:36964 MSB:Host:1498 | MSB:Para:1756 MSB:Host:7417 | MSB:Para:1624 MSB:Host:4395 | MSB:Para:5967 MSB:Host:6922 | MSB:Para:1412 MSB:Host:10445 | MSB:Para:1460 MSB:Host:8786 | MSB:Para:6094 MSB:Host:11229 | MSB:Para:17807 MSB:Host:10189 | MSB:Para:1757 MSB:Host:10196 | MSB:Para:1765 MSB:Host:10703 | MSB:Para:7541 MSB:Host:16598 | MSB:Para:22159 MSB:Host:14695 | MSB:Para:14819 MSB:Host:14697 | MSB:Para:14830 MSB:Host:19671 | MSB:Para:22160 MSB:Host:16325 | MSB:Para:22155 MSB:Host:16326 | MSB:Para:22156 MSB:Host:19721 | MSB:Para:22115 UAM:Mamm:127679 | UAM:Ento:443108 UAM:Mamm:127641 | UAM:Ento:443356

— Reply to this email directly, view it on GitHub https://github.com/ArctosDB/arctos/issues/6249#issuecomment-1539022917, or unsubscribe https://github.com/notifications/unsubscribe-auth/ADQ7JBBOF64WEJ2SYSUOCGTXFFUG5ANCNFSM6AAAAAAXXFL4WA . You are receiving this because you authored the thread.Message ID: @.***>

campmlc commented 1 year ago

Nevermind, it's working. It's just I expected those attributes to cluster as they are related, and not be arranged alphabetically.

On Mon, May 8, 2023, 5:20 PM Mariel Campbell @.***> wrote:

Can we get the panama search to use "and"? It seems to be using "or"?

On Mon, May 8, 2023, 4:00 PM dustymc @.***> wrote:

  • [EXTERNAL]*

Find all Muridae from Panama with Examined for "Virus:Orthohantavirus" and Detected: "Virus:Orthohantavirus"

https://arctos.database.museum/search.cfm?family=Muridae&attribute_type_1=detected&attribute_value_1=virus%3A%20Orthohantavirus&attribute_type_2=examined%20for&attribute_value_2=virus%3A%20Orthohantavirus&country=Panama

Find all Family Sciuridae from Alaska that have the relationship "host of" to Order Siphonaptera

This is greatly complicated by #6163 https://github.com/ArctosDB/arctos/issues/6163, but here's a QnD approximation:

select a.guid,b.guid from flat a inner join coll_obj_other_id_num on a.collection_object_id=coll_obj_other_id_num.collection_object_id inner join flat b on coll_obj_other_id_num.other_id_type||':'||coll_obj_other_id_num.display_value=b.guid where a.family='Sciuridae' and a.state_prov='Alaska' and coll_obj_other_id_num.id_references='host of' and b.phylorder='Siphonaptera' ;

  guid       |      guid

-----------------+----------------- UAM:Mamm:47277 | UAM:Ento:282060 UAM:Mamm:41610 | UAM:Ento:280803 UAM:Mamm:35157 | UAM:Ento:276944 UAM:Mamm:38307 | UAM:Ento:278157 UAM:Mamm:48320 | UAM:Ento:282514 UAM:Mamm:48330 | UAM:Ento:282383 UAM:Mamm:48426 | UAM:Ento:279584 UAM:Mamm:70880 | UAM:Ento:280675 UAM:Mamm:48431 | UAM:Ento:277273 UAM:Mamm:48447 | UAM:Ento:281795 UAM:Mamm:70918 | UAM:Ento:278825 UAM:Mamm:51095 | UAM:Ento:279080 UAM:Mamm:70850 | UAM:Ento:279787 UAM:Mamm:70892 | UAM:Ento:276625 UAM:Mamm:70929 | UAM:Ento:276440 UAM:Mamm:70893 | UAM:Ento:280618 UAM:Mamm:70943 | UAM:Ento:280049 UAM:Mamm:70944 | UAM:Ento:280045 UAM:Mamm:70953 | UAM:Ento:277940 UAM:Mamm:70951 | UAM:Ento:277376 UAM:Mamm:70982 | UAM:Ento:279989 UAM:Mamm:71011 | UAM:Ento:281074 UAM:Mamm:71011 | UAM:Ento:334630 UAM:Mamm:71043 | UAM:Ento:278595 UAM:Mamm:70993 | UAM:Ento:280269 MSB:Mamm:193566 | MSB:Para:26151 UAM:Mamm:71205 | UAM:Ento:279511 UAM:Mamm:86397 | UAM:Ento:282422 UAM:Mamm:71023 | UAM:Ento:277247 UAM:Mamm:71025 | UAM:Ento:281866 UAM:Mamm:93135 | MSB:Para:26445 MSB:Mamm:136519 | MSB:Para:5967 MSB:Mamm:192793 | MSB:Para:1412 MSB:Mamm:192797 | MSB:Para:1624 MSB:Mamm:198278 | MSB:Para:37250 MSB:Mamm:192798 | MSB:Para:6094 MSB:Mamm:192805 | MSB:Para:1756 MSB:Mamm:193321 | MSB:Para:37179 MSB:Mamm:193324 | MSB:Para:37317 MSB:Mamm:198279 | MSB:Para:37266 MSB:Mamm:198307 | MSB:Para:37003 MSB:Mamm:198281 | MSB:Para:37255 MSB:Mamm:198292 | MSB:Para:37260 MSB:Mamm:198293 | MSB:Para:37267 MSB:Mamm:198295 | MSB:Para:37269 MSB:Mamm:198296 | MSB:Para:37257 MSB:Mamm:198297 | MSB:Para:37268 MSB:Mamm:198298 | MSB:Para:37253 MSB:Mamm:198299 | MSB:Para:37275 MSB:Mamm:198300 | MSB:Para:37273 MSB:Mamm:232705 | MSB:Para:37065 UAM:Mamm:111878 | UAM:Ento:279367 UAM:Mamm:111879 | UAM:Ento:281583 MSB:Host:715 | MSB:Para:795 MSB:Mamm:233665 | MSB:Para:37041 MSB:Mamm:247656 | MSB:Para:36964 MSB:Host:1498 | MSB:Para:1756 MSB:Host:7417 | MSB:Para:1624 MSB:Host:4395 | MSB:Para:5967 MSB:Host:6922 | MSB:Para:1412 MSB:Host:10445 | MSB:Para:1460 MSB:Host:8786 | MSB:Para:6094 MSB:Host:11229 | MSB:Para:17807 MSB:Host:10189 | MSB:Para:1757 MSB:Host:10196 | MSB:Para:1765 MSB:Host:10703 | MSB:Para:7541 MSB:Host:16598 | MSB:Para:22159 MSB:Host:14695 | MSB:Para:14819 MSB:Host:14697 | MSB:Para:14830 MSB:Host:19671 | MSB:Para:22160 MSB:Host:16325 | MSB:Para:22155 MSB:Host:16326 | MSB:Para:22156 MSB:Host:19721 | MSB:Para:22115 UAM:Mamm:127679 | UAM:Ento:443108 UAM:Mamm:127641 | UAM:Ento:443356

— Reply to this email directly, view it on GitHub https://github.com/ArctosDB/arctos/issues/6249#issuecomment-1539022917, or unsubscribe https://github.com/notifications/unsubscribe-auth/ADQ7JBBOF64WEJ2SYSUOCGTXFFUG5ANCNFSM6AAAAAAXXFL4WA . You are receiving this because you authored the thread.Message ID: @.***>

campmlc commented 1 year ago

Just curious when next release will be?

dustymc commented 1 year ago

Why has this been reopened?

campmlc commented 1 year ago

I would like all these related issues to remain open until we can document in the context of each issue what solutions were implemented.

5135

Jegelewicz commented 1 year ago

@campmlc I don't know what to document - and it would probably be better to open an issue in the documentation repo for that?

campmlc commented 1 year ago

@dustymc can you provide a description of what was done here related to last release? And perhaps this can be moved to discussion as the overall issue isn't resolved?

acdoll commented 1 year ago

I believe this may have been closed because the new search interface does address your need for 6. the complex search, as demonstrated here (if you turn on all the search fields you'll see how):

https://arctos.database.museum/search.cfm?family=Muridae&attribute_type_1=detected&attribute_value_1=virus%3A%20Orthohantavirus&attribute_type_2=examined%20for&attribute_value_2=virus%3A%20Orthohantavirus&country=Panama

But that doesn't address your needs for 3. and 4. The host/parasite information (identification and collecting info) does not display on the respective parasite/host records. While that would make things easier, it is just one click away and reduces the amount of content on any particular page.

Jegelewicz commented 1 year ago

the host/parasite information (identification and collecting info) does not display on the respective parasite/host records

it does! Just mouse over the link to the related record

Screenshot 2023-09-13 17 05 25
campmlc commented 1 year ago

I just discovered this by accident yesterday, but that wasn't explained here, hence my request for a response to what solutions have been implemented. And I'm not sure anyone else would find this except by accident.
Can we get this info integrated into the table? It used to display under the equivalent of what is now the url. ?

the host/parasite information (identification and collecting info) does not display on the respective parasite/host records

it does! Just mouse over the link to the related record

campmlc commented 1 year ago

I believe this may have been closed because the new search interface does address your need for 6. the complex search, as demonstrated here (if you turn on all the search fields you'll see how):

https://arctos.database.museum/search.cfm?family=Muridae&attribute_type_1=detected&attribute_value_1=virus%3A%20Orthohantavirus&attribute_type_2=examined%20for&attribute_value_2=virus%3A%20Orthohantavirus&country=Panama

But that doesn't address your needs for 3. and 4. The host/parasite information (identification and collecting info) does not display on the respective parasite/host records. While that would make things easier, it is just one click away and reduces the amount of content on any particular page.

Having the related host or parasite info displayed on the same page allows for double checking for accuracy to make sure the right records were associated, which was hugely beneficial. It is also not obvious to a non-Arctos or even Arctos user that following the url would get you that info. For example, following a link to other external resources won't necessarily get you a taxon ID and collecting event information.

And #6 also is a request for being able to search on selected higher classification values, including backwards from the parasite record - e.g Find all Cestodes collected from Sciuridae. We had this functionality.

We are trying to see if we can develop a separate module for host/pathogen queries, but I have no idea if that will work broadly for other purposes or be supported in the long term. We previously had all these functions in Arctos. I would like to keep all these options in discussion, and would be happy to have committee or task force with other interested parties.

acdoll commented 1 year ago

it does! Just mouse over

Well, would you look at that! I'd swear that wasn't popping up yesterday, but maybe I was scrolling and clicking too fast.

campmlc commented 1 year ago

Again, this issue is not completed. There are multiple aspects that have not been addressed, and there needs to be more discussion. Happy to form a task group. But we by no means have restored the lost functionality as requested, and I don't think these concepts are being understood. We spent three years developing this model. Can we move this to a discussion so that it stops bothering @dusty?