Applied Hydrogeology
Spreadsheet modeling
Adam Říčka
Iterative methods
Iterative methods need initial values at iteration level m, hi,j
m and the purpose is to calculate
Hi,j
m+1.
Jacobi iteration
Resulted values from previous level of iteration are used in actual iteration – not often used
Gauss-Seidel iteration
Newly computed values in the iteration formula are used: iteration level m+1 values are
available for nodes (i-1,j) and (i,j-1) when calculating h for node (i,j) - more efficient than
Jacobi iteration
Succesive over relaxation (SOR)
Convergence rate of Gauss-Seidel iteration method can be improved by relaxation factor ω,
which is obtained by trial and error and optimal value is between 1,5 - 1,8
4/)( 1,1,,1,1
1
,
m
ji
m
ji
m
ji
m
ji
m
ji hhhhh
4/)( 1,1,
1
,1
1
,1
1
,
m
ji
m
ji
m
ji
m
ji
m
ji hhhhh
4/)()1( 1,,1
1
1,
1
,1,
1
,
m
ji
m
ji
m
ji
m
ji
m
ji
m
ji hhhhhh
4
1,1,,1,1
,
m
ji
m
ji
m
ji
m
jim
ji
hhhh
h
4
1
1,1,
1
,1,11
,
m
ji
m
ji
m
ji
m
jim
ji
hhhh
h
Gauss-Seidel Iteration
initial guesses m and
next step m+1
1mm
hh
initial guesses m
Iteration reached Convergence criteria
Iterative methods
Let’s try iterative method
in spreadsheet by hand
Steady-state flow without leaves and/or enters
02
2
2
2
y
h
x
h0
y
q
x
q
y
h
kq
x
h
kq
yy
xx
Laplace’s
equation
514131211 qqqqq in 015141312
x
z
hh
Kz
x
hh
Kx
z
hh
Kz
x
hh
K
22
53
2
42
2
1
)(2)(2
)()()()(
xz
hhxhhz
h
4
)( 5432
1
hhhh
h
4
1,1,,1,1
,
jijijiji
ji
hhhh
h
Darcy’s law in x
and y direction
Water balance
equation
0
y
h
K
yx
h
K
x
Finite difference approximation of Laplace’s equation
Central difference
=+
K is constant
providing dx and dz is the same
2-D Cross-section
Boundary conditions – mesh centered grid
No-flow boundary 0
x
h
0
z
h
0,1,1 jiji hh
4
)2( 432
1
hhh
h
4
)22( 32
1
hh
hcorner No-flow boundary
Constant head boundary
Simply enter appropriate head value in the boundary cell and use central difference
providing dx and dz is the same
providing dx and dz is the same
Steady-state flow with leaves and/or enters
R
y
h
kb
yx
h
kb
x
T
R
y
h
x
h
2
2
2
2
T
R
y
h
x
h 2
2
2
2
2
Poisson equation
Confined aquifer
Poisson equation
Unconfined aquifer
Finite difference approximation of Poisson equation
4
5432
1
K
Q
hhhh
h
Note – from mathematical expression implies:
• Negative sign means recharge (e. g. injection well)
• Positive sign means discharge (e.g. pumping well)
providing dx and dz is the same
Cross-section model – Tóth problem
2-D simulation, steady-state flow
Unconfined, isotropic aquifer
Physical model conception Mathematical model conception
Water balance at Mesh and Block centered grid
Cell in grid
Only ½Q at
no-flow
boundary
condition
Grid node
Imaginary
nodes in no-
flow
boundaries
Cell in grid
Grid node
Cross-section model – Tóth problem
2-D simulation, steady-state flow
Unconfined, isotropic aquifer
hTQy
yx
yhxbkQy ))((
hTQx
yx
xhybkQx ))((
2/y
y
Qx
2/x xx
Qy
y
2/hTQy
2/hTQx
Water balance in mesh centered grid
4
1,1,,1,1
,
jijijiji
ji
hhhh
h
4
)2( 1,1,,1
,
jijiji
ji
hhh
h
4
)22( 1,,1
,
jiji
ji
hh
h
4
)22( ,11,
,
jiji
ji
hh
h
4
)2( 1,1,,1
,
jijiji
ji
hhh
h
4
)2( ,1,1,1
,
jijiji
ji
hhh
h
4
1,1,,1,1
,
K
Q
hhhh
h
jijijiji
ji
Spreadsheet approach – groundwater flow
Well
2-D flow in Excel spreadsheet
4
1,1,,1,1
,
jijijiji
ji
hhhh
h
4
)2( 1,1,,1
,
jijiji
ji
hhh
h
4
)22( 1,,1
,
jiji
ji
hh
h
4
)22( ,11,
,
jiji
ji
hh
h
4
)2( 1,1,,1
,
jijiji
ji
hhh
h
4
)2( ,1,1,1
,
jijiji
ji
hhh
h
4
1,1,,1,1
,
K
Q
hhhh
h
jijijiji
ji
Well
2-D water balance in Excel spreadsheet
hTQy
yx
yhxbkQy ))((
hTQx
yx
xhybkQx ))((
2/y
y
Qx
2/x xx
Qy
y
2/hTQy
2/hTQx
Water balance in mesh centered grid
Difference between input and output = error of numerical approximation related
to (among others) Convergence criteria and grid spacing
Block-centered grid in Excel spreadsheet
References
Anderson, P., M., Bair, E., S. (2001): The Power of Spreadsheet Models.- Modflow 2001
and other modeling odysseys Proceedings, 2001, International Ground Water Modeling
Center, Colorad School of Mines, Golden, CO, p. 815-822.
Fox, P., J. (1996): Spreadsheet Solution Method for Groundwater Flow Problems.Subsurface
Fluid-Flow (Ground-water and Vadose Zone) Modeling, ASTM STP 1288,
Joseph D. Ritchey and James O. Rumbaugh, Eds., American Society for Testing and
Materials.
Karvonen, T. (2001): Soil and Groundwater Hydrology: Basic Theory and Application of
Computational Methods.- Electronic Book, Helsinki University of Technology, Laboratory
of Water Resources. Distributed via Internet: http://www.water.tkk.fi
Wang, H.,F., Anderson, M., P. (1982): Introduction to Groundwater Modeling: Finite
Difference and Finite Element Methods, W. H. Freeman, 256 p.