Mercurial > hg > nsaunier > traffic-intelligence

comparison python/storage.py @ 830:2a5856961933

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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: