//! 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(); }
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; } }
//! 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; } }