Mercurial > hg > nsaunier > traffic-intelligence

comparison trajectorymanagement/src/TrajectoryDBAccessList.h @ 1159:e1e7acef8eab

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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
comparison
equal deleted inserted replaced
1158:7eb972942f22 1159:e1e7acef8eab
1 #ifndef TRAJECTORYDBACCESSLIST_H_
2 #define TRAJECTORYDBACCESSLIST_H_
3
4 #include "TrajectoryDBAccess.h"
5
6 #include <boost/foreach.hpp>
7
8 /**
9 * TrajectoryDBAccessList class.
10 *
11 * The TrajectoryDBAccessList class allows to perform basic operations on a database representing trajectory as a List.
12 */
13 template<typename T>
14 class TrajectoryDBAccessList: public TrajectoryDBAccess<T> {
15 protected:
16 /** Get ids of trajectories starting or ending at frameNum
17 (specific to list version of db) */
18 bool trajectoryIdStartingEndingAt(std::vector<int>& ids, const int& frameNum, const std::string& firstOrLast) {
19 if (!TrajectoryDBAccess<T>::db->isConnected())
20 return false;
21
22 std::stringstream stmtSS;
23 stmtSS << "SELECT trajectory_id from trajectory_instants WHERE "<< firstOrLast <<"_instant=" << frameNum;
24 return TrajectoryDBAccess<T>::db->executeStatementSelectIntegers(ids, stmtSS.str().c_str());
25 }
26
27 public:
28 /** Create single index for a table. */
29 bool createIndex(const std::string& tableName, const std::string& columnName, const bool& unique = false) {
30 if (!TrajectoryDBAccess<T>::db->isConnected())
31 return false;
32
33 std::string stmtStr = "CREATE ";
34 if (unique)
35 stmtStr += "UNIQUE ";
36 stmtStr += "INDEX "+tableName+"_"+columnName+"_index ON "+tableName+"("+columnName+")";
37 return TrajectoryDBAccess<T>::db->executeStatement(stmtStr.c_str());
38 }
39
40 /**
41 * Create a Trajectory table.
42 *
43 * @return information whether the operation was successful
44 */
45 bool createTable(const std::string& tableName = "trajectories")
46 {
47 if (!TrajectoryDBAccess<T>::db->isConnected())
48 {
49 return false;
50 }
51
52 std::string statementString = "create table "+tableName+" ( trajectory_id INTEGER, frame_number INTEGER, x_coordinate REAL, y_coordinate REAL, PRIMARY KEY( trajectory_id, frame_number ) );";
53 //"create table trajectories ( trajectory_id INTEGER, frame_number INTEGER, x_coordinate REAL, y_coordinate REAL, z_coordinate REAL, PRIMARY KEY( trajectory_id, frame_number ) );";
54 bool success = TrajectoryDBAccess<T>::db->executeStatement(statementString.c_str());
55 return success;
56 }
57
58 /** Create view of first and last frame numbers of trajectories.
59 * (specific to list version of db) */
60 bool createViewInstants(const std::string& firstOrLast) {
61 if (!TrajectoryDBAccess<T>::db->isConnected())
62 return false;
63
64 std::string minOrMax = (firstOrLast=="first")?"min":"max";
65 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";
66 bool success = TrajectoryDBAccess<T>::db->executeStatement(stmtStr.c_str());
67 return success;
68 }
69
70 /** Create view or temporary table of first and last instants,
71 as well as length of each trajectory
72 (specific to list version of db) */
73 bool createInstants(const std::string& view) {
74 if (!TrajectoryDBAccess<T>::db->isConnected())
75 return false;
76
77 std::string viewOrTable = (view=="view")?"VIEW":"TEMP TABLE";
78 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";
79 // alternative for trajectory length: SELECT count(*) as length FROM trajectories GROUP BY trajectory_id
80
81 bool success = TrajectoryDBAccess<T>::db->executeStatement(stmtStr.c_str());
82
83 //success = success && createIndex("trajectory_instants", "first_instant");
84 success = success && createIndex("trajectory_instants", "last_instant");
85 success = success && createIndex("trajectory_instants", "trajectory_id", true);
86
87 return success;
88 }
89
90 /** Get ids of trajectories starting at frameNum
91 (specific to list version of db) */
92 bool trajectoryIdStartingAt(std::vector<int>& ids, const int& frameNum) { return trajectoryIdStartingEndingAt(ids, frameNum, "first");}
93 /** Get ids of trajectories ending at frameNum
94 (specific to list version of db) */
95 bool trajectoryIdEndingAt(std::vector<int>& ids, const int& frameNum) { return trajectoryIdStartingEndingAt(ids, frameNum, "last");}
96
97 /** Get ids of trajectories with one instant between first and last instant */
98 bool trajectoryIdInInterval(std::vector<int>& ids, const unsigned int& firstInstant, const unsigned int& lastInstant) {
99 if (!TrajectoryDBAccess<T>::db->isConnected())
100 return false;
101
102 std::stringstream stmtSS;
103 stmtSS << "SELECT trajectory_id from positions WHERE frame_number BETWEEN " << firstInstant << " and " << lastInstant;
104 return TrajectoryDBAccess<T>::db->executeStatementSelectIntegers(ids, stmtSS.str().c_str());
105 }
106
107 /** Returns the maximum trajectory length
108 * \TODO if trajectory_instants does not exist, get it from positions table
109 * \TODO provide a mechanism to handle empty results */
110 bool maxTrajectoryLength(unsigned int& length) {
111 std::string stmtStr = "select max(length) from trajectory_instants";
112 std::vector<int> result;
113 bool success = TrajectoryDBAccess<T>::db->executeStatementSelectIntegers(result, stmtStr.c_str());
114 if (!result.empty())
115 length = result[0];
116 return success;
117 }
118
119
120
121 /**
122 * Read trajectories from a database.
123 *
124 * @param[out] trajectories trajectories
125 * @param[in] limit maximum number of trajectories, which will be read
126 * @param[in] offset offset
127 * @return information whether the operation was successful
128 */
129 bool read(std::vector<std::shared_ptr<Trajectory<T> > > &trajectories, const std::string& tableName = "trajectories")
130 {
131 if (!TrajectoryDBAccess<T>::db->isConnected())
132 {
133 return false;
134 }
135
136 std::string statement = "select * from "+tableName+" order by trajectory_id, frame_number;";
137
138 std::vector<std::vector<std::string> > result;
139 bool success = TrajectoryDBAccess<T>::db->executeStatementGetMatrix(statement.c_str(), result);
140 if (success)
141 {
142 if (trajectories.empty())
143 {
144 trajectories = std::vector<std::shared_ptr<Trajectory<T> > >();
145 }
146
147 bool firstId = true;
148 unsigned int prev = 0;
149 for (unsigned int i = 0; i < result.size(); ++i)
150 {
151 unsigned int trajectoryId = convertString<unsigned int> (result[i][0]);
152
153 std::string s = "";
154 for (unsigned int j = 1; j < result[i].size(); ++j)
155 {
156 s += result[i][j] + " ";
157 }
158
159 std::istringstream is(s);
160 TrajectoryElement<T> t;
161 is >> t;
162
163 if (firstId || trajectoryId != prev)
164 {
165 firstId = false;
166 trajectories.push_back(std::shared_ptr<Trajectory<T> >(new Trajectory<T> ()));
167 trajectories.back()->setId(trajectoryId);
168 prev = trajectoryId;
169 }
170
171 trajectories.back()->add(t);
172 }
173 }
174
175 return success;
176 }
177
178 /**
179 * Read prototypes from a database and matching trajectory Ids.
180 *
181 * @param[out] multimap of prototype ids and trajectory ids
182 * @retur boolean : if the operation was successful or not
183 *
184 */
185 bool read(std::multimap<int,int>& matches, const std::string& tableName = "prototypes") {
186 if (TrajectoryDBAccess<T>::db->isConnected())
187 return false;
188
189 std::string statement = "select * from "+tableName+" order by trajectory_id, trajectory_id_matched;";
190
191 bool success = TrajectoryDBAccess<T>::db->executeStatementSelectPrototypeMatches(matches, statement.c_str());
192 return success;
193 }
194
195 /// Reads trajectory with specific number
196 bool read(std::shared_ptr<Trajectory<cv::Point2f> >& trajectory, const int& trajectoryId, const std::string& tableName = "trajectories") {
197 if (!TrajectoryDBAccess<cv::Point2f>::db->isConnected())
198 return false;
199
200 std::string statement = "select * from "+tableName+" where trajectory_id = " +TrajectoryDBAccess<T>::toString(trajectoryId)+ " order by frame_number;";
201 std::map<int, std::vector<TrajectoryElement<cv::Point2f> > > trajectoryElements;
202 bool success = TrajectoryDBAccess<T>::db->executeStatementSelectTrajectoryElements(trajectoryElements, statement.c_str());
203 if (success) {
204 assert(trajectoryElements.count(trajectoryId) == 1);
205 assert(trajectoryElements.size() == 1);
206 trajectory = std::shared_ptr<Trajectory<cv::Point2f> >(new Trajectory<cv::Point2f>(trajectoryId, trajectoryElements[trajectoryId]));
207 }
208
209 return success;
210 }
211
212 /// Reads trajectory with specific number
213 bool read(std::vector<std::shared_ptr<Trajectory<cv::Point2f> > >& trajectories, const std::vector<int>& trajectoryIds, const std::string& tableName = "trajectories") {
214 if (!TrajectoryDBAccess<cv::Point2f>::db->isConnected())
215 return false;
216 bool success = false;
217 if (!trajectoryIds.empty()) {
218 std::stringstream trajectoryIdsSS;
219 BOOST_FOREACH(int id, trajectoryIds)
220 trajectoryIdsSS << id << ", ";
221 std::string statement = "select * from "+tableName+" where trajectory_id in (" +trajectoryIdsSS.str()+ ") order by frame_number;";
222 std::map<int, std::vector<TrajectoryElement<cv::Point2f> > > trajectoryElements;
223 success = TrajectoryDBAccess<T>::db->executeStatementSelectTrajectoryElements(trajectoryElements, statement.c_str());
224 if (success) {
225 BOOST_FOREACH(int id, trajectoryIds)
226 trajectories.push_back(std::shared_ptr<Trajectory<cv::Point2f> >(new Trajectory<cv::Point2f>(id, trajectoryElements[id])));
227 }
228 }
229 return success;
230 }
231
232 /** Write the trajectory to the database */
233 bool write(const Trajectory<T> &trajectory, const std::string& tableName = "trajectories")
234 {
235 std::string sid = TrajectoryDBAccess<T>::toString(trajectory.getId());
236
237 for (unsigned int i = 0; i < trajectory.size(); ++i)
238 {
239 //std::string stmt = "insert into trajectories (trajectory_id, frame_number, x_coordinate, y_coordinate, z_coordinate) values (";
240 std::string stmt = "insert into "+tableName+" (trajectory_id, frame_number, x_coordinate, y_coordinate) values (";
241 stmt += sid + ", ";
242 std::stringstream ss;
243 T p = trajectory[i];
244 ss << trajectory.getFrameNumber(i) << ", " << p.x << ", " << p.y;
245 stmt += ss.str();
246
247 /* if (dim(trajectory.getPoint(i)) == 2) //for z_coordinate */
248 /* { */
249 /* stmt += ",0"; */
250 /* } */
251
252 stmt += ");";
253
254 bool success = TrajectoryDBAccess<T>::db->executeStatement(stmt.c_str());
255 if (!success)
256 {
257 std::cout << "rollback" << std::endl;
258 success = TrajectoryDBAccess<T>::db->rollback();
259 if (!success)
260 {
261 return false;
262 }
263 }
264 }
265 return true;
266 }
267
268 private:
269 /**
270 * Convert string to variable with a type of \a Tc.
271 *
272 * @param s input parameter
273 * @return output variable
274 */
275 template<typename Tc> Tc convertString(std::string s)
276 {
277 std::istringstream is(s);
278 Tc x;
279 is >> x;
280 return x;
281 }
282
283 /// Returns the name of the view
284 static std::string getViewName(const std::string& firstOrLast) { return "trajectory_"+firstOrLast+"_instants";}
285 };
286
287 #endif /* TRAJECTORYDBACCESSLIST_H_ */