Mercurial Hosting > traffic-intelligence
comparison python/storage.py @ 343:74e437ab5f11
first version of indicator loading code
author | Nicolas Saunier <nicolas.saunier@polymtl.ca> |
---|---|
date | Fri, 21 Jun 2013 15:28:59 -0400 |
parents | 4d69486869a5 |
children | 14a2405f54f8 |
comparison
equal
deleted
inserted
replaced
342:4d69486869a5 | 343:74e437ab5f11 |
---|---|
1 #! /usr/bin/env python | 1 #! /usr/bin/env python |
2 # -*- coding: utf-8 -*- | 2 # -*- coding: utf-8 -*- |
3 '''Various utilities to save and load data''' | 3 '''Various utilities to save and load data''' |
4 | 4 |
5 import utils, moving, events | 5 import utils, moving, events, indicators |
6 | |
7 import sqlite3 | |
6 | 8 |
7 __metaclass__ = type | 9 __metaclass__ = type |
8 | 10 |
9 | 11 |
10 ngsimUserTypes = {'twowheels':1, | 12 ngsimUserTypes = {'twowheels':1, |
21 @param[in] objects -> a list of trajectories | 23 @param[in] objects -> a list of trajectories |
22 @param[in] trajectoryType - | 24 @param[in] trajectoryType - |
23 @param[out] outFile -> the .sqlite file containting the written objects | 25 @param[out] outFile -> the .sqlite file containting the written objects |
24 @param[in] objectNumber : number of objects loaded | 26 @param[in] objectNumber : number of objects loaded |
25 """ | 27 """ |
26 | |
27 import sqlite3 | |
28 connection = sqlite3.connect(outFilename) | 28 connection = sqlite3.connect(outFilename) |
29 cursor = connection.cursor() | 29 cursor = connection.cursor() |
30 | 30 |
31 schema = "CREATE TABLE IF NOT EXISTS \"positions\"(trajectory_id INTEGER,frame_number INTEGER, x_coordinate REAL, y_coordinate REAL, PRIMARY KEY(trajectory_id, frame_number))" | 31 schema = "CREATE TABLE IF NOT EXISTS \"positions\"(trajectory_id INTEGER,frame_number INTEGER, x_coordinate REAL, y_coordinate REAL, PRIMARY KEY(trajectory_id, frame_number))" |
32 cursor.execute(schema) | 32 cursor.execute(schema) |
47 connection.close() | 47 connection.close() |
48 | 48 |
49 def setRoadUserTypes(filename, objects): | 49 def setRoadUserTypes(filename, objects): |
50 '''Saves the user types of the objects in the sqlite database stored in filename | 50 '''Saves the user types of the objects in the sqlite database stored in filename |
51 The objects should exist in the objects table''' | 51 The objects should exist in the objects table''' |
52 import sqlite3 | |
53 connection = sqlite3.connect(filename) | 52 connection = sqlite3.connect(filename) |
54 cursor = connection.cursor() | 53 cursor = connection.cursor() |
55 for obj in objects: | 54 for obj in objects: |
56 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())) |
57 connection.commit() | 56 connection.commit() |
58 connection.close() | 57 connection.close() |
59 | 58 |
60 def printDBError(error): | 59 def printDBError(error): |
61 print('DB Error: {0}'.format(err)) | 60 print('DB Error: {0}'.format(error)) |
62 | 61 |
63 def loadPrototypeMatchIndexesFromSqlite(filename): | 62 def loadPrototypeMatchIndexesFromSqlite(filename): |
64 """ | 63 """ |
65 This function loads the prototypes table in the database of name <filename>. | 64 This function loads the prototypes table in the database of name <filename>. |
66 It returns a list of tuples representing matching ids : [(prototype_id, matched_trajectory_id),...] | 65 It returns a list of tuples representing matching ids : [(prototype_id, matched_trajectory_id),...] |
67 """ | 66 """ |
68 matched_indexes = [] | 67 matched_indexes = [] |
69 | 68 |
70 import sqlite3 | |
71 connection = sqlite3.connect(filename) | 69 connection = sqlite3.connect(filename) |
72 cursor = connection.cursor() | 70 cursor = connection.cursor() |
73 | 71 |
74 try: | 72 try: |
75 cursor.execute('SELECT * from prototypes order by prototype_id, trajectory_id_matched') | 73 cursor.execute('SELECT * from prototypes order by prototype_id, trajectory_id_matched') |
103 def loadTrajectoriesFromTable(connection, tableName, trajectoryType, objectNumbers = -1): | 101 def loadTrajectoriesFromTable(connection, tableName, trajectoryType, objectNumbers = -1): |
104 '''Loads trajectories (in the general sense) from the given table | 102 '''Loads trajectories (in the general sense) from the given table |
105 can be positions or velocities | 103 can be positions or velocities |
106 | 104 |
107 returns a moving object''' | 105 returns a moving object''' |
108 import sqlite3 | |
109 | |
110 cursor = connection.cursor() | 106 cursor = connection.cursor() |
111 | 107 |
112 try: | 108 try: |
113 if trajectoryType == 'feature': | 109 if trajectoryType == 'feature': |
114 trajectoryIdQuery = getTrajectoryIdQuery(objectNumbers, trajectoryType) | 110 trajectoryIdQuery = getTrajectoryIdQuery(objectNumbers, trajectoryType) |
143 def loadTrajectoriesFromSqlite(filename, trajectoryType, objectNumbers = -1): | 139 def loadTrajectoriesFromSqlite(filename, trajectoryType, objectNumbers = -1): |
144 '''Loads nObjects or the indices in objectNumbers from the database | 140 '''Loads nObjects or the indices in objectNumbers from the database |
145 TODO: load feature numbers and not average feature trajectories | 141 TODO: load feature numbers and not average feature trajectories |
146 TODO: other ways of averaging trajectories (load all points, sorted by frame_number and leave the agregation to be done in python) | 142 TODO: other ways of averaging trajectories (load all points, sorted by frame_number and leave the agregation to be done in python) |
147 ''' | 143 ''' |
148 import sqlite3 | |
149 | |
150 connection = sqlite3.connect(filename) # add test if it open | 144 connection = sqlite3.connect(filename) # add test if it open |
151 | 145 |
152 objects = loadTrajectoriesFromTable(connection, 'positions', trajectoryType, objectNumbers) | 146 objects = loadTrajectoriesFromTable(connection, 'positions', trajectoryType, objectNumbers) |
153 objectVelocities = loadTrajectoriesFromTable(connection, 'velocities', trajectoryType, objectNumbers) | 147 objectVelocities = loadTrajectoriesFromTable(connection, 'velocities', trajectoryType, objectNumbers) |
154 | 148 |
155 if len(objectVelocities) > 0: | 149 if len(objectVelocities) > 0: |
156 for o,v in zip(objects, objectVelocities): | 150 for o,v in zip(objects, objectVelocities): |
157 if o.getNum() == v.getNum(): | 151 if o.getNum() == v.getNum(): |
158 o.velocities = v.positions | 152 o.velocities = v.positions |
153 o.velocities.duplicateLastPosition() # avoid having velocity shorter by one position than positions | |
159 else: | 154 else: |
160 print('Could not match positions {0} with velocities {1}'.format(o.getNum(), v.getNum())) | 155 print('Could not match positions {0} with velocities {1}'.format(o.getNum(), v.getNum())) |
161 | 156 |
162 if trajectoryType == 'object': | 157 if trajectoryType == 'object': |
163 cursor = connection.cursor() | 158 cursor = connection.cursor() |
196 connection.close() | 191 connection.close() |
197 return objects | 192 return objects |
198 | 193 |
199 def removeObjectsFromSqlite(filename): | 194 def removeObjectsFromSqlite(filename): |
200 'Removes the objects and object_features tables in the filename' | 195 'Removes the objects and object_features tables in the filename' |
201 import sqlite3 | |
202 connection = sqlite3.connect(filename) | 196 connection = sqlite3.connect(filename) |
203 utils.dropTables(connection, ['objects', 'objects_features']) | 197 utils.dropTables(connection, ['objects', 'objects_features']) |
204 connection.close() | 198 connection.close() |
205 | 199 |
206 def deleteIndicators(filename): | 200 def deleteIndicators(filename): |
207 'Deletes all indicator data in db' | 201 'Deletes all indicator data in db' |
208 pass | 202 pass |
209 | 203 |
210 def createInteractionTable(cursor): | 204 def createInteractionTable(cursor): |
211 cursor.execute('CREATE TABLE IF NOT EXISTS interactions (id INTEGER PRIMARY KEY, object_id1 INTEGER, object_id2 INTEGER, FOREIGN KEY(object_id1) REFERENCES objects(id), FOREIGN KEY(object_id2) REFERENCES objects(id))') | 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))') |
212 | 206 |
213 def createIndicatorTables(cursor): | 207 def createIndicatorTables(cursor): |
214 # cursor.execute('CREATE TABLE IF NOT EXISTS indicators (id INTEGER PRIMARY KEY, interaction_id INTEGER, indicator_type INTEGER, FOREIGN KEY(interaction_id) REFERENCES interactions(id))') | 208 # cursor.execute('CREATE TABLE IF NOT EXISTS indicators (id INTEGER PRIMARY KEY, interaction_id INTEGER, indicator_type INTEGER, FOREIGN KEY(interaction_id) REFERENCES interactions(id))') |
215 # cursor.execute('CREATE TABLE IF NOT EXISTS indicator_values (indicator_id INTEGER, frame_number INTEGER, value REAL, FOREIGN KEY(indicator_id) REFERENCES indicators(id), PRIMARY KEY(indicator_id, frame_number))') | 209 # cursor.execute('CREATE TABLE IF NOT EXISTS indicator_values (indicator_id INTEGER, frame_number INTEGER, value REAL, FOREIGN KEY(indicator_id) REFERENCES indicators(id), PRIMARY KEY(indicator_id, frame_number))') |
216 cursor.execute('CREATE TABLE IF NOT EXISTS indicators (interaction_id INTEGER, indicator_type INTEGER, frame_number INTEGER, value REAL, FOREIGN KEY(interaction_id) REFERENCES interactions(id), PRIMARY KEY(interaction_id, indicator_type, frame_number))') | 210 cursor.execute('CREATE TABLE IF NOT EXISTS indicators (interaction_id INTEGER, indicator_type INTEGER, frame_number INTEGER, value REAL, FOREIGN KEY(interaction_id) REFERENCES interactions(id), PRIMARY KEY(interaction_id, indicator_type, frame_number))') |
217 | 211 |
218 def saveInteraction(cursor, interaction): | 212 def saveInteraction(cursor, interaction): |
219 roadUserNumbers = list(interaction.getRoadUserNumbers()) | 213 roadUserNumbers = list(interaction.getRoadUserNumbers()) |
220 cursor.execute('INSERT INTO interactions VALUES({}, {}, {})'.format(interaction.getNum(), roadUserNumbers[0], roadUserNumbers[1])) | 214 cursor.execute('INSERT INTO interactions VALUES({}, {}, {}, {}, {})'.format(interaction.getNum(), roadUserNumbers[0], roadUserNumbers[1], interaction.getFirstInstant(), interaction.getLastInstant())) |
221 | 215 |
222 def saveInteractions(filename, interactions): | 216 def saveInteractions(filename, interactions): |
223 'Saves the interactions in the table' | 217 'Saves the interactions in the table' |
224 import sqlite3 | |
225 connection = sqlite3.connect(filename) | 218 connection = sqlite3.connect(filename) |
226 cursor = connection.cursor() | 219 cursor = connection.cursor() |
227 try: | 220 try: |
228 createInteractionTable(cursor) | 221 createInteractionTable(cursor) |
229 for inter in interactions: | 222 for inter in interactions: |
236 def saveIndicator(cursor, interactionNum, indicator): | 229 def saveIndicator(cursor, interactionNum, indicator): |
237 for instant in indicator.getTimeInterval(): | 230 for instant in indicator.getTimeInterval(): |
238 if indicator[instant]: | 231 if indicator[instant]: |
239 cursor.execute('INSERT INTO indicators VALUES({}, {}, {}, {})'.format(interactionNum, events.Interaction.indicatorNameToIndices[indicator.getName()], instant, indicator[instant])) | 232 cursor.execute('INSERT INTO indicators VALUES({}, {}, {}, {})'.format(interactionNum, events.Interaction.indicatorNameToIndices[indicator.getName()], instant, indicator[instant])) |
240 | 233 |
241 def saveIndicators(filename, interactions, indicatorNames): | 234 def saveIndicators(filename, interactions, indicatorNames = events.Interaction.indicatorNames): |
242 'Saves the indicator values in the table' | 235 'Saves the indicator values in the table' |
243 import sqlite3 | |
244 connection = sqlite3.connect(filename) | 236 connection = sqlite3.connect(filename) |
245 cursor = connection.cursor() | 237 cursor = connection.cursor() |
246 try: | 238 try: |
247 createInteractionTable(cursor) | 239 createInteractionTable(cursor) |
248 createIndicatorTables(cursor) | 240 createIndicatorTables(cursor) |
256 printDBError(error) | 248 printDBError(error) |
257 connection.commit() | 249 connection.commit() |
258 connection.close() | 250 connection.close() |
259 | 251 |
260 def loadIndicators(filename): | 252 def loadIndicators(filename): |
261 indicators = [] | 253 '''Loads interaction indicators |
254 | |
255 TODO choose the interactions to load''' | |
256 interactions = [] | |
262 connection = sqlite3.connect(filename) | 257 connection = sqlite3.connect(filename) |
263 cursor = connection.cursor() | 258 cursor = connection.cursor() |
264 try: | 259 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') | 260 cursor.execute('select INT.id, INT.object_id1, INT.object_id2, INT.first_frame_number, INT.last_frame_number, 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 | 261 interactionNum = -1 |
267 indicatorTypeNum = -1 | 262 indicatorTypeNum = -1 |
263 tmpIndicators = {} | |
268 for row in cursor: | 264 for row in cursor: |
269 if row[0] != interactionNum: | 265 if row[0] != interactionNum: # save interaction and create new interaction |
270 if indicatorNum >= 0: | 266 if interactionNum >= 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 | 267 interactions.append(events.Interaction(interactionNum, moving.TimeInterval(row[3],row[4]), roadUserNumbers[0], roadUserNumbers[1])) |
268 interactions[-1].indicators = tmpIndicators | |
269 tmpIndicators = {} | |
272 interactionNum = row[0] | 270 interactionNum = row[0] |
273 roadUserNumbers = row[1:3] | 271 roadUserNumbers = row[1:3] |
274 indicatorName = events.Interaction.indicatorNames[row[3]] | 272 if indicatorTypeNum != row[5]: |
275 indicatorValues = {row[4]:row[5]} | 273 if indicatorTypeNum >= 0: |
276 # test when new interaction or new indicator indicatorTypeNum != row[3] | 274 indicatorName = events.Interaction.indicatorNames[indicatorTypeNum] |
277 | 275 tmpIndicators[indicatorName] = indicators.SeverityIndicator(indicatorName, indicatorValues) |
276 indicatorTypeNum = row[5] | |
277 indicatorValues = {row[6]:row[7]} | |
278 else: | |
279 indicatorValues[row[6]] = row[7] | |
280 if interactionNum >= 0: | |
281 if indicatorTypeNum >= 0: | |
282 indicatorName = events.Interaction.indicatorNames[indicatorTypeNum] | |
283 tmpIndicators[indicatorName] = indicators.SeverityIndicator(indicatorName, indicatorValues) | |
284 interactions.append(events.Interaction(interactionNum, moving.TimeInterval(row[3],row[4]), roadUserNumbers[0], roadUserNumbers[1])) | |
285 interactions[-1].indicators = tmpIndicators | |
278 except sqlite3.OperationalError as error: | 286 except sqlite3.OperationalError as error: |
279 printDBError(error) | 287 printDBError(error) |
280 connection.close() | 288 return [] |
289 connection.close() | |
290 return interactions | |
291 # 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 | |
281 | 293 |
282 | 294 |
283 ######################### | 295 ######################### |
284 # txt files | 296 # txt files |
285 ######################### | 297 ######################### |