void BMPReach::loadTimeSeriesData(string databasePath,time_t startTime, time_t endTime,int interval)
{
if(!HasTimeSeriesData()) return;
string tableName = TimeSeriesDataTableName();
if(tableName.length() == 0)
{
ostringstream oss;
oss << "The data source for " << m_bmpName << m_reachStructureId << "is empty.";
throw ModelException("BMPReach","loadTimeSeriesData",oss.str());
}
string hydroClimatePath = databasePath + File_HydroClimateDB;
if(!DBManager::IsTableExist(hydroClimatePath,tableName))
throw ModelException("BMPReach","loadTimeSeriesData","The hydroclimate database '" + hydroClimatePath +
"' does not exist or the there is not a table named '" + tableName + "' in hydroclimate database.");
DBManager db;
db.Open(hydroClimatePath);
try
{
utils util;
string sStart = util.ConvertToString(&startTime);
string sEnd = util.ConvertToString(&endTime);
string strSQL = "SELECT * FROM " + tableName + " WHERE [DATE] >= DATE('"+sStart+"') AND [DATE] <= DATE('"+sEnd+"')";
slTable* tbl = db.Load(strSQL);
if(tbl->nRows > 0)
{
int dateColIndex = 1;
for(int j=0;j<tbl->nCols;j++)
{
string col = tbl->FieldValue(0,j);
int colType = TimeSeriesDataName2Type(col);
if(colType == BMP_REACH_UNKONWN_TYPE) continue;
for(int i=1;i<=tbl->nRows;i++)
{
time_t t = util.ConvertToTime(tbl->FieldValue(i,dateColIndex), "%4d-%2d-%2d", false);
float value = float(atof(tbl->FieldValue(i,j).c_str()));
m_timeSerieseData[colType][t] = value;
}
}
}
delete tbl;
db.Close();
}
catch(...)
{
db.Close();
throw;
}
}
//! Get Ascii file headers information
void clsRasterData::getASCHeaders(string databasePath,map<string,float>* headers)
{
DBManager dbman;
string sql;
slTable* tbl;
// open the database
dbman.Open(databasePath + File_ParameterDB);
// if there is not an error
if(dbman.IsError()) throw ModelException("clsRasterData","getASCHeaders","Can't open paramter database!");
// constrcut the SQL statement for the query
sql = "SELECT Parameter,Value FROM Header";
// run the query
tbl = dbman.Load(sql);
if (tbl->nRows == 0) throw ModelException("ModuleParamter","getParameterFromDatabase","Can't find ASC Headers in paramter database!");
headers->clear();
//headers = new map<string,float>();
for(int i=1;i<=tbl->nRows;i++)
{
(*headers)[tbl->FieldValue(i, 1)] = float(atof(tbl->FieldValue(i, 2).c_str()));
}
delete tbl;
tbl = NULL;
dbman.Close();
}
//! if the given table exists
bool DBManager::IsTableExist(string databasePath,string tableName)
{
utils util;
if(!(util.FileExists(databasePath))) return false;
DBManager dbman;
bool exist = false;
dbman.Open(databasePath);
if (!dbman.IsError())
{
string strSQL = "pragma table_info("+tableName+")";
//string strSQL = "SELECT * FROM sqlite_master WHERE type = 'table' and name = '"+tableName+"'";
slTable* tbl = dbman.Load(strSQL);
if(tbl->nRows > 0) exist = true;
delete tbl;
tbl = NULL;
}
dbman.Close();
return exist;
}
//! Constructor for Sqlite
//! \deprecated For now, this constructor is deprecated!
clsSpecificOutput::clsSpecificOutput(string projectPath,string databasePath,clsRasterData* templateRasterData,string outputID)
{
m_outputID = outputID;
string path = databasePath + File_HydroClimateDB;
string tableName = TableNameFromOutputID(outputID);
if(tableName.length() == 0)
throw ModelException( "clsSpecificOutput","clsSpecificOutput",
"The output id "+ outputID + " can't output specific cells.");
if(!DBManager::IsTableExist(path,tableName))
throw ModelException( "clsSpecificOutput","clsSpecificOutput",
"The database " + path +
" dose not exist or the table "+tableName+" does not exist in this database.");
if(templateRasterData == NULL)
throw ModelException( "clsSpecificOutput","clsSpecificOutput",
"The templateRasterData is null.");
m_templateRasterData = templateRasterData;
utils util;
DBManager db;
DBManager dbman;
try
{
// open the hydroclimate database
dbman.Open(path);
// if there is no error
if (!dbman.IsError())
{
// create the query for the data table
// Use the start date and end date to limit the time series data
string strSQL = "SELECT TIME, Longitude, Latitude, ID, MEASURED FROM " + tableName + " order by TIME";
// run the query
slTable* tbl = dbman.Load(strSQL);
// if the query is successful
if (tbl != NULL)
{
// read in the data
for (int idx=1; idx<=tbl->nRows; idx++)
{
time_t time= util.ConvertToTime(tbl->FieldValue(idx,0), "%4d-%2d-%2d", false);
float nrow = (float)(atof(tbl->FieldValue(idx,1).c_str()));
float ncol = (float)(atof(tbl->FieldValue(idx,2).c_str()));
int position = templateRasterData->getPosition(nrow,ncol);
if(position > -1)
{
m_times.push_back(time);
m_positions.push_back(position);
m_values.push_back(-99.0f);
m_slope.push_back(-99.0f);
m_curvature.push_back(-99.0f);
m_landuse.push_back(-99.0f);
m_ids.push_back(tbl->FieldValue(idx,3));
//the measurement is added for convenient comparison
m_measurement.push_back((float)(atof(tbl->FieldValue(idx,4).c_str())));
}
}
setSlope(projectPath);
setCurvature(projectPath);
setLanduse(projectPath);
delete tbl;
}
tbl = NULL;
dbman.Close();
}
}
catch (...)
{
dbman.Close();
throw;
}
}
void BMPReach::loadParameters(string parameterTableName,int reachStructurId)
{
string bmpDatabase = m_bmpDatabasePath + BMP_DATABASE_NAME;
if(!DBManager::IsTableExist(bmpDatabase,parameterTableName))
throw ModelException("BMPReach","loadParameters","The BMP database '" + bmpDatabase +
"' does not exist or the there is not a table named '" + parameterTableName + "' in BMP database.");
//read corresponding data
DBManager db;
db.Open(bmpDatabase);
try
{
string tableInfoSQL = "pragma table_info("+parameterTableName+")";
slTable* table = db.Load(tableInfoSQL);
//get all numeric and text columns
map<int,string> textColumns;
map<int,bool> numericColumns;
int methodIndex = -1;
int sedimentMethodIndex = -1;
int nutrientMethodIndex = -1;
for(int i = 0;i < table->nRows; i++)
{
string type = table->FieldValue(i+1,2);
if(!StringMatch(type,"TEXT"))
{
if(StringMatch(type,"DATE") || StringMatch(type,"DATETIME"))
numericColumns[i] = true;
else
numericColumns[i] = false;
}
else
{
textColumns[i] = table->FieldValue(i+1,1);
if(StringMatch(textColumns[i],RESERVOIR_FLOW_ROUTING_METHOD_COLUMN_NAME)) methodIndex = i; //flow routing method
if(StringMatch(textColumns[i],RESERVOIR_SEDIMENT_ROUTING_METHOD_COLUMN_NAME)) sedimentMethodIndex = i;//sediment routing method
if(StringMatch(textColumns[i],RESERVOIR_NUTRIENT_ROUTING_METHOD_COLUMN_NAME)) nutrientMethodIndex = i;//nutrient routing method
}
}
delete table;
//get the data for the specific id
ostringstream oss;
oss << "select * from " << parameterTableName << " where id =" << reachStructurId;
string selectSQL = oss.str();
table = db.Load(selectSQL);
if(table->nRows == 0)
{
oss.clear();
oss << "There is not a " << parameterTableName << " whose id is " << reachStructurId << ".";
throw ModelException("BMPReach","loadParameters",oss.str());
}
//get numeric values
m_numericParameterNum = int(numericColumns.size());
if(m_bmpId == BMP_TYPE_RESERVOIR)
{
if(methodIndex == -1) throw ModelException("BMPReach","loadParameters","Can't find flow routing method column in table "+m_parameterTableName+".");
if(sedimentMethodIndex == -1) throw ModelException("BMPReach","loadParameters","Can't find sediment routing method column in table "+m_parameterTableName+".");
if(nutrientMethodIndex == -1) throw ModelException("BMPReach","loadParameters","Can't find nutrient routing method column in table "+m_parameterTableName+".");
m_numericParameterNum+=3;
}
if(m_numericParameterNum>0)
{
int index = 0;
m_numericParameters = new float[m_numericParameterNum];
map<int,bool>::iterator it;
for(it = numericColumns.begin();it!=numericColumns.end();it++)
{
string value = table->FieldValue(1,it->first);
float temp = 0.0f;
if(it->second) //date
{
utils util;
m_operationDate = util.ConvertToTime(value,"%4d-%2d-%2d",false);
temp = float(m_operationDate/86400);
}
else
{
temp = float(atof(value.c_str()));
if(it->first == BMP_REACH_X_INDEX) m_x = temp;
if(it->first == BMP_REACH_Y_INDEX) m_y = temp;
}
m_numericParameters[index] = temp;
index ++;
}
}
//get text values
map<int,string>::iterator it2;
for(it2 = textColumns.begin();it2!=textColumns.end();it2++)
{
m_textParameters[it2->second] = table->FieldValue(1,it2->first);
}
delete table;
db.Close();
}
catch(...)
{
db.Close();
throw;
}
}
void clsSiteData::readSiteBasicInfo(string tableName)
{
string path = m_databasePath + File_HydroClimateDB;
if (!DBManager::IsTableExist(path, "stations"))
throw ModelException("clsSiteData", "readSiteBasicInfo",
"The database " + path +
" dose not exist or the table stations does not exist in this database.");
if (!DBManager::IsTableExist(path, tableName))
throw ModelException("clsSiteData", "readSiteBasicInfo",
"The database " + path +
" dose not exist or the table " + tableName + " does not exist in this database.");
//read data
DBManager dbman;
try
{
// open the hydrclimate database
dbman.Open(path);
// if there is no error
if (!dbman.IsError())
{
// create the SQL query for the stations table
//ID, NAME, XPR, YPR, LAT, LONG, ELEVATION, TYPE, UNITS, AREA, STARTDATE, ENDDATE, INTERVAL, TABLENAME
string strSQL =
"SELECT ID, NAME, XPR, YPR, LAT, LONG, ELEVATION, AREA, TYPE FROM stations WHERE TABLENAME='" +
tableName + "'";
// run the query
slTable *tbl = dbman.Load(strSQL);
// if the query is successful
if (tbl != NULL)
{
// if there is at least one record
if (tbl->nRows > 0)
{
utils util;
// remember row 0 contains the field names not values
m_ID = atoi(tbl->FieldValue(1, 0).c_str());
m_Name = tbl->FieldValue(1, 1).c_str();
m_XPR = (float) atof(tbl->FieldValue(1, 2).c_str());
m_YPR = (float) atof(tbl->FieldValue(1, 3).c_str());
m_Latitude = (float) atof(tbl->FieldValue(1, 4).c_str());
m_Longitude = (float) atof(tbl->FieldValue(1, 5).c_str());
m_Elevation = (float) atof(tbl->FieldValue(1, 6).c_str());
m_Area = (float) atof(tbl->FieldValue(1, 7).c_str());
string type = tbl->FieldValue(1, 8);
if (clsHydroClimateData::IsHydroClimateDataType(&type))
{
m_timeSerieseData[type] = new clsHydroClimateData(m_databasePath, tableName);
}
else
{
throw ModelException("clsSiteData", "readSiteBasicInfo",
"The data type for 'station:" + m_Name + ",tableName:" + tableName +
"' is not correct.");
}
}
else
throw ModelException("clsSiteData", "readSiteBasicInfo",
"There is no row whose table name is " + tableName);
delete tbl;
}
tbl = NULL;
}
dbman.Close();
}
catch (...)
{
dbman.Close();
throw;
}
}