Mercurial Hosting > traffic-intelligence
diff 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 |
line wrap: on
line diff
--- a/python/storage.py Wed Aug 12 00:24:06 2015 -0400 +++ b/python/storage.py Wed Aug 12 08:26:59 2015 -0400 @@ -109,208 +109,6 @@ connection.commit() connection.close() -def writeFeaturesToSqlite(objects, outputFilename, trajectoryType, objectNumbers = -1): - '''write features trajectories maintain trajectory ID,velocities dataset ''' - connection = sqlite3.connect(outputFilename) - cursor = connection.cursor() - - 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))") - 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))") - - if trajectoryType == 'feature': - if type(objectNumbers) == int and objectNumbers == -1: - for trajectory in objects: - trajectory_id = trajectory.num - frame_number = trajectory.timeInterval.first - for position,velocity in zip(trajectory.getPositions(),trajectory.getVelocities()): - cursor.execute("insert into positions (trajectory_id, frame_number, x_coordinate, y_coordinate) values (?,?,?,?)",(trajectory_id,frame_number,position.x,position.y)) - cursor.execute("insert into velocities (trajectory_id, frame_number, x_coordinate, y_coordinate) values (?,?,?,?)",(trajectory_id,frame_number,velocity.x,velocity.y)) - frame_number += 1 - - connection.commit() - connection.close() - -def writePrototypesToSqlite(prototypes,nMatching, outputFilename): - """ prototype dataset is a dictionary with keys== routes, values== prototypes Ids """ - connection = sqlite3.connect(outputFilename) - cursor = connection.cursor() - - cursor.execute("CREATE TABLE IF NOT EXISTS \"prototypes\"(prototype_id INTEGER,routeIDstart INTEGER,routeIDend INTEGER, nMatching INTEGER, PRIMARY KEY(prototype_id))") - - for route in prototypes.keys(): - if prototypes[route]!=[]: - for i in prototypes[route]: - cursor.execute("insert into prototypes (prototype_id, routeIDstart,routeIDend, nMatching) values (?,?,?,?)",(i,route[0],route[1],nMatching[route][i])) - - connection.commit() - connection.close() - -def loadPrototypesFromSqlite(filename): - """ - This function loads the prototype file in the database - It returns a dictionary for prototypes for each route and nMatching - """ - prototypes = {} - nMatching={} - - connection = sqlite3.connect(filename) - cursor = connection.cursor() - - try: - cursor.execute('SELECT * from prototypes order by prototype_id, routeIDstart,routeIDend, nMatching') - except sqlite3.OperationalError as error: - utils.printDBError(error) - return [] - - for row in cursor: - route=(row[1],row[2]) - if route not in prototypes.keys(): - prototypes[route]=[] - prototypes[route].append(row[0]) - nMatching[row[0]]=row[3] - - connection.close() - return prototypes,nMatching - -def writeLabelsToSqlite(labels, outputFilename): - """ labels is a dictionary with keys: routes, values: prototypes Ids - """ - connection = sqlite3.connect(outputFilename) - cursor = connection.cursor() - - cursor.execute("CREATE TABLE IF NOT EXISTS \"labels\"(object_id INTEGER,routeIDstart INTEGER,routeIDend INTEGER, prototype_id INTEGER, PRIMARY KEY(object_id))") - - for route in labels.keys(): - if labels[route]!=[]: - for i in labels[route]: - for j in labels[route][i]: - cursor.execute("insert into labels (object_id, routeIDstart,routeIDend, prototype_id) values (?,?,?,?)",(j,route[0],route[1],i)) - - connection.commit() - connection.close() - -def loadLabelsFromSqlite(filename): - labels = {} - - connection = sqlite3.connect(filename) - cursor = connection.cursor() - - try: - cursor.execute('SELECT * from labels order by object_id, routeIDstart,routeIDend, prototype_id') - except sqlite3.OperationalError as error: - utils.printDBError(error) - return [] - - for row in cursor: - route=(row[1],row[2]) - p=row[3] - if route not in labels.keys(): - labels[route]={} - if p not in labels[route].keys(): - labels[route][p]=[] - labels[route][p].append(row[0]) - - connection.close() - return labels -def writeSpeedPrototypeToSqlite(prototypes,nmatching, outFilename): - """ to match the format of second layer prototypes""" - connection = sqlite3.connect(outFilename) - cursor = connection.cursor() - - 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))") - - for route in prototypes.keys(): - if prototypes[route]!={}: - for i in prototypes[route]: - if prototypes[route][i]!= []: - for j in prototypes[route][i]: - cursor.execute("insert into speedprototypes (spdprototype_id,prototype_id, routeID_start, routeID_end, nMatching) values (?,?,?,?,?)",(j,i,route[0],route[1],nmatching[j])) - - connection.commit() - connection.close() - -def loadSpeedPrototypeFromSqlite(filename): - """ - This function loads the prototypes table in the database of name <filename>. - """ - prototypes = {} - nMatching={} - connection = sqlite3.connect(filename) - cursor = connection.cursor() - - try: - cursor.execute('SELECT * from speedprototypes order by spdprototype_id,prototype_id, routeID_start, routeID_end, nMatching') - except sqlite3.OperationalError as error: - utils.printDBError(error) - return [] - - for row in cursor: - route=(row[2],row[3]) - if route not in prototypes.keys(): - prototypes[route]={} - if row[1] not in prototypes[route].keys(): - prototypes[route][row[1]]=[] - prototypes[route][row[1]].append(row[0]) - nMatching[row[0]]=row[4] - - connection.close() - return prototypes,nMatching - - -def writeRoutesToSqlite(Routes, outputFilename): - """ This function writes the activity path define by start and end IDs""" - connection = sqlite3.connect(outputFilename) - cursor = connection.cursor() - - cursor.execute("CREATE TABLE IF NOT EXISTS \"routes\"(object_id INTEGER,routeIDstart INTEGER,routeIDend INTEGER, PRIMARY KEY(object_id))") - - for route in Routes.keys(): - if Routes[route]!=[]: - for i in Routes[route]: - cursor.execute("insert into routes (object_id, routeIDstart,routeIDend) values (?,?,?)",(i,route[0],route[1])) - - connection.commit() - connection.close() - -def loadRoutesFromSqlite(filename): - Routes = {} - - connection = sqlite3.connect(filename) - cursor = connection.cursor() - - try: - cursor.execute('SELECT * from routes order by object_id, routeIDstart,routeIDend') - except sqlite3.OperationalError as error: - utils.printDBError(error) - return [] - - for row in cursor: - route=(row[1],row[2]) - if route not in Routes.keys(): - Routes[route]=[] - Routes[route].append(row[0]) - - connection.close() - return Routes - -def setRoutes(filename, objects): - connection = sqlite3.connect(filename) - cursor = connection.cursor() - for obj in objects: - cursor.execute('update objects set startRouteID = {} where object_id = {}'.format(obj.startRouteID, obj.getNum())) - cursor.execute('update objects set endRouteID = {} where object_id = {}'.format(obj.endRouteID, obj.getNum())) - connection.commit() - connection.close() - -def setRoadUserTypes(filename, objects): - '''Saves the user types of the objects in the sqlite database stored in filename - The objects should exist in the objects table''' - connection = sqlite3.connect(filename) - cursor = connection.cursor() - for obj in objects: - cursor.execute('update objects set road_user_type = {} where object_id = {}'.format(obj.getUserType(), obj.getNum())) - connection.commit() - connection.close() def loadPrototypeMatchIndexesFromSqlite(filename): """ @@ -477,6 +275,22 @@ connection.close() return objects +def savePrototypesToSqlite(filename, prototypes, trajectoryType = 'feature'): + 'Work in progress, do not use' + connection = sqlite3.connect(filename) + cursor = connection.cursor() + try: + 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))') + #for inter in interactions: + # saveInteraction(cursor, inter) + except sqlite3.OperationalError as error: + printDBError(error) + connection.commit() + connection.close() + +def loadPrototypesFromSqlite(filename): + pass + def loadGroundTruthFromSqlite(filename, gtType = 'bb', gtNumbers = None): 'Loads bounding box annotations (ground truth) from an SQLite ' connection = sqlite3.connect(filename) @@ -631,6 +445,214 @@ return boundingBoxes ######################### +# saving and loading for scene interpretation (Mohamed Gomaa Mohamed's PhD) +######################### + +def writeFeaturesToSqlite(objects, outputFilename, trajectoryType, objectNumbers = -1): + '''write features trajectories maintain trajectory ID,velocities dataset ''' + connection = sqlite3.connect(outputFilename) + cursor = connection.cursor() + + 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))") + 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))") + + if trajectoryType == 'feature': + if type(objectNumbers) == int and objectNumbers == -1: + for trajectory in objects: + trajectory_id = trajectory.num + frame_number = trajectory.timeInterval.first + for position,velocity in zip(trajectory.getPositions(),trajectory.getVelocities()): + cursor.execute("insert into positions (trajectory_id, frame_number, x_coordinate, y_coordinate) values (?,?,?,?)",(trajectory_id,frame_number,position.x,position.y)) + cursor.execute("insert into velocities (trajectory_id, frame_number, x_coordinate, y_coordinate) values (?,?,?,?)",(trajectory_id,frame_number,velocity.x,velocity.y)) + frame_number += 1 + + connection.commit() + connection.close() + +def writePrototypesToSqlite(prototypes,nMatching, outputFilename): + """ prototype dataset is a dictionary with keys== routes, values== prototypes Ids """ + connection = sqlite3.connect(outputFilename) + cursor = connection.cursor() + + cursor.execute("CREATE TABLE IF NOT EXISTS \"prototypes\"(prototype_id INTEGER,routeIDstart INTEGER,routeIDend INTEGER, nMatching INTEGER, PRIMARY KEY(prototype_id))") + + for route in prototypes.keys(): + if prototypes[route]!=[]: + for i in prototypes[route]: + cursor.execute("insert into prototypes (prototype_id, routeIDstart,routeIDend, nMatching) values (?,?,?,?)",(i,route[0],route[1],nMatching[route][i])) + + connection.commit() + connection.close() + +def readPrototypesFromSqlite(filename): + """ + This function loads the prototype file in the database + It returns a dictionary for prototypes for each route and nMatching + """ + prototypes = {} + nMatching={} + + connection = sqlite3.connect(filename) + cursor = connection.cursor() + + try: + cursor.execute('SELECT * from prototypes order by prototype_id, routeIDstart,routeIDend, nMatching') + except sqlite3.OperationalError as error: + utils.printDBError(error) + return [] + + for row in cursor: + route=(row[1],row[2]) + if route not in prototypes.keys(): + prototypes[route]=[] + prototypes[route].append(row[0]) + nMatching[row[0]]=row[3] + + connection.close() + return prototypes,nMatching + +def writeLabelsToSqlite(labels, outputFilename): + """ labels is a dictionary with keys: routes, values: prototypes Ids + """ + connection = sqlite3.connect(outputFilename) + cursor = connection.cursor() + + cursor.execute("CREATE TABLE IF NOT EXISTS \"labels\"(object_id INTEGER,routeIDstart INTEGER,routeIDend INTEGER, prototype_id INTEGER, PRIMARY KEY(object_id))") + + for route in labels.keys(): + if labels[route]!=[]: + for i in labels[route]: + for j in labels[route][i]: + cursor.execute("insert into labels (object_id, routeIDstart,routeIDend, prototype_id) values (?,?,?,?)",(j,route[0],route[1],i)) + + connection.commit() + connection.close() + +def loadLabelsFromSqlite(filename): + labels = {} + + connection = sqlite3.connect(filename) + cursor = connection.cursor() + + try: + cursor.execute('SELECT * from labels order by object_id, routeIDstart,routeIDend, prototype_id') + except sqlite3.OperationalError as error: + utils.printDBError(error) + return [] + + for row in cursor: + route=(row[1],row[2]) + p=row[3] + if route not in labels.keys(): + labels[route]={} + if p not in labels[route].keys(): + labels[route][p]=[] + labels[route][p].append(row[0]) + + connection.close() + return labels + +def writeSpeedPrototypeToSqlite(prototypes,nmatching, outFilename): + """ to match the format of second layer prototypes""" + connection = sqlite3.connect(outFilename) + cursor = connection.cursor() + + 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))") + + for route in prototypes.keys(): + if prototypes[route]!={}: + for i in prototypes[route]: + if prototypes[route][i]!= []: + for j in prototypes[route][i]: + cursor.execute("insert into speedprototypes (spdprototype_id,prototype_id, routeID_start, routeID_end, nMatching) values (?,?,?,?,?)",(j,i,route[0],route[1],nmatching[j])) + + connection.commit() + connection.close() + +def loadSpeedPrototypeFromSqlite(filename): + """ + This function loads the prototypes table in the database of name <filename>. + """ + prototypes = {} + nMatching={} + connection = sqlite3.connect(filename) + cursor = connection.cursor() + + try: + cursor.execute('SELECT * from speedprototypes order by spdprototype_id,prototype_id, routeID_start, routeID_end, nMatching') + except sqlite3.OperationalError as error: + utils.printDBError(error) + return [] + + for row in cursor: + route=(row[2],row[3]) + if route not in prototypes.keys(): + prototypes[route]={} + if row[1] not in prototypes[route].keys(): + prototypes[route][row[1]]=[] + prototypes[route][row[1]].append(row[0]) + nMatching[row[0]]=row[4] + + connection.close() + return prototypes,nMatching + + +def writeRoutesToSqlite(Routes, outputFilename): + """ This function writes the activity path define by start and end IDs""" + connection = sqlite3.connect(outputFilename) + cursor = connection.cursor() + + cursor.execute("CREATE TABLE IF NOT EXISTS \"routes\"(object_id INTEGER,routeIDstart INTEGER,routeIDend INTEGER, PRIMARY KEY(object_id))") + + for route in Routes.keys(): + if Routes[route]!=[]: + for i in Routes[route]: + cursor.execute("insert into routes (object_id, routeIDstart,routeIDend) values (?,?,?)",(i,route[0],route[1])) + + connection.commit() + connection.close() + +def loadRoutesFromSqlite(filename): + Routes = {} + + connection = sqlite3.connect(filename) + cursor = connection.cursor() + + try: + cursor.execute('SELECT * from routes order by object_id, routeIDstart,routeIDend') + except sqlite3.OperationalError as error: + utils.printDBError(error) + return [] + + for row in cursor: + route=(row[1],row[2]) + if route not in Routes.keys(): + Routes[route]=[] + Routes[route].append(row[0]) + + connection.close() + return Routes + +def setRoutes(filename, objects): + connection = sqlite3.connect(filename) + cursor = connection.cursor() + for obj in objects: + cursor.execute('update objects set startRouteID = {} where object_id = {}'.format(obj.startRouteID, obj.getNum())) + cursor.execute('update objects set endRouteID = {} where object_id = {}'.format(obj.endRouteID, obj.getNum())) + connection.commit() + connection.close() + +def setRoadUserTypes(filename, objects): + '''Saves the user types of the objects in the sqlite database stored in filename + The objects should exist in the objects table''' + connection = sqlite3.connect(filename) + cursor = connection.cursor() + for obj in objects: + cursor.execute('update objects set road_user_type = {} where object_id = {}'.format(obj.getUserType(), obj.getNum())) + connection.commit() + connection.close() + +######################### # txt files #########################