Mercurial Hosting > traffic-intelligence

comparison python/storage.py @ 614:5e09583275a4

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Merged Nicolas/trafficintelligence into default
author Mohamed Gomaa <eng.m.gom3a@gmail.com>
date Fri, 05 Dec 2014 12:13:53 -0500
parents c5406edbcf12
children 5800a87f11ae 0954aaf28231
comparison
equal deleted inserted replaced
598:11f96bd08552 614:5e09583275a4
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 5 import utils, moving, events, indicators
6 import moving 6
7 import sqlite3, logging
7 8
8 __metaclass__ = type 9 __metaclass__ = type
9 10
11
12 commentChar = '#'
13
14 delimiterChar = '%';
10 15
11 ngsimUserTypes = {'twowheels':1, 16 ngsimUserTypes = {'twowheels':1,
12 'car':2, 17 'car':2,
13 'truck':3} 18 'truck':3}
14 19
15 ######################### 20 #########################
16 # txt files
17 #########################
18
19
20
21 #########################
22 # Sqlite 21 # Sqlite
23 ######################### 22 #########################
24 23
25 def writeTrajectoriesToSqlite(objects, outFile, trajectoryType, objectNumbers = -1): 24 # utils
25 def printDBError(error):
26 print('DB Error: {}'.format(error))
27
28 def dropTables(connection, tableNames):
29 'deletes the table with names in tableNames'
30 try:
31 cursor = connection.cursor()
32 for tableName in tableNames:
33 cursor.execute('DROP TABLE IF EXISTS '+tableName)
34 except sqlite3.OperationalError as error:
35 printDBError(error)
36
37 # TODO: add test if database connection is open
38 # IO to sqlite
39 def writeTrajectoriesToSqlite(objects, outputFilename, trajectoryType, objectNumbers = -1):
26 """ 40 """
27 This function writers trajectories to a specified sqlite file 41 This function writers trajectories to a specified sqlite file
28 @param[in] objects -> a list of trajectories 42 @param[in] objects -> a list of trajectories
29 @param[in] trajectoryType - 43 @param[in] trajectoryType -
30 @param[out] outFile -> the .sqlite file containting the written objects 44 @param[out] outputFilename -> the .sqlite file containting the written objects
31 @param[in] objectNumber : number of objects loaded 45 @param[in] objectNumber : number of objects loaded
32 """ 46 """
33 47 connection = sqlite3.connect(outputFilename)
34 import sqlite3 48 cursor = connection.cursor()
35 connection = sqlite3.connect(outFile) 49
36 cursor = connection.cursor() 50 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))"
37
38 schema = "CREATE TABLE \"positions\"(trajectory_id INTEGER,frame_number INTEGER, x_coordinate REAL, y_coordinate REAL, PRIMARY KEY(trajectory_id, frame_number))"
39 cursor.execute(schema) 51 cursor.execute(schema)
40 52
41 trajectory_id = 0 53 trajectory_id = 0
42 frame_number = 0 54 frame_number = 0
43 if trajectoryType == 'feature': 55 if trajectoryType == 'feature':
48 for position in trajectory.getPositions(): 60 for position in trajectory.getPositions():
49 frame_number += 1 61 frame_number += 1
50 query = "insert into positions (trajectory_id, frame_number, x_coordinate, y_coordinate) values (?,?,?,?)" 62 query = "insert into positions (trajectory_id, frame_number, x_coordinate, y_coordinate) values (?,?,?,?)"
51 cursor.execute(query,(trajectory_id,frame_number,position.x,position.y)) 63 cursor.execute(query,(trajectory_id,frame_number,position.x,position.y))
52 64
53 connection.commit() 65 connection.commit()
66 connection.close()
67
68 def writeFeaturesToSqlite(objects, outputFilename, trajectoryType, objectNumbers = -1):
69 '''write features trajectories maintain trajectory ID,velocities dataset '''
70 connection = sqlite3.connect(outputFilename)
71 cursor = connection.cursor()
72
73 cursor.execute("CREATE TABLE IF NOT EXISTS \"positions\"(trajectory_id INTEGER,frame_number INTEGER, x_coordinate REAL, y_coordinate REAL, PRIMARY KEY(trajectory_id, frame_number))")
74 cursor.execute("CREATE TABLE IF NOT EXISTS \"velocities\"(trajectory_id INTEGER,frame_number INTEGER, x_coordinate REAL, y_coordinate REAL, PRIMARY KEY(trajectory_id, frame_number))")
75
76 if trajectoryType == 'feature':
77 if type(objectNumbers) == int and objectNumbers == -1:
78 for trajectory in objects:
79 trajectory_id = trajectory.num
80 frame_number = trajectory.timeInterval.first
81 for position,velocity in zip(trajectory.getPositions(),trajectory.getVelocities()):
82 cursor.execute("insert into positions (trajectory_id, frame_number, x_coordinate, y_coordinate) values (?,?,?,?)",(trajectory_id,frame_number,position.x,position.y))
83 cursor.execute("insert into velocities (trajectory_id, frame_number, x_coordinate, y_coordinate) values (?,?,?,?)",(trajectory_id,frame_number,velocity.x,velocity.y))
84 frame_number += 1
85
86 connection.commit()
87 connection.close()
88
89 def writePrototypesToSqlite(prototypes,nMatching, outputFilename):
90 """ prototype dataset is a dictionary with keys== routes, values== prototypes Ids """
91 connection = sqlite3.connect(outputFilename)
92 cursor = connection.cursor()
93
94 cursor.execute("CREATE TABLE IF NOT EXISTS \"prototypes\"(prototype_id INTEGER,routeIDstart INTEGER,routeIDend INTEGER, nMatching INTEGER, PRIMARY KEY(prototype_id))")
95
96 for route in prototypes.keys():
97 if prototypes[route]!=[]:
98 for i in prototypes[route]:
99 cursor.execute("insert into prototypes (prototype_id, routeIDstart,routeIDend, nMatching) values (?,?,?,?)",(i,route[0],route[1],nMatching[route][i]))
100
101 connection.commit()
102 connection.close()
103
104 def loadPrototypesFromSqlite(filename):
105 """
106 This function loads the prototype file in the database
107 It returns a dictionary for prototypes for each route and nMatching
108 """
109 prototypes = {}
110 nMatching={}
111
112 connection = sqlite3.connect(filename)
113 cursor = connection.cursor()
114
115 try:
116 cursor.execute('SELECT * from prototypes order by prototype_id, routeIDstart,routeIDend, nMatching')
117 except sqlite3.OperationalError as error:
118 utils.printDBError(error)
119 return []
120
121 for row in cursor:
122 route=(row[1],row[2])
123 if route not in prototypes.keys():
124 prototypes[route]=[]
125 prototypes[route].append(row[0])
126 nMatching[row[0]]=row[3]
127
128 connection.close()
129 return prototypes,nMatching
130
131 def writeLabelsToSqlite(labels, outputFilename):
132 """ labels is a dictionary with keys: routes, values: prototypes Ids
133 """
134 connection = sqlite3.connect(outputFilename)
135 cursor = connection.cursor()
136
137 cursor.execute("CREATE TABLE IF NOT EXISTS \"labels\"(object_id INTEGER,routeIDstart INTEGER,routeIDend INTEGER, prototype_id INTEGER, PRIMARY KEY(object_id))")
138
139 for route in labels.keys():
140 if labels[route]!=[]:
141 for i in labels[route]:
142 for j in labels[route][i]:
143 cursor.execute("insert into labels (object_id, routeIDstart,routeIDend, prototype_id) values (?,?,?,?)",(j,route[0],route[1],i))
144
145 connection.commit()
146 connection.close()
147
148 def loadLabelsFromSqlite(filename):
149 labels = {}
150
151 connection = sqlite3.connect(filename)
152 cursor = connection.cursor()
153
154 try:
155 cursor.execute('SELECT * from labels order by object_id, routeIDstart,routeIDend, prototype_id')
156 except sqlite3.OperationalError as error:
157 utils.printDBError(error)
158 return []
159
160 for row in cursor:
161 route=(row[1],row[2])
162 p=row[3]
163 if route not in labels.keys():
164 labels[route]={}
165 if p not in labels[route].keys():
166 labels[route][p]=[]
167 labels[route][p].append(row[0])
168
169 connection.close()
170 return labels
171
172 def writeRoutesToSqlite(Routes, outputFilename):
173 """ This function writes the activity path define by start and end IDs"""
174 connection = sqlite3.connect(outputFilename)
175 cursor = connection.cursor()
176
177 cursor.execute("CREATE TABLE IF NOT EXISTS \"routes\"(object_id INTEGER,routeIDstart INTEGER,routeIDend INTEGER, PRIMARY KEY(object_id))")
178
179 for route in Routes.keys():
180 if Routes[route]!=[]:
181 for i in Routes[route]:
182 cursor.execute("insert into routes (object_id, routeIDstart,routeIDend) values (?,?,?)",(i,route[0],route[1]))
183
184 connection.commit()
185 connection.close()
186
187 def loadRoutesFromSqlite(filename):
188 Routes = {}
189
190 connection = sqlite3.connect(filename)
191 cursor = connection.cursor()
192
193 try:
194 cursor.execute('SELECT * from routes order by object_id, routeIDstart,routeIDend')
195 except sqlite3.OperationalError as error:
196 utils.printDBError(error)
197 return []
198
199 for row in cursor:
200 route=(row[1],row[2])
201 if route not in Routes.keys():
202 Routes[route]=[]
203 Routes[route].append(row[0])
204
205 connection.close()
206 return Routes
207
208 def setRoutes(filename, objects):
209 connection = sqlite3.connect(filename)
210 cursor = connection.cursor()
211 for obj in objects:
212 cursor.execute('update objects set startRouteID = {} where object_id = {}'.format(obj.startRouteID, obj.getNum()))
213 cursor.execute('update objects set endRouteID = {} where object_id = {}'.format(obj.endRouteID, obj.getNum()))
214 connection.commit()
215 connection.close()
216
217 def setRoadUserTypes(filename, objects):
218 '''Saves the user types of the objects in the sqlite database stored in filename
219 The objects should exist in the objects table'''
220 connection = sqlite3.connect(filename)
221 cursor = connection.cursor()
222 for obj in objects:
223 cursor.execute('update objects set road_user_type = {} where object_id = {}'.format(obj.getUserType(), obj.getNum()))
224 connection.commit()
54 connection.close() 225 connection.close()
55 226
56 def loadPrototypeMatchIndexesFromSqlite(filename): 227 def loadPrototypeMatchIndexesFromSqlite(filename):
57 """ 228 """
58 This function loads the prototypes table in the database of name <filename>. 229 This function loads the prototypes table in the database of name <filename>.
59 It returns a list of tuples representing matching ids : [(prototype_id, matched_trajectory_id),...] 230 It returns a list of tuples representing matching ids : [(prototype_id, matched_trajectory_id),...]
60 """ 231 """
61 matched_indexes = [] 232 matched_indexes = []
62 233
63 import sqlite3
64 connection = sqlite3.connect(filename) 234 connection = sqlite3.connect(filename)
65 cursor = connection.cursor() 235 cursor = connection.cursor()
66 236
67 try: 237 try:
68 cursor.execute('SELECT * from prototypes order by prototype_id, trajectory_id_matched') 238 cursor.execute('SELECT * from prototypes order by prototype_id, trajectory_id_matched')
69 except sqlite3.OperationalError as err: 239 except sqlite3.OperationalError as error:
70 print('DB Error: {0}'.format(err)) 240 printDBError(error)
71 return [] 241 return []
72 242
73 for row in cursor: 243 for row in cursor:
74 matched_indexes.append((row[0],row[1])) 244 matched_indexes.append((row[0],row[1]))
75 245
76 connection.close() 246 connection.close()
77 return matched_indexes 247 return matched_indexes
78 248
79 def getTrajectoryIdQuery(objectNumbers, trajectoryType): 249 def getTrajectoryIdQuery(objectNumbers, trajectoryType):
80 if trajectoryType == 'feature': 250 if trajectoryType == 'feature':
81 statementBeginning = ' where trajectory_id' 251 statementBeginning = 'where trajectory_id '
82 elif trajectoryType == 'object': 252 elif trajectoryType == 'object':
83 statementBeginning = ' and OF.object_id' 253 statementBeginning = 'and OF.object_id '
254 elif trajectoryType == 'bbtop' or 'bbbottom':
255 statementBeginning = 'where object_id '
84 else: 256 else:
85 print('no trajectory type was chosen') 257 print('no trajectory type was chosen')
86 258
87 if type(objectNumbers) == int: 259 if objectNumbers is None:
88 if objectNumbers == -1: 260 query = ''
89 query = '' 261 elif type(objectNumbers) == int:
90 else: 262 query = statementBeginning+'between 0 and {0} '.format(objectNumbers)
91 query = statementBeginning+' between 0 and {0}'.format(objectNumbers)
92 elif type(objectNumbers) == list: 263 elif type(objectNumbers) == list:
93 query = statementBeginning+' in ('+', '.join([str(n) for n in objectNumbers])+')' 264 query = statementBeginning+'in ('+', '.join([str(n) for n in objectNumbers])+') '
94 return query 265 return query
95 266
96 def loadTrajectoriesFromTable(connection, tableName, trajectoryType, objectNumbers = -1): 267 def loadTrajectoriesFromTable(connection, tableName, trajectoryType, objectNumbers = None):
97 '''Loads trajectories (in the general sense) from the given table 268 '''Loads trajectories (in the general sense) from the given table
98 can be positions or velocities 269 can be positions or velocities
99 270
100 returns a moving object''' 271 returns a moving object'''
101 import sqlite3 272 cursor = connection.cursor()
102 273
103 cursor = connection.cursor() 274 try:
104 275 idQuery = getTrajectoryIdQuery(objectNumbers, trajectoryType)
105 try:
106 if trajectoryType == 'feature': 276 if trajectoryType == 'feature':
107 trajectoryIdQuery = getTrajectoryIdQuery(objectNumbers, trajectoryType) 277 queryStatement = 'SELECT * from '+tableName+' '+idQuery+'ORDER BY trajectory_id, frame_number'
108 cursor.execute('SELECT * from '+tableName+trajectoryIdQuery+' order by trajectory_id, frame_number') 278 cursor.execute(queryStatement)
279 logging.debug(queryStatement)
109 elif trajectoryType == 'object': 280 elif trajectoryType == 'object':
110 objectIdQuery = getTrajectoryIdQuery(objectNumbers, trajectoryType) 281 queryStatement = '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 '+idQuery+'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 object_id, frame_number') 282 cursor.execute(queryStatement)
283 logging.debug(queryStatement)
284 elif trajectoryType in ['bbtop', 'bbbottom']:
285 if trajectoryType == 'bbtop':
286 corner = 'top_left'
287 elif trajectoryType == 'bbbottom':
288 corner = 'bottom_right'
289 queryStatement = 'SELECT object_id, frame_number, x_'+corner+', y_'+corner+' FROM '+tableName+' '+trajectoryIdQuery+'ORDER BY object_id, frame_number'
290 cursor.execute(queryStatement)
291 logging.debug(queryStatement)
112 else: 292 else:
113 print('no trajectory type was chosen') 293 print('no trajectory type was chosen')
114 except sqlite3.OperationalError as err: 294 except sqlite3.OperationalError as error:
115 print('DB Error: {0}'.format(err)) 295 printDBError(error)
116 return [] 296 return []
117 297
118 objId = -1 298 objId = -1
119 obj = None 299 obj = None
120 objects = [] 300 objects = []
121 for row in cursor: 301 for row in cursor:
122 if row[0] != objId: 302 if row[0] != objId:
123 objId = row[0] 303 objId = row[0]
124 if obj: 304 if obj != None and obj.length() == obj.positions.length():
125 objects.append(obj) 305 objects.append(obj)
306 elif obj != None:
307 print('Object {} is missing {} positions'.format(obj.getNum(), int(obj.length())-obj.positions.length()))
126 obj = moving.MovingObject(row[0], timeInterval = moving.TimeInterval(row[1], row[1]), positions = moving.Trajectory([[row[2]],[row[3]]])) 308 obj = moving.MovingObject(row[0], timeInterval = moving.TimeInterval(row[1], row[1]), positions = moving.Trajectory([[row[2]],[row[3]]]))
127 else: 309 else:
128 obj.timeInterval.last = row[1] 310 obj.timeInterval.last = row[1]
129 obj.positions.addPositionXY(row[2],row[3]) 311 obj.positions.addPositionXY(row[2],row[3])
130 312
131 if obj: 313 if obj != None and obj.length() == obj.positions.length():
132 objects.append(obj) 314 objects.append(obj)
315 elif obj != None:
316 print('Object {} is missing {} positions'.format(obj.getNum(), int(obj.length())-obj.positions.length()))
133 317
134 return objects 318 return objects
135 319
136 def loadTrajectoriesFromSqlite(filename, trajectoryType, objectNumbers = -1): 320 def loadUserTypesFromTable(cursor, trajectoryType, objectNumbers):
137 '''Loads nObjects or the indices in objectNumbers from the database 321 objectIdQuery = getTrajectoryIdQuery(objectNumbers, trajectoryType)
138 TODO: load feature numbers and not average feature trajectories 322 if objectIdQuery == '':
139 TODO: other ways of averaging trajectories (load all points, sorted by frame_number and leave the agregation to be done in python) 323 cursor.execute('SELECT object_id, road_user_type from objects')
140 ''' 324 else:
141 import sqlite3 325 cursor.execute('SELECT object_id, road_user_type from objects where '+objectIdQuery[7:])
142 326 userTypes = {}
143 connection = sqlite3.connect(filename) # add test if it open 327 for row in cursor:
328 userTypes[row[0]] = row[1]
329 return userTypes
330
331 def loadTrajectoriesFromSqlite(filename, trajectoryType, objectNumbers = None):
332 '''Loads the first objectNumbers objects or the indices in objectNumbers from the database'''
333 connection = sqlite3.connect(filename)
144 334
145 objects = loadTrajectoriesFromTable(connection, 'positions', trajectoryType, objectNumbers) 335 objects = loadTrajectoriesFromTable(connection, 'positions', trajectoryType, objectNumbers)
146 objectVelocities = loadTrajectoriesFromTable(connection, 'velocities', trajectoryType, objectNumbers) 336 objectVelocities = loadTrajectoriesFromTable(connection, 'velocities', trajectoryType, objectNumbers)
147 337
148 if len(objectVelocities) > 0: 338 if len(objectVelocities) > 0:
149 for o,v in zip(objects, objectVelocities): 339 for o,v in zip(objects, objectVelocities):
150 if o.num == v.num: 340 if o.getNum() == v.getNum():
151 o.velocities = v.positions 341 o.velocities = v.positions
342 o.velocities.duplicateLastPosition() # avoid having velocity shorter by one position than positions
152 else: 343 else:
153 print('Could not match positions {0} with velocities {1}'.format(o.num, v.num)) 344 print('Could not match positions {0} with velocities {1}'.format(o.getNum(), v.getNum()))
154 345
155 if trajectoryType == 'object': 346 if trajectoryType == 'object':
156 cursor = connection.cursor() 347 cursor = connection.cursor()
157 try: 348 try:
349 # attribute feature numbers to objects
158 objectIdQuery = getTrajectoryIdQuery(objectNumbers, trajectoryType) 350 objectIdQuery = getTrajectoryIdQuery(objectNumbers, trajectoryType)
159 cursor.execute('SELECT P.trajectory_id, OF.object_id from positions P, objects_features OF where P.trajectory_id = OF.trajectory_id '+objectIdQuery+' group by P.trajectory_id order by OF.object_id') 351 queryStatement = 'SELECT P.trajectory_id, OF.object_id from positions P, objects_features OF where P.trajectory_id = OF.trajectory_id '+objectIdQuery+'group by P.trajectory_id order by OF.object_id' # order is important to group all features per object
160 352 cursor.execute(queryStatement)
161 # attribute feature numbers to objects 353 logging.debug(queryStatement)
162 objId = -1 354
163 featureNumbers = {} 355 featureNumbers = {}
164 for row in cursor: 356 for row in cursor:
165 if row[1] != objId: 357 objId = row[1]
166 objId = row[1] 358 if objId not in featureNumbers:
167 featureNumbers[objId] = [row[0]] 359 featureNumbers[objId] = [row[0]]
168 else: 360 else:
169 featureNumbers[objId].append(row[0]) 361 featureNumbers[objId].append(row[0])
170 362
171 for obj in objects: 363 for obj in objects:
172 obj.featureNumbers = featureNumbers[obj.num] 364 obj.featureNumbers = featureNumbers[obj.getNum()]
173 except sqlite3.OperationalError as err: 365
174 print('DB Error: {0}'.format(err)) 366 # load userType
175 return [] 367 userTypes = loadUserTypesFromTable(cursor, trajectoryType, objectNumbers)
368 for obj in objects:
369 obj.userType = userTypes[obj.getNum()]
370
371 except sqlite3.OperationalError as error:
372 printDBError(error)
373 objects = []
176 374
177 connection.close() 375 connection.close()
178 return objects 376 return objects
179 377
180 def removeObjectsFromSqlite(filename): 378 def loadGroundTruthFromSqlite(filename, gtType, gtNumbers = None):
181 'Removes the objects and object_features tables in the filename' 379 'Loads bounding box annotations (ground truth) from an SQLite '
182 import sqlite3 380 connection = sqlite3.connect(filename)
183 connection = sqlite3.connect(filename) 381 gt = []
184 utils.dropTables(connection, ['objects', 'objects_features']) 382
185 connection.close() 383 if gtType == 'bb':
186 384 topCorners = loadTrajectoriesFromTable(connection, 'bounding_boxes', 'bbtop', gtNumbers)
385 bottomCorners = loadTrajectoriesFromTable(connection, 'bounding_boxes', 'bbbottom', gtNumbers)
386 userTypes = loadUserTypesFromTable(connection.cursor(), 'object', gtNumbers) # string format is same as object
387
388 for t, b in zip(topCorners, bottomCorners):
389 num = t.getNum()
390 if t.getNum() == b.getNum():
391 annotation = moving.BBAnnotation(num, t.getTimeInterval(), t, b, userTypes[num])
392 gt.append(annotation)
393 else:
394 print ('Unknown type of annotation {}'.format(gtType))
395
396 connection.close()
397 return gt
398
399 def deleteFromSqlite(filename, dataType):
400 'Deletes (drops) some tables in the filename depending on type of data'
401 import os
402 if os.path.isfile(filename):
403 connection = sqlite3.connect(filename)
404 if dataType == 'object':
405 dropTables(connection, ['objects', 'objects_features'])
406 elif dataType == 'interaction':
407 dropTables(connection, ['interactions', 'indicators'])
408 elif dataType == 'bb':
409 dropTables(connection, ['bounding_boxes'])
410 else:
411 print('Unknown data type {} to delete from database'.format(dataType))
412 connection.close()
413 else:
414 print('{} does not exist'.format(filename))
415
416 def createInteractionTable(cursor):
417 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))')
418
419 def createIndicatorTables(cursor):
420 # 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))')
421 # 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))')
422 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))')
423
424 def saveInteraction(cursor, interaction):
425 roadUserNumbers = list(interaction.getRoadUserNumbers())
426 cursor.execute('INSERT INTO interactions VALUES({}, {}, {}, {}, {})'.format(interaction.getNum(), roadUserNumbers[0], roadUserNumbers[1], interaction.getFirstInstant(), interaction.getLastInstant()))
427
428 def saveInteractions(filename, interactions):
429 'Saves the interactions in the table'
430 connection = sqlite3.connect(filename)
431 cursor = connection.cursor()
432 try:
433 createInteractionTable(cursor)
434 for inter in interactions:
435 saveInteraction(cursor, inter)
436 except sqlite3.OperationalError as error:
437 printDBError(error)
438 connection.commit()
439 connection.close()
440
441 def saveIndicator(cursor, interactionNum, indicator):
442 for instant in indicator.getTimeInterval():
443 if indicator[instant]:
444 cursor.execute('INSERT INTO indicators VALUES({}, {}, {}, {})'.format(interactionNum, events.Interaction.indicatorNameToIndices[indicator.getName()], instant, indicator[instant]))
445
446 def saveIndicators(filename, interactions, indicatorNames = events.Interaction.indicatorNames):
447 'Saves the indicator values in the table'
448 connection = sqlite3.connect(filename)
449 cursor = connection.cursor()
450 try:
451 createInteractionTable(cursor)
452 createIndicatorTables(cursor)
453 for inter in interactions:
454 saveInteraction(cursor, inter)
455 for indicatorName in indicatorNames:
456 indicator = inter.getIndicator(indicatorName)
457 if indicator != None:
458 saveIndicator(cursor, inter.getNum(), indicator)
459 except sqlite3.OperationalError as error:
460 printDBError(error)
461 connection.commit()
462 connection.close()
463
464 def loadInteractions(filename):
465 '''Loads interaction and their indicators
466
467 TODO choose the interactions to load'''
468 interactions = []
469 connection = sqlite3.connect(filename)
470 cursor = connection.cursor()
471 try:
472 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')
473 interactionNum = -1
474 indicatorTypeNum = -1
475 tmpIndicators = {}
476 for row in cursor:
477 if row[0] != interactionNum: # save interaction and create new interaction
478 if interactionNum >= 0:
479 interactions.append(events.Interaction(interactionNum, moving.TimeInterval(row[3],row[4]), roadUserNumbers[0], roadUserNumbers[1]))
480 interactions[-1].indicators = tmpIndicators
481 tmpIndicators = {}
482 interactionNum = row[0]
483 roadUserNumbers = row[1:3]
484 if indicatorTypeNum != row[5]:
485 if indicatorTypeNum >= 0:
486 indicatorName = events.Interaction.indicatorNames[indicatorTypeNum]
487 tmpIndicators[indicatorName] = indicators.SeverityIndicator(indicatorName, indicatorValues)
488 indicatorTypeNum = row[5]
489 indicatorValues = {row[6]:row[7]}
490 else:
491 indicatorValues[row[6]] = row[7]
492 if interactionNum >= 0:
493 if indicatorTypeNum >= 0:
494 indicatorName = events.Interaction.indicatorNames[indicatorTypeNum]
495 tmpIndicators[indicatorName] = indicators.SeverityIndicator(indicatorName, indicatorValues)
496 interactions.append(events.Interaction(interactionNum, moving.TimeInterval(row[3],row[4]), roadUserNumbers[0], roadUserNumbers[1]))
497 interactions[-1].indicators = tmpIndicators
498 except sqlite3.OperationalError as error:
499 printDBError(error)
500 return []
501 connection.close()
502 return interactions
503 # load first and last object instants
504 # 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
505
506 def createBoundingBoxTable(filename, invHomography = None):
507 '''Create the table to store the object bounding boxes in image space
508 '''
509 connection = sqlite3.connect(filename)
510 cursor = connection.cursor()
511 try:
512 cursor.execute('CREATE TABLE IF NOT EXISTS bounding_boxes (object_id INTEGER, frame_number INTEGER, x_top_left REAL, y_top_left REAL, x_bottom_right REAL, y_bottom_right REAL, PRIMARY KEY(object_id, frame_number))')
513 cursor.execute('INSERT INTO bounding_boxes SELECT object_id, frame_number, min(x), min(y), max(x), max(y) from '
514 '(SELECT object_id, frame_number, (x*{}+y*{}+{})/w as x, (x*{}+y*{}+{})/w as y from '
515 '(SELECT OF.object_id, P.frame_number, P.x_coordinate as x, P.y_coordinate as y, P.x_coordinate*{}+P.y_coordinate*{}+{} as w from positions P, objects_features OF where P.trajectory_id = OF.trajectory_id)) '.format(invHomography[0,0], invHomography[0,1], invHomography[0,2], invHomography[1,0], invHomography[1,1], invHomography[1,2], invHomography[2,0], invHomography[2,1], invHomography[2,2])+
516 'GROUP BY object_id, frame_number')
517 except sqlite3.OperationalError as error:
518 printDBError(error)
519 connection.commit()
520 connection.close()
521
522 def loadBoundingBoxTableForDisplay(filename):
523 connection = sqlite3.connect(filename)
524 cursor = connection.cursor()
525 boundingBoxes = {} # list of bounding boxes for each instant
526 try:
527 cursor.execute('SELECT name FROM sqlite_master WHERE type=\'table\' AND name=\'bounding_boxes\'')
528 result = [row for row in cursor]
529 if len(result) > 0:
530 cursor.execute('SELECT * FROM bounding_boxes')
531 for row in cursor:
532 boundingBoxes.setdefault(row[1], []).append([moving.Point(row[2], row[3]), moving.Point(row[4], row[5])])
533 except sqlite3.OperationalError as error:
534 printDBError(error)
535 return boundingBoxes
536 connection.close()
537 return boundingBoxes
538
539 def loadBoundingBoxTable(filename):
540 connection = sqlite3.connect(filename)
541 cursor = connection.cursor()
542 boundingBoxes = []
543
544 try:
545 pass
546 except sqlite3.OperationalError as error:
547 printDBError(error)
548 return boundingBoxes
549 connection.close()
550 return boundingBoxes
551
552
553 #########################
554 # txt files
555 #########################
556
557 def openCheck(filename, option = 'r', quitting = False):
558 '''Open file filename in read mode by default
559 and checks it is open'''
560 try:
561 return open(filename, option)
562 except IOError:
563 print 'File %s could not be opened.' % filename
564 if quitting:
565 from sys import exit
566 exit()
567 return None
568
569 def readline(f, commentCharacters = commentChar):
570 '''Modified readline function to skip comments
571 Can take a list of characters or a string (in will work in both)'''
572 s = f.readline()
573 while (len(s) > 0) and s[0] in commentCharacters:
574 s = f.readline()
575 return s.strip()
576
577 def getLines(f, commentCharacters = commentChar):
578 '''Gets a complete entry (all the lines) in between delimiterChar.'''
579 dataStrings = []
580 s = readline(f, commentCharacters)
581 while len(s) > 0:
582 dataStrings += [s.strip()]
583 s = readline(f, commentCharacters)
584 return dataStrings
585
586 def writeList(filename, l):
587 f = openCheck(filename, 'w')
588 for x in l:
589 f.write('{}\n'.format(x))
590 f.close()
591
592 def loadListStrings(filename, commentCharacters = commentChar):
593 f = openCheck(filename, 'r')
594 result = getLines(f, commentCharacters)
595 f.close()
596 return result
597
598 def getValuesFromINIFile(filename, option, delimiterChar = '=', commentCharacters = commentChar):
599 values = []
600 for l in loadListStrings(filename, commentCharacters):
601 if l.startswith(option):
602 values.append(l.split(delimiterChar)[1].strip())
603 return values
604
605 class FakeSecHead(object):
606 '''Add fake section header [asection]
607
608 from http://stackoverflow.com/questions/2819696/parsing-properties-file-in-python/2819788#2819788
609 use read_file in Python 3.2+
610 '''
611 def __init__(self, fp):
612 self.fp = fp
613 self.sechead = '[main]\n'
614
615 def readline(self):
616 if self.sechead:
617 try: return self.sechead
618 finally: self.sechead = None
619 else: return self.fp.readline()
620
621 def loadTrajectoriesFromVissimFile(filename, simulationStepsPerTimeUnit, nObjects = -1, warmUpLastInstant = None):
622 '''Reads data from VISSIM .fzp trajectory file
623 simulationStepsPerTimeUnit is the number of simulation steps per unit of time used by VISSIM
624 for example, there seems to be 5 simulation steps per simulated second in VISSIM,
625 so simulationStepsPerTimeUnit should be 5,
626 so that all times correspond to the number of the simulation step (and can be stored as integers)
627
628 Assumed to be sorted over time'''
629 objects = {} # dictionary of objects index by their id
630 firstInstants = {}
631
632 inputfile = openCheck(filename, quitting = True)
633
634 # data = pd.read_csv(filename, skiprows=15, delimiter=';')
635 # skip header: 15 lines + 1
636 line = readline(inputfile, '*$')
637 while len(line) > 0:#for line in inputfile:
638 data = line.strip().split(';')
639 objNum = int(data[1])
640 instant = int(float(data[0])*simulationStepsPerTimeUnit)
641 s = float(data[4])
642 y = float(data[5])
643 lane = data[2]+'_'+data[3]
644 if objNum not in firstInstants:
645 firstInstants[objNum] = instant
646 if warmUpLastInstant == None or firstInstants[objNum] >= warmUpLastInstant:
647 if nObjects < 0 or len(objects) < nObjects:
648 objects[objNum] = moving.MovingObject(num = objNum, timeInterval = moving.TimeInterval(instant, instant))
649 objects[objNum].curvilinearPositions = moving.CurvilinearTrajectory()
650 if (warmUpLastInstant == None or firstInstants[objNum] >= warmUpLastInstant) and objNum in objects:
651 objects[objNum].timeInterval.last = instant
652 objects[objNum].curvilinearPositions.addPositionSYL(s, y, lane)
653 line = readline(inputfile, '*$')
654
655 return objects.values()
656
187 def loadTrajectoriesFromNgsimFile(filename, nObjects = -1, sequenceNum = -1): 657 def loadTrajectoriesFromNgsimFile(filename, nObjects = -1, sequenceNum = -1):
188 '''Reads data from the trajectory data provided by NGSIM project 658 '''Reads data from the trajectory data provided by NGSIM project
189 and returns the list of Feature objects''' 659 and returns the list of Feature objects'''
190 objects = [] 660 objects = []
191 661
192 input = utils.openCheck(filename) 662 inputfile = openCheck(filename, quitting = True)
193 if not input:
194 import sys
195 sys.exit()
196 663
197 def createObject(numbers): 664 def createObject(numbers):
198 firstFrameNum = int(numbers[1]) 665 firstFrameNum = int(numbers[1])
199 # do the geometry and usertype 666 # do the geometry and usertype
200 667
209 obj.laneNums = [int(numbers[13])] 676 obj.laneNums = [int(numbers[13])]
210 obj.precedingVehicles = [int(numbers[14])] # lead vehicle (before) 677 obj.precedingVehicles = [int(numbers[14])] # lead vehicle (before)
211 obj.followingVehicles = [int(numbers[15])] # following vehicle (after) 678 obj.followingVehicles = [int(numbers[15])] # following vehicle (after)
212 obj.spaceHeadways = [float(numbers[16])] # feet 679 obj.spaceHeadways = [float(numbers[16])] # feet
213 obj.timeHeadways = [float(numbers[17])] # seconds 680 obj.timeHeadways = [float(numbers[17])] # seconds
214 obj.curvilinearPositions = moving.Trajectory([[float(numbers[5])],[float(numbers[4])]]) # X is the longitudinal coordinate 681 obj.curvilinearPositions = moving.CurvilinearTrajectory([float(numbers[5])],[float(numbers[4])], obj.laneNums) # X is the longitudinal coordinate
215 obj.speeds = [float(numbers[11])] 682 obj.speeds = [float(numbers[11])]
216 obj.size = [float(numbers[8]), float(numbers[9])] # 8 lengh, 9 width # TODO: temporary, should use a geometry object 683 obj.size = [float(numbers[8]), float(numbers[9])] # 8 lengh, 9 width # TODO: temporary, should use a geometry object
217 return obj 684 return obj
218 685
219 numbers = input.readline().strip().split() 686 numbers = readline(inputfile).strip().split()
220 if (len(numbers) > 0): 687 if (len(numbers) > 0):
221 obj = createObject(numbers) 688 obj = createObject(numbers)
222 689
223 for line in input: 690 for line in inputfile:
224 numbers = line.strip().split() 691 numbers = line.strip().split()
225 if obj.num != int(numbers[0]): 692 if obj.getNum() != int(numbers[0]):
226 # check and adapt the length to deal with issues in NGSIM data 693 # check and adapt the length to deal with issues in NGSIM data
227 if (obj.length() != obj.positions.length()): 694 if (obj.length() != obj.positions.length()):
228 print 'length pb with object %s (%d,%d)' % (obj.num,obj.length(),obj.positions.length()) 695 print 'length pb with object %s (%d,%d)' % (obj.getNum(),obj.length(),obj.positions.length())
229 obj.last = obj.getFirstInstant()+obj.positions.length()-1 696 obj.last = obj.getFirstInstant()+obj.positions.length()-1
230 #obj.velocities = utils.computeVelocities(f.positions) # compare norm to speeds ? 697 #obj.velocities = utils.computeVelocities(f.positions) # compare norm to speeds ?
231 objects.append(obj) 698 objects.append(obj)
232 if (nObjects>0) and (len(objects)>=nObjects): 699 if (nObjects>0) and (len(objects)>=nObjects):
233 break 700 break
234 obj = createObject(numbers) 701 obj = createObject(numbers)
235 else: 702 else:
703 obj.laneNums.append(int(numbers[13]))
236 obj.positions.addPositionXY(float(numbers[6]), float(numbers[7])) 704 obj.positions.addPositionXY(float(numbers[6]), float(numbers[7]))
237 obj.curvilinearPositions.addPositionXY(float(numbers[5]), float(numbers[4])) 705 obj.curvilinearPositions.addPositionSYL(float(numbers[5]), float(numbers[4]), obj.laneNums[-1])
238 obj.speeds.append(float(numbers[11])) 706 obj.speeds.append(float(numbers[11]))
239 obj.laneNums.append(int(numbers[13]))
240 obj.precedingVehicles.append(int(numbers[14])) 707 obj.precedingVehicles.append(int(numbers[14]))
241 obj.followingVehicles.append(int(numbers[15])) 708 obj.followingVehicles.append(int(numbers[15]))
242 obj.spaceHeadways.append(float(numbers[16])) 709 obj.spaceHeadways.append(float(numbers[16]))
243 obj.timeHeadways.append(float(numbers[17])) 710 obj.timeHeadways.append(float(numbers[17]))
244 711
245 if (obj.size[0] != float(numbers[8])): 712 if (obj.size[0] != float(numbers[8])):
246 print 'changed length obj %d' % (f.num) 713 print 'changed length obj %d' % (obj.getNum())
247 if (obj.size[1] != float(numbers[9])): 714 if (obj.size[1] != float(numbers[9])):
248 print 'changed width obj %d' % (f.num) 715 print 'changed width obj %d' % (obj.getNum())
249 716
250 input.close() 717 inputfile.close()
251 return objects 718 return objects
252 719
253 def convertNgsimFile(inFile, outFile, append = False, nObjects = -1, sequenceNum = 0): 720 def convertNgsimFile(inputfile, outputfile, append = False, nObjects = -1, sequenceNum = 0):
254 '''Reads data from the trajectory data provided by NGSIM project 721 '''Reads data from the trajectory data provided by NGSIM project
255 and converts to our current format.''' 722 and converts to our current format.'''
256 if append: 723 if append:
257 out = open(outFile,'a') 724 out = openCheck(outputfile,'a')
258 else: 725 else:
259 out = open(outFile,'w') 726 out = openCheck(outputfile,'w')
260 nObjectsPerType = [0,0,0] 727 nObjectsPerType = [0,0,0]
261 728
262 features = loadNgsimFile(inFile, sequenceNum) 729 features = loadNgsimFile(inputfile, sequenceNum)
263 for f in features: 730 for f in features:
264 nObjectsPerType[f.userType-1] += 1 731 nObjectsPerType[f.userType-1] += 1
265 f.write(out) 732 f.write(out)
266 733
267 print nObjectsPerType 734 print nObjectsPerType
268 735
269 out.close() 736 out.close()
737
738 def writePositionsToCsv(f, obj):
739 timeInterval = obj.getTimeInterval()
740 positions = obj.getPositions()
741 curvilinearPositions = obj.getCurvilinearPositions()
742 for i in xrange(int(obj.length())):
743 p1 = positions[i]
744 s = '{},{},{},{}'.format(obj.num,timeInterval[i],p1.x,p1.y)
745 if curvilinearPositions != None:
746 p2 = curvilinearPositions[i]
747 s += ',{},{}'.format(p2[0],p2[1])
748 f.write(s+'\n')
749
750 def writeTrajectoriesToCsv(filename, objects):
751 f = openCheck(filename, 'w')
752 for i,obj in enumerate(objects):
753 writePositionsToCsv(f, obj)
754 f.close()
755
756
757 #########################
758 # Utils to read .ini type text files for configuration, meta data...
759 #########################
760
761 class ProcessParameters:
762 '''Class for all parameters controlling data processing: input,
763 method parameters, etc. for tracking, classification and safety
764
765 Note: framerate is already taken into account'''
766
767 def loadConfigFile(self, filename):
768 from ConfigParser import ConfigParser
769 from numpy import loadtxt
770 from os import path
771
772 config = ConfigParser()
773 config.readfp(FakeSecHead(openCheck(filename)))
774 self.sectionHeader = config.sections()[0]
775 # Tracking/display parameters
776 self.videoFilename = config.get(self.sectionHeader, 'video-filename')
777 self.databaseFilename = config.get(self.sectionHeader, 'database-filename')
778 self.homographyFilename = config.get(self.sectionHeader, 'homography-filename')
779 if (path.exists(self.homographyFilename)):
780 self.homography = loadtxt(self.homographyFilename)
781 else:
782 self.homography = None
783 self.intrinsicCameraFilename = config.get(self.sectionHeader, 'intrinsic-camera-filename')
784 if (path.exists(self.intrinsicCameraFilename)):
785 self.intrinsicCameraMatrix = loadtxt(self.intrinsicCameraFilename)
786 else:
787 self.intrinsicCameraMatrix = None
788 distortionCoefficients = getValuesFromINIFile(filename, 'distortion-coefficients', '=')
789 self.distortionCoefficients = [float(x) for x in distortionCoefficients]
790 self.undistortedImageMultiplication = config.getfloat(self.sectionHeader, 'undistorted-size-multiplication')
791 self.undistort = config.getboolean(self.sectionHeader, 'undistort')
792 self.firstFrameNum = config.getint(self.sectionHeader, 'frame1')
793 self.videoFrameRate = config.getfloat(self.sectionHeader, 'video-fps')
794
795 # Classification parameters
796
797
798 # Safety parameters
799 self.maxPredictedSpeed = config.getfloat(self.sectionHeader, 'max-predicted-speed')/3.6/self.videoFrameRate
800 self.predictionTimeHorizon = config.getfloat(self.sectionHeader, 'prediction-time-horizon')*self.videoFrameRate
801 self.collisionDistance = config.getfloat(self.sectionHeader, 'collision-distance')
802 self.crossingZones = config.getboolean(self.sectionHeader, 'crossing-zones')
803 self.predictionMethod = config.get(self.sectionHeader, 'prediction-method')
804 self.nPredictedTrajectories = config.getint(self.sectionHeader, 'npredicted-trajectories')
805 self.maxNormalAcceleration = config.getfloat(self.sectionHeader, 'max-normal-acceleration')/self.videoFrameRate**2
806 self.maxNormalSteering = config.getfloat(self.sectionHeader, 'max-normal-steering')/self.videoFrameRate
807 self.minExtremeAcceleration = config.getfloat(self.sectionHeader, 'min-extreme-acceleration')/self.videoFrameRate**2
808 self.maxExtremeAcceleration = config.getfloat(self.sectionHeader, 'max-extreme-acceleration')/self.videoFrameRate**2
809 self.maxExtremeSteering = config.getfloat(self.sectionHeader, 'max-extreme-steering')/self.videoFrameRate
810 self.useFeaturesForPrediction = config.getboolean(self.sectionHeader, 'use-features-prediction')
811
812 def __init__(self, filename = None):
813 if filename != None:
814 self.loadConfigFile(filename)
815
816 class SceneParameters:
817 def __init__(self, config, sectionName):
818 from ConfigParser import NoOptionError
819 from ast import literal_eval
820 try:
821 self.sitename = config.get(sectionName, 'sitename')
822 self.databaseFilename = config.get(sectionName, 'data-filename')
823 self.homographyFilename = config.get(sectionName, 'homography-filename')
824 self.calibrationFilename = config.get(sectionName, 'calibration-filename')
825 self.videoFilename = config.get(sectionName, 'video-filename')
826 self.frameRate = config.getfloat(sectionName, 'framerate')
827 self.date = datetime.strptime(config.get(sectionName, 'date'), datetimeFormat) # 2011-06-22 11:00:39
828 self.translation = literal_eval(config.get(sectionName, 'translation')) # = [0.0, 0.0]
829 self.rotation = config.getfloat(sectionName, 'rotation')
830 self.duration = config.getint(sectionName, 'duration')
831 except NoOptionError as e:
832 print(e)
833 print('Not a section for scene meta-data')
834
835 @staticmethod
836 def loadConfigFile(filename):
837 from ConfigParser import ConfigParser
838 config = ConfigParser()
839 config.readfp(openCheck(filename))
840 configDict = dict()
841 for sectionName in config.sections():
842 configDict[sectionName] = SceneParameters(config, sectionName)
843 return configDict
844
270 845
271 if __name__ == "__main__": 846 if __name__ == "__main__":
272 import doctest 847 import doctest
273 import unittest 848 import unittest
274 suite = doctest.DocFileSuite('tests/storage.txt') 849 suite = doctest.DocFileSuite('tests/storage.txt')