Mercurial > hg > nsaunier > traffic-intelligence

comparison python/storage.py @ 417:a2ff03a52b73

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
added basic logging capability for debugging
author Nicolas Saunier <nicolas.saunier@polymtl.ca>
date Thu, 26 Sep 2013 17:07:44 -0400
parents 6567fee37c16
children f6415f012640
comparison
equal deleted inserted replaced
416:8fdbc13dad8b 417:a2ff03a52b73
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, moving, events, indicators 5 import utils, moving, events, indicators
6 6
7 import sqlite3 7 import sqlite3, logging
8 8
9 __metaclass__ = type 9 __metaclass__ = type
10 10
11 11
12 ngsimUserTypes = {'twowheels':1, 12 ngsimUserTypes = {'twowheels':1,
103 cursor = connection.cursor() 103 cursor = connection.cursor()
104 104
105 try: 105 try:
106 if trajectoryType == 'feature': 106 if trajectoryType == 'feature':
107 trajectoryIdQuery = getTrajectoryIdQuery(objectNumbers, trajectoryType) 107 trajectoryIdQuery = getTrajectoryIdQuery(objectNumbers, trajectoryType)
108 cursor.execute('SELECT * from '+tableName+' '+trajectoryIdQuery+'order by trajectory_id, frame_number') 108 queryStatement = 'SELECT * from '+tableName+' '+trajectoryIdQuery+'order by trajectory_id, frame_number'
109 cursor.execute(queryStatement)
110 logging.debug(queryStatement)
109 elif trajectoryType == 'object': 111 elif trajectoryType == 'object':
110 objectIdQuery = getTrajectoryIdQuery(objectNumbers, trajectoryType) 112 objectIdQuery = getTrajectoryIdQuery(objectNumbers, trajectoryType)
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 OF.object_id, P.frame_number order by OF.object_id, P.frame_number') 113 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 '+objectIdQuery+'group by OF.object_id, P.frame_number order by OF.object_id, P.frame_number'
114 cursor.execute(queryStatement)
115 logging.debug(queryStatement)
112 else: 116 else:
113 print('no trajectory type was chosen') 117 print('no trajectory type was chosen')
114 except sqlite3.OperationalError as error: 118 except sqlite3.OperationalError as error:
115 utils.printDBError(error) 119 utils.printDBError(error)
116 return [] 120 return []
132 objects.append(obj) 136 objects.append(obj)
133 137
134 return objects 138 return objects
135 139
136 def loadTrajectoriesFromSqlite(filename, trajectoryType, objectNumbers = -1): 140 def loadTrajectoriesFromSqlite(filename, trajectoryType, objectNumbers = -1):
137 '''Loads nObjects or the indices in objectNumbers from the database 141 '''Loads nObjects or the indices in objectNumbers from the database'''
138 TODO: load feature numbers and not average feature trajectories
139 TODO: other ways of averaging trajectories (load all points, sorted by frame_number and leave the agregation to be done in python)
140 '''
141 connection = sqlite3.connect(filename) # add test if it open 142 connection = sqlite3.connect(filename) # add test if it open
142 143
143 objects = loadTrajectoriesFromTable(connection, 'positions', trajectoryType, objectNumbers) 144 objects = loadTrajectoriesFromTable(connection, 'positions', trajectoryType, objectNumbers)
144 objectVelocities = loadTrajectoriesFromTable(connection, 'velocities', trajectoryType, objectNumbers) 145 objectVelocities = loadTrajectoriesFromTable(connection, 'velocities', trajectoryType, objectNumbers)
145 146
154 if trajectoryType == 'object': 155 if trajectoryType == 'object':
155 cursor = connection.cursor() 156 cursor = connection.cursor()
156 try: 157 try:
157 # attribute feature numbers to objects 158 # attribute feature numbers to objects
158 objectIdQuery = getTrajectoryIdQuery(objectNumbers, trajectoryType) 159 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') # order is important to group all features per object 160 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
161 cursor.execute(queryStatement)
162 logging.debug(queryStatement)
160 163
161 featureNumbers = {} 164 featureNumbers = {}
162 for row in cursor: 165 for row in cursor:
163 objId = row[1] 166 objId = row[1]
164 if objId not in featureNumbers: 167 if objId not in featureNumbers: