Mercurial > hg > nsaunier > traffic-intelligence

comparison python/storage.py @ 736:967d244968a4 dev

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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