Mercurial > hg > nsaunier > traffic-intelligence

comparison python/storage.py @ 330:00800ebae698

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
corrected bug in db loading
author Nicolas Saunier <nicolas.saunier@polymtl.ca>
date Thu, 13 Jun 2013 23:05:28 -0400
parents a70c205ebdd9
children 3950bfe22768
comparison
equal deleted inserted replaced
329:a70c205ebdd9 330:00800ebae698
46 46
47 connection.commit() 47 connection.commit()
48 connection.close() 48 connection.close()
49 49
50 def setRoadUserTypes(filename, objects): 50 def setRoadUserTypes(filename, objects):
51 '''Saves the user types of the objects in the sqlite database stored in filename
52 The objects should exist in the objects table'''
51 import sqlite3 53 import sqlite3
52 connection = sqlite3.connect(filename) 54 connection = sqlite3.connect(filename)
53 cursor = connection.cursor() 55 cursor = connection.cursor()
54 for obj in objects: 56 for obj in objects:
55 cursor.execute('update objects set road_user_type = {} where object_id = {}'.format(obj.getUserType(), obj.getNum())) 57 cursor.execute('update objects set road_user_type = {} where object_id = {}'.format(obj.getUserType(), obj.getNum()))
79 connection.close() 81 connection.close()
80 return matched_indexes 82 return matched_indexes
81 83
82 def getTrajectoryIdQuery(objectNumbers, trajectoryType): 84 def getTrajectoryIdQuery(objectNumbers, trajectoryType):
83 if trajectoryType == 'feature': 85 if trajectoryType == 'feature':
84 statementBeginning = 'trajectory_id' 86 statementBeginning = 'where trajectory_id '
85 elif trajectoryType == 'object': 87 elif trajectoryType == 'object':
86 statementBeginning = 'object_id' 88 statementBeginning = 'and OF.object_id '
87 else: 89 else:
88 print('no trajectory type was chosen') 90 print('no trajectory type was chosen')
89 91
90 if type(objectNumbers) == int: 92 if type(objectNumbers) == int:
91 if objectNumbers == -1: 93 if objectNumbers == -1:
92 query = '' 94 query = ''
93 else: 95 else:
94 query = statementBeginning+' between 0 and {0}'.format(objectNumbers) 96 query = statementBeginning+'between 0 and {0} '.format(objectNumbers)
95 elif type(objectNumbers) == list: 97 elif type(objectNumbers) == list:
96 query = statementBeginning+' in ('+', '.join([str(n) for n in objectNumbers])+')' 98 query = statementBeginning+'in ('+', '.join([str(n) for n in objectNumbers])+') '
97 return query 99 return query
98 100
99 def loadTrajectoriesFromTable(connection, tableName, trajectoryType, objectNumbers = -1): 101 def loadTrajectoriesFromTable(connection, tableName, trajectoryType, objectNumbers = -1):
100 '''Loads trajectories (in the general sense) from the given table 102 '''Loads trajectories (in the general sense) from the given table
101 can be positions or velocities 103 can be positions or velocities
106 cursor = connection.cursor() 108 cursor = connection.cursor()
107 109
108 try: 110 try:
109 if trajectoryType == 'feature': 111 if trajectoryType == 'feature':
110 trajectoryIdQuery = getTrajectoryIdQuery(objectNumbers, trajectoryType) 112 trajectoryIdQuery = getTrajectoryIdQuery(objectNumbers, trajectoryType)
111 cursor.execute('SELECT * from '+tableName+' where '+trajectoryIdQuery+' order by trajectory_id, frame_number') 113 cursor.execute('SELECT * from '+tableName+' '+trajectoryIdQuery+'order by trajectory_id, frame_number')
112 elif trajectoryType == 'object': 114 elif trajectoryType == 'object':
113 objectIdQuery = getTrajectoryIdQuery(objectNumbers, trajectoryType) 115 objectIdQuery = getTrajectoryIdQuery(objectNumbers, trajectoryType)
114 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') 116 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 OF.object_id, P.frame_number order by OF.object_id, P.frame_number')
115 else: 117 else:
116 print('no trajectory type was chosen') 118 print('no trajectory type was chosen')
117 except sqlite3.OperationalError as err: 119 except sqlite3.OperationalError as err:
118 print('DB Error: {0}'.format(err)) 120 print('DB Error: {0}'.format(err))
119 return [] 121 return []
158 if trajectoryType == 'object': 160 if trajectoryType == 'object':
159 cursor = connection.cursor() 161 cursor = connection.cursor()
160 try: 162 try:
161 # attribute feature numbers to objects 163 # attribute feature numbers to objects
162 objectIdQuery = getTrajectoryIdQuery(objectNumbers, trajectoryType) 164 objectIdQuery = getTrajectoryIdQuery(objectNumbers, trajectoryType)
163 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 165 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') # order is important to group all features per object
164 166
165 featureNumbers = {} 167 featureNumbers = {}
166 for row in cursor: 168 for row in cursor:
167 objId = row[1] 169 objId = row[1]
168 if objId not in featureNumbers: 170 if objId not in featureNumbers:
172 174
173 for obj in objects: 175 for obj in objects:
174 obj.featureNumbers = featureNumbers[obj.getNum()] 176 obj.featureNumbers = featureNumbers[obj.getNum()]
175 177
176 # load userType 178 # load userType
177 cursor.execute('SELECT object_id, road_user_type from objects where '+objectIdQuery) 179 if objectIdQuery == '':
180 cursor.execute('SELECT object_id, road_user_type from objects')
181 else:
182 cursor.execute('SELECT object_id, road_user_type from objects where '+objectIdQuery[7:])
178 userTypes = {} 183 userTypes = {}
179 for row in cursor: 184 for row in cursor:
180 userTypes[row[0]] = row[1] 185 userTypes[row[0]] = row[1]
181 186
182 for obj in objects: 187 for obj in objects: