Ejemplo n.º 1
0
        /// <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);
            }
        }
Ejemplo n.º 2
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");
            }
        }
Ejemplo n.º 3
0
        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);
            }
        }
Ejemplo n.º 4
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");
            }
        }
Ejemplo n.º 5
0
        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;
        }
Ejemplo n.º 6
0
        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;
        }
Ejemplo n.º 7
0
        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;
        }
Ejemplo n.º 8
0
        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;
        }
Ejemplo n.º 9
0
        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", ""));
        }
Ejemplo n.º 10
0
 /// <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);
 }
Ejemplo n.º 11
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");
            }
        }
Ejemplo n.º 12
0
 /// <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));
            }
        }