Mercurial > hg > nsaunier > traffic-intelligence

comparison python/storage.py @ 390:12be4a0cb9aa

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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