def summary_status_query():
# TODO add union for streetcar data
ret_val = []
# get count and target for each route
web_session = Session()
query = web_session.execute("""
SELECT
rte,
rte_desc,
sum(count) AS count,
sum(target) AS target
FROM summary
GROUP BY rte, rte_desc
ORDER BY rte;""")
# indexes for tuples returned by query
RTE = 0
RTE_DESC = 1
COUNT = 2
TARGET = 3
# build data for each route
# and add to ret_val list
# sorted by route number
for record in query:
data = {}
data['rte'] = str(record[RTE])
data['rte_desc'] = record[RTE_DESC]
data['count'] = float(record[COUNT])
data['target'] = float(record[TARGET])
ret_val.append(data)
web_session.close()
return ret_val
def get_stops(rte, dir):
ret_val = []
rte = rte
dir = dir
fil_val = {"rte": rte, "dir": dir}
session = Session()
stops = session.execute(
"""
SELECT rte, dir, stop_id, stop_name, ST_AsGeoJson(ST_Transform(geom, 4236)) AS geom
FROM tm_route_stops
WHERE rte = :rte AND
dir = :dir""", fil_val)
RTE = 0
DIR = 1
STOP_ID = 2
STOP_NAME = 3
GEOM = 4
ret_val = [{
'rte': row[RTE],
'dir': row[DIR],
'stop_id': row[STOP_ID],
'stop_name': row[STOP_NAME],
'geom': row[GEOM]
} for row in stops]
session.close()
#debug(ret_val)
return ret_val
def status():
routes = [route['rte_desc'] for route in Helper.get_routes()]
data = Helper.query_route_status()
web_session = Session()
query = web_session.execute("""
SELECT s.rte_desc, sum(s.count) AS count
FROM odk.records_long s
WHERE s.rte_desc LIKE 'Portland Streetcar%'
GROUP BY s.rte_desc;""")
#hardcode streetcar targets, then populate the count
streetcar = {
"Portland Streetcar - NS Line": {
'target': 869,
'count': 0
},
"Portland Streetcar - A Loop": {
'target': 331,
'count': 0
},
"Portland Streetcar - B Loop": {
'target': 343,
'count': 0
}
}
for record in query:
debug(record)
streetcar[record[0]]['count'] = int(record[1])
web_session.close()
summary = Helper.query_routes_summary()
return render_template('long/status.html',
streetcar=streetcar,
routes=routes,
data=data,
summary=summary)
def map():
session = Session()
keys = []
query = session.query(SurveysCore)
for record in query:
#TODO check that survey has not already been flagged by user
debug(record.uri)
#if record.flags.locations:
keys.append(record.uri)
session.close()
return render_template(static('map.html'), keys=keys)
def rte_lookup(rte_desc):
rte = None
session = Session()
query = session.execute(
"""
SELECT rte
FROM lookup_rte
WHERE rte_desc = :rte_desc""", {'rte_desc': rte_desc})
for record in query:
rte = record[0]
session.close()
return rte
def get_questions():
ret_val = []
web_session = Session()
questions = web_session.execute("""
SELECT num, questions
FROM odk.ques_lookup
ORDER BY num;""")
ret_val = [[question[0], str(question[1])] for question in questions]
web_session.close()
debug(ret_val)
return ret_val
def get_destination(where, qnum):
ret_val = []
where = where
bar_chart = pygal.Bar(print_values=True)
bar_chart.title = 'Trip Destination Types'
query_string = """
WITH survey as (
select *
from odk.fall_survey_2016_data f
where
f.willing in ('1','2') and
f.q4_dest_type is not null {0}),
destcount as (
select
case
WHEN q4_dest_type = '1' THEN 'Home'
WHEN q4_dest_type = '2' THEN 'Work'
WHEN q4_dest_type = '3' THEN 'School'
WHEN q4_dest_type = '4' THEN 'Recreation'
WHEN q4_dest_type = '5' THEN 'Shopping'
WHEN q4_dest_type = '6' THEN 'Personal business'
WHEN q4_dest_type = '7' THEN 'Visit family or friends'
WHEN q4_dest_type = '8' THEN 'Medical appointment'
WHEN q4_dest_type = '9' THEN 'Other'
else ''
end as dest_type,
count(*) as count,
round(100*count(*)/(select count(*) from survey)::numeric,1) as pct
from survey
group by q4_dest_type
order by pct desc)
select * from destcount;""".format(where)
debug(query_string)
web_session = Session()
query = web_session.execute(query_string)
# each record will be converted as json
# and sent back to page
ret_val = [[record[0], int(record[1]),
float(record[2])] for record in query]
debug(ret_val)
for row in ret_val:
bar_chart.add(str(row[0]), int(row[1]))
bar_chart.render_to_file(
os.path.join(DIRPATH, "static/image/{0}{1}.svg".format('q', qnum)))
web_session.close()
return ret_val
def get_routes():
ret_val = []
session = Session()
routes = session.execute("""
SELECT rte, rte_desc
FROM orange_route_direction
ORDER BY route_sort_order;""")
ret_val = [{
'rte': str(route[0]),
'rte_desc': route[1]
} for route in routes]
debug(ret_val)
session.close()
return ret_val
def query_route_status(rte_desc=''):
# set rte_desc to wildcard to query
# if no route was specified
ret_val = {}
# query web database
# using helper views
web_session = Session()
if rte_desc:
query = web_session.execute(
"""
SELECT s.rte,s.rte_desc,s.dir,s.dir_desc,s.time_period,
s.count, s.target * .1
FROM
odk.summary_long s
WHERE s.rte_desc = :rte_desc
ORDER BY s.rte, s.dir,
CASE s.time_period
WHEN 'AM Peak' THEN 1
WHEN 'Midday' THEN 2
WHEN 'PM Peak' THEN 3
WHEN 'Evening' THEN 4
WHEN 'Total' THEN 5
ELSE 6
END;""", {'rte_desc': rte_desc})
ret_val = Helper.build_response_route_status(query)
else:
# query web database
# using helper views
query = web_session.execute("""
SELECT s.rte,s.rte_desc,s.dir,s.dir_desc,s.time_period,
s.count, s.target * .1
FROM
odk.summary_long s
ORDER BY s.rte, s.dir,
CASE s.time_period
WHEN 'AM Peak' THEN 1
WHEN 'Midday' THEN 2
WHEN 'PM Peak' THEN 3
WHEN 'Evening' THEN 4
WHEN 'Total' THEN 5
ELSE 6
END;""")
ret_val = Helper.build_response_summary_status(query)
web_session.close()
#debug(ret_val)
return ret_val
def get_directions():
ret_val = []
session = Session()
directions = session.execute("""
SELECT rte, rte_desc, dir, dir_desc
FROM lookup_dir
ORDER BY rte, dir;""")
ret_val = [{
'rte': str(direction[0]),
'rte_desc': direction[1],
'dir': int(direction[2]),
'dir_desc': direction[3]
} for direction in directions]
session.close()
return ret_val
def get_satisfaction(where, qnum):
ret_val = []
where = where
pie_chart = pygal.Pie(print_values=True)
pie_chart.title = 'Customer Satisfaction'
query_string = """
WITH survey as (
select *
from odk.fall_survey_2016_data f
where
f.willing in ('1','2') and
f.q1_satisfaction is not null {0}),
satisfactioncount as (
select
CASE
WHEN q1_satisfaction = '1' THEN 'Very satisfied'
WHEN q1_satisfaction = '3' THEN 'Somewhat satisfied'
WHEN q1_satisfaction = '4' THEN 'Neutral'
WHEN q1_satisfaction = '5' THEN 'Somewhat dissatisfied'
WHEN q1_satisfaction = '6' THEN 'Very dissatisfied'
WHEN q1_satisfaction = '7' THEN 'Do not know'
else ''
END as satisfaction,
count(*) as count,
round(100*count(*)/(select count(*) from survey)::numeric,1) as pct
from survey
group by q1_satisfaction
order by pct desc)
select * from satisfactioncount;""".format(where)
debug(query_string)
web_session = Session()
query = web_session.execute(query_string)
# each record will be converted as json
# and sent back to page
ret_val = [[record[0], int(record[1]),
float(record[2])] for record in query]
debug(ret_val)
for row in ret_val:
pie_chart.add(str(row[0]), float(row[2]))
pie_chart.render_to_file(
os.path.join(DIRPATH, "static/image/{0}{1}.svg".format('q', qnum)))
web_session.close()
return ret_val
def current_users(date):
ret_val = {}
web_session = Session()
results = web_session.execute(
"""
SELECT u.rte_desc, u.time_period, u.user_id, u.rte
FROM
(SELECT * from users_tod_ts
UNION
SELECT * from users_tod) u
WHERE u.date = :date
ORDER BY
CASE u.time_period
WHEN 'AM Peak' THEN 1
WHEN 'Midday' THEN 2
WHEN 'PM Peak' THEN 3
WHEN 'Evening' THEN 4
WHEN 'Total' THEN 5
ELSE 6
END, u.rte;""", {'date': date})
for result in results:
rte_desc = result[0]
time_period = result[1]
user = "******".join(
sorted(
list(set([user.strip()
for user in result[2].split(",")]))))
#list1 = result[2].split(",")
#list2 = set(list1)
#list3 = ", ".join(list2)
#debug(list1)
#debug(list2)
#debug(list3)
#user = "******".join(list(set(result[2].split(","))))
#debug(user)
if time_period not in ret_val:
ret_val[time_period] = []
data = {'rte_desc': rte_desc, 'user': user}
ret_val[time_period].append(data)
web_session.close()
debug(ret_val)
return ret_val
def query_sep_data(where):
ret_val = []
query_args = {}
where = where
region = " AND f.q5_orig_region='2' and f.q6_dest_region='2' "
validate = " AND f.loc_validated='1' "
not_null = " AND f.q3_orig_type is not null AND f.q4_dest_type is not null;"
query_string = """
WITH survey as (
select *
from odk.fall_survey_2016_data f
where
f.willing in ('1','2') and
f.origin_sep is not null {0}),
sepcount as (
select origin_sep,
count(*) as sep_count,
round(100*count(*)/(select count(*) from survey)::numeric,1) as pct
from survey
group by origin_sep
order by origin_sep)
select * from sepcount;""".format(where)
#query_string += where
#query_string += ;
debug(query_string)
web_session = Session()
query = web_session.execute(query_string)
SEP = 0
COUNT = 1
PER = 2
# each record will be converted as json
# and sent back to page
for record in query:
data = {}
data['sep'] = record[SEP]
data['count'] = record[COUNT]
data['percentage'] = float(record[PER])
ret_val.append(data)
web_session.close()
return ret_val
def get_travel_change(where, qnum):
ret_val = []
where = where
bar_chart = pygal.Bar(print_values=True)
bar_chart.title = 'Transit Usage Compared to A Year Ago'
query_string = """
WITH survey as (
select *
from odk.fall_survey_2016_data f
where
f.willing in ('1','2') and
f.q7_travel_change is not null {0}),
changecount as (
select
case
WHEN q7_travel_change = '1' THEN 'More'
WHEN q7_travel_change = '2' THEN 'Same'
WHEN q7_travel_change = '3' THEN 'Less'
WHEN q7_travel_change = '4' THEN 'Do not know'
else ''
end as ride_change,
count(*) as count,
round(100*count(*)/(select count(*) from survey)::numeric,1) as pct
from survey
group by q7_travel_change
order by pct desc)
select * from changecount;""".format(where)
debug(query_string)
web_session = Session()
query = web_session.execute(query_string)
# each record will be converted as json
# and sent back to page
ret_val = [[record[0], int(record[1]),
float(record[2])] for record in query]
debug(ret_val)
for row in ret_val:
bar_chart.add(str(row[0]), int(row[1]))
bar_chart.render_to_file(
os.path.join(DIRPATH, "static/image/{0}{1}.svg".format('q', qnum)))
web_session.close()
return ret_val
def get_routes():
ret_val = []
web_session = Session()
routes = web_session.execute("""
SELECT distinct rte, rte_desc
FROM odk.rte_lookup
ORDER BY rte;""")
RTE = 0
RTE_DESC = 1
ret_val = [{
'rte': str(route[RTE]),
'rte_desc': route[RTE_DESC]
} for route in routes]
web_session.close()
debug(ret_val)
return ret_val
def get_routes():
ret_val = []
web_session = Session()
routes = web_session.execute("""
SELECT rte, rte_desc
FROM orange_route_direction
ORDER BY route_sort_order;""")
RTE = 0
RTE_DESC = 1
ret_val = [{
'rte': str(route[RTE]),
'rte_desc': route[RTE_DESC]
} for route in routes]
web_session.close()
return ret_val
def get_users():
users = []
session = Session()
results = session.execute("""
SELECT name
FROM odk.surveyors
ORDER BY name;""")
for result in results:
print((dict(result)))
print("Type:", type(dict(result)))
user_dict = dict(result)
print(user_dict)
user = user_dict.get('name')
users.append(str(user))
session.close()
return users
def get_users():
users = []
web_session = Session()
results = web_session.execute("""
SELECT first
FROM users
WHERE first IS NOT NULL
ORDER BY first;""")
for result in results:
#print((dict(result)))
#print("Type:", type(dict(result)))
user_dict = dict(result)
#print(user_dict)
user = user_dict.get('first')
users.append(str(user))
web_session.close()
return users
def query_cty_data(where):
ret_val = []
query_args = {}
where = where
query_string = """
WITH survey as (
select *
from odk.fall_survey_2016_data f
where
f.willing in ('1','2') and
f.origin_cty is not null {0}),
ctycount as (
select origin_cty,
count(*) as cty_count,
round(100*count(*)/(select count(*) from survey)::numeric,1) as pct
from survey
group by origin_cty
order by origin_cty)
select * from ctycount;""".format(where)
debug(query_string)
web_session = Session()
query = web_session.execute(query_string)
COUNTY = 0
COUNT = 1
PER = 2
# each record will be converted as json
# and sent back to page
for record in query:
data = {}
data['COUNTY'] = record[COUNTY]
data['count'] = record[COUNT]
data['percentage'] = float(record[PER])
ret_val.append(data)
web_session.close()
return ret_val
def get_directions():
ret_val = []
web_session = Session()
directions = web_session.execute("""
SELECT rte, rte_desc, dir, dir_desc
FROM lookup_dir
ORDER BY rte, dir;""")
RTE = 0
RTE_DESC = 1
DIR = 2
DIR_DESC = 3
ret_val = [{
'rte': str(direction[RTE]),
'rte_desc': direction[RTE_DESC],
'dir': int(direction[DIR]),
'dir_desc': direction[DIR_DESC]
} for direction in directions]
web_session.close()
return ret_val
def get_user_data(date):
surveyordata = []
bar_chart = pygal.HorizontalBar(print_values=True)
bar_chart.title = 'Number of Surveys by Surveyor on {0}'.format(date)
web_session = Session()
results = web_session.execute(
"""
select
name,
string_agg(distinct route, ' || ') as routes,
count(route) as count,
round(count(route)*100/(select count(*) from odk.users_tod where _date=:date)::numeric,2) as pct
from odk.users_tod
where _date=:date
group by name
order by count desc;""", {'date': date})
for result in results:
print(result[0], result[1], result[2], result[3])
surveyordata.append(
[result[0], result[1],
int(result[2]),
float(result[3])])
bar_chart.add(result[0], int(result[2]))
web_session.close()
debug(surveyordata)
bar_chart.render_to_file(
os.path.join(DIRPATH,
"static/image/{0}{1}.svg".format('surveyors-', date)))
return surveyordata
def map_offs_details():
response = {'success': False}
if 'rte' in request.args.keys():
rte = int(request.args['rte'].strip())
debug(rte)
#debug(type(rte))
#rte = h.rte_lookup(rte_desc)
session = Session()
fields = ['dir', 'tad', 'centroid', 'stops', 'ons', 'count']
query_time = session.execute(
"""
SELECT """ + ','.join(fields) + """, bucket
FROM tad_time_stats
WHERE rte = :rte;""", {'rte': rte})
query_markers = session.execute(
"""
SELECT dir, tad, centroid, stops, ons, count
FROM tad_stats
WHERE rte = :rte;""", {'rte': rte})
query_tads = session.execute(
"""
SELECT
r.dir,
t.tadce10 AS tad,
ST_AsGeoJson(ST_Transform(ST_Union(t.geom), 4326)) AS geom,
ST_AsGeoJson(ST_Transform(ST_Centroid(ST_Union(t.geom)), 4326)) AS centroid
FROM tad AS t
JOIN tm_routes AS r
ON ST_Intersects(t.geom, r.geom)
WHERE r.rte = :rte
GROUP BY r.dir, t.tadce10;""", {'rte': rte})
query_data_summary = session.execute(
"""
SELECT
dir,
on_tad,
sum(count) AS ons
FROM tad_onoff
WHERE rte = :rte
GROUP BY dir, on_tad;""", {'rte': rte})
query_data = session.execute(
"""
SELECT
dir,
on_tad,
off_tad,
count
FROM tad_onoff
WHERE rte = :rte;""", {'rte': rte})
query_routes = session.execute(
"""
SELECT dir, ST_AsGeoJson(ST_Transform(ST_Union(geom), 4326))
FROM tm_routes
WHERE rte = :rte
GROUP BY dir;""", {'rte': rte})
query_minmax = session.execute(
"""
SELECT dir, label, stop_name, ST_AsGeoJson(ST_Transform(geom, 4326))
FROM stop_minmax
WHERE rte = :rte;""", {'rte': rte})
def build_data(record):
data = {}
for index in range(1, len(fields)):
field = record[index]
if isinstance(field, Decimal): field = int(field)
data[fields[index]] = field
return data
def int_zero(value):
try:
return int(value)
except:
return 0
tads = []
stops = {}
stops[0] = {}
stops[1] = {}
summary = {}
summary[0] = []
summary[1] = []
data = {}
data[0] = {}
data[1] = {}
routes_geom = {}
minmax = {}
time_data = {}
time_data[0] = {}
time_data[1] = {}
for record in query_tads:
dir_ = record[0]
tad = record[1]
geom = record[2]
centroid = record[3]
tads.append({
'dir': dir_,
'tad': tad,
'geom': geom,
'centroid': centroid
})
for record in query_markers:
dir_ = record[0]
tad = record[1]
centroid = json.loads(record[2])
stops_geom = json.loads(record[3])
ons = int_zero(record[4])
count = int_zero(record[5])
stops[dir_][tad] = {
'tad': tad,
'centroid': centroid,
'count': count,
'stops': stops_geom,
'ons': ons
}
for record in query_data_summary:
dir_ = record[0]
tad_on = record[1]
ons = int(record[2])
summary[dir_].append({'tad': tad_on, 'ons': ons})
for record in query_data:
dir_ = record[0]
tad_on = record[1]
tad_off = record[2]
offs = int(record[3])
if tad_on not in data[dir_]:
data[dir_][tad_on] = {}
data[dir_][tad_on]['offs'] = []
data[dir_][tad_on]['offs'].append({'tad': tad_off, 'offs': offs})
for record in query_routes:
routes_geom[record[0]] = {
'dir': record[0],
'geom': json.loads(record[1])
}
for record in query_minmax:
if record[0] not in minmax:
minmax[record[0]] = {}
minmax[record[0]][record[1]] = {
'geom': record[3],
'stop_name': record[2]
}
# time of day buckets
for record in query_time:
#debug(record)
tad = record[1]
if tad not in time_data[record[0]]:
time_data[record[0]][tad] = []
ons = int_zero(record[4])
count = int_zero(record[5])
bucket = int_zero(record[6])
# insert because data comes in sorted and needs to go out sorted?
time_data[record[0]][tad].insert(bucket, {
"count": count,
"ons": ons
})
response['success'] = True
response['stops'] = stops
response['summary'] = summary
response['data'] = data
response['tads'] = tads
response['routes'] = routes_geom
response['minmax'] = minmax
response['time_data'] = time_data
session.close()
return jsonify(response)
def query_map_data(where):
ret_val = []
query_args = {}
where = where
region = " AND f.q7_orig_region='2' and f.q7_dest_region='2' "
validate = " AND f.loc_validated='1' "
not_null = " AND f.q5_orig_type is NOT NULL AND f.q6_dest_type is NOT NULL "
limit = "limit 5000;"
query_string = """
SELECT
f.rte,
r.rte_desc,
f.dir,
r.dir_desc,
q5_orig_type AS o_type,
q6_dest_type AS d_type,
f.q7_orig_lat AS o_lat,
f.q7_orig_lng AS o_lng,
f.q7_dest_lat AS d_lat,
f.q7_dest_lng AS d_lng,
f.q7_board_id,
f.q7_alight_id,
coalesce(f.q39_zipcode::text, ''),
coalesce(q36_age, '0') AS age,
coalesce(q37_gender, '0') AS gender,
coalesce(q38_income, '0') AS income,
f.time_of_day,
to_char(f._date, 'Mon DD YYYY') AS _date
FROM odk.orange_after_2017_all f
JOIN route_directions r
ON f.rte::integer = r.rte AND f.dir::integer = r.dir """
query_string += where
query_string += region
query_string += validate
query_string += not_null
query_string += limit
#debug(query_string)
web_session = Session()
query = web_session.execute(query_string)
RTE = 0
RTE_DESC = 1
DIR = 2
DIR_DESC = 3
OTYPE = 4
DTYPE = 5
OLAT = 6
OLNG = 7
DLAT = 8
DLNG = 9
BOARD = 10
ALIGHT = 11
ZIPCODE = 12
AGE = 13
GENDER = 14
INCOME = 15
TOD = 16
DATE = 17
# each record will be converted as json
# and sent back to page
for record in query:
data = {}
data['rte'] = record[RTE]
data['rte_desc'] = record[RTE_DESC]
data['dir'] = record[DIR]
data['dir_desc'] = record[DIR_DESC]
data['o_type'] = metadata['trip'][record[OTYPE]]
data['d_type'] = metadata['trip'][record[DTYPE]]
data['o_lat'] = float(record[OLAT])
data['o_lng'] = float(record[OLNG])
data['d_lat'] = float(record[DLAT])
data['d_lng'] = float(record[DLNG])
data['board'] = record[BOARD]
data['alight'] = record[ALIGHT]
data['zipcode'] = record[ZIPCODE]
data['age'] = metadata['age'][record[AGE]]
data['gender'] = metadata['gender'][record[GENDER]]
data['income'] = metadata['income'][record[INCOME]]
data['time_of_day'] = record[TOD]
data['date'] = record[DATE]
ret_val.append(data)
web_session.close()
return ret_val
def query_route_status(rte_desc=''):
# set rte_desc to wildcard to query
# if no route was specified
ret_val = {}
# query web database
# using helper views
web_session = Session()
if rte_desc:
query = web_session.execute(
"""
SELECT sq.rte,sq.rte_desc,sq.dir,sq.dir_desc,sq.time_period,
sq.scount + sq.tcount AS count, sq.target * .2
FROM
(SELECT s.rte, s.rte_desc,s.dir,s.dir_desc,s.time_period, s.target,
s.count AS scount, t.count AS tcount
FROM
summary_ts t
LEFT JOIN summary s
ON t.rte = s.rte AND
t.dir = s.dir AND
t.time_period = s.time_period) sq
WHERE sq.rte_desc = :rte_desc
ORDER BY sq.rte, sq.dir,
CASE sq.time_period
WHEN 'AM Peak' THEN 1
WHEN 'Midday' THEN 2
WHEN 'PM Peak' THEN 3
WHEN 'Evening' THEN 4
WHEN 'Total' THEN 5
ELSE 6
END;""", {'rte_desc': rte_desc})
ret_val = Helper.build_response_route_status(query)
else:
# query web database
# using helper views
query = web_session.execute("""
SELECT sq.rte,sq.rte_desc,sq.dir,sq.dir_desc,sq.time_period,
sq.scount + sq.tcount AS count, sq.target * .2
FROM
(SELECT s.rte, s.rte_desc,s.dir,s.dir_desc,s.time_period, s.target,
s.count AS scount, t.count AS tcount
FROM
summary_ts t
LEFT JOIN summary s
ON t.rte = s.rte AND
t.dir = s.dir AND
t.time_period = s.time_period) sq
ORDER BY sq.rte, sq.dir,
CASE sq.time_period
WHEN 'AM Peak' THEN 1
WHEN 'Midday' THEN 2
WHEN 'PM Peak' THEN 3
WHEN 'Evening' THEN 4
WHEN 'Total' THEN 5
ELSE 6
END;""")
ret_val = Helper.build_response_summary_status(query)
web_session.close()
#debug(ret_val)
return ret_val
def get_travel_less(where, qnum):
ret_val = []
where = where
bar_chart = pygal.Bar(print_values=True)
bar_chart.title = 'Reasons for Riding TriMet Less'
query_string = """
WITH survey as (
select *
from odk.fall_survey_2016_data f
where
f.willing in ('1','2') and
f.q8_ride_less is not null {0}),
unnest_element as (
select unnest(string_to_array(q8_ride_less, ' ')) as element
from survey),
ridechange as (
select
case
WHEN element = '1' THEN 'Gasoline prices low'
WHEN element = '2' THEN 'Home changed'
WHEN element = '3' THEN 'Work changed'
WHEN element = '4' THEN 'School changed'
WHEN element = '5' THEN 'Life changed'
WHEN element = '6' THEN 'Telecommute more'
WHEN element = '7' THEN 'Take ride hailing services'
WHEN element = '8' THEN 'On time issues'
WHEN element = '9' THEN 'Frequency not enough'
WHEN element = '10' THEN 'Crowding issues'
WHEN element = '11' THEN 'Span of service not enough'
WHEN element = '12' THEN 'Fare prices too high'
WHEN element = '13' THEN 'Drive instead'
WHEN element = '14' THEN 'Bicycle instead'
WHEN element = '15' THEN 'Walk instead'
WHEN element = '16' THEN 'Other'
WHEN element = '17' THEN 'Do not know'
else ''
end as ride_less,
count(*) as count,
round(100*count(*)/(select count(*) from unnest_element)::numeric,1) as pct
from unnest_element
group by element
order by pct desc)
select * from ridechange;""".format(where)
debug(query_string)
web_session = Session()
query = web_session.execute(query_string)
# each record will be converted as json
# and sent back to page
ret_val = [[record[0], int(record[1]),
float(record[2])] for record in query]
debug(ret_val)
for row in ret_val:
bar_chart.add(str(row[0]), int(row[1]))
bar_chart.render_to_file(
os.path.join(DIRPATH, "static/image/{0}{1}.svg".format('q', qnum)))
web_session.close()
return ret_val
def query_route_data(user='', rte_desc='', dir_desc='', csv=False):
ret_val = []
query_args = {}
where = ""
if user: user = "******" + user + "%"
user_filter = " first_name LIKE :user "
rte_desc_filter = " rte_desc = :rte_desc "
dir_desc_filter = " dir_desc = :dir_desc "
def construct_where(string, param, filt_name):
if not param:
return string
if filt_name == "user": filt = user_filter
elif filt_name == "rte_desc": filt = rte_desc_filter
else: filt = dir_desc_filter
if string:
return string + " AND " + filt
else:
return string + filt
# build where clause
debug(where)
for param in [(user, 'user'), (rte_desc, 'rte_desc'),
(dir_desc, 'dir_desc')]:
where = construct_where(where, param[0], param[1])
debug(where)
query_args[param[1]] = param[0]
if where:
where = " WHERE " + where
limit = "LIMIT 300;"
if csv:
# add headers to csv data
ret_val.append([
'date', 'time', 'user', 'rte_desc', 'dir_desc', 'on_stop',
'off_stop'
])
limit = ";"
query_string = """
SELECT rte_desc, dir_desc, date, time, first_name,
on_stop_name, off_stop_name
FROM display_data """
query_string_ts = """
SELECT rte_desc, dir_desc, date, time, first_name,
on_stop_name, off_stop_name
FROM display_data_ts """
query_string += where
query_string += " ORDER BY date DESC, time DESC "
query_string += limit
query_string_ts += where
query_string_ts += " ORDER BY date DESC, time DESC "
query_string_ts += limit
debug(query_string)
debug(query_string_ts)
web_session = Session()
query = web_session.execute(query_string, query_args)
# convert query object to list
result = [r for r in query]
query_ts = web_session.execute(query_string_ts, query_args)
# convert query object to list
result_ts = [r for r in query_ts]
# merge two lists to one list
result_all = result + result_ts
debug(type(result_all))
RTE_DESC = 0
DIR_DESC = 1
DATE = 2
TIME = 3
USER = 4
ON_STOP = 5
OFF_STOP = 6
# each record will be converted as json
# and sent back to page
for record in result_all:
if csv:
data = []
data.append(str(record[DATE]))
data.append(str(record[TIME]))
data.append(record[USER])
data.append(record[RTE_DESC])
data.append(record[DIR_DESC])
data.append(record[ON_STOP])
data.append(record[OFF_STOP])
else:
data = {}
data['date'] = str(record[DATE])
data['time'] = str(record[TIME])
data['user'] = record[USER]
data['rte_desc'] = record[RTE_DESC]
data['dir_desc'] = record[DIR_DESC]
data['on_stop'] = record[ON_STOP]
data['off_stop'] = record[OFF_STOP]
ret_val.append(data)
web_session.close()
return ret_val
def query_map_data(where, csv):
ret_val = []
query_args = {}
where = where
csv = csv
debug(csv)
not_null = " location is not null AND manager_supervisor is not null "
if csv == "yes":
# add headers to csv data
ret_val.append([
'date', 'time', 'manager', 'region', 'shelterid',
'location_lat', 'location_lng', 'litter', 'graffiti', 'washed',
'roof', 'glass', 'bench', 'trashcan', 'lid',
'trashcangraffiti', 'repair', 'comments'
])
query_string = """
SELECT
to_char(createdate, 'Mon DD YYYY') as _date,
cast(createdate as time),
manager_supervisor as manager,
contractregion as region,
case
WHEN shelterid = 'None' or shelterid = '' THEN '1234'
else shelterid
end as shelterid,
location,
coalesce(nolitter, '') as litter,
coalesce(nograffiti, '') as graffiti,
coalesce(pressurewashed, '') as washed,
coalesce(roofclean, '') as roof,
coalesce(glassdried, '') as glass,
coalesce(benchdried, '') as bench,
coalesce(trashcanempty, '') as trashcan,
coalesce(lidclean, '') as lid,
coalesce(trashcangraffiti, ''),
case
WHEN needrepair = 'true' THEN 'Yes'
WHEN needrepair = 'false' THEN 'No'
else ''
end as repair,
coalesce(comments, '')
from bus_shelter_inspection """
query_string += " WHERE "
query_string += not_null
query_string += where
debug(query_string)
web_session = Session()
query = web_session.execute(query_string)
DATE = 0
TIME = 1
MANAGER = 2
REGION = 3
SHELTER = 4
LOCATION = 5
LITTER = 6
GRAFFITI = 7
WASHED = 8
ROOF = 9
GLASS = 10
BENCH = 11
TRASHCAN = 12
LID = 13
TRASHCANGRAFFITI = 14
REPAIR = 15
COMMENTS = 16
# each record will be converted as json
# and sent back to page
for record in query:
if csv == "yes":
data = []
data.append(str(record[DATE]))
data.append(str(record[TIME]))
data.append(record[MANAGER])
data.append(record[REGION])
data.append(record[SHELTER])
data.append(record[LOCATION])
data.append(record[LITTER])
data.append(record[GRAFFITI])
data.append(record[WASHED])
data.append(record[ROOF])
data.append(record[GLASS])
data.append(record[BENCH])
data.append(record[TRASHCAN])
data.append(record[LID])
data.append(record[TRASHCANGRAFFITI])
data.append(record[REPAIR])
data.append(record[COMMENTS])
else:
data = {}
data['date'] = str(record[DATE])
data['time'] = str(record[TIME])
data['manager'] = record[MANAGER]
data['region'] = record[REGION]
data['shelter'] = record[SHELTER]
data['location'] = record[LOCATION]
data['litter'] = record[LITTER]
data['graffiti'] = record[GRAFFITI]
data['washed'] = record[WASHED]
data['roof'] = record[ROOF]
data['glass'] = record[GLASS]
data['bench'] = record[BENCH]
data['trashcan'] = record[TRASHCAN]
data['lid'] = record[LID]
data['trashcangraffiti'] = record[TRASHCANGRAFFITI]
data['repair'] = record[REPAIR]
data['comments'] = record[COMMENTS]
ret_val.append(data)
web_session.close()
return ret_val
def query_station_data(where):
ret_val = []
query_args = {}
where = where
not_null = " platform is not null AND inspector is not null "
query_string = """
SELECT
to_char(createdate, 'Mon DD YYYY') as _date,
cast(createdate as time),
inspector,
cleaningcrew as crew,
washcrew,
platform as station,
coalesce(bathroom_cleaned, ''),
coalesce(bathroom_stocked, ''),
coalesce(bench_handrail_phone_tvm_cleaned, '') as bench,
coalesce(crew_room_cleaned, ''),
coalesce(electric_panel_cleaned, '') as electric,
coalesce(parking_lot_notrash, '') as lot_notrash,
coalesce(platform_cleaned, ''),
coalesce(platform_hosedoff, ''),
coalesce(shelter_area_hosed_down, '') as shelter_hosed,
coalesce(shelter_enclosure_swept, '') as swept,
coalesce(track_area_no_litter, '') as track_nolitter,
coalesce(trash_emptied, ''),
coalesce(landscaping_notrash, ''),
coalesce(walk_pathway_notrash, '') as path_notrash,
coalesce(platform_novegetation, ''),
coalesce(comments, '')
from max_platform_inspection """
query_string += " WHERE "
query_string += not_null
query_string += where
debug(query_string)
web_session = Session()
query = web_session.execute(query_string)
DATE = 0
TIME = 1
INSPECTOR = 2
CREW = 3
WASH = 4
STATION = 5
BRCLEANED = 6
BRSTOCKED = 7
BENCH = 8
CREWROOM = 9
ELECTRIC = 10
LOTNOTRASH = 11
PLATFORM_CLEANED = 12
PLATFORMHOSED = 13
SHELTERHOSED = 14
SWEPT = 15
TRACKLITTER = 16
TRASHEMPTIED = 17
LANDSCAPING = 18
PATHNOTRASH = 19
VEGETATION = 20
COMMENTS = 21
# each record will be converted as json
# and sent back to page
for record in query:
data = {}
data['date'] = str(record[DATE])
data['time'] = str(record[TIME])
data['inspector'] = record[INSPECTOR]
data['crew'] = record[CREW]
data['wash'] = record[WASH]
data['station'] = record[STATION]
data['bathroom_cleaned'] = record[BRCLEANED]
data['bathroom_stocked'] = record[BRSTOCKED]
data['bench_cleaned'] = record[BENCH]
data['crew_room'] = record[CREWROOM]
data['electric'] = record[ELECTRIC]
data['lot_notrash'] = record[BENCH]
data['platform_cleaned'] = record[PLATFORM_CLEANED]
data['platform_hosed'] = record[PLATFORMHOSED]
data['shelter_hosed'] = record[SHELTERHOSED]
data['swept'] = record[SWEPT]
data['track_litter'] = record[TRACKLITTER]
data['trash_empied'] = record[TRASHEMPTIED]
data['landscaping'] = record[LANDSCAPING]
data['pathway_trash'] = record[PATHNOTRASH]
data['vegetation'] = record[VEGETATION]
data['comments'] = record[COMMENTS]
ret_val.append(data)
web_session.close()
return ret_val
def query_map_data(where):
ret_val = []
query_args = {}
where = where
region = " AND f.q5_orig_region='2' and f.q6_dest_region='2' "
validate = " AND f.loc_validated='1' "
not_null = " AND f.q3_orig_type is not null AND f.q4_dest_type is not null "
limit = "limit 2000;"
query_string = """
SELECT
f.rte,
r.rte_desc,
f.dir,
r.dir_desc,
CASE
WHEN q1_satisfaction = '1' THEN 'Very satisfied'
WHEN q1_satisfaction = '3' THEN 'Somewhat satisfied'
WHEN q1_satisfaction = '4' THEN 'Neutral'
WHEN q1_satisfaction = '5' THEN 'Somewhat dissatisfied'
WHEN q1_satisfaction = '6' THEN 'Very dissatisfied'
WHEN q1_satisfaction = '7' THEN 'Do not know'
else ''
end as satisfaction,
case
WHEN q3_orig_type = '1' THEN 'Home'
WHEN q3_orig_type = '2' THEN 'Work'
WHEN q3_orig_type = '3' THEN 'School'
WHEN q3_orig_type = '4' THEN 'Recreation'
WHEN q3_orig_type = '5' THEN 'Shopping'
WHEN q3_orig_type = '6' THEN 'Personal business'
WHEN q3_orig_type = '7' THEN 'Visit family or friends'
WHEN q3_orig_type = '8' THEN 'Medical appointment'
WHEN q3_orig_type = '9' THEN 'Other'
else ''
end as o_type,
case
WHEN q4_dest_type = '1' THEN 'Home'
WHEN q4_dest_type = '2' THEN 'Work'
WHEN q4_dest_type = '3' THEN 'School'
WHEN q4_dest_type = '4' THEN 'Recreation'
WHEN q4_dest_type = '5' THEN 'Shopping'
WHEN q4_dest_type = '6' THEN 'Personal business'
WHEN q4_dest_type = '7' THEN 'Visit family or friends'
WHEN q4_dest_type = '8' THEN 'Medical appointment'
WHEN q4_dest_type = '9' THEN 'Other'
else ''
end as d_type,
f.q5_orig_lat as o_lat,
f.q5_orig_lng as o_lng,
f.q6_dest_lat as d_lat,
f.q6_dest_lng as d_lng,
case
WHEN q7_travel_change = '1' THEN 'More'
WHEN q7_travel_change = '2' THEN 'Same'
WHEN q7_travel_change = '3' THEN 'Less'
WHEN q7_travel_change = '4' THEN 'Do not know'
else ''
end as ride_change,
case
WHEN q18_ridership = '1' THEN 'Frequent rider'
WHEN q18_ridership = '2' THEN 'Regular rider'
WHEN q18_ridership = '3' THEN 'Occasional rider'
WHEN q18_ridership = '4' THEN 'Infrequent rider'
WHEN q18_ridership = '5' THEN 'Do not know'
else ''
end as ridership,
case
WHEN q19_ride_years = '1' THEN 'Less than 1 year'
WHEN q19_ride_years = '2' THEN '1 to 2 years'
WHEN q19_ride_years = '3' THEN '3 to 5 years'
WHEN q19_ride_years = '4' THEN '6 to 10 years'
WHEN q19_ride_years = '5' THEN 'Over 10 years'
WHEN q19_ride_years = '6' THEN 'Do not know'
else ''
end as ride_years,
case
WHEN q20_approval = '1' THEN 'Strongly approve'
WHEN q20_approval = '2' THEN 'Somewhat approve'
WHEN q20_approval = '3' THEN 'Somewhat disapprove'
WHEN q20_approval = '4' THEN 'Strongly disapprove'
WHEN q20_approval = '5' THEN 'Do not know'
else ''
end as job_approval,
case
WHEN q21_one_change = '1' THEN 'Frequency improved'
WHEN q21_one_change = '2' THEN 'Reliability improved'
WHEN q21_one_change = '3' THEN 'Service expanded'
WHEN q21_one_change = '4' THEN 'Routes go to more places'
WHEN q21_one_change = '5' THEN 'Stops closer to my origin/destination'
WHEN q21_one_change = '6' THEN 'Crowding less'
WHEN q21_one_change = '7' THEN 'Faster trip'
WHEN q21_one_change = '8' THEN 'Transfer less'
WHEN q21_one_change = '9' THEN 'Safer trip'
WHEN q21_one_change = '10' THEN 'Fare less expensive'
WHEN q21_one_change = '11' THEN 'Other'
WHEN q21_one_change = '12' THEN 'Nothing'
WHEN q21_one_change = '13' THEN 'Do not know'
else ''
end as one_change,
coalesce(f.q24_zipcode::text, ''),
case
WHEN q25_age = '1' THEN 'Under 18'
WHEN q25_age = '2' THEN '18-24'
WHEN q25_age = '3' THEN '25-34'
WHEN q25_age = '4' THEN '35-44'
WHEN q25_age = '5' THEN '45-54'
WHEN q25_age = '6' THEN '55-64'
WHEN q25_age = '7' THEN '65 or more'
else ''
end as age,
case
WHEN q26_gender = '1' THEN 'Female'
WHEN q26_gender = '2' THEN 'Male'
WHEN q26_gender = '3' THEN 'Transgender'
WHEN q26_gender = '4' THEN 'Other'
else ''
end as gender,
case
WHEN q29_income = '1' THEN 'Under $10,000'
WHEN q29_income = '2' THEN '$10,000-$19,999'
WHEN q29_income = '3' THEN '$20,000-$29,999'
WHEN q29_income = '4' THEN '$30,000-$39,999'
WHEN q29_income = '5' THEN '$40,000-$49,999'
WHEN q29_income = '6' THEN '$50,000-$59,999'
WHEN q29_income = '7' THEN '$60,000-$69,999'
WHEN q29_income = '8' THEN '$70,000-$79,999'
WHEN q29_income = '9' THEN '$80,000-$89,999'
WHEN q29_income = '10' THEN '$90,000-$99,999'
WHEN q29_income = '11' THEN '$100,000-$124,999'
WHEN q29_income = '12' THEN '$125,000-$150,000'
WHEN q29_income = '13' THEN 'Over $150,000'
WHEN q29_income = '14' THEN 'Do not know'
else ''
end as income,
f.time_of_day,
to_char(f._date, 'Mon DD YYYY') as _date
from odk.fall_survey_2016_data f
join odk.rte_lookup r
on f.rte::integer = r.rte and f.dir::integer = r.dir """
query_string += where
query_string += region
query_string += validate
query_string += not_null
query_string += limit
#debug(query_string)
web_session = Session()
query = web_session.execute(query_string)
RTE = 0
RTE_DESC = 1
DIR = 2
DIR_DESC = 3
SATISFACTION = 4
OTYPE = 5
DTYPE = 6
OLAT = 7
OLNG = 8
DLAT = 9
DLNG = 10
TRAVEL_CHANGE = 11
RIDERSHIP = 12
RIDE_YEARS = 13
JOB_APPROVAL = 14
ONE_CHANGE = 15
ZIPCODE = 16
AGE = 17
GENDER = 18
INCOME = 19
TOD = 20
DATE = 21
# each record will be converted as json
# and sent back to page
for record in query:
data = {}
data['rte'] = record[RTE]
data['rte_desc'] = record[RTE_DESC]
data['dir'] = record[DIR]
data['dir_desc'] = record[DIR_DESC]
data['satisfaction'] = record[SATISFACTION]
data['o_type'] = record[OTYPE]
data['d_type'] = record[DTYPE]
data['o_lat'] = float(record[OLAT])
data['o_lng'] = float(record[OLNG])
data['d_lat'] = float(record[DLAT])
data['d_lng'] = float(record[DLNG])
data['travel_change'] = record[TRAVEL_CHANGE]
data['ridership'] = record[RIDERSHIP]
data['ride_years'] = record[RIDE_YEARS]
data['job_approval'] = record[JOB_APPROVAL]
data['one_change'] = record[ONE_CHANGE]
data['zipcode'] = record[ZIPCODE]
data['age'] = record[AGE]
data['gender'] = record[GENDER]
data['income'] = record[INCOME]
data['time_of_day'] = record[TOD]
data['date'] = record[DATE]
ret_val.append(data)
web_session.close()
return ret_val
def query_route_data(user='', rte_desc='', dir_desc='', csv=False):
ret_val = []
query_args = {}
where = ""
#if user: user = "******" + user + "%"
user_filter = " s.name = :user"
rte_desc_filter = " r.rte_desc = :rte_desc "
dir_desc_filter = " r.dir_desc = :dir_desc "
def construct_where(string, param, filt_name):
if not param:
return string
if filt_name == "user": filt = user_filter
elif filt_name == "rte_desc": filt = rte_desc_filter
else: filt = dir_desc_filter
if string:
return string + " AND " + filt
else:
return string + filt
# build where clause
debug(where)
for param in [(user, 'user'), (rte_desc, 'rte_desc'),
(dir_desc, 'dir_desc')]:
where = construct_where(where, param[0], param[1])
debug(where)
query_args[param[1]] = param[0]
if where:
where = " WHERE " + where
limit = "LIMIT 300;"
if csv:
# add headers to csv data
ret_val.append([
'date', 'time', 'user', 'rte_desc', 'dir_desc', 'satisfaction',
'comments'
])
limit = ";"
query_string = """
SELECT
r.rte_desc,
r.dir_desc,
f._date,
date_trunc('second',f._end) as _time,
s.name as user,
case
WHEN q1_satisfaction = '1' THEN 'Very satisfied'
WHEN q1_satisfaction = '3' THEN 'Somewhat satisfied'
WHEN q1_satisfaction = '4' THEN 'Neutral'
WHEN q1_satisfaction = '5' THEN 'Somewhat dissatisfied'
WHEN q1_satisfaction = '6' THEN 'Very dissatisfied'
WHEN q1_satisfaction = '7' THEN 'Do not know'
end as satisfaction,
coalesce(f.q2_satis_comments,'')
from odk.fall_survey_2016_data f
join odk.rte_lookup r
on f.rte::integer = r.rte and f.dir::integer = r.dir
join odk.surveyors s
on f._surveyor = s.username """
query_string += where
query_string += " ORDER BY f._date DESC, f._end DESC "
query_string += limit
debug(query_string)
web_session = Session()
query = web_session.execute(query_string, query_args)
RTE_DESC = 0
DIR_DESC = 1
DATE = 2
TIME = 3
USER = 4
SATISFACTION = 5
COMMENTS = 6
# each record will be converted as json
# and sent back to page
for record in query:
if csv:
data = []
data.append(str(record[DATE]))
data.append(str(record[TIME]))
data.append(record[USER])
data.append(record[RTE_DESC])
data.append(record[DIR_DESC])
data.append(record[SATISFACTION])
data.append(record[COMMENTS])
else:
data = {}
data['date'] = str(record[DATE])
data['time'] = str(record[TIME])
data['user'] = record[USER]
data['rte_desc'] = record[RTE_DESC]
data['dir_desc'] = record[DIR_DESC]
data['satisfaction'] = record[SATISFACTION]
data['comments'] = record[COMMENTS]
ret_val.append(data)
web_session.close()
return ret_val
