ORSANCO Design Specifications

Technologies in Use

1. Ubuntu 20.04.6 LTS
2. PSQL Server 13.13 (Ubuntu 13.13-1.pgdg20.04+1)
3. Python 3.9.18
4. nginx version: nginx/1.18.0 (Ubuntu)

One Shot cross-section fill

The current Magic number is 4077 that is how many cross-sections there should be.\ The functional specification for this code is to read in the cross-section data from flat files (CSV’s) and import the data into the PostgreSQL database orsanco_flows the tables that will be filled by this code are River, Reach, and Stations.\ The design is as follows:

1. There will be 2 CSV’s
   1. CSV generated from Shape Files using ARCGIS.
   2. CSV that will have the ID from HDF5 file.
2. Check that there are only 4077 cross-sections in the CSVs
3. Both CSV’s will be placed into pandas data frames in python.
4. The Dataframes will be joined by the River, Reach, and Station columns.
5. The Data frames will be written to the Database.

The inserts of all tables took approximately 11 seconds. See the final report for image.

Daily Repeated section

The current Magic numbers for flow and velocity data are 4077 columns and 379 rows. That means that there should be that is how many cross-sections there should be 1545183 rows in the database. The functional Specifications for this code is to read in the flow and velocity data based on cross-section and time from the HDF5 file. Then to import the data into the PostgreSQL database orsanco_flows the tables that will be filled this code are the riverflow and later the dailyriverflow.\

The Design is as follows:

1. We will read in the HDF5 file and navigate through the groups (folders) to get to the correct datasets (data tables). We do this using h5py.
2. We then take the flow dataset and set it to a pandas data frame. The columns are index to the station names. At this time we add in the timestamp values into the dataframe then we call on pandas melt function to change the shape of the dataframe. Instead of being a data frame of 4077 X 379 it will be 3 X 1545183 where the 3 columns are Unix-Time, Station(aka Station_ID aka column Name), Flow value.
3. Next we do the same for Velocity.
4. Join the 2 tables based on Unix-Time and Station.
5. Insert these values into the database.

A design decision was made to insert the data in batches instead of individually as we do for the one shot cross section tables. This was done due to time and efficiency issues. Melissa had it working but it was taking over 30 min to insert the rows. This was only 2 weeks of data we are likely to have 10 years. The method that was used was Pandas Dataframe to sql database. https://stackoverflow.com/questions/29706278/python-pandas-to-sql-with-sqlalchemy-how-to-speed-up-exporting-to-ms-sql

The inserts of the flows table with only 2 weeks of data took approximately 2 minutes and 12 seconds. See the final report for image.

We might in the future look into bulk data import. This will need research done before we attempt it.