Mercurial Hosting > traffic-intelligence
diff python/storage.py @ 927:c030f735c594
added assignment of trajectories to prototypes and cleanup of insert queries
author | Nicolas Saunier <nicolas.saunier@polymtl.ca> |
---|---|
date | Tue, 11 Jul 2017 17:56:23 -0400 |
parents | acb5379c5fd7 |
children | 0e63a918a1ca |
line wrap: on
line diff
--- a/python/storage.py Mon Jul 10 18:04:41 2017 -0400 +++ b/python/storage.py Tue Jul 11 17:56:23 2017 -0400 @@ -54,7 +54,7 @@ elif dataType == 'pois': dropTables(connection, ['gaussians2d', 'objects_pois']) elif dataType == 'prototype': - dropTables(connection, ['prototypes']) + dropTables(connection, ['prototypes', 'objects_prototypes']) else: print('Unknown data type {} to delete from database'.format(dataType)) connection.close() @@ -100,13 +100,13 @@ cursor.execute('CREATE TABLE IF NOT EXISTS indicators (interaction_id INTEGER, indicator_type INTEGER, frame_number INTEGER, value REAL, FOREIGN KEY(interaction_id) REFERENCES interactions(id), PRIMARY KEY(interaction_id, indicator_type, frame_number))') def insertTrajectoryQuery(tableName): - return "INSERT INTO "+tableName+" (trajectory_id, frame_number, x_coordinate, y_coordinate) VALUES (?,?,?,?)" + return "INSERT INTO "+tableName+" VALUES (?,?,?,?)" def insertObjectQuery(): - return "INSERT INTO objects (object_id, road_user_type, n_objects) VALUES (?,?,?)" + return "INSERT INTO objects VALUES (?,?,?)" def insertObjectFeatureQuery(): - return "INSERT INTO objects_features (object_id, trajectory_id) VALUES (?,?)" + return "INSERT INTO objects_features VALUES (?,?)" def createIndex(connection, tableName, columnName, unique = False): '''Creates an index for the column in the table @@ -152,28 +152,6 @@ except sqlite3.OperationalError as error: printDBError(error) -def loadPrototypeMatchIndexesFromSqlite(filename): - """ - This function loads the prototypes table in the database of name <filename>. - It returns a list of tuples representing matching ids : [(prototype_id, matched_trajectory_id),...] - """ - matched_indexes = [] - - connection = sqlite3.connect(filename) - cursor = connection.cursor() - - try: - cursor.execute('SELECT * from prototypes order by prototype_id, trajectory_id_matched') - except sqlite3.OperationalError as error: - printDBError(error) - return [] - - for row in cursor: - matched_indexes.append((row[0],row[1])) - - connection.close() - return matched_indexes - def getObjectCriteria(objectNumbers): if objectNumbers is None: query = '' @@ -432,7 +410,7 @@ # Parse curvilinear position structure elif(trajectoryType == 'curvilinear'): createCurvilinearTrajectoryTable(cursor) - curvilinearQuery = "insert into curvilinear_positions (trajectory_id, frame_number, s_coordinate, y_coordinate, lane) values (?,?,?,?,?)" + curvilinearQuery = "INSERT INTO curvilinear_positions VALUES (?,?,?,?,?)" for obj in objects: num = obj.getNum() frameNum = obj.getFirstInstant() @@ -485,7 +463,7 @@ roadUserNumbers = list(interaction.getRoadUserNumbers()) cursor.execute('INSERT INTO interactions VALUES({}, {}, {}, {}, {})'.format(interaction.getNum(), roadUserNumbers[0], roadUserNumbers[1], interaction.getFirstInstant(), interaction.getLastInstant())) -def saveInteractions(filename, interactions): +def saveInteractionsToSqlite(filename, interactions): 'Saves the interactions in the table' connection = sqlite3.connect(filename) cursor = connection.cursor() @@ -503,7 +481,7 @@ if indicator[instant]: cursor.execute('INSERT INTO indicators VALUES({}, {}, {}, {})'.format(interactionNum, events.Interaction.indicatorNameToIndices[indicator.getName()], instant, indicator[instant])) -def saveIndicators(filename, interactions, indicatorNames = events.Interaction.indicatorNames): +def saveIndicatorsToSqlite(filename, interactions, indicatorNames = events.Interaction.indicatorNames): 'Saves the indicator values in the table' connection = sqlite3.connect(filename) cursor = connection.cursor() @@ -521,7 +499,7 @@ connection.commit() connection.close() -def loadInteractions(filename): +def loadInteractionsFromSqlite(filename): '''Loads interaction and their indicators TODO choose the interactions to load''' @@ -529,7 +507,7 @@ connection = sqlite3.connect(filename) cursor = connection.cursor() try: - cursor.execute('select INT.id, INT.object_id1, INT.object_id2, INT.first_frame_number, INT.last_frame_number, IND.indicator_type, IND.frame_number, IND.value from interactions INT, indicators IND WHERE INT.id = IND.interaction_id ORDER BY INT.id, IND.indicator_type, IND.frame_number') + cursor.execute('SELECT INT.id, INT.object_id1, INT.object_id2, INT.first_frame_number, INT.last_frame_number, IND.indicator_type, IND.frame_number, IND.value from interactions INT, indicators IND WHERE INT.id = IND.interaction_id ORDER BY INT.id, IND.indicator_type, IND.frame_number') interactionNum = -1 indicatorTypeNum = -1 tmpIndicators = {} @@ -597,16 +575,26 @@ connection = sqlite3.connect(filename) cursor = connection.cursor() try: - cursor.execute('CREATE TABLE IF NOT EXISTS prototypes (filename VARCHAR, id INTEGER, trajectory_type VARCHAR CHECK (trajectory_type IN (\"feature\", \"object\")), nmatchings INTEGER, PRIMARY KEY (filename, id))') + cursor.execute('CREATE TABLE IF NOT EXISTS prototypes (prototype_filename VARCHAR, prototype_id INTEGER, trajectory_type VARCHAR CHECK (trajectory_type IN (\"feature\", \"object\")), nmatchings INTEGER, PRIMARY KEY (prototype_filename, prototype_id, trajectory_type))') for p in prototypes: - cursor.execute('INSERT INTO prototypes (filename, id, trajectory_type, nmatchings) VALUES (?,?,?,?)', (p.getFilename(), p.getNum(), p.getTrajectoryType(), p.getNMatchings())) + cursor.execute('INSERT INTO prototypes VALUES(?,?,?,?)', (p.getFilename(), p.getNum(), p.getTrajectoryType(), p.getNMatchings())) except sqlite3.OperationalError as error: printDBError(error) connection.commit() connection.close() -def savePrototypeAssignments(filename, objects): - pass +def savePrototypeAssignmentsToSqlite(filename, objects, labels, prototypes): + connection = sqlite3.connect(filename) + cursor = connection.cursor() + try: + cursor.execute('CREATE TABLE IF NOT EXISTS objects_prototypes (object_id INTEGER, prototype_filename VARCHAR, prototype_id INTEGER, trajectory_type VARCHAR CHECK (trajectory_type IN (\"feature\", \"object\")), PRIMARY KEY(object_id, prototype_filename, prototype_id, trajectory_type))') + for obj, label in zip(objects, labels): + proto = prototypes[label] + cursor.execute('INSERT INTO objects_prototypes VALUES(?,?,?,?)', (obj.getNum(), proto.getFilename(), proto.getNum(), proto.getTrajectoryType())) + except sqlite3.OperationalError as error: + printDBError(error) + connection.commit() + connection.close() def loadPrototypesFromSqlite(filename, withTrajectories = True): 'Loads prototype ids and matchings (if stored)' @@ -638,7 +626,7 @@ print('Different types of prototypes in database ({}).'.format(set([p.getTrajectoryType() for p in prototypes]))) return prototypes -def savePOIs(filename, gmm, gmmType, gmmId): +def savePOIsToSqlite(filename, gmm, gmmType, gmmId): '''Saves a Gaussian mixture model (of class sklearn.mixture.GaussianMixture) gmmType is a type of GaussianMixture, learnt either from beginnings or ends of trajectories''' connection = sqlite3.connect(filename) @@ -650,26 +638,26 @@ try: cursor.execute('CREATE TABLE IF NOT EXISTS gaussians2d (poi_id INTEGER, id INTEGER, type VARCHAR, x_center REAL, y_center REAL, covariance VARCHAR, covariance_type VARCHAR, weight, precisions_cholesky VARCHAR, PRIMARY KEY(poi_id, id))') for i in xrange(gmm.n_components): - cursor.execute('INSERT INTO gaussians2d VALUES({}, {}, \'{}\', {}, {}, \'{}\', \'{}\', {}, \'{}\')'.format(gmmId, i, gmmType, gmm.means_[i][0], gmm.means_[i][1], str(gmm.covariances_[i].tolist()), gmm.covariance_type, gmm.weights_[i], str(gmm.precisions_cholesky_[i].tolist()))) + cursor.execute('INSERT INTO gaussians2d VALUES(?,?,?,?,?,?,?,?,?)', (gmmId, i, gmmType, gmm.means_[i][0], gmm.means_[i][1], str(gmm.covariances_[i].tolist()), gmm.covariance_type, gmm.weights_[i], str(gmm.precisions_cholesky_[i].tolist()))) connection.commit() except sqlite3.OperationalError as error: printDBError(error) connection.close() -def savePOIAssignments(filename, objects): +def savePOIAssignmentsToSqlite(filename, objects): 'save the od fields of objects' connection = sqlite3.connect(filename) cursor = connection.cursor() try: cursor.execute('CREATE TABLE IF NOT EXISTS objects_pois (object_id INTEGER, origin_poi_id INTEGER, destination_poi_id INTEGER, PRIMARY KEY(object_id))') for o in objects: - cursor.execute('INSERT INTO objects_pois VALUES({},{},{})'.format(o.getNum(), o.od[0], o.od[1])) + cursor.execute('INSERT INTO objects_pois VALUES(?,?,?)', (o.getNum(), o.od[0], o.od[1])) connection.commit() except sqlite3.OperationalError as error: printDBError(error) connection.close() -def loadPOIs(filename): +def loadPOIsFromSqlite(filename): 'Loads all 2D Gaussians in the database' from sklearn import mixture # todo if not avalaible, load data in duck-typed class with same fields from ast import literal_eval