Mercurial Hosting > traffic-intelligence
changeset 693:5ee22bf7e4d5 dev
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 | 9a258687af4c |
children | c4363aa6f0e5 |
files | python/indicators.py python/storage.py |
diffstat | 2 files changed, 41 insertions(+), 34 deletions(-) [+] |
line wrap: on
line diff
--- a/python/indicators.py Mon Jun 29 23:09:58 2015 -0400 +++ b/python/indicators.py Tue Jun 30 15:46:31 2015 -0400 @@ -19,21 +19,21 @@ it should have more information like name, unit''' - def __init__(self, name, values, timeInterval=None, maxValue = None): + def __init__(self, name, values, timeInterval = None, maxValue = None): self.name = name - if timeInterval: + if timeInterval is None: + self.values = values + instants = sorted(self.values.keys()) + if len(instants) > 0: + self.timeInterval = moving.TimeInterval(instants[0], instants[-1]) + else: + self.timeInterval = moving.TimeInterval() + else: assert len(values) == timeInterval.length() self.timeInterval = timeInterval self.values = {} for i in xrange(int(round(self.timeInterval.length()))): self.values[self.timeInterval[i]] = values[i] - else: - self.values = values - instants = sorted(self.values.keys()) - if instants: - self.timeInterval = moving.TimeInterval(instants[0], instants[-1]) - else: - self.timeInterval = moving.TimeInterval() self.maxValue = maxValue def __len__(self):
--- a/python/storage.py Mon Jun 29 23:09:58 2015 -0400 +++ b/python/storage.py Tue Jun 30 15:46:31 2015 -0400 @@ -290,22 +290,16 @@ connection.close() return matched_indexes -def getTrajectoryIdQuery(objectNumbers, trajectoryType): - if trajectoryType == 'feature': - statementBeginning = 'where trajectory_id ' - elif trajectoryType == 'object': - statementBeginning = 'and OF.object_id ' - elif trajectoryType == 'bbtop' or 'bbbottom': - statementBeginning = 'where object_id ' - else: - print('no trajectory type was chosen') - +def getObjectCriteria(objectNumbers): if objectNumbers is None: query = '' elif type(objectNumbers) == int: - query = statementBeginning+'between 0 and {0} '.format(objectNumbers-1) + query = 'between 0 and {0}'.format(objectNumbers-1) elif type(objectNumbers) == list: - query = statementBeginning+'in ('+', '.join([str(n) for n in objectNumbers])+') ' + query = 'in ('+', '.join([str(n) for n in objectNumbers])+')' + else: + print('objectNumbers {} are not a known type ({})'.format(objectNumbers, type(objectNumbers))) + query = '' return query def loadTrajectoriesFromTable(connection, tableName, trajectoryType, objectNumbers = None): @@ -316,13 +310,19 @@ cursor = connection.cursor() try: - idQuery = getTrajectoryIdQuery(objectNumbers, trajectoryType) + objectCriteria = getObjectCriteria(objectNumbers) if trajectoryType == 'feature': - queryStatement = 'SELECT * from '+tableName+' '+idQuery+'ORDER BY trajectory_id, frame_number' + queryStatement = 'SELECT * from '+tableName + if objectNumbers is not None: + queryStatement += ' where trajectory_id '+objectCriteria + queryStatement += ' ORDER BY trajectory_id, frame_number' cursor.execute(queryStatement) logging.debug(queryStatement) elif trajectoryType == 'object': - 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' + 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' + if objectNumbers is not None: + queryStatement += ' and OF.object_id '+objectCriteria + queryStatement += ' group by OF.object_id, P.frame_number ORDER BY OF.object_id, P.frame_number' cursor.execute(queryStatement) logging.debug(queryStatement) elif trajectoryType in ['bbtop', 'bbbottom']: @@ -330,7 +330,10 @@ corner = 'top_left' elif trajectoryType == 'bbbottom': corner = 'bottom_right' - queryStatement = 'SELECT object_id, frame_number, x_'+corner+', y_'+corner+' FROM '+tableName+' '+idQuery+'ORDER BY object_id, frame_number' + queryStatement = 'SELECT object_id, frame_number, x_'+corner+', y_'+corner+' FROM '+tableName + if objectNumbers is not None: + queryStatement += ' where object_id '+objectCriteria + queryStatement += ' ORDER BY object_id, frame_number' cursor.execute(queryStatement) logging.debug(queryStatement) else: @@ -362,11 +365,11 @@ return objects def loadUserTypesFromTable(cursor, trajectoryType, objectNumbers): - objectIdQuery = getTrajectoryIdQuery(objectNumbers, trajectoryType) - if objectIdQuery == '': - cursor.execute('SELECT object_id, road_user_type from objects') - else: - cursor.execute('SELECT object_id, road_user_type from objects where '+objectIdQuery[7:]) + objectCriteria = getObjectCriteria(objectNumbers) + queryStatement = 'SELECT object_id, road_user_type from objects' + if objectNumbers is not None: + queryStatement += ' where object_id '+objectCriteria + cursor.execute(queryStatement) userTypes = {} for row in cursor: userTypes[row[0]] = row[1] @@ -391,8 +394,11 @@ cursor = connection.cursor() try: # attribute feature numbers to objects - objectIdQuery = getTrajectoryIdQuery(objectNumbers, trajectoryType) - 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 + objectCriteria = getObjectCriteria(objectNumbers) + queryStatement = 'SELECT P.trajectory_id, OF.object_id from positions P, objects_features OF where P.trajectory_id = OF.trajectory_id' + if objectNumbers is not None: + queryStatement += ' and OF.object_id '+objectCriteria + queryStatement += ' group by P.trajectory_id order by OF.object_id' # order is important to group all features per object cursor.execute(queryStatement) logging.debug(queryStatement) @@ -521,7 +527,7 @@ connection = sqlite3.connect(filename) cursor = connection.cursor() try: - 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') + 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') interactionNum = -1 indicatorTypeNum = -1 tmpIndicators = {} @@ -530,13 +536,14 @@ interactionNum = row[0] interactions.append(events.Interaction(interactionNum, moving.TimeInterval(row[3],row[4]), row[1], row[2])) interactions[-1].indicators = {} - if indicatorTypeNum != row[5]: + if indicatorTypeNum != row[5] or row[0] != interactionNum: indicatorName = events.Interaction.indicatorNames[indicatorTypeNum] indicatorValues = {row[6]:row[7]} interactions[-1].indicators[indicatorName] = indicators.SeverityIndicator(indicatorName, indicatorValues) indicatorTypeNum = row[5] else: indicatorValues[row[6]] = row[7] + interactions[-1].indicators[indicatorName].timeInterval.last = row[6] except sqlite3.OperationalError as error: printDBError(error) return []