Mercurial Hosting > traffic-intelligence
comparison python/storage.py @ 390:12be4a0cb9aa
sql code to create bounding boxes in image space
author | Nicolas Saunier <nicolas.saunier@polymtl.ca> |
---|---|
date | Thu, 25 Jul 2013 18:55:44 -0400 |
parents | ba813f148ade |
children | eaf7765221d9 |
comparison
equal
deleted
inserted
replaced
389:6d26dcc7bba0 | 390:12be4a0cb9aa |
---|---|
193 connection = sqlite3.connect(filename) | 193 connection = sqlite3.connect(filename) |
194 if dataType == 'object': | 194 if dataType == 'object': |
195 utils.dropTables(connection, ['objects', 'objects_features']) | 195 utils.dropTables(connection, ['objects', 'objects_features']) |
196 elif dataType == 'interaction': | 196 elif dataType == 'interaction': |
197 utils.dropTables(connection, ['interactions', 'indicators']) | 197 utils.dropTables(connection, ['interactions', 'indicators']) |
198 elif dataType == 'bb': | |
199 utils.dropTables(connection, ['boundingbox']) | |
198 else: | 200 else: |
199 print('Unknown data type {} to delete from database'.format(dataType)) | 201 print('Unknown data type {} to delete from database'.format(dataType)) |
200 connection.close() | 202 connection.close() |
201 | 203 |
202 def createInteractionTable(cursor): | 204 def createInteractionTable(cursor): |
287 connection.close() | 289 connection.close() |
288 return interactions | 290 return interactions |
289 # load first and last object instants | 291 # load first and last object instants |
290 # 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 | 292 # 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 |
291 | 293 |
294 def createBoundingBoxTable(filename, invHomography = None): | |
295 '''Create the table to store the object bounding boxes | |
296 ''' | |
297 connection = sqlite3.connect(filename) | |
298 cursor = connection.cursor() | |
299 try: | |
300 cursor.execute('CREATE TABLE IF NOT EXISTS boundingbox (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))') | |
301 cursor.execute('INSERT INTO boundingbox SELECT object_id, frame_number, min(x), min(y), max(x), max(y) from ' | |
302 '(SELECT object_id, frame_number, (x*{}+y*{}+{})/w as x, (x*{}+y*{}+{})/w as y from ' | |
303 '(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])+ | |
304 'GROUP BY object_id, frame_number') | |
305 except sqlite3.OperationalError as error: | |
306 utils.printDBError(error) | |
307 connection.commit() | |
308 connection.close() | |
292 | 309 |
293 ######################### | 310 ######################### |
294 # txt files | 311 # txt files |
295 ######################### | 312 ######################### |
296 | 313 |