Mercurial > hg > nsaunier > traffic-intelligence

comparison python/storage.py @ 239:93c26e45efd8

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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
comparison
equal deleted inserted replaced
238:be3761a09b20 239:93c26e45efd8
64 matched_indexes.append((row[0],row[1])) 64 matched_indexes.append((row[0],row[1]))
65 65
66 connection.close() 66 connection.close()
67 return matched_indexes 67 return matched_indexes
68 68
69 def loadTrajectoriesFromSqlite(filename, trajectoryType, objectNumbers = -1): 69 def loadTrajectoriesFromTable(connection, tableName, trajectoryType, objectNumbers = -1):
70 '''Loads nObjects or the indices in objectNumbers from the database 70 '''Loads trajectories (in the general sense) from the given table
71 TODO: load velocities (replace table name 'positions' by 'velocities' 71 can be positions or velocities
72 TODO: load features as well, other ways of averaging trajectories 72
73 ''' 73 returns a moving object'''
74 import sqlite3
75
76 connection = sqlite3.connect(filename) # add test if it open
77 cursor = connection.cursor() 74 cursor = connection.cursor()
78 75
79 try: 76 try:
80 if trajectoryType == 'feature': 77 if trajectoryType == 'feature':
81 if type(objectNumbers) == int: 78 if type(objectNumbers) == int:
82 if objectNumbers == -1: 79 if objectNumbers == -1:
83 cursor.execute('SELECT * from positions order by trajectory_id, frame_number') 80 cursor.execute('SELECT * from '+tableName+' order by trajectory_id, frame_number')
84 else: 81 else:
85 cursor.execute('SELECT * from positions where trajectory_id between 0 and {0} order by trajectory_id, frame_number'.format(objectNumbers)) 82 cursor.execute('SELECT * from {0} where trajectory_id between 0 and {1} order by trajectory_id, frame_number'.format(tableName, objectNumbers))
86 elif type(objectNumbers) == list: 83 elif type(objectNumbers) == list:
87 cursor.execute('SELECT * from positions where trajectory_id in ('+', '.join([str(n) for n in objectNumbers])+') order by trajectory_id, frame_number') 84 cursor.execute('SELECT * from '+tableName+' where trajectory_id in ('+', '.join([str(n) for n in objectNumbers])+') order by trajectory_id, frame_number')
88 elif trajectoryType == 'object': 85 elif trajectoryType == 'object':
89 if type(objectNumbers) == int: 86 if type(objectNumbers) == int:
90 if objectNumbers == -1: 87 if objectNumbers == -1:
91 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') 88 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')
92 else: 89 else:
93 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)) 90 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))
94 elif type(objectNumbers) == list: 91 elif type(objectNumbers) == list:
95 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') 92 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')
96 else: 93 else:
97 print('no trajectory type was chosen') 94 print('no trajectory type was chosen')
98 except sqlite3.OperationalError as err: 95 except sqlite3.OperationalError as err:
99 print('DB Error: {0}'.format(err)) 96 print('DB Error: {0}'.format(err))
100 return [] 97 return []
101 98
102 objId = -1 99 objId = -1
103 obj = None 100 obj = None
104 objects = [] 101 objects = []
105 for row in cursor: 102 for row in cursor:
106 if row[0] != objId: 103 if row[0] != objId:
112 obj.timeInterval.last = row[1] 109 obj.timeInterval.last = row[1]
113 obj.positions.addPositionXY(row[2],row[3]) 110 obj.positions.addPositionXY(row[2],row[3])
114 111
115 if obj: 112 if obj:
116 objects.append(obj) 113 objects.append(obj)
114
115 return objects
116
117 def loadTrajectoriesFromSqlite(filename, trajectoryType, objectNumbers = -1):
118 '''Loads nObjects or the indices in objectNumbers from the database
119 TODO: load feature numbers and not average feature trajectories
120 TODO: other ways of averaging trajectories (load all points, sorted by frame_number and leave the agregation to be done in python)
121 '''
122 import sqlite3
123
124 connection = sqlite3.connect(filename) # add test if it open
125
126 objects = loadTrajectoriesFromTable(connection, 'positions', trajectoryType, objectNumbers)
127 objectVelocities = loadTrajectoriesFromTable(connection, 'velocities', trajectoryType, objectNumbers)
128
129 if len(objectVelocities) > 0:
130 for o,v in zip(objects, objectVelocities):
131 if o.num == v.num:
132 o.velocities = v.positions
133 else:
134 print('Could not match positions {0} with velocities {1}'.format(o.num, v.num))
117 135
118 connection.close() 136 connection.close()
119 return objects 137 return objects
120 138
121 def removeObjectsFromSqlite(filename): 139 def removeObjectsFromSqlite(filename):