--- a/python/storage.py	Tue Oct 20 00:03:25 2015 -0400
+++ b/python/storage.py	Wed Oct 28 17:50:32 2015 -0400
@@ -2,7 +2,7 @@
 # -*- coding: utf-8 -*-
 '''Various utilities to save and load data'''
 
-import utils, moving, events, indicators
+import utils, moving, events, indicators, shutil
 from base import VideoFilenameAddable
 
 import sqlite3, logging
@@ -163,20 +163,17 @@
 
     try:
         objectCriteria = getObjectCriteria(objectNumbers)
+        queryStatement = None
         if trajectoryType == 'feature':
             queryStatement = 'SELECT * from '+tableName
             if objectNumbers is not None:
-                queryStatement += ' where trajectory_id '+objectCriteria
+                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'
+            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']:
             if trajectoryType == 'bbtop':
                 corner = 'top_left'
@@ -186,10 +183,11 @@
             if objectNumbers is not None:
                 queryStatement += ' WHERE object_id '+objectCriteria
             queryStatement += ' ORDER BY object_id, frame_number'
+        else:
+            print('no trajectory type was chosen')
+        if queryStatement is not None:
             cursor.execute(queryStatement)
             logging.debug(queryStatement)
-        else:
-            print('no trajectory type was chosen')
     except sqlite3.OperationalError as error:
         printDBError(error)
         return []
@@ -220,7 +218,7 @@
     objectCriteria = getObjectCriteria(objectNumbers)
     queryStatement = 'SELECT object_id, road_user_type from objects'
     if objectNumbers is not None:
-        queryStatement += ' where object_id '+objectCriteria
+        queryStatement += ' WHERE object_id '+objectCriteria
     cursor.execute(queryStatement)
     userTypes = {}
     for row in cursor:
@@ -247,7 +245,7 @@
         try:
             # attribute feature numbers to objects
             objectCriteria = getObjectCriteria(objectNumbers)
-            queryStatement = 'SELECT P.trajectory_id, OF.object_id from positions P, objects_features OF where P.trajectory_id = OF.trajectory_id'
+            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
@@ -395,7 +393,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, IND.frame_number')
+        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 = {}
@@ -418,7 +416,7 @@
     connection.close()
     return interactions
 # load first and last object instants
-# 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
+# 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
 
 def createBoundingBoxTable(filename, invHomography = None):
     '''Create the table to store the object bounding boxes in image space
@@ -429,7 +427,7 @@
         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))')
         cursor.execute('INSERT INTO bounding_boxes SELECT object_id, frame_number, min(x), min(y), max(x), max(y) from '
               '(SELECT object_id, frame_number, (x*{}+y*{}+{})/w as x, (x*{}+y*{}+{})/w as y from '
-              '(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])+
+              '(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])+
               'GROUP BY object_id, frame_number')
     except sqlite3.OperationalError as error:
         printDBError(error)
@@ -647,8 +645,8 @@
     connection = sqlite3.connect(filename)
     cursor = connection.cursor()
     for obj in objects:
-        cursor.execute('update objects set startRouteID = {} where object_id = {}'.format(obj.startRouteID, obj.getNum()))
-        cursor.execute('update objects set endRouteID = {} where object_id = {}'.format(obj.endRouteID, obj.getNum()))        
+        cursor.execute('update objects set startRouteID = {} WHERE object_id = {}'.format(obj.startRouteID, obj.getNum()))
+        cursor.execute('update objects set endRouteID = {} WHERE object_id = {}'.format(obj.endRouteID, obj.getNum()))        
     connection.commit()
     connection.close()
 
@@ -658,7 +656,7 @@
     connection = sqlite3.connect(filename)
     cursor = connection.cursor()
     for obj in objects:
-        cursor.execute('update objects set road_user_type = {} where object_id = {}'.format(obj.getUserType(), obj.getNum()))
+        cursor.execute('update objects set road_user_type = {} WHERE object_id = {}'.format(obj.getUserType(), obj.getNum()))
     connection.commit()
     connection.close()
 
@@ -733,7 +731,23 @@
 def generatePDLaneColumn(data):
     data['LANE'] = data['LANE\LINK\NO'].astype(str)+'_'+data['LANE\INDEX'].astype(str)
 
-def loadTrajectoriesFromVissimFile(filename, simulationStepsPerTimeUnit, nObjects = -1, warmUpLastInstant = None, usePandas = False, nDecimals = 2, lowMemory = True):
+def convertTrajectoriesVissimToSqlite(filename):
+    '''Relies on a system call to sqlite3
+    sqlite3 [file.sqlite] < import_fzp.sql'''
+    sqlScriptFilename = "import_fzp.sql"
+    # create sql file
+    out = openCheck(sqlScriptFilename, "w")
+    out.write(".separator \";\"\n"+
+              "CREATE TABLE IF NOT EXISTS curvilinear_positions (t REAL, trajectory_id INTEGER, link_id INTEGER, lane_id INTEGER, s_coordinate REAL, y_coordinate REAL, speed REAL, PRIMARY KEY (t, trajectory_id));\n"+
+              ".import "+filename+" curvilinear_positions\n"+
+              "DELETE FROM curvilinear_positions WHERE trajectory_id IS NULL OR trajectory_id = \"NO\";\n")
+    out.close()
+    # system call
+    from os import system, remove
+    system("sqlite3 "+utils.removeExtension(filename)+".sqlite < "+sqlScriptFilename)
+    remove(sqlScriptFilename)
+
+def loadTrajectoriesFromVissimFile(filename, simulationStepsPerTimeUnit, objectNumbers = None, warmUpLastInstant = None, usePandas = False, nDecimals = 2, lowMemory = True):
     '''Reads data from VISSIM .fzp trajectory file
     simulationStepsPerTimeUnit is the number of simulation steps per unit of time used by VISSIM
     for example, there seems to be 5 simulation steps per simulated second in VISSIM, 
@@ -761,32 +775,55 @@
             objects[objNum] = moving.MovingObject(num = objNum, timeInterval = moving.TimeInterval(row['first'], row['last']))
             # positions should be rounded to nDecimals decimals only
             objects[objNum].curvilinearPositions = moving.CurvilinearTrajectory(S = npround(tmp['POS'].tolist(), nDecimals), Y = npround(tmp['POSLAT'].tolist(), nDecimals), lanes = tmp['LANE'].tolist())
-            if nObjects > 0 and len(objects) >= nObjects:
+            if objectNumbers is not None and objectNumbers > 0 and len(objects) >= objectNumbers:
                 break
         return objects.values()
     else:
-        inputfile = openCheck(filename, quitting = True)
-        # data = pd.read_csv(filename, skiprows=15, delimiter=';')
-        # skip header: 15 lines + 1
-        line = readline(inputfile, '*$')
-        while len(line) > 0:#for line in inputfile:
-            data = line.strip().split(';')
-            objNum = int(data[1])
-            instant = int(float(data[0])*simulationStepsPerTimeUnit)
-            s = float(data[4])
-            y = float(data[5])
-            lane = data[2]+'_'+data[3]
-            if objNum not in objects:
-                if warmUpLastInstant is None or instant >= warmUpLastInstant:
-                    if nObjects < 0 or len(objects) < nObjects:
-                        objects[objNum] = moving.MovingObject(num = objNum, timeInterval = moving.TimeInterval(instant, instant))
-                        objects[objNum].curvilinearPositions = moving.CurvilinearTrajectory()
-            if (warmUpLastInstant is None or instant >= warmUpLastInstant) and objNum in objects:
-                objects[objNum].timeInterval.last = instant
-                objects[objNum].curvilinearPositions.addPositionSYL(s, y, lane)
+        if filename.endswith(".fzp"):
+            inputfile = openCheck(filename, quitting = True)
             line = readline(inputfile, '*$')
+            while len(line) > 0:#for line in inputfile:
+                data = line.strip().split(';')
+                objNum = int(data[1])
+                instant = float(data[0])*simulationStepsPerTimeUnit
+                s = float(data[4])
+                y = float(data[5])
+                lane = data[2]+'_'+data[3]
+                if objNum not in objects:
+                    if warmUpLastInstant is None or instant >= warmUpLastInstant:
+                        if objectNumbers is None or len(objects) < objectNumbers:
+                            objects[objNum] = moving.MovingObject(num = objNum, timeInterval = moving.TimeInterval(instant, instant))
+                            objects[objNum].curvilinearPositions = moving.CurvilinearTrajectory()
+                if (warmUpLastInstant is None or instant >= warmUpLastInstant) and objNum in objects:
+                    objects[objNum].timeInterval.last = instant
+                    objects[objNum].curvilinearPositions.addPositionSYL(s, y, lane)
+                line = readline(inputfile, '*$')
+            return objects.values()
+        elif filename.endswith(".sqlite"):
+            connection = sqlite3.connect(filename)
+            cursor = connection.cursor()
+            queryStatement = 'SELECT t, trajectory_id, link_id, lane_id, s_coordinate, y_coordinate FROM curvilinear_positions'
+            if objectNumbers is not None:
+                queryStatement += ' WHERE trajectory_id '+getObjectCriteria(objectNumbers)
+            queryStatement += ' ORDER BY trajectory_id, t'
+            #objects = loadTrajectoriesFromTable(connection, "curvilinear_positions", "vissim_curvilinear", objectNumbers)
+            for row in cursor:
+                objNum = row[1]
+                instant = row[0]*simulationStepsPerTimeUnit
+                s = row[4]
+                y = row[5]
+                lane = '{}_{}'.format(row[2], row[3])
+                if objNum not in objects:
+                    if warmUpLastInstant is None or instant >= warmUpLastInstant:
+                        if objectNumbers is None or len(objects) < objectNumbers:
+                            objects[objNum] = moving.MovingObject(num = objNum, timeInterval = moving.TimeInterval(instant, instant))
+                            objects[objNum].curvilinearPositions = moving.CurvilinearTrajectory()
+                if (warmUpLastInstant is None or instant >= warmUpLastInstant) and objNum in objects:
+                    objects[objNum].timeInterval.last = instant
+                    objects[objNum].curvilinearPositions.addPositionSYL(s, y, lane)
+        else:
+            print("File type of "+filename+" not supported (only .sqlite and .fzp files)")
 
-        return objects.values()
 
 def selectPDLanes(data, lanes = None):
     '''Selects the subset of data for the right lanes