FishScapes: Lake Association interviews (data upload)

[cwbeltz]

Dane W. and I have been working on prepping his 2019 data from the FishScapes project for upload as new tables to the MFE database. This includes making adjustments to formats, naming conventions, etc. suggested by @ctsolomon beginning in mid-June 2022. These updates are nearing completion and are tentatively ready to be uploaded to the database pending resolution of two issues and approval from CS and Dane W. All files associated with this database update can be found on Box in my personal archive in the last dated update in archivedDB (currently 20220715, though this will change when actually updated).

The remaining issues are:

1. Big Lake currently exists twice in the LAKES table. The WBIC # that was in there previously (2963800) and the WBIC from Dane are not the same (2334700). Any advice on how to handle this? I don't have much knowledge of WBIC numbers and if this is expected.

2. The LAKE_ASSOCATION table is the junction table connecting the orgs to the multiple lakes when that occurs. I included both the orgID and the orgName, but can remove one or the other to pare it down, if needed.

The update includes eight tables (4 new and 4 modified):

• Four tables we are adding for Dane: LAKE_ORG, LAKE_ORG_INTERVIEW, INTERVIEW_WORKSHEET, and LAKE_ASSOCIATION (i.e., junction table)
• Four tables that are being updated: PROJECTS, UNITS, METADATA, and LAKES.

[ctsolomon]

I responded to your email Chris about the Big Lake issue. Short version: it looks to me like the LAKES table in the current database gives the WBIC for one Big Lake and the lat/long for a different Big Lake - so we need to sort this out and then figure out whether Dane's is a new one or not. I suspect that we're only talking about one Big Lake which got sampled for FishScapes, but I'm not positive until we do a little digging.

[ctsolomon]

One additional issue:

3. We realized that LAKE_ORG_INTERVIEW includes information in the raw notes columns (such as financial data, group conflict, etc.) that we promised not to publicize without anonymizing or aggregating. So this table should either be posted behind some kind of permissions, or else held back from the database. For now we will hold it back from the database (a copy is on my hard drive in the MFE database folder) but we should figure out whether there's a way to post it with restricted permissions.

[ctsolomon]

Resolution of the Big Lake mystery:

I checked SAMPLES and FISH_SAMPLES, and the only samples in the database associated with Big Lake (lakeID=='BG') are from the bluegill survey in 2016-2017. I emailed Alex Ross (and he looped Colin Dassow in to the conversation), asking if he remembered which Big Lake they sampled for the bluegill work. He said that he was nearly certain that the one they sampled is the one up near UNDERC - he knows they launched a boat there a couple times. He has no memory of the other Vilas County Big Lake (the one that Dane interviewed), and he's sure he didn't sample on the Oneida County Big Lake. Colin concurs with all this.

So, the Big Lake that's currently in the database, with lakeID=='BG', is this one, near UNDERC. The WBIC is correctly recorded in the database as 2963800, but the lat/long need to be corrected. The correct lat/long will be something like 46.20002 -89.44638, but we should use the Lake Finder app or whatever our standard approach is for getting lat/long. We should also double-check the other columns in the LAKES table for this entry, I haven't done that yet. The WDNR page on this lake may be helpful in this regard.

We also need to create a new Big Lake in the LAKES table, with some new lakeID. This is the lake that Dane interviewed. The WBIC is 2334700, and the lat/long is something like 46.15484 -89.76957, but we should use the Lake Finder app or whatever our standard approach is for getting lat/long. Again, the WDNR page for this lake may be helpful in filling out other columns for this new entry in the LAKES table.

We should make sure that Dane's data refer to whatever new lakeID we create for the "new" Big Lake, NOT to the existing 'BG' lakeID.

[ctsolomon]

@cwbeltz, a couple more notes about the Big Lake mystery and uploading Dane's data, which I discovered as I was working with these data just now:

• It looks like the LAKES table that was going to get uploaded as part of the Dane update (or at least the version of it that I have, named 'LAKES.20220715.csv') has an extra column at the end, 'maxDdepth', in addition to the 'maxDepth' column.
• It looks like 'BX' would be a good lakeID for the "new" Big Lake (the one with WBIC==2334700). Other possibilities, like BI and BZ, are already taken.
• For this new Big Lake, it looks like surfaceArea (==827) was entered in acres, instead of being converted first to hectares. It should be changed to 335 hectares.
• Similarly, maxDepth can be entered as 18.6 meters for the new Big Lake.

Also - some of Dane's other tables have a lakeID column in them - like LAKE_ORG, at least. Appearances of 'BG' in the lakeID column in these tables should be switched for the correct new lakeID, BX.