Mercurial Hosting > traffic-intelligence
comparison python/storage.py @ 491:343cfd185ca6
minor changes and reaarrangements
author | Nicolas Saunier <nicolas.saunier@polymtl.ca> |
---|---|
date | Wed, 16 Apr 2014 17:43:53 -0400 |
parents | f6415f012640 |
children | 935430b1d408 |
comparison
equal
deleted
inserted
replaced
490:60735bd452fc | 491:343cfd185ca6 |
---|---|
15 | 15 |
16 ######################### | 16 ######################### |
17 # Sqlite | 17 # Sqlite |
18 ######################### | 18 ######################### |
19 | 19 |
20 # utils | |
21 def printDBError(error): | |
22 print('DB Error: {}'.format(error)) | |
23 | |
24 def dropTables(connection, tableNames): | |
25 'deletes the table with names in tableNames' | |
26 try: | |
27 cursor = connection.cursor() | |
28 for tableName in tableNames: | |
29 cursor.execute('DROP TABLE IF EXISTS '+tableName) | |
30 except sqlite3.OperationalError as error: | |
31 printDBError(error) | |
32 | |
33 # IO to sqlite | |
20 def writeTrajectoriesToSqlite(objects, outFilename, trajectoryType, objectNumbers = -1): | 34 def writeTrajectoriesToSqlite(objects, outFilename, trajectoryType, objectNumbers = -1): |
21 """ | 35 """ |
22 This function writers trajectories to a specified sqlite file | 36 This function writers trajectories to a specified sqlite file |
23 @param[in] objects -> a list of trajectories | 37 @param[in] objects -> a list of trajectories |
24 @param[in] trajectoryType - | 38 @param[in] trajectoryType - |
67 cursor = connection.cursor() | 81 cursor = connection.cursor() |
68 | 82 |
69 try: | 83 try: |
70 cursor.execute('SELECT * from prototypes order by prototype_id, trajectory_id_matched') | 84 cursor.execute('SELECT * from prototypes order by prototype_id, trajectory_id_matched') |
71 except sqlite3.OperationalError as error: | 85 except sqlite3.OperationalError as error: |
72 utils.printDBError(error) | 86 printDBError(error) |
73 return [] | 87 return [] |
74 | 88 |
75 for row in cursor: | 89 for row in cursor: |
76 matched_indexes.append((row[0],row[1])) | 90 matched_indexes.append((row[0],row[1])) |
77 | 91 |
114 cursor.execute(queryStatement) | 128 cursor.execute(queryStatement) |
115 logging.debug(queryStatement) | 129 logging.debug(queryStatement) |
116 else: | 130 else: |
117 print('no trajectory type was chosen') | 131 print('no trajectory type was chosen') |
118 except sqlite3.OperationalError as error: | 132 except sqlite3.OperationalError as error: |
119 utils.printDBError(error) | 133 printDBError(error) |
120 return [] | 134 return [] |
121 | 135 |
122 objId = -1 | 136 objId = -1 |
123 obj = None | 137 obj = None |
124 objects = [] | 138 objects = [] |
183 | 197 |
184 for obj in objects: | 198 for obj in objects: |
185 obj.userType = userTypes[obj.getNum()] | 199 obj.userType = userTypes[obj.getNum()] |
186 | 200 |
187 except sqlite3.OperationalError as error: | 201 except sqlite3.OperationalError as error: |
188 utils.printDBError(error) | 202 printDBError(error) |
189 return [] | 203 return [] |
190 | 204 |
191 connection.close() | 205 connection.close() |
192 return objects | 206 return objects |
193 | 207 |
194 def removeFromSqlite(filename, dataType): | 208 def removeFromSqlite(filename, dataType): |
195 'Removes some tables in the filename depending on type of data' | 209 'Removes some tables in the filename depending on type of data' |
196 connection = sqlite3.connect(filename) | 210 import os |
197 if dataType == 'object': | 211 if os.path.isfile(filename): |
198 utils.dropTables(connection, ['objects', 'objects_features']) | 212 connection = sqlite3.connect(filename) |
199 elif dataType == 'interaction': | 213 if dataType == 'object': |
200 utils.dropTables(connection, ['interactions', 'indicators']) | 214 dropTables(connection, ['objects', 'objects_features']) |
201 elif dataType == 'bb': | 215 elif dataType == 'interaction': |
202 utils.dropTables(connection, ['bounding_boxes']) | 216 dropTables(connection, ['interactions', 'indicators']) |
217 elif dataType == 'bb': | |
218 dropTables(connection, ['bounding_boxes']) | |
219 else: | |
220 print('Unknown data type {} to delete from database'.format(dataType)) | |
221 connection.close() | |
203 else: | 222 else: |
204 print('Unknown data type {} to delete from database'.format(dataType)) | 223 print('{} does not exist'.format(filename)) |
205 connection.close() | |
206 | 224 |
207 def createInteractionTable(cursor): | 225 def createInteractionTable(cursor): |
208 cursor.execute('CREATE TABLE IF NOT EXISTS interactions (id INTEGER PRIMARY KEY, object_id1 INTEGER, object_id2 INTEGER, first_frame_number INTEGER, last_frame_number INTEGER, FOREIGN KEY(object_id1) REFERENCES objects(id), FOREIGN KEY(object_id2) REFERENCES objects(id))') | 226 cursor.execute('CREATE TABLE IF NOT EXISTS interactions (id INTEGER PRIMARY KEY, object_id1 INTEGER, object_id2 INTEGER, first_frame_number INTEGER, last_frame_number INTEGER, FOREIGN KEY(object_id1) REFERENCES objects(id), FOREIGN KEY(object_id2) REFERENCES objects(id))') |
209 | 227 |
210 def createIndicatorTables(cursor): | 228 def createIndicatorTables(cursor): |
223 try: | 241 try: |
224 createInteractionTable(cursor) | 242 createInteractionTable(cursor) |
225 for inter in interactions: | 243 for inter in interactions: |
226 saveInteraction(cursor, inter) | 244 saveInteraction(cursor, inter) |
227 except sqlite3.OperationalError as error: | 245 except sqlite3.OperationalError as error: |
228 utils.printDBError(error) | 246 printDBError(error) |
229 connection.commit() | 247 connection.commit() |
230 connection.close() | 248 connection.close() |
231 | 249 |
232 def saveIndicator(cursor, interactionNum, indicator): | 250 def saveIndicator(cursor, interactionNum, indicator): |
233 for instant in indicator.getTimeInterval(): | 251 for instant in indicator.getTimeInterval(): |
246 for indicatorName in indicatorNames: | 264 for indicatorName in indicatorNames: |
247 indicator = inter.getIndicator(indicatorName) | 265 indicator = inter.getIndicator(indicatorName) |
248 if indicator != None: | 266 if indicator != None: |
249 saveIndicator(cursor, inter.getNum(), indicator) | 267 saveIndicator(cursor, inter.getNum(), indicator) |
250 except sqlite3.OperationalError as error: | 268 except sqlite3.OperationalError as error: |
251 utils.printDBError(error) | 269 printDBError(error) |
252 connection.commit() | 270 connection.commit() |
253 connection.close() | 271 connection.close() |
254 | 272 |
255 def loadInteractions(filename): | 273 def loadInteractions(filename): |
256 '''Loads interaction and their indicators | 274 '''Loads interaction and their indicators |
285 indicatorName = events.Interaction.indicatorNames[indicatorTypeNum] | 303 indicatorName = events.Interaction.indicatorNames[indicatorTypeNum] |
286 tmpIndicators[indicatorName] = indicators.SeverityIndicator(indicatorName, indicatorValues) | 304 tmpIndicators[indicatorName] = indicators.SeverityIndicator(indicatorName, indicatorValues) |
287 interactions.append(events.Interaction(interactionNum, moving.TimeInterval(row[3],row[4]), roadUserNumbers[0], roadUserNumbers[1])) | 305 interactions.append(events.Interaction(interactionNum, moving.TimeInterval(row[3],row[4]), roadUserNumbers[0], roadUserNumbers[1])) |
288 interactions[-1].indicators = tmpIndicators | 306 interactions[-1].indicators = tmpIndicators |
289 except sqlite3.OperationalError as error: | 307 except sqlite3.OperationalError as error: |
290 utils.printDBError(error) | 308 printDBError(error) |
291 return [] | 309 return [] |
292 connection.close() | 310 connection.close() |
293 return interactions | 311 return interactions |
294 # load first and last object instants | 312 # load first and last object instants |
295 # CREATE TEMP TABLE IF NOT EXISTS object_instants AS SELECT OF.object_id, min(frame_number) as first_instant, max(frame_number) as last_instant from positions P, objects_features OF where P.trajectory_id = OF.trajectory_id group by OF.object_id order by OF.object_id | 313 # CREATE TEMP TABLE IF NOT EXISTS object_instants AS SELECT OF.object_id, min(frame_number) as first_instant, max(frame_number) as last_instant from positions P, objects_features OF where P.trajectory_id = OF.trajectory_id group by OF.object_id order by OF.object_id |
304 cursor.execute('INSERT INTO bounding_boxes SELECT object_id, frame_number, min(x), min(y), max(x), max(y) from ' | 322 cursor.execute('INSERT INTO bounding_boxes SELECT object_id, frame_number, min(x), min(y), max(x), max(y) from ' |
305 '(SELECT object_id, frame_number, (x*{}+y*{}+{})/w as x, (x*{}+y*{}+{})/w as y from ' | 323 '(SELECT object_id, frame_number, (x*{}+y*{}+{})/w as x, (x*{}+y*{}+{})/w as y from ' |
306 '(SELECT OF.object_id, P.frame_number, P.x_coordinate as x, P.y_coordinate as y, P.x_coordinate*{}+P.y_coordinate*{}+{} as w from positions P, objects_features OF where P.trajectory_id = OF.trajectory_id)) '.format(invHomography[0,0], invHomography[0,1], invHomography[0,2], invHomography[1,0], invHomography[1,1], invHomography[1,2], invHomography[2,0], invHomography[2,1], invHomography[2,2])+ | 324 '(SELECT OF.object_id, P.frame_number, P.x_coordinate as x, P.y_coordinate as y, P.x_coordinate*{}+P.y_coordinate*{}+{} as w from positions P, objects_features OF where P.trajectory_id = OF.trajectory_id)) '.format(invHomography[0,0], invHomography[0,1], invHomography[0,2], invHomography[1,0], invHomography[1,1], invHomography[1,2], invHomography[2,0], invHomography[2,1], invHomography[2,2])+ |
307 'GROUP BY object_id, frame_number') | 325 'GROUP BY object_id, frame_number') |
308 except sqlite3.OperationalError as error: | 326 except sqlite3.OperationalError as error: |
309 utils.printDBError(error) | 327 printDBError(error) |
310 connection.commit() | 328 connection.commit() |
311 connection.close() | 329 connection.close() |
312 | 330 |
313 def loadBoundingBoxTable(filename): | 331 def loadBoundingBoxTable(filename): |
314 connection = sqlite3.connect(filename) | 332 connection = sqlite3.connect(filename) |
322 #objId = -1 | 340 #objId = -1 |
323 for row in cursor: | 341 for row in cursor: |
324 #if row[0] != objId: | 342 #if row[0] != objId: |
325 boundingBoxes.setdefault(row[1], []).append([moving.Point(row[2], row[3]), moving.Point(row[4], row[5])]) | 343 boundingBoxes.setdefault(row[1], []).append([moving.Point(row[2], row[3]), moving.Point(row[4], row[5])]) |
326 except sqlite3.OperationalError as error: | 344 except sqlite3.OperationalError as error: |
327 utils.printDBError(error) | 345 printDBError(error) |
328 return boundingBoxes | 346 return boundingBoxes |
329 connection.close() | 347 connection.close() |
330 return boundingBoxes | 348 return boundingBoxes |
331 | 349 |
332 | 350 |