Mercurial > hg > nsaunier > traffic-intelligence

comparison python/storage.py @ 750:6049e9b6902c dev

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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'''