Mercurial Hosting > traffic-intelligence
comparison python/storage.py @ 619:dc2d0a0d7fe1
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) |