def get_running_geodata(areaName):
dp = DatabasePort()
with dp.connection_handler(commit=True,
cursor_factory=DictCursor) as cursor:
query = "WITH running AS (" \
" SELECT DISTINCT ST_Transform(line.way, 4326) AS line_way, line.highway " \
" FROM planet_osm_polygon AS polygon " \
" CROSS JOIN planet_osm_line AS line " \
" WHERE polygon.name = %s AND ST_Intersects(polygon.way, line.way) " \
" AND line.highway " \
" IN ('sidewalk', 'path', 'footway', 'bridleway', 'steps', 'pedestrian', 'living_street') " \
" ) " \
"SELECT json_build_object(" \
"'type', 'Feature'," \
"'geometry', ST_AsGeoJSON(line_way)::json," \
"'properties', json_build_object(" \
" 'title', highway," \
" 'description', round(ST_Length(line_way::GEOGRAPHY)) || 'm'," \
" 'stroke', '#d1004c'," \
" 'stroke-opacity', 0.7," \
" 'stroke-width', 3.25" \
" )" \
") FROM running;"
cursor.execute(query, (areaName, ))
rows = list(chain(*cursor.fetchall()))
return rows
def get_pitch_sport_list():
dp = DatabasePort()
with dp.connection_handler(commit=True) as cursor:
query = "SELECT DISTINCT unnest(string_to_array(plgn.sport, ';')) " \
"AS sport FROM planet_osm_polygon AS plgn " \
"WHERE leisure = 'pitch' ORDER BY sport ASC;"
cursor.execute(query)
rows = list(chain(*cursor.fetchall()))
return rows
def render_burgers():
dp = DatabasePort()
with dp.connection_handler(commit=True, cursor_factory=DictCursor) as cursor:
with open('./static/queries.json') as queries:
data = json.load(queries)
query = data['state_statistics']
cursor.execute(query)
rows = list(chain(*cursor.fetchall()))
return jsonify(rows)
def render_fat():
dp = DatabasePort()
with dp.connection_handler(commit=True, cursor_factory=DictCursor) as cursor:
with open('./static/queries.json') as queries:
data = json.load(queries)
query = data['obesity_per_state']
cursor.execute(query)
rows = list(chain(*cursor.fetchall()))
obesity_arrs = [[], [], [], []]
for state in rows:
if state['obesity'] >= 20 and state['obesity'] < 24:
state['properties']['fill'] = "#32CD32"
obesity_arrs[0].append(state['obesity'])
elif state['obesity'] >= 24 and state['obesity'] < 28:
state['properties']['fill'] = "#FFD700"
obesity_arrs[1].append(state['obesity'])
elif state['obesity'] >= 28 and state['obesity'] < 32:
state['properties']['fill'] = "#FF8C00"
obesity_arrs[2].append(state['obesity'])
else:
state['properties']['fill'] = "#DC143C"
obesity_arrs[3].append(state['obesity'])
for lst in obesity_arrs:
lst.sort()
a = 0.5 # lower opacity
b = 0.8 # higher opacity
diff = 0
for state in rows:
if state['obesity'] > 20 and state['obesity'] < 24:
diff = (obesity_arrs[0][-1] - obesity_arrs[0][0]) / 2
if state['obesity'] < obesity_arrs[0][0] + diff:
state['properties']['fill-opacity'] = a
else:
state['properties']['fill-opacity'] = b
elif state['obesity'] >= 24 and state['obesity'] < 28:
diff = (obesity_arrs[1][-1] - obesity_arrs[1][0]) / 2
if state['obesity'] < obesity_arrs[1][0] + diff:
state['properties']['fill-opacity'] = a
else:
state['properties']['fill-opacity'] = b
elif state['obesity'] >= 28 and state['obesity'] < 32:
diff = (obesity_arrs[2][-1] - obesity_arrs[2][0]) / 2
if state['obesity'] < obesity_arrs[2][0] + diff:
state['properties']['fill-opacity'] = a
else:
state['properties']['fill-opacity'] = b
else:
diff = (obesity_arrs[3][-1] - obesity_arrs[3][0]) / 2
if state['obesity'] < obesity_arrs[3][0] + diff:
state['properties']['fill-opacity'] = a
else:
state['properties']['fill-opacity'] = b
return jsonify(rows)
def render_index():
data = []
dp = DatabasePort()
with dp.connection_handler(commit=True, cursor_factory=DictCursor) as cursor:
with open('./static/queries.json') as queries:
data = json.load(queries)
query = data['get_huouston_parts']
cursor.execute(query)
rows = list(chain(*cursor.fetchall()))
data = rows
return render_template('index.html', data=map(json.dumps, data))
def McDonaldHeatmap():
houstonPart = request.args.get('state')
print(houstonPart, type(houstonPart))
dp = DatabasePort()
with dp.connection_handler(commit=True, cursor_factory=DictCursor) as cursor:
with open('./static/queries.json') as queries:
data = json.load(queries)
query = data['find_specific_houston_part']
cursor.execute(query, (houstonPart,))
rows = list(chain(*cursor.fetchall()))
query = data['mcdonalds_in_houstuon_part']
cursor.execute(query, (houstonPart,))
lst = list(chain(*cursor.fetchall()))
rows += lst
return jsonify(rows)
def get_pitch_geodata(sport_type):
dp = DatabasePort()
with dp.connection_handler(commit=True,
cursor_factory=DictCursor) as cursor:
query = "SELECT json_build_object(" \
"'type', 'Feature'," \
"'geometry', ST_AsGeoJSON(ST_Transform(way, 4326))::json," \
"'properties', json_build_object(" \
" 'title', sport," \
" 'description', round(ST_Area(ST_Transform(way, 4326)::GEOGRAPHY)) || ' m2'," \
" 'fill', '#ff1462'," \
" 'stroke', '#ff1462'," \
" 'stroke-width', 1.5" \
" )" \
") FROM planet_osm_polygon " \
"WHERE leisure = 'pitch' AND sport = %s"
cursor.execute(query, (sport_type, ))
rows = list(chain(*cursor.fetchall()))
return rows
def get_gym_geodata(dist, search_location):
lat = search_location['lat']
lng = search_location['lng']
postgis_location = 'SRID=4326;POINT(' + str(lng) + ' ' + str(lat) + ')'
dp = DatabasePort()
with dp.connection_handler(commit=True,
cursor_factory=DictCursor) as cursor:
query = "SELECT json_build_object(" \
"'type', 'Feature'," \
"'geometry', ST_AsGeoJSON(sub.geog_way)::json," \
"'properties', json_build_object(" \
" 'title', sub.name," \
" 'description', coalesce(sub.leisure, sub.amenity)," \
" 'marker-color', '#3bb2d0'," \
" 'marker-size', 'medium'," \
" 'marker-symbol', 'circle'" \
" )" \
") FROM (" \
"SELECT ST_Transform(way, 4326) AS geog_way, name, leisure, amenity FROM planet_osm_point " \
"WHERE leisure = 'fitness_centre' OR amenity = 'gym' " \
"UNION " \
"SELECT ST_Transform(ST_Centroid(way), 4326) AS geog_way, name, leisure, amenity FROM planet_osm_polygon " \
"WHERE leisure = 'fitness_centre' OR amenity = 'gym' " \
") AS sub WHERE ST_DWithin(sub.geog_way::GEOGRAPHY, ST_GeogFromText(%s), %s);"
cursor.execute(query, (postgis_location, dist))
rows = list(chain(*cursor.fetchall()))
search_location_point = {
'type': 'Feature',
'geometry': {
'type': 'Point',
'coordinates': [lng, lat]
},
'properties': {
'title': 'Your location',
'description': 'Distance to search = ' + str(dist) + 'm',
'marker-color': '#f44274',
'marker-size': 'large',
'marker-symbol': 'star'
}
}
rows.append(search_location_point.copy())
return rows
def get_city_area_geodata(areaName):
dp = DatabasePort()
with dp.connection_handler(commit=True,
cursor_factory=DictCursor) as cursor:
query = "SELECT json_build_object(" \
"'type', 'Feature'," \
"'geometry', ST_AsGeoJSON(ST_Transform(way, 4326))::json," \
"'properties', json_build_object(" \
" 'title', name," \
" 'fill', '#003b84'," \
" 'fill-opacity', 0.15," \
" 'stroke-width', 5," \
" 'stroke-opacity', 0.9" \
" )" \
") FROM planet_osm_polygon " \
"WHERE to_tsvector('sk', name) @@ to_tsquery('sk', %s) " \
"ORDER BY NAME <-> %s ASC " \
"LIMIT 1;"
cursor.execute(query, (areaName, areaName))
rows = list(chain(*cursor.fetchall()))
return rows