Mercurial > hg > nsaunier > traffic-intelligence

comparison python/storage.py @ 263:c71540470057

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
reorganized loading trajectories and objects, added loading feature numbers for objects (the set of features grouped as one moving object
author Nicolas Saunier <nicolas.saunier@polymtl.ca>
date Wed, 25 Jul 2012 22:06:51 -0400
parents 8ab76b95ee72
children f7872d15a6cc
comparison
equal deleted inserted replaced
262:a048066bd20f 263:c71540470057
74 matched_indexes.append((row[0],row[1])) 74 matched_indexes.append((row[0],row[1]))
75 75
76 connection.close() 76 connection.close()
77 return matched_indexes 77 return matched_indexes
78 78
79 def getTrajectoryIdQuery(objectNumbers, trajectoryType):
80 if trajectoryType == 'feature':
81 statementBeginning = ' where trajectory_id'
82 elif trajectoryType == 'object':
83 statementBeginning = ' and OF.object_id'
84 else:
85 print('no trajectory type was chosen')
86
87 if type(objectNumbers) == int:
88 if objectNumbers == -1:
89 query = ''
90 else:
91 query = statementBeginning+' between 0 and {0}'.format(objectNumbers)
92 elif type(objectNumbers) == list:
93 query = statementBeginning+' in ('+', '.join([str(n) for n in objectNumbers])+')'
94 return query
95
79 def loadTrajectoriesFromTable(connection, tableName, trajectoryType, objectNumbers = -1): 96 def loadTrajectoriesFromTable(connection, tableName, trajectoryType, objectNumbers = -1):
80 '''Loads trajectories (in the general sense) from the given table 97 '''Loads trajectories (in the general sense) from the given table
81 can be positions or velocities 98 can be positions or velocities
82 99
83 returns a moving object''' 100 returns a moving object'''
85 102
86 cursor = connection.cursor() 103 cursor = connection.cursor()
87 104
88 try: 105 try:
89 if trajectoryType == 'feature': 106 if trajectoryType == 'feature':
90 if type(objectNumbers) == int: 107 trajectoryIdQuery = getTrajectoryIdQuery(objectNumbers, trajectoryType)
91 if objectNumbers == -1: 108 cursor.execute('SELECT * from '+tableName+trajectoryIdQuery+' order by trajectory_id, frame_number')
92 cursor.execute('SELECT * from '+tableName+' order by trajectory_id, frame_number')
93 else:
94 cursor.execute('SELECT * from {0} where trajectory_id between 0 and {1} order by trajectory_id, frame_number'.format(tableName, objectNumbers))
95 elif type(objectNumbers) == list:
96 cursor.execute('SELECT * from '+tableName+' where trajectory_id in ('+', '.join([str(n) for n in objectNumbers])+') order by trajectory_id, frame_number')
97 elif trajectoryType == 'object': 109 elif trajectoryType == 'object':
98 if type(objectNumbers) == int: 110 objectIdQuery = getTrajectoryIdQuery(objectNumbers, trajectoryType)
99 if objectNumbers == -1: 111 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 '+objectIdQuery+' group by object_id, frame_number')
100 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')
101 else:
102 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))
103 elif type(objectNumbers) == list:
104 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')
105 else: 112 else:
106 print('no trajectory type was chosen') 113 print('no trajectory type was chosen')
107 except sqlite3.OperationalError as err: 114 except sqlite3.OperationalError as err:
108 print('DB Error: {0}'.format(err)) 115 print('DB Error: {0}'.format(err))
109 return [] 116 return []
142 for o,v in zip(objects, objectVelocities): 149 for o,v in zip(objects, objectVelocities):
143 if o.num == v.num: 150 if o.num == v.num:
144 o.velocities = v.positions 151 o.velocities = v.positions
145 else: 152 else:
146 print('Could not match positions {0} with velocities {1}'.format(o.num, v.num)) 153 print('Could not match positions {0} with velocities {1}'.format(o.num, v.num))
154
155 if trajectoryType == 'object':
156 cursor = connection.cursor()
157 try:
158 objectIdQuery = getTrajectoryIdQuery(objectNumbers, trajectoryType)
159 cursor.execute('SELECT P.trajectory_id, OF.object_id from positions P, objects_features OF where P.trajectory_id = OF.trajectory_id '+objectIdQuery+' group by P.trajectory_id order by OF.object_id')
160
161 # attribute feature numbers to objects
162 objId = -1
163 featureNumbers = {}
164 for row in cursor:
165 if row[1] != objId:
166 objId = row[1]
167 featureNumbers[objId] = [row[0]]
168 else:
169 featureNumbers[objId].append(row[0])
170
171 for obj in objects:
172 obj.featureNumbers = featureNumbers[obj.num]
173 except sqlite3.OperationalError as err:
174 print('DB Error: {0}'.format(err))
175 return []
147 176
148 connection.close() 177 connection.close()
149 return objects 178 return objects
150 179
151 def removeObjectsFromSqlite(filename): 180 def removeObjectsFromSqlite(filename):