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 _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(self):
if len(self.buf) > 0:
plpy.info(self.buf.rstrip("\n"))
self.buf = ""
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)
