Deprecate NARDEV Postgres database

[philipbaileynar]

We currently host and maintain a Postgres database on AWS that stores two things:

• BRAT parameters
• VBET observations

No one uses these data 99.9% of the time but we are paying for the service 24/7. AWS is about to retire the version of RDS that it's hosted on and I'm proposing that we deprecate the RDS data and use different solutions for these two types of data.

BRAT Parameters

The true BRAT parameters are in git here. These CSV files contain the values that sqlBRAT actually uses. The Postgres database was created merely to provide an easy way to query and manipulate the values.

I propose a new strategy:

1. A Python script in the RSTools repo that can build an ephemeral SQLite database containing the BRAT parameters. This SQLite database is never committed to git. The CSV files remain the truth.
2. A Python script that can dump the SQLite database to CSV files.

The goal is that data analysts can still work with a SQL database to query, review and enter data. But this is done locally rather than using an expensive RDS instance.

VBET observations

We archive these values in a GeoPackage. Shelby, @jtgilbert or @joewheaton can use this database should they want to perform further analysis or draft illustrations for a VBET paper.

FYI @MattReimer

[shelbysawyer]

@philipbaileynar Just making sure that I understand-- with the archived geopackage, I could download it and make (local) changes, right? Specifically, easily add observations if needed? I have used it for a 3-panel figure which will likely undergo some changes by the time it gets into the manuscript (April).

[philipbaileynar]

You can think of it as a ShapeFile. You can edit the data. There just won't be a centrally hosted online database.

Given that these data are going in a manuscript, the proper thing to do is to make them into a Riverscapes project and upload them to the Data Exchange.

[philipbaileynar]

Or Zenodo where you will get a DOI

[shelbysawyer]

Sounds good. The database, as it currently exists, will need to be reviewed before sharing parts of it either through the DE or Zenodo. This is something I can do, as I have a record of the observationid of each observation that was actually used.

1. The vast majority of the points in the VBET observations database were collected before the VBET algorithm moved to a level path ordering system and, therefore, could not provide suitable data for transform function specification after the update. This is due to fundamental changes in how the composite HAND and Likelihood of Being Valley Bottom raster values are calculated. When the original observations were used to sample VBET lines of evidence (slope, HAND), the slope raster value / likelihood raster value relationship was fine (expected), but the HAND values were not (also expected). Because of this, we created a subset on our local machines (mine and @jtgilbert's) with particular attention to how HAND is calculated for each level path. @jtgilbert Is that something you still have?

2. I have used the database to store independently collected observations of valley bottom for VBET output verification purposes and will very likely add to that subset for the manuscript because currently n = 289. It makes sense to me for this point layer to eventually end up hosted as a layer (or on its own) or on Zenodo. Could the geopackage be structured with the lookup tables so that adding observations is as easy as it was with the hosted database (e.g., click to add a point, dropdown menu of options for vb category)?