Mercurial > hg > nsaunier > traffic-intelligence

comparison python/storage.py @ 917:89cc05867c4c

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
reorg and work in progress
author Nicolas Saunier <nicolas.saunier@polymtl.ca>
date Tue, 04 Jul 2017 18:00:01 -0400
parents 13434f5017dd
children 3a06007a4bb7
comparison
equal deleted inserted replaced
916:7345f0d51faa 917:89cc05867c4c
37 cursor = connection.cursor() 37 cursor = connection.cursor()
38 for tableName in tableNames: 38 for tableName in tableNames:
39 cursor.execute('DROP TABLE IF EXISTS '+tableName) 39 cursor.execute('DROP TABLE IF EXISTS '+tableName)
40 except sqlite3.OperationalError as error: 40 except sqlite3.OperationalError as error:
41 printDBError(error) 41 printDBError(error)
42
43 def deleteFromSqlite(filename, dataType):
44 'Deletes (drops) some tables in the filename depending on type of data'
45 if path.isfile(filename):
46 connection = sqlite3.connect(filename)
47 if dataType == 'object':
48 dropTables(connection, ['objects', 'objects_features'])
49 elif dataType == 'interaction':
50 dropTables(connection, ['interactions', 'indicators'])
51 elif dataType == 'bb':
52 dropTables(connection, ['bounding_boxes'])
53 elif dataType == 'pois':
54 dropTables(connection, ['gaussians2d', 'objects_pois'])
55 elif dataType == 'prototype':
56 dropTables(connection, ['prototypes'])
57 else:
58 print('Unknown data type {} to delete from database'.format(dataType))
59 connection.close()
60 else:
61 print('{} does not exist'.format(filename))
42 62
43 def tableExists(filename, tableName): 63 def tableExists(filename, tableName):
44 'indicates if the table exists in the database' 64 'indicates if the table exists in the database'
45 try: 65 try:
46 connection = sqlite3.connect(filename) 66 connection = sqlite3.connect(filename)
352 missingObjectNumbers.append(objNum) 372 missingObjectNumbers.append(objNum)
353 if objNum in objects: 373 if objNum in objects:
354 objects[objNum].curvilinearPositions.addPositionSYL(row[2],row[3],row[4]) 374 objects[objNum].curvilinearPositions.addPositionSYL(row[2],row[3],row[4])
355 if len(missingObjectNumbers) > 0: 375 if len(missingObjectNumbers) > 0:
356 print('List of missing objects to attach corresponding curvilinear trajectories: {}'.format(missingObjectNumbers)) 376 print('List of missing objects to attach corresponding curvilinear trajectories: {}'.format(missingObjectNumbers))
357
358 377
359 def saveTrajectoriesToSqlite(outputFilename, objects, trajectoryType, withFeatures = False): 378 def saveTrajectoriesToSqlite(outputFilename, objects, trajectoryType, withFeatures = False):
360 '''Writes features, ie the trajectory positions (and velocities if exist) 379 '''Writes features, ie the trajectory positions (and velocities if exist)
361 with their instants to a specified sqlite file 380 with their instants to a specified sqlite file
362 Either feature positions (and velocities if they exist) 381 Either feature positions (and velocities if they exist)
420 connection.commit() 439 connection.commit()
421 except sqlite3.OperationalError as error: 440 except sqlite3.OperationalError as error:
422 printDBError(error) 441 printDBError(error)
423 connection.close() 442 connection.close()
424 443
425 def savePrototypesToSqlite(filename, prototypeIndices, trajectoryType, nMatchings = None, dbFilenames = None):
426 '''save the prototype indices
427 nMatchings, if not None, is a list of the number of matches
428 dbFilenames, if not None, is a list of the DB filenames'''
429 connection = sqlite3.connect(filename)
430 cursor = connection.cursor()
431 try:
432 cursor.execute('CREATE TABLE IF NOT EXISTS prototypes (id INTEGER, dbfilename VARCHAR, trajectory_type VARCHAR CHECK (trajectory_type IN (\"feature\", \"object\")), nMatchings INTEGER, PRIMARY KEY (id, dbfilename))')
433 for i, protoId in enumerate(prototypeIndices):
434 if nMatchings is not None:
435 n = nMatchings[i]
436 else:
437 n = 'NULL'
438 if dbFilenames is not None:
439 dbfn = dbFilenames[i]
440 else:
441 dbfn = filename
442 cursor.execute('INSERT INTO prototypes (id, dbfilename, trajectory_type, nMatchings) VALUES ({},\"{}\",\"{}\",{})'.format(protoId, dbfn, trajectoryType, n))
443 cursor.execute('SELECT * from sqlite_master WHERE type = \"table\" and name = \"{}\"'.format(tableNames[trajectoryType]))
444 if len(cursor.fetchall()) == 0:
445 pass # save prototype trajectory data
446 except sqlite3.OperationalError as error:
447 printDBError(error)
448 connection.commit()
449 connection.close()
450
451 def loadPrototypesFromSqlite(filename):
452 'Loads prototype ids and matchings (if stored)'
453 connection = sqlite3.connect(filename)
454 cursor = connection.cursor()
455 prototypeIndices = []
456 dbFilenames = []
457 trajectoryTypes = []
458 nMatchings = []
459 try:
460 cursor.execute('SELECT * FROM prototypes')
461 for row in cursor:
462 prototypeIndices.append(row[0])
463 dbFilenames.append(row[1])
464 trajectoryTypes.append(row[2])
465 if row[3] is not None:
466 nMatchings.append(row[3])
467 except sqlite3.OperationalError as error:
468 printDBError(error)
469 connection.close()
470 if len(set(trajectoryTypes)) > 1:
471 print('Different types of prototypes in database ({}).'.format(set(trajectoryTypes)))
472 return prototypeIndices, dbFilenames, trajectoryTypes, nMatchings
473
474 def loadBBMovingObjectsFromSqlite(filename, objectType = 'bb', objectNumbers = None, timeStep = None): 444 def loadBBMovingObjectsFromSqlite(filename, objectType = 'bb', objectNumbers = None, timeStep = None):
475 '''Loads bounding box moving object from an SQLite 445 '''Loads bounding box moving object from an SQLite
476 (format of SQLite output by the ground truth annotation tool 446 (format of SQLite output by the ground truth annotation tool
477 or Urban Tracker 447 or Urban Tracker
478 448
493 else: 463 else:
494 print ('Unknown type of bounding box {}'.format(objectType)) 464 print ('Unknown type of bounding box {}'.format(objectType))
495 465
496 connection.close() 466 connection.close()
497 return objects 467 return objects
498
499 def deleteFromSqlite(filename, dataType):
500 'Deletes (drops) some tables in the filename depending on type of data'
501 if path.isfile(filename):
502 connection = sqlite3.connect(filename)
503 if dataType == 'object':
504 dropTables(connection, ['objects', 'objects_features'])
505 elif dataType == 'interaction':
506 dropTables(connection, ['interactions', 'indicators'])
507 elif dataType == 'bb':
508 dropTables(connection, ['bounding_boxes'])
509 elif dataType == 'pois':
510 dropTables(connection, ['gaussians2d', 'objects_pois'])
511 elif dataType == 'prototype':
512 dropTables(connection, ['prototypes'])
513 else:
514 print('Unknown data type {} to delete from database'.format(dataType))
515 connection.close()
516 else:
517 print('{} does not exist'.format(filename))
518 468
519 def saveInteraction(cursor, interaction): 469 def saveInteraction(cursor, interaction):
520 roadUserNumbers = list(interaction.getRoadUserNumbers()) 470 roadUserNumbers = list(interaction.getRoadUserNumbers())
521 cursor.execute('INSERT INTO interactions VALUES({}, {}, {}, {}, {})'.format(interaction.getNum(), roadUserNumbers[0], roadUserNumbers[1], interaction.getFirstInstant(), interaction.getLastInstant())) 471 cursor.execute('INSERT INTO interactions VALUES({}, {}, {}, {}, {})'.format(interaction.getNum(), roadUserNumbers[0], roadUserNumbers[1], interaction.getFirstInstant(), interaction.getLastInstant()))
522 472
622 return boundingBoxes 572 return boundingBoxes
623 connection.close() 573 connection.close()
624 return boundingBoxes 574 return boundingBoxes
625 575
626 ######################### 576 #########################
627 # saving and loading for scene interpretation 577 # saving and loading for scene interpretation: POIs and Prototypes
628 ######################### 578 #########################
579
580 def savePrototypesToSqlite(filename, prototypeIndices, trajectoryType, objects = None, nMatchings = None, dbFilenames = None):
581 '''save the prototype indices
582 if objects is not None, the trajectories are also saved in prototype_positions and _velocities
583 (prototypeIndices have to be in objects)
584 nMatchings, if not None, is a list of the number of matches
585 dbFilenames, if not None, is a list of the DB filenames'''
586 connection = sqlite3.connect(filename)
587 cursor = connection.cursor()
588 try:
589 cursor.execute('CREATE TABLE IF NOT EXISTS prototypes (id INTEGER, dbfilename VARCHAR, trajectory_type VARCHAR CHECK (trajectory_type IN (\"feature\", \"object\")), nmatchings INTEGER, positions_id INTEGER, PRIMARY KEY (id, dbfilename))')
590 for i, protoId in enumerate(prototypeIndices):
591 if nMatchings is not None:
592 n = nMatchings[i]
593 else:
594 n = 'NULL'
595 if dbFilenames is not None:
596 dbfn = dbFilenames[i]
597 else:
598 dbfn = filename
599 cursor.execute('INSERT INTO prototypes (id, dbfilename, trajectory_type, nmatchings, positions_id) VALUES ({},\"{}\",\"{}\",{}, {})'.format(protoId, dbfn, trajectoryType, n, i))
600 #cursor.execute('SELECT * from sqlite_master WHERE type = \"table\" and name = \"{}\"'.format(tableNames[trajectoryType]))
601 if objects is not None:
602 pass
603 except sqlite3.OperationalError as error:
604 printDBError(error)
605 connection.commit()
606 connection.close()
607
608 def savePrototypeAssignments(filename, objects):
609 pass
610
611 def loadPrototypesFromSqlite(filename):
612 'Loads prototype ids and matchings (if stored)'
613 connection = sqlite3.connect(filename)
614 cursor = connection.cursor()
615 prototypeIndices = []
616 dbFilenames = []
617 trajectoryTypes = []
618 nMatchings = []
619 try:
620 cursor.execute('SELECT * FROM prototypes')
621 for row in cursor:
622 prototypeIndices.append(row[0])
623 dbFilenames.append(row[1])
624 trajectoryTypes.append(row[2])
625 if row[3] is not None:
626 nMatchings.append(row[3])
627 except sqlite3.OperationalError as error:
628 printDBError(error)
629 connection.close()
630 if len(set(trajectoryTypes)) > 1:
631 print('Different types of prototypes in database ({}).'.format(set(trajectoryTypes)))
632 return prototypeIndices, dbFilenames, trajectoryTypes, nMatchings
629 633
630 def savePOIs(filename, gmm, gmmType, gmmId): 634 def savePOIs(filename, gmm, gmmType, gmmId):
631 '''Saves a Gaussian mixture model (of class sklearn.mixture.GaussianMixture) 635 '''Saves a Gaussian mixture model (of class sklearn.mixture.GaussianMixture)
632 gmmType is a type of GaussianMixture, learnt either from beginnings or ends of trajectories''' 636 gmmType is a type of GaussianMixture, learnt either from beginnings or ends of trajectories'''
633 connection = sqlite3.connect(filename) 637 connection = sqlite3.connect(filename)