Keywords

Introduction

Streamflow measurements are the most important basic data of hydrologic studies required for the design and construction of hydraulic structures, flood management and flood forecasting. Accurate streamflow measurement is of paramount importance for economic and safe design of water resource project. Streamflow measurement techniques are broadly classified as direct determination of stream discharge and indirect determination of streamflow. For direct determination of discharge methods depending on the site conditions either of area velocity method, dilution method (chemical method), ultrasonic method, or electromagnetic method can be used. Indirect determination of streamflow requires construction of hydraulic structures such as flumes, notches, weirs, and gated structures or application of the slope-area method. However, continuous measurement of discharge at number of sections in a river is very costly and sometimes impractical. To eliminate this limitation, rating curve is commonly used. Development of the rating curve for a gauging site generally requires two step procedures. First step is to develop stage–discharge relationship using historical stage–discharge data of the gauging site by plotting the graph between the observed stage (G) and observed discharge (Q) at the gauging site. Second step involves the measurement of stages only, and the corresponding discharges are estimated from the rating curve. It is easy to maintain a continuous record of stages rather than maintaining continuous record of discharges in a river. Thus eliminating the need of rigorous, time-consuming, costly, and sometime impractical exercise of continuous discharge measurement, the rating curve has always been extensively used for discharge measurement in many artificial and natural channel as it represents the combined effect of many flow and channel parameters.

Stage–discharge relationship has always remained an area of interest for hydrologists, and many attempts have been made by hydrologists to establish reliable rating curves using graphical and numerical techniques. Stage–discharge relationship was established by graphical method by Herschy (1995), Fenton and Kellar (2001). Polynomial models for stage–discharge relationships were proposed by Herschy (1995), McGinn and Chubak (2002) Braca (2008). For extrapolating rating curve beyond the measured data range, various numerical methods have been proposed including regression method (Peterson-Øverlier 2004; Moyeed and Clarke 2005), polynomial regression model (Braca 2008), support vector machine (SVM) (Sivapragasam and Mutlie 2005), artificial neural network (ANN) (Tawfik et al. 1997; Jain and Chalisgaonkar 2000; Sudheer and Jain 2003; Bhattacharya and Solomatine 2005; Habib and Maselhe 2006), gene expression programming (GEP) (Guven and Aytek 2009), ANN with LMR (Bisht et al. 2010), and genetic algorithm (GA) with model tree (Ghimire and Reddy 2010). Least square fitting of rating curve using curvilinear asymptotes (Mir and Dubeau 2014) outlined the limitations of earlier approaches and proposed the use of curvilinear asymptotes rather than straight ones.

The accuracy of discharge estimated from the rating curve depends on the accuracy of stage measurement and development of the rating curve. The conventional method of regression analysis for the development of the rating curve often fails to give stage–discharge relationship accurately. The main objective of the present study was, therefore, to implement a simple and quick optimization-based Excel solver to estimate the parameters of the stage–discharge relationship and assess the performance of conventional method as well as Excel solver approach for stage–discharge data.

Conventional Method

The stage–discharge relationship is generally a single-valued relation for majority of streams and rivers, especially non-alluvial rivers (Subramanya 2008), and it is represented as

$$Q = K\left( {G - a} \right)^{n} ,$$
(1)

where Q = stream discharge; G = stage height; a = constant representing the gauge reading corresponding to zero discharge; K and n are rating curve parameters.

Being a hypothetical parameter, ‘a’ cannot be determined in field. The following methods are available to find the value of gauge height corresponding to zero discharge.

  1. (1)

    To estimate the value of ‘a,’ Q versus G plot is prepared on arithmetical graph paper and best fit curve is drawn. By extrapolating the curve, the value of ‘a’ is found by eye judgment. This value of ‘a’ is then utilized to obtain a plot of log Q versus log (G − a). If the plot obtained is a straight line, previously assumed value of ‘a’ is correct; otherwise, this process is repeated until an acceptable value of ‘a’ is obtained.

  2. (2)

    A graph between Q and G is prepared on an arithmetical scale, and by the eye judgment a smooth best fit curve is drawn. Three discharges Q 1, Q 2, and Q 3 are selected in such a way that Q 1/Q 2 = Q 2/Q 3, and the corresponding values of stages G 1, G 2, and G 3 are noted from the curve. In such case, the following relation may be obtained using Eq. (1).

    $$\frac{{\left( {G_{1} - a} \right)}}{{\left( {G_{2} - a} \right)}} = \frac{{\left( {G_{2 } {-}a} \right)}}{{(G_{3} - a)}},$$
    (2a)

    which may be reduced to

    $$a = \frac{{\left( {G_{1} G_{2} - G_{2}^{2} } \right)}}{{\left( {G_{1} + G_{3} - G_{3} } \right)}}.$$
    (2b)
  3. (3)

    Apart from graphical and arithmetical methods, various optimization techniques can be used to obtain the best value of ‘a.’ The value of ‘a’ can be determined by trial-and-error method. The value of ‘a’ corresponding to correlation coefficient most close to unity is adopted.

As far as the estimation of the rating curve parameters K, n is concerned; they were obtained by the least square error method on the logarithmic plot between Q and (G − a). Excel solver available in Microsoft Excel is a nonlinear optimization code, and its specific implementations have been proved in use over many years as one of the most robust and reliable approaches to solve difficult and highly nonlinear programming problems. A brief description on this solver is provided here.

Excel Solver

Excel solver is an optimization tool in Microsoft Excel basically used for optimization of profit and loss in business problems. Excel solver has the capability to optimize linear as well as nonlinear equations by changing specified parameters. It consists of linear programming solver (LPS) to optimize linear equations, generalized reduced gradient (GRG) solver, and evolutionary solver to optimize nonlinear equations. Rating curve equations are basically of nonlinear form, therefore, GRG nonlinear solver and evolutionary solver are used to obtain the optimum values of rating curve parameters. The GRG solver is a nonlinear optimization code developed by Leon Lasdon from University of Texas at Austin and Allan Waren from Cleveland University. GRG and its specific implementations have been proved in use over many years as one of the most robust and reliable approaches to solve difficult and highly nonlinear programming problems (Lasdon and Smith 1992). GRG solver uses two techniques for determination of search direction. The default choice is quasi-Newton method, a gradient-based technique, and the second choice is the conjugate gradient method. Depending on the available storage, GRG solver may utilize either of the quasi-Newton or conjugate gradient method. Evolutionary solver is a hybrid of genetic and evolutionary algorithms and classical optimization methods, including gradient-free direct search methods, classical gradient-based quasi-Newton methods, and simplex method (Premium Solver Platform 2010).

Analysis, Results, and Discussion

Two data sets of stage and discharge have been procured from literature (Subramanya 2008; Reddy 2011). The rating curve parameters a, K, and n were estimated using Excel solver on the spreadsheet for each data set separately and noted down in Tables 1 and 2 with other details of the results. The values of these parameters based on conventional approach are also provided in the same table just for reference. The difference in values obtained from both approaches may be observed.

Table 1 Comparison of performance evaluation criteria by Excel solver and conventional method (Subramanya 2008)
Full size table
Table 2 Comparison of performance evaluation criteria by Excel solver and conventional method (Reddy 2011)
Full size table

The performance of both the methods for the parameter estimation was assessed with respect to the relation between the observed and the predicted discharges based on the following statistics

$${\text{Root}}\,{\text{Mean}}\,{\text{Square}}\,{\text{Error}}\,\left( {\text{RMSE}} \right) = \sqrt {\frac{{\mathop \sum \nolimits_{i = 1}^{n} \left( {Q_{\text{o}}^{i} - Q_{\text{p}}^{i} } \right)^{2} }}{N}}$$
(3)
$${\text{Nash-Sutcliffe}}\,{\text{criteria}}\,\left( E \right) = \left[ {1 - \frac{{\mathop \sum \nolimits_{i = 1}^{N} \left( {Q_{\text{o}}^{i} - Q_{\text{p}}^{i} } \right)^{2} }}{{\mathop \sum \nolimits_{i = 1}^{N} \left( {Q_{\text{o}}^{i} - \overline{Q} } \right)^{2} }}} \right]$$
(4)
$${\text{Correlation}}\,{\text{coefficient}}\,\left( r \right)\, = \frac{{N\left( {\sum Q_{\text{o}} Q_{\text{p}} } \right) - \left( {\sum Q_{\text{p}} } \right)\left( {\sum Q_{\text{o}} } \right)}}{{\sqrt {\left( {N\left( {\sum Q_{\text{p}}^{2} } \right) - \left( {\sum Q_{\text{p}} } \right)^{2} } \right)\left( {N\left( {\sum Q_{\text{o}}^{2} } \right) - \left( {\sum Q_{\text{o}} } \right)^{2} } \right)} }},$$
(5)

where Q o is the observed discharge; Q p is the predicted discharge, and \(\overline{Q}\) is mean of observed discharge.

The prediction method with lowest error and the highest correlation coefficient would be considered as the best models. The values performance indices have been provided in Tables 3 and 4 for both sets of data under considerations.

Table 3 Performance indices of Excel solver and conventional methods for the data of Table 1
Full size table
Table 4 Performance indices of Excel solver and conventional methods for the data of Table 2
Full size table

These tables indicate that the performance of the Excel solver method is better than that of the conventional method. A qualitative performance assessment of the both the approach for the parameter estimation of the rating curve was also made as shown in Figs. 1, 2, and 3. These figures also indicate that the present approach based on Excel solver is better than the conventional method.

Fig. 1
figure 1

A comparison of rating curves for data set 1

Full size image
Fig. 2
figure 2

A comparison of rating curves for data set 2

Full size image
Fig. 3
figure 3

A comparison of observed and predicted discharges for a data set 1 b data set 2

Full size image

Conclusion

The parameters of the rating curves were estimated using conventional method and optimization-based Excel solver code for the two different sets of the data in the present study. The performance of both the methods was assessed quantitatively as well as qualitatively. It has been found that Excel solver is a promising tool for predicting parameters of the rating curves precisely. Further, it may be noted that the Excel solver eliminates the need of time-consuming and tedious process of trial and error for computation of stage–discharge relationships as in the case of the conventional method.