Mercurial Hosting > traffic-intelligence
changeset 329:a70c205ebdd9
added sqlite code, in particular to load and save road user type
author | Nicolas Saunier <nicolas.saunier@polymtl.ca> |
---|---|
date | Thu, 13 Jun 2013 00:42:40 -0400 |
parents | 5e43b7389c25 |
children | 00800ebae698 |
files | python/moving.py python/storage.py python/ubc_utils.py |
diffstat | 3 files changed, 61 insertions(+), 39 deletions(-) [+] |
line wrap: on
line diff
--- a/python/moving.py Wed Jun 12 18:23:16 2013 -0400 +++ b/python/moving.py Thu Jun 13 00:42:40 2013 -0400 @@ -128,7 +128,7 @@ def empty(self): return self.timeInterval.empty() or not self.boudingPolygon - def getId(self): + def getNum(self): return self.num def getFirstInstant(self): @@ -555,9 +555,11 @@ # Moving Objects ################## -userTypeNames = ['car', +userTypeNames = ['unknown', + 'car', 'pedestrian', - 'twowheels', + 'motorcycle', + 'bicycle', 'bus', 'truck'] @@ -565,7 +567,7 @@ class MovingObject(STObject): '''Class for moving objects: a spatio-temporal object - with a trajectory and a geometry (constant volume over time) and a usertype (e.g. road user) + with a trajectory and a geometry (constant volume over time) and a usertype (e.g. road user) coded as a number (see ''' def __init__(self, num = None, timeInterval = None, positions = None, geometry = None, userType = None): @@ -599,6 +601,12 @@ def getVelocities(self): return self.velocities + def getUserType(self): + return self.userType + + def setUserType(self, userType): + self.userType = userType + def setFeatures(self, features): self.features = [features[i] for i in self.featureNumbers]
--- a/python/storage.py Wed Jun 12 18:23:16 2013 -0400 +++ b/python/storage.py Thu Jun 13 00:42:40 2013 -0400 @@ -12,26 +12,11 @@ 'car':2, 'truck':3} -sqliteUserTypeNames = ['unknown', - 'car', - 'pedestrian', - 'motorcycle', - 'bicycle', - 'bus', - 'truck'] - - -######################### -# txt files -######################### - - - ######################### # Sqlite ######################### -def writeTrajectoriesToSqlite(objects, outFile, trajectoryType, objectNumbers = -1): +def writeTrajectoriesToSqlite(objects, outFilename, trajectoryType, objectNumbers = -1): """ This function writers trajectories to a specified sqlite file @param[in] objects -> a list of trajectories @@ -40,8 +25,8 @@ @param[in] objectNumber : number of objects loaded """ - import sqlite3 - connection = sqlite3.connect(outFile) + import sqlite3 + connection = sqlite3.connect(outFilename) cursor = connection.cursor() schema = "CREATE TABLE \"positions\"(trajectory_id INTEGER,frame_number INTEGER, x_coordinate REAL, y_coordinate REAL, PRIMARY KEY(trajectory_id, frame_number))" @@ -59,7 +44,16 @@ query = "insert into positions (trajectory_id, frame_number, x_coordinate, y_coordinate) values (?,?,?,?)" cursor.execute(query,(trajectory_id,frame_number,position.x,position.y)) - connection.commit() + connection.commit() + connection.close() + +def setRoadUserTypes(filename, objects): + import sqlite3 + connection = sqlite3.connect(filename) + cursor = connection.cursor() + for obj in objects: + cursor.execute('update objects set road_user_type = {} where object_id = {}'.format(obj.getUserType(), obj.getNum())) + connection.commit() connection.close() def loadPrototypeMatchIndexesFromSqlite(filename): @@ -87,9 +81,9 @@ def getTrajectoryIdQuery(objectNumbers, trajectoryType): if trajectoryType == 'feature': - statementBeginning = ' where trajectory_id' + statementBeginning = 'trajectory_id' elif trajectoryType == 'object': - statementBeginning = ' and OF.object_id' + statementBeginning = 'object_id' else: print('no trajectory type was chosen') @@ -114,10 +108,10 @@ try: if trajectoryType == 'feature': trajectoryIdQuery = getTrajectoryIdQuery(objectNumbers, trajectoryType) - cursor.execute('SELECT * from '+tableName+trajectoryIdQuery+' order by trajectory_id, frame_number') + cursor.execute('SELECT * from '+tableName+' where '+trajectoryIdQuery+' order by trajectory_id, frame_number') elif trajectoryType == 'object': objectIdQuery = getTrajectoryIdQuery(objectNumbers, trajectoryType) - cursor.execute('SELECT OF.object_id, P.frame_number, avg(P.x_coordinate), avg(P.y_coordinate) from '+tableName+' P, objects_features OF where P.trajectory_id = OF.trajectory_id '+objectIdQuery+' group by object_id, frame_number') + cursor.execute('SELECT OF.object_id, P.frame_number, avg(P.x_coordinate), avg(P.y_coordinate) from '+tableName+' P, objects_features OF where P.trajectory_id = OF.trajectory_id and OF.'+objectIdQuery+' group by OF.object_id, P.frame_number order by OF.object_id, P.frame_number') else: print('no trajectory type was chosen') except sqlite3.OperationalError as err: @@ -156,29 +150,38 @@ if len(objectVelocities) > 0: for o,v in zip(objects, objectVelocities): - if o.num == v.num: + if o.getNum() == v.getNum(): o.velocities = v.positions else: - print('Could not match positions {0} with velocities {1}'.format(o.num, v.num)) + print('Could not match positions {0} with velocities {1}'.format(o.getNum(), v.getNum())) if trajectoryType == 'object': cursor = connection.cursor() try: + # attribute feature numbers to objects objectIdQuery = getTrajectoryIdQuery(objectNumbers, trajectoryType) - cursor.execute('SELECT P.trajectory_id, OF.object_id from positions P, objects_features OF where P.trajectory_id = OF.trajectory_id '+objectIdQuery+' group by P.trajectory_id order by OF.object_id') + cursor.execute('SELECT P.trajectory_id, OF.object_id from positions P, objects_features OF where P.trajectory_id = OF.trajectory_id and OF.'+objectIdQuery+' group by P.trajectory_id order by OF.object_id') # order is important to group all features per object - # attribute feature numbers to objects - objId = -1 featureNumbers = {} for row in cursor: - if row[1] != objId: - objId = row[1] + objId = row[1] + if objId not in featureNumbers: featureNumbers[objId] = [row[0]] else: featureNumbers[objId].append(row[0]) for obj in objects: - obj.featureNumbers = featureNumbers[obj.num] + obj.featureNumbers = featureNumbers[obj.getNum()] + + # load userType + cursor.execute('SELECT object_id, road_user_type from objects where '+objectIdQuery) + userTypes = {} + for row in cursor: + userTypes[row[0]] = row[1] + + for obj in objects: + obj.userType = userTypes[obj.getNum()] + except sqlite3.OperationalError as err: print('DB Error: {0}'.format(err)) return [] @@ -193,6 +196,11 @@ utils.dropTables(connection, ['objects', 'objects_features']) connection.close() + +######################### +# txt files +######################### + def loadTrajectoriesFromNgsimFile(filename, nObjects = -1, sequenceNum = -1): '''Reads data from the trajectory data provided by NGSIM project and returns the list of Feature objects''' @@ -231,10 +239,10 @@ for line in input: numbers = line.strip().split() - if obj.num != int(numbers[0]): + if obj.getNum() != int(numbers[0]): # check and adapt the length to deal with issues in NGSIM data if (obj.length() != obj.positions.length()): - print 'length pb with object %s (%d,%d)' % (obj.num,obj.length(),obj.positions.length()) + print 'length pb with object %s (%d,%d)' % (obj.getNum(),obj.length(),obj.positions.length()) obj.last = obj.getFirstInstant()+obj.positions.length()-1 #obj.velocities = utils.computeVelocities(f.positions) # compare norm to speeds ? objects.append(obj) @@ -252,9 +260,9 @@ obj.timeHeadways.append(float(numbers[17])) if (obj.size[0] != float(numbers[8])): - print 'changed length obj %d' % (f.num) + print 'changed length obj %d' % (obj.getNum()) if (obj.size[1] != float(numbers[9])): - print 'changed width obj %d' % (f.num) + print 'changed width obj %d' % (obj.getNum()) input.close() return objects
--- a/python/ubc_utils.py Wed Jun 12 18:23:16 2013 -0400 +++ b/python/ubc_utils.py Thu Jun 13 00:42:40 2013 -0400 @@ -19,6 +19,12 @@ 'Severity Index', 'TTC'] +userTypeNames = ['car', + 'pedestrian', + 'twowheels', + 'bus', + 'truck'] + # severityIndicator = {'Distance': 0, # 'Cosine': 1, # 'Velocity Cosine': 2,