-
Notifications
You must be signed in to change notification settings - Fork 5
/
app.py
131 lines (110 loc) · 5.73 KB
/
app.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
from flask import Flask
from flask import render_template
from flask import views
import os
from flask import render_template, request, redirect, url_for, jsonify
from cartodb import CartoDBAPIKey, CartoDBException
import json
#import keys
app = Flask(__name__)
cartodb_key = os.environ.get("cartodb_key")
cartodb_user= os.environ.get("cartodb_user")
@app.route('/')
def index():
cl = CartoDBAPIKey('',cartodb_user)
try:
carto_geoj = cl.sql("SELECT * FROM points ORDER BY random() LIMIT 1;", format='geojson')
id = carto_geoj['features'][0]['properties']['cartodb_id']
#TODO: Parse array of strings, not array of objects as place labels
labels_resp = cl.sql("SELECT pelias_label FROM points WHERE cartodb_id = %s ;" % id)
labels = [[y for y in json.loads(x['pelias_label'])] for x in labels_resp['rows']]
last_row_id_resp = cl.sql("SELECT MAX(cartodb_id) AS id FROM points")
last_row_id = last_row_id_resp['rows'][0]['id']
except CartoDBException as e:
print("some error ocurred", e)
return render_template('index.html',
carto_geoj=json.dumps(carto_geoj),
carto_places=labels,
last_row_id=last_row_id, id=id)
@app.route('/all')
def all():
cl = CartoDBAPIKey('',cartodb_user)
try:
carto_geoj = cl.sql("SELECT * FROM points;", format='geojson')
id = "All"
#TODO: Parse array of strings, not array of objects as place labels
labels_resp = cl.sql("SELECT pelias_label FROM points ")
labels = [[y for y in json.loads(x['pelias_label'])] for x in labels_resp['rows']]
last_row_id_resp = cl.sql("SELECT MAX(cartodb_id) AS id FROM points")
last_row_id = last_row_id_resp['rows'][0]['id']
except CartoDBException as e:
print("some error ocurred", e)
return render_template('index.html',
carto_geoj=json.dumps(carto_geoj),
carto_places=labels,
last_row_id=last_row_id, id=id)
@app.route('/custom')
def custom():
cl = CartoDBAPIKey('',cartodb_user)
group_id = ''
try:
carto_geoj = cl.sql("SELECT * FROM points WHERE group_id = '1';", format='geojson')
id = "Custom"
#TODO: Parse array of strings, not array of objects as place labels
labels_resp = cl.sql("SELECT pelias_label FROM points ")
labels = [[y for y in json.loads(x['pelias_label'])] for x in labels_resp['rows']]
last_row_id_resp = cl.sql("SELECT MAX(cartodb_id) AS id FROM points")
last_row_id = last_row_id_resp['rows'][0]['id']
except CartoDBException as e:
print("some error ocurred", e)
return render_template('index.html',
carto_geoj=json.dumps(carto_geoj),
carto_places=labels,
last_row_id=last_row_id, id=id)
@app.route('/geo', methods=['GET', 'POST'])
def geodata():
cl = CartoDBAPIKey(cartodb_key, cartodb_user)
#TODO: validate that geoJSON is valid and nonmalicious
geodata = json.dumps(request.json)
try:
#TODO: Store places as array of string, not array of object
#TODO: user parameter binding instead of string concatenation
result = json.dumps(cl.sql("DROP TABLE temp ; CREATE TABLE temp AS WITH data AS (SELECT '" + geodata + "'::json AS fc) SELECT row_number() OVER () AS gid, ST_AsText(ST_GeomFromGeoJSON(feat->>'geometry')) AS geom, feat->'group_id' AS properties->>'placenote' FROM (SELECT json_array_elements(fc->'features') AS feat FROM data) , feat->'properties' AS properties FROM (SELECT json_array_elements(fc->'features') AS feat FROM data) AS f; INSERT INTO points (the_geom, group_id, pelias_label,session_id) SELECT ST_COLLECT(ST_SETSRID(geom, 4326)),json_agg(group_id), json_agg(properties), max(gid) from temp;"))
except CartoDBException as e:
print("some error ocurred", e)
return redirect(url_for('index'))
@app.route('/more')
def update():
cl = CartoDBAPIKey('',cartodb_user)
prevRow = int(request.args.get('rowid',''))
try:
#TODO: user parameter binding instead of string concatenation
carto_geoj = cl.sql("SELECT the_geom FROM points WHERE cartodb_id > " + str(prevRow) + ";", format='geojson')
#TODO: Parse array of strings, not array of objects as place labels
labels_resp = cl.sql("SELECT pelias_label FROM points WHERE cartodb_id > " + str(prevRow) + ";")
labels = [[y for y in json.loads(x['pelias_label'])] for x in labels_resp['rows']]
last_row_id_resp = cl.sql("SELECT MAX(cartodb_id) AS id FROM points")
last_row_id = last_row_id_resp['rows'][0]['id']
except CartoDBException as e:
print("some error occurred", e)
return jsonify(multipoints=carto_geoj,
places=labels,
lastrowid=last_row_id)
@app.route('/<int:id>')
def index_s(id):
cl = CartoDBAPIKey('',cartodb_user)
try:
carto_geoj = json.dumps(cl.sql("SELECT * FROM points WHERE cartodb_id= %d;" % id , format='geojson'))
#TODO: Parse array of strings, not array of objects as place labels
labels_resp = cl.sql("SELECT pelias_label FROM points WHERE cartodb_id= %d;" % id)
labels = [[y for y in json.loads(x['pelias_label'])] for x in labels_resp['rows']]
last_row_id_resp = cl.sql("SELECT MAX(cartodb_id) AS id FROM points")
last_row_id = last_row_id_resp['rows'][0]['id']
except CartoDBException as e:
print("some error ocurred", e)
return render_template('index.html',
carto_geoj=carto_geoj,
carto_places=labels,
last_row_id=last_row_id, id=id)
if __name__ == '__main__':
app.run(debug=True)