def getSqlData(db_cursor=None):
if db_cursor is None:
from scripts.db.db import Database
password = os.environ.get('SIRIUS_PASSWORD')
if password is None:
raise Exception('No database password available')
db = Database(password)
cur = db.get_cur()
else:
cur = db_cursor
cur.execute("""SELECT floor_id,marauder_accesspoint.location_id, x_coordinate, y_coordinate, direction,
array_to_string(array_agg(mac_address),',') as MAC_list,
array_to_string(array_agg(signal_strength),',') as strength_list
from marauder_accesspoint
join marauder_location
on marauder_location.id=marauder_accesspoint.location_id
group by floor_id,marauder_accesspoint.location_id,x_coordinate,y_coordinate,direction""")
access_points = cur.fetchall()
res = []
for f in access_points:
msg = {
'floor_id': f[0],
'location_id': f[1],
'x': f[2],
'y': f[3],
'direction': f[4],
'macs': f[5].split(','),
'rss': map(float, f[6].split(','))
}
res.append(msg)
return res
def __init__(self, db_suffix, ways_table, filter_ways):
self.db_suffix = db_suffix
db = Database(self.db_suffix)
self.ways_table = ways_table
self.filter_ways = filter_ways
self.batch_size = 200
self.elevs_interval = 25
conn = db.connect()
cur = conn.cursor()
cur.execute('''SELECT count(*) FROM {0} {1};'''.format(
self.ways_table, self.filter_ways))
self.total_cnt = cur.fetchall()[0][0]
cur.execute(
'''SELECT select_from_variable_container_s('one_meter_degree');''')
self.meter_degree = float(cur.fetchall()[0][0])
cur.execute('''SELECT id FROM {0} {1};'''.format(
self.ways_table, self.filter_ways))
ids = cur.fetchall()
self.ids = [x[0] for x in ids]
conn.close()
def getSqlData(db_cursor=None):
if db_cursor is None:
from scripts.db.db import Database
password = os.environ.get('SIRIUS_PASSWORD')
if password is None:
raise Exception('No database password available')
db = Database(password)
cur = db.get_cur()
else:
cur = db_cursor
cur.execute(
"""SELECT floor_id,marauder_accesspoint.location_id, x_coordinate, y_coordinate, direction,
array_to_string(array_agg(mac_address),',') as MAC_list,
array_to_string(array_agg(signal_strength),',') as strength_list
from marauder_accesspoint
join marauder_location
on marauder_location.id=marauder_accesspoint.location_id
group by floor_id,marauder_accesspoint.location_id,x_coordinate,y_coordinate,direction"""
)
access_points = cur.fetchall()
res = []
for f in access_points:
msg = {
'floor_id': f[0],
'location_id': f[1],
'x': f[2],
'y': f[3],
'direction': f[4],
'macs': f[5].split(','),
'rss': map(float, f[6].split(','))
}
res.append(msg)
return res
def create_export_table(self):
db = Database(self.db_suffix)
conn = db.connect()
cur = conn.cursor()
sql_merge_tables = '''DROP TABLE IF EXISTS slope_profile_{0};
CREATE TABLE slope_profile_{0} AS
SELECT e.*, i.s_imp, i.rs_imp, s.slope, w.geom
FROM elevs_{0} e
LEFT JOIN average_slopes_{0} s
ON e.id = s.id
LEFT JOIN impedances_{0} i
ON e.id = i.id
LEFT JOIN {0} w
ON e.id = w.id;'''.format(self.ways_table)
cur.execute(sql_merge_tables)
cur.execute(
'ALTER TABLE slope_profile_{0} ADD PRIMARY KEY (id);'.format(
self.ways_table))
cur.execute(
'CREATE INDEX ON slope_profile_{0} USING GIST(geom);'.format(
self.ways_table))
conn.commit()
conn.close()
def elevation_profile(self):
db = Database(self.db_suffix)
conn = db.connect()
cur = conn.cursor()
sql_create_table = '''
DROP TABLE IF EXISTS elevs_{0};
CREATE TABLE elevs_{0}
(
id bigint,
elevs float[],
elevs_interval float,
length_m float
);'''.format(self.ways_table)
cur.execute(sql_create_table)
conn.commit()
cnt = 0
for i in self.ids:
cnt = cnt + 1
if (cnt / self.batch_size).is_integer():
print('Slope profile for %s out of %s lines' %
(cnt, self.total_cnt))
conn.commit()
sql_elevs = '''
INSERT INTO elevs_{0}(id, elevs, elevs_interval, length_m)
WITH way AS
(
SELECT id, geom, length_m
FROM {0}
WHERE id = {1}
)
SELECT w.id, s.elevs, {2} elevs_interval, w.length_m
FROM way w,
LATERAL (
SELECT ARRAY_AGG(elev) elevs
FROM get_elevation_profile_vector(geom, length_m, {3}, {2})
) s;'''.format(self.ways_table, i, self.elevs_interval,
self.meter_degree)
cur.execute(sql_elevs)
conn.commit()
sql_null_false = '''UPDATE elevs_{0}
SET elevs = NULL
WHERE ARRAY_LENGTH(elevs,1) = 1
'''.format(self.ways_table)
cur.execute(sql_null_false)
cur.execute('ALTER TABLE elevs_{0} ADD PRIMARY KEY (id);'.format(
self.ways_table))
conn.commit()
conn.close()
def compute_cycling_impedance(self):
db = Database(self.db_suffix)
conn = db.connect()
cur = conn.cursor()
sql_create_table = '''
DROP TABLE IF EXISTS impedances_{0};
CREATE TABLE impedances_{0}
(
id bigint,
s_imp float,
rs_imp float
);'''.format(self.ways_table)
cur.execute(sql_create_table)
conn.commit()
cnt = 0
for i in self.ids:
cnt = cnt + 1
if (cnt / self.batch_size).is_integer():
print('Compute impedances for %s out of %s lines' %
(cnt, self.total_cnt))
conn.commit()
sql_update_impedance = '''
INSERT INTO impedances_{0}(id, s_imp, rs_imp)
SELECT x.id, i.imp, i.rs_imp
FROM (
SELECT *
FROM elevs_{0}
WHERE id = {1}
AND elevs IS NOT NULL
) x, LATERAL compute_impedances(elevs, length_m, elevs_interval) i'''.format(
self.ways_table, i)
cur.execute(sql_update_impedance)
conn.commit()
sql_primary_key = 'ALTER TABLE impedances_{0} ADD PRIMARY KEY (id);'.format(
self.ways_table)
cur.execute(sql_primary_key)
conn.commit()
conn.close()
def compute_average_slope(self):
db = Database(self.db_suffix)
conn = db.connect()
cur = conn.cursor()
sql_create_table = '''
DROP TABLE IF EXISTS average_slopes_{0};
CREATE TABLE average_slopes_{0}
(
id bigint,
slope float
);'''.format(self.ways_table)
cur.execute(sql_create_table)
conn.commit()
cnt = 0
for i in self.ids:
cnt = cnt + 1
if (cnt / self.batch_size).is_integer():
print('Compute slopes for %s out of %s lines' %
(cnt, self.total_cnt))
conn.commit()
sql_update_impedance = '''
INSERT INTO average_slopes_{0}(id, slope)
SELECT e.id, compute_average_slope(elevs, length_m, elevs_interval)
FROM (SELECT * FROM elevs_{0} WHERE id = {1}) e'''.format(
self.ways_table, i)
cur.execute(sql_update_impedance)
conn.commit()
cur.execute(
'ALTER TABLE average_slopes_{0} ADD PRIMARY KEY (id);'.format(
self.ways_table))
conn.commit()
conn.close()
def update_line_tables(self):
db = Database(self.db_suffix)
conn = db.connect()
cur = conn.cursor()
if self.ways_table == 'ways':
sql_update = '''
UPDATE ways w
SET s_imp = s.s_imp, rs_imp = s.rs_imp
FROM slope_profile_ways s
WHERE ST_EQUALS(w.geom, s.geom)
'''
elif self.ways_table == 'footpaths_visualization':
sql_update = '''
UPDATE footpath_visualization f
SET incline_percent = slope
FROM slope_profile_footpath_visualization s
WHERE ST_EQUALS(f.geom, s.geom)
'''
else:
return {"Error": 'Please specify a valid table!'}
cur.execute(sql_update)
conn.commit()
conn.close()
def fresh_setup(self):
#Create temp database for setup and create connections
CreateDatabase(ReadYAML()).create_pgpass_files()
db_conn = Database('temp')
CreateDatabase(ReadYAML()).create_fresh_temp_db()
#Import custom data
data_import = DataImport(ReadYAML(), True, db_conn)
data_import.import_data_folder('/opt/data/')
#Download OSM data
data_import.prepare_planet_osm()
#Import OSM
data_import.import_osm2pgsql()
data_import.import_osm2pgrouting()
#Create variable container and function for data preparation
prepare_database = PrepareDatabase(ReadYAML(), True, db_conn)
prepare_database.data_preparation_table_types_functions()
#Prepare data layers
prepare_layers = PrepareLayers(ReadYAML(), True, prepare_database,
db_conn)
prepare_layers.pois()
prepare_layers.ways()
prepare_layers.produce_population_points(
ReadYAML().return_goat_conf()["DATA_REFINEMENT_VARIABLES"]
["POPULATION"])
prepare_layers.mapping_tables()
prepare_layers.insert_osm_timestamp()
#Create database functions and rename database
prepare_database.create_functions()
CreateDatabase(ReadYAML()).rename_databases()
import json
# import pymysql
app = Flask(__name__)
# The error response json
ERROR_RETURN = json.dumps({'error': "Error"})
SUCCESS_RETURN = json.dumps({'success': "Success"})
password = os.environ.get('SIRIUS_PASSWORD')
if password is None:
raise Exception('No password available')
db = Database(password)
cur = db.get_cur()
def log(msg):
sys.stderr.write("{}\n".format(msg))
def handle_exception(e):
exc_type, exc_obj, exc_tb = sys.exc_info()
fname = os.path.split(exc_tb.tb_frame.f_code.co_filename)[1]
log("Error: {} in file {} at line {}".format(str(repr(e)), fname, exc_tb.tb_lineno))
def all_in(L, dic):
"""Given a list and a dictionary checks that
all values in the list are keys in the dictionary"""
for item in L:
prepare_layers.pois()
prepare_layers.ways()
prepare_layers.produce_population_points(
ReadYAML().return_goat_conf()["DATA_REFINEMENT_VARIABLES"]
["POPULATION"])
prepare_layers.mapping_tables()
prepare_layers.insert_osm_timestamp()
#Create database functions and rename database
prepare_database.create_functions()
CreateDatabase(ReadYAML()).rename_databases()
CreateDatabase(ReadYAML()).create_pgpass_files()
db_conn = Database()
data_import = DataImport(ReadYAML(), False, db_conn)
prepare_database = PrepareDatabase(ReadYAML(), False, db_conn)
#Define command line options
help_text_type = '''Please define the setup type.
1. -t new_setup Do a completely fresh setup and drop your old database.
2. -t functions Update functions only.
3. -t variable_container Update variable container only.
4. -t restore_dump Restore a database dump that is labelled goat_db.sql
'''
setup_types = ['new_setup', 'functions', 'variable_container', 'restore_dump']
parser = argparse.ArgumentParser(formatter_class=argparse.RawTextHelpFormatter)