Mercurial Hosting > traffic-intelligence
comparison python/storage.py @ 239:93c26e45efd8
modified functions to read velocities from sqlite database
author | Nicolas Saunier <nicolas.saunier@polymtl.ca> |
---|---|
date | Wed, 11 Jul 2012 16:30:23 -0400 |
parents | 584613399513 |
children | 583a2c4622f9 |
comparison
equal
deleted
inserted
replaced
238:be3761a09b20 | 239:93c26e45efd8 |
---|---|
64 matched_indexes.append((row[0],row[1])) | 64 matched_indexes.append((row[0],row[1])) |
65 | 65 |
66 connection.close() | 66 connection.close() |
67 return matched_indexes | 67 return matched_indexes |
68 | 68 |
69 def loadTrajectoriesFromSqlite(filename, trajectoryType, objectNumbers = -1): | 69 def loadTrajectoriesFromTable(connection, tableName, trajectoryType, objectNumbers = -1): |
70 '''Loads nObjects or the indices in objectNumbers from the database | 70 '''Loads trajectories (in the general sense) from the given table |
71 TODO: load velocities (replace table name 'positions' by 'velocities' | 71 can be positions or velocities |
72 TODO: load features as well, other ways of averaging trajectories | 72 |
73 ''' | 73 returns a moving object''' |
74 import sqlite3 | |
75 | |
76 connection = sqlite3.connect(filename) # add test if it open | |
77 cursor = connection.cursor() | 74 cursor = connection.cursor() |
78 | 75 |
79 try: | 76 try: |
80 if trajectoryType == 'feature': | 77 if trajectoryType == 'feature': |
81 if type(objectNumbers) == int: | 78 if type(objectNumbers) == int: |
82 if objectNumbers == -1: | 79 if objectNumbers == -1: |
83 cursor.execute('SELECT * from positions order by trajectory_id, frame_number') | 80 cursor.execute('SELECT * from '+tableName+' order by trajectory_id, frame_number') |
84 else: | 81 else: |
85 cursor.execute('SELECT * from positions where trajectory_id between 0 and {0} order by trajectory_id, frame_number'.format(objectNumbers)) | 82 cursor.execute('SELECT * from {0} where trajectory_id between 0 and {1} order by trajectory_id, frame_number'.format(tableName, objectNumbers)) |
86 elif type(objectNumbers) == list: | 83 elif type(objectNumbers) == list: |
87 cursor.execute('SELECT * from positions where trajectory_id in ('+', '.join([str(n) for n in objectNumbers])+') order by trajectory_id, frame_number') | 84 cursor.execute('SELECT * from '+tableName+' where trajectory_id in ('+', '.join([str(n) for n in objectNumbers])+') order by trajectory_id, frame_number') |
88 elif trajectoryType == 'object': | 85 elif trajectoryType == 'object': |
89 if type(objectNumbers) == int: | 86 if type(objectNumbers) == int: |
90 if objectNumbers == -1: | 87 if objectNumbers == -1: |
91 cursor.execute('SELECT OF.object_id, P.frame_number, avg(P.x_coordinate), avg(P.y_coordinate) from positions P, objects_features OF where P.trajectory_id = OF.trajectory_id group by object_id, frame_number') | 88 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 group by object_id, frame_number') |
92 else: | 89 else: |
93 cursor.execute('SELECT OF.object_id, P.frame_number, avg(P.x_coordinate), avg(P.y_coordinate) from positions P, objects_features OF where P.trajectory_id = OF.trajectory_id and OF.object_id between 0 and {0} group by object_id, frame_number'.format(objectNumbers)) | 90 cursor.execute('SELECT OF.object_id, P.frame_number, avg(P.x_coordinate), avg(P.y_coordinate) from {0} P, objects_features OF where P.trajectory_id = OF.trajectory_id and OF.object_id between 0 and {1} group by object_id, frame_number'.format(tableName, objectNumbers)) |
94 elif type(objectNumbers) == list: | 91 elif type(objectNumbers) == list: |
95 cursor.execute('SELECT OF.object_id, P.frame_number, avg(P.x_coordinate), avg(P.y_coordinate) from positions P, objects_features OF where P.trajectory_id = OF.trajectory_id and OF.object_id in ('+', '.join([str(n) for n in objectNumbers])+') group by object_id, frame_number') | 92 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.object_id in ('+', '.join([str(n) for n in objectNumbers])+') group by object_id, frame_number') |
96 else: | 93 else: |
97 print('no trajectory type was chosen') | 94 print('no trajectory type was chosen') |
98 except sqlite3.OperationalError as err: | 95 except sqlite3.OperationalError as err: |
99 print('DB Error: {0}'.format(err)) | 96 print('DB Error: {0}'.format(err)) |
100 return [] | 97 return [] |
101 | 98 |
102 objId = -1 | 99 objId = -1 |
103 obj = None | 100 obj = None |
104 objects = [] | 101 objects = [] |
105 for row in cursor: | 102 for row in cursor: |
106 if row[0] != objId: | 103 if row[0] != objId: |
112 obj.timeInterval.last = row[1] | 109 obj.timeInterval.last = row[1] |
113 obj.positions.addPositionXY(row[2],row[3]) | 110 obj.positions.addPositionXY(row[2],row[3]) |
114 | 111 |
115 if obj: | 112 if obj: |
116 objects.append(obj) | 113 objects.append(obj) |
114 | |
115 return objects | |
116 | |
117 def loadTrajectoriesFromSqlite(filename, trajectoryType, objectNumbers = -1): | |
118 '''Loads nObjects or the indices in objectNumbers from the database | |
119 TODO: load feature numbers and not average feature trajectories | |
120 TODO: other ways of averaging trajectories (load all points, sorted by frame_number and leave the agregation to be done in python) | |
121 ''' | |
122 import sqlite3 | |
123 | |
124 connection = sqlite3.connect(filename) # add test if it open | |
125 | |
126 objects = loadTrajectoriesFromTable(connection, 'positions', trajectoryType, objectNumbers) | |
127 objectVelocities = loadTrajectoriesFromTable(connection, 'velocities', trajectoryType, objectNumbers) | |
128 | |
129 if len(objectVelocities) > 0: | |
130 for o,v in zip(objects, objectVelocities): | |
131 if o.num == v.num: | |
132 o.velocities = v.positions | |
133 else: | |
134 print('Could not match positions {0} with velocities {1}'.format(o.num, v.num)) | |
117 | 135 |
118 connection.close() | 136 connection.close() |
119 return objects | 137 return objects |
120 | 138 |
121 def removeObjectsFromSqlite(filename): | 139 def removeObjectsFromSqlite(filename): |