The Clary Lake Water Level Order requires that the dam operator(s) keep a log of lake levels and dam status throughout the year. We’ll keep a field book of course, but I’ve decided to make the recorded data available in a publicly accessible, web-based spreadsheet. A quick glance at the Dam Operation Log spreadsheet shows that it is way more than a simple web-accessible log file! The purpose of this page is to explain what is taking place in the spreadsheet, what the numbers mean, and how they’re being generated. That is a story that goes back to the early days of the Clary Lake water level petition.

The spreadsheet makes considerable use of a bathymetric model of Clary Lake that I’ve been working on for the past 3-4 years, building on the original bathymetric assessment (image at left) that DEP completed in the fall of 2012 during the water level petition proceedings. The DEP survey was a good start and a considerable improvement over data used by DEP’s Division Environmental Assessment to generate their original Contour Report and Water Level Recommendation for Clary Lake which you may recall specified a maximum lake draw down of 2.7 feet. The subsequent Fall 2012 bathymetric assessment resulted in a determination that a maximum lake draw down of 2.0 feet better served to protect and preserve Clary Lake, a determination that Clary Lake Association agreed was much better.

There were several problems with the Fall 2012 bathymetric assessment. One was that the elevation of the high water mark had not been determined at the time of the survey and another is that the Department used an acreage figure for the lake of 667 acres. I’m not sure where this figure came from; the IF&W depth map of Clary states it to be 682 acres. The Normal High Water Mark of Clary Lake was subsequently determined in the Fall of 2014 by CES Inc. to be at a NAVD88 elevation of 151.17 feet, but the bathymetric analysis was not updated with this new data. Nor should it have been really, as it had served its purpose. Our needs for an accurate bathymetric assessment however were just beginning and have been ongoing, and I felt we could build on the DEP model and end up with a more accurate and utilitarian survey.

And so we did. The DEP field survey was conducted in September of 2012 when the lake level was a little over 3 feet below the top of the dam, hence they weren’t able to obtain depth measurements in the vast meadow area at the northwest end of the lake (they did end up modeling this area, though I’m not aware of how they did it). To fill in the missing areas and establish the actual perimeter of Clary Lake, I downloaded LIDAR data (a digital 3D “point cloud” generated from aerial photography) from the Maine Office of GIS, loaded it into a QGIS Geographic Information System and used it to fill in the areas without any elevation data. As luck would have it, the LIDAR data available from MeGIS was from flights made on April 18, 2012 when the lake level was -4.20 feet below the normal high water mark (NHWM). Consequently, the coverage included the entire meadow at the northwest end of the lake. Using the LIDAR data along with the DEP depth data, we were able to generate an accurate contour (depth) map of Clary Lake using an elevation of 151′ for the Normal High Water Mark (image at left).

Contours are lines on the earth of equal elevation. Most people are familiar with them. So the first big surprise was learning that the area within that line on the ground with an elevation of 151 feet which defined and enclosed Clary Lake wasn’t even close to 667 acres, it was more like 762.5 acres, nearly 100 acres more than what was used in the DEP model! Once the contours were finished, the next step was to model the changing area and lake volume as the water level dropped. This was done by taking “slices” of the lake at regular intervals, determining the acreage of each slice and the volume of water between each slice, and then fitting a curve to the resulting data so that volume and area could be calculated given just the lake level below the HWM (see image at left). Other information can be derived from those two piece of data such as percent of volume loss, percent of area loss, etc. Some of this information has been available for some time now on the Current Water Level Charts page.

So the resulting Clary Lake Bathymetric Analysis exists as a project in a geographic information mapping system and several linked spreadsheets which include a bunch of functions and formulas that calculate many different pieces of data all keyed to one value: the water level below the high water mark. The Dam Operation Log is a much simpler spreadsheet that incorporates only a subset of those functions and formulas.

Clary Lake Dam Operation Log

The primary purpose of the Dam Operation Log is to meet the requirements of the Water Level Order to record the lake level and the weir and gate statuses, but it is designed to do a whole lot more than that. Once the basic information has been entered, the spreadsheet calculates the volumes of water flowing over the weir and through the gate (using standard engineering formulas) and compares Total Outflows to the amount of water we should be releasing to maintain the downstream aquatic habitat, otherwise known as Minimum Flows. Minimum Flows are simple enough but widely misunderstood. See Minimum Flows Explained for more information about them.

Once we have a working lake model to play with, other interesting pieces of information can be easily calculated including how much water is involved in raising or lowering the lake a given amount. For example if the lake level is -19.08″ below the HWM with a total lake area of 629.3 acres and the lake has risen 0.05 feet in 24 hours, we can easily calculate how much water in cubic feet per second (cfs) was required to raise it that much: first turn acres into acre-feet, multiply it by the fractional lake level rise in feet, and divide by the number of seconds in 24 hours. That example calculation looks like this:

(629.3 acres x 43560 sq. ft. x 0.05′)/86,400 = 15.9 cfs

Once we know how much water is flowing OUT of the lake during the same time period then we can algebraically sum them to get the total amount of water that was flowing INTO the lake to meet both conditions. The Operation Log then compares total inflows to the minimum flows and lets us know if we’re letting out too much, not enough, or just the right amount of water.

Spreadsheet input consists of only 4 pieces of data: the date, the water level in decimal feet measured at the dam, weir status (height of stop logs installed in the weir in decimal feet), gate status (gate opening in decimal feet). There are also columns for the initials of the operator, and for notes. Currently anyone with the link to the spreadsheet can view it. Here’s a chart generated in real time from the recorded data showing flow statistics:

The spreadsheet is a work in progress and data validation is still taking place. For example, it appears to be working fine now with data being recorded daily. How good will the output be if we only record data weekly? Bi-weekly? Has all the necessary data validation been done? Are all formulas checked for possible error conditions? Is the data in the form and order in which it is most useful? This spreadsheet is after all supposed to be a tool to help us manage the dam effectively. It’s supposed to make that job easier, not harder. It may take a little while to get everything just right.

You’ll find additional information in individual notes attached to each column heading in the spreadsheet (hopefully) explaining what each column represents and on the DATA sheet I’ll be experimenting with various graphs and other ways of data visualization.

Clary Lake Gate Flow Calculations

The Dam Operation Log shows us how much water IS leaving the lake and how much water SHOULD be leaving the lake to maintain the required downstream aquatic base flows. The missing piece then is the correct gate opening to release the right amount of water to make up the difference. To figure that out, we use another spreadsheet to calculate the discharge under a sluice (which is really what our 34″ diameter gate really is). The schematic at left gives you the general idea of what’s happening. The spreadsheet allows you to vary Y1 (the head) and the gate opening (Y2) and spits out a table of flows in cfs:

Clary Lake Gate Flow Calculations

It turns out the gate is NOT a full unobstructed 34″ diameter pipe; the bottom 13.5 inches of the pipe is blocked with a steel plate which reduces the effective area of the pipe and hence the amount of water it can pass. For gate openings up to about 12″ (40 cfs) the spreadsheet calculates sufficiently accurate flow figures which is more than enough to handle the largest minimum flow value. Fully open and with a full lake the gate will pass about 55 cfs.