***Logistical Measures*** ***&*** ***Forecast Services***

***Users Guide***

***OB8.1***

***June 18, 2007***

**Table of Contents**

1.0
Introduction...................................................................................1

2.0 Graphical User
Interface....................................................................1

2.1 Starting the Forecast Services
GUI........................................................1

2.2 Main Menu
Bar...............................................................................3

> 2.2.1 File
> Option\.....................................................................................3
>
> 2.2.2 Service Types
> Options......................................................................3
>
> 2.2.2.1 Data Service
> Option.........................................................................3
>
> 2.2.2.2 Water Supply Forecast
> Option.............................................................5
>
> 2.2.2.3 Deterministic Forecast
> Option.............................................................6
>
> 2.2.2.4 Ensemble Forecast
> Option..................................................................7
>
> 2.2.3 Setup
> Option.................................................................................8
>
> 2.2.3.1 RefTables
> Option............................................................................8
>
> 2.2.4 Help
> Option..................................................................................9
>
> 2.2.4.1 About Forecast Services
> Option...........................................................9

Appendix A Data
Dictionary........................................................................\...A-1

A.1 Primary
Tables...........................................................................A-1

A.2 Lookup
Tables...........................................................................A-5

Appendix B Table
Schemas........................................................................\....B-1

Appendix C Using the Bulk_Input
Script.........................................................\...C-1

Appendix D Beta Site Notes on Initial Population of
Tables.....................................D-1

**1.0 Introduction**

The goal of Logistical Measures/Forecast Services is to standardize and
automate the collection of logistical verification measures and provide
a national database of those measures to those who manage the hydrology
program. The most basic set of logistical measures include measures of
hydrologic forecast services provided by service type, frequency and
location. These measures quantify what hydrologic services the NWS
provides; where these services are provided; and how often.

To standardize the collection of the logistical verification of forecast
services, four Forecast Services tables with associated attribute tables
were added to the IHFS database. The four Forecast Services tables are

-   Data Point Service

-   Deterministic Forecast Service

-   Ensemble Forecast Service

-   Water Supply Forecast Service

The Data Point Service table holds locations on a river/stream for which
observed data is input to RFC or WFO hydrologic forecast procedures, or
included in public hydrologic products. For the purposes of logistical
verification, all locations along a river or stream for which hydrologic
forecast and warning services are provided (i.e., forecast points)
should be included as a data service. The Deterministic Forecast Service
table holds all forecast points for which a single-value forecast is
produced. The Ensemble Forecast Service table holds all forecast points
for which ensemble forecasting is used to generate forecasts and
associated uncertainty information. The Water Supply Service table holds
all forecast points for which water supply forecasts are provided.
Attributes regarding the different services are defined in the Data
Dictionary found in Appendix A.

A Forecast Services Graphical User Interface (GUI) allows the user to
input, modify and delete data in the forecast services tables of the
IHFS database. This GUI is described in detail in this Users Guide.

**2.0 Graphical User Interface**

This section describes the options available in the GUI and the windows
which are created when the options are chosen.

**2.1** **Starting the Forecast Services GUI**

The application is started by running the script

/awips/hydroapps/whfs/bin/start_fcstservice

at the command line. The first window pops up displaying river station
information for all stations in the site's FcstPtService table (see
Figure 1).

![](media/image1.png){width="6.659722222222222in"
height="4.794444444444444in"}

Figure 1: Forecast Services Main Window

For each river station, the following information is displayed:

Location Identifier

Name

Stream Name

State

County

HSA

The Name, State, County and HSA field values are read from the Location
table. The Stream Name is read from the RiverStat table. The records
displayed in this window are selected through the ServiceTableView view
listed at the end of Appendix B. Note that the location identifiers
displayed in this window are those found in the FcstPtService table.
This table is initially populated as part of the install process.

Clicking on a column label causes the values in the column to be sorted
in alphabetical order.

At the top/middle of the window is a text box labeled "Station Search".
Typing a station identifier into this box will cause the record for that
station identifier to be highlighted.

**2.2 Main Menu Bar**

The menu bar at the top of the main window contains the following
options:

File

ServiceTypes

Setup

Help

These options are described in detail below.

**2.2.1 File Option**

Currently, the only option available is Close Application. Choosing this
option will exit the application, close all windows and return the user
to the command prompt.

**2.2.2 ServiceTypes Option**

The ServicesTypes option contains sub-options for displaying and
managing the four main services tables used for logistical verification.
The sub-option choices are

Data Service

Water Supply Forecast

Deterministic Forecast

Ensemble Forecast

**2.2.2.1 Data Service Option**

Choosing the Data Service option will popup a window displaying all
records in the FcstPtService table (Figure 2). This table is initially
populated as part of the install procedure.

![](media/image2.png){width="6.666666666666667in"
height="5.358333333333333in"}

Figure 2: Data Services Window

As with all table displays, clicking on the column header will the cause
the records to be sorted alphabetically by the column value.

Below the display of the table's records, is a section labeled "Column
Editor". Within this area, the user can type in new values for the
station identifier and/or choose values from drop-down menus for other
columns. Clicking on a record in the upper portion of the window will
cause the column values from that record to appear in the text boxes of
the "Column Editor" area. The values can then be edited. Clicking on the
"Save Record" button in the lower left corner will cause the record to
be saved into the database. Clicking on the "Clear" button will cause
the text boxes in the "Column Editor" area to be cleared.

All values in the drop-down menus in the "Column Editor" area are stored
in database tables. All of the values appearing in the drop-down menus
can be displayed by choosing the Setup/RefTables option. See Section
2.2.3 for details.

The user should be aware that if a column value of a column which is
part of the primary key is updated, it will result in a new record being
inserted into the table. The user can refer to the table schemas listed
in Appendix B to see the list of primary key columns for each table. In
the "Column Editor" area of the window, the primary key columns are
colored a different color than the other columns to help the user
distinguish between primary key and non-primary key columns.

The user should also be aware that a foreign key constraint on the lid
field exists between the FcstPtService table and the other services
tables. This means that before a record can be inserted into the
FcstPtWatSup, FcstPtESP or FcstPtDeterm tables, the lid of the record
MUST be stored in the FcstPtService table.

The user should also be aware that to insert a record in the
FcstPtService table through this window, a value for the Exceed
Probability MUST be entered or else the insert will fail. Also, if a
value is entered for the Analysis Start Date, a value MUST also be
entered for the Analysis End Date.

To delete a record from the FcstPtService database table, highlight a
record in the upper part of the window and then choose the "Delete"
option. Note that because of the foreign key constraint described above,
a record for an lid MUST be deleted from the FcstPtWatSup, FcstPtESP and
FcstPtDeterm tables BEFORE the record can be deleted from the
FcstPtService table.

The area at the very bottom of the window labeled "Database Messages"
displays messages generated each time an action such as delete, update,
insert or select is performed on the table. A scroll bar is available to
the right of this area to allow the user to scroll through the messages.
Note that each new database message is displayed at the TOP of this
area.

The window can be closed by choosing the "Close Window" option in the
lower right or by choosing the "X" in the top right corner of the
window.

**2.2.2.2 Water Supply Forecast Option**

Choosing this option will popup a window displaying all records in the
FcstPtWatSup table (Figure 3). The "Column Edit" section and other
options work the same as above.

![](media/image3.png){width="6.666666666666667in"
height="5.333333333333333in"}

Figure 3: Water Supply Points Window

**2.2.2.3 Deterministic Forecast Option**

Choosing this option will popup a window displaying all records in the
FcstPtDeterm table (Figure 4). The "Column Edit" section and other
options work in a similar manner to the other windows as described
previously.

![](media/image4.png){width="6.666666666666667in"
height="5.333333333333333in"}

Figure 4: Deterministic Point Services Window

The "Show reservoirs" option in the upper right corner, displays all
points which are reservoirs. After this option is chosen, the button
changes its name to "Show all points". Selecting this option will
display all points.

**2.2.2.4 Ensemble Forecast Option**

Choosing this option will popup a window displaying all records in the
FcstPtESP table (Figure 5). The "Column Edit" section and other options
work the same as above.

![](media/image5.png){width="6.666666666666667in"
height="5.333333333333333in"}

Figure 5: Ensemble Forecast Point Window

**2.2.3 Setup Option**

The only sub-option currently available under this option is RefTables.

**2.2.3.1 RefTables Option**

Choosing this option will bring up a window which displays all of the
values appearing in all of the drop-down menus appearing in the services
tables discussed in Section 2.2.2. Figure 6 is a screen shot of this
window.

![](media/image6.png){width="6.666666666666667in"
height="5.333333333333333in"}

Figure 6: Reference Tables Window

All values in the drop-down menus are stored in tables in the database.
The values in these tables enforce the foreign key constraints on the
column values in the main services tables. Forcing the user to choose a
value from a drop-down menu eliminates spelling problems and
upper-case/lower-case issues.

The window is closed using the "Close Window" button.

**2.2.4 Help Option**

The only sub-option here is labeled "About Forecast Services".

**2.2.4.1 About Forecast Services Option**

Clicking on this option displays an informational window with
information which includes the version number and release date of the
software.

**Appendix A Data Dictionary**

**A.1 Primary Tables**

For the primary tables, a description of the table is followed by the
column name, type and definition of each column in the table. Primary
key fields and foreign key information for these tables can be found in
Appendix B which lists the schemas for all services tables.

TABLE FcstPtService: Attributes of site\'s data points and forecast
points.

> Initially this table is populated with all unique identifiers from the
> IngestFilter table with pe = H\* or Q\* and with ts = F\*.

  ------------------ ----------- -----------------------------------------
  **Column Name**    **Type**    **Column Description**

                                 

  Lid                varchar     location id

  flood_thres        float       user defined flood threshold

  exceed_prob        int2        From a historical analysis, the
                                 probability of exceeding the user defined
                                 flood threshold. (%)

  service_type       varchar     service type (data or forecast service)

  anal_start_date    date        start date for the historical analysis of
                                 the exceedance probability for a given
                                 flood threshold

  anal_end_date      date        end date for the historical analysis of
                                 the exceedance probability for a given
                                 flood threshold

  impl_date          date        date observed data first used as input to
                                 RFC hydrologic procedures

  web_date           date        date observed hydrograph first available
                                 on web
  ------------------ ----------- -----------------------------------------

TABLE FcstPtWatSup: Attributes of site\'s statistical water supply
forecast points.

  --------------------- ---------- -----------------------------------------
  **Column Name**       **Type**   **Column Description**

                                   

  lid                   varchar    location id

  watsup_method         varchar    computational method used to produce the
                                   water supply forecast

  watsup_coord_agency   varchar    coordinating agency for the water supply
                                   forecast

  frequpd_normal        varchar    normal operations period of issuance

  period_req            varchar    period required to be forecast

  watsup_crit           varchar    water supply criteria or interest

  watsup_resp_agency    varchar    agency responsible for issuing the water
                                   supply forecast

  customer_desc         varchar    description of customers interested in
                                   the water supply forecast

  impl_date             date       date service was first produced

  web_date              date       date graphical products first available
                                   on web
  --------------------- ---------- -----------------------------------------

TABLE FcstPtDeterm: Attributes of site\'s deterministic forecast points.

+----------------+------------+---------------------------------------+
| **Column       | **Type**   | **Column Description**                |
| Name**         |            |                                       |
+----------------+------------+---------------------------------------+
|                |            |                                       |
+----------------+------------+---------------------------------------+
| lid            | varchar    | location id                           |
+----------------+------------+---------------------------------------+
| snow_method    | varchar    | snow computational method for the     |
|                |            | segment                               |
+----------------+------------+---------------------------------------+
| hydrol_method  | varchar    | hydrologic computation method for the |
|                |            | segment                               |
+----------------+------------+---------------------------------------+
| r              | varchar    | reservoir model for the segment       |
| eservoir_model |            |                                       |
+----------------+------------+---------------------------------------+
| upstream_seg   | varchar    | location id of upstream point may be  |
|                |            | forecast or data point                |
|                |            |                                       |
|                |            | if point is a headwater point, then   |
|                |            | this value is set to 'xxxxx'.         |
+----------------+------------+---------------------------------------+
| hydraul_method | varchar    | routing computational method for the  |
|                |            | segment                               |
+----------------+------------+---------------------------------------+
| def_issue_crit | varchar    | minimum issuance criteria for the     |
|                |            | forecast service                      |
+----------------+------------+---------------------------------------+
| hours_qpf      | int2       | hours of QPF used in streamflow       |
|                |            | forecast                              |
+----------------+------------+---------------------------------------+
| frequpd_normal | varchar    | normal operations update frequency    |
+----------------+------------+---------------------------------------+
| frequpd_flood  | varchar    | flood operations update frequency     |
+----------------+------------+---------------------------------------+
| f              | varchar    | drought operations update frequency   |
| requpd_drought |            |                                       |
+----------------+------------+---------------------------------------+
| fcst_horizon   | varchar    | forecast horizon                      |
+----------------+------------+---------------------------------------+
| hours_qtf      | int2       | hours of temperature forecast used in |
|                |            | streamflow forecast                   |
+----------------+------------+---------------------------------------+
| hours_qzf      | int2       | hours of freezing level forecast used |
|                |            | in streamflow forecast                |
+----------------+------------+---------------------------------------+
| num_elev_zones | int2       | number of elevation zones in          |
|                |            | mountainous area                      |
|                |            |                                       |
|                |            | may be 1,2 or 3                       |
|                |            |                                       |
|                |            | else should be set to 1               |
+----------------+------------+---------------------------------------+
| c              | Varchar    | indicates consumptive use model is    |
| onsumptive_use |            | used for the segment (Y or N)         |
+----------------+------------+---------------------------------------+
| channel_loss   | varchar    | indicates channel loss model is used  |
|                |            | for the segment (Y or N)              |
+----------------+------------+---------------------------------------+
| f              | varchar    | forecast generation method            |
| cst_gen_method |            |                                       |
+----------------+------------+---------------------------------------+
| impl_date      | date       | date service was first produce        |
+----------------+------------+---------------------------------------+
| web_date       | date       | date official forecast hydrograph     |
|                |            | first available on AHPS website       |
+----------------+------------+---------------------------------------+

TABLE FcstPtESP: Attributes of site\'s Ensemble Streamflow Prediction
(ESP) points.

+----------------+-------------+--------------------------------------+
| **Column       | **Type**    | **Column Description**               |
| Name**         |             |                                      |
+----------------+-------------+--------------------------------------+
|                |             |                                      |
+----------------+-------------+--------------------------------------+
| lid            | varchar     | location id                          |
+----------------+-------------+--------------------------------------+
| snow_method    | varchar     | snow computation method for the      |
|                |             | segment                              |
+----------------+-------------+--------------------------------------+
| hydrol_method  | varchar     | hydrologic computation method for    |
|                |             | the segment                          |
+----------------+-------------+--------------------------------------+
| r              | varchar     | reservoir model for the segment      |
| eservoir_model |             |                                      |
+----------------+-------------+--------------------------------------+
| upstream_seg   | varchar     | location id of upstream point        |
|                |             |                                      |
|                |             | may be forecast or data point        |
|                |             |                                      |
|                |             | if point is a headwater point, then  |
|                |             | this value is set to 'xxxxx'.        |
+----------------+-------------+--------------------------------------+
| hydraul_method | varchar     | routing computation method for the   |
|                |             | segment                              |
+----------------+-------------+--------------------------------------+
| flowtype       | varchar     | flow type                            |
+----------------+-------------+--------------------------------------+
| fcsttype       | varchar     | forecast time horizon type           |
+----------------+-------------+--------------------------------------+
| frequpd_normal | varchar     | normal operations period of issuance |
+----------------+-------------+--------------------------------------+
| frequpd_flood  | varchar     | flood operations period of issuance  |
+----------------+-------------+--------------------------------------+
| f              | varchar     | drought operations period of         |
| requpd_drought |             | issuance                             |
+----------------+-------------+--------------------------------------+
| fcst_horizon   | varchar     | forecast horizon                     |
+----------------+-------------+--------------------------------------+
| nummonclim     | int2        | number of months climotological      |
|                |             | forecasts are used in streamflow     |
|                |             | forecast                             |
|                |             |                                      |
|                |             | 0 -- N/A                             |
+----------------+-------------+--------------------------------------+
| numdayhyd      | int2        | number of days hydrometeorological   |
|                |             | forecasts are used in streamflow     |
|                |             | forecast                             |
|                |             |                                      |
|                |             | 0 -- N/A                             |
+----------------+-------------+--------------------------------------+
| num_elev_zones | int2        | number of elevation zones in         |
|                |             | mountainous area                     |
|                |             |                                      |
|                |             | may be 1,2 or 3                      |
|                |             |                                      |
|                |             | else should be set to 1              |
+----------------+-------------+--------------------------------------+
| c              | varchar     | indicates consumptive use model is   |
| onsumptive_use |             | used for the segment (Y or N)        |
+----------------+-------------+--------------------------------------+
| channel_loss   | varchar     | indicates channel loss model is used |
|                |             | for the segment (Y or N)             |
+----------------+-------------+--------------------------------------+
| post_processor | varchar     | post processing model used to create |
|                |             | the forecast                         |
+----------------+-------------+--------------------------------------+
| impl_date      | date        | date service was first produced      |
+----------------+-------------+--------------------------------------+
| external_date  | date        | date product first available the     |
|                |             | external customers                   |
+----------------+-------------+--------------------------------------+
| web_date       | date        | date graphical products first        |
|                |             | available on web                     |
+----------------+-------------+--------------------------------------+

**A.2 Lookup tables for defining foreign keys constraints**

For the lookup tables, a description of the table is followed by the
column name, type and a list of allowable values.

TABLE FcstType: Look up table for forecast time horizon type

fcsttype varchar

Allowable values:

'Short-Term' Two weeks or fewer forecast time horizon

'Long-Term' Monthly or seasonal forecast time horizon

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--

TABLE ServiceType: Look up table for service types (data or forecast
service)

service_type varchar

Allowable values:

'Data' A location on a river/stream for which observed data is input to
RFC or WFO hydrologic forecast procedures, or included in public
hydrologic products. Flood forecasts and warnings are not issued for
data points.

'Forecast' A location along a river/stream for which hydrologic forecast
and warning services are provided by a WFO.

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--

TABLE HydrologicMethod: Look up table for hydrologic computation method
for the segment

hydrol_method varchar

Allowable values:

'SiteSpecific-API' Site Specific Antecedent Precipitation Index model

'SiteSpecific-SAC-SMA' Site Specific Sacramento Soil Moisture Accounting

'NWSRFS--API-CONT' NWSRFS Continuous Antecedent Precipitation Index

'NWSRFS-API-SLC' NWSRFS Salt Lake City Antecedent Precipitation Index

'NWSRFS--API-CIN' NWSRFS Cincinnati Antecedent Precipitation Index

'NWSRFS-API-HAR' NWSRFS Harrisburg Antecedent Precipitation Index

'NWSRFS--API-HAR2' NWSRFS Modified Harrisburg Antecedent Precipitation
Index

'NWSRFS-API-HFD' NWSRFS Hartford Antecedent Precipitation Index

'NWSRFS--API-MKC' NWSRFS Kansas City Antecedent Precipitation Index

'NWSRFS-SS-SAC' NWSRFS State Space Sacramento Model

'NWSRFS-ASSIM' NWSRFS Assimilator Operation

'NWSRFS-SWB-NILE' NWSRFS Simple Water Balance Model

'NWSRFS--SAC-SMA' NWSRFS Sacramento Soil Moisture Accounting Model

'NWSRFS-XIN-SMA' NWSRFS Xinanjiang Soil Moisture Accounting

'NWSRFS-DistributedModel' NWSRFS Distributed Model

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--

TABLE RoutingMethod: Look up table for routing computation method for
the current segment

hydraul_method varchar(COMP_METH_LEN)

Allowable values:

'None' No routing used, headwater basin

'TATUM' Tatum coefficient routing

'LAG/K' Lag and K routing

'FLDWAV' Flood Wave Routing Operation

'DWOPER' Dynamic Wave Routing Operation

'LAY-COEF' Layered Coefficient Routing

'MUSKROUT' Muskingum Routing

'SARROUTE' SSARR Channel Routing

'GLACIER' Glacier routing operation

'LOOKUP' 2 variable table lookup operation

'LOOKUP3' 3 variable table lookup operation

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--

TABLE FlowType: Look up table for flow types

flowtype varchar

Allowable values:

'FullNatural' Forecasts of full natural flow at forecast point (no
upstream regulation)

'FullRegulated' Forecasts of full regulated flow at forecast point with
modeled regulation

'LocalFlow' Forecasts of local flow only

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--

TABLE DefiningIssueCriteria: Look up table for minimum issuance criteria
for the forecast service

def_issue_crit varchar

Allowable values:

'DailyDuringFcstIssueStage' Forecasts are issued at least daily, when
the forecast issuance stage has been met

'DuringDrought' Forecasts are only issued for this point during drought

'DailyMandatorySeasonal' Forecasts are issued at least daily, during
specific seasons

'MandatorySpringSnowmeltOutlook' Forecast of spring snowmelt outlook is
mandatory

'OptionalSpringSnowmeltOutlook' Forecast of spring snowmelt outlook is
optional

'CrestFcst' Only crest forecasts are issued

'DailyMandatory' Forecasts are issued at least daily

'AsRequested' Forecasts are issued as requested

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--

TABLE WatSupMethod: Lookup table for the computational method used to
produce the water supply forecast

watsup_method varchar

Allowable values:

'SWS-PrincipleComponents(Regression)' Statistical Water Supply Principle
Components

'APrioriVariables' A Priori Variable water supply method

'ESP' Ensemble Streamflow Prediction

'SingleTraceProcedure' Single Trace Procedure

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--

TABLE WatSupCoordAgency: Lookup table for coordinating agency for water
supply forecasts

watsup_coord_agency varchar(AGENCY_OFFICE_NAME_LEN)

Allowable values:

'SALT' Salt River Project

'NRCS' Natural Resources Conservation Services

'NRCS/AlbertaEnvironment' Natural Resources Conservation Services and
Alberta Environemt

'COE' U.S. Army Corps of Engineers

'StateWaterResourcesAgency' State Water Resources Agency

'None' No coordinating agency

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--

TABLE WatSupRespAgency: Lookup table for agency responsible for issuing
the water supply forecast

watsup_resp_agency varchar(AGENCY_OFFICE_NAME_LEN)

Allowable values:

'RFC' River Forecast Center

'WFO' Weather Forecast Office

'NRCS' Natural Resources Conservation Services

'RFC & NRCS' River Forecast Center and Natural Resources Conservation
Services

'RFC & StateWaterResourcesAgency' River Forecast Center and State Water
Resources Agency

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--

TABLE WatSupCriterion: Lookup table for criterion or interest for water
supply forecasts

watsup_criterion varchar

Allowable values:

'HighFlow' High flow forecast

'LowFlow' Low flow forecast

'PeakStage' Peak stage forecast

'CriticalThreshold' Forecast for a critical threshold (stage, flow or
volume)

'Volume' Volume forecast

'Other' Other forecast criterion or interest

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--

TABLE SnowMethod: Lookup table for snow computation method for the
segment

snow_method varchar

Allowable values:

'SNOW-17' NWSRFS Hydro-17 snow model

'SNOW-43' NWSRFS NWS-43 snow model

'None' Snow models not used for current segment

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--

TABLE FrequencyUpdate: Lookup table for how frequent forecasts are
typically updated

frequency_update varchar

Allowable values:

'1hour' every hour

'2hour' every two hours

'3hour' every three hours

'4hour' every four hours

'5hour' every five hours

'6hour' every six hours

'12hour' every 12 hours

'24hour' every 24 hours

'EveryOtherDay' every other day

'Weekly' Weekly

'BiWeekly' every other week

'BiMonthly' twice a month

'Monthly' once a month

'EventDriven' event driven

'Other' other

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--

TABLE RequiredPeriod: Lookup table for the typical forecast horizon for
water supply forecasts

period_req varchar

Allowable values:

'Jan-Mar' January through March

'Apr-Sep' April through September

'Mar-Sep' March through September

'Apr-Jul' April through July

'Apr-Aug' April through August

'May-Sep' May through September

'Apr-peak' April through peak

'Spring' Spring (March through May)

'Fall' Fall (September through November)

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--

TABLE PostProcessor: Lookup table for post processing model used to
create the forecast

post_processor varchar

Allowable values:

'ER1' NWSRFS Error Model 1

'ER2D' NWSRFS Error Model 2D

'ER2S' NWSRFS Error Model 2S

'None' Post processing not used

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--

TableName FcstHorizon: Lookup table for forecast horizon values

fcst_horizon varchar

Allowable values:

'1 day' 1 day forecast

'2 days' 2 day forecast

'3 days' 3 day forecast

'4 days' 4 day forecast

'5 days' 5 day forecast

'6 days' 6 day forecast

'1 week' 1 week forecast

'2 weeks' 2 week forecast

'1 month' 1 month forecast

'2 months' 2 month forecast

'3 months' 3 month forecast

'6 months' 6 month forecast

'1 year' 1 year forecast

'Other' other

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--

TableName: FcstGenMethod: Lookup table for how forecast was generated

fcst_gen_method varchar

Allowable values:

'IFP' Interactive Forecast Program

'Batch/CRON' Batch mode and on a CRON

'Other' Other

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--

Tablename: ReservoirModel: Lookup table for reservoir model for the
segment

reservoir_model varchar

Allowable values:

'SSSAR' Streamflow Simulation and Reservoir Regulation System (SSSAR)
Reservoir Regulation Method

'RES-SINGL' Single Reservoir Regulation Operation

'RES-J' Joint Reservoir Regulation Operation

'FLDWAV' Generalized Flood Wave Routing Operation

'None' No reservoir model in current segment

**Appendix B Table Schemas**

CREATE TABLE FcstPtService

(

lid varchar(LOC_ID_LEN) not null,

flood_thres float,

exceed_prob int2,

service_type varchar(SERVICE_TYPE_LEN),

anal_start_date date,

anal_end_date date,

impl_date date,

web_date date

)

;

ALTER TABLE FcstPtService

ADD CONSTRAINT fcstptservice_pk

PRIMARY KEY (lid);

ALTER TABLE FcstPtService

ADD CONSTRAINT fcstptser_riverst_fk

FOREIGN KEY (lid)

REFERENCES RiverStat (lid)

MATCH FULL ;

ALTER TABLE FcstPtService

ADD CONSTRAINT fcstptser_servtype_fk

FOREIGN KEY (service_type)

REFERENCES ServiceType (service_type)

MATCH FULL ;

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--

CREATE TABLE FcstPtWatSup

(

lid varchar(LOC_ID_LEN) not null,

watsup_method varchar(WATSUP_METH_LEN) not null,

watsup_coord_agency varchar(AGENCY_OFFICE_NAME_LEN)not null,

frequpd_normal varchar(FREQ_UPDATE_LEN) not null,

period_req varchar(REQ_PERIOD_LEN) not null,

watsup_crit varchar(WATSUP_CRIT_LEN) not null,

watsup_resp_agency varchar(AGENCY_OFFICE_NAME_LEN),

customer_desc varchar(CUSTOMER_LEN),

impl_date date,

web_date date

)

;

ALTER TABLE FcstPtWatSup

ADD CONSTRAINT fcstptwatsup_pk

PRIMARY KEY
(lid,watsup_method,watsup_coord_agency,frequpd_normal,period_req,watsup_crit);

ALTER TABLE FcstPtWatSup

ADD CONSTRAINT fpwatsup_fpserv_fk

FOREIGN KEY (lid)

REFERENCES FcstPtService (lid)

MATCH FULL ;

ALTER TABLE FcstPtWatSup

ADD CONSTRAINT fpwatsup_meth_fk

FOREIGN KEY (watsup_method)

REFERENCES WatSupMethod(watsup_method)

MATCH FULL;

ALTER TABLE FcstPtWatSup

ADD CONSTRAINT fpwatsup_agency_fk

FOREIGN KEY (watsup_coord_agency)

REFERENCES WatSupCoordAgency(watsup_coord_agency)

MATCH FULL;

ALTER TABLE FcstPtWatSup

ADD CONSTRAINT fpwatsup_resp_fk

FOREIGN KEY (watsup_resp_agency)

REFERENCES WatSupRespAgency(watsup_resp_agency)

MATCH FULL;

ALTER TABLE FcstPtWatSup

ADD CONSTRAINT fpwatsup_frequpd_fk

FOREIGN KEY (frequpd_normal)

REFERENCES FrequencyUpdate(frequency_update)

MATCH FULL;

ALTER TABLE FcstPtWatSup

ADD CONSTRAINT fpwatsup_periodreq_fk

FOREIGN KEY (period_req)

REFERENCES RequiredPeriod(period_req)

MATCH FULL;

ALTER TABLE FcstPtWatSup

ADD CONSTRAINT fpwatsup_crit_fk

FOREIGN KEY (watsup_crit)

REFERENCES WatSupCriterion(watsup_criterion)

MATCH FULL;

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--

CREATE TABLE FcstPtDeterm

(

lid varchar(LOC_ID_LEN) not null,

snow_method varchar(SNOW_METH_LEN) not null,

hydrol_method varchar(COMP_METH_LEN) not null,

reservoir_model varchar(RES_MODEL_LEN) not null,

upstream_seg varchar(LOC_ID_LEN) not null,

hydraul_method varchar(COMP_METH_LEN) not null,

def_issue_crit varchar(ISSUE_CRIT_LEN) not null,

hours_qpf int2 not null,

frequpd_normal varchar(FREQ_UPDATE_LEN),

frequpd_flood varchar(FREQ_UPDATE_LEN),

frequpd_drought varchar(FREQ_UPDATE_LEN),

fcst_horizon varchar(FCST_HORIZON_LEN),

hours_qtf int2,

hours_qzf int2,

num_elev_zones int2,

consumptive_use varchar(CODE_LEN),

channel_loss varchar(CODE_LEN),

fcst_gen_method varchar(FCST_METHOD_LEN),

impl_date date,

web_date date

)

;

ALTER TABLE FcstPtDeterm

ADD CONSTRAINT fcstptdeterm_pk

PRIMARY KEY
(lid,snow_method,hydrol_method,reservoir_model,upstream_seg,hydraul_method,
def_issue_crit,hours_qpf);

ALTER TABLE FcstPtDeterm

ADD CONSTRAINT fpdeterm_fpserv_fk

FOREIGN KEY (lid)

REFERENCES FcstPtService (lid)

MATCH FULL ;

ALTER TABLE FcstPtDeterm

ADD CONSTRAINT fpdeterm_upseg_fk

FOREIGN KEY (upstream_seg)

REFERENCES Location (lid)

MATCH FULL ;

ALTER TABLE FcstPtDeterm

ADD CONSTRAINT fpdeterm_hydrolcomp_fk

FOREIGN KEY (hydrol_method)

REFERENCES HydrologicMethod(hydrol_method)

MATCH FULL;

ALTER TABLE FcstPtDeterm

ADD CONSTRAINT fpdeterm_routingcomp_fk

FOREIGN KEY (hydraul_method)

REFERENCES RoutingMethod(hydraul_method)

MATCH FULL;

ALTER TABLE FcstPtDeterm

ADD CONSTRAINT fpdeterm_snowmeth_fk

FOREIGN KEY (snow_method)

REFERENCES SnowMethod(snow_method)

MATCH FULL;

ALTER TABLE FcstPtDeterm

ADD CONSTRAINT fpdeterm_issuecrit_fk

FOREIGN KEY (def_issue_crit)

REFERENCES DefiningIssueCriteria(def_issue_crit)

MATCH FULL;

ALTER TABLE FcstPtDeterm

ADD CONSTRAINT fpdeterm_resmodel_fk

FOREIGN KEY (reservoir_model)

REFERENCES ReservoirModel(reservoir_model)

MATCH FULL;

ALTER TABLE FcstPtDeterm

ADD CONSTRAINT fpdeterm_fcstmethod_fk

FOREIGN KEY (fcst_gen_method)

REFERENCES FcstGenMethod(fcst_gen_method)

MATCH FULL;

ALTER TABLE FcstPtDeterm

ADD CONSTRAINT fpdeterm_frupnorm_fk

FOREIGN KEY (frequpd_normal)

REFERENCES FrequencyUpdate(frequency_update)

MATCH FULL;

ALTER TABLE FcstPtDeterm

ADD CONSTRAINT fpdeterm_frupflood_fk

FOREIGN KEY (frequpd_flood)

REFERENCES FrequencyUpdate(frequency_update)

MATCH FULL;

ALTER TABLE FcstPtDeterm

ADD CONSTRAINT fpdeterm_frupdrght_fk

FOREIGN KEY (frequpd_drought)

REFERENCES FrequencyUpdate(frequency_update)

MATCH FULL;

ALTER TABLE FcstPtDeterm

ADD CONSTRAINT fpdeterm_fcsthoriz_fk

FOREIGN KEY (fcst_horizon)

REFERENCES FcstHorizon(fcst_horizon)

MATCH FULL;

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--

CREATE TABLE FcstPtESP

(

lid varchar(LOC_ID_LEN) not null,

snow_method varchar(SNOW_METH_LEN) not null,

hydrol_method varchar(COMP_METH_LEN) not null,

reservoir_model varchar(RES_MODEL_LEN) not null,

upstream_seg varchar(LOC_ID_LEN) not null,

hydraul_method varchar(COMP_METH_LEN) not null,

flowtype varchar(FLOW_TYPE_LEN) not null,

fcsttype varchar(ESP_FCST_LEN) not null,

frequpd_normal varchar(FREQ_UPDATE_LEN),

frequpd_flood varchar(FREQ_UPDATE_LEN),

frequpd_drought varchar(FREQ_UPDATE_LEN),

fcst_horizon varchar(FCST_HORIZON_LEN),

nummonclim int2,

numdayhyd int2,

num_elev_zones int2,

consumptive_use varchar(CODE_LEN),

channel_loss varchar(CODE_LEN),

post_processor varchar(PROCESSOR_LEN),

impl_date date,

external_date date,

web_date date

)

;

ALTER TABLE FcstPtESP

ADD CONSTRAINT fcstptesp_pk

PRIMARY KEY
(lid,snow_method,hydrol_method,reservoir_model,upstream_seg,hydraul_method,

flowtype,fcsttype);

ALTER TABLE FcstPtESP

ADD CONSTRAINT fpesp_fpserv_fk

FOREIGN KEY (lid)

REFERENCES FcstPtService (lid)

MATCH FULL ;

ALTER TABLE FcstPtESP

ADD CONSTRAINT fpesp_upseg_fk

FOREIGN KEY (upstream_seg)

REFERENCES Location (lid)

MATCH FULL ;

ALTER TABLE FcstPtESP

ADD CONSTRAINT fpesp_hydrolcomp_fk

FOREIGN KEY (hydrol_method)

REFERENCES HydrologicMethod(hydrol_method)

MATCH FULL;

ALTER TABLE FcstPtESP

ADD CONSTRAINT fpesp_routingcomp_fk

FOREIGN KEY (hydraul_method)

REFERENCES RoutingMethod(hydraul_method)

MATCH FULL;

ALTER TABLE FcstPtESP

ADD CONSTRAINT fpesp_snowmeth_fk

FOREIGN KEY (snow_method)

REFERENCES SnowMethod(snow_method)

MATCH FULL;

ALTER TABLE FcstPtESP

ADD CONSTRAINT fpesp_flowtype_fk

FOREIGN KEY (flowtype)

REFERENCES FlowType(flowtype)

MATCH FULL;

ALTER TABLE FcstPtESP

ADD CONSTRAINT fpesp_fcsttype_fk

FOREIGN KEY (fcsttype)

REFERENCES FcstType(fcsttype)

MATCH FULL;

ALTER TABLE FcstPtESP

ADD CONSTRAINT fpesp_resmodel_fk

FOREIGN KEY (reservoir_model)

REFERENCES ReservoirModel(reservoir_model)

MATCH FULL;

ALTER TABLE FcstPtESP

ADD CONSTRAINT fpesp_frupnorm_fk

FOREIGN KEY (frequpd_normal)

REFERENCES FrequencyUpdate(frequency_update)

MATCH FULL;

ALTER TABLE FcstPtESP

ADD CONSTRAINT fpesp_frupflood_fk

FOREIGN KEY (frequpd_flood)

REFERENCES FrequencyUpdate(frequency_update)

MATCH FULL;

ALTER TABLE FcstPtESP

ADD CONSTRAINT fpesp_frupdrght_fk

FOREIGN KEY (frequpd_drought)

REFERENCES FrequencyUpdate(frequency_update)

MATCH FULL;

ALTER TABLE FcstPtESP

ADD CONSTRAINT fpesp_postproc_fk

FOREIGN KEY (post_processor)

REFERENCES PostProcessor(post_processor)

MATCH FULL;

ALTER TABLE FcstPtESP

ADD CONSTRAINT fpesp_fcsthoriz_fk

FOREIGN KEY (fcst_horizon)

REFERENCES FcstHorizon(fcst_horizon)

MATCH FULL;

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--

Following tables are lookup tables for defining foreign keys
constraints:

CREATE TABLE FcstType

(

fcsttype varchar(FCST_TYPE_LEN)

)

;

ALTER TABLE FcstType

ADD CONSTRAINT fcsttype_pk

PRIMARY KEY (fcsttype);

GRANT select ON FcstType to public;

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--

CREATE TABLE ServiceType

(

service_type varchar(SERVICE_TYPE_LEN)

)

;

ALTER TABLE ServiceType

ADD CONSTRAINT servtype_pk

PRIMARY KEY (service_type);

GRANT select ON ServiceType to public;

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--

CREATE TABLE HydrologicMethod

(

hydrol_method varchar(COMP_METH_LEN)

)

;

ALTER TABLE HydrologicMethod

ADD CONSTRAINT hydrologicmethod_pk

PRIMARY KEY (hydrol_method);

GRANT select ON HydrologicMethod to public;

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--

CREATE TABLE RoutingMethod

(

hydraul_method varchar(COMP_METH_LEN)

)

;

ALTER TABLE RoutingMethod

ADD CONSTRAINT routingmethod_pk

PRIMARY KEY (hydraul_method);

GRANT select ON RoutingMethod to public;

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--

CREATE TABLE FlowType

(

flowtype varchar(FLOW_TYPE_LEN)

)

;

ALTER TABLE FlowType

ADD CONSTRAINT flowtype_pk

PRIMARY KEY (flowtype);

GRANT select ON FlowType to public;

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--

CREATE TABLE DefiningIssueCriteria

(

def_issue_crit varchar(ISSUE_CRIT_LEN)

)

;

ALTER TABLE DefiningIssueCriteria

ADD CONSTRAINT def_issuecrit_pk

PRIMARY KEY (def_issue_crit);

GRANT select ON DefiningIssueCriteria to public;

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--

CREATE TABLE WatSupMethod

(

watsup_method varchar(WATSUP_METH_LEN)

)

;

ALTER TABLE WatSupMethod

ADD CONSTRAINT watsup_method_pk

PRIMARY KEY (watsup_method);

GRANT select ON WatSupMethod to public;

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--

CREATE TABLE WatSupCoordAgency

(

watsup_coord_agency varchar(AGENCY_OFFICE_NAME_LEN)

)

;

ALTER TABLE WatSupCoordAgency

ADD CONSTRAINT watsup_agency_pk

PRIMARY KEY (watsup_coord_agency);

GRANT select ON WatSupCoordAgency to public;

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--

CREATE TABLE WatSupRespAgency

(

watsup_resp_agency varchar(AGENCY_OFFICE_NAME_LEN)

)

;

ALTER TABLE WatSupRespAgency

ADD CONSTRAINT watsup_resp_pk

PRIMARY KEY (watsup_resp_agency);

GRANT select ON WatSupRespAgency to public;

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--

CREATE TABLE WatSupCriterion

(

watsup_criterion varchar(WATSUP_CRIT_LEN)

)

;

ALTER TABLE WatSupCriterion

ADD CONSTRAINT watsup_crit_pk

PRIMARY KEY (watsup_criterion);

GRANT select ON WatSupCriterion to public;

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--

CREATE TABLE SnowMethod

(

snow_method varchar(SNOW_METH_LEN)

)

;

ALTER TABLE SnowMethod

ADD CONSTRAINT snowmethod_pk

PRIMARY KEY (snow_method);

GRANT select ON SnowMethod to public;

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--

CREATE TABLE FrequencyUpdate

(

frequency_update varchar(FREQ_UPDATE_LEN)

)

;

ALTER TABLE FrequencyUpdate

ADD CONSTRAINT freq_upd_pk

PRIMARY KEY (frequency_update);

GRANT select ON FrequencyUpdate to public;

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--

CREATE TABLE RequiredPeriod

(

period_req varchar(REQ_PERIOD_LEN)

)

;

ALTER TABLE RequiredPeriod

ADD CONSTRAINT req_period_pk

PRIMARY KEY (period_req);

GRANT select ON RequiredPeriod to public;

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--

CREATE TABLE PostProcessor

(

post_processor varchar(PROCESSOR_LEN)

)

;

ALTER TABLE PostProcessor

ADD CONSTRAINT post_processor_pk

PRIMARY KEY (post_processor);

GRANT select ON PostProcessor to public;

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--

CREATE TABLE FcstHorizon

(

fcst_horizon varchar(FCST_HORIZON_LEN)

)

;

ALTER TABLE FcstHorizon

ADD CONSTRAINT fcst_horizon_pk

PRIMARY KEY (fcst_horizon);

GRANT select ON FcstHorizon to public;

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--

CREATE TABLE FcstGenMethod

(

fcst_gen_method varchar(FCST_METHOD_LEN)

)

;

ALTER TABLE FcstGenMethod

ADD CONSTRAINT fcst_method_pk

PRIMARY KEY (fcst_gen_method);

GRANT select ON FcstGenMethod to public;

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--

CREATE TABLE ReservoirModel

(

reservoir_model varchar(RES_MODEL_LEN)

)

;

ALTER TABLE ReservoirModel

ADD CONSTRAINT res_model_pk

PRIMARY KEY (reservoir_model);

GRANT select ON ReservoirModel to public;

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--

Following view generates the initial GUI display:

CREATE VIEW ServiceTableView AS

SELECT f.lid, l.name, r.stream, l.state, l.county, l.hsa

FROM fcstptservice f LEFT OUTER JOIN Location l

ON f.lid = l.lid,

RiverStat r

WHERE f.lid = r.lid

;

GRANT SELECT ON ServiceTableView TO PUBLIC;

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--

Typedefs to define sizes of varchar columns

#define COMP_METH_LEN 30

#define CUSTOMER_LEN 80

#define ESP_FCST_LEN 20

#define FCST_HORIZON_LEN 30

#define FCST_METHOD_LEN 30

#define FLOW_TYPE_LEN 40

#define FREQ_UPDATE_LEN 30

#define ISSUE_CRIT_LEN 50

#define PROCESSOR_LEN 30

#define REQ_PERIOD_LEN 30

#define RES_MODEL_LEN 30

#define SERVICE_TYPE_LEN 20

#define SNOW_METH_LEN 30

#define WATSUP_CRIT_LEN 30

#define WATSUP_METH_LEN 50

**Appendix C -- Using the Bulk_Input Script**

The Bulk_Input script has been provided as a method for doing bulk
inserts of records into the

-   FcstPtDeterm

-   FcstPtESP

-   FcstPtWatSup

tables in the database. The Bulk_Input script calls another script
(populate_main_tables.sql) which executes three "\\copy" commands from
within psql to copy records from ascii pipe-delimited files into the
tables. Templates for the pipe-delimited files for the three tables are
also provided.

Before executing the Bulk_Input script, the user must edit the template
files providing a valid data value for each column. The table schemas
and Data Dictionary appearing in the previous Appendices give the user
enough information about the individual columns to fill them with
appropriate data values. All three template files must be edited before
executing the script. Once the template files are set, the user can run
the Bulk_Input script from the command line as

./Bulk_Input db_name

where db_name is the database name containing the forecast services
tables. Once this is done, the records in the tables can be displayed
through the GUI and edited further if necessary.

One possible scenario for inserting records is to use the Bulk_Input
script to insert large numbers of records and then to use the GUI to
quickly edit the records. This scenario was used successfully by Julie
Meyer at MBRFC to initially populate the tables.

**Appendix D: Beta Site Notes on Initial Population of Tables**

The following notes describe work that was needed to initially populate
the forecast services tables. These notes are from Julie Meyer (MBRFC).

**D.1 Adding missing stations to the FcstPtService table**

Site specific only points can be added using the following SQL:

insert into fcstptservice

select lid from sshpconfig

where lid not (select lid from fcstptservice)

and lid in (select lid from riverstat);

Created lid lists using information from other sources. Created a simple
awk script which takes these lists and creates load files for the
fcstptwatsup table. Created one file for SWS method points and another
file for ESP method points. I discussed the water supply table with our
in-house expert and came up with a best guess at implementation and web
dates. Will populate other fields after inserting the records.

For site specific points, created a list of lids from the sshpconfig
table. Then used this list and an awk command to create the load file.
Have a few discrepancies to resolve.

For reservoir inflow points, created lid list and used awk command to
create the initial load files, one for res-j sites and one for res-sngl.
Decided which res ops by which fcst groups we do ahps and which we do
not.

Remainder of the missing stations are most likely water supply only
forecast points. Information for these stations can be found in the
water supply database.

**D.2 Check count of stations by HSA**

Used information from an ascii text file dump of the OFS binary
bfratings info created locally at MBRFC. Output includes a count of fcst
pts by hsa. Ran the following query to generate a similar count by hsa
list

select count(\*),hsa from servicetableview

group by hsa

order by hsa;

Noted which hsa had count discrepancies, and then by ordering the view
in the GUI by hsa, was able to compare lists. Added or deleted lids from
fcstptservice table as needed.

In some cases, stations which were added resulted in foreign key
violations. In these cases, used HydroBase to add an entry into the
Riverstat table. In other cases, the fcst pt had the incorrect HSA
defined in the Location table.

**D.3 Clean up station information**

Several of the points in the view did not have river names. Used
Hydrobase to add the names into the Riverstat table.

Did some clean up of station names. Only did this for stations where the
name displayed in the view was different from the name in the fcstptlist
output file.

**D.4 Use IFP to check reservoirs**

Checked to see how long it would take to use IFP to check each reservoir
and make changes needed because there is an upstream segment and/or
consumptive use/channel loss for each reservoir.