Mercurial Hosting > traffic-intelligence
comparison python/storage.py @ 615:0954aaf28231
Merge
author | MohamedGomaa |
---|---|
date | Wed, 10 Dec 2014 14:12:06 -0500 |
parents | 84690dfe5560 c5406edbcf12 |
children | dc2d0a0d7fe1 |
comparison
equal
deleted
inserted
replaced
613:306db0f3c7a2 | 615:0954aaf28231 |
---|---|
32 for tableName in tableNames: | 32 for tableName in tableNames: |
33 cursor.execute('DROP TABLE IF EXISTS '+tableName) | 33 cursor.execute('DROP TABLE IF EXISTS '+tableName) |
34 except sqlite3.OperationalError as error: | 34 except sqlite3.OperationalError as error: |
35 printDBError(error) | 35 printDBError(error) |
36 | 36 |
37 # TODO: add test if database connection is open | |
37 # IO to sqlite | 38 # IO to sqlite |
38 def writeTrajectoriesToSqlite(objects, outputFilename, trajectoryType, objectNumbers = -1): | 39 def writeTrajectoriesToSqlite(objects, outputFilename, trajectoryType, objectNumbers = -1): |
39 """ | 40 """ |
40 This function writers trajectories to a specified sqlite file | 41 This function writers trajectories to a specified sqlite file |
41 @param[in] objects -> a list of trajectories | 42 @param[in] objects -> a list of trajectories |
291 | 292 |
292 def getTrajectoryIdQuery(objectNumbers, trajectoryType): | 293 def getTrajectoryIdQuery(objectNumbers, trajectoryType): |
293 if trajectoryType == 'feature': | 294 if trajectoryType == 'feature': |
294 statementBeginning = 'where trajectory_id ' | 295 statementBeginning = 'where trajectory_id ' |
295 elif trajectoryType == 'object': | 296 elif trajectoryType == 'object': |
296 statementBeginning = 'and OF.object_id ' | 297 statementBeginning = 'and OF.object_id ' |
298 elif trajectoryType == 'bbtop' or 'bbbottom': | |
299 statementBeginning = 'where object_id ' | |
297 else: | 300 else: |
298 print('no trajectory type was chosen') | 301 print('no trajectory type was chosen') |
299 | 302 |
300 if type(objectNumbers) == int: | 303 if objectNumbers is None: |
301 if objectNumbers == -1: | 304 query = '' |
302 query = '' | 305 elif type(objectNumbers) == int: |
303 else: | 306 query = statementBeginning+'between 0 and {0} '.format(objectNumbers) |
304 query = statementBeginning+'between 0 and {0} '.format(objectNumbers) | |
305 elif type(objectNumbers) == list: | 307 elif type(objectNumbers) == list: |
306 query = statementBeginning+'in ('+', '.join([str(n) for n in objectNumbers])+') ' | 308 query = statementBeginning+'in ('+', '.join([str(n) for n in objectNumbers])+') ' |
307 return query | 309 return query |
308 | 310 |
309 def loadTrajectoriesFromTable(connection, tableName, trajectoryType, objectNumbers = -1): | 311 def loadTrajectoriesFromTable(connection, tableName, trajectoryType, objectNumbers = None): |
310 '''Loads trajectories (in the general sense) from the given table | 312 '''Loads trajectories (in the general sense) from the given table |
311 can be positions or velocities | 313 can be positions or velocities |
312 | 314 |
313 returns a moving object''' | 315 returns a moving object''' |
314 cursor = connection.cursor() | 316 cursor = connection.cursor() |
315 | 317 |
316 try: | 318 try: |
319 idQuery = getTrajectoryIdQuery(objectNumbers, trajectoryType) | |
317 if trajectoryType == 'feature': | 320 if trajectoryType == 'feature': |
318 trajectoryIdQuery = getTrajectoryIdQuery(objectNumbers, trajectoryType) | 321 queryStatement = 'SELECT * from '+tableName+' '+idQuery+'ORDER BY trajectory_id, frame_number' |
319 queryStatement = 'SELECT * from '+tableName+' '+trajectoryIdQuery+'order by trajectory_id, frame_number' | |
320 cursor.execute(queryStatement) | 322 cursor.execute(queryStatement) |
321 logging.debug(queryStatement) | 323 logging.debug(queryStatement) |
322 elif trajectoryType == 'object': | 324 elif trajectoryType == 'object': |
323 objectIdQuery = getTrajectoryIdQuery(objectNumbers, trajectoryType) | 325 queryStatement = '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 '+idQuery+'group by OF.object_id, P.frame_number ORDER BY OF.object_id, P.frame_number' |
324 queryStatement = '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 OF.object_id, P.frame_number order by OF.object_id, P.frame_number' | 326 cursor.execute(queryStatement) |
327 logging.debug(queryStatement) | |
328 elif trajectoryType in ['bbtop', 'bbbottom']: | |
329 if trajectoryType == 'bbtop': | |
330 corner = 'top_left' | |
331 elif trajectoryType == 'bbbottom': | |
332 corner = 'bottom_right' | |
333 queryStatement = 'SELECT object_id, frame_number, x_'+corner+', y_'+corner+' FROM '+tableName+' '+trajectoryIdQuery+'ORDER BY object_id, frame_number' | |
325 cursor.execute(queryStatement) | 334 cursor.execute(queryStatement) |
326 logging.debug(queryStatement) | 335 logging.debug(queryStatement) |
327 else: | 336 else: |
328 print('no trajectory type was chosen') | 337 print('no trajectory type was chosen') |
329 except sqlite3.OperationalError as error: | 338 except sqlite3.OperationalError as error: |
334 obj = None | 343 obj = None |
335 objects = [] | 344 objects = [] |
336 for row in cursor: | 345 for row in cursor: |
337 if row[0] != objId: | 346 if row[0] != objId: |
338 objId = row[0] | 347 objId = row[0] |
339 if obj: | 348 if obj != None and obj.length() == obj.positions.length(): |
340 objects.append(obj) | 349 objects.append(obj) |
350 elif obj != None: | |
351 print('Object {} is missing {} positions'.format(obj.getNum(), int(obj.length())-obj.positions.length())) | |
341 obj = moving.MovingObject(row[0], timeInterval = moving.TimeInterval(row[1], row[1]), positions = moving.Trajectory([[row[2]],[row[3]]])) | 352 obj = moving.MovingObject(row[0], timeInterval = moving.TimeInterval(row[1], row[1]), positions = moving.Trajectory([[row[2]],[row[3]]])) |
342 else: | 353 else: |
343 obj.timeInterval.last = row[1] | 354 obj.timeInterval.last = row[1] |
344 obj.positions.addPositionXY(row[2],row[3]) | 355 obj.positions.addPositionXY(row[2],row[3]) |
345 | 356 |
346 if obj: | 357 if obj != None and obj.length() == obj.positions.length(): |
347 objects.append(obj) | 358 objects.append(obj) |
359 elif obj != None: | |
360 print('Object {} is missing {} positions'.format(obj.getNum(), int(obj.length())-obj.positions.length())) | |
348 | 361 |
349 return objects | 362 return objects |
350 | 363 |
351 def loadTrajectoriesFromSqlite(filename, trajectoryType, objectNumbers = -1): | 364 def loadUserTypesFromTable(cursor, trajectoryType, objectNumbers): |
352 '''Loads nObjects or the indices in objectNumbers from the database''' | 365 objectIdQuery = getTrajectoryIdQuery(objectNumbers, trajectoryType) |
353 connection = sqlite3.connect(filename) # add test if it open | 366 if objectIdQuery == '': |
367 cursor.execute('SELECT object_id, road_user_type from objects') | |
368 else: | |
369 cursor.execute('SELECT object_id, road_user_type from objects where '+objectIdQuery[7:]) | |
370 userTypes = {} | |
371 for row in cursor: | |
372 userTypes[row[0]] = row[1] | |
373 return userTypes | |
374 | |
375 def loadTrajectoriesFromSqlite(filename, trajectoryType, objectNumbers = None): | |
376 '''Loads the first objectNumbers objects or the indices in objectNumbers from the database''' | |
377 connection = sqlite3.connect(filename) | |
354 | 378 |
355 objects = loadTrajectoriesFromTable(connection, 'positions', trajectoryType, objectNumbers) | 379 objects = loadTrajectoriesFromTable(connection, 'positions', trajectoryType, objectNumbers) |
356 objectVelocities = loadTrajectoriesFromTable(connection, 'velocities', trajectoryType, objectNumbers) | 380 objectVelocities = loadTrajectoriesFromTable(connection, 'velocities', trajectoryType, objectNumbers) |
357 | 381 |
358 if len(objectVelocities) > 0: | 382 if len(objectVelocities) > 0: |
382 | 406 |
383 for obj in objects: | 407 for obj in objects: |
384 obj.featureNumbers = featureNumbers[obj.getNum()] | 408 obj.featureNumbers = featureNumbers[obj.getNum()] |
385 | 409 |
386 # load userType | 410 # load userType |
387 if objectIdQuery == '': | 411 userTypes = loadUserTypesFromTable(cursor, trajectoryType, objectNumbers) |
388 cursor.execute('SELECT object_id, road_user_type from objects') | |
389 else: | |
390 cursor.execute('SELECT object_id, road_user_type from objects where '+objectIdQuery[7:]) | |
391 userTypes = {} | |
392 for row in cursor: | |
393 userTypes[row[0]] = row[1] | |
394 | |
395 for obj in objects: | 412 for obj in objects: |
396 obj.userType = userTypes[obj.getNum()] | 413 obj.userType = userTypes[obj.getNum()] |
397 | 414 |
398 except sqlite3.OperationalError as error: | 415 except sqlite3.OperationalError as error: |
399 printDBError(error) | 416 printDBError(error) |
400 return [] | 417 objects = [] |
401 | 418 |
402 connection.close() | 419 connection.close() |
403 return objects | 420 return objects |
404 | 421 |
405 def removeFromSqlite(filename, dataType): | 422 def loadGroundTruthFromSqlite(filename, gtType, gtNumbers = None): |
406 'Removes some tables in the filename depending on type of data' | 423 'Loads bounding box annotations (ground truth) from an SQLite ' |
424 connection = sqlite3.connect(filename) | |
425 gt = [] | |
426 | |
427 if gtType == 'bb': | |
428 topCorners = loadTrajectoriesFromTable(connection, 'bounding_boxes', 'bbtop', gtNumbers) | |
429 bottomCorners = loadTrajectoriesFromTable(connection, 'bounding_boxes', 'bbbottom', gtNumbers) | |
430 userTypes = loadUserTypesFromTable(connection.cursor(), 'object', gtNumbers) # string format is same as object | |
431 | |
432 for t, b in zip(topCorners, bottomCorners): | |
433 num = t.getNum() | |
434 if t.getNum() == b.getNum(): | |
435 annotation = moving.BBAnnotation(num, t.getTimeInterval(), t, b, userTypes[num]) | |
436 gt.append(annotation) | |
437 else: | |
438 print ('Unknown type of annotation {}'.format(gtType)) | |
439 | |
440 connection.close() | |
441 return gt | |
442 | |
443 def deleteFromSqlite(filename, dataType): | |
444 'Deletes (drops) some tables in the filename depending on type of data' | |
407 import os | 445 import os |
408 if os.path.isfile(filename): | 446 if os.path.isfile(filename): |
409 connection = sqlite3.connect(filename) | 447 connection = sqlite3.connect(filename) |
410 if dataType == 'object': | 448 if dataType == 'object': |
411 dropTables(connection, ['objects', 'objects_features']) | 449 dropTables(connection, ['objects', 'objects_features']) |
523 except sqlite3.OperationalError as error: | 561 except sqlite3.OperationalError as error: |
524 printDBError(error) | 562 printDBError(error) |
525 connection.commit() | 563 connection.commit() |
526 connection.close() | 564 connection.close() |
527 | 565 |
528 def loadBoundingBoxTable(filename): | 566 def loadBoundingBoxTableForDisplay(filename): |
529 connection = sqlite3.connect(filename) | 567 connection = sqlite3.connect(filename) |
530 cursor = connection.cursor() | 568 cursor = connection.cursor() |
531 boundingBoxes = {} # list of bounding boxes for each instant | 569 boundingBoxes = {} # list of bounding boxes for each instant |
532 try: | 570 try: |
533 cursor.execute('SELECT name FROM sqlite_master WHERE type=\'table\' AND name=\'bounding_boxes\'') | 571 cursor.execute('SELECT name FROM sqlite_master WHERE type=\'table\' AND name=\'bounding_boxes\'') |
534 result = [row for row in cursor] | 572 result = [row for row in cursor] |
535 if len(result) > 0: | 573 if len(result) > 0: |
536 cursor.execute('SELECT * FROM bounding_boxes') | 574 cursor.execute('SELECT * FROM bounding_boxes') |
537 #objId = -1 | |
538 for row in cursor: | 575 for row in cursor: |
539 #if row[0] != objId: | |
540 boundingBoxes.setdefault(row[1], []).append([moving.Point(row[2], row[3]), moving.Point(row[4], row[5])]) | 576 boundingBoxes.setdefault(row[1], []).append([moving.Point(row[2], row[3]), moving.Point(row[4], row[5])]) |
577 except sqlite3.OperationalError as error: | |
578 printDBError(error) | |
579 return boundingBoxes | |
580 connection.close() | |
581 return boundingBoxes | |
582 | |
583 def loadBoundingBoxTable(filename): | |
584 connection = sqlite3.connect(filename) | |
585 cursor = connection.cursor() | |
586 boundingBoxes = [] | |
587 | |
588 try: | |
589 pass | |
541 except sqlite3.OperationalError as error: | 590 except sqlite3.OperationalError as error: |
542 printDBError(error) | 591 printDBError(error) |
543 return boundingBoxes | 592 return boundingBoxes |
544 connection.close() | 593 connection.close() |
545 return boundingBoxes | 594 return boundingBoxes |