Mercurial Hosting > traffic-intelligence

comparison python/storage.py @ 979:cc89267b5ff9

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
work on learning and assigning
author Nicolas Saunier <nicolas.saunier@polymtl.ca>
date Mon, 19 Feb 2018 10:47:19 -0500
parents 184f1dd307f9
children 933670761a57
comparison
equal deleted inserted replaced
978:184f1dd307f9 979:cc89267b5ff9
76 print('Unallowed name {} for trajectory table'.format(tableName)) 76 print('Unallowed name {} for trajectory table'.format(tableName))
77 77
78 def createObjectsTable(cursor): 78 def createObjectsTable(cursor):
79 cursor.execute("CREATE TABLE IF NOT EXISTS objects (object_id INTEGER, road_user_type INTEGER, n_objects INTEGER, PRIMARY KEY(object_id))") 79 cursor.execute("CREATE TABLE IF NOT EXISTS objects (object_id INTEGER, road_user_type INTEGER, n_objects INTEGER, PRIMARY KEY(object_id))")
80 80
81 def createAssignmentTable(cursor, objectType1, objectType2, columnName1, columnName2): 81 def createAssignmentTable(cursor, objectType1, objectType2, objectIdColumnName1, objectIdColumnName2):
82 cursor.execute("CREATE TABLE IF NOT EXISTS "+objectType1+"s_"+objectType2+"s ("+columnName1+" INTEGER, "+columnName1+" INTEGER, PRIMARY KEY("+columnName1+","+columnName2+"))") 82 cursor.execute("CREATE TABLE IF NOT EXISTS "+objectType1+"s_"+objectType2+"s ("+objectIdColumnName1+" INTEGER, "+objectIdColumnName2+" INTEGER, PRIMARY KEY("+objectIdColumnName1+","+objectIdColumnName2+"))")
83 83
84 def createObjectsFeaturesTable(cursor): # same as 84 def createObjectsFeaturesTable(cursor):
85 cursor.execute("CREATE TABLE IF NOT EXISTS objects_features (object_id INTEGER, trajectory_id INTEGER, PRIMARY KEY(object_id, trajectory_id))") 85 cursor.execute("CREATE TABLE IF NOT EXISTS objects_features (object_id INTEGER, trajectory_id INTEGER, PRIMARY KEY(object_id, trajectory_id))")
86 86
87 87
88 def createCurvilinearTrajectoryTable(cursor): 88 def createCurvilinearTrajectoryTable(cursor):
89 cursor.execute("CREATE TABLE IF NOT EXISTS curvilinear_positions (trajectory_id INTEGER, frame_number INTEGER, s_coordinate REAL, y_coordinate REAL, lane TEXT, PRIMARY KEY(trajectory_id, frame_number))") 89 cursor.execute("CREATE TABLE IF NOT EXISTS curvilinear_positions (trajectory_id INTEGER, frame_number INTEGER, s_coordinate REAL, y_coordinate REAL, lane TEXT, PRIMARY KEY(trajectory_id, frame_number))")
563 ######################### 563 #########################
564 # saving and loading for scene interpretation: POIs and Prototypes 564 # saving and loading for scene interpretation: POIs and Prototypes
565 ######################### 565 #########################
566 566
567 def savePrototypesToSqlite(filename, prototypes): 567 def savePrototypesToSqlite(filename, prototypes):
568 '''save the prototypes (a prototype is defined by a filename, a number and type''' 568 '''save the prototypes (a prototype is defined by a filename, a number (id) and type'''
569 with sqlite3.connect(filename) as connection: 569 with sqlite3.connect(filename) as connection:
570 cursor = connection.cursor() 570 cursor = connection.cursor()
571 try: 571 try:
572 cursor.execute('CREATE TABLE IF NOT EXISTS prototypes (prototype_filename VARCHAR, prototype_id INTEGER, trajectory_type VARCHAR CHECK (trajectory_type IN (\"feature\", \"object\")), nmatchings INTEGER, PRIMARY KEY (prototype_filename, prototype_id, trajectory_type))') 572 cursor.execute('CREATE TABLE IF NOT EXISTS prototypes (prototype_filename VARCHAR, prototype_id INTEGER, trajectory_type VARCHAR CHECK (trajectory_type IN (\"feature\", \"object\")), nmatchings INTEGER, PRIMARY KEY (prototype_filename, prototype_id, trajectory_type))')
573 for p in prototypes: 573 for p in prototypes:
574 cursor.execute('INSERT INTO prototypes VALUES(?,?,?,?)', (p.getFilename(), p.getNum(), p.getTrajectoryType(), p.getNMatchings())) 574 cursor.execute('INSERT INTO prototypes VALUES(?,?,?,?)', (p.getFilename(), p.getNum(), p.getTrajectoryType(), p.getNMatchings()))
575 except sqlite3.OperationalError as error: 575 except sqlite3.OperationalError as error:
576 printDBError(error) 576 printDBError(error)
577 connection.commit() 577 connection.commit()
578 578
579 def savePrototypeAssignmentsToSqlite(filename, objects, labels, prototypes): 579 def savePrototypeAssignmentsToSqlite(filename, objects, objectType, labels, prototypes):
580 with sqlite3.connect(filename) as connection: 580 with sqlite3.connect(filename) as connection:
581 cursor = connection.cursor() 581 cursor = connection.cursor()
582 try: 582 try:
583 cursor.execute('CREATE TABLE IF NOT EXISTS objects_prototypes (object_id INTEGER, prototype_filename VARCHAR, prototype_id INTEGER, trajectory_type VARCHAR CHECK (trajectory_type IN (\"feature\", \"object\")), PRIMARY KEY(object_id, prototype_filename, prototype_id, trajectory_type))') 583 if objectType == 'feature':
584 tableName = 'features_prototypes'
585 objectIdColumnName = 'trajectory_id'
586 elif objectType == 'object':
587 tableName = 'objects_prototypes'
588 objectIdColumnName = 'object_id'
589 cursor.execute('CREATE TABLE IF NOT EXISTS '+tableName+' ('+objectIdColumnName+' INTEGER, prototype_filename VARCHAR, prototype_id INTEGER, trajectory_type VARCHAR CHECK (trajectory_type IN (\"feature\", \"object\")), PRIMARY KEY('+objectIdColumnName+', prototype_filename, prototype_id, trajectory_type))')
584 for obj, label in zip(objects, labels): 590 for obj, label in zip(objects, labels):
585 proto = prototypes[label] 591 proto = prototypes[label]
586 cursor.execute('INSERT INTO objects_prototypes VALUES(?,?,?,?)', (obj.getNum(), proto.getFilename(), proto.getNum(), proto.getTrajectoryType())) 592 cursor.execute('INSERT INTO objects_prototypes VALUES(?,?,?,?)', (obj.getNum(), proto.getFilename(), proto.getNum(), proto.getTrajectoryType()))
587 except sqlite3.OperationalError as error: 593 except sqlite3.OperationalError as error:
588 printDBError(error) 594 printDBError(error)