Modelling

Quantitative modelling

The exercise below is copied from the Manual of our 2nd year module, GEOG250 Applied GIS and numerical modelling.

7.3 Changes in lake composition: a simple illustration of the finite difference method.

Imagine we have an acidified lake. It is 10 hectares in area, and 5 m deep on average (the volume is thus 500 000 m³). A stream feeds into the lake, bringing water with the same acidity that the lake started with. The stream flow rate is constant at 8000 m³/day. The lake has now been limed to allow fish to survive, giving an enhanced Ca concentration of 20 g/m³ above its pre-liming value (a value you have not been given, and makes no difference to the calculation). This will provide protection to the fish until the enhanced Ca falls to 5 g/m³. How long will it be before this threshold is reached?

Picture this model. We have a well-mixed (and rapidly mixed) tank with an inflow and an outflow. The inflow brings no enhanced Ca, but the outflow removes enhanced Ca from the lake. We can write a mass balance expression for the change in lake water Ca:

Just as with the leaky container example, the problem is to decide which value to us for the mean Ca concentration? For each time step, we must either remove the outflow water before adding the inflow water (in which case the mean lake Ca is the value at the end of the previous time period), or we must add the inflow water first, which dilutes the lake Ca (remember the assumption of rapid mixing) and reduces the calculated Ca loss rate. Again, just as with the previous example, the trick is to find a small enough time step that it make no difference which of the two values is used.

In the practical, you will solve this problem, and will experiment with different time steps to identify a suitable step by trial and error.

7.4 The practical exercise.

In your spreadsheet, you must represent changes through time. Thus, one dimension in the spreadsheet must be a time dimension. I find it convenient to increment time down the rows. Follow the steps below:

In the top left corner of the spreadsheet enter a title and your name

Enter the key parameters in tabular form in the top left of the spreadsheet. Write the units as well as the variable names. Put the value in a separate cell on the same row as the variable name. For example:

Further down in the spreadsheet, starting close to the left hand side, type a column title. Say, ‘time’.
In the cell below enter 0 (time starts at zero).
For the next 100 cells below the zero, increment the time using the time step from the table above. (Incrementing a number can be done very conveniently in a spreadsheet. For example, let’s say the 0 value is in cell A9. Then (assuming that the time step value is stored in cell B5), write in cell A10 the formula =A9+B$5, and copy this down to the cells below. This makes each cell equal to the value of the one above plus the value stored in cell B5.

Note the $ sign makes the cell row number in the formula always refer to the same row (5 in this case). If the $ symbol was not used, then each cell to which the formula was copied would refer to a different cell. Try it!

In successive columns to the right of the one you have just put ‘time’ into, enter the additional titles (make sure that you put them in the same row as the ‘time’ title): ‘Conc’, ‘Mass ’, ‘Export ’
Calculate the mass of Ca present at the first time step (i.e., in the top cell in the ‘mass’ column). The mass of Ca is found by multiplying the Ca concentration by the volume of water present. Make the first cell below the ‘mass.’ title equal to the Ca concentration (g/m³) times the water volume (m³) (using absolute ($) cell references to data in the table at the top of the spreadsheet). <>Do not simply enter the values; one of the strengths of spreadsheet modeling is that variables can be changed.
In the first cell below the ‘Export’ title, write a formula that make it equal to:

Time step ´ inflow rate ´ current mass / lake volume

Note that all of these variables except ‘current mass’ refer to data from the table, and must be written as a formula with absolute cell references. The ‘current mass’ is the value for mass in the same row (i.e., for the same time step). Use a cell reference (not an absolute reference, so don’t use a $ symbol) to the cell in the ‘mass’ column on the same row (the next row to the left if you have followed the instructions). Note: don’t worry that most of the ‘mass’ cells are empty when you do this. They will be filled in shortly.

By using this formula we are saying that the export of Ca is equal the concentration of Ca in the lake water (Here calculated from mass of Ca divide by lake volume) times the volume per day of exported water. The reason for not getting the concentration directly is that it is convenient for the sake of clarity to focus on the loss of total Ca mass. The changing concentration will then be calculated from the changing mass.

Now, copy the formula you have just written down to the lowest cell in the column. This is because we want the same formula in all cells in this column. I.e., the export of Ca is always a function of the mass in the lake (in the adjacent cell), the lake volume, water outflow rate, and the time step.
The mass of Ca in the lake is calculated in the same way for all cells except the first (which we have already calculated). For the cell below the first cell, and all cells beneath it, we want the mass to be equal to the mass in the previous time period (the value in the cell above), minus the amount exported in the previous time period (the value in row above, in the ‘export’ column). I.e., new mass = old mass – old export

When you have written the formula, copy it down to the lowest cell in the column.

The last step is to calculate the concentrations. In the ‘conc’ column calculate the changing Ca concentration from the changing mass, using the formula: concentration = mass/volume
Finally, create a graph of concentration (y-axis) against time (x-axis). Insert this graph in the spreadsheet where it can be conveniently monitored.

7.5 Your report

Remember, as in the first week, to submit both:

A hard copy to the Geography Office
E-mailed attachment to jfb@liv.ac.uk (with your name in the ‘subject’ field).

Run the model for a range of time steps (both greater and lower than 20). This can easily be done, by simply adjusting the value in B5. Don’t simply make small changes (e.g., 19, 20, 21, 22). Instead try doubling and halving the time steps (1, 2, 5, 10, 20, 40, 80, for example). For each time step, make a note of how many days it takes for the Ca concentration to fall to the critical value (5 mg/l). Note: there is only one ‘correct’ number of days. The reason that different time steps can give different answers is that over-long time steps give inaccurate results.

As the time step gets small, there will be less and less difference in the length of time the model predicts it will take to reach the critical Ca concentration (simply because when the time step is small enough, all values give a correct, or nearly correct, result). Assuming that your shortest time step gives you the ‘true’ value, you can express the other time period estimates as percentages of the ‘true’ value (i.e., 100´Tother time step/Tshortest time step). Plot a scatter graph (XY graph) of the answer (expressed as percentages of the ‘true’ answer) against the corresponding time step magnitude.

Your report must consist of:

A single graph with Ca concentration/time curves for some of your time steps (No more than 8, carefully selected to illustrate a wide range of time step values)
A graph showing the dependence of your answer on the time step size (i.e., XY plot of days to 5 mg/g v. time step).
Your estimate of a maximum acceptable time step, with some discussion justifying your choice. To answer this read this week’s text carefully. Remember that short time steps give more accurate results, but take longer to calculate. You need to find a compromise time step that is accurate enough, but does not take too long.
Your best estimate of the time it will take for the lake Ca concentration to reach the critical value.

Return