Mercurial > hg > nsaunier > traffic-intelligence

comparison python/storage.py @ 343:74e437ab5f11

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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 #########################