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