Mercurial Hosting > traffic-intelligence
diff python/storage.py @ 239:93c26e45efd8
modified functions to read velocities from sqlite database
author | Nicolas Saunier <nicolas.saunier@polymtl.ca> |
---|---|
date | Wed, 11 Jul 2012 16:30:23 -0400 |
parents | 584613399513 |
children | 583a2c4622f9 |
line wrap: on
line diff
--- a/python/storage.py Mon Jul 09 00:46:04 2012 -0400 +++ b/python/storage.py Wed Jul 11 16:30:23 2012 -0400 @@ -66,39 +66,36 @@ connection.close() return matched_indexes -def loadTrajectoriesFromSqlite(filename, trajectoryType, objectNumbers = -1): - '''Loads nObjects or the indices in objectNumbers from the database - TODO: load velocities (replace table name 'positions' by 'velocities' - TODO: load features as well, other ways of averaging trajectories - ''' - import sqlite3 - - connection = sqlite3.connect(filename) # add test if it open +def loadTrajectoriesFromTable(connection, tableName, trajectoryType, objectNumbers = -1): + '''Loads trajectories (in the general sense) from the given table + can be positions or velocities + + returns a moving object''' cursor = connection.cursor() try: if trajectoryType == 'feature': if type(objectNumbers) == int: if objectNumbers == -1: - cursor.execute('SELECT * from positions order by trajectory_id, frame_number') + cursor.execute('SELECT * from '+tableName+' order by trajectory_id, frame_number') else: - cursor.execute('SELECT * from positions where trajectory_id between 0 and {0} order by trajectory_id, frame_number'.format(objectNumbers)) + cursor.execute('SELECT * from {0} where trajectory_id between 0 and {1} order by trajectory_id, frame_number'.format(tableName, objectNumbers)) elif type(objectNumbers) == list: - cursor.execute('SELECT * from positions where trajectory_id in ('+', '.join([str(n) for n in objectNumbers])+') order by trajectory_id, frame_number') + cursor.execute('SELECT * from '+tableName+' where trajectory_id in ('+', '.join([str(n) for n in objectNumbers])+') order by trajectory_id, frame_number') elif trajectoryType == 'object': if type(objectNumbers) == int: if objectNumbers == -1: - cursor.execute('SELECT OF.object_id, P.frame_number, avg(P.x_coordinate), avg(P.y_coordinate) from positions P, objects_features OF where P.trajectory_id = OF.trajectory_id group by object_id, frame_number') + cursor.execute('SELECT OF.object_id, P.frame_number, avg(P.x_coordinate), avg(P.y_coordinate) from '+tableName+' P, objects_features OF where P.trajectory_id = OF.trajectory_id group by object_id, frame_number') else: - cursor.execute('SELECT OF.object_id, P.frame_number, avg(P.x_coordinate), avg(P.y_coordinate) from positions P, objects_features OF where P.trajectory_id = OF.trajectory_id and OF.object_id between 0 and {0} group by object_id, frame_number'.format(objectNumbers)) + cursor.execute('SELECT OF.object_id, P.frame_number, avg(P.x_coordinate), avg(P.y_coordinate) from {0} P, objects_features OF where P.trajectory_id = OF.trajectory_id and OF.object_id between 0 and {1} group by object_id, frame_number'.format(tableName, objectNumbers)) elif type(objectNumbers) == list: - cursor.execute('SELECT OF.object_id, P.frame_number, avg(P.x_coordinate), avg(P.y_coordinate) from positions P, objects_features OF where P.trajectory_id = OF.trajectory_id and OF.object_id in ('+', '.join([str(n) for n in objectNumbers])+') group by object_id, frame_number') + cursor.execute('SELECT OF.object_id, P.frame_number, avg(P.x_coordinate), avg(P.y_coordinate) from '+tableName+' P, objects_features OF where P.trajectory_id = OF.trajectory_id and OF.object_id in ('+', '.join([str(n) for n in objectNumbers])+') group by object_id, frame_number') else: print('no trajectory type was chosen') except sqlite3.OperationalError as err: print('DB Error: {0}'.format(err)) return [] - + objId = -1 obj = None objects = [] @@ -115,6 +112,27 @@ if obj: objects.append(obj) + return objects + +def loadTrajectoriesFromSqlite(filename, trajectoryType, objectNumbers = -1): + '''Loads nObjects or the indices in objectNumbers from the database + TODO: load feature numbers and not average feature trajectories + TODO: other ways of averaging trajectories (load all points, sorted by frame_number and leave the agregation to be done in python) + ''' + import sqlite3 + + connection = sqlite3.connect(filename) # add test if it open + + objects = loadTrajectoriesFromTable(connection, 'positions', trajectoryType, objectNumbers) + objectVelocities = loadTrajectoriesFromTable(connection, 'velocities', trajectoryType, objectNumbers) + + if len(objectVelocities) > 0: + for o,v in zip(objects, objectVelocities): + if o.num == v.num: + o.velocities = v.positions + else: + print('Could not match positions {0} with velocities {1}'.format(o.num, v.num)) + connection.close() return objects