def push_flood_forecast_event_plpy(self, flood_forecast_events):
import plpy
# Filter only the needed column
flood_events = [
{
'flood_map_id': v['flood_map_id'],
'acquisition_date': v['acquisition_date'].isoformat(),
'forecast_date': v['forecast_date'].isoformat(),
'source': v['source'],
'notes': v['notes'],
'link': v['link'],
'trigger_status': v['trigger_status_candidate'],
# Progress 1 means, impact level has not been calculated
'progress': GloFASForecast.PROGRESS_IN_PROGRESS
} for v in flood_forecast_events
]
json_string = json.dumps(flood_events)
# The process needs to be idempotent, so delete matching forecast first
for event in flood_events:
plan = plpy.prepare(self.plpy_flood_event_delete_filter,
["int", "timestamp", "timestamp", "varchar"])
plpy.execute(plan, [
event['flood_map_id'], event['acquisition_date'],
event['forecast_date'], event['source']
])
# We bulk insert the events
plan = plpy.prepare(self.plpy_flood_event_insert_query, ["text"])
result = plpy.execute(plan, [json_string])
return result
def get_plan(self, sql, types):
"""Prepare the plan and cache it."""
t = (sql, tuple(types))
if t in self.plan_map:
pc = self.plan_map[t]
# put to the end
self.plan_list.remove(pc)
self.plan_list.append(pc)
return pc.plan
# prepare new plan
plan = plpy.prepare(sql, types)
# add to cache
pc = CachedPlan(t, plan)
self.plan_list.append(pc)
self.plan_map[t] = pc
# remove plans if too much
while len(self.plan_map) > self.maxplans:
# this is ugly workaround for pylint
drop = self.plan_list.pop()
del self.plan_map[getattr(drop, 'key')]
return plan
def get_plan(self, sql, types):
"""Prepare the plan and cache it."""
t = (sql, tuple(types))
if t in self.plan_map:
pc = self.plan_map[t]
# put to the end
self.plan_list.remove(pc)
self.plan_list.append(pc)
return pc.plan
# prepare new plan
plan = plpy.prepare(sql, types)
# add to cache
pc = CachedPlan(t, plan)
self.plan_list.append(pc)
self.plan_map[t] = pc
# remove plans if too much
while len(self.plan_map) > self.maxplans:
pc = self.plan_list.pop()
del self.plan_map[pc.key]
return plan
def __init__(self, sql):
qb = QueryBuilder(sql, None)
p_sql = qb.get_sql(PARAM_PLPY)
p_types = qb._arg_type_list
self.plan = plpy.prepare(p_sql, p_types)
self.arg_map = qb._arg_value_list
self.sql = sql
def get_plan(self, sql, types):
"""Prepare the plan and cache it."""
t = (sql, tuple(types))
if t in self.plan_map:
pc = self.plan_map[t]
# put to the end
self.plan_list.remove(pc)
self.plan_list.append(pc)
return pc.plan
# prepare new plan
plan = plpy.prepare(sql, types)
# add to cache
pc = CachedPlan(t, plan)
self.plan_list.append(pc)
self.plan_map[t] = pc
# remove plans if too much
while len(self.plan_map) > self.maxplans:
pc = self.plan_list.pop()
del self.plan_map[pc.key]
return plan
def _plan_batch(insert_sql, column_types, batch_size):
index = 1 # PostgreSQL's place holder begins from 1
# append value list (...), (...), ... at the end of insert_sql
batch_isnert_sql = [insert_sql]
first = True
for i in range(batch_size):
if first:
first = False
else:
batch_isnert_sql.append(", ")
# ($1::column_type, $2::column_type, ...)
batch_isnert_sql.append("(")
value_first = True
for column_type in column_types:
if value_first:
value_first = False
else:
batch_isnert_sql.append(", ")
batch_isnert_sql.append("$%s::%s" % (index, column_type))
index += 1
batch_isnert_sql.append(")")
return plpy.prepare(''.join(batch_isnert_sql), column_types * batch_size)
def __init__(self, sql):
qb = QueryBuilder(sql, None)
p_sql = qb.get_sql(PARAM_PLPY)
p_types = qb._arg_type_list
self.plan = plpy.prepare(p_sql, p_types)
self.arg_map = qb._arg_value_list
self.sql = sql
def execute(self, operation, parameters=None):
if self._is_closed():
raise Error()
self.connection._ensure_transaction()
parameters = parameters or []
placeholders = []
types = []
values = []
i = 0
for param in parameters:
if param is None:
# Directly put "None" as "NULL" in the sql
# as it's not possible to get the type
placeholders.append('NULL')
else:
i += 1
placeholders.append("$%d" % i)
types.append(self.py_param_to_pg_type(param))
if types[-1] == 'bytea' and hasattr(param, 'tobytes'):
values.append(param.tobytes())
else:
values.append(param)
query = operation % tuple(placeholders)
try:
plan = plpy.prepare(query, types)
res = plpy.execute(plan, values)
except plpy.SPIError as e:
raise Error(e)
self._execute_result = None
self.rownumber = None
self.description = None
self.rowcount = -1
if res.status() in [self._SPI_OK_SELECT, self._SPI_OK_INSERT_RETURNING,
self._SPI_OK_DELETE_RETURNING, self._SPI_OK_UPDATE_RETURNING]:
if 'colnames' in res.__class__.__dict__:
# Use colnames to get the order of the variables in the query
self._execute_result = [tuple([row[col] for col in res.colnames()]) for row in res]
else:
self._execute_result = [tuple([row[col] for col in row]) for row in res]
self.rownumber = 0
if 'colnames' in res.__class__.__dict__:
# PG 9.2+: use .colnames() and .coltypes() methods
self.description = [(name, get_type_obj(typeoid), None, None, None, None, None) for name, typeoid in zip(res.colnames(), res.coltypes())]
elif len(res) > 0:
# else get at least the column names from the row keys
self.description = [(name, None, None, None, None, None, None) for name in res[0].keys()]
else:
# else we know nothing
self.description = [(None, None, None, None, None, None, None)]
if res.status() == self._SPI_OK_UTILITY:
self.rowcount = -1
else:
self.rowcount = res.nrows()
def plpy_execute(query, types, values):
assert query.count('%s') == len(types)
assert len(types) == len(values)
for i in range(len(values)):
query = query.replace('%s', '$' + str(i + 1), 1)
plan = plpy.prepare(query, types)
return plpy.execute(plan, values)
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 get_type_obj(typeoid):
"""Return the type object (STRING, NUMBER, etc.) that corresponds
to the given type OID."""
if not _typoid_typeobjs:
for row in plpy.execute(plpy.prepare("SELECT oid, typname, typcategory FROM pg_type")):
if row['typcategory'] in _typcategory_typeobjs:
_typoid_typeobjs[int(row['oid'])] = _typcategory_typeobjs[row['typcategory']]
elif row['typname'] in _typname_typeobjs:
_typoid_typeobjs[int(row['oid'])] = _typname_typeobjs[row['typname']]
return _typoid_typeobjs.get(typeoid)
def country_to_iso3(country):
""" Convert country to its iso3 code """
try:
country_plan = plpy.prepare("SELECT adm0_a3 as iso3 FROM admin0_synonyms WHERE lower(regexp_replace($1, " \
"'[^a-zA-Z\u00C0-\u00ff]+', '', 'g'))::text = name_; ", ['text'])
country_result = plpy.execute(country_plan, [country], 1)
if country_result:
return country_result[0]['iso3']
else:
return None
except BaseException as e:
plpy.warning("Can't get the iso3 code from {0}: {1}".format(country, e))
return None
def execute(self, operation, parameters=None):
if self._is_closed():
raise Error()
self.connection._ensure_transaction()
parameters = parameters or []
placeholders = []
types = []
values = []
for i, param in enumerate(parameters):
placeholders.append("$%d" % (i + 1))
types.append(self.py_param_to_pg_type(param))
values.append(param)
if len(placeholders) == 1:
query = operation % placeholders[0]
else:
query = operation % placeholders
try:
plan = plpy.prepare(query, types)
res = plpy.execute(plan, values)
except plpy.SPIError as e:
raise Error(e)
self._execute_result = None
self.rownumber = None
self.description = None
self.rowcount = -1
if res.status() == self._SPI_OK_SELECT:
self._execute_result = [[row[col] for col in row] for row in res]
self.rownumber = 0
if 'colnames' in res.__class__.__dict__:
# PG 9.2+: use .colnames() and .coltypes() methods
self.description = [
(name, get_type_obj(typeoid), None, None, None, None, None)
for name, typeoid in zip(res.colnames(), res.coltypes())
]
elif len(res) > 0:
# else get at least the column names from the row keys
self.description = [(name, None, None, None, None, None, None)
for name in res[0].keys()]
else:
# else we know nothing
self.description = [(None, None, None, None, None, None, None)]
if res.status() == self._SPI_OK_UTILITY:
self.rowcount = -1
else:
self.rowcount = res.nrows()
def get_type_obj(typeoid):
"""Return the type object (STRING, NUMBER, etc.) that corresponds
to the given type OID."""
if not _typoid_typeobjs:
for row in plpy.execute(
plpy.prepare("SELECT oid, typname, typcategory FROM pg_type")):
if row['typcategory'] in _typcategory_typeobjs:
_typoid_typeobjs[int(
row['oid'])] = _typcategory_typeobjs[row['typcategory']]
elif row['typname'] in _typname_typeobjs:
_typoid_typeobjs[int(
row['oid'])] = _typname_typeobjs[row['typname']]
return _typoid_typeobjs.get(typeoid)
def country_to_iso3(country):
""" Convert country to its iso3 code """
try:
country_plan = plpy.prepare("SELECT adm0_a3 as iso3 FROM admin0_synonyms WHERE lower(regexp_replace($1, " \
"'[^a-zA-Z\u00C0-\u00ff]+', '', 'g'))::text = name_; ", ['text'])
country_result = plpy.execute(country_plan, [country], 1)
if country_result:
return country_result[0]['iso3']
else:
return None
except BaseException as e:
plpy.warning("Can't get the iso3 code from {0}: {1}".format(
country, e))
return None
def find_flood_map_plpy(self, station_id, return_period_min, return_period_max):
import plpy
plan = plpy.prepare(self.plpy_query_flood_map_filter,
["int", "int", "int"])
result = plpy.execute(plan, [station_id, return_period_min,
return_period_max])
if len(result) == 0:
# We didn't found any flood map
return None
# We found the flood map
row = result[0]
return row['id']
def find_previous_flood_forecast_plpy(
self, forecast_date, maximum_acquisition_date, source):
import plpy
plan = plpy.prepare(self.plpy_query_flood_map_filter,
["timestamp", "timestamp", "varchar"])
result = plpy.execute(
plan, [maximum_acquisition_date.isoformat(), forecast_date.isoformat(), source])
if len(result) == 0:
# We didn't found any flood map
return None
# We found the flood map
row = result[0]
return row
def execute(self, operation, parameters=None):
if self._is_closed():
raise Error()
self.connection._ensure_transaction()
parameters = parameters or []
placeholders = []
types = []
values = []
for i, param in enumerate(parameters):
placeholders.append("$%d" % (i + 1))
types.append(self.py_param_to_pg_type(param))
values.append(param)
if len(placeholders) == 1:
query = operation % placeholders[0]
else:
query = operation % placeholders
try:
plan = plpy.prepare(query, types)
res = plpy.execute(plan, values)
except plpy.SPIError as e:
raise Error(e)
self._execute_result = None
self.rownumber = None
self.description = None
self.rowcount = -1
if res.status() == self._SPI_OK_SELECT:
self._execute_result = [[row[col] for col in row] for row in res]
self.rownumber = 0
if 'colnames' in res.__class__.__dict__:
# PG 9.2+: use .colnames() and .coltypes() methods
self.description = [(name, get_type_obj(typeoid), None, None, None, None, None) for name, typeoid in zip(res.colnames(), res.coltypes())]
elif len(res) > 0:
# else get at least the column names from the row keys
self.description = [(name, None, None, None, None, None, None) for name in res[0].keys()]
else:
# else we know nothing
self.description = [(None, None, None, None, None, None, None)]
if res.status() == self._SPI_OK_UTILITY:
self.rowcount = -1
else:
self.rowcount = res.nrows()
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):
try:
last_id = int(p[x][1]['id'])
except:
last_id = -1
global central_node_plan
if "source" in col_source:
check_x = plpy.execute(central_node_plan, [x, last_id])
else:
check_x = plpy.execute(central_node_plan, [last_id, x])
for checking in check_x:
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 min(st_distance_sphere(v1.geom, v2.geom)) as dist from vertex v1, vertex v2 where v1.id = $1 and (v2.id = $2 or v2.id = $3)',['Integer', 'Integer', 'Integer'])
distance =plpy.execute(distance_plan, [x, source, target], 1)[0]["dist"]
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)
#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 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 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 _transform(obj, type_, func_name, func_sign, params):
''' Transform obj, whose type is type_, using func_name, func_sign and params.
'''
if func_name not in func_names:
plpy.error('function {} is unknown'.format(func_name))
func_name = func_names[func_name]
if isinstance(params, basestring): # NOQA
params = json.loads(params)
args = [params[p] for p in func_sign if p != '_time']
args_str, args_val = args_to_array_string(args)
q = 'select {}(\'{}\'::{}{}) r'.format(func_name, obj, type_, args_str)
plpy.debug(q, args_val)
plan = plpy.prepare(q, ['numeric'] * len(args_val))
rv = plpy.execute(plan, args_val)
if len(rv) != 1:
plpy.error('unexpected number of rows ({}) returned from {}'.format(len(rv), q))
result = rv[0].get('r')
if result is None:
plpy.error('unexpected value None returned from {}'.format(q))
return result
def _plan_batch(insert_sql, values_sql_format, column_types, batch_size):
# format string 'values ($1, $2), ($3, $4) ...'
values_sql = (", ".join([values_sql_format] * batch_size)).format(*range(1, batch_size * len(column_types) + 1))
batch_insert_sql = insert_sql + values_sql
return plpy.prepare(batch_insert_sql, column_types * batch_size)
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):
try:
last_id = int(p[x][1]['id'])
except:
last_id = -1
global central_node_plan
if "source" in col_source:
check_x = plpy.execute(central_node_plan, [x, last_id])
else:
check_x = plpy.execute(central_node_plan, [last_id, x])
for checking in check_x:
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 min(st_distance_sphere(v1.geom, v2.geom)) as dist from vertex v1, vertex v2 where v1.id = $1 and (v2.id = $2 or v2.id = $3)',
['Integer', 'Integer', 'Integer'])
distance = plpy.execute(distance_plan, [x, source, target],
1)[0]["dist"]
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)
#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 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_adj(cat, source, target, p, tablename='routing', col_geom='geom', col_edge='id', col_cost='cost', col_source='source', col_target='target', col_revc='reverse_cost', col_cat='category', col_name='name', col_rule='rule'):
if adj_plan == -1:
global adj_plan
global adj_plan_rev
adj_plan_rev = plpy.prepare('\n\
select a.*, st_distance_sphere(st_startpoint(a.geom), b.geom) + a.length * ' + str(heuristic_constant) + ' * (a.category + 1) as heuristic 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_rule + ' from ' + tablename + ' r where r.' + col_edge + ' = $2 and ' + col_rule + ' is not null)'
, ['Integer', 'Integer', 'Integer'])
adj_plan = plpy.prepare('\n\
select a.*, st_distance_sphere(st_startpoint(a.geom), b.geom) + a.length * ' + str(heuristic_constant) + ' * (a.category + 1) as heuristic 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'])
try:
last_id = int(p[int(source)][1]['id'])
except:
last_id = -1
# plpy.info([source, last_id, target])
if "source" in col_source:
return plpy.execute(adj_plan, [source, last_id, target])
else:
return plpy.execute(adj_plan_rev, [source, last_id, target])
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)