River, Reach, Station Explained

In the HDF5 files used for ORSANCO the combination of River. Reach, and Station is the unique cross-section where data is sampled.

We get River Reach and Station values from the Cross Section Attributes dataset in the HDF5 Files.

When we bring this dataset into the program the Index is also brought in as it is the index of the dataset array.

In the program we have a pandas dataframe that was derived from the cross-section attributes dataset. This data frame keeps the index as part of the of a dataframe we name it hdf5_index. We also remove the name column as there is no data in this column.

So the dataframe has hdf5_index, river, reach, and station.

The HDF5_index is important because it is the order of the River stations in the columns of the other datasets like flow, Velocity, and Stage.

Currently there are 4077 cross-sections the indexes of which are between [0-4076].

Following best practices for databases broken up the River, Reach, and Station data into 3 database tables.

River contains an auto_incrementing_Id and the name of the river.

Reach contains an auto_incrementing_Id, the name of the reach, and the river_id the reach belongs to as a foreign key.

Station contains an auto_incrementing_Id, foreign keys for the river and the reach, the name of the station, the HDF5_index and X and Y coordinates that we get from a GIS process.

A question you might ask is why in the world are we keeping around the HDF5_index for the station table when when the auto_incrementing_Id also documents the order.

Answer: In the future we will be adding or removing stations, river, reaches...

When this happens the HDF5 index will change in the new hdf5 files. If a station is added then geographically everything after that station will move down 1. If a station is removed then it will move up 1. The station_id is auto incrementing all new stations will be added at the end. So unless we want to blow away all our data(Which we do NOT) we do not want to tie the Auto incrementing id to the geographic location of the HDF5 file. This is why we have kept the HDF5_index. We will update the HDF5 index by comparing the River, Reach, and Station in the database to the River reach and station in the HDF5 and update the HDF5_index accordingly.

The river and Reach will be modified accordingly. If a new river or Reach is added then the new river or reach will go in as the last entry in there tables.

If a river or reach is removed then the delete needs to cascade down. If the river is removed then the reaches and stations need to be removed. The on delete setting is currently set to null. So we may want to either change that to on delete cascade or to create a script that says if river is null then delete the reach, if the reach is null then delete the station... Cascade will be easier this is what it is designed for.

Updating the river or reach will need to be done manually as well. I doubt the names of the rivers will change much but the reaches can to deal with that we would need to manually create an update statement that would modify the station table.