Mercurial Hosting > traffic-intelligence
comparison python/storage.py @ 263:c71540470057
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): |