Mercurial Hosting > traffic-intelligence
diff trajectorymanagement/src/TrajectoryDBAccessList.h @ 1159:e1e7acef8eab
moved trajectory management library into Traffic Intelligence
author | Nicolas Saunier <nicolas.saunier@polymtl.ca> |
---|---|
date | Mon, 22 Feb 2021 22:09:35 -0500 |
parents | |
children |
line wrap: on
line diff
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/trajectorymanagement/src/TrajectoryDBAccessList.h Mon Feb 22 22:09:35 2021 -0500 @@ -0,0 +1,287 @@ +#ifndef TRAJECTORYDBACCESSLIST_H_ +#define TRAJECTORYDBACCESSLIST_H_ + +#include "TrajectoryDBAccess.h" + +#include <boost/foreach.hpp> + +/** + * TrajectoryDBAccessList class. + * + * The TrajectoryDBAccessList class allows to perform basic operations on a database representing trajectory as a List. + */ +template<typename T> +class TrajectoryDBAccessList: public TrajectoryDBAccess<T> { + protected: + /** Get ids of trajectories starting or ending at frameNum + (specific to list version of db) */ + bool trajectoryIdStartingEndingAt(std::vector<int>& ids, const int& frameNum, const std::string& firstOrLast) { + if (!TrajectoryDBAccess<T>::db->isConnected()) + return false; + + std::stringstream stmtSS; + stmtSS << "SELECT trajectory_id from trajectory_instants WHERE "<< firstOrLast <<"_instant=" << frameNum; + return TrajectoryDBAccess<T>::db->executeStatementSelectIntegers(ids, stmtSS.str().c_str()); + } + + public: + /** Create single index for a table. */ + bool createIndex(const std::string& tableName, const std::string& columnName, const bool& unique = false) { + if (!TrajectoryDBAccess<T>::db->isConnected()) + return false; + + std::string stmtStr = "CREATE "; + if (unique) + stmtStr += "UNIQUE "; + stmtStr += "INDEX "+tableName+"_"+columnName+"_index ON "+tableName+"("+columnName+")"; + return TrajectoryDBAccess<T>::db->executeStatement(stmtStr.c_str()); + } + + /** + * Create a Trajectory table. + * + * @return information whether the operation was successful + */ + bool createTable(const std::string& tableName = "trajectories") + { + if (!TrajectoryDBAccess<T>::db->isConnected()) + { + return false; + } + + std::string statementString = "create table "+tableName+" ( trajectory_id INTEGER, frame_number INTEGER, x_coordinate REAL, y_coordinate REAL, PRIMARY KEY( trajectory_id, frame_number ) );"; + //"create table trajectories ( trajectory_id INTEGER, frame_number INTEGER, x_coordinate REAL, y_coordinate REAL, z_coordinate REAL, PRIMARY KEY( trajectory_id, frame_number ) );"; + bool success = TrajectoryDBAccess<T>::db->executeStatement(statementString.c_str()); + return success; + } + + /** Create view of first and last frame numbers of trajectories. + * (specific to list version of db) */ + bool createViewInstants(const std::string& firstOrLast) { + if (!TrajectoryDBAccess<T>::db->isConnected()) + return false; + + std::string minOrMax = (firstOrLast=="first")?"min":"max"; + std::string stmtStr = "CREATE VIEW IF NOT EXISTS "+getViewName(firstOrLast)+" AS select trajectory_id, "+minOrMax+"(frame_number) as frame_number from positions group by trajectory_id"; + bool success = TrajectoryDBAccess<T>::db->executeStatement(stmtStr.c_str()); + return success; + } + + /** Create view or temporary table of first and last instants, + as well as length of each trajectory + (specific to list version of db) */ + bool createInstants(const std::string& view) { + if (!TrajectoryDBAccess<T>::db->isConnected()) + return false; + + std::string viewOrTable = (view=="view")?"VIEW":"TEMP TABLE"; + std::string stmtStr = "CREATE "+viewOrTable+" IF NOT EXISTS trajectory_instants AS select trajectory_id, min(frame_number) as first_instant, max(frame_number) as last_instant, max(frame_number)-min(frame_number)+1 as length from positions group by trajectory_id"; + // alternative for trajectory length: SELECT count(*) as length FROM trajectories GROUP BY trajectory_id + + bool success = TrajectoryDBAccess<T>::db->executeStatement(stmtStr.c_str()); + + //success = success && createIndex("trajectory_instants", "first_instant"); + success = success && createIndex("trajectory_instants", "last_instant"); + success = success && createIndex("trajectory_instants", "trajectory_id", true); + + return success; + } + + /** Get ids of trajectories starting at frameNum + (specific to list version of db) */ + bool trajectoryIdStartingAt(std::vector<int>& ids, const int& frameNum) { return trajectoryIdStartingEndingAt(ids, frameNum, "first");} + /** Get ids of trajectories ending at frameNum + (specific to list version of db) */ + bool trajectoryIdEndingAt(std::vector<int>& ids, const int& frameNum) { return trajectoryIdStartingEndingAt(ids, frameNum, "last");} + + /** Get ids of trajectories with one instant between first and last instant */ + bool trajectoryIdInInterval(std::vector<int>& ids, const unsigned int& firstInstant, const unsigned int& lastInstant) { + if (!TrajectoryDBAccess<T>::db->isConnected()) + return false; + + std::stringstream stmtSS; + stmtSS << "SELECT trajectory_id from positions WHERE frame_number BETWEEN " << firstInstant << " and " << lastInstant; + return TrajectoryDBAccess<T>::db->executeStatementSelectIntegers(ids, stmtSS.str().c_str()); + } + + /** Returns the maximum trajectory length + * \TODO if trajectory_instants does not exist, get it from positions table + * \TODO provide a mechanism to handle empty results */ + bool maxTrajectoryLength(unsigned int& length) { + std::string stmtStr = "select max(length) from trajectory_instants"; + std::vector<int> result; + bool success = TrajectoryDBAccess<T>::db->executeStatementSelectIntegers(result, stmtStr.c_str()); + if (!result.empty()) + length = result[0]; + return success; + } + + + + /** + * Read trajectories from a database. + * + * @param[out] trajectories trajectories + * @param[in] limit maximum number of trajectories, which will be read + * @param[in] offset offset + * @return information whether the operation was successful + */ + bool read(std::vector<std::shared_ptr<Trajectory<T> > > &trajectories, const std::string& tableName = "trajectories") + { + if (!TrajectoryDBAccess<T>::db->isConnected()) + { + return false; + } + + std::string statement = "select * from "+tableName+" order by trajectory_id, frame_number;"; + + std::vector<std::vector<std::string> > result; + bool success = TrajectoryDBAccess<T>::db->executeStatementGetMatrix(statement.c_str(), result); + if (success) + { + if (trajectories.empty()) + { + trajectories = std::vector<std::shared_ptr<Trajectory<T> > >(); + } + + bool firstId = true; + unsigned int prev = 0; + for (unsigned int i = 0; i < result.size(); ++i) + { + unsigned int trajectoryId = convertString<unsigned int> (result[i][0]); + + std::string s = ""; + for (unsigned int j = 1; j < result[i].size(); ++j) + { + s += result[i][j] + " "; + } + + std::istringstream is(s); + TrajectoryElement<T> t; + is >> t; + + if (firstId || trajectoryId != prev) + { + firstId = false; + trajectories.push_back(std::shared_ptr<Trajectory<T> >(new Trajectory<T> ())); + trajectories.back()->setId(trajectoryId); + prev = trajectoryId; + } + + trajectories.back()->add(t); + } + } + + return success; + } + + /** + * Read prototypes from a database and matching trajectory Ids. + * + * @param[out] multimap of prototype ids and trajectory ids + * @retur boolean : if the operation was successful or not + * + */ + bool read(std::multimap<int,int>& matches, const std::string& tableName = "prototypes") { + if (TrajectoryDBAccess<T>::db->isConnected()) + return false; + + std::string statement = "select * from "+tableName+" order by trajectory_id, trajectory_id_matched;"; + + bool success = TrajectoryDBAccess<T>::db->executeStatementSelectPrototypeMatches(matches, statement.c_str()); + return success; + } + + /// Reads trajectory with specific number + bool read(std::shared_ptr<Trajectory<cv::Point2f> >& trajectory, const int& trajectoryId, const std::string& tableName = "trajectories") { + if (!TrajectoryDBAccess<cv::Point2f>::db->isConnected()) + return false; + + std::string statement = "select * from "+tableName+" where trajectory_id = " +TrajectoryDBAccess<T>::toString(trajectoryId)+ " order by frame_number;"; + std::map<int, std::vector<TrajectoryElement<cv::Point2f> > > trajectoryElements; + bool success = TrajectoryDBAccess<T>::db->executeStatementSelectTrajectoryElements(trajectoryElements, statement.c_str()); + if (success) { + assert(trajectoryElements.count(trajectoryId) == 1); + assert(trajectoryElements.size() == 1); + trajectory = std::shared_ptr<Trajectory<cv::Point2f> >(new Trajectory<cv::Point2f>(trajectoryId, trajectoryElements[trajectoryId])); + } + + return success; + } + + /// Reads trajectory with specific number + bool read(std::vector<std::shared_ptr<Trajectory<cv::Point2f> > >& trajectories, const std::vector<int>& trajectoryIds, const std::string& tableName = "trajectories") { + if (!TrajectoryDBAccess<cv::Point2f>::db->isConnected()) + return false; + bool success = false; + if (!trajectoryIds.empty()) { + std::stringstream trajectoryIdsSS; + BOOST_FOREACH(int id, trajectoryIds) + trajectoryIdsSS << id << ", "; + std::string statement = "select * from "+tableName+" where trajectory_id in (" +trajectoryIdsSS.str()+ ") order by frame_number;"; + std::map<int, std::vector<TrajectoryElement<cv::Point2f> > > trajectoryElements; + success = TrajectoryDBAccess<T>::db->executeStatementSelectTrajectoryElements(trajectoryElements, statement.c_str()); + if (success) { + BOOST_FOREACH(int id, trajectoryIds) + trajectories.push_back(std::shared_ptr<Trajectory<cv::Point2f> >(new Trajectory<cv::Point2f>(id, trajectoryElements[id]))); + } + } + return success; + } + + /** Write the trajectory to the database */ + bool write(const Trajectory<T> &trajectory, const std::string& tableName = "trajectories") + { + std::string sid = TrajectoryDBAccess<T>::toString(trajectory.getId()); + + for (unsigned int i = 0; i < trajectory.size(); ++i) + { + //std::string stmt = "insert into trajectories (trajectory_id, frame_number, x_coordinate, y_coordinate, z_coordinate) values ("; + std::string stmt = "insert into "+tableName+" (trajectory_id, frame_number, x_coordinate, y_coordinate) values ("; + stmt += sid + ", "; + std::stringstream ss; + T p = trajectory[i]; + ss << trajectory.getFrameNumber(i) << ", " << p.x << ", " << p.y; + stmt += ss.str(); + + /* if (dim(trajectory.getPoint(i)) == 2) //for z_coordinate */ + /* { */ + /* stmt += ",0"; */ + /* } */ + + stmt += ");"; + + bool success = TrajectoryDBAccess<T>::db->executeStatement(stmt.c_str()); + if (!success) + { + std::cout << "rollback" << std::endl; + success = TrajectoryDBAccess<T>::db->rollback(); + if (!success) + { + return false; + } + } + } + return true; + } + +private: + /** + * Convert string to variable with a type of \a Tc. + * + * @param s input parameter + * @return output variable + */ + template<typename Tc> Tc convertString(std::string s) + { + std::istringstream is(s); + Tc x; + is >> x; + return x; + } + + /// Returns the name of the view + static std::string getViewName(const std::string& firstOrLast) { return "trajectory_"+firstOrLast+"_instants";} +}; + +#endif /* TRAJECTORYDBACCESSLIST_H_ */