def additional_fields_to_layers(self, table, layer, layer_fields):
curr = self.project.conn.cursor()
fields = layer.dataProvider().fields()
string_fields = []
curr.execute(f'PRAGMA table_info({table});')
field_names = curr.fetchall()
existing_fields = [f[1].lower() for f in field_names]
for f in set(layer_fields.keys()):
if f in existing_fields:
continue
field = fields[layer_fields[f]]
field_length = field.length()
if not field.isNumeric():
field_type = "char"
string_fields.append(f)
else:
if "Int" in field.typeName():
field_type = "INTEGER"
else:
field_type = "REAL"
try:
sql = "alter table " + table + " add column " + f + " " + field_type + "(" + str(
field_length) + ")"
curr.execute(sql)
self.project.conn.commit()
except:
logger.error(sql)
self.report.append("field " + str(f) + " could not be added")
curr.close()
return string_fields
def remove_triggers(conn: Connection) -> None:
curr = conn.cursor()
spec_folder = join(dirname(realpath(__file__)), "database_specification", 'triggers')
with open(join(spec_folder, 'triggers_list.txt'), 'r') as file_list:
all_trigger_sets = file_list.readlines()
create_drop_regex = re.compile(r'create\s+trigger\s+(\w+)', flags=re.I)
for table in all_trigger_sets:
qry_file = join(spec_folder, f'{table.rstrip()}.sql')
with open(qry_file, "r") as sql_file:
query_list = sql_file.read()
# Running one query/command at a time helps debugging in the case a particular command fails
for cmd in query_list.split("--#"):
for qry in cmd.split("\n"):
if qry[:2] == '--':
continue
while ' ' in qry:
qry = qry.replace(' ', ' ')
m = re.search(create_drop_regex, qry)
if m:
try:
curr.execute(f'drop trigger if exists {m.group(1).lower()}')
except Exception as e:
logger.error(f'Failed removing triggers table - > {e.args}')
logger.error(f'Point of failure - > {qry}')
conn.commit()
def run(self):
self.progressbar.setVisible(True)
self.chb_empty_as_zero.setVisible(False)
try:
for out_name in self.job_queue.keys():
self.outfile = out_name
self.worker_thread = self.job_queue[self.outfile]
self.run_thread()
except Exception as e:
logger.error(e.args)
def delete(self, mode_id: str) -> None:
"""Removes the mode with **mode_id** from the project"""
try:
self.curr.execute(f'delete from modes where mode_id="{mode_id}"')
self.conn.commit()
except IntegrityError as e:
logger.error(f'Failed to remove mode {mode_id}. {e.args}')
raise e
logger.warning(
f'Mode {mode_id} was successfully removed from the database')
self.__update_list_of_modes()
def delete(self, link_type_id: str) -> None:
"""Removes the link_type with **link_type_id** from the project"""
try:
lt = self.__items[link_type_id] # type: LinkType
lt.delete()
del self.__items[link_type_id]
self.conn.commit()
except IntegrityError as e:
logger.error(f'Failed to remove link_type {link_type_id}. {e.args}')
raise e
logger.warning(f'Link type {link_type_id} was successfully removed from the project database')
def __add_trigger_from_file(self, qry_file: str):
curr = self.conn.cursor()
sql_file = open(qry_file, "r")
query_list = sql_file.read()
sql_file.close()
# Run one query/command at a time
for cmd in query_list.split("#"):
try:
curr.execute(cmd)
except Exception as e:
msg = f"Error creating trigger: {e.args}"
logger.error(msg)
logger.info(cmd)
self.conn.commit()
def run_queries_from_sql_file(conn: Connection, qry_file: str) -> None:
curr = conn.cursor()
with open(qry_file, "r") as sql_file:
query_list = sql_file.read()
# Running one query/command at a time helps debugging in the case a particular command fails
for cmd in query_list.split("--#"):
try:
curr.execute(cmd)
except Exception as e:
msg = f"Error running SQL command: {e.args}"
logger.error(msg)
logger.info(cmd)
raise e
def import_nodes(self, nodes_to_add, node_ids):
table = "nodes"
fields = self.get_node_fields()
field_names = ",".join(fields)
field_names = ",".join(
['"{}"'.format(x) for x in field_names.split(",")])
logger.info("Adding network nodes")
self.__emit_all(["text", "Adding network nodes"])
self.__emit_all(["maxValue", len(nodes_to_add)])
vars = {}
for counter, osm_id in enumerate(nodes_to_add):
self.__emit_all(["Value", counter])
vars["node_id"] = node_ids[osm_id]
vars["osm_id"] = osm_id
vars["is_centroid"] = 0
geometry = "POINT({} {})".format(self.nodes[osm_id]["lon"],
self.nodes[osm_id]["lat"])
attributes = [vars.get(x) for x in fields]
attributes = ", ".join([str(x) for x in attributes])
sql = self.insert_qry.format(table, field_names, attributes,
geometry)
sql = sql.replace("None", "null")
try:
self.curr.execute(sql)
except Exception as e:
data = list(vars.values())
logger.error("error when inserting NODE {}. Error {}".format(
data, e.args))
logger.error(sql)
self.conn.commit()
self.curr.close()
self.__emit_all(["finished_threaded_procedure", 0])
def execute(self):
for c in self.traffic_classes:
c.graph.set_graph(self.time_field)
logger.info("{} Assignment STATS".format(self.algorithm))
logger.info("Iteration, RelativeGap, stepsize")
for self.iter in range(1, self.max_iter + 1):
if pyqt:
self.equilibration.emit(['rgap', self.rgap])
self.equilibration.emit(['iterations', self.iter])
flows = []
aon_flows = []
for c in self.traffic_classes:
aon = allOrNothing(c.matrix, c.graph, c._aon_results)
if pyqt:
aon.assignment.connect(self.signal_handler)
aon.execute()
c._aon_results.total_flows()
aon_flows.append(c._aon_results.total_link_loads * c.pce)
self.aon_total_flow = np.sum(aon_flows, axis=0)
if self.iter == 1:
for c in self.traffic_classes:
copy_two_dimensions(c.results.link_loads,
c._aon_results.link_loads, self.cores)
c.results.total_flows()
copy_one_dimension(c.results.total_link_loads,
c._aon_results.total_link_loads,
self.cores)
if c.results.num_skims > 0:
copy_three_dimensions(c.results.skims.matrix_view,
c._aon_results.skims.matrix_view,
self.cores)
flows.append(c.results.total_link_loads * c.pce)
else:
self.__calculate_step_direction()
self.calculate_stepsize()
for c in self.traffic_classes:
stp_dir = self.step_direction[c.mode]
cls_res = c.results
linear_combination(cls_res.link_loads, stp_dir.link_loads,
cls_res.link_loads, self.stepsize,
self.cores)
if cls_res.num_skims > 0:
linear_combination_skims(cls_res.skims.matrix_view,
stp_dir.skims.matrix_view,
cls_res.skims.matrix_view,
self.stepsize, self.cores)
cls_res.total_flows()
flows.append(cls_res.total_link_loads * c.pce)
self.fw_total_flow = np.sum(flows, axis=0)
# Check convergence
# This needs ot be done with the current costs, and not the future ones
if self.iter > 1:
if self.check_convergence():
if self.steps_below >= self.steps_below_needed_to_terminate:
break
else:
self.steps_below += 1
self.vdf.apply_vdf(self.congested_time, self.fw_total_flow,
self.capacity, self.free_flow_tt,
*self.vdf_parameters)
for c in self.traffic_classes:
c.graph.cost = self.congested_time
if self.time_field in c.graph.skim_fields:
idx = c.graph.skim_fields.index(self.time_field)
c.graph.skims[:, idx] = self.congested_time[:]
c._aon_results.reset()
logger.info("{},{},{}".format(self.iter, self.rgap, self.stepsize))
if self.rgap > self.rgap_target:
logger.error("Desired RGap of {} was NOT reached".format(
self.rgap_target))
logger.info(
f"{self.algorithm} Assignment finished. {self.iter} iterations and {self.rgap} final gap"
)
if pyqt:
self.equilibration.emit(['rgap', self.rgap])
self.equilibration.emit(['iterations', self.iter])
self.equilibration.emit(['finished_threaded_procedure'])
def importing_links(self, node_count):
node_ids = {}
vars = {}
vars["link_id"] = 1
table = "links"
fields = self.get_link_fields()
self.__update_table_structure()
field_names = ",".join(fields)
logger.info("Adding network links")
self.__emit_all(["text", "Adding network links"])
L = len(list(self.links.keys()))
self.__emit_all(["maxValue", L])
counter = 0
mode_codes, not_found_tags = self.modes_per_link_type()
owf, twf = self.field_osm_source()
for osm_id, link in self.links.items():
self.__emit_all(["Value", counter])
counter += 1
if counter % 1000 == 0:
logger.info(
f'Inserting segments from {counter:,} out of {L:,} OSM link objects'
)
vars["osm_id"] = osm_id
vars['link_type'] = 'default'
linknodes = link["nodes"]
linktags = link["tags"]
indices = np.searchsorted(node_count[:, 0], linknodes)
nodedegree = node_count[indices, 1]
# Makes sure that beginning and end are end nodes for a link
nodedegree[0] = 2
nodedegree[-1] = 2
intersections = np.where(nodedegree > 1)[0]
segments = intersections.shape[0] - 1
# Attributes that are common to all individual links/segments
vars["direction"] = (linktags.get("oneway") == "yes") * 1
for k, v in owf.items():
vars[k] = linktags.get(v)
for k, v in twf.items():
val = linktags.get(v["osm_source"])
if vars["direction"] == 0:
for d1, d2 in [("ab", "forward"), ("ba", "backward")]:
vars[f"{k}_{d1}"] = self.__get_link_property(
d2, val, linktags, v)
elif vars["direction"] == -1:
vars[f"{k}_ba"] = linktags.get(
f"{v['osm_source']}:{'backward'}", val)
elif vars["direction"] == 1:
vars[f"{k}_ab"] = linktags.get(
f"{v['osm_source']}:{'forward'}", val)
vars["modes"] = mode_codes.get(linktags.get("highway"),
not_found_tags)
vars['link_type'] = self.__link_type_quick_reference.get(
vars['link_type'].lower(),
self.__repair_link_type(vars['link_type']))
if len(vars["modes"]) > 0:
for i in range(segments):
attributes = self.__build_link_data(
vars, intersections, i, linknodes, node_ids, fields)
sql = self.insert_qry.format(
table, field_names,
','.join(['?'] * (len(attributes) - 1)))
try:
self.curr.execute(sql, attributes)
self.curr.execute(
'Select a_node, b_node from links where link_id=?',
[vars["link_id"]])
a, b = self.curr.fetchone()
self.curr.executemany(
'update nodes set osm_id=? where node_id=?',
[[linknodes[intersections[i]], a],
[linknodes[intersections[i + 1]], b]])
except Exception as e:
data = list(vars.values())
logger.error(
"error when inserting link {}. Error {}".format(
data, e.args))
logger.error(sql)
vars["link_id"] += 1
self.conn.commit()
self.__emit_all(
["text", f"{counter:,} of {L:,} super links added"])
self.links[osm_id] = []
self.conn.commit()
self.curr.close()
def execute(self):
for c in self.traffic_classes:
c.graph.set_graph(self.time_field)
logger.info(f"{self.algorithm} Assignment STATS")
logger.info("Iteration, RelativeGap, stepsize")
for self.iter in range(1, self.max_iter + 1):
self.iteration_issue = []
if pyqt:
self.equilibration.emit(["rgap", self.rgap])
self.equilibration.emit(["iterations", self.iter])
aon_flows = []
for c in self.traffic_classes:
aon = allOrNothing(c.matrix, c.graph, c._aon_results)
if pyqt:
aon.assignment.connect(self.signal_handler)
aon.execute()
aon_flows.append(c._aon_results.total_link_loads * c.pce)
self.aon_total_flow = np.sum(aon_flows, axis=0)
flows = []
if self.iter == 1:
for c in self.traffic_classes:
copy_two_dimensions(c.results.link_loads,
c._aon_results.link_loads, self.cores)
copy_one_dimension(c.results.total_link_loads,
c._aon_results.total_link_loads,
self.cores)
if c.results.num_skims > 0:
copy_three_dimensions(c.results.skims.matrix_view,
c._aon_results.skims.matrix_view,
self.cores)
flows.append(c.results.total_link_loads * c.pce)
else:
self.__calculate_step_direction()
self.calculate_stepsize()
for c in self.traffic_classes:
stp_dir = self.step_direction[c.mode]
cls_res = c.results
linear_combination(cls_res.link_loads, stp_dir.link_loads,
cls_res.link_loads, self.stepsize,
self.cores)
if cls_res.num_skims > 0:
linear_combination_skims(
cls_res.skims.matrix_view,
stp_dir.skims.matrix_view,
cls_res.skims.matrix_view,
self.stepsize,
self.cores,
)
cls_res.total_flows()
flows.append(cls_res.total_link_loads * c.pce)
self.fw_total_flow = np.sum(flows, axis=0)
# Check convergence
# This needs to be done with the current costs, and not the future ones
converged = False
if self.iter > 1:
converged = self.check_convergence()
self.convergence_report["iteration"].append(self.iter)
self.convergence_report["rgap"].append(self.rgap)
self.convergence_report["warnings"].append("; ".join(
self.iteration_issue))
self.convergence_report["alpha"].append(self.stepsize)
if self.algorithm == "bfw":
self.convergence_report["beta0"].append(self.betas[0])
self.convergence_report["beta1"].append(self.betas[1])
self.convergence_report["beta2"].append(self.betas[2])
logger.info(f"{self.iter},{self.rgap},{self.stepsize}")
if converged:
if self.steps_below >= self.steps_below_needed_to_terminate:
break
else:
self.steps_below += 1
self.vdf.apply_vdf(
self.congested_time,
self.fw_total_flow,
self.capacity,
self.free_flow_tt,
*self.vdf_parameters,
self.cores,
)
for c in self.traffic_classes:
aggregate_link_costs(self.congested_time, c.graph.compact_cost,
c.results.crosswalk)
if self.time_field in c.graph.skim_fields:
idx = c.graph.skim_fields.index(self.time_field)
c.graph.skims[:, idx] = self.congested_time[:]
c._aon_results.reset()
if self.rgap > self.rgap_target:
logger.error(f"Desired RGap of {self.rgap_target} was NOT reached")
logger.info(
f"{self.algorithm} Assignment finished. {self.iter} iterations and {self.rgap} final gap"
)
if pyqt:
self.equilibration.emit(["rgap", self.rgap])
self.equilibration.emit(["iterations", self.iter])
self.equilibration.emit(["finished_threaded_procedure"])
def execute(self):
# We build the fixed cost field
for c in self.traffic_classes:
if c.fixed_cost_field:
# divide fixed cost by volume-dependent prefactor (vot) such that we don't have to do it for
# each occurence in the objective funtion. TODO: Need to think about cost skims here, we do
# not want this there I think
c.fixed_cost[c.graph.graph.__supernet_id__] = (
c.graph.graph[c.fixed_cost_field].values[:] *
c.fc_multiplier / c.vot)
c.fixed_cost[np.isnan(c.fixed_cost)] = 0
# TODO: Review how to eliminate this. It looks unnecessary
# Just need to create some arrays for cost
for c in self.traffic_classes:
c.graph.set_graph(self.time_field)
logger.info(f"{self.algorithm} Assignment STATS")
logger.info("Iteration, RelativeGap, stepsize")
for self.iter in range(1, self.max_iter + 1):
self.iteration_issue = []
if pyqt:
self.equilibration.emit(["rgap", self.rgap])
self.equilibration.emit(["iterations", self.iter])
aon_flows = []
self.__maybe_create_path_file_directories()
for c in self.traffic_classes: # type: TrafficClass
# cost = c.fixed_cost / c.vot + self.congested_time # now only once
cost = c.fixed_cost + self.congested_time
aggregate_link_costs(cost, c.graph.compact_cost,
c.results.crosswalk)
aon = allOrNothing(c.matrix, c.graph, c._aon_results)
if pyqt:
aon.assignment.connect(self.signal_handler)
aon.execute()
c._aon_results.link_loads *= c.pce
c._aon_results.total_flows()
aon_flows.append(c._aon_results.total_link_loads)
self.aon_total_flow = np.sum(aon_flows, axis=0)
flows = []
if self.iter == 1:
for c in self.traffic_classes:
copy_two_dimensions(c.results.link_loads,
c._aon_results.link_loads, self.cores)
c.results.total_flows()
if c.results.num_skims > 0:
copy_three_dimensions(c.results.skims.matrix_view,
c._aon_results.skims.matrix_view,
self.cores)
flows.append(c.results.total_link_loads)
if self.algorithm == "all-or-nothing":
break
else:
self.__calculate_step_direction()
self.calculate_stepsize()
for c in self.traffic_classes:
stp_dir = self.step_direction[c.__id__]
cls_res = c.results
linear_combination(cls_res.link_loads, stp_dir.link_loads,
cls_res.link_loads, self.stepsize,
self.cores)
if cls_res.num_skims > 0:
linear_combination_skims(
cls_res.skims.matrix_view,
stp_dir.skims.matrix_view,
cls_res.skims.matrix_view,
self.stepsize,
self.cores,
)
cls_res.total_flows()
flows.append(cls_res.total_link_loads)
self.fw_total_flow = np.sum(flows, axis=0)
# Check convergence
# This needs to be done with the current costs, and not the future ones
converged = self.check_convergence() if self.iter > 1 else False
self.vdf.apply_vdf(
self.congested_time,
self.fw_total_flow,
self.capacity,
self.free_flow_tt,
*self.vdf_parameters,
self.cores,
)
self.convergence_report["iteration"].append(self.iter)
self.convergence_report["rgap"].append(self.rgap)
self.convergence_report["warnings"].append("; ".join(
self.iteration_issue))
self.convergence_report["alpha"].append(self.stepsize)
if self.algorithm in ["cfw", "bfw"]:
self.convergence_report["beta0"].append(self.betas[0])
self.convergence_report["beta1"].append(self.betas[1])
self.convergence_report["beta2"].append(self.betas[2])
for c in self.traffic_classes:
c._aon_results.reset()
if self.time_field not in c.graph.skim_fields:
continue
idx = c.graph.skim_fields.index(self.time_field)
c.graph.skims[:, idx] = self.congested_time[:]
logger.info(f"{self.iter},{self.rgap},{self.stepsize}")
if converged:
self.steps_below += 1
if self.steps_below >= self.steps_below_needed_to_terminate:
break
else:
self.steps_below = 0
for c in self.traffic_classes:
c.results.link_loads /= c.pce
c.results.total_flows()
# TODO (Jan 18/4/21): Do we want to blob store path files (by iteration, class, origin, destination) in sqlite?
# or do we just use one big hdf5 file?
if (self.rgap > self.rgap_target) and (self.algorithm !=
"all-or-nothing"):
logger.error(f"Desired RGap of {self.rgap_target} was NOT reached")
logger.info(
f"{self.algorithm} Assignment finished. {self.iter} iterations and {self.rgap} final gap"
)
if pyqt:
self.equilibration.emit(["rgap", self.rgap])
self.equilibration.emit(["iterations", self.iter])
self.equilibration.emit(["finished_threaded_procedure"])
def execute(self):
for c in self.traffic_classes:
c.graph.set_graph(self.time_field)
logger.info("{} Assignment STATS".format(self.algorithm))
logger.info("Iteration, RelativeGap, stepsize")
for self.iter in range(1, self.max_iter + 1):
flows = []
aon_flows = []
for c in self.traffic_classes:
aon = allOrNothing(c.matrix, c.graph, c._aon_results)
aon.execute()
c._aon_results.total_flows()
aon_flows.append(c._aon_results.total_link_loads * c.pce)
self.aon_total_flow = np.sum(aon_flows, axis=0)
if self.iter == 1:
for c in self.traffic_classes:
copy_two_dimensions(c.results.link_loads, c._aon_results.link_loads, self.cores)
c.results.total_flows()
copy_one_dimension(c.results.total_link_loads, c._aon_results.total_link_loads, self.cores)
if c.results.num_skims > 0:
copy_three_dimensions(c.results.skims.matrix_view, c._aon_results.skims.matrix_view, self.cores)
flows.append(c.results.total_link_loads * c.pce)
else:
self.__calculate_step_direction()
self.calculate_stepsize()
for c in self.traffic_classes:
stp_dir = self.step_direction[c.mode]
cls_res = c.results
linear_combination(cls_res.link_loads, stp_dir.link_loads, cls_res.link_loads, self.stepsize,
self.cores)
# TODO: We need to compute the step direction for skims as well.
# It is probably a matter of transforming the step_direction values from numpy arrays to
# full AssignmentResults() ones, and cleaning the stuff we don't need
if cls_res.num_skims > 0:
linear_combination_skims(cls_res.skims.matrix_view,
stp_dir.skims.matrix_view,
cls_res.skims.matrix_view,
self.stepsize,
self.cores)
cls_res.total_flows()
flows.append(cls_res.total_link_loads * c.pce)
self.fw_total_flow = np.sum(flows, axis=0)
# Check convergence
# This needs ot be done with the current costs, and not the future ones
if self.iter > 1:
if self.check_convergence():
if self.steps_below >= self.steps_below_needed_to_terminate:
break
else:
self.steps_below += 1
self.vdf.apply_vdf(
self.congested_time, self.fw_total_flow, self.capacity, self.free_flow_tt, *self.vdf_parameters
)
for c in self.traffic_classes:
c.graph.cost = self.congested_time
c._aon_results.reset()
logger.info("{},{},{}".format(self.iter, self.rgap, self.stepsize))
if self.rgap > self.rgap_target:
logger.error("Desired RGap of {} was NOT reached".format(self.rgap_target))
logger.info(
"{} Assignment finished. {} iterations and {} final gap".format(self.algorithm, self.iter, self.rgap)
)
def importing_links(self, node_count):
node_ids = {}
vars = {}
vars["link_id"] = 1
table = "links"
fields = self.get_link_fields()
field_names = ",".join(fields)
fn = ",".join(['"{}"'.format(x) for x in field_names.split(",")])
logger.info("Adding network links")
self.__emit_all(["text", "Adding network links"])
L = len(list(self.links.keys()))
self.__emit_all(["maxValue", L])
nodes_to_add = set()
counter = 0
mode_codes, not_found_tags = self.modes_per_link_type()
for osm_id, link in self.links.items():
self.__emit_all(["Value", counter])
counter += 1
vars["osm_id"] = osm_id
linknodes = link["nodes"]
linktags = link["tags"]
indices = np.searchsorted(node_count[:, 0], linknodes)
nodedegree = node_count[indices, 1]
# Makes sure that beginning and end are end nodes for a link
nodedegree[0] = 2
nodedegree[-1] = 2
intersections = np.where(nodedegree > 1)[0]
segments = intersections.shape[0] - 1
owf, twf = self.field_osm_source()
# Attributes that are common to all individual links/segments
vars["direction"] = (linktags.get("oneway") == "yes") * 1
for k, v in owf.items():
attr_value = linktags.get(v)
if isinstance(attr_value, str):
attr_value = attr_value.replace('"', "'")
attr_value = '"{}"'.format(attr_value)
vars[k] = attr_value
for k, v in twf.items():
val = linktags.get(v["osm_source"])
for d1, d2 in [("ab", "forward"), ("ba", "backward")]:
vars["{}_{}".format(k, d1)] = self.__get_link_property(
d2, val, linktags, v)
vars["modes"] = mode_codes.get(linktags.get("highway"),
not_found_tags)
if len(vars["modes"]) > 0:
for i in range(segments):
geometry, attributes = self.__build_link_data(
vars, intersections, i, linknodes, node_ids, fields)
sql = self.insert_qry.format(table, fn, attributes,
geometry)
sql = sql.replace("None", "null")
try:
self.curr.execute(sql)
nodes_to_add.update([
linknodes[intersections[i]],
linknodes[intersections[i + 1]]
])
except Exception as e:
data = list(vars.values())
logger.error(
"error when inserting link {}. Error {}".format(
data, e.args))
logger.error(sql)
vars["link_id"] += 1
self.__emit_all(
["text", f"{counter:,} of {L:,} super links added"])
return nodes_to_add, node_ids