Mercurial Hosting > traffic-intelligence
comparison python/storage.py @ 344:14a2405f54f8
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 |