Mercurial Hosting > traffic-intelligence
changeset 879:f9ea5083588e
Initial commit of object DB storage update
author | pstaub |
---|---|
date | Tue, 14 Mar 2017 14:22:14 -0400 |
parents | 8e8ec4ece66e |
children | 000555430b28 |
files | python/storage.py |
diffstat | 1 files changed, 65 insertions(+), 23 deletions(-) [+] |
line wrap: on
line diff
--- a/python/storage.py Fri Mar 10 15:31:26 2017 -0500 +++ b/python/storage.py Tue Mar 14 14:22:14 2017 -0400 @@ -52,6 +52,13 @@ else: print('Unallowed name {} for trajectory table'.format(tableName)) +def createObjectsTable(cursor): + cursor.execute("CREATE TABLE IF NOT EXISTS objects (object_id INTEGER, road_user_type INTEGER, n_objects INTEGER, PRIMARY KEY(object_id))") + +def createObjectsFeaturesTable(cursor): + cursor.execute("CREATE TABLE IF NOT EXISTS objects_features (object_id INTEGER, trajectory_id INTEGER, PRIMARY KEY(trajectory_id))") + + 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))") @@ -67,6 +74,12 @@ def insertTrajectoryQuery(tableName): return "INSERT INTO "+tableName+" (trajectory_id, frame_number, x_coordinate, y_coordinate) VALUES (?,?,?,?)" +def insertObjectQuery(): + return "INSERT INTO objects (object_id, road_user_type, n_objects) VALUES (?,?,?)" + +def insertObjectFeatureQuery(): + return "INSERT INTO objects_features (object_id, trajectory_id) 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''' @@ -313,41 +326,71 @@ if len(missingObjectNumbers) > 0: print('List of missing objects to attach corresponding curvilinear trajectories: {}'.format(missingObjectNumbers)) + def saveTrajectoriesToSqlite(outputFilename, objects, trajectoryType, withFeatures = False): '''Writes features, ie the trajectory positions (and velocities if exist) with their instants to a specified sqlite file Either feature positions (and velocities if they exist) - or curvilinear positions will be saved at a time + or curvilinear positions will be saved at a time''' - TODO: Not implemented for trajectoryType MovingObject with features - For objects, with features will control whether the features - corresponding to the object are also saved''' - + ## Sanitation + if(type(objects) is not list or len(objects)==0): return False connection = sqlite3.connect(outputFilename) try: cursor = connection.cursor() - - if trajectoryType == 'feature': + ## Extract features from objects + if(trajectoryType == 'object'): + features = [] + for obj in objects: + if(obj.hasFeatures()): + features += obj.getFeatures() + elif(trajectoryType == 'feature'): + features = objects + ## Setup feature queries + if(trajectoryType == 'feature' or trajectoryType == 'object'): createTrajectoryTable(cursor, "positions") createTrajectoryTable(cursor, "velocities") - positionQuery = insertTrajectoryQuery("positions") velocityQuery = insertTrajectoryQuery("velocities") - for obj in objects: - num = obj.getNum() - frame_number = obj.getFirstInstant() - for position in obj.getPositions(): - cursor.execute(positionQuery, (num, frame_number, position.x, position.y)) - frame_number += 1 - # velocities - velocities = obj.getVelocities() - if velocities is not None: - frame_number = obj.getFirstInstant() - for i in xrange(velocities.length()-1): - v = velocities[i] - cursor.execute(velocityQuery, (num, frame_number, v.x, v.y)) + ## Setup object queries + if(trajectoryType == 'object'): + createObjectsTable(cursor) + createObjectsFeaturesTable(cursor) + objectQuery = insertObjectQuery() + objectFeatureQuery = insertObjectFeatureQuery() + ## Parse feature and/or object structure and commit to DB + if(trajectoryType == 'feature' or trajectoryType == 'object'): + running_tally_of_unique_nums = [] + for feature in features: + num = feature.getNum() + if(num not in running_tally_of_unique_nums): + running_tally_of_unique_nums.append(num) + frame_number = feature.getFirstInstant() + for position in feature.getPositions(): + cursor.execute(positionQuery, (num, frame_number, position.x, position.y)) frame_number += 1 - elif trajectoryType == 'curvilinear': + velocities = feature.getVelocities() + if velocities is not None: + frame_number = feature.getFirstInstant() + for i in xrange(velocities.length()-1): + v = velocities[i] + cursor.execute(velocityQuery, (num, frame_number, v.x, v.y)) + frame_number += 1 + if(trajectoryType == 'object'): + running_tally_of_unique_nums = [] + for obj in objects: + if(obj.hasFeatures()): + n_objects = len(obj.getFeatures()) + for feature in obj.getFeatures(): + feature_num = feature.getNum() + if(feature_num not in running_tally_of_unique_nums): + running_tally_of_unique_nums.append(feature_num) + cursor.execute(objectFeatureQuery, (obj.getNum(), feature_num)) + else: + n_objects = 0 + cursor.execute(objectQuery, (obj.getNum(), obj.getUserType(), n_objects)) + ## Parse curvilinear position structure + elif(trajectoryType == 'curvilinear'): createCurvilinearTrajectoryTable(cursor) curvilinearQuery = "insert into curvilinear_positions (trajectory_id, frame_number, s_coordinate, y_coordinate, lane) values (?,?,?,?,?)" for obj in objects: @@ -356,7 +399,6 @@ for position in obj.getCurvilinearPositions(): cursor.execute(curvilinearQuery, (num, frame_number, position[0], position[1], position[2])) frame_number += 1 - #elif trajectoryType == 'object': else: print('Unknown trajectory type {}'.format(trajectoryType)) connection.commit()