def _send_to_plpy(self, level, text, exception=None): # exception might also be a tuple generated by sys.exc_info if exception: if isinstance(exception, tuple) and len(exception) > 1: exception = exception[1] exception_message = '. Exception: {}'.format(exception) else: exception_message = '' # Adding trace breaks tests # trace = traceback.format_exc(15) # message = '{}{}. Trace: {}'.format(text, exception_message, trace) message = '{}{}'.format(text, exception_message) if self._check_plpy(): if level == 'debug': plpy.debug(message) elif level == 'info': plpy.info(message) elif level == 'warning': plpy.warning(message) elif level == 'error': # Plpy.error and fatal raises exceptions and we only want to # log an error, exceptions should be raise explicitly plpy.warning(message)
def mount_csv(in_file_name, out_table_name, sep=";", header=True, column_types = None, **kwargs): import plpy if sep != None: with open(in_file_name, "r") as fp: columns = fp.readline().replace("\n", "").replace("\r", "").split(sep) else: columns = ["value"] if header == False: columns = ["COL%d" %d for d in range(len(columns))] elif type(header) == list: columns = header header = False columns = map(lambda x: x.lower(), columns) if not column_types: column_types = ["text"] * len(columns) try: plpy.execute("""CREATE EXTENSION IF NOT EXISTS file_fdw;CREATE SERVER csv_server FOREIGN DATA WRAPPER file_fdw;""") except: pass if out_table_name.find(".") > 0: plpy.execute("CREATE SCHEMA IF NOT EXISTS %s;" %out_table_name.split(".")[0]) plpy.execute("DROP FOREIGN TABLE IF EXISTS %s;" %out_table_name) cmd = """CREATE FOREIGN TABLE %s (%s) SERVER csv_server OPTIONS (filename '%s', format 'csv', \ header '%s' %s);""" %(out_table_name, ",".join(["%s %s" %(columns[c], column_types[c]) for c in range(len(columns))]), in_file_name, "true" if header else "false", ", delimiter '%s'" %sep if sep != None else "") ret = plpy.execute(cmd) plpy.info(cmd) return ret
def hba_heuristic(source, target, tablename='vertex', col_geom='geom', col_id='id'): if heuristic_plan == -1: global heuristic_plan heuristic_plan = plpy.prepare('select st_distance(a.' + col_geom + ', b.' + col_geom + ') as cost from ' + tablename + ' as a, ' + tablename + ' as b where a.' + col_id + ' = $2 and b.' + col_id + ' = $3', ['text', 'integer', 'integer', 'text', 'text']) try: return plpy.execute(heuristic_plan, [col_geom, source, target, tablename, col_id], 1)[0]['cost'] except: plpy.info("No heuristic distance. This is a bug, probably.") return float('inf')
def hba_process_y(adj, p, cat, d, ol, cl, x, target, vertex_tablename, col_vertex_geom, col_edge, already_processed=[], distance='Infinity'): # plpy.info("hba_process_y", cat[0]) cat_array = cat cat_array[0] = cat_array[0] + 1 categories = [hba_process_vertex(y, p, cat_array, d, ol, cl, x, target, vertex_tablename, col_vertex_geom, col_edge, already_processed, distance) for y in adj] cat = cat_array # plpy.info("Salimos del hba_process_y con ", cat[0]) if len(already_processed) == 0 and len(adj) > 0: plpy.info("Bajando nivel desde ", cat[0]) cat[0] = 10 hba_process_y(adj, p, cat, d, ol, cl, x, target, vertex_tablename, col_vertex_geom, col_edge, already_processed, distance)
def _send_to_plpy(self, level, text): if self._check_plpy(): if level == 'debug': plpy.debug(text) elif level == 'info': plpy.info(text) elif level == 'warning': plpy.warning(text) elif level == 'error': # Plpy.error and fatal raises exceptions and we only want to # log an error, exceptions should be raise explicitly plpy.warning(text)
def hba_buildPath(ps, pt, m, source, target, olf, olb): #Return variable res = [] current = m if m == 0: m = hba_bestNext(olf) if m == -1: m = hba_bestNext(olb) plpy.info(m) try: while current != source: # plpy.info("Current-s: ", ps[current][0]) current = int(ps[current][0]) # res.append([int(ps[current][1]['id']), ps[current][1]['geom'], "name", ps[current][1]['cost']]) res.append([ int(ps[current][1]['id']), ps[current][1]['geom'], ps[current][1]['name'], ps[current][1]['cost'] ]) except: pass res.reverse() current = m try: while current != target: current = int(pt[current][0]) # res.append([int(pt[current][1]['id']), pt[current][1]['geom'], "name", pt[current][1]['cost']]) res.append([ int(pt[current][1]['id']), pt[current][1]['geom'], pt[current][1]['name'], pt[current][1]['cost'] ]) except: pass plpy.info("Path: ", len(res)) #, res) return res
def hba_heuristic(source, target, tablename='vertex', col_geom='geom', col_id='id'): if heuristic_plan == -1: global heuristic_plan heuristic_plan = plpy.prepare( 'select st_distance(a.' + col_geom + ', b.' + col_geom + ') as cost from ' + tablename + ' as a, ' + tablename + ' as b where a.' + col_id + ' = $2 and b.' + col_id + ' = $3', ['text', 'integer', 'integer', 'text', 'text']) try: return plpy.execute(heuristic_plan, [col_geom, source, target, tablename, col_id], 1)[0]['cost'] except: plpy.info("No heuristic distance. This is a bug, probably.") return float('inf')
def hba_buildPath(ps, pt, m, source, target, olf, olb): #Return variable res = [] current = m if m == 0: m = hba_bestNext(olf) if m == -1: m = hba_bestNext(olb) # plpy.info(m) try: while current != source: # plpy.info("Current-s: ", ps[current][0]) current = int(ps[current][0]) # res.append([int(ps[current][1]['id']), ps[current][1]['geom'], "name", ps[current][1]['cost']]) res.append([int(ps[current][1]['id']), ps[current][1]['geom'], ps[current][1]['name'], ps[current][1]['cost']]) except: pass res.reverse() # plpy.info("weee") current=m try: while current != target: # plpy.info("Current-t: ", pt[current][0]) current = int(pt[current][0]) # res.append([int(pt[current][1]['id']), pt[current][1]['geom'], "name", pt[current][1]['cost']]) res.append([int(pt[current][1]['id']), pt[current][1]['geom'], pt[current][1]['name'], pt[current][1]['cost']]) except: pass plpy.info("Path: ", len(res)) #, res) return res
def hba_astar(source, target, ol, cl, cl2, cat, d, p, tablename='routing', col_geom='geom', col_edge='id', col_cost='cost', col_revc='reverse_cost', col_source='source', col_target='target', vertex_tablename='vertex', col_cat='category', col_vertex_geom='geom', col_name='name', col_rule='rule'): #If we don't have open candidates... if len(ol) == 0: return 0 if len(ol) > 0: #x <- node with smallest f-value x = hba_bestNext(ol) #We move through the next best option: cl.append(x) del ol[x] #Have we just found the middle point? if (x == target or x in cl2): return x #Next candidates # If we are in the initialization buffer, use hba_adj_initialization if distance_plan == -1: global distance_plan distance_plan = plpy.prepare('\n\ ' + 'SELECT st_distance_sphere(v1.geom, v2.geom) as dist from vertex v1, vertex v2 where v1.id = $1 and v2.id = $2', ['Integer', 'Integer']) distance =plpy.execute(distance_plan, [source, x])[0]["dist"] plpy.info("Distance from origin=" + str(distance)) if (distance <= distance_buffer): plpy.info("Using hba_adj_buffer") adj = hba_adj_buffer(x, target, p,tablename, col_geom, col_edge, col_cost, col_source, col_target, col_revc, col_cat, col_name, col_rule) else: plpy.info("Using hba_adj") adj = hba_adj(cat, x, target, p,tablename, col_geom, col_edge, col_cost, col_source, col_target, col_revc, col_cat, col_name, col_rule) #plpy.info("Obtained adjacents for node " + str(x) + " with category cat >= " + str(cat) + ". " + str(adj.nrows()) + " nodes.") #Forever alone if adj is None: plpy.error("This vertex is alone") #For each candidate hba_process_y(adj, p, cat, d, ol, cl, x, target, vertex_tablename, col_vertex_geom, col_edge, [], distance) #Return false, we still have to loop more return 0
def _flush_to_newline(self): pos = self.buf.rfind("\n") if pos == -1: return plpy.info(self.buf[:pos]) self.buf = self.buf[(pos + 1) :]
def flush(self): if len(self.buf) > 0: plpy.info(self.buf.rstrip("\n")) self.buf = ""
def hba_star_pl(source, target, tablename='routing', col_edge='id', col_cost='cost', col_revc='reverse_cost', col_rule='rule', col_source='source', col_target='target', col_geom='the_geom', col_name='name', col_cat='category', vertex_tablename='vertex', col_vertex_geom='geom'): #Closed Lists (backward and forward) clf = [] clb = [] #Open Lists (backward and forward) #Candidate nodes from which we want to continue calculating #Every node (key) contains (value) its estimated (heuristic) cost till the target olf = {} olb = {} #Total cost (backward and forward) #For every node, globally, its lowest cost from source/target d = {} d[target] = 0 d[source] = 0 #Predecessor array (backward and forward) #For every node, which is its previous node (related to d[]) ps = {} pt = {} #Current category of search (backward and forward) catf = [10] catb = [10] #Initial values olf[source] = d[source] + hba_heuristic(source, target, vertex_tablename, col_vertex_geom, col_edge) olb[target] = d[target] + hba_heuristic(target, source, vertex_tablename, col_vertex_geom, col_edge) global adj_plan global adj_plan_rev global central_node_plan adj_plan_rev = plpy.prepare( 'select a.*, ' + 'CASE WHEN a.name = $4 AND a.category <= 3 THEN \n\ (a.length + st_distance_sphere(st_startpoint(a.geom), b.geom)) * (a.category + 1) \n\ WHEN a.name = b.name THEN \n\ a.length + st_distance_sphere(st_startpoint(a.geom), b.geom) \n\ ELSE \n\ (a.length + st_distance_sphere(st_startpoint(a.geom), b.geom)) * ' + str(heuristic_constant) + ' * (a.category + 1) \n\ END as heuristic \n\ from ((select \n\ ' + col_geom + ' as geom, \n\ ' + col_edge + ' as id, \n\ ' + col_cost + ' as cost,\n\ st_distance_sphere(st_startpoint(' + col_geom + '), st_endpoint(' + col_geom + ')) as length,\n\ ' + col_target + ' as source, \n\ ' + col_source + ' as target, \n\ ' + col_cat + ' as category, \n\ ' + col_name + ' as name from ' + tablename + '\n\ ) union all (select \n\ st_reverse(' + col_geom + ') as geom, \n\ ' + col_edge + ' as id, \n\ ' + col_revc + ' as cost, \n\ st_distance_sphere(st_startpoint(' + col_geom + '), st_endpoint(' + col_geom + ')) as length,\n\ ' + col_source + ' as source,\n\ ' + col_target + ' as target, \n\ ' + col_cat + ' as category, \n\ ' + col_name + ' as name from ' + tablename + ' )\n\ ) a, (select st_startpoint(' + col_geom + ') as geom from ' + tablename + ' where ' + col_source + ' = $3 or ' + col_target + ' = $3 limit 1) b \n\ where source = $1 and category >= 0\n\ and cost <> \'Infinity\'' #Turn restrictions: + 'and ' + col_edge + ' not in (SELECT ' + col_edge + ' from ' + tablename + ' r where r.' + col_rule + ' = $2)', ['Integer', 'Integer', 'Integer', 'text', 'Integer']) adj_plan = plpy.prepare( '\n\ select a.*,' + 'CASE WHEN a.name = $4 AND a.category <= 3 THEN \n\ (a.length + st_distance_sphere(st_startpoint(a.geom), b.geom)) * (a.category + 1) \n\ WHEN a.name = b.name THEN \n\ a.length + st_distance_sphere(st_startpoint(a.geom), b.geom) \n\ ELSE \n\ (a.length + st_distance_sphere(st_startpoint(a.geom), b.geom)) * ' + str(heuristic_constant) + ' * (a.category + 1) \n\ END as heuristic \n\ from ((select \n\ st_reverse(' + col_geom + ') as geom, \n\ ' + col_edge + ' as id, \n\ ' + col_revc + ' as cost,\n\ st_distance_sphere(st_startpoint(' + col_geom + '), st_endpoint(' + col_geom + ')) as length,\n\ ' + col_target + ' as source, \n\ ' + col_source + ' as target, \n\ ' + col_cat + ' as category, \n\ ' + col_name + ' as name from ' + tablename + '\n\ ) union all (select \n\ ' + col_geom + ' as geom, \n\ ' + col_edge + ' as id, \n\ ' + col_cost + ' as cost, \n\ st_distance_sphere(st_startpoint(' + col_geom + '), st_endpoint(' + col_geom + ')) as length,\n\ ' + col_source + ' as source,\n\ ' + col_target + ' as target, \n\ ' + col_cat + ' as category, \n\ ' + col_name + ' as name from ' + tablename + ' )\n\ ) a, (select st_startpoint(' + col_geom + ') as geom from ' + tablename + ' where ' + col_source + ' = $3 or ' + col_target + ' = $3 limit 1) b \n\ where source = $1 and category >= 0\n\ and cost <> \'Infinity\'' #Turn restrictions: + 'and ' + col_edge + ' not in (SELECT ' + col_rule + ' from ' + tablename + ' r where r.' + col_edge + ' = $2 and ' + col_rule + ' is not null)', ['Integer', 'Integer', 'Integer', 'text', 'Integer']) central_node_plan = plpy.prepare( 'SELECT 1 WHERE NOT EXISTS(SELECT 1 FROM ' + tablename + ' WHERE ' + col_edge + ' = $2 and ' + col_rule + ' = $1)', ['Integer', 'Integer']) #Star two-sided A* search m = -1 m1 = 0 m2 = 0 #We try A* step by step until the two paths collided while not m1 and not m2 and (len(olf) > 0 and len(olb) > 0): m1 = hba_astar(source, target, olf, clf, clb, catf, d, ps, tablename, col_geom, col_edge, col_cost, col_revc, col_source, col_target, vertex_tablename, col_cat, col_vertex_geom, col_name, col_rule) m2 = hba_astar(target, source, olb, clb, clf, catb, d, pt, tablename, col_geom, col_edge, col_revc, col_cost, col_target, col_source, vertex_tablename, col_cat, col_vertex_geom, col_name, col_rule) m = m1 if m <= 0: m = m2 plpy.info("cl:", len(clf) + len(clb)) if m == 0: plpy.info("No path found. Inferring path") #Now, get the result return hba_buildPath(ps, pt, m, source, target, olf, olb)
def hba_star_pl(source, target, tablename='routing', col_edge='id', col_cost='cost', col_revc='reverse_cost', col_rule='rule', col_source='source', col_target='target', col_geom='the_geom', col_name='name', col_cat='category', vertex_tablename='vertex', col_vertex_geom='geom'): plpy.info("hba_star_pl(" + str(source) + ", " + str(target) + ")") #Closed Lists (backward and forward) clf = [] clb = [] #Open Lists (backward and forward) #Candidate nodes from which we want to continue calculating #Every node (key) contains (value) its estimated (heuristic) cost till the target olf = {} olb = {} #Total cost (backward and forward) #For every node, globally, its lowest cost from source/target d = {} d[target] = 0 d[source] = 0 #Predecessor array (backward and forward) #For every node, which is its previous node (related to d[]) ps = {} pt = {} #Current category of search (backward and forward) catf = [10] catb = [10] #Initial values olf[source] = d[source] + hba_heuristic(source, target, vertex_tablename, col_vertex_geom, col_edge) olb[target] = d[target] + hba_heuristic(target, source, vertex_tablename, col_vertex_geom, col_edge) #Star two-sided A* search m = -1 m1 = 0 m2 = 0 #We try A* step by step until the two paths collided while not m1 and not m2 and (len(olf) > 0 and len(olb) > 0): m1 = hba_astar(source, target, olf, clf, clb, catf, d, ps, tablename, col_geom, col_edge, col_cost, col_revc, col_source, col_target, vertex_tablename, col_cat, col_vertex_geom, col_name, col_rule) m2 = hba_astar(target, source, olb, clb, clf, catb, d, pt, tablename, col_geom, col_edge, col_revc, col_cost, col_target, col_source, vertex_tablename, col_cat, col_vertex_geom, col_name, col_rule) plpy.info("Finished calculation") m = m1 if m <= 0 : m = m2 plpy.info("cl:", len(clf) + len(clb)) plpy.info(clf) plpy.info(clb) if m == 0: plpy.info("No path found. Inferring path") #Now, get the result return hba_buildPath(ps, pt, m, source, target, olf, olb)
def _flush_to_newline(self): pos = self.buf.rfind("\n") if pos == -1: return plpy.info(self.buf[:pos]) self.buf = self.buf[(pos + 1):]
def hba_star_pl(source, target, tablename='routing', col_edge='id', col_cost='cost', col_revc='reverse_cost', col_rule='rule', col_source='source', col_target='target', col_geom='the_geom', col_name='name', col_cat='category', vertex_tablename='vertex', col_vertex_geom='geom'): #Closed Lists (backward and forward) clf = [] clb = [] #Open Lists (backward and forward) #Candidate nodes from which we want to continue calculating #Every node (key) contains (value) its estimated (heuristic) cost till the target olf = {} olb = {} #Total cost (backward and forward) #For every node, globally, its lowest cost from source/target d = {} d[target] = 0 d[source] = 0 #Predecessor array (backward and forward) #For every node, which is its previous node (related to d[]) ps = {} pt = {} #Current category of search (backward and forward) catf = [10] catb = [10] #Initial values olf[source] = d[source] + hba_heuristic(source, target, vertex_tablename, col_vertex_geom, col_edge) olb[target] = d[target] + hba_heuristic(target, source, vertex_tablename, col_vertex_geom, col_edge) global adj_plan global adj_plan_rev global central_node_plan adj_plan_rev = plpy.prepare('select a.*, ' + 'CASE WHEN a.name = $4 AND a.category <= 3 THEN \n\ (a.length + st_distance_sphere(st_startpoint(a.geom), b.geom)) * (a.category + 1) \n\ WHEN a.name = b.name THEN \n\ a.length + st_distance_sphere(st_startpoint(a.geom), b.geom) \n\ ELSE \n\ (a.length + st_distance_sphere(st_startpoint(a.geom), b.geom)) * ' + str(heuristic_constant) + ' * (a.category + 1) \n\ END as heuristic \n\ from ((select \n\ ' + col_geom + ' as geom, \n\ ' + col_edge + ' as id, \n\ ' + col_cost + ' as cost,\n\ st_distance_sphere(st_startpoint(' + col_geom + '), st_endpoint(' + col_geom + ')) as length,\n\ ' + col_target + ' as source, \n\ ' + col_source + ' as target, \n\ ' + col_cat + ' as category, \n\ ' + col_name + ' as name from ' + tablename + '\n\ ) union all (select \n\ st_reverse(' + col_geom + ') as geom, \n\ ' + col_edge + ' as id, \n\ ' + col_revc + ' as cost, \n\ st_distance_sphere(st_startpoint(' + col_geom + '), st_endpoint(' + col_geom + ')) as length,\n\ ' + col_source + ' as source,\n\ ' + col_target + ' as target, \n\ ' + col_cat + ' as category, \n\ ' + col_name + ' as name from ' + tablename + ' )\n\ ) a, (select st_startpoint(' + col_geom + ') as geom from ' + tablename + ' where ' + col_source + ' = $3 or ' + col_target + ' = $3 limit 1) b \n\ where source = $1 and category >= 0\n\ and cost <> \'Infinity\'' #Turn restrictions: + 'and ' + col_edge + ' not in (SELECT ' + col_edge + ' from ' + tablename + ' r where r.' + col_rule + ' = $2)' , ['Integer', 'Integer', 'Integer', 'text', 'Integer']) adj_plan = plpy.prepare('\n\ select a.*,' + 'CASE WHEN a.name = $4 AND a.category <= 3 THEN \n\ (a.length + st_distance_sphere(st_startpoint(a.geom), b.geom)) * (a.category + 1) \n\ WHEN a.name = b.name THEN \n\ a.length + st_distance_sphere(st_startpoint(a.geom), b.geom) \n\ ELSE \n\ (a.length + st_distance_sphere(st_startpoint(a.geom), b.geom)) * ' + str(heuristic_constant) + ' * (a.category + 1) \n\ END as heuristic \n\ from ((select \n\ st_reverse(' + col_geom + ') as geom, \n\ ' + col_edge + ' as id, \n\ ' + col_revc + ' as cost,\n\ st_distance_sphere(st_startpoint(' + col_geom + '), st_endpoint(' + col_geom + ')) as length,\n\ ' + col_target + ' as source, \n\ ' + col_source + ' as target, \n\ ' + col_cat + ' as category, \n\ ' + col_name + ' as name from ' + tablename + '\n\ ) union all (select \n\ ' + col_geom + ' as geom, \n\ ' + col_edge + ' as id, \n\ ' + col_cost + ' as cost, \n\ st_distance_sphere(st_startpoint(' + col_geom + '), st_endpoint(' + col_geom + ')) as length,\n\ ' + col_source + ' as source,\n\ ' + col_target + ' as target, \n\ ' + col_cat + ' as category, \n\ ' + col_name + ' as name from ' + tablename + ' )\n\ ) a, (select st_startpoint(' + col_geom + ') as geom from ' + tablename + ' where ' + col_source + ' = $3 or ' + col_target + ' = $3 limit 1) b \n\ where source = $1 and category >= 0\n\ and cost <> \'Infinity\'' #Turn restrictions: + 'and ' + col_edge + ' not in (SELECT ' + col_rule + ' from ' + tablename + ' r where r.' + col_edge + ' = $2 and ' + col_rule + ' is not null)' , ['Integer', 'Integer', 'Integer', 'text', 'Integer']) central_node_plan = plpy.prepare('SELECT 1 WHERE NOT EXISTS(SELECT 1 FROM ' + tablename + ' WHERE ' + col_edge + ' = $2 and ' + col_rule + ' = $1)', ['Integer', 'Integer']) #Star two-sided A* search m = -1 m1 = 0 m2 = 0 #We try A* step by step until the two paths collided while not m1 and not m2 and (len(olf) > 0 and len(olb) > 0): m1 = hba_astar(source, target, olf, clf, clb, catf, d, ps, tablename, col_geom, col_edge, col_cost, col_revc, col_source, col_target, vertex_tablename, col_cat, col_vertex_geom, col_name, col_rule) m2 = hba_astar(target, source, olb, clb, clf, catb, d, pt, tablename, col_geom, col_edge, col_revc, col_cost, col_target, col_source, vertex_tablename, col_cat, col_vertex_geom, col_name, col_rule) m = m1 if m <= 0 : m = m2 plpy.info("cl:", len(clf) + len(clb)) if m == 0: plpy.info("No path found. Inferring path") #Now, get the result return hba_buildPath(ps, pt, m, source, target, olf, olb)