Mercurial Hosting > traffic-intelligence
comparison python/storage.py @ 342:4d69486869a5
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 |