Mercurial Hosting > traffic-intelligence
comparison 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 |
comparison
equal
deleted
inserted
replaced
829:0ddcc41663f5 | 830:2a5856961933 |
---|---|
44 cursor.execute('SELECT COUNT(*) FROM SQLITE_MASTER WHERE type = \'table\' AND name = \''+tableName+'\'') | 44 cursor.execute('SELECT COUNT(*) FROM SQLITE_MASTER WHERE type = \'table\' AND name = \''+tableName+'\'') |
45 return cursor.fetchone()[0] == 1 | 45 return cursor.fetchone()[0] == 1 |
46 except sqlite3.OperationalError as error: | 46 except sqlite3.OperationalError as error: |
47 printDBError(error) | 47 printDBError(error) |
48 | 48 |
49 def createTrajectoryTable(cursor, tableName): | |
50 if tableName in ['positions', 'velocities']: | |
51 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))") | |
52 else: | |
53 print('Unallowed name {} for trajectory table'.format(tableName)) | |
54 | |
55 def createCurvilinearTrajectoryTable(cursor): | |
56 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))") | |
57 | |
58 def createFeatureCorrespondenceTable(cursor): | |
59 cursor.execute('CREATE TABLE IF NOT EXISTS feature_correspondences (trajectory_id INTEGER, source_dbname VARCHAR, db_trajectory_id INTEGER, PRIMARY KEY(trajectory_id))') | |
60 | |
61 def createInteractionTable(cursor): | |
62 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))') | |
63 | |
64 def createIndicatorTable(cursor): | |
65 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))') | |
66 | |
67 def insertTrajectoryQuery(tableName): | |
68 return "INSERT INTO "+tableName+" (trajectory_id, frame_number, x_coordinate, y_coordinate) VALUES (?,?,?,?)" | |
69 | |
49 def createIndex(connection, tableName, columnName, unique = False): | 70 def createIndex(connection, tableName, columnName, unique = False): |
50 '''Creates an index for the column in the table | 71 '''Creates an index for the column in the table |
51 I will make querying with a condition on this column faster''' | 72 I will make querying with a condition on this column faster''' |
52 try: | 73 try: |
53 #connection = sqlite3.connect(filename) | 74 #connection = sqlite3.connect(filename) |
295 connection = sqlite3.connect(outputFilename) | 316 connection = sqlite3.connect(outputFilename) |
296 try: | 317 try: |
297 cursor = connection.cursor() | 318 cursor = connection.cursor() |
298 | 319 |
299 if trajectoryType == 'feature': | 320 if trajectoryType == 'feature': |
300 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))") | 321 createTrajectoryTable(cursor, "positions") |
301 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))") | 322 createTrajectoryTable(cursor, "velocities") |
302 | 323 |
303 positionQuery = "insert into positions (trajectory_id, frame_number, x_coordinate, y_coordinate) values (?,?,?,?)" | 324 positionQuery = insertTrajectoryQuery("positions") |
304 velocityQuery = "insert into velocities (trajectory_id, frame_number, x_coordinate, y_coordinate) values (?,?,?,?)" | 325 velocityQuery = insertTrajectoryQuery("velocities") |
305 for obj in objects: | 326 for obj in objects: |
306 num = obj.getNum() | 327 num = obj.getNum() |
307 frame_number = obj.getFirstInstant() | 328 frame_number = obj.getFirstInstant() |
308 for position in obj.getPositions(): | 329 for position in obj.getPositions(): |
309 cursor.execute(positionQuery, (num, frame_number, position.x, position.y)) | 330 cursor.execute(positionQuery, (num, frame_number, position.x, position.y)) |
315 for i in xrange(velocities.length()-1): | 336 for i in xrange(velocities.length()-1): |
316 v = velocities[i] | 337 v = velocities[i] |
317 cursor.execute(velocityQuery, (num, frame_number, v.x, v.y)) | 338 cursor.execute(velocityQuery, (num, frame_number, v.x, v.y)) |
318 frame_number += 1 | 339 frame_number += 1 |
319 elif trajectoryType == 'curvilinear': | 340 elif trajectoryType == 'curvilinear': |
320 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))") | 341 createCurvilinearTrajectoryTable(cursor) |
321 curvilinearQuery = "insert into curvilinear_positions (trajectory_id, frame_number, s_coordinate, y_coordinate, lane) values (?,?,?,?,?)" | 342 curvilinearQuery = "insert into curvilinear_positions (trajectory_id, frame_number, s_coordinate, y_coordinate, lane) values (?,?,?,?,?)" |
322 for obj in objects: | 343 for obj in objects: |
323 num = obj.getNum() | 344 num = obj.getNum() |
324 frame_number = obj.getFirstInstant() | 345 frame_number = obj.getFirstInstant() |
325 for position in obj.getCurvilinearPositions(): | 346 for position in obj.getCurvilinearPositions(): |
391 print('Unknown data type {} to delete from database'.format(dataType)) | 412 print('Unknown data type {} to delete from database'.format(dataType)) |
392 connection.close() | 413 connection.close() |
393 else: | 414 else: |
394 print('{} does not exist'.format(filename)) | 415 print('{} does not exist'.format(filename)) |
395 | 416 |
396 def createInteractionTable(cursor): | |
397 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))') | |
398 | |
399 def createIndicatorTables(cursor): | |
400 # 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))') | |
401 # 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))') | |
402 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))') | |
403 | |
404 def saveInteraction(cursor, interaction): | 417 def saveInteraction(cursor, interaction): |
405 roadUserNumbers = list(interaction.getRoadUserNumbers()) | 418 roadUserNumbers = list(interaction.getRoadUserNumbers()) |
406 cursor.execute('INSERT INTO interactions VALUES({}, {}, {}, {}, {})'.format(interaction.getNum(), roadUserNumbers[0], roadUserNumbers[1], interaction.getFirstInstant(), interaction.getLastInstant())) | 419 cursor.execute('INSERT INTO interactions VALUES({}, {}, {}, {}, {})'.format(interaction.getNum(), roadUserNumbers[0], roadUserNumbers[1], interaction.getFirstInstant(), interaction.getLastInstant())) |
407 | 420 |
408 def saveInteractions(filename, interactions): | 421 def saveInteractions(filename, interactions): |
427 'Saves the indicator values in the table' | 440 'Saves the indicator values in the table' |
428 connection = sqlite3.connect(filename) | 441 connection = sqlite3.connect(filename) |
429 cursor = connection.cursor() | 442 cursor = connection.cursor() |
430 try: | 443 try: |
431 createInteractionTable(cursor) | 444 createInteractionTable(cursor) |
432 createIndicatorTables(cursor) | 445 createIndicatorTable(cursor) |
433 for inter in interactions: | 446 for inter in interactions: |
434 saveInteraction(cursor, inter) | 447 saveInteraction(cursor, inter) |
435 for indicatorName in indicatorNames: | 448 for indicatorName in indicatorNames: |
436 indicator = inter.getIndicator(indicatorName) | 449 indicator = inter.getIndicator(indicatorName) |
437 if indicator is not None: | 450 if indicator is not None: |