Mercurial Hosting > traffic-intelligence
changeset 830:2a5856961933
first working version of feature merging (works with feature grouping)
author | Nicolas Saunier <nicolas.saunier@polymtl.ca> |
---|---|
date | Wed, 29 Jun 2016 17:56:19 -0400 |
parents | 0ddcc41663f5 |
children | a8ff35e6fb43 |
files | python/metadata.py python/storage.py scripts/merge-features.py scripts/play-synced-videos.py |
diffstat | 4 files changed, 106 insertions(+), 35 deletions(-) [+] |
line wrap: on
line diff
--- a/python/metadata.py Wed Jun 29 13:50:21 2016 -0400 +++ b/python/metadata.py Wed Jun 29 17:56:19 2016 -0400 @@ -232,13 +232,11 @@ startTime = Column(DateTime) duration = Column(Interval) # video sequence duration databaseFilename = Column(String) # path relative to the the site name - siteIdx = Column(Integer, ForeignKey('sites.idx')) cameraViewIdx = Column(Integer, ForeignKey('camera_views.idx')) - site = relationship("Site", backref=backref('video_sequences', order_by = idx)) cameraView = relationship("CameraView", backref=backref('video_sequences', order_by = idx)) - def __init__(self, name, startTime, duration, site, cameraView, databaseFilename = None): + def __init__(self, name, startTime, duration, cameraView, databaseFilename = None): '''startTime is passed as string in utils.datetimeFormat, eg 2011-06-22 10:00:39 duration is a timedelta object''' self.name = name @@ -247,17 +245,22 @@ else: self.startTime = startTime self.duration = duration - self.site = site self.cameraView = cameraView if databaseFilename is None and len(self.name) > 0: self.databaseFilename = removeExtension(self.name)+'.sqlite' def getVideoSequenceFilename(self, relativeToSiteFilename = True): if relativeToSiteFilename: - return self.site.getFilename()+path.sep+self.name + return self.cameraView.site.getFilename()+path.sep+self.name else: return self.name + def getDatabaseFilename(self, relativeToSiteFilename = True): + if relativeToSiteFilename: + return self.cameraView.site.getFilename()+path.sep+self.databaseFilename + else: + return self.databaseFilename + def getTimeInterval(self): return TimeInterval(self.startTime, self.startTime+self.duration)
--- a/python/storage.py Wed Jun 29 13:50:21 2016 -0400 +++ b/python/storage.py Wed Jun 29 17:56:19 2016 -0400 @@ -46,6 +46,27 @@ except sqlite3.OperationalError as error: printDBError(error) +def createTrajectoryTable(cursor, tableName): + if tableName in ['positions', 'velocities']: + cursor.execute("CREATE TABLE IF NOT EXISTS "+tableName+" (trajectory_id INTEGER, frame_number INTEGER, x_coordinate REAL, y_coordinate REAL, PRIMARY KEY(trajectory_id, frame_number))") + else: + print('Unallowed name {} for trajectory table'.format(tableName)) + +def createCurvilinearTrajectoryTable(cursor): + cursor.execute("CREATE TABLE IF NOT EXISTS curvilinear_positions (trajectory_id INTEGER, frame_number INTEGER, s_coordinate REAL, y_coordinate REAL, lane TEXT, PRIMARY KEY(trajectory_id, frame_number))") + +def createFeatureCorrespondenceTable(cursor): + cursor.execute('CREATE TABLE IF NOT EXISTS feature_correspondences (trajectory_id INTEGER, source_dbname VARCHAR, db_trajectory_id INTEGER, PRIMARY KEY(trajectory_id))') + +def createInteractionTable(cursor): + cursor.execute('CREATE TABLE IF NOT EXISTS interactions (id INTEGER PRIMARY KEY, object_id1 INTEGER, object_id2 INTEGER, first_frame_number INTEGER, last_frame_number INTEGER, FOREIGN KEY(object_id1) REFERENCES objects(id), FOREIGN KEY(object_id2) REFERENCES objects(id))') + +def createIndicatorTable(cursor): + 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 (?,?,?,?)" + def createIndex(connection, tableName, columnName, unique = False): '''Creates an index for the column in the table I will make querying with a condition on this column faster''' @@ -297,11 +318,11 @@ cursor = connection.cursor() if trajectoryType == 'feature': - 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))") - 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))") + createTrajectoryTable(cursor, "positions") + createTrajectoryTable(cursor, "velocities") - positionQuery = "insert into positions (trajectory_id, frame_number, x_coordinate, y_coordinate) values (?,?,?,?)" - velocityQuery = "insert into velocities (trajectory_id, frame_number, x_coordinate, y_coordinate) values (?,?,?,?)" + positionQuery = insertTrajectoryQuery("positions") + velocityQuery = insertTrajectoryQuery("velocities") for obj in objects: num = obj.getNum() frame_number = obj.getFirstInstant() @@ -317,7 +338,7 @@ cursor.execute(velocityQuery, (num, frame_number, v.x, v.y)) frame_number += 1 elif trajectoryType == 'curvilinear': - cursor.execute("CREATE TABLE IF NOT EXISTS curvilinear_positions (trajectory_id INTEGER, frame_number INTEGER, s_coordinate REAL, y_coordinate REAL, lane TEXT, PRIMARY KEY(trajectory_id, frame_number))") + createCurvilinearTrajectoryTable(cursor) curvilinearQuery = "insert into curvilinear_positions (trajectory_id, frame_number, s_coordinate, y_coordinate, lane) values (?,?,?,?,?)" for obj in objects: num = obj.getNum() @@ -393,14 +414,6 @@ else: print('{} does not exist'.format(filename)) -def createInteractionTable(cursor): - cursor.execute('CREATE TABLE IF NOT EXISTS interactions (id INTEGER PRIMARY KEY, object_id1 INTEGER, object_id2 INTEGER, first_frame_number INTEGER, last_frame_number INTEGER, FOREIGN KEY(object_id1) REFERENCES objects(id), FOREIGN KEY(object_id2) REFERENCES objects(id))') - -def createIndicatorTables(cursor): - # cursor.execute('CREATE TABLE IF NOT EXISTS indicators (id INTEGER PRIMARY KEY, interaction_id INTEGER, indicator_type INTEGER, FOREIGN KEY(interaction_id) REFERENCES interactions(id))') - # cursor.execute('CREATE TABLE IF NOT EXISTS indicator_values (indicator_id INTEGER, frame_number INTEGER, value REAL, FOREIGN KEY(indicator_id) REFERENCES indicators(id), PRIMARY KEY(indicator_id, frame_number))') - 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 saveInteraction(cursor, interaction): roadUserNumbers = list(interaction.getRoadUserNumbers()) cursor.execute('INSERT INTO interactions VALUES({}, {}, {}, {}, {})'.format(interaction.getNum(), roadUserNumbers[0], roadUserNumbers[1], interaction.getFirstInstant(), interaction.getLastInstant())) @@ -429,7 +442,7 @@ cursor = connection.cursor() try: createInteractionTable(cursor) - createIndicatorTables(cursor) + createIndicatorTable(cursor) for inter in interactions: saveInteraction(cursor, inter) for indicatorName in indicatorNames:
--- a/scripts/merge-features.py Wed Jun 29 13:50:21 2016 -0400 +++ b/scripts/merge-features.py Wed Jun 29 17:56:19 2016 -0400 @@ -1,15 +1,16 @@ #! /usr/bin/env python -import sys, argparse, os.path -import cvutils, utils, moving -from metadata import createDatabase, Site, VideoSequence +import sys, argparse, os.path, sqlite3 +import cvutils, utils, moving, storage +from metadata import createDatabase, Site, VideoSequence, CameraView from datetime import datetime, timedelta parser = argparse.ArgumentParser(description='The program merges feature trajectories recorded from the same site synchronously between start and end time.') parser.add_argument('-i', dest = 'metadataFilename', help = 'name of the metadata file', required = True) parser.add_argument('-n', dest = 'siteId', help = 'site id or site name', required = True) -parser.add_argument('--t1', dest = 'startTime', help = 'time to start merging features (format %Y-%m-%d %H:%M:%S, eg 2011-06-22 10:00:39)') # if not provided, take common time interval -parser.add_argument('--t2', dest = 'endTime', help = 'time to stop merging features (format %Y-%m-%d %H:%M:%S, eg 2011-06-22 10:00:39)') +parser.add_argument('--start', dest = 'startTime', help = 'time to start merging features (format %Y-%m-%d %H:%M:%S, eg 2011-06-22 10:00:39)') # if not provided, take common time interval +parser.add_argument('--end', dest = 'endTime', help = 'time to stop merging features (format %Y-%m-%d %H:%M:%S, eg 2011-06-22 10:00:39)') +parser.add_argument('-o', dest = 'outputDBFilename', help = 'name of the output SQLite file', required = True) args = parser.parse_args() @@ -25,30 +26,84 @@ startTime = datetime.strptime(args.startTime, utils.datetimeFormat) endTime = datetime.strptime(args.endTime, utils.datetimeFormat) processInterval = moving.TimeInterval(startTime, endTime) -videoSequences = session.query(VideoSequence).filter(VideoSequence.site == site).order_by(VideoSequence.startTime.asc()).all() #.order_by(VideoSequence.cameraViewIdx) .filter(VideoSequence.startTime <= startTime) +cameraViews = session.query(CameraView).filter(CameraView.site == site) +videoSequences = session.query(VideoSequence).order_by(VideoSequence.startTime.asc()).all() #.order_by(VideoSequence.cameraViewIdx) .filter(VideoSequence.startTime <= startTime) +videoSequences = [vs for vs in videoSequences if vs.cameraView in cameraViews] #timeIntervals = [v.intersection(startTime, endTime) for v in videoSequences] - -cameraViews = set([v.cameraView for v in videoSequences]) +#cameraViews = set([v.cameraView for v in videoSequences]) videoSequences = {cv: [v for v in videoSequences if v.cameraView == cv] for cv in cameraViews} timeIntervals = {} for cv in videoSequences: timeIntervals[cv] = moving.TimeInterval.unionIntervals([v.getTimeInterval() for v in videoSequences[cv]]) +# intersection of the time interval (union) for each camera view commonTimeInterval = timeIntervals.values()[0] for inter in timeIntervals.values()[1:]: commonTimeInterval = moving.TimeInterval.intersection(commonTimeInterval, inter) commonTimeInterval = moving.TimeInterval.intersection(commonTimeInterval, processInterval) +if commonTimeInterval.empty(): + print('Empty time interval. Exiting') + sys.exit() + if len(set([cv.cameraType.frameRate for cv in cameraViews])) > 1: print('Different framerates of the cameras ({}) are not handled yet. Exiting'.format([cv.cameraType.frameRate for cv in cameraViews])) +else: + frameRate = cv.cameraType.frameRate -for cv, v in videoSequences.iteritems(): - +try: + outConnection = sqlite3.connect(args.outputDBFilename) + outCursor = outConnection.cursor() + storage.createTrajectoryTable(outCursor, 'positions') + storage.createTrajectoryTable(outCursor, 'velocities') + storage.createFeatureCorrespondenceTable(outCursor) + outConnection.commit() +except sqlite3.OperationalError as error: + storage.printDBError(error) + sys.exit() + +dirname = os.path.split(args.metadataFilename)[0] +if len(dirname) == 0: + dirname = '.' -# for all camera view, for all video, select from positions and velocities where frame_number is in the right range and insert in new database +newTrajectoryId = -1 +# first frame num is commonTimeInterval +for cv, vs in videoSequences.iteritems(): + #return cursor.fetchone()[0] == 1 + for videoSequence in vs: + print videoSequence.name + try: + vsConnection = sqlite3.connect(dirname+os.path.sep+videoSequence.getDatabaseFilename()) + vsCursor = vsConnection.cursor() + if commonTimeInterval.first < videoSequence.startTime: + firstFrameNum = -(videoSequence.startTime-commonTimeInterval.first).seconds*frameRate + else: + firstFrameNum = (commonTimeInterval.first-videoSequence.startTime).seconds*frameRate + lastFrameNum = (commonTimeInterval.last-videoSequence.startTime).seconds*frameRate + # positions table + vsCursor.execute('SELECT * FROM positions WHERE frame_number BETWEEN {} AND {} ORDER BY trajectory_id'.format(firstFrameNum, lastFrameNum)) + featureIdCorrespondences = {} + currentTrajectoryId = -1 + for row in vsCursor: + if row[0] != currentTrajectoryId: + currentTrajectoryId = row[0] + newTrajectoryId += 1 + featureIdCorrespondences[currentTrajectoryId] = newTrajectoryId + outCursor.execute(storage.insertTrajectoryQuery('positions'), (newTrajectoryId, row[1]-firstFrameNum, row[2], row[3])) + # velocities table + vsCursor.execute('SELECT * FROM velocities WHERE frame_number BETWEEN {} AND {} ORDER BY trajectory_id'.format(firstFrameNum, lastFrameNum)) + for row in vsCursor: + outCursor.execute(storage.insertTrajectoryQuery('velocities'), (featureIdCorrespondences[row[0]], row[1]-firstFrameNum, row[2], row[3])) + # saving the id correspondences + for oldId, newId in featureIdCorrespondences.iteritems(): + outCursor.execute("INSERT INTO feature_correspondences (trajectory_id, source_dbname, db_trajectory_id) VALUES ({},\"{}\",{})".format(newId, videoSequence.name, oldId)) + outConnection.commit() + except sqlite3.OperationalError as error: + storage.printDBError(error) -# should we save the information of the new "sequence" in the metadata? -#session.add(VideoSequence('merged', , timedelta(seconds = 31616./30.), laurierSite, laurierCameraViewSpot0)) - -#session.commit() +# TODO save the information of the new "sequence" in the metadata +mergedCameraView = CameraView('merged', None, site, cv.cameraType, None, None) +session.commit() +session.add(VideoSequence('merged', commonTimeInterval.first, commonTimeInterval.last-commonTimeInterval.first, mergedCameraView)) +session.commit()
--- a/scripts/play-synced-videos.py Wed Jun 29 13:50:21 2016 -0400 +++ b/scripts/play-synced-videos.py Wed Jun 29 17:56:19 2016 -0400 @@ -8,7 +8,7 @@ parser = argparse.ArgumentParser(description='The program displays several views of the same site synchronously.') parser.add_argument('-i', dest = 'metadataFilename', help = 'name of the metadata file', required = True) parser.add_argument('-n', dest = 'siteId', help = 'site id or site name', required = True) -parser.add_argument('-t', dest = 'startTime', help = 'time to start playing (format %Y-%m-%d %H:%M:%S, eg 2011-06-22 10:00:39)', required = True) +parser.add_argument('-f', dest = 'startTime', help = 'time to start playing (format %Y-%m-%d %H:%M:%S, eg 2011-06-22 10:00:39)', required = True) parser.add_argument('--fps', dest = 'frameRate', help = 'approximate frame rate to replay', default = -1, type = float) parser.add_argument('-r', dest = 'rescale', help = 'rescaling factor for the displayed image', default = 1., type = float) parser.add_argument('-s', dest = 'step', help = 'display every s image', default = 1, type = int)