TSTool / Datastore Reference / ColoradoHydroBaseRest
Overview
The State of Colorado’s HydroBase database is the primary database for water data in Colorado. However, using the HydroBase database datastore or database input type in TSTool (see the HydroBase Datastore appendix) requires a direct connection to a Microsoft SQL Server HydroBase database, and a local installation of the database may not be available. A local database is typically used by State of Colorado staff and consultants or others that need the higher performance of a local database.
IMPORTANT: Data available from web services originate from a wide variety of sources and formats
Data collection and processing methods vary.
Consequently, it is difficult in some cases to confirm data contents without
researching the full data workflow.
For example, SnowDepth.Day could be a mean of snow depth measurements in the day,
the last value in a day, a manually measured value taken at any point in the day,
or other value.
This documentation is general and the documentation for original data sources should be consulted to answer questions.
The ColoradoHydroBaseRest datastore provides access to HydroBase using internet REST web services
and is automatically enabled in TSTool as the HydroBaseWeb datastore.
REST web services use URLs to uniquely identify data resources,
typically in JSON, comma-separated-value, or other format.
The resulting data are converted into time series and other data in TSTool for use with commands.
See the Wikipedia REST article for background on REST web services. Web services allow data to be retrieved and processed locally in tables and time series. Because access to the database is through web services, there is no ability to use Structured Query Language (SQL) to control web service queries. Instead, available web service URL query parameters can be used to control the request.
See the following page for HydroBase web service documentation.
TSTool integrates with web services by specifying appropriate web service request query parameters.
The TSTool log file and the time series dataUrl property after reading
includes the URL that is used to request data.
See also the following documentation that is helpful when trying to understand similarity and differences between database and web service datastores, in particular data types and corresponding intervals used in time series. The ColoradoHydroBaseRest web services and datastore provide access to real-time and historical data, which requires using data type and interval to define unique time series identifiers. This is different than legacy datastores where multiple web services each provided access to some of the HydroBase data and uniqueness was within the specific datastore.
- HydroBase Database Datastore - current software for historical data
- ColoradoWaterHBGuest Datastore - legacy (obsolete) datastore for historical data replaced by ColoradoHydroBaseRest web services
- ColoradoWaterSMS Datastore - legacy (obsolete) data datastore for real-time data replaced by ColoradoHydroBaseRest web services
The ColoradoHydroBaseRest datastore uses new DWR HydroBase web services. The following sections list data types and intervals that are available in this datastore.
Web Service to Time Series Mapping
Time series data objects in TSTool consist of various properties such as location identifier, data type and units, and data arrays for data values and flags. To populate data in a time series typically requires joining the following:
- station or structure data (for location identifier)
- parameter or data type (for data type, data source, and units)
- time series data records as original measurement or calculated interval value
The TSTool main interface browsing tool requires a join of information in order to list time series for selection,
typically using one of the "datatypes" services.
Once the data type and interval are selected, specific time series are listed.
Similar functionality is provided in the
ReadColoradoHydroBaseRest command
to allow automated processing of many time series with a few commands.
Time series that are returned include built-in properties and also a list of properties from web services.
Use the TSTool time series results area Time Series Properties menu item to view time series properties.
The following table summarizes DWR HydroBase REST web services, corresponding to data types in the datastore.
This table may not agree with the latest available web services.
Web services, once implemented, tend not to be modified so that existing data applications will continue to function.
However, new data may be added to web services without impacting existing applications.
If a web service is not directly supported by this datastore,
the TSTool WebGet command can be used to request data and then
other TSTool commands can read and process the data, typically as tables,
including the
ReadTableFromJSON and
ReadTableFromDelimitedFile
commands, which are used for JSON and comma-separated-value files, respectively.
See also more detailed information in the ColoradoHydroBaseRest Time Series Data Types section, which provides a map of all data types and intervals.
Summary of Web Services Implemented in ColoradoHydroBaseRest Datastore
| Web Service | Time Series Data Types | Comments, including HydroBase Datastore Examples |
|---|---|---|
| Administrative Calls | Currently none. | Could treat historical calls as time series. |
| Analysis Services | Currently none. | Not currently integrated into TSTool. |
| Climate Stations | See Climate Station - Evap and similar data type below. | The TSTool HydroBase database datastore uses Climate data types and TSIDs similar to USC00298284.NOAA.Precip.Day~HydroBase. Climate station data types are MixedCase. |
| Dam Safety | Currently none. | Not currently integrated into TSTool. |
| Diversion Records | See
|
The TSTool HydroBase database datastore uses TSIDs similar to:
To coding. Any water class that includes periods is enclosed in single quotes to protect within normal TSID format. |
| Groundwater Geophysical Logs | Currently none. | Not currently integrated into TSTool. |
| Groundwater Levels | See
|
The TSTool HydroBase database datastore uses TSIDs similar to 402930104414301.DWR.WellLevelDepth.Day~HydroBase and 402930104414301.DWR.WellLevelElev.Day~HydroBase.Must use point graph to see data points since sparce. |
| Parcel Use TS | Currently incomplete. | Not currently integrated into TSTool, may be implemented in StateDMI software first. |
| Reference Tables | As needed. | Used to display choices and perform checks. |
| Structures | See Diversion Records. | Structure data is processed with Diversion Records. Time series query structure data and save as time series properties. |
| Surface Water | See:
|
See the TSTool HydroBase database datastore Stream - Streamflow data types, which uses TSIDs similar to 09306395.USGS.Streamflow.Month~HydroBase. |
| Telemetry Stations | See Telemetry Station - Parameter data type below. |
The TSTool HydroBase database datastore does not provide access to real-time data because web services now provides this functionality. The telemetry station data types are UPPERCASE, which allows differentiation from climate station data types that overlap, for example EVAP and Evap. |
| Water Rights | Currently none. | Not currently integrated into TSTool. Could treat water rights as time series to allow accumulation. The ReadStateMod command reads water rights time series from model files. |
| Well Permits | Currently none. | Not currently integrated into TSTool. |
ColoradoHydroBaseRest Time Series Data Types
The following table lists the data types that have been implemented in the ColoradoHydroBaseRest datastore. Time series identifiers can be requested for valid combinations of location identifier, data type, data source, and interval.
Data indicated as "historical data" undergo review by the State in order to produce a data archive, and third party data are also ingested for some data types, to simplify access to useful data types and allow creation of HydroBase database snapshots that can be used by modelers who do not want data to change during modeling work. Month and year interval data are often based on daily time series and daily time series often use common statistic, such as mean daily flow. Information from the original data sources should be consulted to better understand data details.
Data indicated as "real-time data" include current and recent measurements and may also have a long period. Real-time data are typically processed into historical data using a process that includes review and quality control, but may also remain as "real-time" only because the data are used for real-time decisions rather than historical analysis. As these datasets grow in length, they become more useful for historical analysis.
Data from third parties may be available using other TSTool datastores. It is generally recommended to use the data from the original data provider if possible in order to access the data from its primary and authoritative source.
ColoradoHydroBaseRest Time Series Data Types
| Group | Data Type | Description |
|---|---|---|
| Climate Station (historical data) |
Evap |
Evaporation from free water surface for intervals:
Climate station data use mixed case Evap whereas telemetry station data for real-time stations use upper case EVAP. |
FrostDate |
Day of year (1-366) for first/last frost date temperature:
The day of year can be converted to a date. The WriteStateCU command outputs the time series for consumptive use modeling. |
|
MaxTemp |
Maximum temperature for intervals:
|
|
MeanTemp |
Mean temperature for intervals:
|
|
MinTemp |
Minimum temperature for intervals:
|
|
Precip |
Precipitation for intervals:
Climate station data use mixed case Precip whereas telemetry station data for real-time stations use upper case PRECIP. |
|
Snow |
Snow accumulation for intervals:
|
|
SnowDepth |
Snow depth for intervals:
|
|
SnowSWE |
Snow water equivalent (SWE) depth for intervals:
|
|
Solar |
Solar radiation rate for intervals:
Climate station data used mixed case Solar whereas telemetry station data for real-time stations use upper case SOLAR. |
|
VP |
Vapor pressure for intervals:
|
|
Wind |
Wind run (distance traveled, KM) for intervals:
|
|
| Structure (historical data) |
DivComment |
Diversion comment for intervals:
See also the Diversion Record Coding section. |
DivTotal |
Diversion total through structure for intervals:
See also the Diversion Record Coding section. |
|
RelTotal |
Release from structure for intervals:
See also the Diversion Record Coding section. |
|
Stage |
Reservoir stage (elevation) for intervals:
|
|
Volume |
Reservoir volume for intervals:
|
|
WaterClass |
Diversion records that use water class ("water color" reflecting use, type, etc.) for intervals:
See the Diversion Record Coding section. |
|
| Surface Water Station (historical data) |
Streamflow |
Streamflow (discharge) for intervals:
Avg is used for daily data in anticipation that other statistics such as Max and Min may be added for daily interval data. |
| Telemetry Station (real-time data) |
Many parameters, including
Use a data type of * in TSTool to list all available data. |
Real-time stations from stations maintained by Division of Water Resources and contributors. The data are used for real-time administration and operations. The data may be processed into historical data. Data may be collected on multiple tributaries at one station (e.g., DISCHRG1, DISCHRG2). Timestamp indicates the interval-ending statistic, such as mean discharge during the interval ending on the timestamp. Midnight value is the last value for the previous day, for interval ending at midnight. Intervals include:
Telemetry stations use upper case data type (e.g., EVAP, PRECIP, and SOLAR) whereas climate stations used mixed case data type (e.g., Evap, Precip, Solar) to ensure unique time series identifiers. |
| Well (historical data) |
WaterLevelDepth |
Water level depth for intervals:
|
WaterLevelElev |
Water level elevation for intervals:
|
Diversion Record Coding
State of Colorado diversion records that are represented in WaterClass data adhere to the
Diversion Records Standard.
Note that the HydroBase database datastore uses DivClass rather than WaterClass due to previous naming conventions.
This datastore relies on the diversion coding "SFUT2" (source, from, use, type, to) database data to provide unique identification for diversion record data.
The main time series identifier data type is WaterClass, followed by the SFUT code, for example:
0300503.DWR.WaterClass-S:6 F:0300934 U:Q T:0 G:~HydroBaseWeb
0300773.DWR.WaterClass-0300773 S:1 F: U:Q T: G:0103397 To:0302035.Month~HydroBaseWeb
0302317.DWR.'WaterClass-0302317 S:1 F:0300934.001 U:Q T:7 G:0303330 To:'.Month~HydroBaseWeb
Because TSTool uses a period to separate parts of the TSID, SFUT2 codes that include a period are surrounded by single quotes, as in the third example above. The single quotes are added automatically by TSTool when necessary.
For structure diversion records, additional zero values are added to facilitate data use, as follows:
- The diversion records for the appropriate interval are queried and set in time series.
- The diversion comments for the structure are queried.
Any irrigation year (November to October) where diversion comments are available and have
notUsedvalues ofA,B,C, orDwill cause any missing values in the time series for the year to be set to zero and the flag will be set to thenotUsedvalue. See also theFillUsingDiversionCommentscommand. - Additionally, any daily and monthly diversion records for start of year (November 1) until the end of the year (October 1) will be set to zero if missing because the State does not fill in zeros at the start of the year.
It is a known issue that monthly diversion records read from the HydroBase database and web services handle missing values and zeros differently.
Calculation of Statistics for Interval Data
This section provides background on how statistics are used to calculate interval data values. However, the details for each data type vary and the documentation provided for original data sources should be consulted.
Raw data measurements are collected using various methods, including:
- manual observations reported using "instantaneous" time such as to second, or a less precise time such as day, depending on the data type,
- "continuous monitoring" stations that measure data relatively frequently, such as every minute or 15 minutes
- "polled" stations that may be contacted less frequently, such as once a day
- values that are estimated from operations, such as no diversions in a year
The raw values may or may not be provided in web services.
For example Telemetry Station 15 minute data are considered "raw" data and for most purposes can be considered as
instantaneous or mean over 15 minutes.
Interval data are calculated from a sample taken from the original raw data or the data for a base interval. For example, the daily maximum streamflow will be different if calculated from instantaneous 1 minute values or mean 15 minute values. This distinction may or may not be important based on the use of the data.
For ColoradoHydroBaseRest web services, daily data are often used as the base interval to compute month and year interval data values. The base interval data may have been loaded from a third party.
The data processing and quality control procedures vary depending on the original data source, data type, and expected data use. Refer to the original data providers' documentation (if available) for more information about specific datasets.
Standard Time Series Properties
The general form of time series identifier used by TSTool is:
Location.DataSource.DataType.Interval~DataStoreName
LocType:Location.DataSource.DataType.Interval~DataStoreName
The standard time series identifier format for ColoradoHydroBaseRest time series is of the following form,
where HydroBaseWeb is the default datastore name for HydroBase web services:
Location.DataSource.DataType.Interval~HydroBaseWeb
Location.DataSource.DataType-Statistic.Interval~HydroBaseWeb
LocType:Location.DataSource.DataType.Interval~HydroBaseWeb
The meaning of the parts is as follows:
- The
LocationTypeis used where necessary to clarify which location identifier is used and avoid ambiguity in interpreting identifiers, for example:wellidfor internal database well identifiersabbrevfor telemetry stationsusgsfor USGS site identifiers for surface water stations, used whenabbrevis also available
- The
Locationis set to one of the following depending on the measurement location type:- the State of Colorado’s water district identifier (WDID) for structures.
- the State of Colorado’s abbreviation for telemetry stations
- the State of Colorado's numerical identifier for wells (for water level and depth)
- a third party data provider's identifier, such as USGS site identifier
- if the database contains more than one identifier, the
LocationType(see above) may be used to differentiate which identifier is used
- The
DataSourceis set to the primary data provider agency:- for example,
DWRfor diversion data - for example,
USGSfor USGS stream gages
- for example,
- The
DataTypeis set to:- The water class
divrectypefor structure time series parameterfor telemetry stationsWaterLevelDepthandWaterLevelElevfor groundwater levelsStageandVolumeforstructures/divrec/stagevolumeweb services- climate station
measType(see statistic below) - surface water station
measType(see statistic below) - Interval time series such as climate station and surface water station time series may require a statistic to
accurately describe the data and uniquely identify the time series.
For example, historical monthly streamflow can be the average value (full data type is
Streamflow-Avg, statistic isAvg), minimum value (statistic isMin), maximum value (statistic isMax), and total (statistic isTotal). Statistics also require an appropriate data unit. - Refer to the HydroBase datastore appendix
for a full list of time series data available in the HydroBase database,
which are published in web services.
In some cases, the data type used by TSTool will not exactly match HydroBase.
For example, TSTool uses
ResMeasStorageand HydroBase usesResMeasto indicate reservoir measurements, which can contain several observations. The additional “Storage” is needed in TSTool to uniquely identify the time series for the specific data type. Because web services are a newer generation of data access tools, it has been possible to improve the consistency of data types used in web services and TSTool software.
- The water class
IntervalisHour,Day,Month, orYear, as requested, and15Minfor raw telemetry data.- The
Dayinterval is used for telemetry station and climate station data; consequently, case-specific data type is needed to ensure unique time series identifiers for data types such asEvap/EVAP. - the meaning of the
Yearinterval varies:- calendar year for climate station data and as a general default
- water year (October to September) for
Streamflow, in particular when read from third party sources that use water year - irrigation year (November to October) for State of Colorado diversion records
- if necessary, monthly data can be processed into alternative
Yearintervals
- The
- The datastore name (
HydroBaseWebby default) indicates that the data are being read from the ColoradoHydroBaseRest web service.
Limitations
The following limitations of the web service may impact users of the data.
- The REST web services are enhanced as services are integrated with CDSS software tools. See the repository issues for more information.
- Data type – The goal of the datastore is to provide access to all time series that are available in HydroBase.
However, data access is limited by features of the web services and funding to integrate with TSTool and other software.
The legacy ColoradoWaterHBGuest web services and
HydroBase Datastore documentation
indicate a complete list of available data types that may ultimately be supported.
Note that creating a complete diversion time series,
in particular for structures that are not frequently measured,
may require considering several data types, including
DivTotal(total through headgate),IDivTotal(infrequent measurement, not currently implemented in this datastore), andDivComment(implemented as default). The following table lists HydroBase database data and corresponding web service data types. - Water Classes - The REST web services provide water classes using new diversion coding. The same functionality is not yet implemented in HydroBase direct database connection.
- Roundoff – a comparison of data values read directly from HydroBase and from the web service may show very slight differences when values are rounded. This is due to numbers being read and formatted during processing. The differences should not be large enough to significantly impact final results.
- Performance – time series metadata (lists of location/data type/interval combinations) are retrievable from the web service by water district, water division, and single entry. In order for TSTool to provide the user with “drill down” capability starting with a full list of available data, it is necessary to request blocks of data from the web service. However, requesting too large a block results in performance problems due to the bandwidth necessary to transmit data across the network. TSTool is designed to utilize caching to store lists of time series metadata, grouped by water district, data type, and interval. The cache can be populated based on user requests. Caching will be phased in over time as resources are made available to enhance software.
Datastore Configuration File
This datastore is automatically configured in a new TSTool installation,
has the name HydroBaseWeb and uses the installation configuration file named datastores/HydroBaseWeb.cfg.
The configuration can be overruled by adding a similarly-named configuration file
in the user's datastore files, as discussed below.
A datastore is typically configured by one or both of the following options:
- Enable ColoradoHydroBaseRest datastores in the installation
system/TSTool.cfgconfiguration file and create a datastore configuration filedatastores/HydroBaseWeb.cfgto configure the datastore. TSTool is distributed with a default file that does not define theApiKeyproperty. - Create a user datastore configuration
.tstool/NN/datastores/HydroBaseWeb.cfg, for example by copying and modifying the installation datastore configuration file. The installation configuration file can be copied and modified. Typically, theApiKeycan be specified to increase the amount of data that can be queried. If found, this configuration file will override the configuration in option 1.
Configurations are processed at software startup. An example of the TSTool configuration file is shown below.
# Configuration file for TSTool
[TSTool]
ColoradoHydroBaseRestEnabled = true
TSTool Configuration File with ColoradHydroBaseRest Datastore Properties
Properties for each datastore are specified in an accompanying configuration file described below.
The following illustrates the ColoradoHydroBaseRest datastore configuration file format
and configures a datastore named HydroBaseWeb.
This file is typically copied from installation files to .tstool/NN/datastores/HydroBaseWeb.cfg
and edited to provide user-specific API key.
The API Key can be requested from Colorado DWR web services page to increase web service query limits.
# Configuration information for "ColoradoHydroBaseRest" web service datastore.
# The user will see the following when interacting with the datastore:
#
# Enabled - True to enable and False to disable the datastore
# Type - must be ColoradoHydroBaseRestDataStore
# Name - datastore identifier used in applications, for example as the
# input type information for time series identifiers (usually a short string)
# Description - datastore description for reports and user interfaces (short phrase)
# ServiceRootURI - web service root URI, including the server name and root path
# ApiKey - used to control access limits
# ServiceApiDocumentationUri - used to provide access to web service documentation
Enabled = True
Type = "ColoradoHydroBaseRestDataStore"
Name = "HydroBaseWeb"
Description = "Colorado HydroBase REST Web Service"
ServiceRootURI = "https://dwr.state.co.us/rest/get/api/v2"
ApiKey = "thekey..."
ServiceApiDocumentationUri = "https://dwr.state.co.us/rest/get/help"
ColoradoHydroBaseRest Datastore Configuration File
See Also
FillUsingDiversionCommentscommandReadColoradoHydroBaseRestcommandReadStateModcommandReadTableFromDelimitedFilecommandReadTableFromJSONcommandWebGetcommandWriteStateCUcommand