Mercurial Hosting > traffic-intelligence

changeset 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 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 []