Mercurial Hosting > traffic-intelligence
comparison python/storage.py @ 938:fbf12382f3f8
replaced db connection using with
author | Nicolas Saunier <nicolas.saunier@polymtl.ca> |
---|---|
date | Mon, 17 Jul 2017 16:11:18 -0400 |
parents | 0e63a918a1ca |
children | b1e8453c207c |
comparison
equal
deleted
inserted
replaced
937:b67a784beb69 | 938:fbf12382f3f8 |
---|---|
42 printDBError(error) | 42 printDBError(error) |
43 | 43 |
44 def deleteFromSqlite(filename, dataType): | 44 def deleteFromSqlite(filename, dataType): |
45 'Deletes (drops) some tables in the filename depending on type of data' | 45 'Deletes (drops) some tables in the filename depending on type of data' |
46 if path.isfile(filename): | 46 if path.isfile(filename): |
47 connection = sqlite3.connect(filename) | 47 with sqlite3.connect(filename) as connection: |
48 if dataType == 'object': | 48 if dataType == 'object': |
49 dropTables(connection, ['objects', 'objects_features']) | 49 dropTables(connection, ['objects', 'objects_features']) |
50 elif dataType == 'interaction': | 50 elif dataType == 'interaction': |
51 dropTables(connection, ['interactions', 'indicators']) | 51 dropTables(connection, ['interactions', 'indicators']) |
52 elif dataType == 'bb': | 52 elif dataType == 'bb': |
53 dropTables(connection, ['bounding_boxes']) | 53 dropTables(connection, ['bounding_boxes']) |
54 elif dataType == 'pois': | 54 elif dataType == 'pois': |
55 dropTables(connection, ['gaussians2d', 'objects_pois']) | 55 dropTables(connection, ['gaussians2d', 'objects_pois']) |
56 elif dataType == 'prototype': | 56 elif dataType == 'prototype': |
57 dropTables(connection, ['prototypes', 'objects_prototypes']) | 57 dropTables(connection, ['prototypes', 'objects_prototypes']) |
58 else: | 58 else: |
59 print('Unknown data type {} to delete from database'.format(dataType)) | 59 print('Unknown data type {} to delete from database'.format(dataType)) |
60 connection.close() | |
61 else: | 60 else: |
62 print('{} does not exist'.format(filename)) | 61 print('{} does not exist'.format(filename)) |
63 | 62 |
64 def tableExists(connection, tableName): | 63 def tableExists(connection, tableName): |
65 'indicates if the table exists in the database' | 64 'indicates if the table exists in the database' |
66 try: | 65 try: |
67 #connection = sqlite3.connect(filename) | |
68 cursor = connection.cursor() | 66 cursor = connection.cursor() |
69 cursor.execute('SELECT COUNT(*) FROM SQLITE_MASTER WHERE type = \'table\' AND name = \''+tableName+'\'') | 67 cursor.execute('SELECT COUNT(*) FROM SQLITE_MASTER WHERE type = \'table\' AND name = \''+tableName+'\'') |
70 return cursor.fetchone()[0] == 1 | 68 return cursor.fetchone()[0] == 1 |
71 except sqlite3.OperationalError as error: | 69 except sqlite3.OperationalError as error: |
72 printDBError(error) | 70 printDBError(error) |
110 | 108 |
111 def createIndex(connection, tableName, columnName, unique = False): | 109 def createIndex(connection, tableName, columnName, unique = False): |
112 '''Creates an index for the column in the table | 110 '''Creates an index for the column in the table |
113 I will make querying with a condition on this column faster''' | 111 I will make querying with a condition on this column faster''' |
114 try: | 112 try: |
115 #connection = sqlite3.connect(filename) | |
116 cursor = connection.cursor() | 113 cursor = connection.cursor() |
117 s = "CREATE " | 114 s = "CREATE " |
118 if unique: | 115 if unique: |
119 s += "UNIQUE " | 116 s += "UNIQUE " |
120 cursor.execute(s+"INDEX IF NOT EXISTS "+tableName+"_"+columnName+"_index ON "+tableName+"("+columnName+")") | 117 cursor.execute(s+"INDEX IF NOT EXISTS "+tableName+"_"+columnName+"_index ON "+tableName+"("+columnName+")") |
248 def loadTrajectoriesFromSqlite(filename, trajectoryType, objectNumbers = None, withFeatures = False, timeStep = None, tablePrefix = None): | 245 def loadTrajectoriesFromSqlite(filename, trajectoryType, objectNumbers = None, withFeatures = False, timeStep = None, tablePrefix = None): |
249 '''Loads the trajectories (in the general sense, | 246 '''Loads the trajectories (in the general sense, |
250 either features, objects (feature groups) or bounding box series) | 247 either features, objects (feature groups) or bounding box series) |
251 The number loaded is either the first objectNumbers objects, | 248 The number loaded is either the first objectNumbers objects, |
252 or the indices in objectNumbers from the database''' | 249 or the indices in objectNumbers from the database''' |
253 connection = sqlite3.connect(filename) | 250 objects = [] |
254 | 251 with sqlite3.connect(filename) as connection: |
255 if tablePrefix is None: | 252 if tablePrefix is None: |
256 prefix = '' | 253 prefix = '' |
257 else: | 254 else: |
258 prefix = tablePrefix + '_' | 255 prefix = tablePrefix + '_' |
259 objects = loadTrajectoriesFromTable(connection, prefix+'positions', trajectoryType, objectNumbers, timeStep) | 256 objects = loadTrajectoriesFromTable(connection, prefix+'positions', trajectoryType, objectNumbers, timeStep) |
260 objectVelocities = loadTrajectoriesFromTable(connection, prefix+'velocities', trajectoryType, objectNumbers, timeStep) | 257 objectVelocities = loadTrajectoriesFromTable(connection, prefix+'velocities', trajectoryType, objectNumbers, timeStep) |
261 | 258 |
262 if len(objectVelocities) > 0: | 259 if len(objectVelocities) > 0: |
263 for o,v in zip(objects, objectVelocities): | 260 for o,v in zip(objects, objectVelocities): |
264 if o.getNum() == v.getNum(): | 261 if o.getNum() == v.getNum(): |
265 o.velocities = v.positions | 262 o.velocities = v.positions |
266 o.velocities.duplicateLastPosition() # avoid having velocity shorter by one position than positions | 263 o.velocities.duplicateLastPosition() # avoid having velocity shorter by one position than positions |
267 else: | |
268 print('Could not match positions {0} with velocities {1}'.format(o.getNum(), v.getNum())) | |
269 | |
270 if trajectoryType == 'object': | |
271 cursor = connection.cursor() | |
272 try: | |
273 # attribute feature numbers to objects | |
274 queryStatement = 'SELECT trajectory_id, object_id FROM objects_features' | |
275 if objectNumbers is not None: | |
276 queryStatement += ' WHERE object_id '+getObjectCriteria(objectNumbers) | |
277 queryStatement += ' ORDER BY object_id' # order is important to group all features per object | |
278 logging.debug(queryStatement) | |
279 cursor.execute(queryStatement) | |
280 | |
281 featureNumbers = {} | |
282 for row in cursor: | |
283 objId = row[1] | |
284 if objId not in featureNumbers: | |
285 featureNumbers[objId] = [row[0]] | |
286 else: | 264 else: |
287 featureNumbers[objId].append(row[0]) | 265 print('Could not match positions {0} with velocities {1}'.format(o.getNum(), v.getNum())) |
288 | 266 |
289 for obj in objects: | 267 if trajectoryType == 'object': |
290 obj.featureNumbers = featureNumbers[obj.getNum()] | 268 cursor = connection.cursor() |
291 | 269 try: |
292 # load userType | 270 # attribute feature numbers to objects |
293 userTypes = loadUserTypesFromTable(cursor, objectNumbers) | 271 queryStatement = 'SELECT trajectory_id, object_id FROM objects_features' |
294 for obj in objects: | 272 if objectNumbers is not None: |
295 obj.userType = userTypes[obj.getNum()] | 273 queryStatement += ' WHERE object_id '+getObjectCriteria(objectNumbers) |
296 | 274 queryStatement += ' ORDER BY object_id' # order is important to group all features per object |
297 if withFeatures: | 275 logging.debug(queryStatement) |
298 nFeatures = 0 | 276 cursor.execute(queryStatement) |
277 | |
278 featureNumbers = {} | |
279 for row in cursor: | |
280 objId = row[1] | |
281 if objId not in featureNumbers: | |
282 featureNumbers[objId] = [row[0]] | |
283 else: | |
284 featureNumbers[objId].append(row[0]) | |
285 | |
299 for obj in objects: | 286 for obj in objects: |
300 nFeatures = max(nFeatures, max(obj.featureNumbers)) | 287 obj.featureNumbers = featureNumbers[obj.getNum()] |
301 features = loadTrajectoriesFromSqlite(filename, 'feature', nFeatures+1, timeStep = timeStep) | 288 |
289 # load userType | |
290 userTypes = loadUserTypesFromTable(cursor, objectNumbers) | |
302 for obj in objects: | 291 for obj in objects: |
303 obj.setFeatures(features) | 292 obj.userType = userTypes[obj.getNum()] |
304 | 293 |
305 except sqlite3.OperationalError as error: | 294 if withFeatures: |
306 printDBError(error) | 295 nFeatures = 0 |
307 objects = [] | 296 for obj in objects: |
308 | 297 nFeatures = max(nFeatures, max(obj.featureNumbers)) |
309 connection.close() | 298 features = loadTrajectoriesFromSqlite(filename, 'feature', nFeatures+1, timeStep = timeStep) |
299 for obj in objects: | |
300 obj.setFeatures(features) | |
301 | |
302 except sqlite3.OperationalError as error: | |
303 printDBError(error) | |
310 return objects | 304 return objects |
311 | 305 |
312 def loadObjectFeatureFrameNumbers(filename, objectNumbers = None): | 306 def loadObjectFeatureFrameNumbers(filename, objectNumbers = None): |
313 'Loads the feature frame numbers for each object' | 307 'Loads the feature frame numbers for each object' |
314 connection = sqlite3.connect(filename) | 308 with sqlite3.connect(filename) as connection: |
315 cursor = connection.cursor() | 309 cursor = connection.cursor() |
316 try: | 310 try: |
317 queryStatement = 'SELECT OF.object_id, TL.trajectory_id, TL.length FROM (SELECT trajectory_id, max(frame_number)-min(frame_number) AS length FROM positions GROUP BY trajectory_id) TL, objects_features OF WHERE TL.trajectory_id = OF.trajectory_id' | 311 queryStatement = 'SELECT OF.object_id, TL.trajectory_id, TL.length FROM (SELECT trajectory_id, max(frame_number)-min(frame_number) AS length FROM positions GROUP BY trajectory_id) TL, objects_features OF WHERE TL.trajectory_id = OF.trajectory_id' |
318 if objectNumbers is not None: | 312 if objectNumbers is not None: |
319 queryStatement += ' AND object_id '+getObjectCriteria(objectNumbers) | 313 queryStatement += ' AND object_id '+getObjectCriteria(objectNumbers) |
320 queryStatement += ' ORDER BY OF.object_id, TL.length DESC' | 314 queryStatement += ' ORDER BY OF.object_id, TL.length DESC' |
321 logging.debug(queryStatement) | 315 logging.debug(queryStatement) |
322 cursor.execute(queryStatement) | 316 cursor.execute(queryStatement) |
323 objectFeatureNumbers = {} | 317 objectFeatureNumbers = {} |
324 for row in cursor: | 318 for row in cursor: |
325 objId = row[0] | 319 objId = row[0] |
326 if objId in objectFeatureNumbers: | 320 if objId in objectFeatureNumbers: |
327 objectFeatureNumbers[objId].append(row[1]) | 321 objectFeatureNumbers[objId].append(row[1]) |
328 else: | 322 else: |
329 objectFeatureNumbers[objId] = [row[1]] | 323 objectFeatureNumbers[objId] = [row[1]] |
330 return objectFeatureNumbers | 324 return objectFeatureNumbers |
331 except sqlite3.OperationalError as error: | 325 except sqlite3.OperationalError as error: |
332 printDBError(error) | 326 printDBError(error) |
333 return None | 327 return None |
334 | 328 |
335 def addCurvilinearTrajectoriesFromSqlite(filename, objects): | 329 def addCurvilinearTrajectoriesFromSqlite(filename, objects): |
336 '''Adds curvilinear positions (s_coordinate, y_coordinate, lane) | 330 '''Adds curvilinear positions (s_coordinate, y_coordinate, lane) |
337 from a database to an existing MovingObject dict (indexed by each objects's num)''' | 331 from a database to an existing MovingObject dict (indexed by each objects's num)''' |
338 connection = sqlite3.connect(filename) | 332 with sqlite3.connect(filename) as connection: |
339 cursor = connection.cursor() | 333 cursor = connection.cursor() |
340 | 334 |
341 try: | 335 try: |
342 cursor.execute('SELECT * from curvilinear_positions order by trajectory_id, frame_number') | 336 cursor.execute('SELECT * from curvilinear_positions order by trajectory_id, frame_number') |
343 except sqlite3.OperationalError as error: | 337 except sqlite3.OperationalError as error: |
344 printDBError(error) | 338 printDBError(error) |
345 return [] | 339 return [] |
346 | 340 |
347 missingObjectNumbers = [] | 341 missingObjectNumbers = [] |
348 objNum = None | 342 objNum = None |
349 for row in cursor: | 343 for row in cursor: |
350 if objNum != row[0]: | 344 if objNum != row[0]: |
351 objNum = row[0] | 345 objNum = row[0] |
346 if objNum in objects: | |
347 objects[objNum].curvilinearPositions = moving.CurvilinearTrajectory() | |
348 else: | |
349 missingObjectNumbers.append(objNum) | |
352 if objNum in objects: | 350 if objNum in objects: |
353 objects[objNum].curvilinearPositions = moving.CurvilinearTrajectory() | 351 objects[objNum].curvilinearPositions.addPositionSYL(row[2],row[3],row[4]) |
354 else: | 352 if len(missingObjectNumbers) > 0: |
355 missingObjectNumbers.append(objNum) | 353 print('List of missing objects to attach corresponding curvilinear trajectories: {}'.format(missingObjectNumbers)) |
356 if objNum in objects: | |
357 objects[objNum].curvilinearPositions.addPositionSYL(row[2],row[3],row[4]) | |
358 if len(missingObjectNumbers) > 0: | |
359 print('List of missing objects to attach corresponding curvilinear trajectories: {}'.format(missingObjectNumbers)) | |
360 | 354 |
361 def saveTrajectoriesToTable(connection, objects, trajectoryType, tablePrefix = None): | 355 def saveTrajectoriesToTable(connection, objects, trajectoryType, tablePrefix = None): |
362 'Saves trajectories in table tableName' | 356 'Saves trajectories in table tableName' |
363 cursor = connection.cursor() | 357 cursor = connection.cursor() |
364 # Parse feature and/or object structure and commit to DB | 358 # Parse feature and/or object structure and commit to DB |
425 '''Writes features, ie the trajectory positions (and velocities if exist) | 419 '''Writes features, ie the trajectory positions (and velocities if exist) |
426 with their instants to a specified sqlite file | 420 with their instants to a specified sqlite file |
427 Either feature positions (and velocities if they exist) | 421 Either feature positions (and velocities if they exist) |
428 or curvilinear positions will be saved at a time''' | 422 or curvilinear positions will be saved at a time''' |
429 | 423 |
430 connection = sqlite3.connect(outputFilename) | 424 with sqlite3.connect(outputFilename) as connection: |
431 try: | 425 try: |
432 saveTrajectoriesToTable(connection, objects, trajectoryType, None) | 426 saveTrajectoriesToTable(connection, objects, trajectoryType, None) |
433 except sqlite3.OperationalError as error: | 427 except sqlite3.OperationalError as error: |
434 printDBError(error) | 428 printDBError(error) |
435 connection.close() | 429 |
430 def setRoadUserTypes(filename, objects): | |
431 '''Saves the user types of the objects in the sqlite database stored in filename | |
432 The objects should exist in the objects table''' | |
433 with sqlite3.connect(filename) as connection: | |
434 cursor = connection.cursor() | |
435 for obj in objects: | |
436 cursor.execute('update objects set road_user_type = {} WHERE object_id = {}'.format(obj.getUserType(), obj.getNum())) | |
437 connection.commit() | |
436 | 438 |
437 def loadBBMovingObjectsFromSqlite(filename, objectType = 'bb', objectNumbers = None, timeStep = None): | 439 def loadBBMovingObjectsFromSqlite(filename, objectType = 'bb', objectNumbers = None, timeStep = None): |
438 '''Loads bounding box moving object from an SQLite | 440 '''Loads bounding box moving object from an SQLite |
439 (format of SQLite output by the ground truth annotation tool | 441 (format of SQLite output by the ground truth annotation tool |
440 or Urban Tracker | 442 or Urban Tracker |
441 | 443 |
442 Load descriptions?''' | 444 Load descriptions?''' |
443 connection = sqlite3.connect(filename) | |
444 objects = [] | 445 objects = [] |
445 | 446 with sqlite3.connect(filename) as connection: |
446 if objectType == 'bb': | 447 if objectType == 'bb': |
447 topCorners = loadTrajectoriesFromTable(connection, 'bounding_boxes', 'bbtop', objectNumbers, timeStep) | 448 topCorners = loadTrajectoriesFromTable(connection, 'bounding_boxes', 'bbtop', objectNumbers, timeStep) |
448 bottomCorners = loadTrajectoriesFromTable(connection, 'bounding_boxes', 'bbbottom', objectNumbers, timeStep) | 449 bottomCorners = loadTrajectoriesFromTable(connection, 'bounding_boxes', 'bbbottom', objectNumbers, timeStep) |
449 userTypes = loadUserTypesFromTable(connection.cursor(), objectNumbers) # string format is same as object | 450 userTypes = loadUserTypesFromTable(connection.cursor(), objectNumbers) # string format is same as object |
450 | 451 |
451 for t, b in zip(topCorners, bottomCorners): | 452 for t, b in zip(topCorners, bottomCorners): |
452 num = t.getNum() | 453 num = t.getNum() |
453 if t.getNum() == b.getNum(): | 454 if t.getNum() == b.getNum(): |
454 annotation = moving.BBMovingObject(num, t.getTimeInterval(), t, b, userTypes[num]) | 455 annotation = moving.BBMovingObject(num, t.getTimeInterval(), t, b, userTypes[num]) |
455 objects.append(annotation) | 456 objects.append(annotation) |
456 else: | 457 else: |
457 print ('Unknown type of bounding box {}'.format(objectType)) | 458 print ('Unknown type of bounding box {}'.format(objectType)) |
458 | |
459 connection.close() | |
460 return objects | 459 return objects |
461 | 460 |
462 def saveInteraction(cursor, interaction): | 461 def saveInteraction(cursor, interaction): |
463 roadUserNumbers = list(interaction.getRoadUserNumbers()) | 462 roadUserNumbers = list(interaction.getRoadUserNumbers()) |
464 cursor.execute('INSERT INTO interactions VALUES({}, {}, {}, {}, {})'.format(interaction.getNum(), roadUserNumbers[0], roadUserNumbers[1], interaction.getFirstInstant(), interaction.getLastInstant())) | 463 cursor.execute('INSERT INTO interactions VALUES({}, {}, {}, {}, {})'.format(interaction.getNum(), roadUserNumbers[0], roadUserNumbers[1], interaction.getFirstInstant(), interaction.getLastInstant())) |
465 | 464 |
466 def saveInteractionsToSqlite(filename, interactions): | 465 def saveInteractionsToSqlite(filename, interactions): |
467 'Saves the interactions in the table' | 466 'Saves the interactions in the table' |
468 connection = sqlite3.connect(filename) | 467 with sqlite3.connect(filename) as connection: |
469 cursor = connection.cursor() | 468 cursor = connection.cursor() |
470 try: | 469 try: |
471 createInteractionTable(cursor) | 470 createInteractionTable(cursor) |
472 for inter in interactions: | 471 for inter in interactions: |
473 saveInteraction(cursor, inter) | 472 saveInteraction(cursor, inter) |
474 except sqlite3.OperationalError as error: | 473 except sqlite3.OperationalError as error: |
475 printDBError(error) | 474 printDBError(error) |
476 connection.commit() | 475 connection.commit() |
477 connection.close() | |
478 | 476 |
479 def saveIndicator(cursor, interactionNum, indicator): | 477 def saveIndicator(cursor, interactionNum, indicator): |
480 for instant in indicator.getTimeInterval(): | 478 for instant in indicator.getTimeInterval(): |
481 if indicator[instant]: | 479 if indicator[instant]: |
482 cursor.execute('INSERT INTO indicators VALUES({}, {}, {}, {})'.format(interactionNum, events.Interaction.indicatorNameToIndices[indicator.getName()], instant, indicator[instant])) | 480 cursor.execute('INSERT INTO indicators VALUES({}, {}, {}, {})'.format(interactionNum, events.Interaction.indicatorNameToIndices[indicator.getName()], instant, indicator[instant])) |
483 | 481 |
484 def saveIndicatorsToSqlite(filename, interactions, indicatorNames = events.Interaction.indicatorNames): | 482 def saveIndicatorsToSqlite(filename, interactions, indicatorNames = events.Interaction.indicatorNames): |
485 'Saves the indicator values in the table' | 483 'Saves the indicator values in the table' |
486 connection = sqlite3.connect(filename) | 484 with sqlite3.connect(filename) as connection: |
487 cursor = connection.cursor() | 485 cursor = connection.cursor() |
488 try: | 486 try: |
489 createInteractionTable(cursor) | 487 createInteractionTable(cursor) |
490 createIndicatorTable(cursor) | 488 createIndicatorTable(cursor) |
491 for inter in interactions: | 489 for inter in interactions: |
492 saveInteraction(cursor, inter) | 490 saveInteraction(cursor, inter) |
493 for indicatorName in indicatorNames: | 491 for indicatorName in indicatorNames: |
494 indicator = inter.getIndicator(indicatorName) | 492 indicator = inter.getIndicator(indicatorName) |
495 if indicator is not None: | 493 if indicator is not None: |
496 saveIndicator(cursor, inter.getNum(), indicator) | 494 saveIndicator(cursor, inter.getNum(), indicator) |
497 except sqlite3.OperationalError as error: | 495 except sqlite3.OperationalError as error: |
498 printDBError(error) | 496 printDBError(error) |
499 connection.commit() | 497 connection.commit() |
500 connection.close() | |
501 | 498 |
502 def loadInteractionsFromSqlite(filename): | 499 def loadInteractionsFromSqlite(filename): |
503 '''Loads interaction and their indicators | 500 '''Loads interaction and their indicators |
504 | 501 |
505 TODO choose the interactions to load''' | 502 TODO choose the interactions to load''' |
506 interactions = [] | 503 interactions = [] |
507 connection = sqlite3.connect(filename) | 504 with sqlite3.connect(filename) as connection: |
508 cursor = connection.cursor() | 505 cursor = connection.cursor() |
509 try: | 506 try: |
510 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') | 507 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') |
511 interactionNum = -1 | 508 interactionNum = -1 |
512 indicatorTypeNum = -1 | 509 indicatorTypeNum = -1 |
513 tmpIndicators = {} | 510 tmpIndicators = {} |
514 for row in cursor: | 511 for row in cursor: |
515 if row[0] != interactionNum: | 512 if row[0] != interactionNum: |
516 interactionNum = row[0] | 513 interactionNum = row[0] |
517 interactions.append(events.Interaction(interactionNum, moving.TimeInterval(row[3],row[4]), row[1], row[2])) | 514 interactions.append(events.Interaction(interactionNum, moving.TimeInterval(row[3],row[4]), row[1], row[2])) |
518 interactions[-1].indicators = {} | 515 interactions[-1].indicators = {} |
519 if indicatorTypeNum != row[5] or row[0] != interactionNum: | 516 if indicatorTypeNum != row[5] or row[0] != interactionNum: |
520 indicatorTypeNum = row[5] | 517 indicatorTypeNum = row[5] |
521 indicatorName = events.Interaction.indicatorNames[indicatorTypeNum] | 518 indicatorName = events.Interaction.indicatorNames[indicatorTypeNum] |
522 indicatorValues = {row[6]:row[7]} | 519 indicatorValues = {row[6]:row[7]} |
523 interactions[-1].indicators[indicatorName] = indicators.SeverityIndicator(indicatorName, indicatorValues, mostSevereIsMax = not indicatorName in events.Interaction.timeIndicators) | 520 interactions[-1].indicators[indicatorName] = indicators.SeverityIndicator(indicatorName, indicatorValues, mostSevereIsMax = not indicatorName in events.Interaction.timeIndicators) |
524 else: | 521 else: |
525 indicatorValues[row[6]] = row[7] | 522 indicatorValues[row[6]] = row[7] |
526 interactions[-1].indicators[indicatorName].timeInterval.last = row[6] | 523 interactions[-1].indicators[indicatorName].timeInterval.last = row[6] |
527 except sqlite3.OperationalError as error: | 524 except sqlite3.OperationalError as error: |
528 printDBError(error) | 525 printDBError(error) |
529 return [] | 526 return [] |
530 connection.close() | |
531 return interactions | 527 return interactions |
532 # load first and last object instants | 528 # load first and last object instants |
533 # 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 | 529 # 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 |
534 | 530 |
535 def createBoundingBoxTable(filename, invHomography = None): | 531 def createBoundingBoxTable(filename, invHomography = None): |
536 '''Create the table to store the object bounding boxes in image space | 532 '''Create the table to store the object bounding boxes in image space |
537 ''' | 533 ''' |
538 connection = sqlite3.connect(filename) | 534 with sqlite3.connect(filename) as connection: |
539 cursor = connection.cursor() | 535 cursor = connection.cursor() |
540 try: | 536 try: |
541 cursor.execute('CREATE TABLE IF NOT EXISTS bounding_boxes (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))') | 537 cursor.execute('CREATE TABLE IF NOT EXISTS bounding_boxes (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))') |
542 cursor.execute('INSERT INTO bounding_boxes SELECT object_id, frame_number, min(x), min(y), max(x), max(y) from ' | 538 cursor.execute('INSERT INTO bounding_boxes SELECT object_id, frame_number, min(x), min(y), max(x), max(y) from ' |
543 '(SELECT object_id, frame_number, (x*{}+y*{}+{})/w as x, (x*{}+y*{}+{})/w as y from ' | 539 '(SELECT object_id, frame_number, (x*{}+y*{}+{})/w as x, (x*{}+y*{}+{})/w as y from ' |
544 '(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])+ | 540 '(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])+ |
545 'GROUP BY object_id, frame_number') | 541 'GROUP BY object_id, frame_number') |
546 except sqlite3.OperationalError as error: | 542 except sqlite3.OperationalError as error: |
547 printDBError(error) | 543 printDBError(error) |
548 connection.commit() | 544 connection.commit() |
549 connection.close() | |
550 | 545 |
551 def loadBoundingBoxTableForDisplay(filename): | 546 def loadBoundingBoxTableForDisplay(filename): |
552 '''Loads bounding boxes from bounding_boxes table for display over trajectories''' | 547 '''Loads bounding boxes from bounding_boxes table for display over trajectories''' |
553 connection = sqlite3.connect(filename) | |
554 cursor = connection.cursor() | |
555 boundingBoxes = {} # list of bounding boxes for each instant | 548 boundingBoxes = {} # list of bounding boxes for each instant |
556 try: | 549 with sqlite3.connect(filename) as connection: |
557 cursor.execute('SELECT name FROM sqlite_master WHERE type=\'table\' AND name=\'bounding_boxes\'') | 550 cursor = connection.cursor() |
558 result = cursor.fetchall() | 551 try: |
559 if len(result) > 0: | 552 cursor.execute('SELECT name FROM sqlite_master WHERE type=\'table\' AND name=\'bounding_boxes\'') |
560 cursor.execute('SELECT * FROM bounding_boxes') | 553 result = cursor.fetchall() |
561 for row in cursor: | 554 if len(result) > 0: |
562 boundingBoxes.setdefault(row[1], []).append([moving.Point(row[2], row[3]), moving.Point(row[4], row[5])]) | 555 cursor.execute('SELECT * FROM bounding_boxes') |
563 except sqlite3.OperationalError as error: | 556 for row in cursor: |
564 printDBError(error) | 557 boundingBoxes.setdefault(row[1], []).append([moving.Point(row[2], row[3]), moving.Point(row[4], row[5])]) |
565 return boundingBoxes | 558 except sqlite3.OperationalError as error: |
566 connection.close() | 559 printDBError(error) |
560 return boundingBoxes | |
567 return boundingBoxes | 561 return boundingBoxes |
568 | 562 |
569 ######################### | 563 ######################### |
570 # saving and loading for scene interpretation: POIs and Prototypes | 564 # saving and loading for scene interpretation: POIs and Prototypes |
571 ######################### | 565 ######################### |
572 | 566 |
573 def savePrototypesToSqlite(filename, prototypes): | 567 def savePrototypesToSqlite(filename, prototypes): |
574 '''save the prototypes (a prototype is defined by a filename, a number and type''' | 568 '''save the prototypes (a prototype is defined by a filename, a number and type''' |
575 connection = sqlite3.connect(filename) | 569 with sqlite3.connect(filename) as connection: |
576 cursor = connection.cursor() | 570 cursor = connection.cursor() |
577 try: | 571 try: |
578 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))') | 572 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))') |
579 for p in prototypes: | 573 for p in prototypes: |
580 cursor.execute('INSERT INTO prototypes VALUES(?,?,?,?)', (p.getFilename(), p.getNum(), p.getTrajectoryType(), p.getNMatchings())) | 574 cursor.execute('INSERT INTO prototypes VALUES(?,?,?,?)', (p.getFilename(), p.getNum(), p.getTrajectoryType(), p.getNMatchings())) |
581 except sqlite3.OperationalError as error: | 575 except sqlite3.OperationalError as error: |
582 printDBError(error) | 576 printDBError(error) |
583 connection.commit() | 577 connection.commit() |
584 connection.close() | |
585 | 578 |
586 def savePrototypeAssignmentsToSqlite(filename, objects, labels, prototypes): | 579 def savePrototypeAssignmentsToSqlite(filename, objects, labels, prototypes): |
587 connection = sqlite3.connect(filename) | 580 with sqlite3.connect(filename) as connection: |
588 cursor = connection.cursor() | 581 cursor = connection.cursor() |
589 try: | 582 try: |
590 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))') | 583 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))') |
591 for obj, label in zip(objects, labels): | 584 for obj, label in zip(objects, labels): |
592 proto = prototypes[label] | 585 proto = prototypes[label] |
593 cursor.execute('INSERT INTO objects_prototypes VALUES(?,?,?,?)', (obj.getNum(), proto.getFilename(), proto.getNum(), proto.getTrajectoryType())) | 586 cursor.execute('INSERT INTO objects_prototypes VALUES(?,?,?,?)', (obj.getNum(), proto.getFilename(), proto.getNum(), proto.getTrajectoryType())) |
594 except sqlite3.OperationalError as error: | 587 except sqlite3.OperationalError as error: |
595 printDBError(error) | 588 printDBError(error) |
596 connection.commit() | 589 connection.commit() |
597 connection.close() | |
598 | 590 |
599 def loadPrototypesFromSqlite(filename, withTrajectories = True): | 591 def loadPrototypesFromSqlite(filename, withTrajectories = True): |
600 'Loads prototype ids and matchings (if stored)' | 592 'Loads prototype ids and matchings (if stored)' |
601 connection = sqlite3.connect(filename) | |
602 cursor = connection.cursor() | |
603 prototypes = [] | 593 prototypes = [] |
604 objects = [] | 594 with sqlite3.connect(filename) as connection: |
605 try: | 595 cursor = connection.cursor() |
606 cursor.execute('SELECT * FROM prototypes') | 596 objects = [] |
607 for row in cursor: | 597 try: |
608 prototypes.append(moving.Prototype(row[0], row[1], row[2], row[3])) | 598 cursor.execute('SELECT * FROM prototypes') |
609 if withTrajectories: | 599 for row in cursor: |
610 for p in prototypes: | 600 prototypes.append(moving.Prototype(row[0], row[1], row[2], row[3])) |
611 p.setMovingObject(loadTrajectoriesFromSqlite(p.getFilename(), p.getTrajectoryType(), [p.getNum()])[0]) | 601 if withTrajectories: |
612 # loadingInformation = {} # complicated slightly optimized | 602 for p in prototypes: |
613 # for p in prototypes: | 603 p.setMovingObject(loadTrajectoriesFromSqlite(p.getFilename(), p.getTrajectoryType(), [p.getNum()])[0]) |
614 # dbfn = p.getFilename() | 604 # loadingInformation = {} # complicated slightly optimized |
615 # trajType = p.getTrajectoryType() | 605 # for p in prototypes: |
616 # if (dbfn, trajType) in loadingInformation: | 606 # dbfn = p.getFilename() |
617 # loadingInformation[(dbfn, trajType)].append(p) | 607 # trajType = p.getTrajectoryType() |
618 # else: | 608 # if (dbfn, trajType) in loadingInformation: |
619 # loadingInformation[(dbfn, trajType)] = [p] | 609 # loadingInformation[(dbfn, trajType)].append(p) |
620 # for k, v in loadingInformation.iteritems(): | 610 # else: |
621 # objects += loadTrajectoriesFromSqlite(k[0], k[1], [p.getNum() for p in v]) | 611 # loadingInformation[(dbfn, trajType)] = [p] |
622 except sqlite3.OperationalError as error: | 612 # for k, v in loadingInformation.iteritems(): |
623 printDBError(error) | 613 # objects += loadTrajectoriesFromSqlite(k[0], k[1], [p.getNum() for p in v]) |
624 connection.close() | 614 except sqlite3.OperationalError as error: |
615 printDBError(error) | |
625 if len(set([p.getTrajectoryType() for p in prototypes])) > 1: | 616 if len(set([p.getTrajectoryType() for p in prototypes])) > 1: |
626 print('Different types of prototypes in database ({}).'.format(set([p.getTrajectoryType() for p in prototypes]))) | 617 print('Different types of prototypes in database ({}).'.format(set([p.getTrajectoryType() for p in prototypes]))) |
627 return prototypes | 618 return prototypes |
628 | 619 |
629 def savePOIsToSqlite(filename, gmm, gmmType, gmmId): | 620 def savePOIsToSqlite(filename, gmm, gmmType, gmmId): |
630 '''Saves a Gaussian mixture model (of class sklearn.mixture.GaussianMixture) | 621 '''Saves a Gaussian mixture model (of class sklearn.mixture.GaussianMixture) |
631 gmmType is a type of GaussianMixture, learnt either from beginnings or ends of trajectories''' | 622 gmmType is a type of GaussianMixture, learnt either from beginnings or ends of trajectories''' |
632 connection = sqlite3.connect(filename) | 623 with sqlite3.connect(filename) as connection: |
633 cursor = connection.cursor() | 624 cursor = connection.cursor() |
634 if gmmType not in ['beginning', 'end']: | 625 if gmmType not in ['beginning', 'end']: |
635 print('Unknown POI type {}. Exiting'.format(gmmType)) | 626 print('Unknown POI type {}. Exiting'.format(gmmType)) |
636 import sys | 627 import sys |
637 sys.exit() | 628 sys.exit() |
638 try: | 629 try: |
639 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))') | 630 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))') |
640 for i in xrange(gmm.n_components): | 631 for i in xrange(gmm.n_components): |
641 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()))) | 632 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()))) |
642 connection.commit() | 633 connection.commit() |
643 except sqlite3.OperationalError as error: | 634 except sqlite3.OperationalError as error: |
644 printDBError(error) | 635 printDBError(error) |
645 connection.close() | |
646 | 636 |
647 def savePOIAssignmentsToSqlite(filename, objects): | 637 def savePOIAssignmentsToSqlite(filename, objects): |
648 'save the od fields of objects' | 638 'save the od fields of objects' |
649 connection = sqlite3.connect(filename) | 639 with sqlite3.connect(filename) as connection: |
650 cursor = connection.cursor() | 640 cursor = connection.cursor() |
651 try: | 641 try: |
652 cursor.execute('CREATE TABLE IF NOT EXISTS objects_pois (object_id INTEGER, origin_poi_id INTEGER, destination_poi_id INTEGER, PRIMARY KEY(object_id))') | 642 cursor.execute('CREATE TABLE IF NOT EXISTS objects_pois (object_id INTEGER, origin_poi_id INTEGER, destination_poi_id INTEGER, PRIMARY KEY(object_id))') |
653 for o in objects: | 643 for o in objects: |
654 cursor.execute('INSERT INTO objects_pois VALUES(?,?,?)', (o.getNum(), o.od[0], o.od[1])) | 644 cursor.execute('INSERT INTO objects_pois VALUES(?,?,?)', (o.getNum(), o.od[0], o.od[1])) |
655 connection.commit() | 645 connection.commit() |
656 except sqlite3.OperationalError as error: | 646 except sqlite3.OperationalError as error: |
657 printDBError(error) | 647 printDBError(error) |
658 connection.close() | |
659 | 648 |
660 def loadPOIsFromSqlite(filename): | 649 def loadPOIsFromSqlite(filename): |
661 'Loads all 2D Gaussians in the database' | 650 'Loads all 2D Gaussians in the database' |
662 from sklearn import mixture # todo if not avalaible, load data in duck-typed class with same fields | 651 from sklearn import mixture # todo if not avalaible, load data in duck-typed class with same fields |
663 from ast import literal_eval | 652 from ast import literal_eval |
664 connection = sqlite3.connect(filename) | |
665 cursor = connection.cursor() | |
666 pois = [] | 653 pois = [] |
667 try: | 654 with sqlite3.connect(filename) as connection: |
668 cursor.execute('SELECT * from gaussians2d') | 655 cursor = connection.cursor() |
669 gmmId = None | 656 try: |
670 gmm = [] | 657 cursor.execute('SELECT * from gaussians2d') |
671 for row in cursor: | 658 gmmId = None |
672 if gmmId is None or row[0] != gmmId: | 659 gmm = [] |
673 if len(gmm) > 0: | 660 for row in cursor: |
674 tmp = mixture.GaussianMixture(len(gmm), covarianceType) | 661 if gmmId is None or row[0] != gmmId: |
675 tmp.means_ = array([gaussian['mean'] for gaussian in gmm]) | 662 if len(gmm) > 0: |
676 tmp.covariances_ = array([gaussian['covar'] for gaussian in gmm]) | 663 tmp = mixture.GaussianMixture(len(gmm), covarianceType) |
677 tmp.weights_ = array([gaussian['weight'] for gaussian in gmm]) | 664 tmp.means_ = array([gaussian['mean'] for gaussian in gmm]) |
678 tmp.gmmTypes = [gaussian['type'] for gaussian in gmm] | 665 tmp.covariances_ = array([gaussian['covar'] for gaussian in gmm]) |
679 tmp.precisions_cholesky_ = array([gaussian['precisions'] for gaussian in gmm]) | 666 tmp.weights_ = array([gaussian['weight'] for gaussian in gmm]) |
680 pois.append(tmp) | 667 tmp.gmmTypes = [gaussian['type'] for gaussian in gmm] |
681 gaussian = {'type': row[2], | 668 tmp.precisions_cholesky_ = array([gaussian['precisions'] for gaussian in gmm]) |
682 'mean': row[3:5], | 669 pois.append(tmp) |
683 'covar': array(literal_eval(row[5])), | 670 gaussian = {'type': row[2], |
684 'weight': row[7], | 671 'mean': row[3:5], |
685 'precisions': array(literal_eval(row[8]))} | 672 'covar': array(literal_eval(row[5])), |
686 gmm = [gaussian] | 673 'weight': row[7], |
687 covarianceType = row[6] | 674 'precisions': array(literal_eval(row[8]))} |
688 gmmId = row[0] | 675 gmm = [gaussian] |
689 else: | 676 covarianceType = row[6] |
690 gmm.append({'type': row[2], | 677 gmmId = row[0] |
691 'mean': row[3:5], | 678 else: |
692 'covar': array(literal_eval(row[5])), | 679 gmm.append({'type': row[2], |
693 'weight': row[7], | 680 'mean': row[3:5], |
694 'precisions': array(literal_eval(row[8]))}) | 681 'covar': array(literal_eval(row[5])), |
695 if len(gmm) > 0: | 682 'weight': row[7], |
696 tmp = mixture.GaussianMixture(len(gmm), covarianceType) | 683 'precisions': array(literal_eval(row[8]))}) |
697 tmp.means_ = array([gaussian['mean'] for gaussian in gmm]) | 684 if len(gmm) > 0: |
698 tmp.covariances_ = array([gaussian['covar'] for gaussian in gmm]) | 685 tmp = mixture.GaussianMixture(len(gmm), covarianceType) |
699 tmp.weights_ = array([gaussian['weight'] for gaussian in gmm]) | 686 tmp.means_ = array([gaussian['mean'] for gaussian in gmm]) |
700 tmp.gmmTypes = [gaussian['type'] for gaussian in gmm] | 687 tmp.covariances_ = array([gaussian['covar'] for gaussian in gmm]) |
701 tmp.precisions_cholesky_ = array([gaussian['precisions'] for gaussian in gmm]) | 688 tmp.weights_ = array([gaussian['weight'] for gaussian in gmm]) |
702 pois.append(tmp) | 689 tmp.gmmTypes = [gaussian['type'] for gaussian in gmm] |
703 except sqlite3.OperationalError as error: | 690 tmp.precisions_cholesky_ = array([gaussian['precisions'] for gaussian in gmm]) |
704 printDBError(error) | 691 pois.append(tmp) |
705 connection.close() | 692 except sqlite3.OperationalError as error: |
693 printDBError(error) | |
706 return pois | 694 return pois |
707 | 695 |
708 ######################### | 696 ######################### |
709 # saving and loading for scene interpretation (Mohamed Gomaa Mohamed's PhD) | 697 # saving and loading for scene interpretation (Mohamed Gomaa Mohamed's PhD) |
710 ######################### | 698 ######################### |
880 cursor.execute('update objects set startRouteID = {} WHERE object_id = {}'.format(obj.startRouteID, obj.getNum())) | 868 cursor.execute('update objects set startRouteID = {} WHERE object_id = {}'.format(obj.startRouteID, obj.getNum())) |
881 cursor.execute('update objects set endRouteID = {} WHERE object_id = {}'.format(obj.endRouteID, obj.getNum())) | 869 cursor.execute('update objects set endRouteID = {} WHERE object_id = {}'.format(obj.endRouteID, obj.getNum())) |
882 connection.commit() | 870 connection.commit() |
883 connection.close() | 871 connection.close() |
884 | 872 |
885 def setRoadUserTypes(filename, objects): | |
886 '''Saves the user types of the objects in the sqlite database stored in filename | |
887 The objects should exist in the objects table''' | |
888 connection = sqlite3.connect(filename) | |
889 cursor = connection.cursor() | |
890 for obj in objects: | |
891 cursor.execute('update objects set road_user_type = {} WHERE object_id = {}'.format(obj.getUserType(), obj.getNum())) | |
892 connection.commit() | |
893 connection.close() | |
894 | |
895 ######################### | 873 ######################### |
896 # txt files | 874 # txt files |
897 ######################### | 875 ######################### |
898 | 876 |
899 def openCheck(filename, option = 'r', quitting = False): | 877 def openCheck(filename, option = 'r', quitting = False): |
900 '''Open file filename in read mode by default | 878 '''Open file filename in read mode by default |
901 and checks it is open''' | 879 and checks it is open''' |
902 try: | 880 try: |
903 return open(filename, option) | 881 return open(filename, option) |
904 except IOError: | 882 except IOError: |
905 print 'File %s could not be opened.' % filename | 883 print 'File {} could not be opened.'.format(filename) |
906 if quitting: | 884 if quitting: |
907 from sys import exit | 885 from sys import exit |
908 exit() | 886 exit() |
909 return None | 887 return None |
910 | 888 |
994 out.close() | 972 out.close() |
995 shutil.os.remove(sqlScriptFilename) | 973 shutil.os.remove(sqlScriptFilename) |
996 | 974 |
997 def loadObjectNumbersInLinkFromVissimFile(filename, linkIds): | 975 def loadObjectNumbersInLinkFromVissimFile(filename, linkIds): |
998 '''Finds the ids of the objects that go through any of the link in the list linkIds''' | 976 '''Finds the ids of the objects that go through any of the link in the list linkIds''' |
999 connection = sqlite3.connect(filename) | 977 with sqlite3.connect(filename) as connection: |
1000 cursor = connection.cursor() | 978 cursor = connection.cursor() |
1001 queryStatement = 'SELECT DISTINCT trajectory_id FROM curvilinear_positions where link_id IN ('+','.join([str(id) for id in linkIds])+')' | 979 queryStatement = 'SELECT DISTINCT trajectory_id FROM curvilinear_positions where link_id IN ('+','.join([str(id) for id in linkIds])+')' |
1002 try: | 980 try: |
1003 cursor.execute(queryStatement) | 981 cursor.execute(queryStatement) |
1004 return [row[0] for row in cursor] | 982 return [row[0] for row in cursor] |
1005 except sqlite3.OperationalError as error: | 983 except sqlite3.OperationalError as error: |
1006 printDBError(error) | 984 printDBError(error) |
1007 | 985 |
1008 def getNObjectsInLinkFromVissimFile(filename, linkIds): | 986 def getNObjectsInLinkFromVissimFile(filename, linkIds): |
1009 '''Returns the number of objects that traveled through the link ids''' | 987 '''Returns the number of objects that traveled through the link ids''' |
1010 connection = sqlite3.connect(filename) | 988 with sqlite3.connect(filename) as connection: |
1011 cursor = connection.cursor() | 989 cursor = connection.cursor() |
1012 queryStatement = 'SELECT link_id, COUNT(DISTINCT trajectory_id) FROM curvilinear_positions where link_id IN ('+','.join([str(id) for id in linkIds])+') GROUP BY link_id' | 990 queryStatement = 'SELECT link_id, COUNT(DISTINCT trajectory_id) FROM curvilinear_positions where link_id IN ('+','.join([str(id) for id in linkIds])+') GROUP BY link_id' |
1013 try: | 991 try: |
1014 cursor.execute(queryStatement) | 992 cursor.execute(queryStatement) |
1015 return {row[0]:row[1] for row in cursor} | 993 return {row[0]:row[1] for row in cursor} |
1016 except sqlite3.OperationalError as error: | 994 except sqlite3.OperationalError as error: |
1017 printDBError(error) | 995 printDBError(error) |
1018 | 996 |
1019 def loadTrajectoriesFromVissimFile(filename, simulationStepsPerTimeUnit, objectNumbers = None, warmUpLastInstant = None, usePandas = False, nDecimals = 2, lowMemory = True): | 997 def loadTrajectoriesFromVissimFile(filename, simulationStepsPerTimeUnit, objectNumbers = None, warmUpLastInstant = None, usePandas = False, nDecimals = 2, lowMemory = True): |
1020 '''Reads data from VISSIM .fzp trajectory file | 998 '''Reads data from VISSIM .fzp trajectory file |
1021 simulationStepsPerTimeUnit is the number of simulation steps per unit of time used by VISSIM (second) | 999 simulationStepsPerTimeUnit is the number of simulation steps per unit of time used by VISSIM (second) |
1022 for example, there seems to be 10 simulation steps per simulated second in VISSIM, | 1000 for example, there seems to be 10 simulation steps per simulated second in VISSIM, |
1065 if (warmUpLastInstant is None or instant >= warmUpLastInstant) and objNum in objects: | 1043 if (warmUpLastInstant is None or instant >= warmUpLastInstant) and objNum in objects: |
1066 objects[objNum].timeInterval.last = instant | 1044 objects[objNum].timeInterval.last = instant |
1067 objects[objNum].curvilinearPositions.addPositionSYL(s, y, lane) | 1045 objects[objNum].curvilinearPositions.addPositionSYL(s, y, lane) |
1068 line = readline(inputfile, '*$') | 1046 line = readline(inputfile, '*$') |
1069 elif filename.endswith(".sqlite"): | 1047 elif filename.endswith(".sqlite"): |
1070 connection = sqlite3.connect(filename) | 1048 with sqlite3.connect(filename) as connection: |
1071 cursor = connection.cursor() | 1049 cursor = connection.cursor() |
1072 queryStatement = 'SELECT t, trajectory_id, link_id, lane_id, s_coordinate, y_coordinate FROM curvilinear_positions' | 1050 queryStatement = 'SELECT t, trajectory_id, link_id, lane_id, s_coordinate, y_coordinate FROM curvilinear_positions' |
1073 if objectNumbers is not None: | 1051 if objectNumbers is not None: |
1074 queryStatement += ' WHERE trajectory_id '+getObjectCriteria(objectNumbers) | 1052 queryStatement += ' WHERE trajectory_id '+getObjectCriteria(objectNumbers) |
1075 queryStatement += ' ORDER BY trajectory_id, t' | 1053 queryStatement += ' ORDER BY trajectory_id, t' |
1076 try: | 1054 try: |
1077 cursor.execute(queryStatement) | 1055 cursor.execute(queryStatement) |
1078 for row in cursor: | 1056 for row in cursor: |
1079 objNum = row[1] | 1057 objNum = row[1] |
1080 instant = row[0]*simulationStepsPerTimeUnit | 1058 instant = row[0]*simulationStepsPerTimeUnit |
1081 s = row[4] | 1059 s = row[4] |
1082 y = row[5] | 1060 y = row[5] |
1083 lane = '{}_{}'.format(row[2], row[3]) | 1061 lane = '{}_{}'.format(row[2], row[3]) |
1084 if objNum not in objects: | 1062 if objNum not in objects: |
1085 if warmUpLastInstant is None or instant >= warmUpLastInstant: | 1063 if warmUpLastInstant is None or instant >= warmUpLastInstant: |
1086 if objectNumbers is None or len(objects) < objectNumbers: | 1064 if objectNumbers is None or len(objects) < objectNumbers: |
1087 objects[objNum] = moving.MovingObject(num = objNum, timeInterval = moving.TimeInterval(instant, instant)) | 1065 objects[objNum] = moving.MovingObject(num = objNum, timeInterval = moving.TimeInterval(instant, instant)) |
1088 objects[objNum].curvilinearPositions = moving.CurvilinearTrajectory() | 1066 objects[objNum].curvilinearPositions = moving.CurvilinearTrajectory() |
1089 if (warmUpLastInstant is None or instant >= warmUpLastInstant) and objNum in objects: | 1067 if (warmUpLastInstant is None or instant >= warmUpLastInstant) and objNum in objects: |
1090 objects[objNum].timeInterval.last = instant | 1068 objects[objNum].timeInterval.last = instant |
1091 objects[objNum].curvilinearPositions.addPositionSYL(s, y, lane) | 1069 objects[objNum].curvilinearPositions.addPositionSYL(s, y, lane) |
1092 except sqlite3.OperationalError as error: | 1070 except sqlite3.OperationalError as error: |
1093 printDBError(error) | 1071 printDBError(error) |
1094 else: | 1072 else: |
1095 print("File type of "+filename+" not supported (only .sqlite and .fzp files)") | 1073 print("File type of "+filename+" not supported (only .sqlite and .fzp files)") |
1096 return objects.values() | 1074 return objects.values() |
1097 | 1075 |
1098 def selectPDLanes(data, lanes = None): | 1076 def selectPDLanes(data, lanes = None): |