The third Brewing Salts prototype. This one separates mash and sparge modification, and calculates residual alkalinity.

brewing-salts-3

/**
This is a program that automatically determines appropriate mineral additions for brewing water.

I was playing with Bru'n Water, trying to determine how to adjust my water (Boston MWRA), and
was frustrated with the somewhat manual trial-and-error process. With this program I type in my
current and desired water profiles, and it automatically determines a suggested list of
salt and acidadditions. It solves the problem of salt additions, but it is not a replacement 
for tools like Bru'n Water: it does not verify the water report, and currently will only suggest
mash adjustments, and ignores sparge water completely.

The way I am currently using this is to select the target water profile from Bru'n Water, type
the targets into this program, and run it. It produces a list of suggested salt additions, often
some of those salts are in very small quantities, so I will use the max_XXX (e.g. max_gypsum)
parameter to completely disallow certain salts, then re-run the program to get a new suggestion.

To run the program type:

   glpsol -m brewing-water.glpk

If you copy the "data" section from the bottom of this file to a new file, you can have separate
configurations for different water profiles. To choose which one to use, run a command like this:

    glpsol -m brewing-water.glpk -d boston-to-amber.dat

This is a work in progress. It would be nice to provide more example water profiles, and an
improved user interface. Automating the "minimum salt addition" part would make it a lot easier.
Ultimately, turning it into an online tool would make it easiest for other people to use, since
most people don't have GLPK installed.
*/


param mash_gallons;
param sparge_gallons;


param target_residual_alkalinity;

param target_calcium;
param target_magnesium;
param target_sodium;
param target_sulfate;
param target_chloride;
param target_bicarbonate;

param initial_calcium;
param initial_magnesium;
param initial_sodium;
param initial_sulfate;
param initial_chloride;
param initial_bicarbonate;

param max_gypsum;
param max_epsom;
param max_table;
param max_cacl2;
param max_mgcl2;
param max_soda;
param max_lime;
param max_chalk;

param max_lactic;
param max_phosphoric;
param max_sauermalz;

var mash_gypsum >= 0;
var mash_epsom >= 0;
var mash_table >= 0;
var mash_cacl2 >= 0;
var mash_mgcl2 >= 0;
var mash_soda >= 0;
var mash_lime >= 0;
var mash_chalk >= 0;

var sparge_gypsum >= 0;
var sparge_epsom >= 0;
var sparge_table >= 0;
var sparge_cacl2 >= 0;
var sparge_mgcl2 >= 0;
var sparge_soda >= 0;
var sparge_lime >= 0;
var sparge_chalk >= 0;

var residual_alkalinity;

var mash_calcium >= 0;
var mash_magnesium >= 0;
var mash_sodium >= 0;
var mash_sulfate >= 0;
var mash_chloride >= 0;
var mash_bicarbonate >=0;

var sparge_calcium >= 0;
var sparge_magnesium >= 0;
var sparge_sodium >= 0;
var sparge_sulfate >= 0;
var sparge_chloride >= 0;
var sparge_bicarbonate >=0;

var added_calcium >= 0;
var added_magnesium >= 0;
var added_sodium >= 0;
var added_sulfate >= 0;
var added_chloride >= 0;
var added_bicarbonate >=0;

/* Acids
   It seems a bit atypical for a tool like this
   to suggest sauermalz additions, but I think
   it makes sense. For most recipes, sauermalz
   is being used for water adjustment, and not
   for its flavor.
*/
var mash_lactic >= 0;
var mash_phosphoric >=0;
var mash_sauermalz >= 0;

var e_residual_alkalinity;

var e_calcium;
var e_magnesium;
var e_sodium;
var e_sulfate;
var e_chloride;
var e_bicarbonate;

var abse_residual_alkalinity;

var abse_calcium;
var abse_magnesium;
var abse_sodium;
var abse_sulfate;
var abse_chloride;
var abse_bicarbonate;

/* Assume RA is most important, followed by sulfate and chloride, then sodium and magnesium, then everything else */
minimize z:   abse_calcium + 10*abse_magnesium + 10*abse_sodium 
            + 100*abse_sulfate + 100*abse_chloride + abse_bicarbonate
            + 1000*abse_residual_alkalinity
            /* L1 regularization terms to encourage a sparse solution. Probably not necessary with
               a simplex method solver, but neccesary for the web version that uses an interior
               point method */
            + 0.001 * mash_gypsum
            + 0.001 * mash_epsom
            + 0.001 * mash_table
            + 0.001 * mash_cacl2
            + 0.001 * mash_mgcl2
            + 0.001 * mash_soda
            + 0.001 * mash_lime
            + 0.001 * mash_chalk
            + 0.001 * mash_phosphoric
            + 0.001 * mash_lactic
            + 0.001 * mash_sauermalz

            + 0.001 * sparge_gypsum
            + 0.001 * sparge_epsom
            + 0.001 * sparge_table
            + 0.001 * sparge_cacl2
            + 0.001 * sparge_mgcl2
            + 0.001 * sparge_soda
            + 0.001 * sparge_lime
            + 0.001 * sparge_chalk

            ;

s.t. ABS_RA_P : abse_residual_alkalinity >=  e_residual_alkalinity;
s.t. ABS_RA_N : abse_residual_alkalinity >= -e_residual_alkalinity;

s.t. ABS_CALCIUM_P : abse_calcium >=  e_calcium;
s.t. ABS_CALCIUM_N : abse_calcium >= -e_calcium;

s.t. ABS_MAGNESIUM_P : abse_magnesium >=  e_magnesium;
s.t. ABS_MAGNESIUM_N : abse_magnesium >= -e_magnesium;

s.t. ABS_SODIUM_P : abse_sodium >=  e_sodium;
s.t. ABS_SODIUM_N : abse_sodium >= -e_sodium;

s.t. ABS_SULFATE_P : abse_sulfate >=  e_sulfate;
s.t. ABS_SULFATE_N : abse_sulfate >= -e_sulfate;

s.t. ABS_CHLORIDE_P : abse_chloride >=  e_chloride;
s.t. ABS_CHLORIDE_N : abse_chloride >= -e_chloride;

s.t. ABS_BICARBONATE_P : abse_bicarbonate >=  e_bicarbonate;
s.t. ABS_BICARBONATE_N : abse_bicarbonate >= -e_bicarbonate;

s.t. MAX_GYPSUM : mash_gypsum + sparge_gypsum <= max_gypsum;
s.t. MAX_EPSOM : mash_epsom + sparge_epsom <= max_epsom;
s.t. MAX_TABLE : mash_table + sparge_table <= max_table;
s.t. MAX_CACL2 : mash_cacl2 + sparge_cacl2 <= max_cacl2;
s.t. MAX_MGCL2 : mash_mgcl2 + sparge_mgcl2 <= max_mgcl2;
s.t. MAX_SODA : mash_soda + sparge_soda <= max_soda;
s.t. MAX_LIME : mash_lime + sparge_lime <= max_lime;
s.t. MAX_CHALK : mash_chalk +sparge_chalk <= max_chalk;

s.t. MAX_LACTIC : mash_lactic <= max_lactic;
s.t. MAX_PHOSPHORIC: mash_phosphoric <= max_phosphoric;
s.t. MAX_SAUERMALZ: mash_sauermalz <= max_sauermalz;

/* Residual alkalinity as CaCO3 */
s.t. RESIDUAL_ALKALINITY:
      (mash_bicarbonate) * 0.8197
    - (mash_calcium) * 0.7143
    - (mash_magnesium) * 0.5882
    - mash_lactic * 153.1
    - mash_phosphoric * 14.88
    - mash_sauermalz * 125
    = residual_alkalinity;
s.t. E_RESIDUAL_ALKALINITY:
    residual_alkalinity 
  + e_residual_alkalinity
  = target_residual_alkalinity;


s.t. MASH_CALCIUM:   61.5 * mash_gypsum
                   + 72.0 * mash_cacl2
                   + 142.8 * mash_lime
                   + 105.7 * mash_chalk
                   + initial_calcium
                   = mash_calcium;
s.t. SPARGE_CALCIUM:   61.5 * sparge_gypsum
                     + 72.0 * sparge_cacl2
                     + initial_calcium
                     = sparge_calcium;
s.t. TOTAL_CALCIUM :   mash_calcium * (mash_gallons / (mash_gallons + sparge_gallons))
                     + sparge_calcium * (sparge_gallons / (mash_gallons + sparge_gallons))
                     + e_calcium
                     = target_calcium;

s.t. MASH_MAGNESIUM :   26.0 * mash_epsom
                      + 31.6 * mash_mgcl2
                      + initial_magnesium
                      = mash_magnesium;
s.t. SPARGE_MAGNESIUM :   26.0 * sparge_epsom
                        + 31.6 * sparge_mgcl2
                        + initial_magnesium
                        = sparge_magnesium;
s.t. TOTAL_MAGNESIUM :   mash_magnesium * (mash_gallons / (mash_gallons + sparge_gallons))
                       + sparge_magnesium * (sparge_gallons / (mash_gallons + sparge_gallons))
                       + e_magnesium
                       = target_magnesium;


s.t. MASH_SODIUM :     104.0 * mash_table
                     + 73.2 * mash_soda
                     + initial_sodium
                     = mash_sodium;
s.t. SPARGE_SODIUM :     104.0 * sparge_table
                       + 73.2 * sparge_soda
                       + initial_sodium
                       = sparge_sodium;
s.t. TOTAL_SODIUM :   mash_sodium * (mash_gallons / (mash_gallons + sparge_gallons))
                    + sparge_sodium * (sparge_gallons / (mash_gallons + sparge_gallons))
                    + e_sodium
                    = target_sodium;



s.t. MASH_SULFATE :   147.4 * mash_gypsum
                    + 103.0 * mash_epsom
                    + initial_sulfate
                    = mash_sulfate;
s.t. SPARGE_SULFATE : 147.4 * sparge_gypsum
                    + 103.0 * sparge_epsom
                    + initial_sulfate
                    = sparge_sulfate;
s.t. TOTAL_SULFATE :   mash_sulfate * (mash_gallons / (mash_gallons + sparge_gallons))
                     + sparge_sulfate * (sparge_gallons / (mash_gallons + sparge_gallons))
                     + e_sulfate
                     = target_sulfate;


s.t. MASH_CHLORIDE :   160.3 * mash_table
                     + 127.0 * mash_cacl2
                     + 92.2 * mash_mgcl2
                     + initial_chloride
                     = mash_chloride;
s.t. SPARGE_CHLORIDE :   160.3 * sparge_table
                       + 127.0 * sparge_cacl2
                       + 92.2 * sparge_mgcl2
                       + initial_chloride
                       = sparge_chloride;
s.t. TOTAL_CHLORIDE :   mash_chloride * (mash_gallons / (mash_gallons + sparge_gallons))
                      + sparge_chloride * (sparge_gallons / (mash_gallons + sparge_gallons))
                      + e_chloride
                      = target_chloride;


s.t. MASH_BICARBONATE :   191.9 * mash_soda
                             + 434.8 * mash_lime
                             + 322.3 * mash_chalk
                             + initial_bicarbonate
                             = mash_bicarbonate;
s.t. SPARGE_BICARBONATE :   
                              initial_bicarbonate
                             = sparge_bicarbonate;
s.t. TOTAL_BICARBONATE :   mash_bicarbonate * (mash_gallons / (mash_gallons + sparge_gallons))
                         + sparge_bicarbonate * (sparge_gallons / (mash_gallons + sparge_gallons))
                         + e_bicarbonate
                         = target_bicarbonate;



solve;
printf "\nIon Error\n";
printf "---------\n";
printf "Calcium error: %f\n", -e_calcium;
printf "Magnesium error: %f\n", -e_magnesium;
printf "Sodium error: %f\n", -e_sodium;
printf "Sulfate error: %f\n", -e_sulfate;
printf "Chloride error: %f\n", -e_chloride;
printf "Bicarbonate error: %f\n", -e_bicarbonate;


printf "\nIons Added / Total / Target\n";
printf "---------------------------\n";
printf "Calcium: %f / %f / %f\n", target_calcium - e_calcium - initial_calcium, target_calcium - e_calcium, target_calcium;
printf "Magnesium: %f / %f / %f\n", target_magnesium - e_magnesium - initial_magnesium, target_magnesium - e_magnesium, target_magnesium;
printf "Sodium: %f / %f / %f\n", target_sodium - e_sodium - initial_sodium, target_sodium - e_sodium, target_sodium;
printf "Sulfate: %f / %f / %f\n", target_sulfate - e_sulfate - initial_sulfate, target_sulfate - e_sulfate, target_sulfate;
printf "Chloride: %f / %f / %f\n", target_chloride - e_chloride - initial_chloride, target_chloride - e_chloride, target_chloride;
printf "Bicarbonate: %f / %f / %f\n", target_bicarbonate - e_bicarbonate - initial_bicarbonate, target_bicarbonate - e_bicarbonate, target_bicarbonate;


printf "\npH Data\n";
printf "-------\n";
printf "Residual alkalinity: %f (%f error)\n", residual_alkalinity,
  residual_alkalinity - target_residual_alkalinity;


printf "\n# Mash Water Additions (g/gal) (tsp / %.2f gal) #\n", mash_gallons;
printf "------------------------------------------\n";
printf "---- Mineral Additions ----\n";
printf "Gypsum (CaSO4):      %f g/gal (%f g, ~ %f tsp)\n", mash_gypsum,
       mash_gypsum * mash_gallons, mash_gypsum * mash_gallons / 4.0;
printf "Epsom Salt (MgSO4):  %f g/gal (%f g, ~ %f tsp)\n", mash_epsom,
       mash_epsom * mash_gallons, mash_epsom * mash_gallons / 4.5;
printf "Table Salt (NaCl):   %f g/gal (%f g, ~ %f tsp)\n", mash_table,
       mash_table * mash_gallons, mash_table * mash_gallons / 5.7;
printf "Calcium Chloride (CaCl2):   %f g/gal (%f g, ~ %f tsp)\n", mash_cacl2,
       mash_cacl2 * mash_gallons, mash_cacl2 * mash_gallons / 3.4;
printf "Magnesium Chloride (MgCl2): %f g/gal (%f g, ~ %f tsp)\n", mash_mgcl2,
       mash_mgcl2 * mash_gallons, mash_mgcl2 * mash_gallons / 3.2;
printf "Baking Soda (NaHCO3):    %f g/gal (%f g, ~ %f tsp)\n", mash_soda,
       mash_soda * mash_gallons, mash_soda * mash_gallons / 4.4;
printf "Pickling lime (Ca(OH)2): %f g/gal (%f g, ~ %f tsp)\n", mash_lime,
       mash_lime * mash_gallons, mash_lime * mash_gallons / 4.4;
printf "Chalk (CaCO3):           %f g/gal (%f g, ~ %f tsp)\n", mash_chalk,
       mash_chalk * mash_gallons, mash_chalk * mash_gallons / 4.4;
printf "---- Acid Additions ----\n";
printf "Lactic Acid: %f ml/gal (%f ml)\n", mash_lactic,
  mash_lactic * mash_gallons;
printf "Phosphoric Acid: %f ml/gal (%f ml)\n", mash_phosphoric,
  mash_phosphoric * mash_gallons;
printf "Sauermalz: %f oz/gal (%f oz)\n", mash_sauermalz,
  mash_sauermalz * mash_gallons;


printf "\n# Sparge Water Additions (g/gal) (tsp / %.2f gal) #\n", sparge_gallons;
printf "------------------------------------------\n";
printf "---- Mineral Additions ----\n";
printf "Gypsum (CaSO4):      %f g/gal (%f g, ~ %f tsp)\n", sparge_gypsum,
       sparge_gypsum * sparge_gallons, sparge_gypsum * sparge_gallons / 4.0;
printf "Epsom Salt (MgSO4):  %f g/gal (%f g, ~ %f tsp)\n", sparge_epsom,
       sparge_epsom * sparge_gallons, sparge_epsom * sparge_gallons / 4.5;
printf "Table Salt (NaCl):   %f g/gal (%f g, ~ %f tsp)\n", sparge_table,
       sparge_table * sparge_gallons, sparge_table * sparge_gallons / 5.7;
printf "Calcium Chloride (CaCl2):   %f g/gal (%f g, ~ %f tsp)\n", sparge_cacl2,
       sparge_cacl2 * sparge_gallons, sparge_cacl2 * sparge_gallons / 3.4;
printf "Magnesium Chloride (MgCl2): %f g/gal (%f g, ~ %f tsp)\n", sparge_mgcl2,
       sparge_mgcl2 * sparge_gallons, sparge_mgcl2 * sparge_gallons / 3.2;


printf "\n";

printf "Mash sodium: %f\n", mash_sodium;
printf "Sparge sodium: %f\n", sparge_sodium;


data;

param mash_gallons := 5.5;
param sparge_gallons := 0.0;


/***** Initial Profiles *****/


/* Cambride Water Report, 2013, merged w/ HBT thread from 2012 */
param initial_calcium     := 20.8;
param initial_magnesium   := 4.0;
param initial_sodium      := 79.0;
param initial_sulfate     := 28.0;
param initial_chloride    := 120.0;
param initial_bicarbonate := 60.5;


/***** Target Profiles *****/

/* Bru'n Water "Yellow Malty" */
param target_calcium   := 50.0;
param target_magnesium := 5.0;
param target_sodium    := 5;
param target_sulfate   := 55.0;
/* param target_sulfate := 94.3; */
param target_chloride  := 70.0;
param target_bicarbonate := 0.0;

param target_residual_alkalinity := -95;

/***** Salt Limits (g / gal)*****/

param max_gypsum := 1000;
param max_epsom  := 000;
param max_table  := 1000;
param max_cacl2  := 1000;
param max_mgcl2  := 1000;
param max_soda   := 1000;
param max_lime   := 1000;
param max_chalk  := 1000;

param max_lactic := 000;
param max_phosphoric := 1000;
param max_sauermalz := 1000;

end;