Mercurial Hosting > traffic-intelligence

comparison python/storage.py @ 341:2f39c4ed0b62

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
first version of indicator saving to sqlite
author Nicolas Saunier <nicolas.saunier@polymtl.ca>
date Thu, 20 Jun 2013 14:31:24 -0400
parents 1046b7346886
children 4d69486869a5
comparison
equal deleted inserted replaced
340:1046b7346886 341:2f39c4ed0b62
1 #! /usr/bin/env python 1 #! /usr/bin/env python
2 # -*- coding: utf-8 -*- 2 # -*- coding: utf-8 -*-
3 '''Various utilities to save and load data''' 3 '''Various utilities to save and load data'''
4 4
5 import utils 5 import utils, moving, events
6 import moving
7 6
8 __metaclass__ = type 7 __metaclass__ = type
9 8
10 9
11 ngsimUserTypes = {'twowheels':1, 10 ngsimUserTypes = {'twowheels':1,
14 13
15 ######################### 14 #########################
16 # Sqlite 15 # Sqlite
17 ######################### 16 #########################
18 17
19 def writeTrajectoriesToSqlite(objects, outFilename, trajectoryType, objectNumbers = -1): 18 def saveTrajectoriesToSqlite(objects, outFilename, trajectoryType, objectNumbers = -1):
20 """ 19 """
21 This function writers trajectories to a specified sqlite file 20 This function writers trajectories to a specified sqlite file
22 @param[in] objects -> a list of trajectories 21 @param[in] objects -> a list of trajectories
23 @param[in] trajectoryType - 22 @param[in] trajectoryType -
24 @param[out] outFile -> the .sqlite file containting the written objects 23 @param[out] outFile -> the .sqlite file containting the written objects
27 26
28 import sqlite3 27 import sqlite3
29 connection = sqlite3.connect(outFilename) 28 connection = sqlite3.connect(outFilename)
30 cursor = connection.cursor() 29 cursor = connection.cursor()
31 30
32 schema = "CREATE TABLE \"positions\"(trajectory_id INTEGER,frame_number INTEGER, x_coordinate REAL, y_coordinate REAL, PRIMARY KEY(trajectory_id, frame_number))" 31 schema = "CREATE TABLE IF NOT EXISTS \"positions\"(trajectory_id INTEGER,frame_number INTEGER, x_coordinate REAL, y_coordinate REAL, PRIMARY KEY(trajectory_id, frame_number))"
33 cursor.execute(schema) 32 cursor.execute(schema)
34 33
35 trajectory_id = 0 34 trajectory_id = 0
36 frame_number = 0 35 frame_number = 0
37 if trajectoryType == 'feature': 36 if trajectoryType == 'feature':
203 202
204 def deleteIndicators(filename): 203 def deleteIndicators(filename):
205 'Deletes all indicator data in db' 204 'Deletes all indicator data in db'
206 pass 205 pass
207 206
207 def createInteractionTable(cursor):
208 cursor.execute('CREATE TABLE IF NOT EXISTS interactions (id INTEGER PRIMARY KEY, object_id1 INTEGER, object_id2 INTEGER, FOREIGN KEY(object_id1) REFERENCES objects(id), FOREIGN KEY(object_id2) REFERENCES objects(id))')
209
210 def createIndicatorTables(cursor):
211 # 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))')
212 # 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))')
213 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))')
214
215 def saveInteraction(cursor, interaction):
216 roadUserNumbers = list(interaction.getRoadUserNumbers())
217 cursor.execute('INSERT INTO interactions VALUES({}, {}, {})'.format(interaction.getNum(), roadUserNumbers[0], roadUserNumbers[1]))
218
208 def saveInteractions(filename, interactions): 219 def saveInteractions(filename, interactions):
209 'Saves the interactions in the table' 220 'Saves the interactions in the table'
210 import sqlite3 221 import sqlite3
211 connection = sqlite3.connect(filename) 222 connection = sqlite3.connect(filename)
212 cursor = connection.cursor() 223 cursor = connection.cursor()
213 cursor.execute('CREATE TABLE interactions IF NOT EXISTS (id INTEGER PRIMARY KEY, object_id1 INTEGER, object_id2 INTEGER, FOREIGN KEY(object_id1) REFERENCES objects(id), FOREIGN KEY(object_id2) REFERENCES objects(id))') 224 createInteractionTable(cursor)
214 # get the highest interaction id 225 for inter in interactions:
215 for i in interactions: 226 saveInteraction(cursor, inter)
216 cursor.execute('INSERT INTO interactions VALUES({})'.format(i.getNum())) # todo getRoadUserNumbers() 227 connection.commit()
217 # CREATE TABLE IF NOT EXISTS interactions (id INTEGER PRIMARY KEY, object_id1 INTEGER, object_id2 INTEGER, FOREIGN KEY(object_id1) REFERENCES objects(id), FOREIGN KEY(object_id2) REFERENCES objects(id)); 228 connection.close()
218 # CREATE TABLE IF NOT EXISTS indicators (id INTEGER PRIMARY KEY, interaction_id INTEGER, indicator_type INTEGER, FOREIGN KEY(interaction_id) REFERENCES interactions(id)) 229
219 # 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)) 230 def saveIndicator(cursor, interactionNum, indicator):
220 231 for instant in indicator.getTimeInterval():
221 connection.close() 232 if indicator[instant]:
222 233 cursor.execute('INSERT INTO indicators VALUES({}, {}, {}, {})'.format(interactionNum, events.Interaction.indicatorNameToIndices[indicator.getName()], instant, indicator[instant]))
223 def saveIndicators(filename, indicators): 234
235 def saveIndicators(filename, interactions, indicatorNames):
224 'Saves the indicator values in the table' 236 'Saves the indicator values in the table'
225 import sqlite3 237 import sqlite3
226 connection = sqlite3.connect(filename) 238 connection = sqlite3.connect(filename)
227 239 cursor = connection.cursor()
228 240 createInteractionTable(cursor)
229 connection.close() 241 createIndicatorTables(cursor)
242 for inter in interactions:
243 saveInteraction(cursor, inter)
244 for indicatorName in indicatorNames:
245 indicator = inter.getIndicator(indicatorName)
246 if indicator != None:
247 saveIndicator(cursor, inter.getNum(), indicator)
248 connection.commit()
249 connection.close()
250
230 251
231 ######################### 252 #########################
232 # txt files 253 # txt files
233 ######################### 254 #########################
234 255