Mercurial > hg > nsaunier > traffic-intelligence

comparison python/storage.py @ 777:ef6dd60be2e1 dev

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
added function to save feature trajectories
author Nicolas Saunier <nicolas.saunier@polymtl.ca>
date Fri, 05 Feb 2016 17:19:13 -0500
parents bf4a1790cfac
children bd684e57c431
comparison
equal deleted inserted replaced
776:84420159c5f4 777:ef6dd60be2e1
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
455 printDBError(error) 426 printDBError(error)
456 return boundingBoxes 427 return boundingBoxes
457 connection.close() 428 connection.close()
458 return boundingBoxes 429 return boundingBoxes
459 430
431 def saveTrajectoriesToSqlite(outputFilename, objects, trajectoryType, withFeatures = False):
432 '''Writes features, ie the trajectories positions (and velocities if exist)
433 with their instants to a specified sqlite file
434
435 TODO: Not implemented for other trajectoryType than features
436 For objects, with features will control whether the features
437 corresponding to the object are also saved'''
438
439 connection = sqlite3.connect(outputFilename)
440 try:
441 cursor = connection.cursor()
442
443 if trajectoryType == 'feature':
444 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))")
445 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))")
446
447 positionQuery = "insert into positions (trajectory_id, frame_number, x_coordinate, y_coordinate) values (?,?,?,?)"
448 velocityQuery = "insert into velocities (trajectory_id, frame_number, x_coordinate, y_coordinate) values (?,?,?,?)"
449 for obj in objects:
450 num = obj.getNum()
451 frame_number = obj.getFirstInstant()
452 for position in obj.getPositions():
453 cursor.execute(positionQuery, (num, frame_number, position.x, position.y))
454 frame_number += 1
455 if obj.getVelocities() is not None:
456 frame_number = obj.getFirstInstant()
457 velocities = obj.getVelocities()
458 for i in xrange(velocities.length()-1):
459 v = velocities[i]
460 cursor.execute(velocityQuery, (num, frame_number, v.x, v.y))
461 frame_number += 1
462 connection.commit()
463 #elif trajectoryType == 'feature':
464 else:
465 print('Unknown trajectory type {}'.format(trajectoryType))
466 except sqlite3.OperationalError as error:
467 printDBError(error)
468 connection.close()
469
460 ######################### 470 #########################
461 # saving and loading for scene interpretation (Mohamed Gomaa Mohamed's PhD) 471 # saving and loading for scene interpretation (Mohamed Gomaa Mohamed's PhD)
462 ######################### 472 #########################
463 473
464 def writeFeaturesToSqlite(objects, outputFilename, trajectoryType, objectNumbers = -1):
465 '''write features trajectories maintain trajectory ID,velocities dataset '''
466 connection = sqlite3.connect(outputFilename)
467 cursor = connection.cursor()
468
469 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))")
470 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))")
471
472 if trajectoryType == 'feature':
473 if type(objectNumbers) == int and objectNumbers == -1:
474 for trajectory in objects:
475 trajectory_id = trajectory.num
476 frame_number = trajectory.timeInterval.first
477 for position,velocity in zip(trajectory.getPositions(),trajectory.getVelocities()):
478 cursor.execute("insert into positions (trajectory_id, frame_number, x_coordinate, y_coordinate) values (?,?,?,?)",(trajectory_id,frame_number,position.x,position.y))
479 cursor.execute("insert into velocities (trajectory_id, frame_number, x_coordinate, y_coordinate) values (?,?,?,?)",(trajectory_id,frame_number,velocity.x,velocity.y))
480 frame_number += 1
481
482 connection.commit()
483 connection.close()
484
485 def writePrototypesToSqlite(prototypes,nMatching, outputFilename): 474 def writePrototypesToSqlite(prototypes,nMatching, outputFilename):
486 """ prototype dataset is a dictionary with keys== routes, values== prototypes Ids """ 475 """ prototype dataset is a dictionary with keys== routes, values== prototypes Ids """
487 connection = sqlite3.connect(outputFilename) 476 connection = sqlite3.connect(outputFilename)
488 cursor = connection.cursor() 477 cursor = connection.cursor()
489 478
490 cursor.execute("CREATE TABLE IF NOT EXISTS \"prototypes\"(prototype_id INTEGER,routeIDstart INTEGER,routeIDend INTEGER, nMatching INTEGER, PRIMARY KEY(prototype_id))") 479 cursor.execute("CREATE TABLE IF NOT EXISTS prototypes (prototype_id INTEGER,routeIDstart INTEGER,routeIDend INTEGER, nMatching INTEGER, PRIMARY KEY(prototype_id))")
491 480
492 for route in prototypes.keys(): 481 for route in prototypes.keys():
493 if prototypes[route]!=[]: 482 if prototypes[route]!=[]:
494 for i in prototypes[route]: 483 for i in prototypes[route]:
495 cursor.execute("insert into prototypes (prototype_id, routeIDstart,routeIDend, nMatching) values (?,?,?,?)",(i,route[0],route[1],nMatching[route][i])) 484 cursor.execute("insert into prototypes (prototype_id, routeIDstart,routeIDend, nMatching) values (?,?,?,?)",(i,route[0],route[1],nMatching[route][i]))
528 """ labels is a dictionary with keys: routes, values: prototypes Ids 517 """ labels is a dictionary with keys: routes, values: prototypes Ids
529 """ 518 """
530 connection = sqlite3.connect(outputFilename) 519 connection = sqlite3.connect(outputFilename)
531 cursor = connection.cursor() 520 cursor = connection.cursor()
532 521
533 cursor.execute("CREATE TABLE IF NOT EXISTS \"labels\"(object_id INTEGER,routeIDstart INTEGER,routeIDend INTEGER, prototype_id INTEGER, PRIMARY KEY(object_id))") 522 cursor.execute("CREATE TABLE IF NOT EXISTS labels (object_id INTEGER,routeIDstart INTEGER,routeIDend INTEGER, prototype_id INTEGER, PRIMARY KEY(object_id))")
534 523
535 for route in labels.keys(): 524 for route in labels.keys():
536 if labels[route]!=[]: 525 if labels[route]!=[]:
537 for i in labels[route]: 526 for i in labels[route]:
538 for j in labels[route][i]: 527 for j in labels[route][i]:
568 def writeSpeedPrototypeToSqlite(prototypes,nmatching, outFilename): 557 def writeSpeedPrototypeToSqlite(prototypes,nmatching, outFilename):
569 """ to match the format of second layer prototypes""" 558 """ to match the format of second layer prototypes"""
570 connection = sqlite3.connect(outFilename) 559 connection = sqlite3.connect(outFilename)
571 cursor = connection.cursor() 560 cursor = connection.cursor()
572 561
573 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))") 562 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))")
574 563
575 for route in prototypes.keys(): 564 for route in prototypes.keys():
576 if prototypes[route]!={}: 565 if prototypes[route]!={}:
577 for i in prototypes[route]: 566 for i in prototypes[route]:
578 if prototypes[route][i]!= []: 567 if prototypes[route][i]!= []:
613 def writeRoutesToSqlite(Routes, outputFilename): 602 def writeRoutesToSqlite(Routes, outputFilename):
614 """ This function writes the activity path define by start and end IDs""" 603 """ This function writes the activity path define by start and end IDs"""
615 connection = sqlite3.connect(outputFilename) 604 connection = sqlite3.connect(outputFilename)
616 cursor = connection.cursor() 605 cursor = connection.cursor()
617 606
618 cursor.execute("CREATE TABLE IF NOT EXISTS \"routes\"(object_id INTEGER,routeIDstart INTEGER,routeIDend INTEGER, PRIMARY KEY(object_id))") 607 cursor.execute("CREATE TABLE IF NOT EXISTS routes (object_id INTEGER,routeIDstart INTEGER,routeIDend INTEGER, PRIMARY KEY(object_id))")
619 608
620 for route in Routes.keys(): 609 for route in Routes.keys():
621 if Routes[route]!=[]: 610 if Routes[route]!=[]:
622 for i in Routes[route]: 611 for i in Routes[route]:
623 cursor.execute("insert into routes (object_id, routeIDstart,routeIDend) values (?,?,?)",(i,route[0],route[1])) 612 cursor.execute("insert into routes (object_id, routeIDstart,routeIDend) values (?,?,?)",(i,route[0],route[1]))