Request - Bulk add publications to project for UCM types

[ebraker]

@dustymc - can you help with some publications -> project magic? I am creating projects that summarize the type publications associated with 1) UCM specimens across collections, and 2) separate projects detailing collection-specific type publications.

-I would like to add publications associated with 378 specimens to the UCM type specimen project (https://arctos.database.museum/project/10004584). The publications are associated with the following specimens: https://arctos.database.museum/search.cfm?guid_prefix=UCM%3ABird%2CUCM%3AFish%2CUCM%3AHerp%2CUCM%3AMamm&type_status=type. To isolate the correct publication select type = holotype OR paratype

-I was able to set up collection-specific projects and pull in publications by hand for everything except herps. Can you automate this? This will add publications citing 344 specimens to project (https://arctos.database.museum/project/10004588). The publications are associated with the following specimens: https://arctos.database.museum/search.cfm?guid_prefix=UCM%3AHerp&type_status=type. To isolate the correct publication select type = holotype OR paratype

I couldn't think of an easy way to do this myself, but please let me know if there is one!

[dustymc]

easy way to do this myself

Depends a bit on what you're trying to do, but I'd use data loans. The direct publication-project has always been intended as a way to recover SOMETHING from "some rats from Alaska"-type "citations" and can't do much beyond that, where transaction-based projects allow all the magic (eg demonstrating how collecting funding supports research - https://github.com/ArctosDB/arctos/issues/1694).

[ebraker]

Yes but if I pull the specimens in via a transaction the project will then display all publications linked with them and I'd like to isolate the project to only displaying type publications.

[dustymc]

OK, here's the simplest query I could come up with to find everything and exclude anything already linked. If that looks right (please check a few!) I'll insert this, then you can give me a new pair of (project_id, guid_prefix) as needed for the rest.

select
    10004584 as project_id,
    citation.publication_id,
    'https://arctos.database.museum/project/10004584' as projectID,
    'https://arctos.database.museum/publication/' ||citation.publication_id::text as publicationID
from
    flat
    inner join citation on flat.collection_object_id=citation.collection_object_id
where
    flat.guid_prefix in ('UCM:Bird','UCM:Fish','UCM:Herp','UCM:Mamm') and
    type_status in ('holotype','paratype') and
    (10004584,publication_id) not in (select project_id,publication_id from project_publication)
group by citation.publication_id;

 project_id | publication_id |                    projectid                    |                    publicationid                    
------------+----------------+-------------------------------------------------+-----------------------------------------------------
   10004584 |       10000359 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10000359
   10004584 |       10000404 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10000404
   10004584 |       10000474 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10000474
   10004584 |       10001760 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10001760
   10004584 |       10006244 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10006244
   10004584 |       10007145 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10007145
   10004584 |       10007279 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10007279
   10004584 |       10007280 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10007280
   10004584 |       10007282 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10007282
   10004584 |       10007283 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10007283
   10004584 |       10007311 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10007311
   10004584 |       10007312 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10007312
   10004584 |       10007313 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10007313
   10004584 |       10007314 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10007314
   10004584 |       10007315 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10007315
   10004584 |       10007316 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10007316
   10004584 |       10007317 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10007317
   10004584 |       10007318 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10007318
   10004584 |       10007319 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10007319
   10004584 |       10007320 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10007320
   10004584 |       10007321 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10007321
   10004584 |       10007322 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10007322
   10004584 |       10007323 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10007323
   10004584 |       10007340 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10007340
   10004584 |       10007552 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10007552
   10004584 |       10007752 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10007752
   10004584 |       10008293 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10008293
   10004584 |       10008295 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10008295
   10004584 |       10008572 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10008572
   10004584 |       10008720 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10008720
   10004584 |       10008738 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10008738
   10004584 |       10008739 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10008739
   10004584 |       10008798 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10008798
   10004584 |       10008805 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10008805
   10004584 |       10008816 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10008816
   10004584 |       10009103 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10009103
   10004584 |       10009333 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10009333
   10004584 |       10009396 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10009396
   10004584 |       10009450 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10009450
   10004584 |       10009465 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10009465
   10004584 |       10009469 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10009469
   10004584 |       10009587 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10009587
   10004584 |       10009588 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10009588
   10004584 |       10009601 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10009601
   10004584 |       10009633 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10009633
   10004584 |       10009634 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10009634
   10004584 |       10009655 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10009655
   10004584 |       10009656 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10009656
   10004584 |       10009658 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10009658
   10004584 |       10009660 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10009660
   10004584 |       10009661 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10009661
   10004584 |       10009669 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10009669
   10004584 |       10009670 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10009670
   10004584 |       10009766 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10009766
   10004584 |       10009863 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10009863
   10004584 |       10009867 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10009867
   10004584 |       10009868 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10009868
   10004584 |       10009899 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10009899
   10004584 |       10009900 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10009900
   10004584 |       10010947 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10010947
   10004584 |       10011068 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10011068
   10004584 |       10011714 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10011714
   10004584 |       10012262 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10012262
   10004584 |       10012272 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10012272
   10004584 |       10012394 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10012394
   10004584 |       10012395 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10012395
   10004584 |       10012396 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10012396
   10004584 |       10012432 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10012432
   10004584 |       10012435 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10012435
   10004584 |       10012437 | https://arctos.database.museum/project/10004584 | https://arctos.database.museum/publication/10012437

[ebraker]

@dustymc Looks good to me!

[dustymc]

done

[ebraker]

@dustymc Thank you!

Will you do the same for https://arctos.database.museum/project/10004588 UCM:Herp? I've got the rest.

[dustymc]

Yup, as long as I didn't mangle the first one...

INSERT 0 59

[ebraker]

Thank you!