search

USACE / cwms-data-api

Corps Water Management System RESTful Data Service
MIT License
13 stars 14 forks source link

timeseries group - office filter not working #252

Closed adamscarberry closed 2 years ago

adamscarberry commented 2 years ago

example: https://cwms-data.usace.army.mil/cwms-data/timeseries/group?office=LRH

Screenshot shows SWF timeseries paths:

image

rma-rripken commented 2 years ago

This is a good catch. I think the query is missing a join on the office_id.

The query is this: select "CWMS_20"."AV_TS_CAT_GRP"."CAT_DB_OFFICE_ID", "CWMS_20"."AV_TS_CAT_GRP"."TS_CATEGORY_ID", "CWMS_20"."AV_TS_CAT_GRP"."TS_CATEGORY_DESC", "CWMS_20"."AV_TS_CAT_GRP"."GRP_DB_OFFICE_ID", "CWMS_20"."AV_TS_CAT_GRP"."TS_GROUP_ID", "CWMS_20"."AV_TS_CAT_GRP"."TS_GROUP_DESC", "CWMS_20"."AV_TS_CAT_GRP"."SHARED_TS_ALIAS_ID", "CWMS_20"."AV_TS_CAT_GRP"."SHARED_REF_TS_ID", "CWMS_20"."AV_TS_GRP_ASSGN"."CATEGORY_ID", "CWMS_20"."AV_TS_GRP_ASSGN"."DB_OFFICE_ID", "CWMS_20"."AV_TS_GRP_ASSGN"."GROUP_ID", "CWMS_20"."AV_TS_GRP_ASSGN"."TS_ID", "CWMS_20"."AV_TS_GRP_ASSGN"."TS_CODE", "CWMS_20"."AV_TS_GRP_ASSGN"."ATTRIBUTE", "CWMS_20"."AV_TS_GRP_ASSGN"."ALIAS_ID", "CWMS_20"."AV_TS_GRP_ASSGN"."REF_TS_ID" from "CWMS_20"."AV_TS_CAT_GRP" left outer join "CWMS_20"."AV_TS_GRP_ASSGN" on ("CWMS_20"."AV_TS_CAT_GRP"."TS_CATEGORY_ID" = "CWMS_20"."AV_TS_GRP_ASSGN"."CATEGORY_ID" and "CWMS_20"."AV_TS_CAT_GRP"."TS_GROUP_ID" = "CWMS_20"."AV_TS_GRP_ASSGN"."GROUP_ID") where "CWMS_20"."AV_TS_CAT_GRP"."GRP_DB_OFFICE_ID" = 'LRH' order by "CWMS_20"."AV_TS_GRP_ASSGN"."ATTRIBUTE"

And I think it should be: select "CWMS_20"."AV_TS_CAT_GRP"."CAT_DB_OFFICE_ID", "CWMS_20"."AV_TS_CAT_GRP"."TS_CATEGORY_ID", "CWMS_20"."AV_TS_CAT_GRP"."TS_CATEGORY_DESC", "CWMS_20"."AV_TS_CAT_GRP"."GRP_DB_OFFICE_ID", "CWMS_20"."AV_TS_CAT_GRP"."TS_GROUP_ID", "CWMS_20"."AV_TS_CAT_GRP"."TS_GROUP_DESC", "CWMS_20"."AV_TS_CAT_GRP"."SHARED_TS_ALIAS_ID", "CWMS_20"."AV_TS_CAT_GRP"."SHARED_REF_TS_ID", "CWMS_20"."AV_TS_GRP_ASSGN"."CATEGORY_ID", "CWMS_20"."AV_TS_GRP_ASSGN"."DB_OFFICE_ID", "CWMS_20"."AV_TS_GRP_ASSGN"."GROUP_ID", "CWMS_20"."AV_TS_GRP_ASSGN"."TS_ID", "CWMS_20"."AV_TS_GRP_ASSGN"."TS_CODE", "CWMS_20"."AV_TS_GRP_ASSGN"."ATTRIBUTE", "CWMS_20"."AV_TS_GRP_ASSGN"."ALIAS_ID", "CWMS_20"."AV_TS_GRP_ASSGN"."REF_TS_ID" from "CWMS_20"."AV_TS_CAT_GRP" left outer join "CWMS_20"."AV_TS_GRP_ASSGN" on ("CWMS_20"."AV_TS_CAT_GRP".GRP_DB_OFFICE_ID = "CWMS_20"."AV_TS_GRP_ASSGN".DB_OFFICE_ID and "CWMS_20"."AV_TS_CAT_GRP"."TS_CATEGORY_ID" = "CWMS_20"."AV_TS_GRP_ASSGN"."CATEGORY_ID" and "CWMS_20"."AV_TS_CAT_GRP"."TS_GROUP_ID" = "CWMS_20"."AV_TS_GRP_ASSGN"."GROUP_ID") where "CWMS_20"."AV_TS_CAT_GRP"."GRP_DB_OFFICE_ID" = 'LRH' order by "CWMS_20"."AV_TS_GRP_ASSGN"."ATTRIBUTE"

Could someone run those against the national db and tell me if it fixes the bug? If it does #257 will close this issue.

adamscarberry commented 2 years ago

thanks @rma-rripken

rma-rripken commented 2 years ago

This is apparently not resolved.
Query: select * from CWMS_20.AV_TS_CAT_GRP where TS_CATEGORY_ID = 'Default' Results +----------------+--------------+----------------+----------------+-----------+---------------+------------------+----------------+ |CAT_DB_OFFICE_ID|TS_CATEGORY_ID|TS_CATEGORY_DESC|GRP_DB_OFFICE_ID|TS_GROUP_ID|TS_GROUP_DESC |SHARED_TS_ALIAS_ID|SHARED_REF_TS_ID| +----------------+--------------+----------------+----------------+-----------+---------------+------------------+----------------+ |CWMS |Default |Default |CWMS |Default |All Time Series|null |null | +----------------+--------------+----------------+----------------+-----------+---------------+------------------+----------------+

This query shows that there is a TS Category called "Default" and it has a group called "Default"

Lets see what is inside the group: select * from CWMS_20.AV_TS_GRP_ASSGN where GROUP_ID = 'Default'

+-----------+--------+-------+------------+-----------------------------------+--------+---------+---------+---------------+----------------+ |CATEGORY_ID|GROUP_ID|TS_CODE|DB_OFFICE_ID|TS_ID |ALIAS_ID|ATTRIBUTE|REF_TS_ID|SHARED_ALIAS_ID|SHARED_REF_TS_ID| +-----------+--------+-------+------------+-----------------------------------+--------+---------+---------+---------------+----------------+ |Default |Default |1493251|SWT |AARK.Flow.Ave.5Minutes.10Minutes.ks|null |null |null |null |null | |Default |Default |1251 |SWT |AARK.Flow.Inst.15Minutes.0.Ben |null |null |null |null |null | +-----------+--------+-------+------------+-----------------------------------+--------+---------+---------+---------------+----------------+

So it looks like there are two timeseries assignments inside the TS group.

But look at the DB_OFFICE_ID column - its SWT.
Now look back at the office columns in the AV_TS_CAT_GRP results. Both of them are CWMS. hmm

rma-rripken commented 2 years ago

The AV_TS_GRP_ASSGN.DB_OFFICE_ID column is not the office id of the grp as I assumed. Its the office id of the location. This can still be made to work.

rma-rripken commented 2 years ago

Its somewhat harder for me to test b/c I don't have a database which mixes officeIds in it. #263 should fix this issue.

rma-rripken commented 2 years ago

@adamscarberry can you check this issue again? PR #263 was merged and is running at https://cwms-data.usace.army.mil/cwms-data/swagger-ui.html I just checked and didn't see any SWF assignments when filtering for LRH so I think its fixed.

adamscarberry commented 2 years ago

Thanks @rma-rripken. I only see LRH, so it appears fixed to me.