Mercurial Hosting > traffic-intelligence
comparison python/storage.py @ 736:967d244968a4 dev
work in progress on saving/loading prototypes
author | Nicolas Saunier <nicolas.saunier@polymtl.ca> |
---|---|
date | Wed, 12 Aug 2015 08:26:59 -0400 |
parents | 49e99ca34a7d |
children | 867bab9f317a |
comparison
equal
deleted
inserted
replaced
735:0e875a7f5759 | 736:967d244968a4 |
---|---|
107 cursor.execute(query,(trajectory_id,frame_number,position.x,position.y)) | 107 cursor.execute(query,(trajectory_id,frame_number,position.x,position.y)) |
108 | 108 |
109 connection.commit() | 109 connection.commit() |
110 connection.close() | 110 connection.close() |
111 | 111 |
112 def writeFeaturesToSqlite(objects, outputFilename, trajectoryType, objectNumbers = -1): | |
113 '''write features trajectories maintain trajectory ID,velocities dataset ''' | |
114 connection = sqlite3.connect(outputFilename) | |
115 cursor = connection.cursor() | |
116 | |
117 cursor.execute("CREATE TABLE IF NOT EXISTS \"positions\"(trajectory_id INTEGER,frame_number INTEGER, x_coordinate REAL, y_coordinate REAL, PRIMARY KEY(trajectory_id, frame_number))") | |
118 cursor.execute("CREATE TABLE IF NOT EXISTS \"velocities\"(trajectory_id INTEGER,frame_number INTEGER, x_coordinate REAL, y_coordinate REAL, PRIMARY KEY(trajectory_id, frame_number))") | |
119 | |
120 if trajectoryType == 'feature': | |
121 if type(objectNumbers) == int and objectNumbers == -1: | |
122 for trajectory in objects: | |
123 trajectory_id = trajectory.num | |
124 frame_number = trajectory.timeInterval.first | |
125 for position,velocity in zip(trajectory.getPositions(),trajectory.getVelocities()): | |
126 cursor.execute("insert into positions (trajectory_id, frame_number, x_coordinate, y_coordinate) values (?,?,?,?)",(trajectory_id,frame_number,position.x,position.y)) | |
127 cursor.execute("insert into velocities (trajectory_id, frame_number, x_coordinate, y_coordinate) values (?,?,?,?)",(trajectory_id,frame_number,velocity.x,velocity.y)) | |
128 frame_number += 1 | |
129 | |
130 connection.commit() | |
131 connection.close() | |
132 | |
133 def writePrototypesToSqlite(prototypes,nMatching, outputFilename): | |
134 """ prototype dataset is a dictionary with keys== routes, values== prototypes Ids """ | |
135 connection = sqlite3.connect(outputFilename) | |
136 cursor = connection.cursor() | |
137 | |
138 cursor.execute("CREATE TABLE IF NOT EXISTS \"prototypes\"(prototype_id INTEGER,routeIDstart INTEGER,routeIDend INTEGER, nMatching INTEGER, PRIMARY KEY(prototype_id))") | |
139 | |
140 for route in prototypes.keys(): | |
141 if prototypes[route]!=[]: | |
142 for i in prototypes[route]: | |
143 cursor.execute("insert into prototypes (prototype_id, routeIDstart,routeIDend, nMatching) values (?,?,?,?)",(i,route[0],route[1],nMatching[route][i])) | |
144 | |
145 connection.commit() | |
146 connection.close() | |
147 | |
148 def loadPrototypesFromSqlite(filename): | |
149 """ | |
150 This function loads the prototype file in the database | |
151 It returns a dictionary for prototypes for each route and nMatching | |
152 """ | |
153 prototypes = {} | |
154 nMatching={} | |
155 | |
156 connection = sqlite3.connect(filename) | |
157 cursor = connection.cursor() | |
158 | |
159 try: | |
160 cursor.execute('SELECT * from prototypes order by prototype_id, routeIDstart,routeIDend, nMatching') | |
161 except sqlite3.OperationalError as error: | |
162 utils.printDBError(error) | |
163 return [] | |
164 | |
165 for row in cursor: | |
166 route=(row[1],row[2]) | |
167 if route not in prototypes.keys(): | |
168 prototypes[route]=[] | |
169 prototypes[route].append(row[0]) | |
170 nMatching[row[0]]=row[3] | |
171 | |
172 connection.close() | |
173 return prototypes,nMatching | |
174 | |
175 def writeLabelsToSqlite(labels, outputFilename): | |
176 """ labels is a dictionary with keys: routes, values: prototypes Ids | |
177 """ | |
178 connection = sqlite3.connect(outputFilename) | |
179 cursor = connection.cursor() | |
180 | |
181 cursor.execute("CREATE TABLE IF NOT EXISTS \"labels\"(object_id INTEGER,routeIDstart INTEGER,routeIDend INTEGER, prototype_id INTEGER, PRIMARY KEY(object_id))") | |
182 | |
183 for route in labels.keys(): | |
184 if labels[route]!=[]: | |
185 for i in labels[route]: | |
186 for j in labels[route][i]: | |
187 cursor.execute("insert into labels (object_id, routeIDstart,routeIDend, prototype_id) values (?,?,?,?)",(j,route[0],route[1],i)) | |
188 | |
189 connection.commit() | |
190 connection.close() | |
191 | |
192 def loadLabelsFromSqlite(filename): | |
193 labels = {} | |
194 | |
195 connection = sqlite3.connect(filename) | |
196 cursor = connection.cursor() | |
197 | |
198 try: | |
199 cursor.execute('SELECT * from labels order by object_id, routeIDstart,routeIDend, prototype_id') | |
200 except sqlite3.OperationalError as error: | |
201 utils.printDBError(error) | |
202 return [] | |
203 | |
204 for row in cursor: | |
205 route=(row[1],row[2]) | |
206 p=row[3] | |
207 if route not in labels.keys(): | |
208 labels[route]={} | |
209 if p not in labels[route].keys(): | |
210 labels[route][p]=[] | |
211 labels[route][p].append(row[0]) | |
212 | |
213 connection.close() | |
214 return labels | |
215 def writeSpeedPrototypeToSqlite(prototypes,nmatching, outFilename): | |
216 """ to match the format of second layer prototypes""" | |
217 connection = sqlite3.connect(outFilename) | |
218 cursor = connection.cursor() | |
219 | |
220 cursor.execute("CREATE TABLE IF NOT EXISTS \"speedprototypes\"(spdprototype_id INTEGER,prototype_id INTEGER,routeID_start INTEGER, routeID_end INTEGER, nMatching INTEGER, PRIMARY KEY(spdprototype_id))") | |
221 | |
222 for route in prototypes.keys(): | |
223 if prototypes[route]!={}: | |
224 for i in prototypes[route]: | |
225 if prototypes[route][i]!= []: | |
226 for j in prototypes[route][i]: | |
227 cursor.execute("insert into speedprototypes (spdprototype_id,prototype_id, routeID_start, routeID_end, nMatching) values (?,?,?,?,?)",(j,i,route[0],route[1],nmatching[j])) | |
228 | |
229 connection.commit() | |
230 connection.close() | |
231 | |
232 def loadSpeedPrototypeFromSqlite(filename): | |
233 """ | |
234 This function loads the prototypes table in the database of name <filename>. | |
235 """ | |
236 prototypes = {} | |
237 nMatching={} | |
238 connection = sqlite3.connect(filename) | |
239 cursor = connection.cursor() | |
240 | |
241 try: | |
242 cursor.execute('SELECT * from speedprototypes order by spdprototype_id,prototype_id, routeID_start, routeID_end, nMatching') | |
243 except sqlite3.OperationalError as error: | |
244 utils.printDBError(error) | |
245 return [] | |
246 | |
247 for row in cursor: | |
248 route=(row[2],row[3]) | |
249 if route not in prototypes.keys(): | |
250 prototypes[route]={} | |
251 if row[1] not in prototypes[route].keys(): | |
252 prototypes[route][row[1]]=[] | |
253 prototypes[route][row[1]].append(row[0]) | |
254 nMatching[row[0]]=row[4] | |
255 | |
256 connection.close() | |
257 return prototypes,nMatching | |
258 | |
259 | |
260 def writeRoutesToSqlite(Routes, outputFilename): | |
261 """ This function writes the activity path define by start and end IDs""" | |
262 connection = sqlite3.connect(outputFilename) | |
263 cursor = connection.cursor() | |
264 | |
265 cursor.execute("CREATE TABLE IF NOT EXISTS \"routes\"(object_id INTEGER,routeIDstart INTEGER,routeIDend INTEGER, PRIMARY KEY(object_id))") | |
266 | |
267 for route in Routes.keys(): | |
268 if Routes[route]!=[]: | |
269 for i in Routes[route]: | |
270 cursor.execute("insert into routes (object_id, routeIDstart,routeIDend) values (?,?,?)",(i,route[0],route[1])) | |
271 | |
272 connection.commit() | |
273 connection.close() | |
274 | |
275 def loadRoutesFromSqlite(filename): | |
276 Routes = {} | |
277 | |
278 connection = sqlite3.connect(filename) | |
279 cursor = connection.cursor() | |
280 | |
281 try: | |
282 cursor.execute('SELECT * from routes order by object_id, routeIDstart,routeIDend') | |
283 except sqlite3.OperationalError as error: | |
284 utils.printDBError(error) | |
285 return [] | |
286 | |
287 for row in cursor: | |
288 route=(row[1],row[2]) | |
289 if route not in Routes.keys(): | |
290 Routes[route]=[] | |
291 Routes[route].append(row[0]) | |
292 | |
293 connection.close() | |
294 return Routes | |
295 | |
296 def setRoutes(filename, objects): | |
297 connection = sqlite3.connect(filename) | |
298 cursor = connection.cursor() | |
299 for obj in objects: | |
300 cursor.execute('update objects set startRouteID = {} where object_id = {}'.format(obj.startRouteID, obj.getNum())) | |
301 cursor.execute('update objects set endRouteID = {} where object_id = {}'.format(obj.endRouteID, obj.getNum())) | |
302 connection.commit() | |
303 connection.close() | |
304 | |
305 def setRoadUserTypes(filename, objects): | |
306 '''Saves the user types of the objects in the sqlite database stored in filename | |
307 The objects should exist in the objects table''' | |
308 connection = sqlite3.connect(filename) | |
309 cursor = connection.cursor() | |
310 for obj in objects: | |
311 cursor.execute('update objects set road_user_type = {} where object_id = {}'.format(obj.getUserType(), obj.getNum())) | |
312 connection.commit() | |
313 connection.close() | |
314 | 112 |
315 def loadPrototypeMatchIndexesFromSqlite(filename): | 113 def loadPrototypeMatchIndexesFromSqlite(filename): |
316 """ | 114 """ |
317 This function loads the prototypes table in the database of name <filename>. | 115 This function loads the prototypes table in the database of name <filename>. |
318 It returns a list of tuples representing matching ids : [(prototype_id, matched_trajectory_id),...] | 116 It returns a list of tuples representing matching ids : [(prototype_id, matched_trajectory_id),...] |
475 objects = [] | 273 objects = [] |
476 | 274 |
477 connection.close() | 275 connection.close() |
478 return objects | 276 return objects |
479 | 277 |
278 def savePrototypesToSqlite(filename, prototypes, trajectoryType = 'feature'): | |
279 'Work in progress, do not use' | |
280 connection = sqlite3.connect(filename) | |
281 cursor = connection.cursor() | |
282 try: | |
283 cursor.execute('CREATE TABLE IF NOT EXISTS prototypes (id INTEGER PRIMARY KEY, object_id INTEGER, trajectory_id INTEGER, nMatchings INTEGER, FOREIGN KEY(object_id) REFERENCES objects(id), FOREIGN KEY(trajectory_id) REFERENCES positions(trajectory_id))') | |
284 #for inter in interactions: | |
285 # saveInteraction(cursor, inter) | |
286 except sqlite3.OperationalError as error: | |
287 printDBError(error) | |
288 connection.commit() | |
289 connection.close() | |
290 | |
291 def loadPrototypesFromSqlite(filename): | |
292 pass | |
293 | |
480 def loadGroundTruthFromSqlite(filename, gtType = 'bb', gtNumbers = None): | 294 def loadGroundTruthFromSqlite(filename, gtType = 'bb', gtNumbers = None): |
481 'Loads bounding box annotations (ground truth) from an SQLite ' | 295 'Loads bounding box annotations (ground truth) from an SQLite ' |
482 connection = sqlite3.connect(filename) | 296 connection = sqlite3.connect(filename) |
483 gt = [] | 297 gt = [] |
484 | 298 |
627 except sqlite3.OperationalError as error: | 441 except sqlite3.OperationalError as error: |
628 printDBError(error) | 442 printDBError(error) |
629 return boundingBoxes | 443 return boundingBoxes |
630 connection.close() | 444 connection.close() |
631 return boundingBoxes | 445 return boundingBoxes |
446 | |
447 ######################### | |
448 # saving and loading for scene interpretation (Mohamed Gomaa Mohamed's PhD) | |
449 ######################### | |
450 | |
451 def writeFeaturesToSqlite(objects, outputFilename, trajectoryType, objectNumbers = -1): | |
452 '''write features trajectories maintain trajectory ID,velocities dataset ''' | |
453 connection = sqlite3.connect(outputFilename) | |
454 cursor = connection.cursor() | |
455 | |
456 cursor.execute("CREATE TABLE IF NOT EXISTS \"positions\"(trajectory_id INTEGER,frame_number INTEGER, x_coordinate REAL, y_coordinate REAL, PRIMARY KEY(trajectory_id, frame_number))") | |
457 cursor.execute("CREATE TABLE IF NOT EXISTS \"velocities\"(trajectory_id INTEGER,frame_number INTEGER, x_coordinate REAL, y_coordinate REAL, PRIMARY KEY(trajectory_id, frame_number))") | |
458 | |
459 if trajectoryType == 'feature': | |
460 if type(objectNumbers) == int and objectNumbers == -1: | |
461 for trajectory in objects: | |
462 trajectory_id = trajectory.num | |
463 frame_number = trajectory.timeInterval.first | |
464 for position,velocity in zip(trajectory.getPositions(),trajectory.getVelocities()): | |
465 cursor.execute("insert into positions (trajectory_id, frame_number, x_coordinate, y_coordinate) values (?,?,?,?)",(trajectory_id,frame_number,position.x,position.y)) | |
466 cursor.execute("insert into velocities (trajectory_id, frame_number, x_coordinate, y_coordinate) values (?,?,?,?)",(trajectory_id,frame_number,velocity.x,velocity.y)) | |
467 frame_number += 1 | |
468 | |
469 connection.commit() | |
470 connection.close() | |
471 | |
472 def writePrototypesToSqlite(prototypes,nMatching, outputFilename): | |
473 """ prototype dataset is a dictionary with keys== routes, values== prototypes Ids """ | |
474 connection = sqlite3.connect(outputFilename) | |
475 cursor = connection.cursor() | |
476 | |
477 cursor.execute("CREATE TABLE IF NOT EXISTS \"prototypes\"(prototype_id INTEGER,routeIDstart INTEGER,routeIDend INTEGER, nMatching INTEGER, PRIMARY KEY(prototype_id))") | |
478 | |
479 for route in prototypes.keys(): | |
480 if prototypes[route]!=[]: | |
481 for i in prototypes[route]: | |
482 cursor.execute("insert into prototypes (prototype_id, routeIDstart,routeIDend, nMatching) values (?,?,?,?)",(i,route[0],route[1],nMatching[route][i])) | |
483 | |
484 connection.commit() | |
485 connection.close() | |
486 | |
487 def readPrototypesFromSqlite(filename): | |
488 """ | |
489 This function loads the prototype file in the database | |
490 It returns a dictionary for prototypes for each route and nMatching | |
491 """ | |
492 prototypes = {} | |
493 nMatching={} | |
494 | |
495 connection = sqlite3.connect(filename) | |
496 cursor = connection.cursor() | |
497 | |
498 try: | |
499 cursor.execute('SELECT * from prototypes order by prototype_id, routeIDstart,routeIDend, nMatching') | |
500 except sqlite3.OperationalError as error: | |
501 utils.printDBError(error) | |
502 return [] | |
503 | |
504 for row in cursor: | |
505 route=(row[1],row[2]) | |
506 if route not in prototypes.keys(): | |
507 prototypes[route]=[] | |
508 prototypes[route].append(row[0]) | |
509 nMatching[row[0]]=row[3] | |
510 | |
511 connection.close() | |
512 return prototypes,nMatching | |
513 | |
514 def writeLabelsToSqlite(labels, outputFilename): | |
515 """ labels is a dictionary with keys: routes, values: prototypes Ids | |
516 """ | |
517 connection = sqlite3.connect(outputFilename) | |
518 cursor = connection.cursor() | |
519 | |
520 cursor.execute("CREATE TABLE IF NOT EXISTS \"labels\"(object_id INTEGER,routeIDstart INTEGER,routeIDend INTEGER, prototype_id INTEGER, PRIMARY KEY(object_id))") | |
521 | |
522 for route in labels.keys(): | |
523 if labels[route]!=[]: | |
524 for i in labels[route]: | |
525 for j in labels[route][i]: | |
526 cursor.execute("insert into labels (object_id, routeIDstart,routeIDend, prototype_id) values (?,?,?,?)",(j,route[0],route[1],i)) | |
527 | |
528 connection.commit() | |
529 connection.close() | |
530 | |
531 def loadLabelsFromSqlite(filename): | |
532 labels = {} | |
533 | |
534 connection = sqlite3.connect(filename) | |
535 cursor = connection.cursor() | |
536 | |
537 try: | |
538 cursor.execute('SELECT * from labels order by object_id, routeIDstart,routeIDend, prototype_id') | |
539 except sqlite3.OperationalError as error: | |
540 utils.printDBError(error) | |
541 return [] | |
542 | |
543 for row in cursor: | |
544 route=(row[1],row[2]) | |
545 p=row[3] | |
546 if route not in labels.keys(): | |
547 labels[route]={} | |
548 if p not in labels[route].keys(): | |
549 labels[route][p]=[] | |
550 labels[route][p].append(row[0]) | |
551 | |
552 connection.close() | |
553 return labels | |
554 | |
555 def writeSpeedPrototypeToSqlite(prototypes,nmatching, outFilename): | |
556 """ to match the format of second layer prototypes""" | |
557 connection = sqlite3.connect(outFilename) | |
558 cursor = connection.cursor() | |
559 | |
560 cursor.execute("CREATE TABLE IF NOT EXISTS \"speedprototypes\"(spdprototype_id INTEGER,prototype_id INTEGER,routeID_start INTEGER, routeID_end INTEGER, nMatching INTEGER, PRIMARY KEY(spdprototype_id))") | |
561 | |
562 for route in prototypes.keys(): | |
563 if prototypes[route]!={}: | |
564 for i in prototypes[route]: | |
565 if prototypes[route][i]!= []: | |
566 for j in prototypes[route][i]: | |
567 cursor.execute("insert into speedprototypes (spdprototype_id,prototype_id, routeID_start, routeID_end, nMatching) values (?,?,?,?,?)",(j,i,route[0],route[1],nmatching[j])) | |
568 | |
569 connection.commit() | |
570 connection.close() | |
571 | |
572 def loadSpeedPrototypeFromSqlite(filename): | |
573 """ | |
574 This function loads the prototypes table in the database of name <filename>. | |
575 """ | |
576 prototypes = {} | |
577 nMatching={} | |
578 connection = sqlite3.connect(filename) | |
579 cursor = connection.cursor() | |
580 | |
581 try: | |
582 cursor.execute('SELECT * from speedprototypes order by spdprototype_id,prototype_id, routeID_start, routeID_end, nMatching') | |
583 except sqlite3.OperationalError as error: | |
584 utils.printDBError(error) | |
585 return [] | |
586 | |
587 for row in cursor: | |
588 route=(row[2],row[3]) | |
589 if route not in prototypes.keys(): | |
590 prototypes[route]={} | |
591 if row[1] not in prototypes[route].keys(): | |
592 prototypes[route][row[1]]=[] | |
593 prototypes[route][row[1]].append(row[0]) | |
594 nMatching[row[0]]=row[4] | |
595 | |
596 connection.close() | |
597 return prototypes,nMatching | |
598 | |
599 | |
600 def writeRoutesToSqlite(Routes, outputFilename): | |
601 """ This function writes the activity path define by start and end IDs""" | |
602 connection = sqlite3.connect(outputFilename) | |
603 cursor = connection.cursor() | |
604 | |
605 cursor.execute("CREATE TABLE IF NOT EXISTS \"routes\"(object_id INTEGER,routeIDstart INTEGER,routeIDend INTEGER, PRIMARY KEY(object_id))") | |
606 | |
607 for route in Routes.keys(): | |
608 if Routes[route]!=[]: | |
609 for i in Routes[route]: | |
610 cursor.execute("insert into routes (object_id, routeIDstart,routeIDend) values (?,?,?)",(i,route[0],route[1])) | |
611 | |
612 connection.commit() | |
613 connection.close() | |
614 | |
615 def loadRoutesFromSqlite(filename): | |
616 Routes = {} | |
617 | |
618 connection = sqlite3.connect(filename) | |
619 cursor = connection.cursor() | |
620 | |
621 try: | |
622 cursor.execute('SELECT * from routes order by object_id, routeIDstart,routeIDend') | |
623 except sqlite3.OperationalError as error: | |
624 utils.printDBError(error) | |
625 return [] | |
626 | |
627 for row in cursor: | |
628 route=(row[1],row[2]) | |
629 if route not in Routes.keys(): | |
630 Routes[route]=[] | |
631 Routes[route].append(row[0]) | |
632 | |
633 connection.close() | |
634 return Routes | |
635 | |
636 def setRoutes(filename, objects): | |
637 connection = sqlite3.connect(filename) | |
638 cursor = connection.cursor() | |
639 for obj in objects: | |
640 cursor.execute('update objects set startRouteID = {} where object_id = {}'.format(obj.startRouteID, obj.getNum())) | |
641 cursor.execute('update objects set endRouteID = {} where object_id = {}'.format(obj.endRouteID, obj.getNum())) | |
642 connection.commit() | |
643 connection.close() | |
644 | |
645 def setRoadUserTypes(filename, objects): | |
646 '''Saves the user types of the objects in the sqlite database stored in filename | |
647 The objects should exist in the objects table''' | |
648 connection = sqlite3.connect(filename) | |
649 cursor = connection.cursor() | |
650 for obj in objects: | |
651 cursor.execute('update objects set road_user_type = {} where object_id = {}'.format(obj.getUserType(), obj.getNum())) | |
652 connection.commit() | |
653 connection.close() | |
632 | 654 |
633 ######################### | 655 ######################### |
634 # txt files | 656 # txt files |
635 ######################### | 657 ######################### |
636 | 658 |