Arctos: Specimen event bulkloader rounding verbatim coordinates to nearest degree in locality coordiantes.

[mvzhuang]

Issue Documentation is http://handbook.arctosdb.org/how_to/How-to-Use-Issues-in-Arctos.html

Describe the bug
Events loaded with the specimen event bulkloader is loading verbatim coordinates appropriately, but rounds the coordinates in locality to nearest degree.

i.e.
https://arctos.database.museum/guid/UTEP:Ento:10621

To Reproduce
Steps to reproduce the behavior:
1) batch load specimen events.

Expected behavior
Load the coordinates to both the collecting event and locality unless otherwise designated. Example data here. I already loaded these, but this is what my file looks like. Any easy way to fix this or should I delete everything?
insecgeoref2_readyforupload.zip

Screenshots
If applicable, add screenshots to help explain your problem.

* Data*
If this involves external data, attach the __actual__ data that caused the problem. Do not attach a transformation or subset. You may ZIP most formats to attach, or request a Box email address for very large files.

Desktop (please complete the following information):

• OS: [e.g. iOS]
• Browser [e.g. chrome, safari]
• Version [e.g. 22]

Additional context
Add any other context about the problem here.

Priority
Please assign a priority-label.

[dustymc]

This should be brought under the "component loader" fold - https://github.com/ArctosDB/arctos/issues/3125

There no way to save any existing data - PG is discarding data in the transformation, everything needs rebuilt around a more appropriate datatype.

[Jegelewicz]

This should be a priority!

[dustymc]

priority

Then polishing up the template in https://github.com/ArctosDB/arctos/issues/2974 should be as well; it's (soft) blocking this.

[mvzhuang]

ok so in mean time, delete events I uploaded, load localities (I assume there won't be transformation on the coordinates if I do it this way) and name them and then reupload events with locality names? Also, I thought there was a delete function in the bulk update specimen events. Did I dream that up? I can't seem to find it or can I send a list of guids to delete accepted events to you @dustymc ?

[Jegelewicz]

@mvzhuang I think I can fix these I fixed the example you gave above manually...

[Jegelewicz]

Looking at the zip file - a lot of these localities are standard, so creating them in advance and naming them probably makes a lot of sense. That way you can just use the locality names in your event loads.

See https://arctos.database.museum/editLocality.cfm?locality_id=11270717

[Jegelewicz]

I just loaded thousands of localities so let me know if you need anything!

[Jegelewicz]

@mvzhuang while making fixes, I have found that you sometimes have what are duplicate localities but the remark means they won't merge. This isn't necessarily a problem, just wanted you to see it.

Searching localities for specific locality =West Virginia University Forest

Results in these:

[Jegelewicz]

I went through the places in the file attached, merged stuff that could be merged and fixed coordinates for those localities. Let me know if there is anything else I can do!

[mvzhuang]

wait how'd you do that? I have a couple thousand more localities that need to be fixed because I was uploading all of our georeferences that we did last month. Is there a way to bulk edit localities? I thought the locality bulkloader only created localities not edited?

[Jegelewicz]

I am merging the ones that are the same, then editing the single locality. Working through by searching on rounded coordinates!

[Jegelewicz]

It's kinda a pain, but also as you go you notice stuff that should probably be merged further (but would have to be done manually).

[mvzhuang]

I think it might be faster to just delete and reupload then. There's about 1800 uniquish localities and I have 7 days left on this ant grant. Also shouldn't our auto locality merger thingy merge stuff and then we can merge further afterwards?

[Jegelewicz]

Also shouldn't our auto locality merger thingy merge stuff and then we can merge further afterwards?

It will eventually. I've probably fixed a couple hundred. Do you have an actual list of the localities or is that just a guess? I actually think I can fix up a file to have Dusty SQL in the coordinates to the existing localities but he's out today as far as I know.

[mvzhuang]

Number is according to geolocate collaborative. I can make a file of current locality nicknames and coordinates. just need to name them all

[campmlc]

Quick related question - how often does the locality merger run? I loaded
specimens with shared localities a few weeks ago, and there were problems
that need fixing. But they all loaded as unique localities - I'm waiting on
the merge to edit. Timeline?

On Wed, Sep 23, 2020 at 1:35 PM Vicky [e-mail] wrote:

• [EXTERNAL]*

Number is according to geolocate collaborative. I can make a file of
current locality nicknames and coordinates. just need to name them all

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
https://github.com/ArctosDB/arctos/issues/3124#issuecomment-697928702,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/ADQ7JBD5NY5G2AAZC6UE7KDSHJEWNANCNFSM4RVDRB3A
.

[Jegelewicz]

You should be able to put a higher geography on this if it has coordinates

https://arctos.database.museum/editLocality.cfm?locality_id=11265512

[Jegelewicz]

@mvzhuang I think I can get a whole bunch of these done fairly quickly - what would help is a list of unique rounded lat/long combos.

[Jegelewicz]

Timeline?

That's a Dusty question.

[mvzhuang]

You should be able to put a higher geography on this if it has coordinates

https://arctos.database.museum/editLocality.cfm?locality_id=11265512

Yea these were the things i wanted to fix after we repatriated the data from geolocate collaborative. Not easy to fix mistakes from the collaborative client. There's a a few like that.

ok can do about the coordinates list.

[mvzhuang]

Took out stuff that has been fixed already. there's about 500 of these though!
UniqueRoundedLatLong.zip

[Jegelewicz]

[mvzhuang]

ok thanks!

[Jegelewicz]

I think we can whip these out pretty quick if we teamwork it!

[Jegelewicz]

I added coordinates for all localities in the following groups:
All 9 or -9 latitudes
All 8 or -8 latitudes
All 33 or -33 latitudes
All 34 or -34 latitudes
All 35 or -35 latitudes

I'll pick away at these while watching TV tonight too.

[Jegelewicz]

All 7 or -7 latitudes
All 6 or -6 latitudes

[mvzhuang]

ok i'm working through some 40s!

[Jegelewicz]

Anything with a latitude that starts with 5 or -5 is done!

[mvzhuang]

I paused to try to get all the localities merged. Hopefully that will make things faster

[Jegelewicz]

Just finished up the latitude 3 and -3

[Jegelewicz]

Gotta shut down for the night. I'll get back to it in the am.

[dustymc]

This should be fixed, but still needs converted to the "component loader" format.

There's a PG trigger on the temp table that converts various formats to DD.dd format and loads the conversion product, which was being returned in an inappropriate Oracleish datatype. That should be avoided if possible - I think PG12's generated columns would work, and be a lot less opaque.

I can delete anything that needs deleted.

The auto-merger doesn't do anything for 30 days, and then it's wildly variable - maybe 10 minutes, possibly months if there's lots to do. It was designed to run instantly, which seems to work a lot better to me, but there was pushback on that. Issue....

[Jegelewicz]

@mvzhuang I have some calls to take today, but can continue helping with this!

[mvzhuang]

Same. I have interviews and some meetings until afternoon

From: Teresa Mayfield-Meyer [e-mail]
Sent: Thursday, September 24, 2020 8:06 AM
To: ArctosDB/arctos [e-mail]
Cc: Zhuang, Mingna [e-mail]; Mention [e-mail]
Subject: Re: [ArctosDB/arctos] Specimen event bulkloader rounding verbatim coordinates to nearest degree in locality coordiantes. (#3124)

@mvzhuanghttps://github.com/mvzhuang I have some calls to take today, but can continue helping with this!

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHubhttps://github.com/ArctosDB/arctos/issues/3124#issuecomment-698367403, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AJHJ3OLHRTRQBKV2NDHFGQ3SHNG6HANCNFSM4RVDRB3A.

[Jegelewicz]

Like JOB interviews? We need to have a call....

[mvzhuang]

no no hiring work study interviews. I finagled another work study out of the community work study program but they didn't tell us how to hire them really until the beginning of the semester, so just got applicants finished last week.

[mvzhuang]

@dustymc ok, here's the file of all the GUIDS that I need "accepted" specimen events deleted from. Let me know if I need to send it to you in any other format.
SpecimenEventGuids_Deletion.zip

I'll reupload the specimen events for those ones after.
Also tested the specimen event bulkloader and looks like everything works.

[Jegelewicz]

@mvzhuang @dustymc

Just noticed this

https://arctos.database.museum/Locality.cfm?Action=editCollEvnt&collecting_event_id=11716867

Not sure what happened here, but this should probably not happen?

Also, I guess I should just stop adding coordinates to localities?

[dustymc]

I can smell the Excel from here....

[Jegelewicz]

Should that cause some sort of error so that we don't get stuff like that?

[Jegelewicz]

I mean, I guess it is VERBATIM, so "#NAME?" must be what was in there! UGH

[mvzhuang]

probably is my fault. Something funky happened when I was messing with formulas maybe. looks like that's the only one it happened to. and yes can stop adding coordinates.

[dustymc]

https://github.com/ArctosDB/arctos/issues/2678 is capable of that (if there's some pattern which can be defined), but seems to be stuck in the kind of limbo from which few things recover.

@mvzhuang those events should be gone

[mvzhuang]

Ok it looks like when the date got transcribed, it got transcribed as "-7-Aug-2020" and then Excel's autoformat decided to be annoying somewhere along the process...
maybe anything with a # sign? I can't imagine why that would ever be in verbatim date.

yay everything has coordinates that make sense now! Thanks Teresa and Dusty!!

[Jegelewicz]

The actual issue seems to be fixed? Closing.