Mercurial Hosting > traffic-intelligence
comparison python/storage.py @ 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 | ed6ff2ec0aeb |
children | 79405a938407 |
comparison
equal
deleted
inserted
replaced
749:10dbab1e871d | 750:6049e9b6902c |
---|---|
1 #! /usr/bin/env python | 1 #! /usr/bin/env python |
2 # -*- coding: utf-8 -*- | 2 # -*- coding: utf-8 -*- |
3 '''Various utilities to save and load data''' | 3 '''Various utilities to save and load data''' |
4 | 4 |
5 import utils, moving, events, indicators | 5 import utils, moving, events, indicators, shutil |
6 from base import VideoFilenameAddable | 6 from base import VideoFilenameAddable |
7 | 7 |
8 import sqlite3, logging | 8 import sqlite3, logging |
9 from numpy import log, min as npmin, max as npmax, round as npround, array, sum as npsum, loadtxt | 9 from numpy import log, min as npmin, max as npmax, round as npround, array, sum as npsum, loadtxt |
10 | 10 |
161 returns a moving object''' | 161 returns a moving object''' |
162 cursor = connection.cursor() | 162 cursor = connection.cursor() |
163 | 163 |
164 try: | 164 try: |
165 objectCriteria = getObjectCriteria(objectNumbers) | 165 objectCriteria = getObjectCriteria(objectNumbers) |
166 queryStatement = None | |
166 if trajectoryType == 'feature': | 167 if trajectoryType == 'feature': |
167 queryStatement = 'SELECT * from '+tableName | 168 queryStatement = 'SELECT * from '+tableName |
168 if objectNumbers is not None: | 169 if objectNumbers is not None: |
169 queryStatement += ' where trajectory_id '+objectCriteria | 170 queryStatement += ' WHERE trajectory_id '+objectCriteria |
170 queryStatement += ' ORDER BY trajectory_id, frame_number' | 171 queryStatement += ' ORDER BY trajectory_id, frame_number' |
171 cursor.execute(queryStatement) | |
172 logging.debug(queryStatement) | |
173 elif trajectoryType == 'object': | 172 elif trajectoryType == 'object': |
174 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' | 173 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' |
175 if objectNumbers is not None: | 174 if objectNumbers is not None: |
176 queryStatement += ' and OF.object_id '+objectCriteria | 175 queryStatement += ' and OF.object_id '+objectCriteria |
177 queryStatement += ' GROUP BY OF.object_id, P.frame_number ORDER BY OF.object_id, P.frame_number' | 176 queryStatement += ' GROUP BY OF.object_id, P.frame_number ORDER BY OF.object_id, P.frame_number' |
178 cursor.execute(queryStatement) | |
179 logging.debug(queryStatement) | |
180 elif trajectoryType in ['bbtop', 'bbbottom']: | 177 elif trajectoryType in ['bbtop', 'bbbottom']: |
181 if trajectoryType == 'bbtop': | 178 if trajectoryType == 'bbtop': |
182 corner = 'top_left' | 179 corner = 'top_left' |
183 elif trajectoryType == 'bbbottom': | 180 elif trajectoryType == 'bbbottom': |
184 corner = 'bottom_right' | 181 corner = 'bottom_right' |
185 queryStatement = 'SELECT object_id, frame_number, x_'+corner+', y_'+corner+' FROM '+tableName | 182 queryStatement = 'SELECT object_id, frame_number, x_'+corner+', y_'+corner+' FROM '+tableName |
186 if objectNumbers is not None: | 183 if objectNumbers is not None: |
187 queryStatement += ' WHERE object_id '+objectCriteria | 184 queryStatement += ' WHERE object_id '+objectCriteria |
188 queryStatement += ' ORDER BY object_id, frame_number' | 185 queryStatement += ' ORDER BY object_id, frame_number' |
186 else: | |
187 print('no trajectory type was chosen') | |
188 if queryStatement is not None: | |
189 cursor.execute(queryStatement) | 189 cursor.execute(queryStatement) |
190 logging.debug(queryStatement) | 190 logging.debug(queryStatement) |
191 else: | |
192 print('no trajectory type was chosen') | |
193 except sqlite3.OperationalError as error: | 191 except sqlite3.OperationalError as error: |
194 printDBError(error) | 192 printDBError(error) |
195 return [] | 193 return [] |
196 | 194 |
197 objId = -1 | 195 objId = -1 |
218 | 216 |
219 def loadUserTypesFromTable(cursor, trajectoryType, objectNumbers): | 217 def loadUserTypesFromTable(cursor, trajectoryType, objectNumbers): |
220 objectCriteria = getObjectCriteria(objectNumbers) | 218 objectCriteria = getObjectCriteria(objectNumbers) |
221 queryStatement = 'SELECT object_id, road_user_type from objects' | 219 queryStatement = 'SELECT object_id, road_user_type from objects' |
222 if objectNumbers is not None: | 220 if objectNumbers is not None: |
223 queryStatement += ' where object_id '+objectCriteria | 221 queryStatement += ' WHERE object_id '+objectCriteria |
224 cursor.execute(queryStatement) | 222 cursor.execute(queryStatement) |
225 userTypes = {} | 223 userTypes = {} |
226 for row in cursor: | 224 for row in cursor: |
227 userTypes[row[0]] = row[1] | 225 userTypes[row[0]] = row[1] |
228 return userTypes | 226 return userTypes |
245 if trajectoryType == 'object': | 243 if trajectoryType == 'object': |
246 cursor = connection.cursor() | 244 cursor = connection.cursor() |
247 try: | 245 try: |
248 # attribute feature numbers to objects | 246 # attribute feature numbers to objects |
249 objectCriteria = getObjectCriteria(objectNumbers) | 247 objectCriteria = getObjectCriteria(objectNumbers) |
250 queryStatement = 'SELECT P.trajectory_id, OF.object_id from positions P, objects_features OF where P.trajectory_id = OF.trajectory_id' | 248 queryStatement = 'SELECT P.trajectory_id, OF.object_id from positions P, objects_features OF WHERE P.trajectory_id = OF.trajectory_id' |
251 if objectNumbers is not None: | 249 if objectNumbers is not None: |
252 queryStatement += ' and OF.object_id '+objectCriteria | 250 queryStatement += ' and OF.object_id '+objectCriteria |
253 queryStatement += ' group by P.trajectory_id order by OF.object_id' # order is important to group all features per object | 251 queryStatement += ' group by P.trajectory_id order by OF.object_id' # order is important to group all features per object |
254 cursor.execute(queryStatement) | 252 cursor.execute(queryStatement) |
255 logging.debug(queryStatement) | 253 logging.debug(queryStatement) |
393 TODO choose the interactions to load''' | 391 TODO choose the interactions to load''' |
394 interactions = [] | 392 interactions = [] |
395 connection = sqlite3.connect(filename) | 393 connection = sqlite3.connect(filename) |
396 cursor = connection.cursor() | 394 cursor = connection.cursor() |
397 try: | 395 try: |
398 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') | 396 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') |
399 interactionNum = -1 | 397 interactionNum = -1 |
400 indicatorTypeNum = -1 | 398 indicatorTypeNum = -1 |
401 tmpIndicators = {} | 399 tmpIndicators = {} |
402 for row in cursor: | 400 for row in cursor: |
403 if row[0] != interactionNum: | 401 if row[0] != interactionNum: |
416 printDBError(error) | 414 printDBError(error) |
417 return [] | 415 return [] |
418 connection.close() | 416 connection.close() |
419 return interactions | 417 return interactions |
420 # load first and last object instants | 418 # load first and last object instants |
421 # 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 | 419 # 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 |
422 | 420 |
423 def createBoundingBoxTable(filename, invHomography = None): | 421 def createBoundingBoxTable(filename, invHomography = None): |
424 '''Create the table to store the object bounding boxes in image space | 422 '''Create the table to store the object bounding boxes in image space |
425 ''' | 423 ''' |
426 connection = sqlite3.connect(filename) | 424 connection = sqlite3.connect(filename) |
427 cursor = connection.cursor() | 425 cursor = connection.cursor() |
428 try: | 426 try: |
429 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))') | 427 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))') |
430 cursor.execute('INSERT INTO bounding_boxes SELECT object_id, frame_number, min(x), min(y), max(x), max(y) from ' | 428 cursor.execute('INSERT INTO bounding_boxes SELECT object_id, frame_number, min(x), min(y), max(x), max(y) from ' |
431 '(SELECT object_id, frame_number, (x*{}+y*{}+{})/w as x, (x*{}+y*{}+{})/w as y from ' | 429 '(SELECT object_id, frame_number, (x*{}+y*{}+{})/w as x, (x*{}+y*{}+{})/w as y from ' |
432 '(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])+ | 430 '(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])+ |
433 'GROUP BY object_id, frame_number') | 431 'GROUP BY object_id, frame_number') |
434 except sqlite3.OperationalError as error: | 432 except sqlite3.OperationalError as error: |
435 printDBError(error) | 433 printDBError(error) |
436 connection.commit() | 434 connection.commit() |
437 connection.close() | 435 connection.close() |
645 | 643 |
646 def setRoutes(filename, objects): | 644 def setRoutes(filename, objects): |
647 connection = sqlite3.connect(filename) | 645 connection = sqlite3.connect(filename) |
648 cursor = connection.cursor() | 646 cursor = connection.cursor() |
649 for obj in objects: | 647 for obj in objects: |
650 cursor.execute('update objects set startRouteID = {} where object_id = {}'.format(obj.startRouteID, obj.getNum())) | 648 cursor.execute('update objects set startRouteID = {} WHERE object_id = {}'.format(obj.startRouteID, obj.getNum())) |
651 cursor.execute('update objects set endRouteID = {} where object_id = {}'.format(obj.endRouteID, obj.getNum())) | 649 cursor.execute('update objects set endRouteID = {} WHERE object_id = {}'.format(obj.endRouteID, obj.getNum())) |
652 connection.commit() | 650 connection.commit() |
653 connection.close() | 651 connection.close() |
654 | 652 |
655 def setRoadUserTypes(filename, objects): | 653 def setRoadUserTypes(filename, objects): |
656 '''Saves the user types of the objects in the sqlite database stored in filename | 654 '''Saves the user types of the objects in the sqlite database stored in filename |
657 The objects should exist in the objects table''' | 655 The objects should exist in the objects table''' |
658 connection = sqlite3.connect(filename) | 656 connection = sqlite3.connect(filename) |
659 cursor = connection.cursor() | 657 cursor = connection.cursor() |
660 for obj in objects: | 658 for obj in objects: |
661 cursor.execute('update objects set road_user_type = {} where object_id = {}'.format(obj.getUserType(), obj.getNum())) | 659 cursor.execute('update objects set road_user_type = {} WHERE object_id = {}'.format(obj.getUserType(), obj.getNum())) |
662 connection.commit() | 660 connection.commit() |
663 connection.close() | 661 connection.close() |
664 | 662 |
665 ######################### | 663 ######################### |
666 # txt files | 664 # txt files |
731 else: return self.fp.readline() | 729 else: return self.fp.readline() |
732 | 730 |
733 def generatePDLaneColumn(data): | 731 def generatePDLaneColumn(data): |
734 data['LANE'] = data['LANE\LINK\NO'].astype(str)+'_'+data['LANE\INDEX'].astype(str) | 732 data['LANE'] = data['LANE\LINK\NO'].astype(str)+'_'+data['LANE\INDEX'].astype(str) |
735 | 733 |
736 def loadTrajectoriesFromVissimFile(filename, simulationStepsPerTimeUnit, nObjects = -1, warmUpLastInstant = None, usePandas = False, nDecimals = 2, lowMemory = True): | 734 def convertTrajectoriesVissimToSqlite(filename): |
735 '''Relies on a system call to sqlite3 | |
736 sqlite3 [file.sqlite] < import_fzp.sql''' | |
737 sqlScriptFilename = "import_fzp.sql" | |
738 # create sql file | |
739 out = openCheck(sqlScriptFilename, "w") | |
740 out.write(".separator \";\"\n"+ | |
741 "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"+ | |
742 ".import "+filename+" curvilinear_positions\n"+ | |
743 "DELETE FROM curvilinear_positions WHERE trajectory_id IS NULL OR trajectory_id = \"NO\";\n") | |
744 out.close() | |
745 # system call | |
746 from os import system, remove | |
747 system("sqlite3 "+utils.removeExtension(filename)+".sqlite < "+sqlScriptFilename) | |
748 remove(sqlScriptFilename) | |
749 | |
750 def loadTrajectoriesFromVissimFile(filename, simulationStepsPerTimeUnit, objectNumbers = None, warmUpLastInstant = None, usePandas = False, nDecimals = 2, lowMemory = True): | |
737 '''Reads data from VISSIM .fzp trajectory file | 751 '''Reads data from VISSIM .fzp trajectory file |
738 simulationStepsPerTimeUnit is the number of simulation steps per unit of time used by VISSIM | 752 simulationStepsPerTimeUnit is the number of simulation steps per unit of time used by VISSIM |
739 for example, there seems to be 5 simulation steps per simulated second in VISSIM, | 753 for example, there seems to be 5 simulation steps per simulated second in VISSIM, |
740 so simulationStepsPerTimeUnit should be 5, | 754 so simulationStepsPerTimeUnit should be 5, |
741 so that all times correspond to the number of the simulation step (and can be stored as integers) | 755 so that all times correspond to the number of the simulation step (and can be stored as integers) |
759 objNum = int(row['NO']) | 773 objNum = int(row['NO']) |
760 tmp = data[data['NO'] == objNum] | 774 tmp = data[data['NO'] == objNum] |
761 objects[objNum] = moving.MovingObject(num = objNum, timeInterval = moving.TimeInterval(row['first'], row['last'])) | 775 objects[objNum] = moving.MovingObject(num = objNum, timeInterval = moving.TimeInterval(row['first'], row['last'])) |
762 # positions should be rounded to nDecimals decimals only | 776 # positions should be rounded to nDecimals decimals only |
763 objects[objNum].curvilinearPositions = moving.CurvilinearTrajectory(S = npround(tmp['POS'].tolist(), nDecimals), Y = npround(tmp['POSLAT'].tolist(), nDecimals), lanes = tmp['LANE'].tolist()) | 777 objects[objNum].curvilinearPositions = moving.CurvilinearTrajectory(S = npround(tmp['POS'].tolist(), nDecimals), Y = npround(tmp['POSLAT'].tolist(), nDecimals), lanes = tmp['LANE'].tolist()) |
764 if nObjects > 0 and len(objects) >= nObjects: | 778 if objectNumbers is not None and objectNumbers > 0 and len(objects) >= objectNumbers: |
765 break | 779 break |
766 return objects.values() | 780 return objects.values() |
767 else: | 781 else: |
768 inputfile = openCheck(filename, quitting = True) | 782 if filename.endswith(".fzp"): |
769 # data = pd.read_csv(filename, skiprows=15, delimiter=';') | 783 inputfile = openCheck(filename, quitting = True) |
770 # skip header: 15 lines + 1 | |
771 line = readline(inputfile, '*$') | |
772 while len(line) > 0:#for line in inputfile: | |
773 data = line.strip().split(';') | |
774 objNum = int(data[1]) | |
775 instant = int(float(data[0])*simulationStepsPerTimeUnit) | |
776 s = float(data[4]) | |
777 y = float(data[5]) | |
778 lane = data[2]+'_'+data[3] | |
779 if objNum not in objects: | |
780 if warmUpLastInstant is None or instant >= warmUpLastInstant: | |
781 if nObjects < 0 or len(objects) < nObjects: | |
782 objects[objNum] = moving.MovingObject(num = objNum, timeInterval = moving.TimeInterval(instant, instant)) | |
783 objects[objNum].curvilinearPositions = moving.CurvilinearTrajectory() | |
784 if (warmUpLastInstant is None or instant >= warmUpLastInstant) and objNum in objects: | |
785 objects[objNum].timeInterval.last = instant | |
786 objects[objNum].curvilinearPositions.addPositionSYL(s, y, lane) | |
787 line = readline(inputfile, '*$') | 784 line = readline(inputfile, '*$') |
788 | 785 while len(line) > 0:#for line in inputfile: |
789 return objects.values() | 786 data = line.strip().split(';') |
787 objNum = int(data[1]) | |
788 instant = float(data[0])*simulationStepsPerTimeUnit | |
789 s = float(data[4]) | |
790 y = float(data[5]) | |
791 lane = data[2]+'_'+data[3] | |
792 if objNum not in objects: | |
793 if warmUpLastInstant is None or instant >= warmUpLastInstant: | |
794 if objectNumbers is None or len(objects) < objectNumbers: | |
795 objects[objNum] = moving.MovingObject(num = objNum, timeInterval = moving.TimeInterval(instant, instant)) | |
796 objects[objNum].curvilinearPositions = moving.CurvilinearTrajectory() | |
797 if (warmUpLastInstant is None or instant >= warmUpLastInstant) and objNum in objects: | |
798 objects[objNum].timeInterval.last = instant | |
799 objects[objNum].curvilinearPositions.addPositionSYL(s, y, lane) | |
800 line = readline(inputfile, '*$') | |
801 return objects.values() | |
802 elif filename.endswith(".sqlite"): | |
803 connection = sqlite3.connect(filename) | |
804 cursor = connection.cursor() | |
805 queryStatement = 'SELECT t, trajectory_id, link_id, lane_id, s_coordinate, y_coordinate FROM curvilinear_positions' | |
806 if objectNumbers is not None: | |
807 queryStatement += ' WHERE trajectory_id '+getObjectCriteria(objectNumbers) | |
808 queryStatement += ' ORDER BY trajectory_id, t' | |
809 #objects = loadTrajectoriesFromTable(connection, "curvilinear_positions", "vissim_curvilinear", objectNumbers) | |
810 for row in cursor: | |
811 objNum = row[1] | |
812 instant = row[0]*simulationStepsPerTimeUnit | |
813 s = row[4] | |
814 y = row[5] | |
815 lane = '{}_{}'.format(row[2], row[3]) | |
816 if objNum not in objects: | |
817 if warmUpLastInstant is None or instant >= warmUpLastInstant: | |
818 if objectNumbers is None or len(objects) < objectNumbers: | |
819 objects[objNum] = moving.MovingObject(num = objNum, timeInterval = moving.TimeInterval(instant, instant)) | |
820 objects[objNum].curvilinearPositions = moving.CurvilinearTrajectory() | |
821 if (warmUpLastInstant is None or instant >= warmUpLastInstant) and objNum in objects: | |
822 objects[objNum].timeInterval.last = instant | |
823 objects[objNum].curvilinearPositions.addPositionSYL(s, y, lane) | |
824 else: | |
825 print("File type of "+filename+" not supported (only .sqlite and .fzp files)") | |
826 | |
790 | 827 |
791 def selectPDLanes(data, lanes = None): | 828 def selectPDLanes(data, lanes = None): |
792 '''Selects the subset of data for the right lanes | 829 '''Selects the subset of data for the right lanes |
793 | 830 |
794 Lane format is a string 'x_y' where x is link index and y is lane index''' | 831 Lane format is a string 'x_y' where x is link index and y is lane index''' |