Create SQLite databases for data pub

[dlebauer]

• Seasons: 4&6
• Data Products:
  • Las plot clip
  • RGB plot clip
  • Thermal plot clip (season 6 only)
• Changes to sample:
  • all climate variables should be trimmed to 4 significant digits 123.4 or 0.01234)
  • remove gene_markers table
  • add data products (I only see RGB)

if it would help you avoid moving data around, you can run your scripts against the filesystem at workbench.terraref.org

TODO

• [x] Chris add code to data-publication repo
• [x] David review databases
• [ ] David document and upload to dryad submission

[Chris-Schnaufer]

@dlebauer Some questions and comments:

• ~Where can I find the date ranges for Seasons 4&6, or do I assume Jan 1 through Jun 30 2017/2018?~ Season 4 is 2017-04-13 to 2017-09-21 and Season 6 is 2018-04-20 to 2018-08-02
• For the dates in the DB you were looking at, RGB plot was the only sensor available
• I will look into workbench.terraref.org
• What's the date range you'd like to see in each database (minimum would be one per season)?

[dlebauer]

Seasons

Season	Planting Date	Harvest
4	2017-04-13	2017-09-21
6	2018-04-20	2018-08-02

These can be found at https://terraref.ncsa.illinois.edu/bety/api/v1/experiments?name=~Season+6 and https://terraref.ncsa.illinois.edu/bety/api/v1/experiments?name=~Season+4

re: splitting up the databases. I would split as little as possible.

• If you want to keep the size down, would it make sense to provide just a database with the 'unified' table?
• if you want to improve performance, then does indexing take care of this? I suspect start_date, cultivar_name, plot_name, format, and sensor would be the most important fields to index.

[Chris-Schnaufer]

Indexing is very important for performance. The 'unified' table is a view and therefore prevents data duplication and keeps the size small. Making it a materialized view or as a single table would increase the size of the DB due to duplication.

[dlebauer]

@Chris-Schnaufer That makes sense - no need to worry about creating a materialized view - that is a pretty low priority.

[Chris-Schnaufer]

@dlebauer I'm not finding any masked plot level images to work with

[dlebauer]

you are correct that there are no plot level masks :-( I've updated the requirements

[Chris-Schnaufer]

I am not able to run this using Globus. It will always timeout before the run is complete. I'm waiting for the problems with workbench to be resolved via Rob Kooper or someone he sends me to

[Chris-Schnaufer]

Finishing this issue and writing a new ticket for remaining work that's dependent upon terraref workbench access

[Chris-Schnaufer]

https://app.zenhub.com/workspaces/ua-ag-data-science-5a57a3198339f11ba1c85775/issues/agpipeline/issues-and-projects/146

[dlebauer]

reopening - keeping as a placeholder for github.com/agpipeline/issues-and-projects/issues/146

[dlebauer]

@Chris-Schnaufer could you please check the code you used into https://github.com/terraref/data-publication/tree/master/code? (it doesn't have to be pretty, but hopefully commented so it could be used and adapted in the future)

[Chris-Schnaufer]

@dlebauer done: https://github.com/terraref/data-publication/tree/sqlite_jupyter_scripts

[dlebauer]

Remaining tasks are in https://github.com/az-digitalag/organization/issues/254