Mercurial > hg > nsaunier > traffic-intelligence

comparison python/storage.py @ 795:a34ec862371f

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
merged with dev branch
author Nicolas Saunier <nicolas.saunier@polymtl.ca>
date Mon, 09 May 2016 15:33:11 -0400
parents 30bd0f2223b7
children 180b6b0231c0
comparison
equal deleted inserted replaced
758:0a05883216cf 795:a34ec862371f
86 else: 86 else:
87 print("Argument minmax unknown: {}".format(minmax)) 87 print("Argument minmax unknown: {}".format(minmax))
88 return cursor.fetchone()[0] 88 return cursor.fetchone()[0]
89 except sqlite3.OperationalError as error: 89 except sqlite3.OperationalError as error:
90 printDBError(error) 90 printDBError(error)
91
92 # TODO: add test if database connection is open
93 # IO to sqlite
94 def writeTrajectoriesToSqlite(objects, outputFilename, trajectoryType, objectNumbers = -1):
95 """
96 This function writers trajectories to a specified sqlite file
97 @param[in] objects -> a list of trajectories
98 @param[in] trajectoryType -
99 @param[out] outputFilename -> the .sqlite file containting the written objects
100 @param[in] objectNumber : number of objects loaded
101 """
102 connection = sqlite3.connect(outputFilename)
103 cursor = connection.cursor()
104
105 schema = "CREATE TABLE IF NOT EXISTS \"positions\"(trajectory_id INTEGER,frame_number INTEGER, x_coordinate REAL, y_coordinate REAL, PRIMARY KEY(trajectory_id, frame_number))"
106 cursor.execute(schema)
107
108 trajectory_id = 0
109 frame_number = 0
110 if trajectoryType == 'feature':
111 if type(objectNumbers) == int and objectNumbers == -1:
112 for trajectory in objects:
113 trajectory_id += 1
114 frame_number = 0
115 for position in trajectory.getPositions():
116 frame_number += 1
117 query = "insert into positions (trajectory_id, frame_number, x_coordinate, y_coordinate) values (?,?,?,?)"
118 cursor.execute(query,(trajectory_id,frame_number,position.x,position.y))
119
120 connection.commit()
121 connection.close()
122
123 91
124 def loadPrototypeMatchIndexesFromSqlite(filename): 92 def loadPrototypeMatchIndexesFromSqlite(filename):
125 """ 93 """
126 This function loads the prototypes table in the database of name <filename>. 94 This function loads the prototypes table in the database of name <filename>.
127 It returns a list of tuples representing matching ids : [(prototype_id, matched_trajectory_id),...] 95 It returns a list of tuples representing matching ids : [(prototype_id, matched_trajectory_id),...]
183 queryStatement = 'SELECT object_id, frame_number, x_'+corner+', y_'+corner+' FROM '+tableName 151 queryStatement = 'SELECT object_id, frame_number, x_'+corner+', y_'+corner+' FROM '+tableName
184 if objectNumbers is not None: 152 if objectNumbers is not None:
185 queryStatement += ' WHERE object_id '+objectCriteria 153 queryStatement += ' WHERE object_id '+objectCriteria
186 queryStatement += ' ORDER BY object_id, frame_number' 154 queryStatement += ' ORDER BY object_id, frame_number'
187 else: 155 else:
188 print('no trajectory type was chosen') 156 print('Unknown trajectory type {}'.format(trajectoryType))
189 if queryStatement is not None: 157 if queryStatement is not None:
190 cursor.execute(queryStatement) 158 cursor.execute(queryStatement)
191 logging.debug(queryStatement) 159 logging.debug(queryStatement)
192 except sqlite3.OperationalError as error: 160 except sqlite3.OperationalError as error:
193 printDBError(error) 161 printDBError(error)
225 for row in cursor: 193 for row in cursor:
226 userTypes[row[0]] = row[1] 194 userTypes[row[0]] = row[1]
227 return userTypes 195 return userTypes
228 196
229 def loadTrajectoriesFromSqlite(filename, trajectoryType, objectNumbers = None, withFeatures = False): 197 def loadTrajectoriesFromSqlite(filename, trajectoryType, objectNumbers = None, withFeatures = False):
230 '''Loads the first objectNumbers objects or the indices in objectNumbers from the database''' 198 '''Loads the trajectories (in the general sense,
199 either features, objects (feature groups) or bounding box series)
200 The number loaded is either the first objectNumbers objects,
201 or the indices in objectNumbers from the database'''
231 connection = sqlite3.connect(filename) 202 connection = sqlite3.connect(filename)
232 203
233 objects = loadTrajectoriesFromTable(connection, 'positions', trajectoryType, objectNumbers) 204 objects = loadTrajectoriesFromTable(connection, 'positions', trajectoryType, objectNumbers)
234 objectVelocities = loadTrajectoriesFromTable(connection, 'velocities', trajectoryType, objectNumbers) 205 objectVelocities = loadTrajectoriesFromTable(connection, 'velocities', trajectoryType, objectNumbers)
235 206
282 objects = [] 253 objects = []
283 254
284 connection.close() 255 connection.close()
285 return objects 256 return objects
286 257
258 def addCurvilinearTrajectoriesFromSqlite(filename, objects):
259 '''Adds curvilinear positions (s_coordinate, y_coordinate, lane)
260 from a database to an existing MovingObject dict (indexed by each objects's num)'''
261 connection = sqlite3.connect(filename)
262 cursor = connection.cursor()
263
264 try:
265 cursor.execute('SELECT * from curvilinear_positions order by trajectory_id, frame_number')
266 except sqlite3.OperationalError as error:
267 printDBError(error)
268 return []
269
270 missingObjectNumbers = []
271 objNum = None
272 for row in cursor:
273 if objNum != row[0]:
274 objNum = row[0]
275 if objNum in objects:
276 objects[objNum].curvilinearPositions = moving.CurvilinearTrajectory()
277 else:
278 missingObjectNumbers.append(objNum)
279 if objNum in objects:
280 objects[objNum].curvilinearPositions.addPositionSYL(row[2],row[3],row[4])
281 if len(missingObjectNumbers) > 0:
282 print('List of missing objects to attach corresponding curvilinear trajectories: {}'.format(missingObjectNumbers))
283
284 def saveTrajectoriesToSqlite(outputFilename, objects, trajectoryType, withFeatures = False):
285 '''Writes features, ie the trajectories positions (and velocities if exist)
286 with their instants to a specified sqlite file
287 Either feature positions (and velocities if they exist)
288 or curvilinear positions will be saved at a time
289
290 TODO: Not implemented for trajectoryType MovingObject with features
291 For objects, with features will control whether the features
292 corresponding to the object are also saved'''
293
294 connection = sqlite3.connect(outputFilename)
295 try:
296 cursor = connection.cursor()
297
298 if trajectoryType == 'feature':
299 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))")
300 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))")
301
302 positionQuery = "insert into positions (trajectory_id, frame_number, x_coordinate, y_coordinate) values (?,?,?,?)"
303 velocityQuery = "insert into velocities (trajectory_id, frame_number, x_coordinate, y_coordinate) values (?,?,?,?)"
304 for obj in objects:
305 num = obj.getNum()
306 frame_number = obj.getFirstInstant()
307 for position in obj.getPositions():
308 cursor.execute(positionQuery, (num, frame_number, position.x, position.y))
309 frame_number += 1
310 # velocities
311 velocities = obj.getVelocities()
312 if velocities is not None:
313 frame_number = obj.getFirstInstant()
314 for i in xrange(velocities.length()-1):
315 v = velocities[i]
316 cursor.execute(velocityQuery, (num, frame_number, v.x, v.y))
317 frame_number += 1
318 elif trajectoryType == 'curvilinear':
319 cursor.execute("CREATE TABLE IF NOT EXISTS curvilinear_positions (trajectory_id INTEGER, frame_number INTEGER, s_coordinate REAL, y_coordinate REAL, lane TEXT, PRIMARY KEY(trajectory_id, frame_number))")
320 curvilinearQuery = "insert into curvilinear_positions (trajectory_id, frame_number, s_coordinate, y_coordinate, lane) values (?,?,?,?,?)"
321 for obj in objects:
322 num = obj.getNum()
323 frame_number = obj.getFirstInstant()
324 for position in obj.getCurvilinearPositions():
325 cursor.execute(curvilinearQuery, (num, frame_number, position[0], position[1], position[2]))
326 frame_number += 1
327 #elif trajectoryType == 'object':
328 else:
329 print('Unknown trajectory type {}'.format(trajectoryType))
330 connection.commit()
331 except sqlite3.OperationalError as error:
332 printDBError(error)
333 connection.close()
334
287 def savePrototypesToSqlite(filename, prototypes, trajectoryType = 'feature'): 335 def savePrototypesToSqlite(filename, prototypes, trajectoryType = 'feature'):
288 'Work in progress, do not use' 336 'Work in progress, do not use'
289 connection = sqlite3.connect(filename) 337 connection = sqlite3.connect(filename)
290 cursor = connection.cursor() 338 cursor = connection.cursor()
291 try: 339 try:
298 connection.close() 346 connection.close()
299 347
300 def loadPrototypesFromSqlite(filename): 348 def loadPrototypesFromSqlite(filename):
301 pass 349 pass
302 350
303 def loadGroundTruthFromSqlite(filename, gtType = 'bb', gtNumbers = None): 351 def loadBBMovingObjectsFromSqlite(filename, objectType = 'bb', objectNumbers = None):
304 'Loads bounding box annotations (ground truth) from an SQLite ' 352 '''Loads bounding box moving object from an SQLite
305 connection = sqlite3.connect(filename) 353 (format of SQLite output by the ground truth annotation tool
306 gt = [] 354 or Urban Tracker
307 355
308 if gtType == 'bb': 356 Load descriptions?'''
309 topCorners = loadTrajectoriesFromTable(connection, 'bounding_boxes', 'bbtop', gtNumbers) 357 connection = sqlite3.connect(filename)
310 bottomCorners = loadTrajectoriesFromTable(connection, 'bounding_boxes', 'bbbottom', gtNumbers) 358 objects = []
311 userTypes = loadUserTypesFromTable(connection.cursor(), 'object', gtNumbers) # string format is same as object 359
360 if objectType == 'bb':
361 topCorners = loadTrajectoriesFromTable(connection, 'bounding_boxes', 'bbtop', objectNumbers)
362 bottomCorners = loadTrajectoriesFromTable(connection, 'bounding_boxes', 'bbbottom', objectNumbers)
363 userTypes = loadUserTypesFromTable(connection.cursor(), 'object', objectNumbers) # string format is same as object
312 364
313 for t, b in zip(topCorners, bottomCorners): 365 for t, b in zip(topCorners, bottomCorners):
314 num = t.getNum() 366 num = t.getNum()
315 if t.getNum() == b.getNum(): 367 if t.getNum() == b.getNum():
316 annotation = moving.BBAnnotation(num, t.getTimeInterval(), t, b, userTypes[num]) 368 annotation = moving.BBMovingObject(num, t.getTimeInterval(), t, b, userTypes[num])
317 gt.append(annotation) 369 objects.append(annotation)
318 else: 370 else:
319 print ('Unknown type of annotation {}'.format(gtType)) 371 print ('Unknown type of bounding box {}'.format(objectType))
320 372
321 connection.close() 373 connection.close()
322 return gt 374 return objects
323 375
324 def deleteFromSqlite(filename, dataType): 376 def deleteFromSqlite(filename, dataType):
325 'Deletes (drops) some tables in the filename depending on type of data' 377 'Deletes (drops) some tables in the filename depending on type of data'
326 import os 378 import os
327 if os.path.isfile(filename): 379 if os.path.isfile(filename):
440 connection = sqlite3.connect(filename) 492 connection = sqlite3.connect(filename)
441 cursor = connection.cursor() 493 cursor = connection.cursor()
442 boundingBoxes = {} # list of bounding boxes for each instant 494 boundingBoxes = {} # list of bounding boxes for each instant
443 try: 495 try:
444 cursor.execute('SELECT name FROM sqlite_master WHERE type=\'table\' AND name=\'bounding_boxes\'') 496 cursor.execute('SELECT name FROM sqlite_master WHERE type=\'table\' AND name=\'bounding_boxes\'')
445 result = [row for row in cursor] 497 result = cursor.fetchall()
446 if len(result) > 0: 498 if len(result) > 0:
447 cursor.execute('SELECT * FROM bounding_boxes') 499 cursor.execute('SELECT * FROM bounding_boxes')
448 for row in cursor: 500 for row in cursor:
449 boundingBoxes.setdefault(row[1], []).append([moving.Point(row[2], row[3]), moving.Point(row[4], row[5])]) 501 boundingBoxes.setdefault(row[1], []).append([moving.Point(row[2], row[3]), moving.Point(row[4], row[5])])
450 except sqlite3.OperationalError as error: 502 except sqlite3.OperationalError as error:
455 507
456 ######################### 508 #########################
457 # saving and loading for scene interpretation (Mohamed Gomaa Mohamed's PhD) 509 # saving and loading for scene interpretation (Mohamed Gomaa Mohamed's PhD)
458 ######################### 510 #########################
459 511
460 def writeFeaturesToSqlite(objects, outputFilename, trajectoryType, objectNumbers = -1):
461 '''write features trajectories maintain trajectory ID,velocities dataset '''
462 connection = sqlite3.connect(outputFilename)
463 cursor = connection.cursor()
464
465 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))")
466 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))")
467
468 if trajectoryType == 'feature':
469 if type(objectNumbers) == int and objectNumbers == -1:
470 for trajectory in objects:
471 trajectory_id = trajectory.num
472 frame_number = trajectory.timeInterval.first
473 for position,velocity in zip(trajectory.getPositions(),trajectory.getVelocities()):
474 cursor.execute("insert into positions (trajectory_id, frame_number, x_coordinate, y_coordinate) values (?,?,?,?)",(trajectory_id,frame_number,position.x,position.y))
475 cursor.execute("insert into velocities (trajectory_id, frame_number, x_coordinate, y_coordinate) values (?,?,?,?)",(trajectory_id,frame_number,velocity.x,velocity.y))
476 frame_number += 1
477
478 connection.commit()
479 connection.close()
480
481 def writePrototypesToSqlite(prototypes,nMatching, outputFilename): 512 def writePrototypesToSqlite(prototypes,nMatching, outputFilename):
482 """ prototype dataset is a dictionary with keys== routes, values== prototypes Ids """ 513 """ prototype dataset is a dictionary with keys== routes, values== prototypes Ids """
483 connection = sqlite3.connect(outputFilename) 514 connection = sqlite3.connect(outputFilename)
484 cursor = connection.cursor() 515 cursor = connection.cursor()
485 516
486 cursor.execute("CREATE TABLE IF NOT EXISTS \"prototypes\"(prototype_id INTEGER,routeIDstart INTEGER,routeIDend INTEGER, nMatching INTEGER, PRIMARY KEY(prototype_id))") 517 cursor.execute("CREATE TABLE IF NOT EXISTS prototypes (prototype_id INTEGER,routeIDstart INTEGER,routeIDend INTEGER, nMatching INTEGER, PRIMARY KEY(prototype_id))")
487 518
488 for route in prototypes.keys(): 519 for route in prototypes.keys():
489 if prototypes[route]!=[]: 520 if prototypes[route]!=[]:
490 for i in prototypes[route]: 521 for i in prototypes[route]:
491 cursor.execute("insert into prototypes (prototype_id, routeIDstart,routeIDend, nMatching) values (?,?,?,?)",(i,route[0],route[1],nMatching[route][i])) 522 cursor.execute("insert into prototypes (prototype_id, routeIDstart,routeIDend, nMatching) values (?,?,?,?)",(i,route[0],route[1],nMatching[route][i]))
505 cursor = connection.cursor() 536 cursor = connection.cursor()
506 537
507 try: 538 try:
508 cursor.execute('SELECT * from prototypes order by prototype_id, routeIDstart,routeIDend, nMatching') 539 cursor.execute('SELECT * from prototypes order by prototype_id, routeIDstart,routeIDend, nMatching')
509 except sqlite3.OperationalError as error: 540 except sqlite3.OperationalError as error:
510 utils.printDBError(error) 541 printDBError(error)
511 return [] 542 return []
512 543
513 for row in cursor: 544 for row in cursor:
514 route=(row[1],row[2]) 545 route=(row[1],row[2])
515 if route not in prototypes.keys(): 546 if route not in prototypes.keys():
524 """ labels is a dictionary with keys: routes, values: prototypes Ids 555 """ labels is a dictionary with keys: routes, values: prototypes Ids
525 """ 556 """
526 connection = sqlite3.connect(outputFilename) 557 connection = sqlite3.connect(outputFilename)
527 cursor = connection.cursor() 558 cursor = connection.cursor()
528 559
529 cursor.execute("CREATE TABLE IF NOT EXISTS \"labels\"(object_id INTEGER,routeIDstart INTEGER,routeIDend INTEGER, prototype_id INTEGER, PRIMARY KEY(object_id))") 560 cursor.execute("CREATE TABLE IF NOT EXISTS labels (object_id INTEGER,routeIDstart INTEGER,routeIDend INTEGER, prototype_id INTEGER, PRIMARY KEY(object_id))")
530 561
531 for route in labels.keys(): 562 for route in labels.keys():
532 if labels[route]!=[]: 563 if labels[route]!=[]:
533 for i in labels[route]: 564 for i in labels[route]:
534 for j in labels[route][i]: 565 for j in labels[route][i]:
544 cursor = connection.cursor() 575 cursor = connection.cursor()
545 576
546 try: 577 try:
547 cursor.execute('SELECT * from labels order by object_id, routeIDstart,routeIDend, prototype_id') 578 cursor.execute('SELECT * from labels order by object_id, routeIDstart,routeIDend, prototype_id')
548 except sqlite3.OperationalError as error: 579 except sqlite3.OperationalError as error:
549 utils.printDBError(error) 580 printDBError(error)
550 return [] 581 return []
551 582
552 for row in cursor: 583 for row in cursor:
553 route=(row[1],row[2]) 584 route=(row[1],row[2])
554 p=row[3] 585 p=row[3]
564 def writeSpeedPrototypeToSqlite(prototypes,nmatching, outFilename): 595 def writeSpeedPrototypeToSqlite(prototypes,nmatching, outFilename):
565 """ to match the format of second layer prototypes""" 596 """ to match the format of second layer prototypes"""
566 connection = sqlite3.connect(outFilename) 597 connection = sqlite3.connect(outFilename)
567 cursor = connection.cursor() 598 cursor = connection.cursor()
568 599
569 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))") 600 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))")
570 601
571 for route in prototypes.keys(): 602 for route in prototypes.keys():
572 if prototypes[route]!={}: 603 if prototypes[route]!={}:
573 for i in prototypes[route]: 604 for i in prototypes[route]:
574 if prototypes[route][i]!= []: 605 if prototypes[route][i]!= []:
588 cursor = connection.cursor() 619 cursor = connection.cursor()
589 620
590 try: 621 try:
591 cursor.execute('SELECT * from speedprototypes order by spdprototype_id,prototype_id, routeID_start, routeID_end, nMatching') 622 cursor.execute('SELECT * from speedprototypes order by spdprototype_id,prototype_id, routeID_start, routeID_end, nMatching')
592 except sqlite3.OperationalError as error: 623 except sqlite3.OperationalError as error:
593 utils.printDBError(error) 624 printDBError(error)
594 return [] 625 return []
595 626
596 for row in cursor: 627 for row in cursor:
597 route=(row[2],row[3]) 628 route=(row[2],row[3])
598 if route not in prototypes.keys(): 629 if route not in prototypes.keys():
609 def writeRoutesToSqlite(Routes, outputFilename): 640 def writeRoutesToSqlite(Routes, outputFilename):
610 """ This function writes the activity path define by start and end IDs""" 641 """ This function writes the activity path define by start and end IDs"""
611 connection = sqlite3.connect(outputFilename) 642 connection = sqlite3.connect(outputFilename)
612 cursor = connection.cursor() 643 cursor = connection.cursor()
613 644
614 cursor.execute("CREATE TABLE IF NOT EXISTS \"routes\"(object_id INTEGER,routeIDstart INTEGER,routeIDend INTEGER, PRIMARY KEY(object_id))") 645 cursor.execute("CREATE TABLE IF NOT EXISTS routes (object_id INTEGER,routeIDstart INTEGER,routeIDend INTEGER, PRIMARY KEY(object_id))")
615 646
616 for route in Routes.keys(): 647 for route in Routes.keys():
617 if Routes[route]!=[]: 648 if Routes[route]!=[]:
618 for i in Routes[route]: 649 for i in Routes[route]:
619 cursor.execute("insert into routes (object_id, routeIDstart,routeIDend) values (?,?,?)",(i,route[0],route[1])) 650 cursor.execute("insert into routes (object_id, routeIDstart,routeIDend) values (?,?,?)",(i,route[0],route[1]))
628 cursor = connection.cursor() 659 cursor = connection.cursor()
629 660
630 try: 661 try:
631 cursor.execute('SELECT * from routes order by object_id, routeIDstart,routeIDend') 662 cursor.execute('SELECT * from routes order by object_id, routeIDstart,routeIDend')
632 except sqlite3.OperationalError as error: 663 except sqlite3.OperationalError as error:
633 utils.printDBError(error) 664 printDBError(error)
634 return [] 665 return []
635 666
636 for row in cursor: 667 for row in cursor:
637 route=(row[1],row[2]) 668 route=(row[1],row[2])
638 if route not in Routes.keys(): 669 if route not in Routes.keys():
759 cursor.execute(queryStatement) 790 cursor.execute(queryStatement)
760 return [row[0] for row in cursor] 791 return [row[0] for row in cursor]
761 except sqlite3.OperationalError as error: 792 except sqlite3.OperationalError as error:
762 printDBError(error) 793 printDBError(error)
763 794
795 def getNObjectsInLinkFromVissimFile(filename, linkIds):
796 '''Returns the number of objects that traveled through the link ids'''
797 connection = sqlite3.connect(filename)
798 cursor = connection.cursor()
799 queryStatement = 'SELECT link_id, COUNT(DISTINCT trajectory_id) FROM curvilinear_positions where link_id IN ('+','.join([str(id) for id in linkIds])+') GROUP BY link_id'
800 try:
801 cursor.execute(queryStatement)
802 return {row[0]:row[1] for row in cursor}
803 except sqlite3.OperationalError as error:
804 printDBError(error)
764 805
765 def loadTrajectoriesFromVissimFile(filename, simulationStepsPerTimeUnit, objectNumbers = None, warmUpLastInstant = None, usePandas = False, nDecimals = 2, lowMemory = True): 806 def loadTrajectoriesFromVissimFile(filename, simulationStepsPerTimeUnit, objectNumbers = None, warmUpLastInstant = None, usePandas = False, nDecimals = 2, lowMemory = True):
766 '''Reads data from VISSIM .fzp trajectory file 807 '''Reads data from VISSIM .fzp trajectory file
767 simulationStepsPerTimeUnit is the number of simulation steps per unit of time used by VISSIM (second) 808 simulationStepsPerTimeUnit is the number of simulation steps per unit of time used by VISSIM (second)
768 for example, there seems to be 10 simulation steps per simulated second in VISSIM, 809 for example, there seems to be 10 simulation steps per simulated second in VISSIM,