Mercurial Hosting > traffic-intelligence
comparison 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 |
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_ */ |