def run(self):
while True:
md = self.q.get()
if not md or self.exit_event.is_set():
break
start_time = time.time()
updated = dict(outgoing=0, within=0)
# Do Matching for each point type
for p_start, p_end in zip((PointType.Start, PointType.Within_Start), (PointType.End, PointType.Within_End)):
with db.get_connection() as conn:
cur = conn.cursor()
# SELECT the lookup points to a given rs
sql = 'SELECT id FROM de_sim_points_lookup WHERE rs = %(rs)s AND point_type = %(p_type)s ORDER BY RANDOM()'
args = dict(rs=md.rs, p_type=p_start.value)
cur.execute(sql, args)
result = cur.fetchall()
conn.commit()
matched_points = 0
for lookup in result:
matched_points += self._lookup_match(lookup[0], md.rs, p_start, p_end)
if p_start is PointType.Start:
updated['outgoing'] = matched_points
else:
updated['within'] = matched_points
count = self.counter.increment()
self.logging.info('(%4d/%d) Finished matching for %12s in %.2f. Outgoing (%6d/%6d). Within (%6d/%6d).',
count, self.counter.maximum, md.rs, time.time() - start_time,
updated['outgoing'], md.outgoing, updated['within'], md.within)
self.logging.info('Exiting Matcher Tread: %s', self.name)
def generate_routes():
logging.info('Start of route generation')
number_of_processes = 8
route_queue = Queue(maxsize=20000)
sql = 'SELECT id, start_point, end_point FROM de_sim_routes'
threading.Thread(target=_queue_feeder, args=(sql, route_queue, 20000, number_of_processes)).start()
with connection.get_connection() as conn:
cur = conn.cursor()
cur.execute('SELECT COUNT(id) FROM de_sim_routes') # execute 1.7 Secs
rec = cur.fetchone()
counter = Counter(rec[0])
while not route_queue.full():
time.sleep(0.2)
start = time.time()
processes = []
for i in range(number_of_processes):
p = ProcessRouteCalculation(route_queue, counter)
processes.append(p)
processes[-1].start()
for p in processes:
p.join()
end = time.time()
logging.info('Runtime Route Generation: %s', (end - start))
def _lookup_filling_stations(self, distance_meter, sql=None):
"""
Searches for filling stations alongside the route
"""
if not sql:
sql = 'CREATE TEMP TABLE filling (target integer, station_id character varying(38)) ON COMMIT DROP; ' \
'INSERT INTO filling (station_id) SELECT id FROM de_tt_stations AS s ' \
' WHERE ST_DWithin(s.geom, ST_GeomFromEWKB(%(route)s), %(distance)s); ' \
'UPDATE filling SET target = (SELECT id::integer FROM de_2po_vertex ORDER BY geom_vertex <-> ' \
' (SELECT geom FROM de_tt_stations WHERE id = filling.station_id) LIMIT 1); ' \
'SELECT target, station_id FROM filling;'
with db.get_connection() as conn:
cur = conn.cursor(cursor_factory=NamedTupleCursor)
try:
cur.execute(sql, dict(distance=distance_meter, route=self.env.route.geom_line))
stations = cur.fetchall()
except Exception:
log = logging.getLogger('database')
log.exception('Could not execute query. "%s"', cur.query)
conn.rollback()
raise
else:
conn.commit()
if len(stations) == 0:
raise NoFillingStationError('No filling station was found for commuter %s', self.env.commuter.id)
else:
for station in stations:
self._refillstations.append(FillingStation(target=station.target, id=station.station_id))
self.env.result.set_commuter_filling_stations(self.stations_ids)
def modifyUser(name, pw, userPhone):
conn = connection.get_connection()
try:
sql = "select * from user_table where phone_num = %s"
cursor = conn.cursor()
cursor.execute(sql, userPhone)
data = cursor.fetchall()
match = False
if data:
match = True
print("수정했습니다.")
sql = "update user_table SET name = %s where phone_num = %s"
cursor.execute(sql, (name, userPhone))
data = cursor.fetchall()
else:
print("수정할 수 없습니다.")
match = False
print(data)
conn.commit()
finally:
cursor.close()
return match
def run(self):
"""Inserts generated Points into the database
Inspired by https://peterman.is/blog/postgresql-bulk-insertion/2013/08/
"""
self.log.info('Starting inserting thread %s', self.name)
with connection.get_connection() as conn:
cur = conn.cursor()
for prepare_statement in self.plans:
cur.execute(prepare_statement)
conn.commit()
while True:
try:
sql_list = self.q.get(timeout=0.05)
start = time.time()
cur.execute('\n'.join(sql_list))
conn.commit()
end = time.time()
self.log.info('Inserted %s, Queue remaining %s, SQL time %s',
len(sql_list), self.q.qsize(), end - start)
del sql_list
except Empty:
if self.stop_request.is_set():
break
continue
else:
self.q.task_done()
# TODO remove after test?!
self.log.warn('Cleaning %d elements from Queue ... ', self.q.qsize())
while not self.q.empty():
self.q.get()
self.q.task_done()
def get_todolist():
conn = connection.get_connection()
# start_idx = (int(page)-1)*10
sql = '''
select todo_content, todo_importance, todo_idx
from todo
where todo_status = 1 or todo_status = 2
order by todo_idx desc
'''
cursor = conn.cursor()
cursor.execute(sql)
# cursor.execute(sql, (start_idx))
row = cursor.fetchall()
data_list = []
for obj in row:
data_dic = {
'todo_content': obj[0],
'todo_importance': obj[1],
'todo_idx': obj[2]
}
data_list.append(data_dic)
conn.close
return data_list
def getfoodoneContent(food_idx) :
conn = connection.get_connection()
sql = '''
select food_name,food_loc,food_menu,food_thumb, food_score, food_likes
from jeju_best
where food_idx = %s
'''
cursor = conn.cursor()
cursor.execute(sql,food_idx)
row = cursor.fetchall()
data_list_one=[]
for obj in row:
data_dic1={
'food_name' : obj[0],
'food_loc' : obj[1],
'food_menu' : obj[2],
'food_thumb' : obj[3],
'food_score' : obj[4],
'food_likes' : obj[5]
}
data_list_one.append(data_dic1)
conn.close()
return data_list_one
def deleteUser(userName, pw, userPhone):
conn = connection.get_connection()
print('a')
try:
sql = "select * from user_table where phone_num = %s and pw = %s"
cursor = conn.cursor()
cursor.execute(sql, (userPhone, pw))
data = cursor.fetchall()
match = False
if data:
match = True
print("삭제했습니다.")
sql = "delete from user_table where phone_num = %s"
cursor.execute(sql, userPhone)
data = cursor.fetchall()
else:
print("삭제할 수 없습니다.")
match = False
print(data)
conn.commit()
finally:
cursor.close()
return match
def joinUser(name, ssn, phone_num, phone_company, pw):
conn = connection.get_connection()
try:
sql = "select * from user_table where phone_num = %s"
cursor = conn.cursor()
cursor.execute(sql, phone_num)
data = cursor.fetchall()
# 휴대전화번호가 DB에 있는 경우
check = False
if data:
print("이미 있는 번호입니다.")
check = False
else:
check = True
print("가입되었습니다.")
sql = '''insert into user_table(name, ssn, phone_num, phone_company, pw) values (%s, %s, %s, %s, %s)'''
cursor.execute(sql, (name, ssn, phone_num, phone_company, pw))
data = cursor.fetchall()
print(data)
conn.commit()
finally:
cursor.close()
return check
def insert_data(data):
with db.get_connection() as conn:
for d in data:
cur = conn.cursor()
d = json.loads(d)
# First the commuter
d['commuter']['leaving_time'] = \
dt.datetime.strptime(d['commuter']['leaving_time'], '%H:%M:%S') \
- dt.datetime.strptime('0:00:00', '%H:%M:%S')
try:
cur.execute(c_sql, d['commuter'])
except IntegrityError:
log.error('Commuter (%s, %s) has already been simulated. Not inserting.', d['commuter']['c_id'], d['commuter']['rerun'])
conn.rollback()
continue
# Then the route
for ro_data in d['route']:
cur.execute(ro_sql, ro_data)
# and last the refill events
for re_data in d['refill']:
re_data['refueling_time'] = dt.datetime.strptime(re_data['refueling_time'], '%Y-%m-%d %H:%M:%S%z')
cur.execute(re_sql, re_data)
conn.commit()
def _setup_environment(self):
import random
from database import connection as db
from simulation.car import PetrolCar, DieselCar
from simulation.strategy import SimpleRefillStrategy, CheapestRefillStrategy
if self.fsm.env.rerun:
from psycopg2.extras import NamedTupleCursor
with db.get_connection() as conn:
cur = conn.cursor(cursor_factory=NamedTupleCursor)
args = dict(c_id=self.fsm.env.commuter.id)
cur.execute('SELECT * FROM de_sim_data_commuter WHERE c_id = %(c_id)s AND NOT rerun', args)
result = cur.fetchone()
conn.commit()
if result:
if result.fuel_type == 'e5':
car = PetrolCar(self.fsm.env)
else:
car = DieselCar(self.fsm.env)
car._tankFilling = result.tank_filling
self.fsm.env.commuter._leave = result.leaving_time
CheapestRefillStrategy(self.fsm.env)
else:
if random.random() > 0.5:
PetrolCar(self.fsm.env)
else:
DieselCar(self.fsm.env)
SimpleRefillStrategy(self.fsm.env)
def get_machine_data_list():
conn = connection.get_connection()
sql = '''
select machine_code,product_key,start_time,end_time,process_time,machine_data,machine_data_code
from machine
order by process_time ASC
'''
cursor = conn.cursor()
cursor.execute(sql)
row = cursor.fetchall()
machine_data_list = []
for obj in row:
data_dic = {
'machine_code': obj[0],
'product_key': obj[1],
'start_time': obj[2],
'end_time': obj[3],
'machine_data': obj[4],
'machine_data_code': obj[5]
}
machine_data_list.append(data_dic)
conn.close
return machine_data_list
def getfoodContent():
conn=connection.get_connection()
sql='''select food_url, food_thumb, food_score,food_name, food_loc, food_menu, food_views, food_likes, food_calls, food_idx
from jeju_best'''
cursor=conn.cursor()
cursor.execute(sql)
row=cursor.fetchall()
data_list=[]
for obj in row:
data_dic={
'food_url' : obj[0],
'food_thumb' : obj[1],
'food_score' : obj[2],
'food_name' : obj[3],
'food_loc' : obj[4],
'food_menu' : obj[5],
'food_views' : obj[6],
'food_likes' : obj[7],
'food_calls' : obj[8],
'food_idx' : obj[9]
}
data_list.append(data_dic)
conn.close()
return data_list
def match_points():
"""
Matches start and end points with a randomized order of the districts
:return:
"""
number_of_matchers = 8
max_age_distribution = 3
matching_queue = mp.Queue()
logging.info('Start matching points for routes.')
logging.info('Start filling work queue.')
with connection.get_connection() as conn:
cur = conn.cursor()
cur.execute('SELECT rs, outgoing, within FROM de_commuter ORDER BY RANDOM()')
conn.commit()
counter = Counter(cur.rowcount * max_age_distribution)
for rec in cur.fetchall():
obj = MatchingDistribution(rec[0], rec[1], rec[2])
matching_queue.put(obj)
start = time.time()
processes = []
signal.signal(signal.SIGINT, signal.SIG_IGN)
for i in range(number_of_matchers):
processes.append(PointMassMatcherProcess(matching_queue, counter, sig.exit_event, max_age_distribution))
processes[-1].start()
signal.signal(signal.SIGINT, sig.signal_handler)
for p in processes:
p.join()
end = time.time()
logging.info('Runtime Point Matching: %s', (end - start))
def _zeromq_feeder(sql, socket, exit_event, size=500, rerun=False):
"""Feeder thread for queues
As the route is the main attribute that describes a commuter the thread will feed the routes to the queue
one by one to be simulated by the commuter simulation object
:return:
"""
with db.get_connection() as conn:
cur = conn.cursor('feeder')
cur.execute(sql)
i = 0
k = 0
n = 10000
while True:
results = cur.fetchmany(size)
for rec in results:
socket.send_json(dict(c_id=rec[0], rerun=rerun))
i += 1
if i >= n:
k += 1
logging.info('Send commuter: %d', k * n)
i -= n
if not results:
break
if exit_event.is_set():
break
logging.info('Total send commuter: %d', k * n + i)
cur.close()
conn.commit()
socket.setsockopt(zmq.LINGER, 0)
def _feeder(sql, socket, exit_event, rerun=False):
with db.get_connection() as conn:
cur = conn.cursor()
cur.execute(sql)
results = cur.fetchall()
for rec in results:
if exit_event.is_set():
break
socket.send_json(dict(c_id=rec[0], rerun=rerun))
socket.setsockopt(zmq.LINGER, 0)
def limitChange(userPhone):
conn = connection.get_connection()
sql = "update user_table set limit_m = null where phone_num = %s"
cursor = conn.cursor()
cursor.execute(sql, (userPhone))
conn.commit()
cursor.close()
return
def limitMoney(userPhone, money):
conn = connection.get_connection()
sql = "update user_table SET limit_m = %s where phone_num = %s"
cursor = conn.cursor()
cursor.execute(sql, (money, userPhone))
conn.commit()
cursor.close()
return
def userinfo(userPhone):
conn = connection.get_connection()
sql = "select * from user_table where phone_num = %s"
cursor = conn.cursor()
cursor.execute(sql, userPhone)
userInfo = cursor.fetchall()
cursor.close()
return userInfo
def limitChk(userPhone):
conn = connection.get_connection()
sql = "select limit_m from user_table where phone_num = %s"
cursor = conn.cursor()
cursor.execute(sql, (userPhone))
limit = cursor.fetchall()
cursor.close()
return limit
def sayHello():
conn = connection.get_connection()
sql = "select ans_output from output_ans where ans_id = %s"
cursor = conn.cursor()
cursor.execute(sql, ('인사'))
hello = cursor.fetchall()
cursor.close()
return hello
def mainSend(*data):
conn = connection.get_connection()
sql = '''
insert into main_send
(id, time, isPerson, people)
values (%s, %s, %s, %s)
'''
cursor = conn.cursor()
cursor.execute(sql, data)
conn.commit()
conn.close()
def subSend(*data):
conn = connection.get_connection()
sql = '''
insert into sub_send
(id, arduino_sub_num, time, power)
values (%s, %s, %s, %s)
'''
cursor = conn.cursor()
cursor.execute(sql, data)
conn.commit()
conn.close()
def sample_commuters():
"""
Matches start and end points with a randomized order of the districts
:return:
"""
number_of_samplers = mp.cpu_count() - 1
matching_queue = mp.Queue()
logging.info('Start matching points for routes.')
logging.info('Start filling work queue.')
with connection.get_connection() as conn:
cur = conn.cursor()
'''Clear previouly sampled data'''
cur.execute('TRUNCATE de_sim_routes_outgoing_sampled; TRUNCATE de_sim_routes_within_sampled;')
'''Collecting distributions'''
cur.execute('SELECT * FROM ('
'SELECT rs, outgoing, within FROM de_commuter_gemeinden '
'UNION '
'SELECT rs, outgoing, within FROM de_commuter_kreise '
' WHERE rs NOT IN ('
' SELECT SUBSTRING(rs FOR 5) '
' FROM de_commuter_gemeinden '
' WHERE SUBSTRING(rs FROM 6) = \'0000000\')'
') matchpoints '
'ORDER BY RANDOM()')
conn.commit()
counter = Counter(cur.rowcount)
'''Start processes'''
start = time.time()
processes = []
signal.signal(signal.SIGINT, signal.SIG_IGN)
for i in range(number_of_samplers):
processes.append(SampleCommuterProcess(matching_queue, counter, sig.exit_event))
processes[-1].start()
signal.signal(signal.SIGINT, sig.signal_handler)
'''Feed the processes the work'''
[matching_queue.put(x) for x in cur.fetchall()]
# Add sentinels
for i in range(number_of_samplers):
matching_queue.put(None)
'''Wait for sampling processes to finish'''
for p in processes:
p.join()
logging.info('Runtime commuter sampling: %s', (time.time() - start))
def _queue_feeder(sql, queue, size=5000, sentinels=8):
while True:
with connection.get_connection() as conn:
cur = conn.cursor()
cur.execute(sql)
results = cur.fetchmany(size)
for rec in results:
queue.put(rec)
if sentinels > 0 and not results:
for i in range(sentinels):
queue.put(StopIteration)
break
elif not results:
break
def get_name(user_id):
conn = connection.get_connection()
sql = '''
select name from user_info
where id = %s
'''
cursor = conn.cursor()
cursor.execute(sql, user_id)
result = cursor.fetchone()
conn.close()
return result
def formDelete(userPhone, path):
conn = connection.get_connection()
sql = '''
DELETE FROM assets
WHERE user_id = %s and assets_id = %s;
'''
cursor = conn.cursor()
cursor.execute(sql, (userPhone, path))
conn.commit()
cursor.close()
return
def InDetail(userPhone):
conn = connection.get_connection()
sql = '''
select date, money, place, something, inex, category_category_id, assets_id from assets
where (inex=1 and month(date) = %s) and user_id = %s
order by date desc;
'''
cursor = conn.cursor()
cursor.execute(sql, (mm, userPhone))
ID = cursor.fetchall()
cursor.close()
return ID
def get_subInfo(user_id):
conn = connection.get_connection()
sql = '''
select time, power from sub_send
where id = %s
'''
cursor = conn.cursor()
cursor.execute(sql, user_id)
result = cursor.fetchall()
conn.close()
return result
def get_mainInfo(user_id):
conn = connection.get_connection()
sql = '''
select time, isPerson from main_send
where id = %s
'''
cursor = conn.cursor()
cursor.execute(sql, user_id)
result = cursor.fetchall()
conn.close()
return result
def write_todo(element):
conn = connection.get_connection()
sql = '''
insert into todo
(todo_content, todo_status, todo_importance)
values(%s, 1, 0)
'''
cursor = conn.cursor()
cursor.execute(sql, element)
conn.commit()
conn.close()
def delete(itemIdx):
conn = connection.get_connection()
sql = '''
update todo
set todo_status = 0
where todo_idx=%s
'''
cursor = conn.cursor()
cursor.execute(sql, itemIdx)
conn.commit()
conn.close()
def modify(item, itemIdx):
conn = connection.get_connection()
sql = '''
update todo
set todo_status = 2,
todo_content = %s
where todo_idx= %s
'''
cursor = conn.cursor()
cursor.execute(sql, (item, itemIdx))
conn.commit()
conn.close()
def subResponse(user_id):
conn = connection.get_connection()
sql = '''
select isPerson from main_send
where id = %s order by time desc limit 1
'''
cursor = conn.cursor()
cursor.execute(sql, user_id)
result = cursor.fetchone()
conn.close()
return result
def moneyIncome(userPhone):
conn = connection.get_connection()
sql = '''
select sum(money)
from assets
where (inex=1 and month(date) = %s) and user_id = %s
'''
cursor = conn.cursor()
cursor.execute(sql, (mm, userPhone))
EM = cursor.fetchall()
cursor.close()
return EM
def moneySum(userPhone):
conn = connection.get_connection()
sql = '''
select sum(case when inex=1 then money end)
- sum(case when inex=-1 then money end)
from assets
where month(date) = %s and user_id = %s
'''
cursor = conn.cursor()
cursor.execute(sql, (mm, userPhone))
MS = cursor.fetchall()
cursor.close()
print(MS)
return MS
def match_points():
"""
Matches start and end points with a randomized order of the districts
:return:
"""
number_of_matchers = 8
matching_queue = mp.Queue()
logging.info('Start matching points for routes.')
logging.info('Start filling work queue.')
with connection.get_connection() as conn:
'''Collecting distributions'''
cur = conn.cursor(cursor_factory=NamedTupleCursor)
cur.execute('SELECT * FROM ('
'SELECT rs, outgoing, within FROM de_commuter_gemeinden '
'UNION '
'SELECT rs, outgoing, within FROM de_commuter_kreise '
' WHERE rs NOT IN ('
' SELECT SUBSTRING(rs FOR 5) '
' FROM de_commuter_gemeinden '
' WHERE SUBSTRING(rs FROM 6) = \'0000000\')'
') matchpoints '
'ORDER BY RANDOM()')
conn.commit()
counter = Counter(cur.rowcount * (len(cd.commuting_distance) + 1))
'''Start processes'''
start = time.time()
processes = []
signal.signal(signal.SIGINT, signal.SIG_IGN)
for i in range(number_of_matchers):
processes.append(PointMatcherRevised(matching_queue, counter, sig.exit_event))
processes[-1].start()
signal.signal(signal.SIGINT, sig.signal_handler)
'''Feed the processes the work'''
[matching_queue.put(x) for x in cur.fetchall()]
# Add sentinels
for i in range(number_of_matchers):
matching_queue.put(None)
for p in processes:
p.join()
logging.info('Runtime Point Matching: %s', (time.time() - start))
def get_modify_content(idx):
conn = connection.get_connection()
sql = '''
select todo_content
from todo
where todo_idx = %s
'''
cursor = conn.cursor()
cursor.execute(sql, idx)
item = cursor.fetchone()
item = item[0]
conn.close()
return item
def run(self):
i = 0
start_time = time.time()
while True:
r_info = self.rq.get()
# Sentinal to stop Process
if isinstance(r_info, StopIteration):
break
else:
(r_id, start, destination) = r_info
with connection.get_connection() as conn:
cur = conn.cursor()
'''Generate route'''
sql_route = 'SELECT %(id)s AS points, seq, id FROM pgr_dijkstra( ' \
' \'SELECT id, source, target, cost FROM de_2po_4pgr, ' \
' (SELECT ST_Expand(ST_Extent(the_geom),0.1) as box FROM de_2po_4pgr_vertices_pgr ' \
' WHERE id = (SELECT id::integer FROM de_2po_4pgr_vertices_pgr ORDER BY the_geom <-> ST_Transform((SELECT geom FROM de_sim_points WHERE id =%(start)s), 4326) LIMIT 1) ' \
' OR id = (SELECT id::integer FROM de_2po_4pgr_vertices_pgr ORDER BY the_geom <-> ST_Transform((SELECT geom FROM de_sim_points WHERE id =%(dest)s), 4326) LIMIT 1) ' \
' ) as box' \
' WHERE geom_way && box.box\', ' \
' (SELECT id::integer FROM de_2po_4pgr_vertices_pgr ORDER BY the_geom <-> ST_Transform((SELECT geom FROM de_sim_points WHERE id =%(start)s), 4326) LIMIT 1), ' \
' (SELECT id::integer FROM de_2po_4pgr_vertices_pgr ORDER BY the_geom <-> ST_Transform((SELECT geom FROM de_sim_points WHERE id =%(dest)s), 4326) LIMIT 1), ' \
' false, ' \
' false) algo, ' \
' de_2po_4pgr AS r ' \
' WHERE algo.id2 = r.id'
try:
cur.execute(sql_route, {'id': r_id, 'start': start, 'dest': destination})
except InternalError as err:
self.logging.error('Error in route calculation. Code: %s "%s"', err.pgcode, err.pgerror)
conn.rollback()
else:
conn.commit()
if i >= 100:
self.logging.info('(%8d/%d) Generated route in %s',
self.counter.increment(n=i), self.counter.maximum,
time.time() - start_time)
i = 0
start_time = time.time()
else:
i += 1
self.logging.info('Exiting Route Process: %s', self.name)
def calculate_route(start, destination, route_type=RouteType.Other):
"""Calculates the route and returns its fragments
Route will be calculated from the start point, which have to be part of the generated points for the simulation, to
the given destination, also part of the generated points.
:param int start: Id of a point in table de_2po_4pgr
:param int destination: Id of a point in table de_2po_4pgr
:param simulation.routing.route.RouteType route_type: The type of the route
:return: simulation.routing.route.Route
"""
with db.get_connection() as conn:
'''Generate route'''
sql_route = 'CREATE TEMP TABLE route ON COMMIT DROP AS ' \
'SELECT seq, source, target, km, kmh, clazz, geom_way FROM ' \
' pgr_dijkstra({dijkstra_sql!r}, %(start)s, %(dest)s, false, false) route' \
' LEFT JOIN de_2po_4pgr AS info ON route.id2 = info.id'
cur = conn.cursor(cursor_factory=DictCursor)
try:
args = dict(start=start, dest=destination)
cur.execute(sql_route.format(dijkstra_sql=dijkstra_sql), args)
except Exception:
sql_log.exception(cur.query)
conn.rollback()
raise NoRouteError
cur.execute('SELECT seq, source, target, km, kmh, clazz '
'FROM route '
'WHERE source IS NOT NULL ORDER BY seq')
fragments = []
for rec in cur.fetchall():
fragments.append(
RouteFragment(rec['seq'], rec['source'], rec['target'], rec['kmh'], rec['km'], rec['clazz']))
cur.execute('SELECT ST_AsEWKB(ST_LineMerge(ST_union(geom_way))) FROM route')
line, = cur.fetchone()
cur.execute('SELECT SUM(km) FROM route')
distance, = cur.fetchone()
conn.commit()
return Route(start, destination, fragments, route_type, line, distance)
def _map(self, area, output_queue, rs, point_type, num_points, total_area):
"""Map function for ThreadPool
:param area: A psycopg2 record
:return: Number of generated points
"""
with db.get_connection() as conn:
cur = conn.cursor()
args = dict(rs=rs, area=area.geom_b, pt=point_type)
cur.execute('INSERT INTO de_sim_points_lookup (rs, geom, point_type) '
'VALUES(%(rs)s, ST_Centroid(ST_GeomFromEWKB(%(area)s)), %(pt)s) RETURNING id', args)
lookup, = cur.fetchone()
conn.commit()
execute_statement = 'EXECUTE de_sim_points_{type!s}_plan ({rs!r}, \'\\x{point!s}\'::bytea, {lookup!s});'
polygon = loads(bytes(area.geom_b))
num_points = int(round(num_points * area.area / total_area, 0))
points = self._generate_points(polygon, num_points)
[output_queue.put(execute_statement.format(rs=rs, type=point_type, point=p.wkb_hex, lookup=lookup))
for p in points]
return len(points)
def _create_index_points(table):
logging.info('Start creating Indexes for de_sim_points_%s tables', table)
with connection.get_connection() as conn:
cur = conn.cursor()
start_index = time.time()
sql = 'ALTER TABLE de_sim_points_{tbl!s} SET (FILLFACTOR=80); '
if table in ('end', 'within_end'):
sql += 'CREATE INDEX de_sim_points_{tbl!s}_rs_geom_idx ON de_sim_points_{tbl!s} ' \
' USING gist (rs COLLATE pg_catalog."default", geom) WHERE NOT used;'
else:
sql = 'CREATE INDEX de_sim_points_{tbl!s}_geom_idx ON de_sim_points_{tbl!s} USING GIST(geom);' \
'CREATE INDEX de_sim_points_{tbl!s}_rs_used_idx ON de_sim_points_{tbl!s} ' \
' USING BTREE (rs COLLATE pg_catalog."default", used DESC);'
cur.execute(sql.format(tbl=table))
conn.commit()
conn.set_isolation_level(0)
cur.execute('VACUUM ANALYSE de_sim_points_{tbl!s}'.format(tbl=table))
conn.commit()
finish_index = time.time()
logging.info('Finished creating indexes on de_sim_points_%s in %.2f', table, (finish_index - start_index))
def checkLogin(*data):
conn = connection.get_connection()
sql = '''
select id, password from user_info
where id = %s and password = %s
'''
cursor = conn.cursor()
cursor.execute(sql, data)
result = cursor.fetchone()
if result == None:
user_id = -1
else:
user_id = result[0]
conn.close()
return user_id
def getfoodloc():
conn=connection.get_connection()
sql='''select distinct food_loc
from jeju_best'''
cursor=conn.cursor()
cursor.execute(sql)
row=cursor.fetchall()
data_list=[]
for obj in row:
data_dic = {
'food_loc': obj[0]
}
data_list.append(data_dic)
conn.close()
return data_list
# # 게시판 정보를 가져온다.
# def get_board_info(board_idx) :
# conn = connection.get_connection()
#
# sql = '''
# select board_name
# from board_info_table
# where board_idx='%s'
# '''
# cursor = conn.cursor()
# cursor.execute(sql,(board_idx))
#
# result = cursor.fetchone()
#
# board_name = result[0]
#
# conn.close()
# print(board_name)
#
# return board_name
def get_machine_data_list(char1_0, char1, char1_1):
#char1_input = char1
conn = connection.get_connection()
cursor = conn.cursor()
if char1_0 == 'OP_all':
sql = '''
select machine_code,product_key,start_time,end_time,process_time,machine_data,machine_data_code,
date_format( start_time , '%%Y년%%m월%%d일 %%H시%%i분%%s초' ) as insert_date2
from machine
where date_format(start_time , '%%Y-%%m-%%d') >= '%s'
and date_format(start_time , '%%Y-%%m-%%d') <= '%s'
order by start_time ASC
''' % (char1, char1_1)
else:
sql = '''
select machine_code,product_key,start_time,end_time,process_time,machine_data,machine_data_code,
date_format( start_time , '%%Y년%%m월%%d일 %%H시%%i분%%s초' ) as insert_date2
from machine
where machine_code = '%s' and date_format(start_time , '%%Y-%%m-%%d') >= '%s'
and date_format(start_time , '%%Y-%%m-%%d') <= '%s'
order by start_time ASC
''' % (char1_0, char1, char1_1)
cursor.execute(sql)
row = cursor.fetchall()
machine_data_list = []
for obj in row:
data_dic = {
'machine_code': obj[0],
'product_key': obj[1],
'start_time': obj[2],
'end_time': obj[3],
'process_time': obj[4],
'machine_data': obj[5],
'machine_data_code': obj[6]
}
machine_data_list.append(data_dic)
conn.close
return machine_data_list
def formUpdate(userPhone, what_money, m_category, date, content, money, path):
conn = connection.get_connection()
if what_money == 'a':
what_money = -1
elif what_money == 'b':
what_money = 1
if m_category == '의류':
m_category = 1
elif m_category == '뷰티':
m_category = 2
elif m_category == '생활비':
m_category = 3
elif m_category == '여행':
m_category = 4
elif m_category == '교통비':
m_category = 5
elif m_category == '식비':
m_category = 6
elif m_category == '월급':
m_category = 7
elif m_category == '용돈':
m_category = 8
elif m_category == '기타':
m_category = 9
print(m_category)
sql = '''
UPDATE assets SET date=%s, place=%s, something=%s, money=%s, inex=%s, category_category_id =%s
where assets_id = %s;
'''
cursor = conn.cursor()
cursor.execute(sql,
(date, '', content, money, what_money, m_category, path))
conn.commit()
cursor.close()
return
def refill(self):
"""Simulates the refilling process and saves the info into the Database.
To do so the find_filling_station method has to be executed first
:raises: FillingStationError If no target filling station was set before
"""
if not self._target_station:
raise NoFillingStationError('No filling station_id was set. Can not refill. Commuter %s' % self.env.commuter.id)
with db.get_connection() as conn:
cur = conn.cursor(cursor_factory=RealDictCursor)
sql = 'SELECT * FROM (VALUES ({value!r})) s(station_id) LEFT JOIN LATERAL (' \
' SELECT diesel, e5, e10 FROM de_tt_priceinfo ' \
' WHERE station_id = s.station_id AND received <= %(now)s LIMIT 1' \
') p ON TRUE'.format(value=self._target_station)
args = dict(now=self.env.now)
cur.execute(sql, args)
result = cur.fetchone()
conn.commit()
if result[self.env.car.fuel_type]:
price = result[self.env.car.fuel_type]
else:
price = self.calculate_proxy_price(self.env.car.fuel_type)
refill_amount = self.env.car.tank_size - self.env.car.current_filling
# add to the result of the simulation
self.env.result.add_refill(
self.env.commuter.id,
self.env.rerun,
refill_amount,
price,
self.env.now,
self._target_station,
self.env.car.fuel_type
)
self.env.car.refilled() # Car has been refilled.
self._target_station = None # Since the station has been used reset it.
def calculate_proxy_price(self, fuel_type):
"""
Calculates a proxy price if the station selected has no price yet
:param fuel_type: The type of fuel (e5, e10, diesel) to calculate the proxy value for
:type fuel_type: str
:returns: Proxy value for given fuel type
:rtype: float
:raises: NoPriceError
"""
if not self._target_station:
raise NoFillingStationError('No target filling station set. Can not calculate proxy price. Commuter %s' % self.env.commuter.id)
sql = 'SELECT AVG(e5) AS e5, AVG(e10) AS e10, AVG(diesel) as diesel FROM ( ' \
' SELECT id ' \
' FROM de_tt_stations ' \
' ORDER BY geom <-> (SELECT geom ' \
' FROM de_tt_stations ' \
' WHERE id = %(station_id)s) ' \
' LIMIT 5 ' \
' ) s(station_id) ' \
'LEFT JOIN LATERAL ( ' \
' SELECT e5, e10, diesel, received ' \
' FROM de_tt_priceinfo ' \
' WHERE station_id = s.station_id ' \
' AND received <= %(received)s ' \
' ORDER BY received DESC ' \
' LIMIT 1 ' \
' ) p ON TRUE;'
with db.get_connection() as conn:
cur = conn.cursor(cursor_factory=RealDictCursor)
args = dict(received=self.env.now, station_id=self._target_station)
cur.execute(sql, args)
result = cur.fetchone()
conn.commit()
if result[fuel_type]:
return result[fuel_type]
else:
raise NoPriceError('No proxy value found')
def route_home(env):
"""Alias for calculate_route with pre set start and destination points
:param env: Simulation environment
:type env: simulation.environment.SimulationEnvironment
"""
with db.get_connection() as conn:
sql = 'WITH info AS (SELECT end_point AS start, start_point AS dest FROM de_sim_routes WHERE id = %(id)s) ' \
'SELECT s.id, d.id FROM ' \
' (SELECT id::integer FROM de_2po_vertex ORDER BY geom_vertex <-> ' \
' (SELECT geom FROM de_sim_points WHERE id = (SELECT start FROM info)) LIMIT 1) AS s, ' \
' (SELECT id::integer FROM de_2po_vertex ORDER BY geom_vertex <-> ' \
' (SELECT geom FROM de_sim_points WHERE id = (SELECT dest FROM info)) LIMIT 1) AS d '
cur = conn.cursor()
try:
cur.execute(sql, dict(id=env.commuter.id))
except Exception:
sql_log.exception(cur.query)
conn.rollback()
raise NoRoutingPointsError
else:
start, destination = cur.fetchone()
route = calculate_route(start, destination, RouteType.Home)
_save_route_info(env, route)
return route
def run(self):
while True:
'''Flow control'''
# Get a MatchingDistribution
try:
work = self.mq.get(timeout=1)
except Empty:
continue
finally:
if not work or self.exit_event.is_set():
break
rs, outgoing, within = work
'''Start sampling'''
self.logging.debug('Start matching points for: %12s', rs)
start_time = time.time()
with connection.get_connection() as conn:
cur = conn.cursor()
result = []
'''Outgoing sampling'''
temp_sql = 'CREATE TEMPORARY TABLE possible_commuters(i, commuter) ON COMMIT DROP AS ' \
'SELECT row_number() OVER () as i, r.id as commuter ' \
'FROM de_sim_points_start p ' \
' LEFT JOIN de_sim_routes_outgoing r ' \
' ON p.id=r.start_point ' \
' AND r.distance_meter BETWEEN %(min_d)s AND %(max_d)s ' \
'WHERE p.rs = %(rs)s AND p.used AND r.start_point IS NOT NULL '
sample_sql = 'INSERT INTO de_sim_routes_outgoing_sampled ' \
'SELECT commuter ' \
'FROM possible_commuters ' \
'WHERE i IN ( ' \
' SELECT round(random() * (SELECT COUNT(i) FROM possible_commuters)) :: INTEGER AS n ' \
' FROM generate_series(1, (1.5 * %(limit)s)::integer) ' \
' GROUP BY n ' \
') ' \
'LIMIT %(limit)s'
for i, distances in enumerate(commuting_distance):
args = dict(rs=rs, **distances)
cur.execute(temp_sql, args)
cur.execute('SELECT COUNT(*) FROM possible_commuters')
commuters, = cur.fetchone()
if distributed_commuters[rs[:2]][i] < commuters:
commuters = distributed_commuters[rs[:2]][i]
cur.execute(sample_sql, dict(limit=commuters))
try:
result.append((cur.rowcount / commuters))
except ZeroDivisionError:
result.append(0.0)
conn.commit()
'''Within sampling'''
sql = 'WITH possible_commuters AS ( ' \
' SELECT row_number() OVER () as i, r.id as commuter ' \
' FROM de_sim_points_within_start p ' \
' LEFT JOIN de_sim_routes_within r ' \
' ON p.id=r.start_point ' \
' WHERE p.rs = %(rs)s AND p.used AND r.start_point IS NOT NULL ' \
') ' \
'INSERT INTO de_sim_routes_within_sampled ' \
'SELECT commuter FROM possible_commuters WHERE i IN ( ' \
' SELECT round(random() * (SELECT COUNT(i) FROM possible_commuters)) :: INTEGER AS n ' \
' FROM generate_series(1, (1.5 * %(limit)s)::integer) ' \
' GROUP BY n ' \
') ' \
'LIMIT %(limit)s'
args = dict(rs=rs, limit=n)
cur.execute(sql, args)
result.append(cur.rowcount / n)
conn.commit()
count = self.counter.increment()
self.logging.info('(%4d/%d) Finished sampling for %12s in %.2f with: %s ',
count, self.counter.maximum,
rs,
time.time() - start_time,
', '.join(['{:.2%}'.format(x) for x in result]))
self.logging.info('Exiting sampling process: %s', self.name)
def _lookup_match(self, lookup_id, rs, p_start, p_end):
if p_start is PointType.Start:
reachable = 'SELECT id FROM de_sim_points_lookup WHERE point_type = %(end_type)s AND rs != %(rs)s ' \
'AND NOT ST_DWithin(geom::geography, (SELECT geom::geography FROM de_sim_points_lookup WHERE id = %(lookup)s), %(min_d)s)' \
'AND ST_DWithin(geom::geography, (SELECT geom::geography FROM de_sim_points_lookup WHERE id = %(lookup)s), %(max_d)s) ' \
'ORDER BY RANDOM() LIMIT 10000'
tbl_r = 'outgoing'
limit = 'ROUND((SELECT * FROM amount) * %(percent)s)'
elif p_start is PointType.Within_Start:
reachable = 'SELECT id FROM de_sim_points_lookup WHERE point_type = %(end_type)s AND rs = %(rs)s ' \
'AND NOT ST_DWithin(geom::geography, (SELECT geom::geography FROM de_sim_points_lookup WHERE id = %(lookup)s), %(min_d)s)'
tbl_r = 'within'
limit = '(SELECT * FROM amount)'
else:
logging.error('No PointType was given')
return
sql = 'WITH reachable AS ({reachable!s}), ' \
' amount AS (SELECT COUNT(*) FROM de_sim_points_{tbl_s!s} WHERE lookup = %(lookup)s),' \
' points AS (SELECT s.id AS start, e.id AS destination FROM ( ' \
' SELECT id, row_number() over() as i FROM ( ' \
' SELECT id ' \
' FROM de_sim_points_{tbl_s!s} ' \
' WHERE lookup = %(lookup)s AND NOT used' \
' ORDER BY RANDOM() ' \
' LIMIT {limit!s}' \
' FOR UPDATE SKIP LOCKED' \
' ) AS sq ' \
' ) AS s ' \
' INNER JOIN ( ' \
' SELECT id, row_number() over() as i FROM ( ' \
' SELECT id ' \
' FROM de_sim_points_{tbl_e!s} ' \
' WHERE NOT used AND lookup IN (SELECT * FROM reachable)' \
' ORDER BY RANDOM()' \
' LIMIT {limit!s}' \
' FOR UPDATE SKIP LOCKED' \
' ) AS t ' \
' ) AS e ON s.i = e.i),' \
' upsert_start AS (UPDATE de_sim_points_{tbl_s!s} ps SET used = true FROM points p WHERE p.start = ps.id), ' \
' upsert_destination AS (UPDATE de_sim_points_{tbl_e!s} pe SET used = true FROM points p WHERE p.destination = pe.id) ' \
'INSERT INTO de_sim_routes_{tbl_r!s} (start_point, end_point) SELECT start, destination FROM points'
sql = sql.format(tbl_s=p_start.value, tbl_e=p_end.value, tbl_r=tbl_r, reachable=reachable, limit=limit)
updated = 0
if p_start is PointType.Start:
for distances, percent in zip(commuting_distance, commuter_distribution[rs[:2]]):
args = dict(rs=rs, lookup=lookup_id, end_type=p_end.value, percent=percent)
args.update(distances)
with db.get_connection() as conn:
cur = conn.cursor()
logging.info(
cur.mogrify(sql, args)
)
#cur.execute(sql, args)
conn.commit()
updated = cur.rowcount
elif p_start is PointType.Within_Start:
args = dict(end_type=p_end.value, min_d=2000, lookup=lookup_id, rs=rs)
with db.get_connection() as conn:
cur = conn.cursor()
#cur.execute(sql, args)
logging.info(
cur.mogrify(sql, args)
)
conn.commit()
updated = cur.rowcount
return updated
def run(self):
while True:
generation_start = time.time()
try:
cmd = self.queue.get(timeout=0.5)
except Empty:
continue
else:
if not cmd:
break
finally:
if self.exit_event.is_set():
break
assert isinstance(cmd, PointCreationCommand)
# Choose the right sql based on the point type
landuse = '\'residential\''
if cmd.point_type in (PointType.End.value, PointType.Within_End.value):
landuse += ', \'industrial\', \'commercial\', \'retail\''
# Choose right de_shp table based on rs
if len(cmd.rs) is 12:
shp = 'gemeinden'
else:
shp = 'kreise'
# Replace this polygon with a query from the database with the residential areas
with db.get_connection() as conn:
cur = conn.cursor(cursor_factory=NamedTupleCursor)
sql = 'CREATE TEMPORARY TABLE areas ON COMMIT DROP AS ' \
'SELECT ' \
' CASE WHEN ST_Intersects(p.way , s.geom) THEN ' \
' ST_Intersection(p.way ,s.geom) ' \
' ELSE p.way END AS geom, ' \
' p.landuse as landuse, ' \
' 0::double precision AS area ' \
'FROM de_osm_polygon p ' \
'INNER JOIN de_shp_{shp!s} s ON (s.rs = %(rs)s AND (ST_Within(p.way, s.geom) OR ST_Intersects(p.way , s.geom))) ' \
'WHERE landuse IN ({landuse!s})'
args = dict(rs=cmd.rs, w=0.15)
cur.execute(sql.format(shp=shp, landuse=landuse), args) # created temporary table
# Update areas temp table with correct area
if cmd.point_type in (PointType.End.value, PointType.Within_End.value):
sql = 'UPDATE areas SET area = CASE WHEN landuse = \'residential\' THEN (%(w)s*ST_Area(geom)) ELSE ST_Area(geom) END'
else:
sql = 'UPDATE areas SET area = ST_Area(geom)'
cur.execute(sql, args)
sql = 'SELECT SUM(area) FROM areas'
cur.execute(sql)
total_area, = cur.fetchone()
sql = 'SELECT ST_AsEWKB(geom) AS geom_b, area FROM areas'
cur.execute(sql)
areas = cur.fetchall()
#pool = ThreadPool(4)
_map_partial = partial(self._map, output_queue=self.output, rs=cmd.rs, point_type=cmd.point_type,
num_points=cmd.num_points, total_area=total_area)
#created_points = pool.map(_map_partial, areas)
#pool.close()
#pool.join()
try:
created_points = [_map_partial(area) for area in areas]
except ValueError as err:
created_points = []
self.logging.error(err)
self.logging.error('rs: %s, landuse: %s, point_type: %s', cmd.rs, landuse, cmd.point_type)
generation_time = time.time() - generation_start
num = self.counter.increment()
self.logging.info('(%4d/%d) %s: Created %6s/%6s points for "%s". Generation time: %.2f sec.',
num, self.total,
self.name, sum(created_points), cmd.num_points, cmd.name,
generation_time)
self.logging.info('Exiting %s', self.name)
if self.exit_event.is_set():
self.logging.warn('Cleaning %d elements from Queue ... ', self.queue.qsize())
while not self.queue.empty():
self.queue.get()
def create_points():
logging.info('Start creation of points')
logging.info('Start filling work queue')
signal.signal(signal.SIGINT, signal.SIG_IGN)
work_queue = mp.Queue()
insert_queue = mp.Queue()
number_of_processes = 7
def add_command(rec):
"""Function to be used by map() to create commands for the work_queue
:param rec: A database record with named tuple having
(rs, name, geom_b, area, total_area, incomming, outgoing, within)
"""
commuters = [rec.outgoing, rec.incoming, rec.within, rec.within]
[work_queue.put(PointCreationCommand(rec.rs, rec.name, amount, p_type.value))
for amount, p_type in zip(commuters, PointType)]
start = time.time()
with connection.get_connection() as con:
logging.info('Executing query for Gemeinden ...')
cur = con.cursor(cursor_factory=NamedTupleCursor)
sql = 'SELECT c.rs AS rs, s.gen AS name, c.incoming AS incoming, c.within AS within, c.outgoing AS outgoing ' \
'FROM de_commuter_gemeinden c ' \
'LEFT JOIN LATERAL (SELECT gen FROM de_shp_gemeinden WHERE rs = c.rs LIMIT 1) s ON TRUE'
cur.execute(sql)
con.commit()
[add_command(rec) for rec in cur.fetchall()]
logging.info('Executing query for Kreise ...')
sql = 'SELECT ck.rs AS rs, k.gen AS name, ' \
'(ck.incoming-COALESCE(sums.incoming, 0)) AS incoming, ' \
'(ck.outgoing-COALESCE(sums.outgoing, 0)) AS outgoing, ' \
'(ck.within-COALESCE(sums.within, 0)) AS within ' \
'FROM de_commuter_kreise ck ' \
'LEFT JOIN LATERAL (' \
' SELECT gen FROM de_shp_kreise WHERE rs = ck.rs LIMIT 1' \
') k ON TRUE ' \
'LEFT JOIN LATERAL (' \
' SELECT ' \
' SUBSTRING(rs FOR 5) AS rs, ' \
' SUM(incoming) AS incoming, ' \
' SUM(within) AS within, ' \
' SUM(outgoing) AS outgoing ' \
' FROM de_commuter_gemeinden ' \
' WHERE SUBSTRING(rs FOR 5) = ck.rs ' \
' GROUP BY SUBSTRING(rs FOR 5)' \
') sums ON TRUE ' \
'WHERE (ck.incoming-COALESCE(sums.incoming, 0)) > 0 ' \
' AND (ck.outgoing-COALESCE(sums.outgoing, 0)) > 0 ' \
' AND (ck.within-COALESCE(sums.within, 0)) > 0'
cur.execute(sql)
con.commit()
[add_command(rec) for rec in cur.fetchall()]
logging.info('Finished filling work queue. Time: %s', time.time() - start)
processes = []
counter = Counter(work_queue.qsize())
signal.signal(signal.SIGINT, signal.SIG_IGN)
for i in range(number_of_processes):
work_queue.put(None) # Add Sentinel for each process
p = PointCreatorProcess(work_queue, insert_queue, counter, sig.exit_event)
p.set_t(1.75)
processes.append(p)
# SQL inserting process
plans = ['PREPARE de_sim_points_start_plan (varchar, geometry, integer) AS '
'INSERT INTO de_sim_points_start (rs, geom, lookup) '
'VALUES($1, ST_GeomFromWKB(ST_SetSRID($2, 25832)), $3)',
'PREPARE de_sim_points_within_start_plan (varchar, geometry, integer) AS '
'INSERT INTO de_sim_points_within_start (rs, geom, lookup) '
'VALUES($1, ST_GeomFromWKB(ST_SetSRID($2, 25832)), $3)',
'PREPARE de_sim_points_end_plan (varchar, geometry, integer) AS '
'INSERT INTO de_sim_points_end (rs, geom, lookup) '
'VALUES($1, ST_GeomFromWKB(ST_SetSRID($2, 25832)), $3)',
'PREPARE de_sim_points_within_end_plan (varchar, geometry, integer) AS '
'INSERT INTO de_sim_points_within_end (rs, geom, lookup) '
'VALUES($1, ST_GeomFromWKB(ST_SetSRID($2, 25832)), $3)']
insert_process = PointInsertingProcess(insert_queue, plans, sig.exit_event)
insert_process.set_batch_size(10000)
insert_process.set_insert_threads(1)
insert_process.start()
start = time.time()
for p in processes:
p.start()
signal.signal(signal.SIGINT, sig.signal_handler)
for p in processes:
p.join()
sig.exit_event.set()
insert_process.join()
logging.info('JOINT!')
logging.info('Creating Indexes for Tables...')
args = [(_create_index_points, p.value) for p in PointType]
sqls = ('CREATE INDEX de_sim_points_lookup_geom_idx ON de_sim_points_lookup USING GIST (geom); '
' CLUSTER de_sim_points_lookup USING de_sim_points_lookup_geom_idx',
'CREATE INDEX de_sim_points_lookup_rs_idx ON de_sim_points_lookup USING BTREE (rs)',
'CREATE INDEX de_sim_points_lookup_point_type_idx ON de_sim_points_lookup USING BTREE (point_type)')
args += [(connection.run_commands, s) for s in sqls]
with concurrent.futures.ThreadPoolExecutor(max_workers=8) as executor:
result = [executor.submit(a[0], a[1]) for a in args]
result = concurrent.futures.wait(result)
if len(result.not_done) is not 0:
for f in result.not_done:
logging.error(f.exception())
logging.info("Finished creating Indexes.")
end = time.time()
logging.info('Runtime Point Creation: %s', (end - start))
def run(self):
upsert_match_info = 'WITH ' \
' vals(rs, max_d, min_d, done, total) AS (VALUES (%(rs)s, %(max_d)s, %(min_d)s, %(done)s, %(limit)s)),' \
' upsert AS ( ' \
' UPDATE de_sim_data_matching_info ' \
' SET done = %(done)s, total = %(limit)s ' \
' WHERE rs = %(rs)s AND max_d = %(max_d)s AND min_d = %(min_d)s ' \
' RETURNING rs, max_d, min_d ' \
' ) ' \
'INSERT INTO de_sim_data_matching_info ' \
' SELECT * FROM vals WHERE NOT EXISTS(' \
' SELECT 1 FROM upsert WHERE rs = %(rs)s AND max_d = %(max_d)s AND min_d = %(min_d)s)'
while True:
try:
md = self.q.get(timeout=1)
except Empty:
if self.exit_event.is_set():
break
else:
continue
else:
if not md or self.exit_event.is_set():
break
assert isinstance(md, MatchingDistributionRevised)
start_time = time.time()
sql = 'WITH ' \
' points AS ({points!s}), ' \
' upsert_start AS (' \
' UPDATE de_sim_points_{tbl_s!s} ps SET used = TRUE FROM points p WHERE p.start = ps.id),' \
' upsert_destination AS (' \
' UPDATE de_sim_points_{tbl_e!s} pe SET used = TRUE FROM points p WHERE p.destination = pe.id)' \
'INSERT INTO de_sim_routes_{tbl_r!s} (start_point, end_point, distance_meter) ' \
' SELECT start, destination, distance_meter FROM points; '
if md.matching_type is MatchingType.outgoing:
tbl_s = 'start'
tbl_e = 'end'
tbl_r = 'outgoing'
points = 'SELECT ' \
' s.id AS start, ' \
' e.id AS destination, ' \
' ST_Distance(s.geom, e.geom) as distance_meter ' \
'FROM ( ' \
' SELECT id, geom, row_number() OVER () AS i ' \
' FROM ( ' \
' SELECT id, geom ' \
' FROM de_sim_points_start ' \
' WHERE ' \
' NOT used ' \
' AND rs = %(rs)s ' \
' ORDER BY RANDOM() ' \
' LIMIT %(limit)s ' \
' FOR UPDATE SKIP LOCKED ' \
' ) AS sq ' \
') AS s ' \
'INNER JOIN ( ' \
' SELECT id, geom, row_number() OVER () AS i ' \
' FROM ( ' \
' SELECT id, geom ' \
' FROM de_sim_points_end ' \
' WHERE ' \
' NOT used ' \
' AND rs IN ( ' \
' SELECT sk.rs ' \
' FROM de_commuter_kreise ck ' \
' INNER JOIN de_shp_kreise sk ' \
' ON sk.rs = ck.rs AND ST_DWithin((SELECT ST_Union(geom) FROM de_shp WHERE rs = %(rs)s), sk.geom, %(max_d)s) ' \
' UNION ' \
' SELECT cg.rs ' \
' FROM de_commuter_gemeinden cg ' \
' INNER JOIN de_shp_gemeinden sg ' \
' ON sg.rs = cg.rs AND ST_DWithin((SELECT ST_Union(geom) FROM de_shp WHERE rs = %(rs)s), sg.geom, %(max_d)s) ' \
' ) ' \
' LIMIT %(limit)s ' \
' FOR UPDATE SKIP LOCKED ' \
' ) AS t ' \
') AS e ON s.i = e.i AND NOT ST_DWithin(s.geom, e.geom, %(min_d)s) ' \
' AND ST_DWithin(s.geom, e.geom, %(max_d)s) '
else:
tbl_s = 'within_start'
tbl_e = 'within_end'
tbl_r = 'within'
points = 'SELECT ' \
' s.id AS start, ' \
' e.id AS destination, ' \
' ST_Distance(s.geom, e.geom) as distance_meter ' \
'FROM ( ' \
' SELECT id, geom, row_number() OVER () AS i ' \
' FROM ( ' \
' SELECT id, geom ' \
' FROM de_sim_points_within_start ' \
' WHERE ' \
' NOT used ' \
' AND rs = %(rs)s ' \
' ORDER BY RANDOM() ' \
' LIMIT %(limit)s ' \
' FOR UPDATE SKIP LOCKED ' \
' ) AS sq ' \
') AS s ' \
'INNER JOIN ( ' \
' SELECT id, geom, row_number() OVER () AS i ' \
' FROM ( ' \
' SELECT id, geom ' \
' FROM de_sim_points_within_end ' \
' WHERE ' \
' NOT used ' \
' AND rs = %(rs)s '\
' LIMIT %(limit)s ' \
' FOR UPDATE SKIP LOCKED ' \
' ) AS t ' \
') AS e ON s.i = e.i AND NOT ST_DWithin(s.geom, e.geom, %(min_d)s) '
sql = sql.format(tbl_e=tbl_e, tbl_s=tbl_s, tbl_r=tbl_r, points=points)
with db.get_connection() as conn:
cur = conn.cursor()
args = dict(rs=md.rs, max_d=md.max_d, min_d=md.min_d, limit=md.commuter)
cur.execute(sql, args)
updated = cur.rowcount
args['done'] = updated
cur.execute(upsert_match_info, args)
conn.commit()
rows = cur.rowcount
time.sleep(0.0001 * rows)
count = self.counter.increment()
self.log.info('(%5d/%d) Finished matching %8s (min: %6d, max: %6d) %6d/%6d points for %12s in %.2f',
count, self.counter.maximum, md.matching_type.value.ljust(8),md.min_d, md.max_d,
updated, md.commuter, md.rs, time.time() - start_time)
def match_points():
"""
Matches start and end points with a randomized order of the districts
:return:
"""
number_of_matchers = mp.cpu_count()
matching_queue = mp.Queue()
logging.info('Start matching points for routes.')
logging.info('Start filling work queue.')
with connection.get_connection() as conn:
'''Collecting distributions'''
cur = conn.cursor(cursor_factory=NamedTupleCursor)
cur.execute('SELECT * FROM ('
'SELECT rs, outgoing, within FROM de_commuter_gemeinden '
'UNION '
'SELECT rs, outgoing, within FROM de_commuter_kreise '
' WHERE rs NOT IN ('
' SELECT SUBSTRING(rs FOR 5) '
' FROM de_commuter_gemeinden '
' WHERE SUBSTRING(rs FROM 6) = \'0000000\')'
') matchpoints '
'ORDER BY RANDOM()')
conn.commit()
counter = Counter(cur.rowcount * (len(cd.commuting_distance) + 1))
distributions = [[] for i in range(len(cd.commuting_distance))] # Empty List with lists
'''Perform matching'''
start = time.time()
processes = []
signal.signal(signal.SIGINT, signal.SIG_IGN)
for i in range(number_of_matchers):
processes.append(PointMatcherRevised(matching_queue, counter, sig.exit_event))
processes[-1].start()
signal.signal(signal.SIGINT, sig.signal_handler)
rerun_sql = 'SELECT done, total FROM de_sim_data_matching_info WHERE rs=%(rs)s AND max_d=%(max_d)s AND min_d=%(min_d)s'
records = cur.fetchall()
for rec in records:
args = dict(rs=rec.rs, max_d=-1, min_d=2000)
cur.execute(rerun_sql, args)
info = cur.fetchone()
conn.commit()
if info:
within_md = MatchingDistributionRevised(rec.rs, info.total-info.done, MatchingType.within, 2000, -1)
else:
within_md = MatchingDistributionRevised(rec.rs, rec.within, MatchingType.within, 2000, -1)
matching_queue.put(within_md)
for i, d in enumerate(cd.commuting_distance):
args = dict(rs=rec.rs, max_d=d['max_d'], min_d=d['min_d'])
cur.execute(rerun_sql, args)
info = cur.fetchone()
conn.commit()
if info:
md = MatchingDistributionRevised(rec[0], info.total-info.done, MatchingType.outgoing, d['min_d'], d['max_d'])
else:
amount = int(math.floor(cd.commuter_distribution[rec.rs[:2]][i] * rec.outgoing))
md = MatchingDistributionRevised(rec[0], amount, MatchingType.outgoing, d['min_d'], d['max_d'])
distributions[i].append(md)
# Add distributions to queue
[[matching_queue.put(x) for x in mds] for mds in distributions]
# Add sentinels
for i in range(number_of_matchers):
matching_queue.put(None)
# Empty Queue if a exit event was set
if sig.exit_event.is_set():
while not matching_queue.empty():
matching_queue.get()
for p in processes:
p.join()
end = time.time()
logging.info('Runtime Point Matching: %s', (end - start))
def run(self):
while not self.mq.empty():
'''Flow control'''
if self.exit_event.is_set():
break
# Get a MatchingDistribution
work = self.mq.get()
# received a sentinel?
if not work:
break
else:
rs, outgoing, within = work
'''Start matching'''
self.logging.debug('Start matching points for: %12s', rs)
start_time = time.time()
if sum(distributed_commuters[rs[:2]]) < outgoing:
out_distribution = tuple([round(x * outgoing) for x in commuter_distribution[rs[:2]]])
else:
out_distribution = distributed_commuters[rs[:2]]
with connection.get_connection() as conn:
cur = conn.cursor()
# ----- for reference ----- #
# Initialize arguments for MatchingDistribution update
within = 0
outgoing = []
updated = 0
with connection.get_connection() as conn:
cur = conn.cursor()
for params in md:
# Check if there are commuters to be matched, otherwise look at the next set
if params['commuters'] == 0:
continue
# Select the type of matching to be done
if params['type'] is MatchingType.within:
'''Match within'''
sql = 'SELECT s.id AS start, e.id AS destination ' \
'FROM ( ' \
' SELECT id, geom, row_number() over() as i FROM ( ' \
' SELECT id, geom ' \
' FROM de_sim_points_{tbl_s!s} ' \
' WHERE rs {cf!s} AND NOT used' \
' ORDER BY RANDOM() ' \
' FOR UPDATE SKIP LOCKED' \
' ) AS sq ' \
') AS s ' \
'INNER JOIN ( ' \
' SELECT id, geom, row_number() over() as i FROM ( ' \
' SELECT id, geom ' \
' FROM de_sim_points_{tbl_e!s} ' \
' WHERE rs {cf!s} AND NOT used' \
' ORDER BY RANDOM() ' \
' FOR UPDATE SKIP LOCKED' \
' ) AS t ' \
') AS e ' \
'ON s.i = e.i AND NOT ST_DWithin(s.geom::geography, e.geom::geography, %(min_d)s)'
tbl_s = 'within_start'
tbl_e = 'within_end'
tbl_r = 'within'
cf = '= %(rs)s'
else:
'''Matching outgoing'''
sql = 'SELECT s.id AS start, e.id AS destination ' \
'FROM ( ' \
' SELECT id, geom, row_number() over() as i FROM ( ' \
' SELECT id, geom ' \
' FROM de_sim_points_{tbl_s!s} ' \
' WHERE NOT used AND rs = %(rs)s' \
' ORDER BY RANDOM() ' \
' LIMIT %(commuters)s ' \
' FOR UPDATE SKIP LOCKED' \
' ) AS sq ' \
') AS s ' \
'INNER JOIN ( ' \
' SELECT id, geom, row_number() over() as i ' \
' FROM ( ' \
' SELECT id, geom ' \
' FROM de_sim_points_{tbl_e!s} ' \
' WHERE NOT used AND rs {cf!s}' \
' ORDER BY RANDOM() ' \
' LIMIT %(commuters)s ' \
' FOR UPDATE SKIP LOCKED' \
' ) AS t ' \
') AS e ' \
'ON s.i = e.i ' \
'WHERE NOT ST_DWithin(s.geom::geography, e.geom::geography, %(min_d)s) ' \
' AND ST_DWithin(s.geom::geography, e.geom::geography, %(max_d)s)'
cf = 'SELECT sk.rs FROM de_commuter_kreise ck ' \
'INNER JOIN de_shp_kreise sk ON sk.rs=ck.rs AND ST_DWithin(' \
' (SELECT ST_Union(geom) FROM de_shp_kreise WHERE rs = %(rs)s)::geography, sk.geom::geography, %(max_d)s) ' \
'UNION ' \
'SELECT cg.rs FROM de_commuter_gemeinden cg ' \
'INNER JOIN de_shp_gemeinden sg ON sg.rs=cg.rs AND ST_DWithin(' \
' (SELECT ST_Union(geom) FROM de_shp_gemeinden WHERE rs = %(rs)s)::geography, sg.geom::geography, %(max_d)s)'
tbl_s = 'start'
tbl_e = 'end'
tbl_r = 'outgoing'
cf = 'IN (' + cf + ')'
# Match the points and save them
upsert = 'WITH points AS (' + sql + '), ' \
'upsert_start AS (UPDATE de_sim_points_{tbl_s!s} ps SET used = true FROM points p WHERE p.start = ps.id), ' \
'upsert_destination AS (UPDATE de_sim_points_{tbl_e!s} pe SET used = true FROM points p WHERE p.destination = pe.id) ' \
'INSERT INTO de_sim_routes_{tbl_r!s} (start_point, end_point, distance) SELECT start, destination FROM points'
cur.execute(upsert.format(tbl_e=tbl_e, tbl_s=tbl_s, cf=cf, tbl_r=tbl_r), params)
conn.commit()
# Update MatchingDistribution arguments
updated += cur.rowcount
commuters = params['commuters'] - cur.rowcount
if params['type'] is MatchingType.within:
within = commuters
else:
outgoing.append(commuters)
# Check if MatchingDistribution has reached its max age (+1 since it has already been processed)
if md.age + 1 < self.max_age_distribution and sum(outgoing) + within > 0:
md.reuse(within, outgoing)
self.mq.put(md)
count = self.counter.increment()
self.logging.info('(%4d/%d) Finished matching %6d points for %12s in %.2f',
count, self.counter.maximum, updated,
md.rs, time.time() - start_time)
self.logging.info('Exiting Matcher Tread: %s', self.name)
