Mercurial Hosting > traffic-intelligence
comparison python/storage.py @ 917:89cc05867c4c
reorg and work in progress
author | Nicolas Saunier <nicolas.saunier@polymtl.ca> |
---|---|
date | Tue, 04 Jul 2017 18:00:01 -0400 |
parents | 13434f5017dd |
children | 3a06007a4bb7 |
comparison
equal
deleted
inserted
replaced
916:7345f0d51faa | 917:89cc05867c4c |
---|---|
37 cursor = connection.cursor() | 37 cursor = connection.cursor() |
38 for tableName in tableNames: | 38 for tableName in tableNames: |
39 cursor.execute('DROP TABLE IF EXISTS '+tableName) | 39 cursor.execute('DROP TABLE IF EXISTS '+tableName) |
40 except sqlite3.OperationalError as error: | 40 except sqlite3.OperationalError as error: |
41 printDBError(error) | 41 printDBError(error) |
42 | |
43 def deleteFromSqlite(filename, dataType): | |
44 'Deletes (drops) some tables in the filename depending on type of data' | |
45 if path.isfile(filename): | |
46 connection = sqlite3.connect(filename) | |
47 if dataType == 'object': | |
48 dropTables(connection, ['objects', 'objects_features']) | |
49 elif dataType == 'interaction': | |
50 dropTables(connection, ['interactions', 'indicators']) | |
51 elif dataType == 'bb': | |
52 dropTables(connection, ['bounding_boxes']) | |
53 elif dataType == 'pois': | |
54 dropTables(connection, ['gaussians2d', 'objects_pois']) | |
55 elif dataType == 'prototype': | |
56 dropTables(connection, ['prototypes']) | |
57 else: | |
58 print('Unknown data type {} to delete from database'.format(dataType)) | |
59 connection.close() | |
60 else: | |
61 print('{} does not exist'.format(filename)) | |
42 | 62 |
43 def tableExists(filename, tableName): | 63 def tableExists(filename, tableName): |
44 'indicates if the table exists in the database' | 64 'indicates if the table exists in the database' |
45 try: | 65 try: |
46 connection = sqlite3.connect(filename) | 66 connection = sqlite3.connect(filename) |
352 missingObjectNumbers.append(objNum) | 372 missingObjectNumbers.append(objNum) |
353 if objNum in objects: | 373 if objNum in objects: |
354 objects[objNum].curvilinearPositions.addPositionSYL(row[2],row[3],row[4]) | 374 objects[objNum].curvilinearPositions.addPositionSYL(row[2],row[3],row[4]) |
355 if len(missingObjectNumbers) > 0: | 375 if len(missingObjectNumbers) > 0: |
356 print('List of missing objects to attach corresponding curvilinear trajectories: {}'.format(missingObjectNumbers)) | 376 print('List of missing objects to attach corresponding curvilinear trajectories: {}'.format(missingObjectNumbers)) |
357 | |
358 | 377 |
359 def saveTrajectoriesToSqlite(outputFilename, objects, trajectoryType, withFeatures = False): | 378 def saveTrajectoriesToSqlite(outputFilename, objects, trajectoryType, withFeatures = False): |
360 '''Writes features, ie the trajectory positions (and velocities if exist) | 379 '''Writes features, ie the trajectory positions (and velocities if exist) |
361 with their instants to a specified sqlite file | 380 with their instants to a specified sqlite file |
362 Either feature positions (and velocities if they exist) | 381 Either feature positions (and velocities if they exist) |
420 connection.commit() | 439 connection.commit() |
421 except sqlite3.OperationalError as error: | 440 except sqlite3.OperationalError as error: |
422 printDBError(error) | 441 printDBError(error) |
423 connection.close() | 442 connection.close() |
424 | 443 |
425 def savePrototypesToSqlite(filename, prototypeIndices, trajectoryType, nMatchings = None, dbFilenames = None): | |
426 '''save the prototype indices | |
427 nMatchings, if not None, is a list of the number of matches | |
428 dbFilenames, if not None, is a list of the DB filenames''' | |
429 connection = sqlite3.connect(filename) | |
430 cursor = connection.cursor() | |
431 try: | |
432 cursor.execute('CREATE TABLE IF NOT EXISTS prototypes (id INTEGER, dbfilename VARCHAR, trajectory_type VARCHAR CHECK (trajectory_type IN (\"feature\", \"object\")), nMatchings INTEGER, PRIMARY KEY (id, dbfilename))') | |
433 for i, protoId in enumerate(prototypeIndices): | |
434 if nMatchings is not None: | |
435 n = nMatchings[i] | |
436 else: | |
437 n = 'NULL' | |
438 if dbFilenames is not None: | |
439 dbfn = dbFilenames[i] | |
440 else: | |
441 dbfn = filename | |
442 cursor.execute('INSERT INTO prototypes (id, dbfilename, trajectory_type, nMatchings) VALUES ({},\"{}\",\"{}\",{})'.format(protoId, dbfn, trajectoryType, n)) | |
443 cursor.execute('SELECT * from sqlite_master WHERE type = \"table\" and name = \"{}\"'.format(tableNames[trajectoryType])) | |
444 if len(cursor.fetchall()) == 0: | |
445 pass # save prototype trajectory data | |
446 except sqlite3.OperationalError as error: | |
447 printDBError(error) | |
448 connection.commit() | |
449 connection.close() | |
450 | |
451 def loadPrototypesFromSqlite(filename): | |
452 'Loads prototype ids and matchings (if stored)' | |
453 connection = sqlite3.connect(filename) | |
454 cursor = connection.cursor() | |
455 prototypeIndices = [] | |
456 dbFilenames = [] | |
457 trajectoryTypes = [] | |
458 nMatchings = [] | |
459 try: | |
460 cursor.execute('SELECT * FROM prototypes') | |
461 for row in cursor: | |
462 prototypeIndices.append(row[0]) | |
463 dbFilenames.append(row[1]) | |
464 trajectoryTypes.append(row[2]) | |
465 if row[3] is not None: | |
466 nMatchings.append(row[3]) | |
467 except sqlite3.OperationalError as error: | |
468 printDBError(error) | |
469 connection.close() | |
470 if len(set(trajectoryTypes)) > 1: | |
471 print('Different types of prototypes in database ({}).'.format(set(trajectoryTypes))) | |
472 return prototypeIndices, dbFilenames, trajectoryTypes, nMatchings | |
473 | |
474 def loadBBMovingObjectsFromSqlite(filename, objectType = 'bb', objectNumbers = None, timeStep = None): | 444 def loadBBMovingObjectsFromSqlite(filename, objectType = 'bb', objectNumbers = None, timeStep = None): |
475 '''Loads bounding box moving object from an SQLite | 445 '''Loads bounding box moving object from an SQLite |
476 (format of SQLite output by the ground truth annotation tool | 446 (format of SQLite output by the ground truth annotation tool |
477 or Urban Tracker | 447 or Urban Tracker |
478 | 448 |
493 else: | 463 else: |
494 print ('Unknown type of bounding box {}'.format(objectType)) | 464 print ('Unknown type of bounding box {}'.format(objectType)) |
495 | 465 |
496 connection.close() | 466 connection.close() |
497 return objects | 467 return objects |
498 | |
499 def deleteFromSqlite(filename, dataType): | |
500 'Deletes (drops) some tables in the filename depending on type of data' | |
501 if path.isfile(filename): | |
502 connection = sqlite3.connect(filename) | |
503 if dataType == 'object': | |
504 dropTables(connection, ['objects', 'objects_features']) | |
505 elif dataType == 'interaction': | |
506 dropTables(connection, ['interactions', 'indicators']) | |
507 elif dataType == 'bb': | |
508 dropTables(connection, ['bounding_boxes']) | |
509 elif dataType == 'pois': | |
510 dropTables(connection, ['gaussians2d', 'objects_pois']) | |
511 elif dataType == 'prototype': | |
512 dropTables(connection, ['prototypes']) | |
513 else: | |
514 print('Unknown data type {} to delete from database'.format(dataType)) | |
515 connection.close() | |
516 else: | |
517 print('{} does not exist'.format(filename)) | |
518 | 468 |
519 def saveInteraction(cursor, interaction): | 469 def saveInteraction(cursor, interaction): |
520 roadUserNumbers = list(interaction.getRoadUserNumbers()) | 470 roadUserNumbers = list(interaction.getRoadUserNumbers()) |
521 cursor.execute('INSERT INTO interactions VALUES({}, {}, {}, {}, {})'.format(interaction.getNum(), roadUserNumbers[0], roadUserNumbers[1], interaction.getFirstInstant(), interaction.getLastInstant())) | 471 cursor.execute('INSERT INTO interactions VALUES({}, {}, {}, {}, {})'.format(interaction.getNum(), roadUserNumbers[0], roadUserNumbers[1], interaction.getFirstInstant(), interaction.getLastInstant())) |
522 | 472 |
622 return boundingBoxes | 572 return boundingBoxes |
623 connection.close() | 573 connection.close() |
624 return boundingBoxes | 574 return boundingBoxes |
625 | 575 |
626 ######################### | 576 ######################### |
627 # saving and loading for scene interpretation | 577 # saving and loading for scene interpretation: POIs and Prototypes |
628 ######################### | 578 ######################### |
579 | |
580 def savePrototypesToSqlite(filename, prototypeIndices, trajectoryType, objects = None, nMatchings = None, dbFilenames = None): | |
581 '''save the prototype indices | |
582 if objects is not None, the trajectories are also saved in prototype_positions and _velocities | |
583 (prototypeIndices have to be in objects) | |
584 nMatchings, if not None, is a list of the number of matches | |
585 dbFilenames, if not None, is a list of the DB filenames''' | |
586 connection = sqlite3.connect(filename) | |
587 cursor = connection.cursor() | |
588 try: | |
589 cursor.execute('CREATE TABLE IF NOT EXISTS prototypes (id INTEGER, dbfilename VARCHAR, trajectory_type VARCHAR CHECK (trajectory_type IN (\"feature\", \"object\")), nmatchings INTEGER, positions_id INTEGER, PRIMARY KEY (id, dbfilename))') | |
590 for i, protoId in enumerate(prototypeIndices): | |
591 if nMatchings is not None: | |
592 n = nMatchings[i] | |
593 else: | |
594 n = 'NULL' | |
595 if dbFilenames is not None: | |
596 dbfn = dbFilenames[i] | |
597 else: | |
598 dbfn = filename | |
599 cursor.execute('INSERT INTO prototypes (id, dbfilename, trajectory_type, nmatchings, positions_id) VALUES ({},\"{}\",\"{}\",{}, {})'.format(protoId, dbfn, trajectoryType, n, i)) | |
600 #cursor.execute('SELECT * from sqlite_master WHERE type = \"table\" and name = \"{}\"'.format(tableNames[trajectoryType])) | |
601 if objects is not None: | |
602 pass | |
603 except sqlite3.OperationalError as error: | |
604 printDBError(error) | |
605 connection.commit() | |
606 connection.close() | |
607 | |
608 def savePrototypeAssignments(filename, objects): | |
609 pass | |
610 | |
611 def loadPrototypesFromSqlite(filename): | |
612 'Loads prototype ids and matchings (if stored)' | |
613 connection = sqlite3.connect(filename) | |
614 cursor = connection.cursor() | |
615 prototypeIndices = [] | |
616 dbFilenames = [] | |
617 trajectoryTypes = [] | |
618 nMatchings = [] | |
619 try: | |
620 cursor.execute('SELECT * FROM prototypes') | |
621 for row in cursor: | |
622 prototypeIndices.append(row[0]) | |
623 dbFilenames.append(row[1]) | |
624 trajectoryTypes.append(row[2]) | |
625 if row[3] is not None: | |
626 nMatchings.append(row[3]) | |
627 except sqlite3.OperationalError as error: | |
628 printDBError(error) | |
629 connection.close() | |
630 if len(set(trajectoryTypes)) > 1: | |
631 print('Different types of prototypes in database ({}).'.format(set(trajectoryTypes))) | |
632 return prototypeIndices, dbFilenames, trajectoryTypes, nMatchings | |
629 | 633 |
630 def savePOIs(filename, gmm, gmmType, gmmId): | 634 def savePOIs(filename, gmm, gmmType, gmmId): |
631 '''Saves a Gaussian mixture model (of class sklearn.mixture.GaussianMixture) | 635 '''Saves a Gaussian mixture model (of class sklearn.mixture.GaussianMixture) |
632 gmmType is a type of GaussianMixture, learnt either from beginnings or ends of trajectories''' | 636 gmmType is a type of GaussianMixture, learnt either from beginnings or ends of trajectories''' |
633 connection = sqlite3.connect(filename) | 637 connection = sqlite3.connect(filename) |