Mercurial Hosting > traffic-intelligence
diff python/storage.py @ 830:2a5856961933
first working version of feature merging (works with feature grouping)
author | Nicolas Saunier <nicolas.saunier@polymtl.ca> |
---|---|
date | Wed, 29 Jun 2016 17:56:19 -0400 |
parents | 0ddcc41663f5 |
children | a8ff35e6fb43 |
line wrap: on
line diff
--- a/python/storage.py Wed Jun 29 13:50:21 2016 -0400 +++ b/python/storage.py Wed Jun 29 17:56:19 2016 -0400 @@ -46,6 +46,27 @@ except sqlite3.OperationalError as error: printDBError(error) +def createTrajectoryTable(cursor, tableName): + if tableName in ['positions', 'velocities']: + cursor.execute("CREATE TABLE IF NOT EXISTS "+tableName+" (trajectory_id INTEGER, frame_number INTEGER, x_coordinate REAL, y_coordinate REAL, PRIMARY KEY(trajectory_id, frame_number))") + else: + print('Unallowed name {} for trajectory table'.format(tableName)) + +def createCurvilinearTrajectoryTable(cursor): + 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))") + +def createFeatureCorrespondenceTable(cursor): + cursor.execute('CREATE TABLE IF NOT EXISTS feature_correspondences (trajectory_id INTEGER, source_dbname VARCHAR, db_trajectory_id INTEGER, PRIMARY KEY(trajectory_id))') + +def createInteractionTable(cursor): + 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))') + +def createIndicatorTable(cursor): + cursor.execute('CREATE TABLE IF NOT EXISTS indicators (interaction_id INTEGER, indicator_type INTEGER, frame_number INTEGER, value REAL, FOREIGN KEY(interaction_id) REFERENCES interactions(id), PRIMARY KEY(interaction_id, indicator_type, frame_number))') + +def insertTrajectoryQuery(tableName): + return "INSERT INTO "+tableName+" (trajectory_id, frame_number, x_coordinate, y_coordinate) VALUES (?,?,?,?)" + def createIndex(connection, tableName, columnName, unique = False): '''Creates an index for the column in the table I will make querying with a condition on this column faster''' @@ -297,11 +318,11 @@ cursor = connection.cursor() if trajectoryType == 'feature': - cursor.execute("CREATE TABLE IF NOT EXISTS positions (trajectory_id INTEGER, frame_number INTEGER, x_coordinate REAL, y_coordinate REAL, PRIMARY KEY(trajectory_id, frame_number))") - cursor.execute("CREATE TABLE IF NOT EXISTS velocities (trajectory_id INTEGER, frame_number INTEGER, x_coordinate REAL, y_coordinate REAL, PRIMARY KEY(trajectory_id, frame_number))") + createTrajectoryTable(cursor, "positions") + createTrajectoryTable(cursor, "velocities") - positionQuery = "insert into positions (trajectory_id, frame_number, x_coordinate, y_coordinate) values (?,?,?,?)" - velocityQuery = "insert into velocities (trajectory_id, frame_number, x_coordinate, y_coordinate) values (?,?,?,?)" + positionQuery = insertTrajectoryQuery("positions") + velocityQuery = insertTrajectoryQuery("velocities") for obj in objects: num = obj.getNum() frame_number = obj.getFirstInstant() @@ -317,7 +338,7 @@ cursor.execute(velocityQuery, (num, frame_number, v.x, v.y)) frame_number += 1 elif trajectoryType == 'curvilinear': - 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))") + createCurvilinearTrajectoryTable(cursor) curvilinearQuery = "insert into curvilinear_positions (trajectory_id, frame_number, s_coordinate, y_coordinate, lane) values (?,?,?,?,?)" for obj in objects: num = obj.getNum() @@ -393,14 +414,6 @@ else: print('{} does not exist'.format(filename)) -def createInteractionTable(cursor): - 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))') - -def createIndicatorTables(cursor): - # cursor.execute('CREATE TABLE IF NOT EXISTS indicators (id INTEGER PRIMARY KEY, interaction_id INTEGER, indicator_type INTEGER, FOREIGN KEY(interaction_id) REFERENCES interactions(id))') - # cursor.execute('CREATE TABLE IF NOT EXISTS indicator_values (indicator_id INTEGER, frame_number INTEGER, value REAL, FOREIGN KEY(indicator_id) REFERENCES indicators(id), PRIMARY KEY(indicator_id, frame_number))') - cursor.execute('CREATE TABLE IF NOT EXISTS indicators (interaction_id INTEGER, indicator_type INTEGER, frame_number INTEGER, value REAL, FOREIGN KEY(interaction_id) REFERENCES interactions(id), PRIMARY KEY(interaction_id, indicator_type, frame_number))') - def saveInteraction(cursor, interaction): roadUserNumbers = list(interaction.getRoadUserNumbers()) cursor.execute('INSERT INTO interactions VALUES({}, {}, {}, {}, {})'.format(interaction.getNum(), roadUserNumbers[0], roadUserNumbers[1], interaction.getFirstInstant(), interaction.getLastInstant())) @@ -429,7 +442,7 @@ cursor = connection.cursor() try: createInteractionTable(cursor) - createIndicatorTables(cursor) + createIndicatorTable(cursor) for inter in interactions: saveInteraction(cursor, inter) for indicatorName in indicatorNames: