NWP's 30 year statistics

[david-a-ries]

Portland district computed 30 year statistics for a large number of parameters and many different location (examples below). The version CENWP-30year-thru2023-STATS
Those stats are stored as seasonal location level which repeat on a one year period.
Can they be computed using built in Oracle functions? Or is there a better way to be storing these other than in location levels? These won't be updated EVERY year but will probably be updated every ~5 year. Additionally there may be a use case for doing Stats for different time ranges so planning for a path forward should include the ability to update these statistics.

Additionally, the scripts used to generate the 30 year statics were done using comp engine, so there will need to be a path forward.

[ZoeMillerUSACE]

LRE has not quite the same - but somewhat similar statistics. Some key statistics in our office are "Coordinated Monthly Statistics" or "Coordinated Annual Statistics". They are the bread and butter of what we use as a reference for current conditions - for a number of parameters - including Water Levels, Precipitation , and NBS. However, we currently have run into issues trying to store the monthly time series using location levels. The work around (not at all ideal), is to store some "fake" time series in teh year 1900. However this is not ideal because in order to query the data - it shows up public but is not actually data from 1900 (and there is data for the year 1900). I think it would be a helpful addition if there was a "non year" that shows up in the Location Levels for statistics like what David is refering to. LRE would use it.

However, just FYI - while I'm not 100% sure about this - it seemed that monthly location levels were causing issues with something with the CWMS DB "LRE has a location level causing errors for the CWMS DB table view AV_LOCATION_LEVEL_CURVAL. Looks like it is a date format issue. I don’t know which one it is.

Below are example endpoints you can view the output. This endpoint supports a GIS services including dashboards, briefings, and USACE Open Data.
". So we had to flat our delete the monthly location levels. The other work around right now is to now use CWMS and just store things external to CWMS, create plots using R that reference csv files on our SHARE.

[msweier]

This isn't quite the same thing, but you could consider using cwms-python in a batch job. MVP is doing this for flows and elevations to generate a plotly plot. We aren't storing the statistics generated anywhere other than in the plot which is regenerated from POR daily. Could be used to create the end product but doesn't solve the storage problem.

https://water.usace.army.mil/office/mvp/location/LockDam_04/Timeseries%20Plots

[MikeNeilson]

but you could consider using cwms-python in a batch job

That doesn't handle the situation of "how to store the data". Plenty of ways to generate the data.

[jbatterman]

NWO and MRBWM store 30-year average SWE data, both site-specific from the NRCS and also basin averages generated ourselves. Currently these are stored in a repeating time series but the idea of doing a location level is appealing.

All our region's offices compute statistics of some sort for reports, etc. For NWO, they report some statistics (like top 3 daily elevations in the POR) on a bulletin, and initially the idea was to generate those values in a time series, but now they are computed on the fly when the bulletins are generated. On the T7 Oracle side this works quickly for the report, but in CDA that bulletin takes 30+ minutes to run, so that might not be the best way to do it in the cloud. An example of one of these bulletins is at this link:
https://www.nwd-mr.usace.army.mil/rcc/nwo/jamesbull.pdf

[DanielTOsborne]

but you could consider using cwms-python in a batch job

That doesn't handle the situation of "how to store the data". Plenty of ways to generate the data.

Why does it need to be stored?
We compute data like that, but we just do it mostly on demand. We produce reports with that type of data, but since it doesn't change much, we just produce it in static reports. I haven't had a reason to resave all that data to the database.

[ZoeMillerUSACE]

@DanielTOsborne - We have "Official Coordinated" Statistics (which like David - are only updated either once a year, or every ~5 years). These are different than on the fly Statistics. We compare (binationaly with Canada) to the Coordinated Statistics. So for us - having a stored value for those is prefered (and since the period of record is to 1900 for some), calculating on the FLy seems a little inefficient also. But for on the fly stats - that makes sense.
Also - our "Coordinated Stats" use very specific set of steps and unique rounding - that default calculations in CWMS or OpenDCS dont folllow - so we cant rely on being able to reproduce the Coordinated Stats from calculations on the fly

[DanielTOsborne]

Perhaps I'm not understanding, but why wouldn't the existing tools work?
Like "ALBO.Flow.Ave.1Day.30Year.CENWP" or
"ALBO.Flow.Ave.1Day.1Day.CENWP-30Year"
With an Average comp that does the work?