/// <summary>
/// Compare outputs in text files and SQLite database for given SWAT unit
/// </summary>
/// <param name="source">SWAT unit type</param>
/// <returns>Average R2</returns>
/// <remarks>
/// 1. R2 is calculated for each column
/// 2. A text file would be created on desktop to record R2 for all columns
/// </remarks>
public double Compare(UnitType source)
{
using (StreamWriter file = new StreamWriter(
Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Desktop), source.ToString() + "_" + _extractText.OutputInterval.ToString() + "_validation.txt")))
{
string[] cols = ExtractSWAT_SQLite.GetSQLiteColumns(source);
if (cols == null)
{
return(-99.0);
}
double mean_R2 = 0;
int num_R2 = 0;
foreach (string col in cols)
{
double R2 = Compare(source, col, file);
if (R2 > -99)
{
mean_R2 += R2;
num_R2 += 1;
}
}
if (num_R2 > 0)
{
return(mean_R2 / num_R2);
}
return(-99.0);
}
}
static void TestExtractFromSQLite()
{
Console.WriteLine("********************SQLite********************");
using (ExtractSWAT_SQLite extract =
new ExtractSWAT_SQLite(@"C:\zhiqiang\ModelTestWithSWATSQLite\LaSalle\LaSalle2012\Scenarios\Default\txtinout\result_627.db3"))
{
Console.WriteLine("******************** First Try ********************");
extract.Extract(SourceType.REACH, 1, "FLOW_OUTcms");//not case sensitive
extract.Extract(1993, SourceType.REACH, 1, "FLOW_OUTcms");
extract.Extract(2000, SourceType.REACH, 1, "FLOW_OUTcms");
extract.Extract(2007, SourceType.REACH, 1, "FLOW_OUTcms");
extract.Extract(SourceType.HRU, 1, "ETmm");
extract.Extract(1993, SourceType.HRU, 1, "ETmm");
extract.Extract(2000, SourceType.HRU, 1, "ETmm");
extract.Extract(2007, SourceType.HRU, 1, "ETmm");
Console.WriteLine("******************** Second Try ********************");
extract.Extract(SourceType.REACH, 1, "FLOW_OUTcms");//not case sensitive
extract.Extract(1993, SourceType.REACH, 1, "FLOW_OUTcms");
extract.Extract(2000, SourceType.REACH, 1, "FLOW_OUTcms");
extract.Extract(2007, SourceType.REACH, 1, "FLOW_OUTcms");
extract.Extract(SourceType.HRU, 1, "ETmm");
extract.Extract(1993, SourceType.HRU, 1, "ETmm");
extract.Extract(2000, SourceType.HRU, 1, "ETmm");
extract.Extract(2007, SourceType.HRU, 1, "ETmm");
}
}
public double Compare(UnitType source)
{
//System.Diagnostics.Debug.WriteLine("------------------" + source.ToString() + "------------------");
using (System.IO.StreamWriter file = new System.IO.StreamWriter(
System.IO.Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Desktop), source.ToString() + "_validation.txt")))
{
string[] cols = ExtractSWAT_SQLite.GetSQLiteColumns(source);
if (cols == null)
{
return(-99.0);
}
double mean_R2 = 0;
int num_R2 = 0;
foreach (string col in cols)
{
double R2 = Compare(source, col, file);
if (R2 > -99)
{
mean_R2 += R2;
num_R2 += 1;
}
}
if (num_R2 > 0)
{
return(mean_R2 / num_R2);
}
return(-99.0);
}
}
/// <summary>
/// Extract data from SQLite as datatable
/// </summary>
static void TestExtractFromSQLite()
{
Console.WriteLine("********************SQLite********************");
using (ExtractSWAT_SQLite extract =
new ExtractSWAT_SQLite(@"E:\SWAT\Elie\Elie\Scenarios\default\TxtInOut\result_627_daily.db3"))
{
Console.WriteLine("******************** First Try ********************");
//extract.Extract(UnitType.RCH, 1, "FLOW_OUTcms");//not case sensitive
//extract.Extract(1993, UnitType.RCH, 1, "FLOW_OUTcms");
//extract.Extract(2000, UnitType.RCH, 1, "FLOW_OUTcms");
//extract.Extract(2007, UnitType.RCH, 1, "FLOW_OUTcms");
//extract.Extract(UnitType.HRU, 1, "ETmm");
//extract.Extract(1993, UnitType.HRU, 1, "ETmm");
//extract.Extract(2000, UnitType.HRU, 1, "ETmm");
//extract.Extract(2007, UnitType.HRU, 1, "ETmm");
//Console.WriteLine("******************** Second Try ********************");
//extract.Extract(UnitType.RCH, 1, "FLOW_OUTcms");//not case sensitive
//extract.Extract(1993, UnitType.RCH, 1, "FLOW_OUTcms");
//extract.Extract(2000, UnitType.RCH, 1, "FLOW_OUTcms");
//extract.Extract(2007, UnitType.RCH, 1, "FLOW_OUTcms");
//extract.Extract(UnitType.HRU, 1, "ETmm");
//extract.Extract(1993, UnitType.HRU, 1, "ETmm");
//extract.Extract(2000, UnitType.HRU, 1, "ETmm");
//extract.Extract(2007, UnitType.HRU, 1, "ETmm");
}
}
public SQLiteTest(string scenariosDir, string scenarioName)
{
_extractSQLite = new ExtractSWAT_SQLite(scenariosDir + @"\" + scenarioName + @"\txtinout\result_627.db3");
_extractText = new ExtractSWAT_Text(scenariosDir,scenarioName);
_startYear = _extractText.StartYear;
_endYear = _extractText.EndYear;
}
public SQLiteValidation(string scenariosDir, string scenarioName, OutputIntervalType interval)
{
_extractSQLite = new ExtractSWAT_SQLite(scenariosDir, scenarioName,interval);
_extractText = new ExtractSWAT_Text_SWATPlot(scenariosDir,scenarioName);
_startYear = _extractText.StartYear;
_endYear = _extractText.EndYear;
}
public SQLiteValidation(string scenariosDir, string scenarioName, OutputIntervalType interval)
{
_extractSQLite = new ExtractSWAT_SQLite(scenariosDir, scenarioName, interval);
_extractText = new ExtractSWAT_Text_SWATPlot(scenariosDir, scenarioName);
_startYear = _extractText.StartYear;
_endYear = _extractText.EndYear;
}
public SQLiteTest(string scenariosDir, string scenarioName)
{
_extractSQLite = new ExtractSWAT_SQLite(scenariosDir + @"\" + scenarioName + @"\txtinout\result_627.db3");
_extractText = new ExtractSWAT_Text(scenariosDir, scenarioName);
_startYear = _extractText.StartYear;
_endYear = _extractText.EndYear;
}
private double Compare(int startYear, int endYear, UnitType source, int id, string var)
{
string col_sqlite = var.Trim();
string col_text = ExtractSWAT_SQLite.ColumnSQLite2Text(source, var).Trim();
DataTable dtSQLite = _extractSQLite.Extract(startYear, endYear, source, id, col_sqlite);
DataTable dtText = _extractText.Extract(startYear, endYear, source, id, col_text);
if (dtSQLite == null || dtText == null)
{
return(-99.0);
}
if (dtSQLite.Rows.Count == 0 || dtText.Rows.Count == 0)
{
return(-99.0);
}
//Console.WriteLine(string.Format("Extract time for {0}-{1}-{2}-{3}-{4}: SQLite = {5:F4} ms, Text = {6:F4} ms",
// startYear, endYear, source, id, var, _extractSQLite.ExtractTime,_extractText.ExtractTime));
//the join table structure
DataTable dt = new DataTable();
dt.Columns.Add("TIME", typeof(DateTime));
dt.Columns.Add("SQLite", typeof(double));
dt.Columns.Add("Text", typeof(double));
//join these two tables using Linq
var results = from table1 in dtSQLite.AsEnumerable()
join table2 in dtText.AsEnumerable() on table1["TIME"] equals table2["TIME"]
select dt.LoadDataRow(new object[]
{
table1["TIME"],
table1[col_sqlite],
table2[col_text]
}, false);
results.CopyToDataTable();
return(CalculateR2(dt, "SQLite", "Text", ""));
}
/// <summary>
/// Initialize the data extraction class
/// </summary>
/// <param name="txtinoutPath"></param>
public SQLiteValidation2(string txtinoutPath)
{
_extractText = new ExtractSWAT_Text_FileHelperEngine(txtinoutPath);
_extractSQLite = new ExtractSWAT_SQLite(txtinoutPath, _extractText.OutputInterval);
}
/// <summary>
/// Extract data from SQLite as datatable
/// </summary>
static void TestExtractFromSQLite()
{
Console.WriteLine("********************SQLite********************");
using(ExtractSWAT_SQLite extract =
new ExtractSWAT_SQLite(@"E:\SWAT\Elie\Elie\Scenarios\default\TxtInOut\result_627_daily.db3"))
{
Console.WriteLine("******************** First Try ********************");
//extract.Extract(UnitType.RCH, 1, "FLOW_OUTcms");//not case sensitive
//extract.Extract(1993, UnitType.RCH, 1, "FLOW_OUTcms");
//extract.Extract(2000, UnitType.RCH, 1, "FLOW_OUTcms");
//extract.Extract(2007, UnitType.RCH, 1, "FLOW_OUTcms");
//extract.Extract(UnitType.HRU, 1, "ETmm");
//extract.Extract(1993, UnitType.HRU, 1, "ETmm");
//extract.Extract(2000, UnitType.HRU, 1, "ETmm");
//extract.Extract(2007, UnitType.HRU, 1, "ETmm");
//Console.WriteLine("******************** Second Try ********************");
//extract.Extract(UnitType.RCH, 1, "FLOW_OUTcms");//not case sensitive
//extract.Extract(1993, UnitType.RCH, 1, "FLOW_OUTcms");
//extract.Extract(2000, UnitType.RCH, 1, "FLOW_OUTcms");
//extract.Extract(2007, UnitType.RCH, 1, "FLOW_OUTcms");
//extract.Extract(UnitType.HRU, 1, "ETmm");
//extract.Extract(1993, UnitType.HRU, 1, "ETmm");
//extract.Extract(2000, UnitType.HRU, 1, "ETmm");
//extract.Extract(2007, UnitType.HRU, 1, "ETmm");
}
}
/// <summary>
/// Initialize the data extraction class
/// </summary>
/// <param name="txtinoutPath"></param>
public SQLiteValidation2(string txtinoutPath)
{
_extractText = new ExtractSWAT_Text_FileHelperEngine(txtinoutPath);
_extractSQLite = new ExtractSWAT_SQLite(txtinoutPath, _extractText.OutputInterval);
}
/// <summary>
/// Read data from given SWAT unit using given method
/// </summary>
/// <param name="source">SWAT unit type</param>
/// <param name="method">Data reading method</param>
/// <returns>Average reading time</returns>
/// <remarks>Read data of all columns and all ids and then calcuate the average data reading time for one column and one id</remarks>
public string Read(UnitType source, DataReadingMethodType method, bool isQueryEachYear)
{
//if (source == UnitType.HRU && method == DataReadingMethodType.SWATPlot)
// return 0.0;
System.Diagnostics.Debug.WriteLine(string.Format("******* {0} {1} *******", source, method));
using (ExtractSWAT ex = ExtractSWAT.ExtractFromMethod(method, _txtinoutPath))
{
//get number of record and SWAT unit
int numofRecord = _modelInfo.NumberOfRecordForEachUnit;
int numUnits = _modelInfo.NumberofSubbasin;
if (source == UnitType.HRU)
{
numUnits = _modelInfo.NumberofHRU;
}
if (numUnits > MAX_NUM_TESTED_IDS)
{
numUnits = MAX_NUM_TESTED_IDS; //limit the number of ids to be tested to reduce the running time, especially for HRU
}
//all columns
//this should be dynamic
string[] cols = ExtractSWAT_SQLite.GetSQLiteColumns(source);
int numCols = Math.Min(cols.Length, MAX_NUM_TESTED_COLUMNS); //limit the number of ids to be tested to reduce the running time, especially for HRU
//get number of year
int numYears = 1;
if (isQueryEachYear)
{
numYears = Math.Min(_modelInfo.EndYear - _modelInfo.StartYear + 1, MAX_NUM_TESTED_YEARS);
}
//initialize
DataTable dt = null;
double reading_time = 0.0;
//get output file
string outputFile = getOutputFile(isQueryEachYear, _modelInfo.OutputInterval, source, method);
using (StreamWriter file = new StreamWriter(outputFile))
{
//add header
file.WriteLine("Column,Time");
//start to read
for (int colIndex = 0; colIndex < numCols; colIndex++)
{
string column = cols[colIndex];
Console.Write(column.PadRight(20));
double col_time = 0.0;//data reading time for current column
for (int id = 1; id <= numUnits; id++)
{
//ouput the id
if (id > 1)
{
Console.Write(",");
}
Console.Write(id);
if (!isQueryEachYear) //all years
{
//read data
dt = ex.Extract(source, -1, id, column, true);
//make sure the number of record is same
if (dt.Rows.Count != numofRecord)
{
throw new Exception(string.Format("Wrong number of records from {0} {1} on column {2} and id {3}!", source, method, column, id));
}
//add the reading time to the column reading time
col_time += ex.ExtractTime;
}
else //each year
{
for (int yearIndex = 0; yearIndex <= numYears; yearIndex++)
{
int year = _modelInfo.StartYear + yearIndex;
//read data
dt = ex.Extract(source, year, id, column, true);
//add the reading time to the column reading time
col_time += ex.ExtractTime;
}
}
}
Console.WriteLine("");
//calculate the average column reading time
//and output in debug window to make sure all column have similar reading time
col_time /= numUnits * numYears;
file.WriteLine(string.Format("{0},{1:F4}", column, col_time));
System.Diagnostics.Debug.WriteLine(string.Format("{0}: {1:F4}", column, col_time));
//add to total reading time
reading_time += col_time;
}
}
return(string.Format("{0},{1}", ex.PrepareTime, reading_time / numCols));
}
}
