Mercurial Hosting > traffic-intelligence
comparison python/storage.py @ 777:ef6dd60be2e1 dev
added function to save feature trajectories
author | Nicolas Saunier <nicolas.saunier@polymtl.ca> |
---|---|
date | Fri, 05 Feb 2016 17:19:13 -0500 |
parents | bf4a1790cfac |
children | bd684e57c431 |
comparison
equal
deleted
inserted
replaced
776:84420159c5f4 | 777:ef6dd60be2e1 |
---|---|
86 else: | 86 else: |
87 print("Argument minmax unknown: {}".format(minmax)) | 87 print("Argument minmax unknown: {}".format(minmax)) |
88 return cursor.fetchone()[0] | 88 return cursor.fetchone()[0] |
89 except sqlite3.OperationalError as error: | 89 except sqlite3.OperationalError as error: |
90 printDBError(error) | 90 printDBError(error) |
91 | |
92 # TODO: add test if database connection is open | |
93 # IO to sqlite | |
94 def writeTrajectoriesToSqlite(objects, outputFilename, trajectoryType, objectNumbers = -1): | |
95 """ | |
96 This function writers trajectories to a specified sqlite file | |
97 @param[in] objects -> a list of trajectories | |
98 @param[in] trajectoryType - | |
99 @param[out] outputFilename -> the .sqlite file containting the written objects | |
100 @param[in] objectNumber : number of objects loaded | |
101 """ | |
102 connection = sqlite3.connect(outputFilename) | |
103 cursor = connection.cursor() | |
104 | |
105 schema = "CREATE TABLE IF NOT EXISTS \"positions\"(trajectory_id INTEGER,frame_number INTEGER, x_coordinate REAL, y_coordinate REAL, PRIMARY KEY(trajectory_id, frame_number))" | |
106 cursor.execute(schema) | |
107 | |
108 trajectory_id = 0 | |
109 frame_number = 0 | |
110 if trajectoryType == 'feature': | |
111 if type(objectNumbers) == int and objectNumbers == -1: | |
112 for trajectory in objects: | |
113 trajectory_id += 1 | |
114 frame_number = 0 | |
115 for position in trajectory.getPositions(): | |
116 frame_number += 1 | |
117 query = "insert into positions (trajectory_id, frame_number, x_coordinate, y_coordinate) values (?,?,?,?)" | |
118 cursor.execute(query,(trajectory_id,frame_number,position.x,position.y)) | |
119 | |
120 connection.commit() | |
121 connection.close() | |
122 | |
123 | 91 |
124 def loadPrototypeMatchIndexesFromSqlite(filename): | 92 def loadPrototypeMatchIndexesFromSqlite(filename): |
125 """ | 93 """ |
126 This function loads the prototypes table in the database of name <filename>. | 94 This function loads the prototypes table in the database of name <filename>. |
127 It returns a list of tuples representing matching ids : [(prototype_id, matched_trajectory_id),...] | 95 It returns a list of tuples representing matching ids : [(prototype_id, matched_trajectory_id),...] |
183 queryStatement = 'SELECT object_id, frame_number, x_'+corner+', y_'+corner+' FROM '+tableName | 151 queryStatement = 'SELECT object_id, frame_number, x_'+corner+', y_'+corner+' FROM '+tableName |
184 if objectNumbers is not None: | 152 if objectNumbers is not None: |
185 queryStatement += ' WHERE object_id '+objectCriteria | 153 queryStatement += ' WHERE object_id '+objectCriteria |
186 queryStatement += ' ORDER BY object_id, frame_number' | 154 queryStatement += ' ORDER BY object_id, frame_number' |
187 else: | 155 else: |
188 print('no trajectory type was chosen') | 156 print('Unknown trajectory type {}'.format(trajectoryType)) |
189 if queryStatement is not None: | 157 if queryStatement is not None: |
190 cursor.execute(queryStatement) | 158 cursor.execute(queryStatement) |
191 logging.debug(queryStatement) | 159 logging.debug(queryStatement) |
192 except sqlite3.OperationalError as error: | 160 except sqlite3.OperationalError as error: |
193 printDBError(error) | 161 printDBError(error) |
225 for row in cursor: | 193 for row in cursor: |
226 userTypes[row[0]] = row[1] | 194 userTypes[row[0]] = row[1] |
227 return userTypes | 195 return userTypes |
228 | 196 |
229 def loadTrajectoriesFromSqlite(filename, trajectoryType, objectNumbers = None, withFeatures = False): | 197 def loadTrajectoriesFromSqlite(filename, trajectoryType, objectNumbers = None, withFeatures = False): |
230 '''Loads the first objectNumbers objects or the indices in objectNumbers from the database''' | 198 '''Loads the trajectories (in the general sense, |
199 either features, objects (feature groups) or bounding box series) | |
200 The number loaded is either the first objectNumbers objects, | |
201 or the indices in objectNumbers from the database''' | |
231 connection = sqlite3.connect(filename) | 202 connection = sqlite3.connect(filename) |
232 | 203 |
233 objects = loadTrajectoriesFromTable(connection, 'positions', trajectoryType, objectNumbers) | 204 objects = loadTrajectoriesFromTable(connection, 'positions', trajectoryType, objectNumbers) |
234 objectVelocities = loadTrajectoriesFromTable(connection, 'velocities', trajectoryType, objectNumbers) | 205 objectVelocities = loadTrajectoriesFromTable(connection, 'velocities', trajectoryType, objectNumbers) |
235 | 206 |
455 printDBError(error) | 426 printDBError(error) |
456 return boundingBoxes | 427 return boundingBoxes |
457 connection.close() | 428 connection.close() |
458 return boundingBoxes | 429 return boundingBoxes |
459 | 430 |
431 def saveTrajectoriesToSqlite(outputFilename, objects, trajectoryType, withFeatures = False): | |
432 '''Writes features, ie the trajectories positions (and velocities if exist) | |
433 with their instants to a specified sqlite file | |
434 | |
435 TODO: Not implemented for other trajectoryType than features | |
436 For objects, with features will control whether the features | |
437 corresponding to the object are also saved''' | |
438 | |
439 connection = sqlite3.connect(outputFilename) | |
440 try: | |
441 cursor = connection.cursor() | |
442 | |
443 if trajectoryType == 'feature': | |
444 cursor.execute("CREATE TABLE IF NOT EXISTS positions (trajectory_id INTEGER, frame_number INTEGER, x_coordinate REAL, y_coordinate REAL, PRIMARY KEY(trajectory_id, frame_number))") | |
445 cursor.execute("CREATE TABLE IF NOT EXISTS velocities (trajectory_id INTEGER, frame_number INTEGER, x_coordinate REAL, y_coordinate REAL, PRIMARY KEY(trajectory_id, frame_number))") | |
446 | |
447 positionQuery = "insert into positions (trajectory_id, frame_number, x_coordinate, y_coordinate) values (?,?,?,?)" | |
448 velocityQuery = "insert into velocities (trajectory_id, frame_number, x_coordinate, y_coordinate) values (?,?,?,?)" | |
449 for obj in objects: | |
450 num = obj.getNum() | |
451 frame_number = obj.getFirstInstant() | |
452 for position in obj.getPositions(): | |
453 cursor.execute(positionQuery, (num, frame_number, position.x, position.y)) | |
454 frame_number += 1 | |
455 if obj.getVelocities() is not None: | |
456 frame_number = obj.getFirstInstant() | |
457 velocities = obj.getVelocities() | |
458 for i in xrange(velocities.length()-1): | |
459 v = velocities[i] | |
460 cursor.execute(velocityQuery, (num, frame_number, v.x, v.y)) | |
461 frame_number += 1 | |
462 connection.commit() | |
463 #elif trajectoryType == 'feature': | |
464 else: | |
465 print('Unknown trajectory type {}'.format(trajectoryType)) | |
466 except sqlite3.OperationalError as error: | |
467 printDBError(error) | |
468 connection.close() | |
469 | |
460 ######################### | 470 ######################### |
461 # saving and loading for scene interpretation (Mohamed Gomaa Mohamed's PhD) | 471 # saving and loading for scene interpretation (Mohamed Gomaa Mohamed's PhD) |
462 ######################### | 472 ######################### |
463 | 473 |
464 def writeFeaturesToSqlite(objects, outputFilename, trajectoryType, objectNumbers = -1): | |
465 '''write features trajectories maintain trajectory ID,velocities dataset ''' | |
466 connection = sqlite3.connect(outputFilename) | |
467 cursor = connection.cursor() | |
468 | |
469 cursor.execute("CREATE TABLE IF NOT EXISTS \"positions\"(trajectory_id INTEGER,frame_number INTEGER, x_coordinate REAL, y_coordinate REAL, PRIMARY KEY(trajectory_id, frame_number))") | |
470 cursor.execute("CREATE TABLE IF NOT EXISTS \"velocities\"(trajectory_id INTEGER,frame_number INTEGER, x_coordinate REAL, y_coordinate REAL, PRIMARY KEY(trajectory_id, frame_number))") | |
471 | |
472 if trajectoryType == 'feature': | |
473 if type(objectNumbers) == int and objectNumbers == -1: | |
474 for trajectory in objects: | |
475 trajectory_id = trajectory.num | |
476 frame_number = trajectory.timeInterval.first | |
477 for position,velocity in zip(trajectory.getPositions(),trajectory.getVelocities()): | |
478 cursor.execute("insert into positions (trajectory_id, frame_number, x_coordinate, y_coordinate) values (?,?,?,?)",(trajectory_id,frame_number,position.x,position.y)) | |
479 cursor.execute("insert into velocities (trajectory_id, frame_number, x_coordinate, y_coordinate) values (?,?,?,?)",(trajectory_id,frame_number,velocity.x,velocity.y)) | |
480 frame_number += 1 | |
481 | |
482 connection.commit() | |
483 connection.close() | |
484 | |
485 def writePrototypesToSqlite(prototypes,nMatching, outputFilename): | 474 def writePrototypesToSqlite(prototypes,nMatching, outputFilename): |
486 """ prototype dataset is a dictionary with keys== routes, values== prototypes Ids """ | 475 """ prototype dataset is a dictionary with keys== routes, values== prototypes Ids """ |
487 connection = sqlite3.connect(outputFilename) | 476 connection = sqlite3.connect(outputFilename) |
488 cursor = connection.cursor() | 477 cursor = connection.cursor() |
489 | 478 |
490 cursor.execute("CREATE TABLE IF NOT EXISTS \"prototypes\"(prototype_id INTEGER,routeIDstart INTEGER,routeIDend INTEGER, nMatching INTEGER, PRIMARY KEY(prototype_id))") | 479 cursor.execute("CREATE TABLE IF NOT EXISTS prototypes (prototype_id INTEGER,routeIDstart INTEGER,routeIDend INTEGER, nMatching INTEGER, PRIMARY KEY(prototype_id))") |
491 | 480 |
492 for route in prototypes.keys(): | 481 for route in prototypes.keys(): |
493 if prototypes[route]!=[]: | 482 if prototypes[route]!=[]: |
494 for i in prototypes[route]: | 483 for i in prototypes[route]: |
495 cursor.execute("insert into prototypes (prototype_id, routeIDstart,routeIDend, nMatching) values (?,?,?,?)",(i,route[0],route[1],nMatching[route][i])) | 484 cursor.execute("insert into prototypes (prototype_id, routeIDstart,routeIDend, nMatching) values (?,?,?,?)",(i,route[0],route[1],nMatching[route][i])) |
528 """ labels is a dictionary with keys: routes, values: prototypes Ids | 517 """ labels is a dictionary with keys: routes, values: prototypes Ids |
529 """ | 518 """ |
530 connection = sqlite3.connect(outputFilename) | 519 connection = sqlite3.connect(outputFilename) |
531 cursor = connection.cursor() | 520 cursor = connection.cursor() |
532 | 521 |
533 cursor.execute("CREATE TABLE IF NOT EXISTS \"labels\"(object_id INTEGER,routeIDstart INTEGER,routeIDend INTEGER, prototype_id INTEGER, PRIMARY KEY(object_id))") | 522 cursor.execute("CREATE TABLE IF NOT EXISTS labels (object_id INTEGER,routeIDstart INTEGER,routeIDend INTEGER, prototype_id INTEGER, PRIMARY KEY(object_id))") |
534 | 523 |
535 for route in labels.keys(): | 524 for route in labels.keys(): |
536 if labels[route]!=[]: | 525 if labels[route]!=[]: |
537 for i in labels[route]: | 526 for i in labels[route]: |
538 for j in labels[route][i]: | 527 for j in labels[route][i]: |
568 def writeSpeedPrototypeToSqlite(prototypes,nmatching, outFilename): | 557 def writeSpeedPrototypeToSqlite(prototypes,nmatching, outFilename): |
569 """ to match the format of second layer prototypes""" | 558 """ to match the format of second layer prototypes""" |
570 connection = sqlite3.connect(outFilename) | 559 connection = sqlite3.connect(outFilename) |
571 cursor = connection.cursor() | 560 cursor = connection.cursor() |
572 | 561 |
573 cursor.execute("CREATE TABLE IF NOT EXISTS \"speedprototypes\"(spdprototype_id INTEGER,prototype_id INTEGER,routeID_start INTEGER, routeID_end INTEGER, nMatching INTEGER, PRIMARY KEY(spdprototype_id))") | 562 cursor.execute("CREATE TABLE IF NOT EXISTS speedprototypes (spdprototype_id INTEGER,prototype_id INTEGER,routeID_start INTEGER, routeID_end INTEGER, nMatching INTEGER, PRIMARY KEY(spdprototype_id))") |
574 | 563 |
575 for route in prototypes.keys(): | 564 for route in prototypes.keys(): |
576 if prototypes[route]!={}: | 565 if prototypes[route]!={}: |
577 for i in prototypes[route]: | 566 for i in prototypes[route]: |
578 if prototypes[route][i]!= []: | 567 if prototypes[route][i]!= []: |
613 def writeRoutesToSqlite(Routes, outputFilename): | 602 def writeRoutesToSqlite(Routes, outputFilename): |
614 """ This function writes the activity path define by start and end IDs""" | 603 """ This function writes the activity path define by start and end IDs""" |
615 connection = sqlite3.connect(outputFilename) | 604 connection = sqlite3.connect(outputFilename) |
616 cursor = connection.cursor() | 605 cursor = connection.cursor() |
617 | 606 |
618 cursor.execute("CREATE TABLE IF NOT EXISTS \"routes\"(object_id INTEGER,routeIDstart INTEGER,routeIDend INTEGER, PRIMARY KEY(object_id))") | 607 cursor.execute("CREATE TABLE IF NOT EXISTS routes (object_id INTEGER,routeIDstart INTEGER,routeIDend INTEGER, PRIMARY KEY(object_id))") |
619 | 608 |
620 for route in Routes.keys(): | 609 for route in Routes.keys(): |
621 if Routes[route]!=[]: | 610 if Routes[route]!=[]: |
622 for i in Routes[route]: | 611 for i in Routes[route]: |
623 cursor.execute("insert into routes (object_id, routeIDstart,routeIDend) values (?,?,?)",(i,route[0],route[1])) | 612 cursor.execute("insert into routes (object_id, routeIDstart,routeIDend) values (?,?,?)",(i,route[0],route[1])) |