Mercurial Hosting > traffic-intelligence

comparison python/storage.py @ 693:5ee22bf7e4d5 dev

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
corrected bug when loading indicator time intervals and updated how queries are created for better legibility
author Nicolas Saunier <nicolas.saunier@polymtl.ca>
date Tue, 30 Jun 2015 15:46:31 -0400
parents 463150a8e129
children 957126bfb456
comparison
equal deleted inserted replaced
692:9a258687af4c 693:5ee22bf7e4d5
288 matched_indexes.append((row[0],row[1])) 288 matched_indexes.append((row[0],row[1]))
289 289
290 connection.close() 290 connection.close()
291 return matched_indexes 291 return matched_indexes
292 292
293 def getTrajectoryIdQuery(objectNumbers, trajectoryType): 293 def getObjectCriteria(objectNumbers):
294 if trajectoryType == 'feature':
295 statementBeginning = 'where trajectory_id '
296 elif trajectoryType == 'object':
297 statementBeginning = 'and OF.object_id '
298 elif trajectoryType == 'bbtop' or 'bbbottom':
299 statementBeginning = 'where object_id '
300 else:
301 print('no trajectory type was chosen')
302
303 if objectNumbers is None: 294 if objectNumbers is None:
304 query = '' 295 query = ''
305 elif type(objectNumbers) == int: 296 elif type(objectNumbers) == int:
306 query = statementBeginning+'between 0 and {0} '.format(objectNumbers-1) 297 query = 'between 0 and {0}'.format(objectNumbers-1)
307 elif type(objectNumbers) == list: 298 elif type(objectNumbers) == list:
308 query = statementBeginning+'in ('+', '.join([str(n) for n in objectNumbers])+') ' 299 query = 'in ('+', '.join([str(n) for n in objectNumbers])+')'
300 else:
301 print('objectNumbers {} are not a known type ({})'.format(objectNumbers, type(objectNumbers)))
302 query = ''
309 return query 303 return query
310 304
311 def loadTrajectoriesFromTable(connection, tableName, trajectoryType, objectNumbers = None): 305 def loadTrajectoriesFromTable(connection, tableName, trajectoryType, objectNumbers = None):
312 '''Loads trajectories (in the general sense) from the given table 306 '''Loads trajectories (in the general sense) from the given table
313 can be positions or velocities 307 can be positions or velocities
314 308
315 returns a moving object''' 309 returns a moving object'''
316 cursor = connection.cursor() 310 cursor = connection.cursor()
317 311
318 try: 312 try:
319 idQuery = getTrajectoryIdQuery(objectNumbers, trajectoryType) 313 objectCriteria = getObjectCriteria(objectNumbers)
320 if trajectoryType == 'feature': 314 if trajectoryType == 'feature':
321 queryStatement = 'SELECT * from '+tableName+' '+idQuery+'ORDER BY trajectory_id, frame_number' 315 queryStatement = 'SELECT * from '+tableName
316 if objectNumbers is not None:
317 queryStatement += ' where trajectory_id '+objectCriteria
318 queryStatement += ' ORDER BY trajectory_id, frame_number'
322 cursor.execute(queryStatement) 319 cursor.execute(queryStatement)
323 logging.debug(queryStatement) 320 logging.debug(queryStatement)
324 elif trajectoryType == 'object': 321 elif trajectoryType == 'object':
325 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' 322 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'
323 if objectNumbers is not None:
324 queryStatement += ' and OF.object_id '+objectCriteria
325 queryStatement += ' group by OF.object_id, P.frame_number ORDER BY OF.object_id, P.frame_number'
326 cursor.execute(queryStatement) 326 cursor.execute(queryStatement)
327 logging.debug(queryStatement) 327 logging.debug(queryStatement)
328 elif trajectoryType in ['bbtop', 'bbbottom']: 328 elif trajectoryType in ['bbtop', 'bbbottom']:
329 if trajectoryType == 'bbtop': 329 if trajectoryType == 'bbtop':
330 corner = 'top_left' 330 corner = 'top_left'
331 elif trajectoryType == 'bbbottom': 331 elif trajectoryType == 'bbbottom':
332 corner = 'bottom_right' 332 corner = 'bottom_right'
333 queryStatement = 'SELECT object_id, frame_number, x_'+corner+', y_'+corner+' FROM '+tableName+' '+idQuery+'ORDER BY object_id, frame_number' 333 queryStatement = 'SELECT object_id, frame_number, x_'+corner+', y_'+corner+' FROM '+tableName
334 if objectNumbers is not None:
335 queryStatement += ' where object_id '+objectCriteria
336 queryStatement += ' ORDER BY object_id, frame_number'
334 cursor.execute(queryStatement) 337 cursor.execute(queryStatement)
335 logging.debug(queryStatement) 338 logging.debug(queryStatement)
336 else: 339 else:
337 print('no trajectory type was chosen') 340 print('no trajectory type was chosen')
338 except sqlite3.OperationalError as error: 341 except sqlite3.OperationalError as error:
360 print('Object {} is missing {} positions'.format(obj.getNum(), int(obj.length())-obj.positions.length())) 363 print('Object {} is missing {} positions'.format(obj.getNum(), int(obj.length())-obj.positions.length()))
361 364
362 return objects 365 return objects
363 366
364 def loadUserTypesFromTable(cursor, trajectoryType, objectNumbers): 367 def loadUserTypesFromTable(cursor, trajectoryType, objectNumbers):
365 objectIdQuery = getTrajectoryIdQuery(objectNumbers, trajectoryType) 368 objectCriteria = getObjectCriteria(objectNumbers)
366 if objectIdQuery == '': 369 queryStatement = 'SELECT object_id, road_user_type from objects'
367 cursor.execute('SELECT object_id, road_user_type from objects') 370 if objectNumbers is not None:
368 else: 371 queryStatement += ' where object_id '+objectCriteria
369 cursor.execute('SELECT object_id, road_user_type from objects where '+objectIdQuery[7:]) 372 cursor.execute(queryStatement)
370 userTypes = {} 373 userTypes = {}
371 for row in cursor: 374 for row in cursor:
372 userTypes[row[0]] = row[1] 375 userTypes[row[0]] = row[1]
373 return userTypes 376 return userTypes
374 377
389 392
390 if trajectoryType == 'object': 393 if trajectoryType == 'object':
391 cursor = connection.cursor() 394 cursor = connection.cursor()
392 try: 395 try:
393 # attribute feature numbers to objects 396 # attribute feature numbers to objects
394 objectIdQuery = getTrajectoryIdQuery(objectNumbers, trajectoryType) 397 objectCriteria = getObjectCriteria(objectNumbers)
395 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 398 queryStatement = 'SELECT P.trajectory_id, OF.object_id from positions P, objects_features OF where P.trajectory_id = OF.trajectory_id'
399 if objectNumbers is not None:
400 queryStatement += ' and OF.object_id '+objectCriteria
401 queryStatement += ' group by P.trajectory_id order by OF.object_id' # order is important to group all features per object
396 cursor.execute(queryStatement) 402 cursor.execute(queryStatement)
397 logging.debug(queryStatement) 403 logging.debug(queryStatement)
398 404
399 featureNumbers = {} 405 featureNumbers = {}
400 for row in cursor: 406 for row in cursor:
519 TODO choose the interactions to load''' 525 TODO choose the interactions to load'''
520 interactions = [] 526 interactions = []
521 connection = sqlite3.connect(filename) 527 connection = sqlite3.connect(filename)
522 cursor = connection.cursor() 528 cursor = connection.cursor()
523 try: 529 try:
524 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') 530 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, IND.frame_number')
525 interactionNum = -1 531 interactionNum = -1
526 indicatorTypeNum = -1 532 indicatorTypeNum = -1
527 tmpIndicators = {} 533 tmpIndicators = {}
528 for row in cursor: 534 for row in cursor:
529 if row[0] != interactionNum: 535 if row[0] != interactionNum:
530 interactionNum = row[0] 536 interactionNum = row[0]
531 interactions.append(events.Interaction(interactionNum, moving.TimeInterval(row[3],row[4]), row[1], row[2])) 537 interactions.append(events.Interaction(interactionNum, moving.TimeInterval(row[3],row[4]), row[1], row[2]))
532 interactions[-1].indicators = {} 538 interactions[-1].indicators = {}
533 if indicatorTypeNum != row[5]: 539 if indicatorTypeNum != row[5] or row[0] != interactionNum:
534 indicatorName = events.Interaction.indicatorNames[indicatorTypeNum] 540 indicatorName = events.Interaction.indicatorNames[indicatorTypeNum]
535 indicatorValues = {row[6]:row[7]} 541 indicatorValues = {row[6]:row[7]}
536 interactions[-1].indicators[indicatorName] = indicators.SeverityIndicator(indicatorName, indicatorValues) 542 interactions[-1].indicators[indicatorName] = indicators.SeverityIndicator(indicatorName, indicatorValues)
537 indicatorTypeNum = row[5] 543 indicatorTypeNum = row[5]
538 else: 544 else:
539 indicatorValues[row[6]] = row[7] 545 indicatorValues[row[6]] = row[7]
546 interactions[-1].indicators[indicatorName].timeInterval.last = row[6]
540 except sqlite3.OperationalError as error: 547 except sqlite3.OperationalError as error:
541 printDBError(error) 548 printDBError(error)
542 return [] 549 return []
543 connection.close() 550 connection.close()
544 return interactions 551 return interactions