Mercurial > hg > nsaunier > traffic-intelligence

comparison python/storage.py @ 619:dc2d0a0d7fe1

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
merged code from Mohamed Gomaa Mohamed for the use of points of interests in mation pattern learning and motion prediction (TRB 2015)
author Nicolas Saunier <nicolas.saunier@polymtl.ca>
date Wed, 10 Dec 2014 15:27:08 -0500
parents 5800a87f11ae 0954aaf28231
children 977407c9f815
comparison
equal deleted inserted replaced
596:04a8304e13f0 619:dc2d0a0d7fe1
62 query = "insert into positions (trajectory_id, frame_number, x_coordinate, y_coordinate) values (?,?,?,?)" 62 query = "insert into positions (trajectory_id, frame_number, x_coordinate, y_coordinate) values (?,?,?,?)"
63 cursor.execute(query,(trajectory_id,frame_number,position.x,position.y)) 63 cursor.execute(query,(trajectory_id,frame_number,position.x,position.y))
64 64
65 connection.commit() 65 connection.commit()
66 connection.close() 66 connection.close()
67 67
68 def writeFeaturesToSqlite(objects, outputFilename, trajectoryType, objectNumbers = -1): 68 def writeFeaturesToSqlite(objects, outputFilename, trajectoryType, objectNumbers = -1):
69 '''write features trajectories maintain trajectory ID,velocities dataset ''' 69 '''write features trajectories maintain trajectory ID,velocities dataset '''
70 connection = sqlite3.connect(outputFilename) 70 connection = sqlite3.connect(outputFilename)
71 cursor = connection.cursor() 71 cursor = connection.cursor()
72 72
73 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))") 73 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))")
74 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))") 74 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))")
75 75
76 if trajectoryType == 'feature': 76 if trajectoryType == 'feature':
77 if type(objectNumbers) == int and objectNumbers == -1: 77 if type(objectNumbers) == int and objectNumbers == -1:
78 for trajectory in objects: 78 for trajectory in objects:
79 trajectory_id = trajectory.num 79 trajectory_id = trajectory.num
80 frame_number = trajectory.timeInterval.first 80 frame_number = trajectory.timeInterval.first
83 cursor.execute("insert into velocities (trajectory_id, frame_number, x_coordinate, y_coordinate) values (?,?,?,?)",(trajectory_id,frame_number,velocity.x,velocity.y)) 83 cursor.execute("insert into velocities (trajectory_id, frame_number, x_coordinate, y_coordinate) values (?,?,?,?)",(trajectory_id,frame_number,velocity.x,velocity.y))
84 frame_number += 1 84 frame_number += 1
85 85
86 connection.commit() 86 connection.commit()
87 connection.close() 87 connection.close()
88 88
89 def writePrototypesToSqlite(prototypes,nMatching, outputFilename): 89 def writePrototypesToSqlite(prototypes,nMatching, outputFilename):
90 """ prototype dataset is a dictionary with keys== routes, values== prototypes Ids """ 90 """ prototype dataset is a dictionary with keys== routes, values== prototypes Ids """
91 connection = sqlite3.connect(outputFilename) 91 connection = sqlite3.connect(outputFilename)
92 cursor = connection.cursor() 92 cursor = connection.cursor()
93 93
94 cursor.execute("CREATE TABLE IF NOT EXISTS \"prototypes\"(prototype_id INTEGER,routeIDstart INTEGER,routeIDend INTEGER, nMatching INTEGER, PRIMARY KEY(prototype_id))") 94 cursor.execute("CREATE TABLE IF NOT EXISTS \"prototypes\"(prototype_id INTEGER,routeIDstart INTEGER,routeIDend INTEGER, nMatching INTEGER, PRIMARY KEY(prototype_id))")
95 95
96 for route in prototypes.keys(): 96 for route in prototypes.keys():
97 if prototypes[route]!=[]: 97 if prototypes[route]!=[]:
98 for i in prototypes[route]: 98 for i in prototypes[route]:
99 cursor.execute("insert into prototypes (prototype_id, routeIDstart,routeIDend, nMatching) values (?,?,?,?)",(i,route[0],route[1],nMatching[route][i])) 99 cursor.execute("insert into prototypes (prototype_id, routeIDstart,routeIDend, nMatching) values (?,?,?,?)",(i,route[0],route[1],nMatching[route][i]))
100 100
101 connection.commit() 101 connection.commit()
102 connection.close() 102 connection.close()
103 103
104 def loadPrototypesFromSqlite(filename): 104 def loadPrototypesFromSqlite(filename):
105 """ 105 """
106 This function loads the prototype file in the database 106 This function loads the prototype file in the database
107 It returns a dictionary for prototypes for each route and nMatching 107 It returns a dictionary for prototypes for each route and nMatching
108 """ 108 """
125 prototypes[route].append(row[0]) 125 prototypes[route].append(row[0])
126 nMatching[row[0]]=row[3] 126 nMatching[row[0]]=row[3]
127 127
128 connection.close() 128 connection.close()
129 return prototypes,nMatching 129 return prototypes,nMatching
130 130
131 def writeLabelsToSqlite(labels, outputFilename): 131 def writeLabelsToSqlite(labels, outputFilename):
132 """ labels is a dictionary with keys: routes, values: prototypes Ids 132 """ labels is a dictionary with keys: routes, values: prototypes Ids
133 """ 133 """
134 connection = sqlite3.connect(outputFilename) 134 connection = sqlite3.connect(outputFilename)
135 cursor = connection.cursor() 135 cursor = connection.cursor()
136 136
137 cursor.execute("CREATE TABLE IF NOT EXISTS \"labels\"(object_id INTEGER,routeIDstart INTEGER,routeIDend INTEGER, prototype_id INTEGER, PRIMARY KEY(object_id))") 137 cursor.execute("CREATE TABLE IF NOT EXISTS \"labels\"(object_id INTEGER,routeIDstart INTEGER,routeIDend INTEGER, prototype_id INTEGER, PRIMARY KEY(object_id))")
138 138
139 for route in labels.keys(): 139 for route in labels.keys():
140 if labels[route]!=[]: 140 if labels[route]!=[]:
141 for i in labels[route]: 141 for i in labels[route]:
142 for j in labels[route][i]: 142 for j in labels[route][i]:
143 cursor.execute("insert into labels (object_id, routeIDstart,routeIDend, prototype_id) values (?,?,?,?)",(j,route[0],route[1],i)) 143 cursor.execute("insert into labels (object_id, routeIDstart,routeIDend, prototype_id) values (?,?,?,?)",(j,route[0],route[1],i))
144 144
145 connection.commit() 145 connection.commit()
146 connection.close() 146 connection.close()
147 147
148 def loadLabelsFromSqlite(filename): 148 def loadLabelsFromSqlite(filename):
149 labels = {} 149 labels = {}
150 150
151 connection = sqlite3.connect(filename) 151 connection = sqlite3.connect(filename)
152 cursor = connection.cursor() 152 cursor = connection.cursor()
166 labels[route][p]=[] 166 labels[route][p]=[]
167 labels[route][p].append(row[0]) 167 labels[route][p].append(row[0])
168 168
169 connection.close() 169 connection.close()
170 return labels 170 return labels
171 def writeSpeedPrototypeToSqlite(prototypes,nmatching, outFilename):
172 """ to match the format of second layer prototypes"""
173 connection = sqlite3.connect(outFilename)
174 cursor = connection.cursor()
175
176 cursor.execute("CREATE TABLE IF NOT EXISTS \"speedprototypes\"(spdprototype_id INTEGER,prototype_id INTEGER,routeID_start INTEGER, routeID_end INTEGER, nMatching INTEGER, PRIMARY KEY(spdprototype_id))")
177
178 for route in prototypes.keys():
179 if prototypes[route]!={}:
180 for i in prototypes[route]:
181 if prototypes[route][i]!= []:
182 for j in prototypes[route][i]:
183 cursor.execute("insert into speedprototypes (spdprototype_id,prototype_id, routeID_start, routeID_end, nMatching) values (?,?,?,?,?)",(j,i,route[0],route[1],nmatching[j]))
184
185 connection.commit()
186 connection.close()
187
188 def loadSpeedPrototypeFromSqlite(filename):
189 """
190 This function loads the prototypes table in the database of name <filename>.
191 """
192 prototypes = {}
193 nMatching={}
194 connection = sqlite3.connect(filename)
195 cursor = connection.cursor()
196
197 try:
198 cursor.execute('SELECT * from speedprototypes order by spdprototype_id,prototype_id, routeID_start, routeID_end, nMatching')
199 except sqlite3.OperationalError as error:
200 utils.printDBError(error)
201 return []
202
203 for row in cursor:
204 route=(row[2],row[3])
205 if route not in prototypes.keys():
206 prototypes[route]={}
207 if row[1] not in prototypes[route].keys():
208 prototypes[route][row[1]]=[]
209 prototypes[route][row[1]].append(row[0])
210 nMatching[row[0]]=row[4]
211
212 connection.close()
213 return prototypes,nMatching
214
171 215
172 def writeRoutesToSqlite(Routes, outputFilename): 216 def writeRoutesToSqlite(Routes, outputFilename):
173 """ This function writes the activity path define by start and end IDs""" 217 """ This function writes the activity path define by start and end IDs"""
174 connection = sqlite3.connect(outputFilename) 218 connection = sqlite3.connect(outputFilename)
175 cursor = connection.cursor() 219 cursor = connection.cursor()
176 220
177 cursor.execute("CREATE TABLE IF NOT EXISTS \"routes\"(object_id INTEGER,routeIDstart INTEGER,routeIDend INTEGER, PRIMARY KEY(object_id))") 221 cursor.execute("CREATE TABLE IF NOT EXISTS \"routes\"(object_id INTEGER,routeIDstart INTEGER,routeIDend INTEGER, PRIMARY KEY(object_id))")
178 222
179 for route in Routes.keys(): 223 for route in Routes.keys():
180 if Routes[route]!=[]: 224 if Routes[route]!=[]:
181 for i in Routes[route]: 225 for i in Routes[route]:
182 cursor.execute("insert into routes (object_id, routeIDstart,routeIDend) values (?,?,?)",(i,route[0],route[1])) 226 cursor.execute("insert into routes (object_id, routeIDstart,routeIDend) values (?,?,?)",(i,route[0],route[1]))
183 227
184 connection.commit() 228 connection.commit()
185 connection.close() 229 connection.close()
186 230
187 def loadRoutesFromSqlite(filename): 231 def loadRoutesFromSqlite(filename):
188 Routes = {} 232 Routes = {}
189 233
190 connection = sqlite3.connect(filename) 234 connection = sqlite3.connect(filename)
191 cursor = connection.cursor() 235 cursor = connection.cursor()
201 if route not in Routes.keys(): 245 if route not in Routes.keys():
202 Routes[route]=[] 246 Routes[route]=[]
203 Routes[route].append(row[0]) 247 Routes[route].append(row[0])
204 248
205 connection.close() 249 connection.close()
206 return Routes 250 return Routes
207 251
208 def setRoutes(filename, objects): 252 def setRoutes(filename, objects):
209 connection = sqlite3.connect(filename) 253 connection = sqlite3.connect(filename)
210 cursor = connection.cursor() 254 cursor = connection.cursor()
211 for obj in objects: 255 for obj in objects:
212 cursor.execute('update objects set startRouteID = {} where object_id = {}'.format(obj.startRouteID, obj.getNum())) 256 cursor.execute('update objects set startRouteID = {} where object_id = {}'.format(obj.startRouteID, obj.getNum()))
213 cursor.execute('update objects set endRouteID = {} where object_id = {}'.format(obj.endRouteID, obj.getNum())) 257 cursor.execute('update objects set endRouteID = {} where object_id = {}'.format(obj.endRouteID, obj.getNum()))
214 connection.commit() 258 connection.commit()
215 connection.close() 259 connection.close()
216 260
217 def setRoadUserTypes(filename, objects): 261 def setRoadUserTypes(filename, objects):
218 '''Saves the user types of the objects in the sqlite database stored in filename 262 '''Saves the user types of the objects in the sqlite database stored in filename
841 for sectionName in config.sections(): 885 for sectionName in config.sections():
842 configDict[sectionName] = SceneParameters(config, sectionName) 886 configDict[sectionName] = SceneParameters(config, sectionName)
843 return configDict 887 return configDict
844 888
845 889
890
846 if __name__ == "__main__": 891 if __name__ == "__main__":
847 import doctest 892 import doctest
848 import unittest 893 import unittest
849 suite = doctest.DocFileSuite('tests/storage.txt') 894 suite = doctest.DocFileSuite('tests/storage.txt')
850 unittest.TextTestRunner().run(suite) 895 unittest.TextTestRunner().run(suite)