Mercurial Hosting > traffic-intelligence

comparison python/storage.py @ 342:4d69486869a5

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
work on loading indicators
author Nicolas Saunier <nicolas.saunier@polymtl.ca>
date Thu, 20 Jun 2013 15:47:33 -0400
parents 2f39c4ed0b62
children 74e437ab5f11
comparison
equal deleted inserted replaced
341:2f39c4ed0b62 342:4d69486869a5
55 for obj in objects: 55 for obj in objects:
56 cursor.execute('update objects set road_user_type = {} where object_id = {}'.format(obj.getUserType(), obj.getNum())) 56 cursor.execute('update objects set road_user_type = {} where object_id = {}'.format(obj.getUserType(), obj.getNum()))
57 connection.commit() 57 connection.commit()
58 connection.close() 58 connection.close()
59 59
60 def printDBError(error):
61 print('DB Error: {0}'.format(err))
62
60 def loadPrototypeMatchIndexesFromSqlite(filename): 63 def loadPrototypeMatchIndexesFromSqlite(filename):
61 """ 64 """
62 This function loads the prototypes table in the database of name <filename>. 65 This function loads the prototypes table in the database of name <filename>.
63 It returns a list of tuples representing matching ids : [(prototype_id, matched_trajectory_id),...] 66 It returns a list of tuples representing matching ids : [(prototype_id, matched_trajectory_id),...]
64 """ 67 """
68 connection = sqlite3.connect(filename) 71 connection = sqlite3.connect(filename)
69 cursor = connection.cursor() 72 cursor = connection.cursor()
70 73
71 try: 74 try:
72 cursor.execute('SELECT * from prototypes order by prototype_id, trajectory_id_matched') 75 cursor.execute('SELECT * from prototypes order by prototype_id, trajectory_id_matched')
73 except sqlite3.OperationalError as err: 76 except sqlite3.OperationalError as error:
74 print('DB Error: {0}'.format(err)) 77 printDBError(error)
75 return [] 78 return []
76 79
77 for row in cursor: 80 for row in cursor:
78 matched_indexes.append((row[0],row[1])) 81 matched_indexes.append((row[0],row[1]))
79 82
113 elif trajectoryType == 'object': 116 elif trajectoryType == 'object':
114 objectIdQuery = getTrajectoryIdQuery(objectNumbers, trajectoryType) 117 objectIdQuery = getTrajectoryIdQuery(objectNumbers, trajectoryType)
115 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') 118 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')
116 else: 119 else:
117 print('no trajectory type was chosen') 120 print('no trajectory type was chosen')
118 except sqlite3.OperationalError as err: 121 except sqlite3.OperationalError as error:
119 print('DB Error: {0}'.format(err)) 122 printDBError(error)
120 return [] 123 return []
121 124
122 objId = -1 125 objId = -1
123 obj = None 126 obj = None
124 objects = [] 127 objects = []
184 userTypes[row[0]] = row[1] 187 userTypes[row[0]] = row[1]
185 188
186 for obj in objects: 189 for obj in objects:
187 obj.userType = userTypes[obj.getNum()] 190 obj.userType = userTypes[obj.getNum()]
188 191
189 except sqlite3.OperationalError as err: 192 except sqlite3.OperationalError as error:
190 print('DB Error: {0}'.format(err)) 193 printDBError(error)
191 return [] 194 return []
192 195
193 connection.close() 196 connection.close()
194 return objects 197 return objects
195 198
219 def saveInteractions(filename, interactions): 222 def saveInteractions(filename, interactions):
220 'Saves the interactions in the table' 223 'Saves the interactions in the table'
221 import sqlite3 224 import sqlite3
222 connection = sqlite3.connect(filename) 225 connection = sqlite3.connect(filename)
223 cursor = connection.cursor() 226 cursor = connection.cursor()
224 createInteractionTable(cursor) 227 try:
225 for inter in interactions: 228 createInteractionTable(cursor)
226 saveInteraction(cursor, inter) 229 for inter in interactions:
230 saveInteraction(cursor, inter)
231 except sqlite3.OperationalError as error:
232 printDBError(error)
227 connection.commit() 233 connection.commit()
228 connection.close() 234 connection.close()
229 235
230 def saveIndicator(cursor, interactionNum, indicator): 236 def saveIndicator(cursor, interactionNum, indicator):
231 for instant in indicator.getTimeInterval(): 237 for instant in indicator.getTimeInterval():
235 def saveIndicators(filename, interactions, indicatorNames): 241 def saveIndicators(filename, interactions, indicatorNames):
236 'Saves the indicator values in the table' 242 'Saves the indicator values in the table'
237 import sqlite3 243 import sqlite3
238 connection = sqlite3.connect(filename) 244 connection = sqlite3.connect(filename)
239 cursor = connection.cursor() 245 cursor = connection.cursor()
240 createInteractionTable(cursor) 246 try:
241 createIndicatorTables(cursor) 247 createInteractionTable(cursor)
242 for inter in interactions: 248 createIndicatorTables(cursor)
243 saveInteraction(cursor, inter) 249 for inter in interactions:
244 for indicatorName in indicatorNames: 250 saveInteraction(cursor, inter)
245 indicator = inter.getIndicator(indicatorName) 251 for indicatorName in indicatorNames:
246 if indicator != None: 252 indicator = inter.getIndicator(indicatorName)
247 saveIndicator(cursor, inter.getNum(), indicator) 253 if indicator != None:
254 saveIndicator(cursor, inter.getNum(), indicator)
255 except sqlite3.OperationalError as error:
256 printDBError(error)
248 connection.commit() 257 connection.commit()
258 connection.close()
259
260 def loadIndicators(filename):
261 indicators = []
262 connection = sqlite3.connect(filename)
263 cursor = connection.cursor()
264 try:
265 cursor.execute('select INT.id, INT.object_id1, INT.object_id2, 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')
266 interactionNum = -1
267 indicatorTypeNum = -1
268 for row in cursor:
269 if row[0] != interactionNum:
270 if indicatorNum >= 0:
271 interactions.append(events.Interaction(interactionNum, moving.TimeInterval(), roadUserNumbers[0], roadUserNumbers[1])) # todo time interval from distance indicator (if available) and link to road user objects
272 interactionNum = row[0]
273 roadUserNumbers = row[1:3]
274 indicatorName = events.Interaction.indicatorNames[row[3]]
275 indicatorValues = {row[4]:row[5]}
276 # test when new interaction or new indicator indicatorTypeNum != row[3]
277
278 except sqlite3.OperationalError as error:
279 printDBError(error)
249 connection.close() 280 connection.close()
250 281
251 282
252 ######################### 283 #########################
253 # txt files 284 # txt files