Mercurial Hosting > traffic-intelligence

comparison python/storage.py @ 879:f9ea5083588e

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Initial commit of object DB storage update
author pstaub
date Tue, 14 Mar 2017 14:22:14 -0400
parents c70adaeeddf5
children 000555430b28
comparison
equal deleted inserted replaced
878:8e8ec4ece66e 879:f9ea5083588e
50 if tableName in ['positions', 'velocities']: 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))") 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: 52 else:
53 print('Unallowed name {} for trajectory table'.format(tableName)) 53 print('Unallowed name {} for trajectory table'.format(tableName))
54 54
55 def createObjectsTable(cursor):
56 cursor.execute("CREATE TABLE IF NOT EXISTS objects (object_id INTEGER, road_user_type INTEGER, n_objects INTEGER, PRIMARY KEY(object_id))")
57
58 def createObjectsFeaturesTable(cursor):
59 cursor.execute("CREATE TABLE IF NOT EXISTS objects_features (object_id INTEGER, trajectory_id INTEGER, PRIMARY KEY(trajectory_id))")
60
61
55 def createCurvilinearTrajectoryTable(cursor): 62 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))") 63 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 64
58 def createFeatureCorrespondenceTable(cursor): 65 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))') 66 cursor.execute('CREATE TABLE IF NOT EXISTS feature_correspondences (trajectory_id INTEGER, source_dbname VARCHAR, db_trajectory_id INTEGER, PRIMARY KEY(trajectory_id))')
64 def createIndicatorTable(cursor): 71 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))') 72 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 73
67 def insertTrajectoryQuery(tableName): 74 def insertTrajectoryQuery(tableName):
68 return "INSERT INTO "+tableName+" (trajectory_id, frame_number, x_coordinate, y_coordinate) VALUES (?,?,?,?)" 75 return "INSERT INTO "+tableName+" (trajectory_id, frame_number, x_coordinate, y_coordinate) VALUES (?,?,?,?)"
76
77 def insertObjectQuery():
78 return "INSERT INTO objects (object_id, road_user_type, n_objects) VALUES (?,?,?)"
79
80 def insertObjectFeatureQuery():
81 return "INSERT INTO objects_features (object_id, trajectory_id) VALUES (?,?)"
69 82
70 def createIndex(connection, tableName, columnName, unique = False): 83 def createIndex(connection, tableName, columnName, unique = False):
71 '''Creates an index for the column in the table 84 '''Creates an index for the column in the table
72 I will make querying with a condition on this column faster''' 85 I will make querying with a condition on this column faster'''
73 try: 86 try:
311 if objNum in objects: 324 if objNum in objects:
312 objects[objNum].curvilinearPositions.addPositionSYL(row[2],row[3],row[4]) 325 objects[objNum].curvilinearPositions.addPositionSYL(row[2],row[3],row[4])
313 if len(missingObjectNumbers) > 0: 326 if len(missingObjectNumbers) > 0:
314 print('List of missing objects to attach corresponding curvilinear trajectories: {}'.format(missingObjectNumbers)) 327 print('List of missing objects to attach corresponding curvilinear trajectories: {}'.format(missingObjectNumbers))
315 328
329
316 def saveTrajectoriesToSqlite(outputFilename, objects, trajectoryType, withFeatures = False): 330 def saveTrajectoriesToSqlite(outputFilename, objects, trajectoryType, withFeatures = False):
317 '''Writes features, ie the trajectory positions (and velocities if exist) 331 '''Writes features, ie the trajectory positions (and velocities if exist)
318 with their instants to a specified sqlite file 332 with their instants to a specified sqlite file
319 Either feature positions (and velocities if they exist) 333 Either feature positions (and velocities if they exist)
320 or curvilinear positions will be saved at a time 334 or curvilinear positions will be saved at a time'''
321 335
322 TODO: Not implemented for trajectoryType MovingObject with features 336 ## Sanitation
323 For objects, with features will control whether the features 337 if(type(objects) is not list or len(objects)==0): return False
324 corresponding to the object are also saved'''
325
326 connection = sqlite3.connect(outputFilename) 338 connection = sqlite3.connect(outputFilename)
327 try: 339 try:
328 cursor = connection.cursor() 340 cursor = connection.cursor()
329 341 ## Extract features from objects
330 if trajectoryType == 'feature': 342 if(trajectoryType == 'object'):
343 features = []
344 for obj in objects:
345 if(obj.hasFeatures()):
346 features += obj.getFeatures()
347 elif(trajectoryType == 'feature'):
348 features = objects
349 ## Setup feature queries
350 if(trajectoryType == 'feature' or trajectoryType == 'object'):
331 createTrajectoryTable(cursor, "positions") 351 createTrajectoryTable(cursor, "positions")
332 createTrajectoryTable(cursor, "velocities") 352 createTrajectoryTable(cursor, "velocities")
333
334 positionQuery = insertTrajectoryQuery("positions") 353 positionQuery = insertTrajectoryQuery("positions")
335 velocityQuery = insertTrajectoryQuery("velocities") 354 velocityQuery = insertTrajectoryQuery("velocities")
336 for obj in objects: 355 ## Setup object queries
337 num = obj.getNum() 356 if(trajectoryType == 'object'):
338 frame_number = obj.getFirstInstant() 357 createObjectsTable(cursor)
339 for position in obj.getPositions(): 358 createObjectsFeaturesTable(cursor)
340 cursor.execute(positionQuery, (num, frame_number, position.x, position.y)) 359 objectQuery = insertObjectQuery()
341 frame_number += 1 360 objectFeatureQuery = insertObjectFeatureQuery()
342 # velocities 361 ## Parse feature and/or object structure and commit to DB
343 velocities = obj.getVelocities() 362 if(trajectoryType == 'feature' or trajectoryType == 'object'):
344 if velocities is not None: 363 running_tally_of_unique_nums = []
345 frame_number = obj.getFirstInstant() 364 for feature in features:
346 for i in xrange(velocities.length()-1): 365 num = feature.getNum()
347 v = velocities[i] 366 if(num not in running_tally_of_unique_nums):
348 cursor.execute(velocityQuery, (num, frame_number, v.x, v.y)) 367 running_tally_of_unique_nums.append(num)
368 frame_number = feature.getFirstInstant()
369 for position in feature.getPositions():
370 cursor.execute(positionQuery, (num, frame_number, position.x, position.y))
349 frame_number += 1 371 frame_number += 1
350 elif trajectoryType == 'curvilinear': 372 velocities = feature.getVelocities()
373 if velocities is not None:
374 frame_number = feature.getFirstInstant()
375 for i in xrange(velocities.length()-1):
376 v = velocities[i]
377 cursor.execute(velocityQuery, (num, frame_number, v.x, v.y))
378 frame_number += 1
379 if(trajectoryType == 'object'):
380 running_tally_of_unique_nums = []
381 for obj in objects:
382 if(obj.hasFeatures()):
383 n_objects = len(obj.getFeatures())
384 for feature in obj.getFeatures():
385 feature_num = feature.getNum()
386 if(feature_num not in running_tally_of_unique_nums):
387 running_tally_of_unique_nums.append(feature_num)
388 cursor.execute(objectFeatureQuery, (obj.getNum(), feature_num))
389 else:
390 n_objects = 0
391 cursor.execute(objectQuery, (obj.getNum(), obj.getUserType(), n_objects))
392 ## Parse curvilinear position structure
393 elif(trajectoryType == 'curvilinear'):
351 createCurvilinearTrajectoryTable(cursor) 394 createCurvilinearTrajectoryTable(cursor)
352 curvilinearQuery = "insert into curvilinear_positions (trajectory_id, frame_number, s_coordinate, y_coordinate, lane) values (?,?,?,?,?)" 395 curvilinearQuery = "insert into curvilinear_positions (trajectory_id, frame_number, s_coordinate, y_coordinate, lane) values (?,?,?,?,?)"
353 for obj in objects: 396 for obj in objects:
354 num = obj.getNum() 397 num = obj.getNum()
355 frame_number = obj.getFirstInstant() 398 frame_number = obj.getFirstInstant()
356 for position in obj.getCurvilinearPositions(): 399 for position in obj.getCurvilinearPositions():
357 cursor.execute(curvilinearQuery, (num, frame_number, position[0], position[1], position[2])) 400 cursor.execute(curvilinearQuery, (num, frame_number, position[0], position[1], position[2]))
358 frame_number += 1 401 frame_number += 1
359 #elif trajectoryType == 'object':
360 else: 402 else:
361 print('Unknown trajectory type {}'.format(trajectoryType)) 403 print('Unknown trajectory type {}'.format(trajectoryType))
362 connection.commit() 404 connection.commit()
363 except sqlite3.OperationalError as error: 405 except sqlite3.OperationalError as error:
364 printDBError(error) 406 printDBError(error)