search

ArctosDB / arctos

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

USGS data download and identifier sorting request #8042

Closed jldunnum closed 5 days ago

jldunnum commented 2 weeks ago

How can I download the specimen records in this project (https://arctos.database.museum/search.cfm?project_id=10002364) using the advanced_bio_geo profile and then get the identifiers into separate columns so we can sort by "United States Geological Survey identifier" and display the BS/FC number in its own column? I tried doing an identifier download and merge but it crashed two different computers so far. Plus I don't want to lose the other identifers if I filter specifically for "United States Geological Survey Identifier". Used to be able to simply select this identifier in my profile and all was great in seconds. Now I spend hours trying to do work arounds and still fail........ USGS Deputy Regional Director is arriving tomorrow morning and wants to see their data and learn how to navigate Arctos and I can't even do it myself anymore much less teach her. Any assistance would be most appreciated.

dustymc commented 2 weeks ago

I can't do anything in the UI by tomorrow, and I can't do much in the UI as long as there are a bunch of denormalizing types hanging around. There's a fairly thorough demo of what can be done if we can get past the blocks in https://github.com/ArctosDB/arctos/issues/6983 - it basically boils down to "select this identifier in my profile and all was great in seconds" but sorta accidentally avoids a bunch of ways that these data become scattered and greatly simplifies everything in the process. Maybe we can get together with @mkoo and sort that out soonish?

Anyway, data below. I added a "usgs" column to https://arctos.database.museum/search.cfm?project_id=10002364&sp=advanced_bio_geo, results attached, let me know if I can do something else.

temp_usgs.csv.zip

mkoo commented 2 weeks ago

@jldunnum what are the fields you would ideally like to see? (please list them all: GUID, sci_name, USGS identifer and value, verbatim date, spec_localties, dec_lat, dec_long, datum etc etc)

@dustymc can you provide a SQL to retrieve that data in this project? I think this would be a solid use case for a project report, but meanwhile we can use SQL as a workaround for now to generate a spreadsheet

does that work?

jldunnum commented 2 weeks ago

Thanks Dusty and Michelle, I can only access from my phone at the moment so can’t go in too deep easily at the moment. I’ll check out the links when I get back to a computer. Thanks, Jon

Get Outlook for iOShttps://aka.ms/o0ukef

From: Michelle Koo @.> Sent: Monday, August 26, 2024 4:38:47 PM To: ArctosDB/arctos @.> Cc: Jonathan Dunnum @.>; Mention @.> Subject: Re: [ArctosDB/arctos] USGS data download and identifier sorting request (Issue #8042)

[EXTERNAL]

@jldunnumhttps://github.com/jldunnum what are the fields you would ideally like to see? (please list them all: GUID, sci_name, USGS identifer and value, verbatim date, spec_localties, dec_lat, dec_long, datum etc etc)

@dustymchttps://github.com/dustymc can you provide a SQL to retrieve that data in this project? I think this would be a solid use case for a project report, but meanwhile we can use SQL as a workaround for now to generate a spreadsheet

does that work?

— Reply to this email directly, view it on GitHubhttps://github.com/ArctosDB/arctos/issues/8042#issuecomment-2311229000, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AED2PA43B3YIDF3RDLLZVTLZTOU7PAVCNFSM6AAAAABNE2QKVSVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDGMJRGIZDSMBQGA. You are receiving this because you were mentioned.Message ID: @.***>

jldunnum commented 2 weeks ago

That download worked well for the USGS visit Dusty, thanks. Can you do another one for this project (https://arctos.database.museum/search.cfm?customoidoper=LIST&project_id=10002364) with the fields below, the USGS field you previously added, plus fields for entered and edited dates?

use_license_url | collection_object_id | guid | scientific_name | country | state_prov | spec_locality | verbatim_date | dec_lat | dec_long | coordinateuncertaintyinmeters | sex | parts | age_class | othercatalognumbers | phylorder | family | county | nk | identified_by | preparators | remark | accn_number | began_date | minimum_elevation | orig_elev_units | collectors | datum | entered_by | ear_from_notch | hind_foot_with_claw | tail_length | total_length | weight | collector_number | genbank | preparator_number | reproductive_data

Thanks much, Jon

dustymc commented 2 weeks ago

with the fields below

Pretty please: click your link, pick a profile eg...

Screenshot 2024-08-27 at 11 46 59

then...

Screenshot 2024-08-27 at 11 47 47

and send me that URL.

jldunnum commented 2 weeks ago

Here ya go https://arctos.database.museum/search.cfm?customoidoper=LIST&project_id=10002364

Jonathan L. Dunnum Ph.D. (he, him, his) Senior Collection Manager Division of Mammals, Museum of Southwestern Biology Research Assistant Professor (LAT) Department of Biology University of New Mexico Albuquerque, NM 87131 (505) 277-9262 Fax (505) 277-1351

Chair, Systematic Collections Committee, American Society of Mammalogists Latin American Fellowship Committee, ASM

MSB Mammals website: http://www.msb.unm.edu/mammals/index.html Facebook: http://www.facebook.com/MSBDivisionofMammals

Shipping Address: Museum of Southwestern Biology Division of Mammals University of New Mexico CERIA Bldg 83, Room 204 Albuquerque, NM 87131

From: dustymc @.> Sent: Tuesday, August 27, 2024 12:49 PM To: ArctosDB/arctos @.> Cc: Jonathan Dunnum @.>; Mention @.> Subject: Re: [ArctosDB/arctos] USGS data download and identifier sorting request (Issue #8042)

[EXTERNAL]

with the fields below

Pretty please: click your link, pick a profile eg...

Screenshot.2024-08-27.at.11.46.59.png (view on web)https://github.com/user-attachments/assets/26992026-ebdd-4970-b393-981232c8848b

then...

Screenshot.2024-08-27.at.11.47.47.png (view on web)https://github.com/user-attachments/assets/d027ed35-abd5-4f3c-89c0-186c3d6ea3cd

and send me that URL.

— Reply to this email directly, view it on GitHubhttps://github.com/ArctosDB/arctos/issues/8042#issuecomment-2313277657, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AED2PA7K6UCNLSDEPVAOY2DZTTC4HAVCNFSM6AAAAABNE2QKVSVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDGMJTGI3TONRVG4. You are receiving this because you were mentioned.Message ID: @.***>

dustymc commented 2 weeks ago

That isn't it, it should have a "sp={your profile name}" in there somewhere.

jldunnum commented 2 weeks ago

https://arctos.database.museum/search.cfm?customoidoper=LIST&project_id=10002364&sp=Dunnum_USGS

dustymc commented 2 weeks ago

Here you go: temp_usgs.zip

And because I noticed one:

create table temp_dups as select usgs,count(*) c from temp_usgs group by usgs having count(*) > 1;

temp_dups.csv.zip

jldunnum commented 2 weeks ago

Yes, one unfortunate issue is that every taxonomic division within the USGS collection has the same numbering scheme so there could be three or four BS/FC 1.....

Jonathan L. Dunnum Ph.D. (he, him, his) Senior Collection Manager Division of Mammals, Museum of Southwestern Biology Research Assistant Professor (LAT) Department of Biology University of New Mexico Albuquerque, NM 87131 (505) 277-9262 Fax (505) 277-1351

Chair, Systematic Collections Committee, American Society of Mammalogists Latin American Fellowship Committee, ASM

MSB Mammals website: http://www.msb.unm.edu/mammals/index.html Facebook: http://www.facebook.com/MSBDivisionofMammals

Shipping Address: Museum of Southwestern Biology Division of Mammals University of New Mexico CERIA Bldg 83, Room 204 Albuquerque, NM 87131

From: dustymc @.> Sent: Tuesday, August 27, 2024 2:30 PM To: ArctosDB/arctos @.> Cc: Jonathan Dunnum @.>; Mention @.> Subject: Re: [ArctosDB/arctos] USGS data download and identifier sorting request (Issue #8042)

[EXTERNAL]

Here you go: temp_usgs.ziphttps://github.com/user-attachments/files/16768648/temp_usgs.zip

And because I noticed one:

create table temp_dups as select usgs,count() c from temp_usgs group by usgs having count() > 1;

temp_dups.csv.ziphttps://github.com/user-attachments/files/16768659/temp_dups.csv.zip

— Reply to this email directly, view it on GitHubhttps://github.com/ArctosDB/arctos/issues/8042#issuecomment-2313457992, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AED2PA7IFXLYQCQKHGZTO63ZTTOXRAVCNFSM6AAAAABNE2QKVSVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDGMJTGQ2TOOJZGI. You are receiving this because you were mentioned.Message ID: @.***>

dustymc commented 2 weeks ago

done?

jldunnum commented 2 weeks ago

Yup, thanks much! Would be good if we could get something like a report to generate these types of requests going forward.

Get Outlook for iOShttps://aka.ms/o0ukef

From: dustymc @.> Sent: Thursday, August 29, 2024 8:59:29 AM To: ArctosDB/arctos @.> Cc: Jonathan Dunnum @.>; Mention @.> Subject: Re: [ArctosDB/arctos] USGS data download and identifier sorting request (Issue #8042)

[EXTERNAL]

done?

— Reply to this email directly, view it on GitHubhttps://github.com/ArctosDB/arctos/issues/8042#issuecomment-2317987719, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AED2PA22YVFFDXTAGEATDDTZT4ZNDAVCNFSM6AAAAABNE2QKVSVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDGMJXHE4DONZRHE. You are receiving this because you were mentioned.Message ID: @.***>

campmlc commented 2 weeks ago

Is there SQL we can use to get these data? Or to parse any identifier into its own column? Or possible to create a profile with this info?

jldunnum commented 2 weeks ago

Yes, would be great if we could create profiles or simple reporting tools to give to agency people who may need access to their specimen data we manage for them.

Get Outlook for iOShttps://aka.ms/o0ukef

From: Mariel Campbell @.> Sent: Thursday, August 29, 2024 9:39:45 AM To: ArctosDB/arctos @.> Cc: Jonathan Dunnum @.>; Mention @.> Subject: Re: [ArctosDB/arctos] USGS data download and identifier sorting request (Issue #8042)

[EXTERNAL]

Is there SQL we can use to get these data? Or to parse any identifier into its own column? Or possible to create a profile with this info?

— Reply to this email directly, view it on GitHubhttps://github.com/ArctosDB/arctos/issues/8042#issuecomment-2318177624, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AED2PA5EBN5FTKYA2NLKAWDZT46EDAVCNFSM6AAAAABNE2QKVSVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDGMJYGE3TONRSGQ. You are receiving this because you were mentioned.Message ID: @.***>

campmlc commented 2 weeks ago

@dusty - please see questions above.

mkoo commented 6 days ago

Closing this issue (again) since the original request is complete. See this #8091

jldunnum commented 5 days ago

Adding to this thread because todays issue is basically the same type of problem/request. Spent last week at the AMNH and they are eager to have a download of our shared data to update their database and sort out some data problems they have. The MSB worked on a decade long collaborative project with AMNH in the 1980s and 90s in Bolivia. AMNH didn't have a tissue collection at that time so all tissues came to MSB. Thus we have large series of specimens cataloged here which are tissue only and correspond to skins/skels/fluids held at AMNH. These all have NK numbers, MSB numbers and AMNH numbers. I'd like to have a parsed file with MSB guid, AMNH institutional catalog number, NK number, collector name and number, preparator name and number, higher geography, specific locality, georeference data, sex, date. Bottom line is this type of request is going to keep coming up so I really need the ability to use one of my profiles to download records and then parse out all the identifiers into their own columns. Thanks

mkoo commented 5 days ago

Please see #8093 which is a separate request from original