Create Endpoint to Expose Authoritative TimeSeries

[krowvin]

Statement

Propose we create an endpoint that allows end-users to query a project and have it return the authoritative TimeSeries for a given gage or project.

Goal

Data consistency, to ensure districts and other outside sources use the same timeseries. If one changes, the other could still be present unless we pull from these authoritative timeseries.

Background

These TimeSeries are deemed to be authoritative through the CWMS Management Application (CMA) and are upward reported to A2W

Tables exist now for these TimeSeries.

Note

Here is an example URL/ response from the new A2W API:
URL:
https://domain/cda/reporting/providers/lrl/locations/caesarcreek

Click to see the response

JSON Response

Details

[
  {
    "provider": "lrl",
    "code": "CaesarCreek",
    "slug": "caesarcreek",
    "elevation": 846,
    "horizontal_datum": "NAD27",
    "vertical_datum": "NGVD29",
    "state": "OH",
    "kind": "PROJECT",
    "nearest_city": "Waynesville, OH",
    "public_name": "Caesar Creek Lake",
    "location_code": 1064109,
    "nidid": "",
    "aliases": {
      "NIDID": "OH00927"
    },
    "timeseries": [
      {
        "tsid": "CaesarCreek.Elev.Inst.0.0.lrldlb-rev",
        "label": "Elevation",
        "base_parameter": "Elev",
        "parameter": "Elev",
        "unit": "ft",
        "unit_long_name": "Feet",
        "latest_time": "2024-11-21T11:00:00Z",
        "latest_value": 846.41,
        "delta24hr": -0.11,
        "sort_order": 1
      },
      {
        "tsid": "CaesarCreek.Precip.Total.~1Day.1Day.lrldlb-rev",
        "label": "Precipitation",
        "base_parameter": "Precip",
        "parameter": "Precip",
        "unit": "in",
        "unit_long_name": "Inches",
        "latest_time": "2024-11-21T11:00:00Z",
        "latest_value": 0.13,
        "delta24hr": 0.13,
        "sort_order": 16
      },
      {
        "tsid": "CaesarCreek.Flow-Inflow.Ave.1Hour.6Hours.lrldlb-comp",
        "label": "Inflow",
        "base_parameter": "Flow",
        "parameter": "Flow-Inflow",
        "unit": "cfs",
        "unit_long_name": "Cubic feet per second",
        "latest_time": "2024-11-21T11:00:00Z",
        "latest_value": 0,
        "delta24hr": -65.87,
        "sort_order": 6
      },
      {
        "tsid": "CaesarCreek.Stor.Inst.1Hour.0.lrldlb-comp",
        "label": "Conservation Storage",
        "base_parameter": "Stor",
        "parameter": "Stor",
        "unit": "ac-ft",
        "unit_long_name": "Acre-feet",
        "latest_time": "2024-11-21T11:00:00Z",
        "latest_value": 94825.4,
        "delta24hr": -301.4,
        "sort_order": 13
      },
      {
        "tsid": "CaesarCreek.Stor.Inst.1Hour.0.lrldlb-comp",
        "label": "Flood Storage",
        "base_parameter": "Stor",
        "parameter": "Stor",
        "unit": "ac-ft",
        "unit_long_name": "Acre-feet",
        "latest_time": "2024-11-21T11:00:00Z",
        "latest_value": 94825.4,
        "delta24hr": -301.4,
        "sort_order": 11
      },
      {
        "tsid": "CaesarCreek.Flow-Outflow.Inst.0.0.lrldlb-comp",
        "label": "Outflow",
        "base_parameter": "Flow",
        "parameter": "Flow-Outflow",
        "unit": "cfs",
        "unit_long_name": "Cubic feet per second",
        "latest_time": "2024-11-21T11:00:00Z",
        "latest_value": 239.41,
        "delta24hr": -0.11,
        "sort_order": 7
      }
    ],
    "levels": [
      {
        "slug": "elev.bottom of flood",
        "label": "Bottom of Flood",
        "parameter": "Elev",
        "base_parameter": "Elev",
        "tsid": "CaesarCreek.Elev.Inst.0.Bottom of Flood",
        "latest_value": 846.32,
        "units": "ft",
        "units_long_name": "Feet"
      },
      {
        "slug": "elev.bottom of flood control",
        "label": "Bottom of Flood Control",
        "parameter": "Elev",
        "base_parameter": "Elev",
        "tsid": "CaesarCreek.Elev.Inst.0.Bottom of Flood Control",
        "latest_value": 846.32,
        "units": "ft",
        "units_long_name": "Feet"
      },
      {
        "slug": "elev.bottom of inlet",
        "label": "Bottom of Inlet",
        "parameter": "Elev",
        "base_parameter": "Elev",
        "tsid": "CaesarCreek.Elev.Inst.0.Bottom of Inlet",
        "latest_value": 740.56,
        "units": "ft",
        "units_long_name": "Feet"
      },
      {
        "slug": "elev.bottom of normal",
        "label": "Bottom of Normal",
        "parameter": "Elev",
        "base_parameter": "Elev",
        "tsid": "CaesarCreek.Elev.Inst.0.Bottom of Normal",
        "latest_value": 846,
        "units": "ft",
        "units_long_name": "Feet"
      },
      {
        "slug": "elev.guide curve",
        "label": "Guide Curve",
        "parameter": "Elev",
        "base_parameter": "Elev",
        "tsid": "CaesarCreek.Elev.Inst.0.Guide Curve",
        "latest_value": 846,
        "units": "ft",
        "units_long_name": "Feet"
      },
      {
        "slug": "elev.streambed",
        "label": "Streambed",
        "parameter": "Elev",
        "base_parameter": "Elev",
        "tsid": "CaesarCreek.Elev.Inst.0.Streambed",
        "latest_value": 740.56,
        "units": "ft",
        "units_long_name": "Feet"
      },
      {
        "slug": "elev.top of conservation",
        "label": "Top of Conservation",
        "parameter": "Elev",
        "base_parameter": "Elev",
        "tsid": "CaesarCreek.Elev.Inst.0.Top of Conservation",
        "latest_value": 849,
        "units": "ft",
        "units_long_name": "Feet"
      },
      {
        "slug": "elev.top of dam",
        "label": "Top of Dam",
        "parameter": "Elev",
        "base_parameter": "Elev",
        "tsid": "CaesarCreek.Elev.Inst.0.Top of Dam",
        "latest_value": 904,
        "units": "ft",
        "units_long_name": "Feet"
      },
      {
        "slug": "elev.top of flood",
        "label": "Top of Flood",
        "parameter": "Elev",
        "base_parameter": "Elev",
        "tsid": "CaesarCreek.Elev.Inst.0.Top of Flood",
        "latest_value": 883,
        "units": "ft",
        "units_long_name": "Feet"
      },
      {
        "slug": "elev.top of normal",
        "label": "Top of Normal",
        "parameter": "Elev",
        "base_parameter": "Elev",
        "tsid": "CaesarCreek.Elev.Inst.0.Top of Normal",
        "latest_value": 846,
        "units": "ft",
        "units_long_name": "Feet"
      },
      {
        "slug": "elev.top of overflow",
        "label": "Top of Overflow",
        "parameter": "Elev",
        "base_parameter": "Elev",
        "tsid": "CaesarCreek.Elev.Inst.0.Top of Overflow",
        "latest_value": 883,
        "units": "ft",
        "units_long_name": "Feet"
      },
      {
        "slug": "stor.bottom of flood",
        "label": "Bottom of Flood",
        "parameter": "Stor",
        "base_parameter": "Stor",
        "tsid": "CaesarCreek.Stor.Inst.0.Bottom of Flood",
        "latest_value": 94601.33,
        "units": "ac-ft",
        "units_long_name": "Acre-feet"
      },
      {
        "slug": "stor.bottom of flood control",
        "label": "Bottom of Flood Control",
        "parameter": "Stor",
        "base_parameter": "Stor",
        "tsid": "CaesarCreek.Stor.Inst.0.Bottom of Flood Control",
        "latest_value": 94582.26,
        "units": "ac-ft",
        "units_long_name": "Acre-feet"
      },
      {
        "slug": "stor.bottom of inlet",
        "label": "Bottom of Inlet",
        "parameter": "Stor",
        "base_parameter": "Stor",
        "tsid": "CaesarCreek.Stor.Inst.0.Bottom of Inlet",
        "latest_value": 0,
        "units": "ac-ft",
        "units_long_name": "Acre-feet"
      },
      {
        "slug": "stor.bottom of normal",
        "label": "Bottom of Normal",
        "parameter": "Stor",
        "base_parameter": "Stor",
        "tsid": "CaesarCreek.Stor.Inst.0.Bottom of Normal",
        "latest_value": 93700,
        "units": "ac-ft",
        "units_long_name": "Acre-feet"
      },
      {
        "slug": "stor.guide curve",
        "label": "Guide Curve",
        "parameter": "Stor",
        "base_parameter": "Stor",
        "tsid": "CaesarCreek.Stor.Inst.0.Guide Curve",
        "latest_value": 93700,
        "units": "ac-ft",
        "units_long_name": "Acre-feet"
      },
      {
        "slug": "stor.streambed",
        "label": "Streambed",
        "parameter": "Stor",
        "base_parameter": "Stor",
        "tsid": "CaesarCreek.Stor.Inst.0.Streambed",
        "latest_value": 0,
        "units": "ac-ft",
        "units_long_name": "Acre-feet"
      },
      {
        "slug": "stor.top of conservation",
        "label": "Top of Conservation",
        "parameter": "Stor",
        "base_parameter": "Stor",
        "tsid": "CaesarCreek.Stor.Inst.0.Top of Conservation",
        "latest_value": 102000,
        "units": "ac-ft",
        "units_long_name": "Acre-feet"
      },
      {
        "slug": "stor.top of flood",
        "label": "Top of Flood",
        "parameter": "Stor",
        "base_parameter": "Stor",
        "tsid": "CaesarCreek.Stor.Inst.0.Top of Flood",
        "latest_value": 242200,
        "units": "ac-ft",
        "units_long_name": "Acre-feet"
      },
      {
        "slug": "stor.top of normal",
        "label": "Top of Normal",
        "parameter": "Stor",
        "base_parameter": "Stor",
        "tsid": "CaesarCreek.Stor.Inst.0.Top of Normal",
        "latest_value": 93700,
        "units": "ac-ft",
        "units_long_name": "Acre-feet"
      },
      {
        "slug": "stor.top of overflow",
        "label": "Top of Overflow",
        "parameter": "Stor",
        "base_parameter": "Stor",
        "tsid": "CaesarCreek.Stor.Inst.0.Top of Overflow",
        "latest_value": 242200,
        "units": "ac-ft",
        "units_long_name": "Acre-feet"
      }
    ],
    "geometry": {
      "type": "Point",
      "coordinates": [-84.05389, 39.49028]
    }
  }
]

Not to completely take what they have done, but consider the keys presented and if it makes sense for us to return those as well.

PS. I know LRL is needing this to move forward

[MikeNeilson]

This somewhat matches a concept of "virtual time series" for period of record that has been discussed previously.

Both @msweier and @jbkolze have made similar pitches.

Let's leave this issue open, but create a discussion to better formalize the design.

Definitely good to know from Jeremy if some form of that exists, presumably the A2W table, but those are getting updated (properly normalized) and would not necessarily include the period of record based on how we're starting to define such.

It would seem we should properly define what we mean by "Authoritative TimeSeries", Period of Record, and possibly other terms.

[krowvin]

create a discussion to better formalize the design.

Just migrated this to a discussion so it's not lingering for now

It would seem we should properly define what we mean by "Authoritative TimeSeries", Period of Record, and possibly other terms.

Agreed

But you must also ask if A2W also plans to change this for the endpoint we pulled this from. At least have open discussion about it so what A2W is pulling matches what we go with (I.e. returning the same TS)

There's an issue in that this will take some serious time and we have a need now to see what TS A2W/districts deem "Use this thing here - it's the one we like" (for lack of a proper definition)

So then, I pose, is it unreasonable to flesh out the endpoint now. With how it should return. Then as the backend matures update the DTO / etc so that it pulls from VirtualTS/etc?

[msweier]

This somewhat matches a concept of "virtual time series" for period of record that has been discussed previously.

@MikeNeilson, it is related, but I think they are separate issues. I just made a discourse post for discussing on how we may want to deal with storing period of record data in one timeseries (https://discourse.hecdev.net/t/period-of-record-timeseries/3859). I think this topic is more geared toward how to link the period of record timeseries (or whatever best available a district wants to display) to the Access 2 Water website, the district website, and make it obvious at the CDA endpoint. I know our district's database has got way too many timeseries for anyone to be able to figure out which is operational or QA/QC'd.

[MikeNeilson]

NOTE: I do like the idea, just trying to avoid any unnecessary duplication or interference with other work.

[msweier]

I think part of the issue is the field needs more guidance on how to assign something outside of CMA. In theory, when the authoritative timeseries that combine historical and realtime data are created (or have been at some districts) some redundancies begin to show up because the TSID will no longer describe the data in the traditional sense. Each site could have something like:

Loc_ID.Elev.Inst.~15Minutes.0.best-DATUM
Loc_ID-Tailwater.Elev.Inst.~15Minutes.0.best-DATUM
Loc_ID.Flow-Out.Inst.~15Minutes.0.best
Loc_ID.Flow-In.Inst.~15Minutes.0.best
Loc_ID.Stor.Inst.~15Minutes.0.best

But there are so many nuances to each district and projects (e.g. some use Stage-Pool, Loc-Lake.Stage etc...) you almost have to have them assigned to each project to accommodate. CMA does this now, but what will in the future? Timeseries groups shows some promise, and one could make that work, we would need some guidance on how it should be set up. Ideally the CWMS-VUE GUI would be changed for this specific purpose to make it obvious (e.g. each project needs has one spot for Pool Elevation, Tail Elevation, Inflow, Outflow etc...), but that upgrade would probably take some time.

[krowvin]

@MikeNeilson Can we bump this in the priority/fund this? Is this something that relies on Virtual TimeSeries to be completed first?

This comes up in conversations once a month. Even if we don't use Virtual TS we could use the existing mechanism A2W uses to get the TS to use from CMA. While users could query A2W for these TS, seems reasonable to at least start an authoritative endpoint (or whatever it should be called)

@jbkolze for following

[MikeNeilson]

I was thinking about that myself, I need to go back through everything we've written up first.

[MikeNeilson]

Anyone remember where are meeting notes are?
I just read @adamkorynta 's suggestion about the intervals in the discourse post and I rather like that idea as it leaves the version free for say "Period-Of-Record" and other uses.

[krowvin]

@msweier Do you recall if the notes we had were publicly linkable? I think they're in a onenote

[Enovotny]

The notes for virtual timeseries are here
https://www.hec.usace.army.mil/confluence/spaces/CWMS/pages/290456000/Virtual+Timeseries

I think this also relates to the work with Russ and CWMS-vue to create a new CMA/upgrade the access to water tables. Russ was going to start design for that soon after the stand alone version of CWMS-vue is released.