Mercurial Hosting > traffic-intelligence
comparison python/storage.py @ 417:a2ff03a52b73
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: |