Mercurial Hosting > traffic-intelligence
changeset 750:6049e9b6902c dev
work in progress storage vissim sqlite
author | Nicolas Saunier <nicolas.saunier@polymtl.ca> |
---|---|
date | Wed, 28 Oct 2015 17:50:32 -0400 |
parents | 10dbab1e871d |
children | 79405a938407 |
files | python/storage.py |
diffstat | 1 files changed, 76 insertions(+), 39 deletions(-) [+] |
line wrap: on
line diff
--- a/python/storage.py Tue Oct 20 00:03:25 2015 -0400 +++ b/python/storage.py Wed Oct 28 17:50:32 2015 -0400 @@ -2,7 +2,7 @@ # -*- coding: utf-8 -*- '''Various utilities to save and load data''' -import utils, moving, events, indicators +import utils, moving, events, indicators, shutil from base import VideoFilenameAddable import sqlite3, logging @@ -163,20 +163,17 @@ try: objectCriteria = getObjectCriteria(objectNumbers) + queryStatement = None if trajectoryType == 'feature': queryStatement = 'SELECT * from '+tableName if objectNumbers is not None: - queryStatement += ' where trajectory_id '+objectCriteria + queryStatement += ' WHERE trajectory_id '+objectCriteria queryStatement += ' ORDER BY trajectory_id, frame_number' - cursor.execute(queryStatement) - logging.debug(queryStatement) elif trajectoryType == 'object': - queryStatement = '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' + queryStatement = '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' if objectNumbers is not None: queryStatement += ' and OF.object_id '+objectCriteria queryStatement += ' GROUP BY OF.object_id, P.frame_number ORDER BY OF.object_id, P.frame_number' - cursor.execute(queryStatement) - logging.debug(queryStatement) elif trajectoryType in ['bbtop', 'bbbottom']: if trajectoryType == 'bbtop': corner = 'top_left' @@ -186,10 +183,11 @@ if objectNumbers is not None: queryStatement += ' WHERE object_id '+objectCriteria queryStatement += ' ORDER BY object_id, frame_number' + else: + print('no trajectory type was chosen') + if queryStatement is not None: cursor.execute(queryStatement) logging.debug(queryStatement) - else: - print('no trajectory type was chosen') except sqlite3.OperationalError as error: printDBError(error) return [] @@ -220,7 +218,7 @@ objectCriteria = getObjectCriteria(objectNumbers) queryStatement = 'SELECT object_id, road_user_type from objects' if objectNumbers is not None: - queryStatement += ' where object_id '+objectCriteria + queryStatement += ' WHERE object_id '+objectCriteria cursor.execute(queryStatement) userTypes = {} for row in cursor: @@ -247,7 +245,7 @@ try: # attribute feature numbers to objects objectCriteria = getObjectCriteria(objectNumbers) - queryStatement = 'SELECT P.trajectory_id, OF.object_id from positions P, objects_features OF where P.trajectory_id = OF.trajectory_id' + queryStatement = 'SELECT P.trajectory_id, OF.object_id from positions P, objects_features OF WHERE P.trajectory_id = OF.trajectory_id' if objectNumbers is not None: queryStatement += ' and OF.object_id '+objectCriteria queryStatement += ' group by P.trajectory_id order by OF.object_id' # order is important to group all features per object @@ -395,7 +393,7 @@ connection = sqlite3.connect(filename) cursor = connection.cursor() try: - cursor.execute('select INT.id, INT.object_id1, INT.object_id2, INT.first_frame_number, INT.last_frame_number, IND.indicator_type, IND.frame_number, IND.value from interactions INT, indicators IND where INT.id = IND.interaction_id ORDER BY INT.id, IND.indicator_type, IND.frame_number') + cursor.execute('select INT.id, INT.object_id1, INT.object_id2, INT.first_frame_number, INT.last_frame_number, IND.indicator_type, IND.frame_number, IND.value from interactions INT, indicators IND WHERE INT.id = IND.interaction_id ORDER BY INT.id, IND.indicator_type, IND.frame_number') interactionNum = -1 indicatorTypeNum = -1 tmpIndicators = {} @@ -418,7 +416,7 @@ connection.close() return interactions # load first and last object instants -# CREATE TEMP TABLE IF NOT EXISTS object_instants AS SELECT OF.object_id, min(frame_number) as first_instant, max(frame_number) as last_instant from positions P, objects_features OF where P.trajectory_id = OF.trajectory_id group by OF.object_id order by OF.object_id +# CREATE TEMP TABLE IF NOT EXISTS object_instants AS SELECT OF.object_id, min(frame_number) as first_instant, max(frame_number) as last_instant from positions P, objects_features OF WHERE P.trajectory_id = OF.trajectory_id group by OF.object_id order by OF.object_id def createBoundingBoxTable(filename, invHomography = None): '''Create the table to store the object bounding boxes in image space @@ -429,7 +427,7 @@ cursor.execute('CREATE TABLE IF NOT EXISTS bounding_boxes (object_id INTEGER, frame_number INTEGER, x_top_left REAL, y_top_left REAL, x_bottom_right REAL, y_bottom_right REAL, PRIMARY KEY(object_id, frame_number))') cursor.execute('INSERT INTO bounding_boxes SELECT object_id, frame_number, min(x), min(y), max(x), max(y) from ' '(SELECT object_id, frame_number, (x*{}+y*{}+{})/w as x, (x*{}+y*{}+{})/w as y from ' - '(SELECT OF.object_id, P.frame_number, P.x_coordinate as x, P.y_coordinate as y, P.x_coordinate*{}+P.y_coordinate*{}+{} as w from positions P, objects_features OF where P.trajectory_id = OF.trajectory_id)) '.format(invHomography[0,0], invHomography[0,1], invHomography[0,2], invHomography[1,0], invHomography[1,1], invHomography[1,2], invHomography[2,0], invHomography[2,1], invHomography[2,2])+ + '(SELECT OF.object_id, P.frame_number, P.x_coordinate as x, P.y_coordinate as y, P.x_coordinate*{}+P.y_coordinate*{}+{} as w from positions P, objects_features OF WHERE P.trajectory_id = OF.trajectory_id)) '.format(invHomography[0,0], invHomography[0,1], invHomography[0,2], invHomography[1,0], invHomography[1,1], invHomography[1,2], invHomography[2,0], invHomography[2,1], invHomography[2,2])+ 'GROUP BY object_id, frame_number') except sqlite3.OperationalError as error: printDBError(error) @@ -647,8 +645,8 @@ connection = sqlite3.connect(filename) cursor = connection.cursor() for obj in objects: - cursor.execute('update objects set startRouteID = {} where object_id = {}'.format(obj.startRouteID, obj.getNum())) - cursor.execute('update objects set endRouteID = {} where object_id = {}'.format(obj.endRouteID, obj.getNum())) + cursor.execute('update objects set startRouteID = {} WHERE object_id = {}'.format(obj.startRouteID, obj.getNum())) + cursor.execute('update objects set endRouteID = {} WHERE object_id = {}'.format(obj.endRouteID, obj.getNum())) connection.commit() connection.close() @@ -658,7 +656,7 @@ 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())) + cursor.execute('update objects set road_user_type = {} WHERE object_id = {}'.format(obj.getUserType(), obj.getNum())) connection.commit() connection.close() @@ -733,7 +731,23 @@ def generatePDLaneColumn(data): data['LANE'] = data['LANE\LINK\NO'].astype(str)+'_'+data['LANE\INDEX'].astype(str) -def loadTrajectoriesFromVissimFile(filename, simulationStepsPerTimeUnit, nObjects = -1, warmUpLastInstant = None, usePandas = False, nDecimals = 2, lowMemory = True): +def convertTrajectoriesVissimToSqlite(filename): + '''Relies on a system call to sqlite3 + sqlite3 [file.sqlite] < import_fzp.sql''' + sqlScriptFilename = "import_fzp.sql" + # create sql file + out = openCheck(sqlScriptFilename, "w") + out.write(".separator \";\"\n"+ + "CREATE TABLE IF NOT EXISTS curvilinear_positions (t REAL, trajectory_id INTEGER, link_id INTEGER, lane_id INTEGER, s_coordinate REAL, y_coordinate REAL, speed REAL, PRIMARY KEY (t, trajectory_id));\n"+ + ".import "+filename+" curvilinear_positions\n"+ + "DELETE FROM curvilinear_positions WHERE trajectory_id IS NULL OR trajectory_id = \"NO\";\n") + out.close() + # system call + from os import system, remove + system("sqlite3 "+utils.removeExtension(filename)+".sqlite < "+sqlScriptFilename) + remove(sqlScriptFilename) + +def loadTrajectoriesFromVissimFile(filename, simulationStepsPerTimeUnit, objectNumbers = None, warmUpLastInstant = None, usePandas = False, nDecimals = 2, lowMemory = True): '''Reads data from VISSIM .fzp trajectory file simulationStepsPerTimeUnit is the number of simulation steps per unit of time used by VISSIM for example, there seems to be 5 simulation steps per simulated second in VISSIM, @@ -761,32 +775,55 @@ objects[objNum] = moving.MovingObject(num = objNum, timeInterval = moving.TimeInterval(row['first'], row['last'])) # positions should be rounded to nDecimals decimals only objects[objNum].curvilinearPositions = moving.CurvilinearTrajectory(S = npround(tmp['POS'].tolist(), nDecimals), Y = npround(tmp['POSLAT'].tolist(), nDecimals), lanes = tmp['LANE'].tolist()) - if nObjects > 0 and len(objects) >= nObjects: + if objectNumbers is not None and objectNumbers > 0 and len(objects) >= objectNumbers: break return objects.values() else: - inputfile = openCheck(filename, quitting = True) - # data = pd.read_csv(filename, skiprows=15, delimiter=';') - # skip header: 15 lines + 1 - line = readline(inputfile, '*$') - while len(line) > 0:#for line in inputfile: - data = line.strip().split(';') - objNum = int(data[1]) - instant = int(float(data[0])*simulationStepsPerTimeUnit) - s = float(data[4]) - y = float(data[5]) - lane = data[2]+'_'+data[3] - if objNum not in objects: - if warmUpLastInstant is None or instant >= warmUpLastInstant: - if nObjects < 0 or len(objects) < nObjects: - objects[objNum] = moving.MovingObject(num = objNum, timeInterval = moving.TimeInterval(instant, instant)) - objects[objNum].curvilinearPositions = moving.CurvilinearTrajectory() - if (warmUpLastInstant is None or instant >= warmUpLastInstant) and objNum in objects: - objects[objNum].timeInterval.last = instant - objects[objNum].curvilinearPositions.addPositionSYL(s, y, lane) + if filename.endswith(".fzp"): + inputfile = openCheck(filename, quitting = True) line = readline(inputfile, '*$') + while len(line) > 0:#for line in inputfile: + data = line.strip().split(';') + objNum = int(data[1]) + instant = float(data[0])*simulationStepsPerTimeUnit + s = float(data[4]) + y = float(data[5]) + lane = data[2]+'_'+data[3] + if objNum not in objects: + if warmUpLastInstant is None or instant >= warmUpLastInstant: + if objectNumbers is None or len(objects) < objectNumbers: + objects[objNum] = moving.MovingObject(num = objNum, timeInterval = moving.TimeInterval(instant, instant)) + objects[objNum].curvilinearPositions = moving.CurvilinearTrajectory() + if (warmUpLastInstant is None or instant >= warmUpLastInstant) and objNum in objects: + objects[objNum].timeInterval.last = instant + objects[objNum].curvilinearPositions.addPositionSYL(s, y, lane) + line = readline(inputfile, '*$') + return objects.values() + elif filename.endswith(".sqlite"): + connection = sqlite3.connect(filename) + cursor = connection.cursor() + queryStatement = 'SELECT t, trajectory_id, link_id, lane_id, s_coordinate, y_coordinate FROM curvilinear_positions' + if objectNumbers is not None: + queryStatement += ' WHERE trajectory_id '+getObjectCriteria(objectNumbers) + queryStatement += ' ORDER BY trajectory_id, t' + #objects = loadTrajectoriesFromTable(connection, "curvilinear_positions", "vissim_curvilinear", objectNumbers) + for row in cursor: + objNum = row[1] + instant = row[0]*simulationStepsPerTimeUnit + s = row[4] + y = row[5] + lane = '{}_{}'.format(row[2], row[3]) + if objNum not in objects: + if warmUpLastInstant is None or instant >= warmUpLastInstant: + if objectNumbers is None or len(objects) < objectNumbers: + objects[objNum] = moving.MovingObject(num = objNum, timeInterval = moving.TimeInterval(instant, instant)) + objects[objNum].curvilinearPositions = moving.CurvilinearTrajectory() + if (warmUpLastInstant is None or instant >= warmUpLastInstant) and objNum in objects: + objects[objNum].timeInterval.last = instant + objects[objNum].curvilinearPositions.addPositionSYL(s, y, lane) + else: + print("File type of "+filename+" not supported (only .sqlite and .fzp files)") - return objects.values() def selectPDLanes(data, lanes = None): '''Selects the subset of data for the right lanes