tvaLib
metadata.py
Go to the documentation of this file.
1 #!/usr/bin/env python
2 # tvaLib Copyright (c) 2012-2016 Paul G. St-Aubin
3 # Ecole Polytechnique de Montreal, McGill University
4 # Python 2.7; (dt) Spyder Windows 10 64-bit; ipython Ubuntu 15.04 64-bit
5 
10 from datetime import datetime
11 import os
12 from sqlalchemy import create_engine, Column, Integer, Float, Boolean, DateTime, String, ForeignKey
13 from sqlalchemy.orm import relationship, backref, sessionmaker
14 from sqlalchemy.ext.declarative import declarative_base
15 
16 from utils import datetimeFormat
17 
18 Base = declarative_base()
19 
20 
21 
24 def connectToDatabase(dbPath):
25  import sqlite3
26  conn = sqlite3.connect(dbPath)
27  return conn.cursor()
28 
29 def upgradeDatabase(dbPath, tableName, columnNames, columnTypes):
30  ''' Upgrade an existing database using these parameters (if the column
31  doesn't already exist). AKA database migration, not supported natively
32  by SQLAlchemy. This function is not necessary if the upgrade requires a
33  new table (supported natively by SQLAlchemy).
34 
35  Input:
36  ======
37  dbPath: path to database
38  tableName: name of table in database to alter
39  columnNames: a list of column names, e.g. ['Column1','Column2']
40  columnTypes: a list of column types, e.g. ['INTEGER','STRING']
41  '''
42 
43  c = connectToDatabase(dbPath)
44  for column_name,column_type in zip(columnNames,columnTypes):
45  try: c.execute('ALTER TABLE '+tableName+' ADD COLUMN '+column_name+' '+column_type)
46  except: pass
47  c.close()
48 
49 
52 class CamType(Base):
53  ''' Represents parameters of the specific camera used. '''
54  __tablename__ = 'camera_types'
55  idx = Column(Integer, primary_key=True)
56  name = Column(String)
57  resX = Column(Integer)
58  resY = Column(Integer)
59  frameRate = Column(Float)
60  camera_matrix = Column(String)
61  dist_coeffs = Column(String)
62  FOV = Column(Integer)
63  freeScalingParameter = Column(Float)
64  imageScalingFactor = Column(Float)
65 
66  def __init__(self, name, resX, resY, frameRate, camera_matrix='', dist_coeffs='', FOV=1, freeScalingParameter=1.0, imageScalingFactor=1.0):
67  self.name = name
68  try: self.resX = int(resX)
69  except: self.resX = None
70  try: self.resY = int(resY)
71  except: self.resY = None
72  try: self.frameRate = float(frameRate)
73  except: self.frameRate = None
74  self.camera_matrix = camera_matrix
75  self.dist_coeffs = dist_coeffs
76  try: self.FOV = float(FOV)
77  except: self.FOV = None
78  try: self.freeScalingParameter = float(freeScalingParameter)
79  except: self.freeScalingParameter = None
80  try: self.imageScalingFactor = float(imageScalingFactor)
81  except: self.imageScalingFactor = None
82 
83 
84 class Site(Base):
85  __tablename__ = 'sites'
86  idx = Column(Integer, primary_key=True)
87  name = Column(String) # same as path, relative to the database position
88  description = Column(String) # longer names, eg intersection of road1 and road2
89  xcoordinate = Column(Float) # ideally moving.Point, but needs to be
90  ycoordinate = Column(Float)
91  satres = Column(Float) # in metres/pixel
92  satFilename = Column(String)
93  alignments = Column(String)
94  sidewalks = Column(String)
95  bikepaths = Column(String)
96 
97  def __init__(self, name, description = '', xcoordinate = None, ycoordinate = None, satres=0.1, satFilename='ortho-cal.png', alignments='', sidewalks='', bikepaths=''):
98  self.name = name
99  self.description = description
100  try: self.xcoordinate = int(xcoordinate)
101  except: self.xcoordinate = None
102  try: self.ycoordinate = int(ycoordinate)
103  except: self.ycoordinate = None
104  try: self.satres = float(satres)
105  except: self.satres = None
106  self.satFilename = satFilename
107  self.alignments = alignments
108  self.sidewalks = sidewalks
109  self.bikepaths = bikepaths
110 
111  def __getitem__(self, i): return self.camera_views[i]
112 
113  def getFilename(self):
114  return self.name
115 
116 
118  __tablename__ = 'camera_views'
119  idx = Column(Integer, primary_key=True)
120  name = Column(String)
121  homographyFilename = Column(String) # path to homography filename, relative to the site name
122  homographyDistanceUnit = Column(String, default = 'm') # make sure it is default in the database
123  homography = Column(String)
124  cameraCalibrationFilename = Column(String) # path to full camera calibration, relative to the site name
125  configurationFilename = Column(String) # path to configuration .cfg file, relative to site name
126  mask = Column(String)
127  maskFilename = Column(String)
128  camOrigin = Column(String, default = '[0.0,0.0]')
129  camHeight = Column(Float, default = 10.0)
130 
131  siteId = Column(Integer, ForeignKey('sites.idx'))
132  site = relationship('Site', backref=backref('camera_views', order_by = idx))
133  cameraId = Column(Integer, ForeignKey('camera_types.idx'))
134  camera = relationship('CamType', backref=backref('camera_views', order_by = idx))
135 
136  def __init__(self, site, camera, name, homographyFilename='homography.txt', homographyDistanceUnit='m', homography='', cameraCalibrationFilename='', configurationFilename='', mask='', maskFilename='mask.png', camOrigin='[0.0,0.0]', camHeight=10.0):
137  self.site = site
138  self.camera = camera
139  self.name = name
140  self.homographyFilename = homographyFilename
141  self.homographyDistanceUnit = homographyDistanceUnit
142  self.homography = homography
143  self.cameraCalibrationFilename = cameraCalibrationFilename
144  self.configurationFilename = configurationFilename
145  self.mask = mask
146  self.maskFilename = maskFilename
147  self.camOrigin = camOrigin
148  self.camHeight = camHeight
149 
150 
151  def __getitem__(self, i): return self.video_sequences[i]
152 
153  def getHomographyFilename(self, relativeToSiteFilename = True):
154  if(relativeToSiteFilename): return self.site.getFilename()+os.path.sep+self.homographyFilename
155  else: return self.homographyFilename
156 
157 
159  __tablename__ = 'video_sequences'
160  idx = Column(Integer, primary_key=True)
161  videoFilename = Column(String) # path relative to the the site name
162  startTime = Column(DateTime)
163  duration = Column(Float) # video sequence duration
164  durationUnit = Column(String, default = 's')
165  configurationFilename = Column(String)
166  dataFilename = Column(String) # necessary to manage multiple tracking
167  translationX = Column(Float, default = 0.0)
168  translationY = Column(Float, default = 0.0)
169  rotation = Column(Float, default = 0.0)
170 
171  cameraViewId = Column(Integer, ForeignKey('camera_views.idx'))
172  cameraView = relationship('CameraView', backref=backref('video_sequences', order_by = idx))
173 
174  def __init__(self, cameraView, videoFilename, startTime, duration, dataFilename='', durationUnit='s', configurationFilename='', translationX=0.0, translationY=0.0, rotation=0.0):
175  ''' startTime is passed as string in utils.datetimeFormat, e.g.
176  2011-06-22 10:00:39. startTime can also be passed as a
177  datetimeFormat (e.g. %Y%m%d-%H%M) to be read directly off of the
178  video file instead.
179  '''
180  self.cameraView = cameraView
181  self.videoFilename = videoFilename
182  if(type(startTime) == str):
183  try: self.startTime = datetime.strptime(startTime, datetimeFormat)
184  except: self.startTime = datetime.strptime(os.path.splitext(videoFilename)[0], startTime)
185  else: self.startTime = startTime
186  self.duration = duration
187  if(not dataFilename): self.dataFilename = os.path.splitext(videoFilename)[0]+'.sqlite'
188  else: self.dataFilename = dataFilename
189  self.durationUnit = durationUnit
190  self.configurationFilename = configurationFilename
191  self.translationX = translationX
192  self.translationY = translationY
193  self.rotation = rotation
194 
195  def getVideoSequenceFilename(self, relativeToSiteFilename = True):
196  if(relativeToSiteFilename): return self.site.getFilename()+os.path.sep+self.name
197  else: return self.name
198 
199 
200 class Cluster(Base):
201  ''' Contains analysis cases. '''
202  __tablename__ = 'clusters'
203  idx = Column(Integer, primary_key=True)
204  name = Column(String)
205  xref_dbname = Column(String)
206  analyses = Column(String)
207  site_analyses = Column(String)
208  labels = Column(String)
209  colours = Column(String)
210  plot_sites = Column(Integer)
211 
212  def __init__(self, name, xref_dbname, analyses, site_analyses='', labels='', colours='', plot_sites=0):
213  self.name = name
214  self.xref_dbname = xref_dbname
215  self.analyses = analyses
216  self.site_analyses = site_analyses
217  self.labels = labels
218  self.colours = colours
219  self.plot_sites = plot_sites
220 
221 class Analysis(Base):
222  ''' Contains analysis cases. '''
223  __tablename__ = 'analyses'
224  idx = Column(Integer, primary_key=True)
225  name = Column(String)
226  site_analyses = Column(String)
227 
228  def __init__(self, name, site_analyses):
229  self.name = name
230  self.site_analyses = site_analyses
231 
233  ''' Contains site-specific analysis variables. '''
234  __tablename__ = 'site_analyses'
235  idx = Column(Integer, primary_key=True)
236  name = Column(String)
237  camIds = Column(String)
238  startTimes = Column(String)
239  endTimes = Column(String)
240  zone = Column(String)
241  virtual_loops = Column(String)
242  configurationFilename = Column(String)
243  description = Column(String) # eg sunny, before, after
244  temp_avg = Column(Float)
245  precipitation = Column(Float)
246  glare = Column(Boolean)
247  geo_1 = Column(Float)
248  geo_2 = Column(Float)
249  geo_3 = Column(Float)
250  geo_4 = Column(Float)
251  geo_5 = Column(Float)
252  expansion_factors = Column(String, default=str([0.041666 for i in range(24)])) # needs to be parsed externally, but handy for manual editing
253  xy_bounds = Column(String)
254  cm_bounds = Column(String)
255  max_speed = Column(Integer, default = 80)
256  hex_grid_x = Column(Integer, default = 60)
257  hex_grid_y = Column(Integer, default = 60)
258 
259  def __init__(self, zone='', camIds='', startTimes='', endTimes='', name='', virtual_loops='', description='', configurationFilename='', temp_avg=None, precipitation=None, glare=None, infrastructureClass=None, geo_1=None, geo_2=None, geo_3=None, geo_4=None, geo_5=None, expansion_factors='', xy_bounds='', cm_bounds='', max_speed=80, hex_grid_x=60, hex_grid_y=60):
260  ''' StartTime is passed as string in utils.datetimeFormat, eg 2011-06-22 10:00:39. '''
261  self.name = name
262  self.camIds = camIds
263  self.startTimes = startTimes
264  self.endTimes = endTimes
265  self.zone = zone
266  self.virtual_loops = virtual_loops
267  self.description = description
268  self.configurationFilename = configurationFilename
269  self.temp_avg = temp_avg
270  self.precipitation = precipitation
271  self.glare = glare
272  self.geo_1 = geo_1
273  self.geo_2 = geo_2
274  self.geo_3 = geo_3
275  self.geo_4 = geo_4
276  self.geo_5 = geo_5
277  self.expansion_factors = expansion_factors
278  self.xy_bounds = xy_bounds
279  self.cm_bounds = cm_bounds
280  try: self.max_speed = int(max_speed)
281  except: self.max_speed = 80
282  try: self.hex_grid_x = int(hex_grid_x)
283  except: self.hex_grid_x = 60
284  try: self.hex_grid_y = int(hex_grid_y)
285  except: self.hex_grid_y = 60
286 
289 def createDatabase(filename=':memory:'):
290  ''' Creates a session to query the filename. '''
291  engine = create_engine('sqlite:///'+filename)
292  Base.metadata.create_all(engine)
293  Session = sessionmaker(bind=engine)
294  return Session()
295 
296 def connectDatabase(filename=':memory:'):
297  ''' Creates a session to query the filename. '''
298  engine = create_engine('sqlite:///'+filename)
299  Session = sessionmaker(bind=engine)
300  return Session()
301 
302 def initializeSites(session, directoryName):
303  '''Initializes default site objects and Camera Views
304 
305  eg somedirectory/montreal/ contains intersection1, intersection2, etc.
306  The site names would be somedirectory/montreal/intersection1, somedirectory/montreal/intersection2, etc.'''
307  from os import listdir, path
308  sites = []
309  cameraViews = []
310  names = listdir(directoryName)
311  for name in names:
312  if path.isdir(directoryName+'/'+name):
313  sites.append(Site(directoryName+'/'+name, None))
314  cameraViews.append(CameraView(-1, None, None, sites[-1], None))
315  session.add_all(sites)
316  session.add_all(cameraViews)
317  session.commit()
318 
319 
323  __tablename__ = 'roundabout_quadrants'
324  idx = Column(Integer, primary_key=True)
325  start_lanes = Column(String)
326  end_lanes = Column(String)
327  approach_lanes = Column(String)
328  exit_lanes = Column(String)
329  ped_lanes = Column(String)
330  start_merge_zone = Column(String)
331  end_merge_zone = Column(String)
332  approach_merge_zone = Column(String)
333  exit_merge_zone = Column(String)
334 
335  saId = Column(Integer, ForeignKey('site_analyses.idx'))
336  site_analysis = relationship('SiteAnalysis', backref=backref('roundabout_quadrants', uselist=False))
337 
338  def __init__(self, site_analysis, start_lanes='', end_lanes='', approach_lanes='', exit_lanes='', ped_lanes='', start_merge_zone='', end_merge_zone='', approach_merge_zone='', exit_merge_zone=''):
339  self.site_analysis = site_analysis
340  self.start_lanes = start_lanes
341  self.end_lanes = end_lanes
342  self.approach_lanes = approach_lanes
343  self.exit_lanes = exit_lanes
344  self.ped_lanes = ped_lanes
345  self.start_merge_zone = start_merge_zone
346  self.end_merge_zone = end_merge_zone
347  self.approach_merge_zone = approach_merge_zone
348  self.exit_merge_zone = exit_merge_zone
349 
350 
352  __tablename__ = 'origin_destinations'
353  idx = Column(Integer, primary_key=True)
354  ODpairs = Column(String)
355 
356  saId = Column(Integer, ForeignKey('site_analyses.idx'))
357  site_analysis = relationship('SiteAnalysis', backref=backref('origin_destinations', uselist=False))
358 
359  def __init__(self, site_analysis, ODpairs=''):
360  self.site_analysis = site_analysis
361  self.ODpairs = ODpairs
362 
363 
365  __tablename__ = 'stop_signs'
366  idx = Column(Integer, primary_key=True)
367  aligns = Column(String)
368  curvy_dist = Column(String)
369  ua_aligns = Column(String)
370  ua_curvy_dist = Column(String)
371 
372  sId = Column(Integer, ForeignKey('sites.idx'))
373  site = relationship('Site', backref=backref('stop_signs', uselist=False))
374 
375  def __init__(self, site, aligns='', curvy_dist='', ua_aligns='', ua_curvy_dist=''):
376  self.site = site
377  self.aligns = aligns
378  self.curvy_dist = curvy_dist
379  self.ua_aligns = ua_aligns
380  self.ua_curvy_dist = ua_curvy_dist
381 
382 
383 
386 if __name__ == "__main__":
387 
390  session = createDatabase()
391 
392 
395  camTypes = session.query(CamType).all()
396  camTypes.append(CamType(name='Vivotek 1', resX=800, resY=600, frameRate=15.15, camera_matrix='', dist_coeffs='', FOV=1, freeScalingParameter=1.0, imageScalingFactor=1.0)) #0
397  camTypes.append(CamType(name='Vivotek 2', resX=800, resY=600, frameRate=30.00, camera_matrix='', dist_coeffs='', FOV=1, freeScalingParameter=1.0, imageScalingFactor=1.0)) #1
398  camTypes.append(CamType(name='Vivotek 3', resX=1280, resY=1024, frameRate=25.00, camera_matrix='', dist_coeffs='', FOV=1, freeScalingParameter=1.0, imageScalingFactor=1.0)) #2
399  camTypes.append(CamType(name='GoProHero2/3 1', resX=1280, resY=960, frameRate=29.97, camera_matrix='[[377.42,0.0,640.0],[0.0,378.43,490.0],[0.0,0.0,1.0]]', dist_coeffs='[-0.11759321,0.0148536,0.0,0.0,-0.00091816]', FOV=1, freeScalingParameter=1.31, imageScalingFactor=1.31)) #3
400  camTypes.append(CamType(name='GoProHero2/3 2', resX=1280, resY=720, frameRate=29.97, camera_matrix='[[469.96,0.0,640.0],[0.0,467.68,360.0],[0.0,0.0,1.0]]', dist_coeffs='[-0.18957,0.037319,0.0,0.0,-0.00337]', FOV=1, freeScalingParameter=0.5, imageScalingFactor=1.31)) #4
401  camTypes.append(CamType(name='Generic/Lund', resX=640, resY=480, frameRate=15.0, camera_matrix='', dist_coeffs='', FOV=1, freeScalingParameter=1.0, imageScalingFactor=1.0)) #5
402  camTypes.append(CamType(name='AxisPTZ/Lund', resX=704, resY=576, frameRate=22.19, camera_matrix='', dist_coeffs='', FOV=1, freeScalingParameter=1.0, imageScalingFactor=1.0)) #6
403 
404 
407  sites = session.query(Site).all()
408  sites.append(Site('Norway', satres=0.1776, alignments=''))
409 
410 
413  cameras = session.query(CameraView).all()
414  cameras.append(CameraView([x for x in sites if x.name=='Norway'][0], [x for x in camTypes if x.name=='Generic/Lund'][0], '20130617'))
415 
416 
419  sequences = session.query(VideoSequence).all()
420  sequences.append(VideoSequence([x for x in cameras if x.name=='20130617' and x.site.name=='Norway'][0], '18.avi', '2013-06-17 18:30:00', 2520.))
421 
422  session.add_all(camTypes)
423  session.add_all(sites)
424  session.add_all(cameras)
425  session.add_all(sequences)
426 
427 
430  site_analyses = session.query(SiteAnalysis).all()
431  site_analyses.append(SiteAnalysis(zone='[[[1,2],[3,4],[5,6]]]', camIds=str([x.idx for x in cameras if x.name=='20130617' and x.site.name=='Norway']), startTimes='2011-06-22 10:00:39', endTimes='2011-06-22 10:00:40'))
432 
433  session.add_all(site_analyses)
434 
435  analyses = session.query(Analysis).all()
436  analyses.append(Analysis('all', str([x.idx for x in site_analyses])))
437 
438  session.add_all(analyses)
439 
440 
443  session.commit()
def __init__(self, zone='', camIds='', startTimes='', endTimes='', name='', virtual_loops='', description='', configurationFilename='', temp_avg=None, precipitation=None, glare=None, infrastructureClass=None, geo_1=None, geo_2=None, geo_3=None, geo_4=None, geo_5=None, expansion_factors='', xy_bounds='', cm_bounds='', max_speed=80, hex_grid_x=60, hex_grid_y=60)
Definition: metadata.py:259
def __init__(self, name, xref_dbname, analyses, site_analyses='', labels='', colours='', plot_sites=0)
Definition: metadata.py:212
def createDatabase(filename=':memory:')
Basic TI DB handlers.
Definition: metadata.py:289
def getVideoSequenceFilename(self, relativeToSiteFilename=True)
Definition: metadata.py:195
def __init__(self, name, resX, resY, frameRate, camera_matrix='', dist_coeffs='', FOV=1, freeScalingParameter=1.0, imageScalingFactor=1.0)
Definition: metadata.py:66
def getHomographyFilename(self, relativeToSiteFilename=True)
Definition: metadata.py:153
def __init__(self, name, description='', xcoordinate=None, ycoordinate=None, satres=0.1, satFilename='ortho-cal.png', alignments='', sidewalks='', bikepaths='')
Definition: metadata.py:97
def __getitem__(self, i)
Definition: metadata.py:151
def __init__(self, name, site_analyses)
Definition: metadata.py:228
def upgradeDatabase(dbPath, tableName, columnNames, columnTypes)
Definition: metadata.py:29
def __init__(self, cameraView, videoFilename, startTime, duration, dataFilename='', durationUnit='s', configurationFilename='', translationX=0.0, translationY=0.0, rotation=0.0)
Definition: metadata.py:174
def __init__(self, site_analysis, start_lanes='', end_lanes='', approach_lanes='', exit_lanes='', ped_lanes='', start_merge_zone='', end_merge_zone='', approach_merge_zone='', exit_merge_zone='')
Definition: metadata.py:338
def __getitem__(self, i)
Definition: metadata.py:111
def __init__(self, site, camera, name, homographyFilename='homography.txt', homographyDistanceUnit='m', homography='', cameraCalibrationFilename='', configurationFilename='', mask='', maskFilename='mask.png', camOrigin='[0.0, camHeight=10.0)
Definition: metadata.py:136
def connectToDatabase(dbPath)
Tools.
Definition: metadata.py:24
def connectDatabase(filename=':memory:')
Definition: metadata.py:296
Basic TI objects.
Definition: metadata.py:52
def __init__(self, site_analysis, ODpairs='')
Definition: metadata.py:359
def initializeSites(session, directoryName)
Definition: metadata.py:302
def __init__(self, site, aligns='', curvy_dist='', ua_aligns='', ua_curvy_dist='')
Definition: metadata.py:375
def getFilename(self)
Definition: metadata.py:113