Skip to content

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.

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.

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.EvapPan.Month~HydroBase. Climate station data types are MixedCase.
Dam Safety Currently none. Not currently integrated into TSTool.
Diversion Records See
  • Structure - DivComment
  • Structure - DivTotal
  • Structure - RelTotal
  • Structure - Stage
  • Structure - WaterClass
  • Structure - Volume
data type below.		 The TSTool HydroBase database datastore uses TSIDs similar to:
  • 0300905.DWR.DivComment.Year~HydroBase
  • 0300905.DWR.DivTotal.Month~HydroBase
  • 0300503.DWR.DivClass-S:6 F:0300934 U:Q T:0 G:.Month~HydroBase
  • 0303732.DWR.ResMeasElev.Day~HydroBase
  • 0303732.DWR.ResMeasStorage.Day~HydroBase
    • Note that web services support new water classes with account and 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
  • Well - WaterLevelDepth
  • Well - WaterLevelElev
data type below.		 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:
  • Surface Water Station - Streamflow-Avg
  • Surface Water Station - Streamflow-Max
  • Surface Water Station - Streamflow-Min
  • Surface Water Station - Streamflow-Total
and similar data types below.		 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.		 See the TSTool HydroBase database datastore Stream - Streamflow data types for real-time stations (satellite monitoring stations) using time intervals. Telemetry stations with climate data are listed under Climate. Real-time HydroBase data can only by read from HydroBase within the State of Colorado's system. 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.

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:
  • Evap.Day - daily total evaporation
  • Evap-Avg.Month - average of daily evaporation for month
  • Evap-Max.Month - maximum of daily evaporation for month
  • Evap-Min.Month - minimum of daily evaporation for month
  • Evap-Total.Month - total of daily evaporation for month

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:
  • FrostDateF28F.Year - day of year in fall when temperature is first <= 28 F
  • FrostDateF32F - day of year in fall when temperature is first <= 32 F
  • FrostDateL28S - day of year in spring for temperature is last <= 29F
  • FrostDateL32S - day of year in spring when temperature is last <= 32 F

The day of year can be converted to a date. The WriteStateCU command outputs the time series for consumptive use modeling.
MaxTemp Maximum daily temperature for intervals:
  • MaxTemp.Day - maximum daily temperature
  • MaxTemp-Avg.Month - average of daily maximum temperature for month
  • MaxTemp-Max.Month - maximum of daily maximum temperature month
  • MaxTemp-Min.Month - minimum of daily minimum temperature for month
  • MaxTemp-Total.Month - not currently provided (could be used for degree days if enabled)
MeanTemp Mean daily temperature (mean of minimum and maximum?) for intervals:
  • MeanTemp.Day - mean daily temperature
  • MeanTemp-Avg.Month - average of daily mean temperature for month
  • MeanTemp-Max.Month - maximum of daily mean temperature month
  • MeanTemp-Min.Month - average of daily mean temperature for month
  • MeanTemp-Total.Month - not currently provided (could be used for degree days if enabled)
MinTemp Minimum daily temperature for intervals:
  • MinTemp.Day - minimum daily temperature
  • MinTemp-Avg.Month - average of daily minimum temperature for month
  • MinTemp-Max.Month - maximum of daily minimum temperature month
  • MinTemp-Min.Month - minimum of daily minimum temperature for month
  • MinTemp-Total.Month - not currently provided (could be used for degree days if enabled)
Precip Precipitation for intervals:
  • Precip.Day - daily total precipitation
  • Precip-Avg.Month - average of daily precipitation for month
  • Precip-Max.Month - maximum of daily precipitation for month
  • Precip-Min.Month - minimum of daily precipitation for month
  • Precip-Total.Month - total of daily precipitation for month

Climate station data use mixed case Precip whereas telemetry station data for real-time stations use upper case PRECIP.
Snow Snow accumulation for intervals:
  • Snow.Day - daily total snow accumulation
  • Snow-Avg.Month - average of daily snow accumulation for month
  • Snow-Max.Month - maximum of daily snow accumulation for month
  • Snow-Min.Month - minimum of daily snow accumulation for month
  • Snow-Total.Month - total of daily snow accumulation for month
SnowDepth Snow depth for intervals:
  • Snow.Day - daily snow depth (mean? latest recording?)
  • Snow-Avg.Month - average of daily snow depth for month
  • Snow-Max.Month - maximum of daily snow depth for month
  • Snow-Min.Month - minimum of daily snow depth for month
  • Snow-Total.Month - total of daily snow depth for month
SnowSWE Snow water equivalent (SWE) depth for intervals:
  • SnowSWE.Day - daily SWE depth
  • SnowSWE-Avg.Month - average of daily SWE for month
  • SnowSWE-Max.Month - maximum of daily SWE for month
  • SnowSWE-Min.Month - minimum of daily SWE for month
Solar Solar radiation rate for intervals:
  • Solar.Day - daily (mean?) rate
  • Solar-Avg.Month - average of daily rate for month
  • Solar-Max.Month - maximum of daily rate for month
  • Solar-Min.Month - minimum of daily rate for month

Climate station data used mixed case Solar whereas telemetry station data for real-time stations use upper case SOLAR.
VP Vapor pressure for intervals:
  • VP.Day - daily (mean?) vapor pressure
  • VP-Avg.Month - average of daily mean vapor pressure for month
  • VP-Max.Month - maximum of daily mean vapor pressure for month
  • VP-Min.Month - minimum of daily mean vapor pressure for month
Wind Wind run (distance traveled, KM) for intervals:
  • Wind.Day - daily total wind run
  • Wind-Avg.Month - average of daily total wind run for month
  • Wind-Max.Month - maximum of daily total wind run for month
  • Wind-Min.Month - minimum of daily total wind run for month
Structure
(historical data)		 DivComment Diversion comment for intervals:
  • DivComment.Year - acres irrigated, with corresponding diversion comment flag, used when detailed diversion records are unavailable

See also the Diversion Record Coding section.
DivTotal Diversion total through structure for intervals:
  • DivTotal.Day - average daily diversion rate, cfs
  • DivTotal.Month - total monthly diversion volume, af
  • DivTotal.Year - total annual diversion volume for irrigation year (November to October), af

See also the Diversion Record Coding section.
RelTotal Release from structure for intervals:
  • RelTotal.Day - average daily release rate, cfs
  • RelTotal.Month - total monthly release, af
  • RelTotal.Year - total annual release for irrigation year (November to October), af

See also the Diversion Record Coding section.
Stage Reservoir stage (elevation) for intervals:
  • Stage.Day - daily stage (measurement at any time during day?)
Volume Reservoir volume for intervals:
  • Volume.Day - daily volume (measurement at any time during day?)
WaterClass Diversion records that use water class ("water color" reflecting use, type, etc.) for intervals:
  • WaterClass.Day - average daily water class rate, cfs
  • WaterClass.Month - total monthly water class volume, af
  • WaterClass.Year - total water class volume for irrigation year (November to October), af

See the Diversion Record Coding section.
Surface Water Station
(historical data)		 Streamflow Streamflow (discharge) for intervals:
  • Streamflow-Avg.Day - daily mean streamflow
  • Streamflow-Avg.Month - average of daily mean streamflow for month
  • Streamflow-Max.Month - maximum of daily mean streamflow for month
  • Streamflow-Min.Month - minimum of daily mean streamflow for month
  • Streamflow-Total.Month - total of daily mean streamflow as volume (af)
The statistic 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
  • DISCHRG - discharge (streamflow)
  • GAGE_HT
    • - stage (elevation)

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:
  • 15Min
  • Hour
  • Day

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:
  • WaterLevelDepth.Day - treated as a daily time series with many gaps (measurement taken at any time in the day?).
WaterLevelElev Water level elevation for intervals:
  • WaterLevelElev.Day - treated as a daily time series with many gaps (measurement taken at any time in the day?).

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:

  1. The diversion records for the appropriate interval are queried and set in time series.
  2. The diversion comments for the structure are queried. Any irrigation year (November to October) where diversion comments are available and have notUsed values of A, B, C, or D will cause any missing values in the time series for the year to be set to zero and the flag will be set to the notUsed value. See also the FillUsingDiversionComments command.
  3. 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.

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 LocationType is used where necessary to clarify which location identifier is used and avoid ambiguity in interpreting identifiers, for example:
    • wellid for internal database well identifiers
    • abbrev for telemetry stations
    • usgs for USGS site identifiers for surface water stations, used when abbrev is also available
  • The Location is 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 DataSource is set to the primary data provider agency:
    • for example, DWR for diversion data
    • for example, USGS for USGS stream gages
  • The DataType is set to:
    • The water class divrectype for structure time series
    • parameter for telemetry stations
    • WaterLevelDepth and WaterLevelElev for groundwater levels
    • Stage and Volume for structures/divrec/stagevolume web 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 is Avg), minimum value (statistic is Min), maximum value (statistic is Max), and total (statistic is Total). 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 ResMeasStorage and HydroBase uses ResMeas to 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.
  • Interval is Hour, Day, Month, or Year, as requested, and 15Min for raw telemetry data.
    • The Day interval 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 as Evap/EVAP.
    • the meaning of the Year interval 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 Year intervals
  • The datastore name (HydroBaseWeb by 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), and DivComment (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:

  1. Enable ColoradoHydroBaseRest datastores in the installation system/TSTool.cfg configuration file and create a datastore configuration file datastores/HydroBaseWeb.cfg to configure the datastore. TSTool is distributed with a default file that does not define the ApiKey property.
  2. 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, the ApiKey can 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