Mercurial > hg > nsaunier > traffic-intelligence

comparison python/storage.py @ 344:14a2405f54f8

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
slight modification to safety analysis and generalized script to delete computed data (objects and interactions)
author Nicolas Saunier <nicolas.saunier@polymtl.ca>
date Fri, 21 Jun 2013 17:32:57 -0400
parents 74e437ab5f11
children 2aed569f39e7
comparison
equal deleted inserted replaced
343:74e437ab5f11 344:14a2405f54f8
54 for obj in objects: 54 for obj in objects:
55 cursor.execute('update objects set road_user_type = {} where object_id = {}'.format(obj.getUserType(), obj.getNum())) 55 cursor.execute('update objects set road_user_type = {} where object_id = {}'.format(obj.getUserType(), obj.getNum()))
56 connection.commit() 56 connection.commit()
57 connection.close() 57 connection.close()
58 58
59 def printDBError(error):
60 print('DB Error: {0}'.format(error))
61
62 def loadPrototypeMatchIndexesFromSqlite(filename): 59 def loadPrototypeMatchIndexesFromSqlite(filename):
63 """ 60 """
64 This function loads the prototypes table in the database of name <filename>. 61 This function loads the prototypes table in the database of name <filename>.
65 It returns a list of tuples representing matching ids : [(prototype_id, matched_trajectory_id),...] 62 It returns a list of tuples representing matching ids : [(prototype_id, matched_trajectory_id),...]
66 """ 63 """
70 cursor = connection.cursor() 67 cursor = connection.cursor()
71 68
72 try: 69 try:
73 cursor.execute('SELECT * from prototypes order by prototype_id, trajectory_id_matched') 70 cursor.execute('SELECT * from prototypes order by prototype_id, trajectory_id_matched')
74 except sqlite3.OperationalError as error: 71 except sqlite3.OperationalError as error:
75 printDBError(error) 72 utils.printDBError(error)
76 return [] 73 return []
77 74
78 for row in cursor: 75 for row in cursor:
79 matched_indexes.append((row[0],row[1])) 76 matched_indexes.append((row[0],row[1]))
80 77
113 objectIdQuery = getTrajectoryIdQuery(objectNumbers, trajectoryType) 110 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 '+objectIdQuery+'group by OF.object_id, P.frame_number order by OF.object_id, P.frame_number') 111 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: 112 else:
116 print('no trajectory type was chosen') 113 print('no trajectory type was chosen')
117 except sqlite3.OperationalError as error: 114 except sqlite3.OperationalError as error:
118 printDBError(error) 115 utils.printDBError(error)
119 return [] 116 return []
120 117
121 objId = -1 118 objId = -1
122 obj = None 119 obj = None
123 objects = [] 120 objects = []
183 180
184 for obj in objects: 181 for obj in objects:
185 obj.userType = userTypes[obj.getNum()] 182 obj.userType = userTypes[obj.getNum()]
186 183
187 except sqlite3.OperationalError as error: 184 except sqlite3.OperationalError as error:
188 printDBError(error) 185 utils.printDBError(error)
189 return [] 186 return []
190 187
191 connection.close() 188 connection.close()
192 return objects 189 return objects
193 190
194 def removeObjectsFromSqlite(filename): 191 def removeFromSqlite(filename, dataType):
195 'Removes the objects and object_features tables in the filename' 192 'Removes some tables in the filename depending on type of data'
196 connection = sqlite3.connect(filename) 193 connection = sqlite3.connect(filename)
197 utils.dropTables(connection, ['objects', 'objects_features']) 194 if dataType == 'object':
198 connection.close() 195 utils.dropTables(connection, ['objects', 'objects_features'])
199 196 elif dataType == 'interaction':
200 def deleteIndicators(filename): 197 utils.dropTables(connection, ['interactions', 'indicators'])
201 'Deletes all indicator data in db' 198 else:
202 pass 199 print('Unknown data type {} to delete from database'.format(dataType))
200 connection.close()
203 201
204 def createInteractionTable(cursor): 202 def createInteractionTable(cursor):
205 cursor.execute('CREATE TABLE IF NOT EXISTS interactions (id INTEGER PRIMARY KEY, object_id1 INTEGER, object_id2 INTEGER, first_frame_number INTEGER, last_frame_number INTEGER, FOREIGN KEY(object_id1) REFERENCES objects(id), FOREIGN KEY(object_id2) REFERENCES objects(id))') 203 cursor.execute('CREATE TABLE IF NOT EXISTS interactions (id INTEGER PRIMARY KEY, object_id1 INTEGER, object_id2 INTEGER, first_frame_number INTEGER, last_frame_number INTEGER, FOREIGN KEY(object_id1) REFERENCES objects(id), FOREIGN KEY(object_id2) REFERENCES objects(id))')
206 204
207 def createIndicatorTables(cursor): 205 def createIndicatorTables(cursor):
220 try: 218 try:
221 createInteractionTable(cursor) 219 createInteractionTable(cursor)
222 for inter in interactions: 220 for inter in interactions:
223 saveInteraction(cursor, inter) 221 saveInteraction(cursor, inter)
224 except sqlite3.OperationalError as error: 222 except sqlite3.OperationalError as error:
225 printDBError(error) 223 utils.printDBError(error)
226 connection.commit() 224 connection.commit()
227 connection.close() 225 connection.close()
228 226
229 def saveIndicator(cursor, interactionNum, indicator): 227 def saveIndicator(cursor, interactionNum, indicator):
230 for instant in indicator.getTimeInterval(): 228 for instant in indicator.getTimeInterval():
243 for indicatorName in indicatorNames: 241 for indicatorName in indicatorNames:
244 indicator = inter.getIndicator(indicatorName) 242 indicator = inter.getIndicator(indicatorName)
245 if indicator != None: 243 if indicator != None:
246 saveIndicator(cursor, inter.getNum(), indicator) 244 saveIndicator(cursor, inter.getNum(), indicator)
247 except sqlite3.OperationalError as error: 245 except sqlite3.OperationalError as error:
248 printDBError(error) 246 utils.printDBError(error)
249 connection.commit() 247 connection.commit()
250 connection.close() 248 connection.close()
251 249
252 def loadIndicators(filename): 250 def loadIndicators(filename):
253 '''Loads interaction indicators 251 '''Loads interaction indicators
282 indicatorName = events.Interaction.indicatorNames[indicatorTypeNum] 280 indicatorName = events.Interaction.indicatorNames[indicatorTypeNum]
283 tmpIndicators[indicatorName] = indicators.SeverityIndicator(indicatorName, indicatorValues) 281 tmpIndicators[indicatorName] = indicators.SeverityIndicator(indicatorName, indicatorValues)
284 interactions.append(events.Interaction(interactionNum, moving.TimeInterval(row[3],row[4]), roadUserNumbers[0], roadUserNumbers[1])) 282 interactions.append(events.Interaction(interactionNum, moving.TimeInterval(row[3],row[4]), roadUserNumbers[0], roadUserNumbers[1]))
285 interactions[-1].indicators = tmpIndicators 283 interactions[-1].indicators = tmpIndicators
286 except sqlite3.OperationalError as error: 284 except sqlite3.OperationalError as error:
287 printDBError(error) 285 utils.printDBError(error)
288 return [] 286 return []
289 connection.close() 287 connection.close()
290 return interactions 288 return interactions
291 # load first and last object instants 289 # load first and last object instants
292 # 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 290 # 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