Mercurial Hosting > traffic-intelligence
comparison python/storage.py @ 330:00800ebae698
corrected bug in db loading
author | Nicolas Saunier <nicolas.saunier@polymtl.ca> |
---|---|
date | Thu, 13 Jun 2013 23:05:28 -0400 |
parents | a70c205ebdd9 |
children | 3950bfe22768 |
comparison
equal
deleted
inserted
replaced
329:a70c205ebdd9 | 330:00800ebae698 |
---|---|
46 | 46 |
47 connection.commit() | 47 connection.commit() |
48 connection.close() | 48 connection.close() |
49 | 49 |
50 def setRoadUserTypes(filename, objects): | 50 def setRoadUserTypes(filename, objects): |
51 '''Saves the user types of the objects in the sqlite database stored in filename | |
52 The objects should exist in the objects table''' | |
51 import sqlite3 | 53 import sqlite3 |
52 connection = sqlite3.connect(filename) | 54 connection = sqlite3.connect(filename) |
53 cursor = connection.cursor() | 55 cursor = connection.cursor() |
54 for obj in objects: | 56 for obj in objects: |
55 cursor.execute('update objects set road_user_type = {} where object_id = {}'.format(obj.getUserType(), obj.getNum())) | 57 cursor.execute('update objects set road_user_type = {} where object_id = {}'.format(obj.getUserType(), obj.getNum())) |
79 connection.close() | 81 connection.close() |
80 return matched_indexes | 82 return matched_indexes |
81 | 83 |
82 def getTrajectoryIdQuery(objectNumbers, trajectoryType): | 84 def getTrajectoryIdQuery(objectNumbers, trajectoryType): |
83 if trajectoryType == 'feature': | 85 if trajectoryType == 'feature': |
84 statementBeginning = 'trajectory_id' | 86 statementBeginning = 'where trajectory_id ' |
85 elif trajectoryType == 'object': | 87 elif trajectoryType == 'object': |
86 statementBeginning = 'object_id' | 88 statementBeginning = 'and OF.object_id ' |
87 else: | 89 else: |
88 print('no trajectory type was chosen') | 90 print('no trajectory type was chosen') |
89 | 91 |
90 if type(objectNumbers) == int: | 92 if type(objectNumbers) == int: |
91 if objectNumbers == -1: | 93 if objectNumbers == -1: |
92 query = '' | 94 query = '' |
93 else: | 95 else: |
94 query = statementBeginning+' between 0 and {0}'.format(objectNumbers) | 96 query = statementBeginning+'between 0 and {0} '.format(objectNumbers) |
95 elif type(objectNumbers) == list: | 97 elif type(objectNumbers) == list: |
96 query = statementBeginning+' in ('+', '.join([str(n) for n in objectNumbers])+')' | 98 query = statementBeginning+'in ('+', '.join([str(n) for n in objectNumbers])+') ' |
97 return query | 99 return query |
98 | 100 |
99 def loadTrajectoriesFromTable(connection, tableName, trajectoryType, objectNumbers = -1): | 101 def loadTrajectoriesFromTable(connection, tableName, trajectoryType, objectNumbers = -1): |
100 '''Loads trajectories (in the general sense) from the given table | 102 '''Loads trajectories (in the general sense) from the given table |
101 can be positions or velocities | 103 can be positions or velocities |
106 cursor = connection.cursor() | 108 cursor = connection.cursor() |
107 | 109 |
108 try: | 110 try: |
109 if trajectoryType == 'feature': | 111 if trajectoryType == 'feature': |
110 trajectoryIdQuery = getTrajectoryIdQuery(objectNumbers, trajectoryType) | 112 trajectoryIdQuery = getTrajectoryIdQuery(objectNumbers, trajectoryType) |
111 cursor.execute('SELECT * from '+tableName+' where '+trajectoryIdQuery+' order by trajectory_id, frame_number') | 113 cursor.execute('SELECT * from '+tableName+' '+trajectoryIdQuery+'order by trajectory_id, frame_number') |
112 elif trajectoryType == 'object': | 114 elif trajectoryType == 'object': |
113 objectIdQuery = getTrajectoryIdQuery(objectNumbers, trajectoryType) | 115 objectIdQuery = getTrajectoryIdQuery(objectNumbers, trajectoryType) |
114 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 and OF.'+objectIdQuery+' group by OF.object_id, P.frame_number order by OF.object_id, P.frame_number') | 116 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') |
115 else: | 117 else: |
116 print('no trajectory type was chosen') | 118 print('no trajectory type was chosen') |
117 except sqlite3.OperationalError as err: | 119 except sqlite3.OperationalError as err: |
118 print('DB Error: {0}'.format(err)) | 120 print('DB Error: {0}'.format(err)) |
119 return [] | 121 return [] |
158 if trajectoryType == 'object': | 160 if trajectoryType == 'object': |
159 cursor = connection.cursor() | 161 cursor = connection.cursor() |
160 try: | 162 try: |
161 # attribute feature numbers to objects | 163 # attribute feature numbers to objects |
162 objectIdQuery = getTrajectoryIdQuery(objectNumbers, trajectoryType) | 164 objectIdQuery = getTrajectoryIdQuery(objectNumbers, trajectoryType) |
163 cursor.execute('SELECT P.trajectory_id, OF.object_id from positions P, objects_features OF where P.trajectory_id = OF.trajectory_id and OF.'+objectIdQuery+' group by P.trajectory_id order by OF.object_id') # order is important to group all features per object | 165 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 |
164 | 166 |
165 featureNumbers = {} | 167 featureNumbers = {} |
166 for row in cursor: | 168 for row in cursor: |
167 objId = row[1] | 169 objId = row[1] |
168 if objId not in featureNumbers: | 170 if objId not in featureNumbers: |
172 | 174 |
173 for obj in objects: | 175 for obj in objects: |
174 obj.featureNumbers = featureNumbers[obj.getNum()] | 176 obj.featureNumbers = featureNumbers[obj.getNum()] |
175 | 177 |
176 # load userType | 178 # load userType |
177 cursor.execute('SELECT object_id, road_user_type from objects where '+objectIdQuery) | 179 if objectIdQuery == '': |
180 cursor.execute('SELECT object_id, road_user_type from objects') | |
181 else: | |
182 cursor.execute('SELECT object_id, road_user_type from objects where '+objectIdQuery[7:]) | |
178 userTypes = {} | 183 userTypes = {} |
179 for row in cursor: | 184 for row in cursor: |
180 userTypes[row[0]] = row[1] | 185 userTypes[row[0]] = row[1] |
181 | 186 |
182 for obj in objects: | 187 for obj in objects: |