-
Notifications
You must be signed in to change notification settings - Fork 22
/
query_builder.py
267 lines (204 loc) · 9.13 KB
/
query_builder.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
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
# openpoiservice/server/query_builder.py
from openpoiservice.server import db
from openpoiservice.server import categories_tools, ops_settings
import geoalchemy2.functions as geo_func
from geoalchemy2.types import Geography
from shapely import wkb
from shapely.geometry import MultiPoint, Point
from openpoiservice.server.db_import.models import POIs, Tags, Categories
from sqlalchemy.sql.expression import type_coerce
from sqlalchemy import func
import geojson as geojson
import logging
logger = logging.getLogger(__name__)
class QueryBuilder(object):
""" Class that creates the query."""
def __init__(self, payload):
"""
Initializes the query builder.
:param payload: processed GET or POST parameters
:type payload: dict
"""
self.payload = payload
def request_pois(self):
"""
Queries pois or statistics from the database. Pois will always be less then querying category statistics
because one poi can have several categories.
:return: Will either return a feature collection of pois or poi statistics.
:type: dict
"""
params = self.payload
geom_filters, geom = self.generate_geom_filters(params['geometry'], POIs)
logger.debug('geometry filters: {}, geometry: {}'.format(geom_filters, geom))
category_filters = []
if 'filters' in params and 'category_ids' in params['filters']:
category_filters.append(Categories.category.in_(params['filters']['category_ids']))
# currently only available for request=pois
custom_filters = []
if 'filters' in params:
custom_filters = self.generate_custom_filters(params['filters'])
if params['request'] == 'stats':
bbox_query = db.session \
.query(POIs.osm_id, Categories.category) \
.filter(*geom_filters) \
.filter(*category_filters) \
.outerjoin(Categories) \
.subquery()
stats_query = db.session \
.query(bbox_query.c.category, func.count(bbox_query.c.category).label("count")) \
.group_by(bbox_query.c.category) \
# .all()
places_json = self.generate_category_stats(stats_query)
return places_json
# join with tags
elif params['request'] == 'pois':
bbox_query = db.session \
.query(POIs) \
.filter(*geom_filters) \
.subquery()
# sortby needed here for generating features in next step
# sortby_group = [bbox_query.c.osm_id]
sortby_group = []
if 'sortby' in params:
if params['sortby'] == 'distance':
sortby_group.append(geo_func.ST_Distance(type_coerce(geom, Geography), bbox_query.c.geom))
elif params['sortby'] == 'category':
sortby_group.append(bbox_query.c.category)
keys_agg = func.array_agg(Tags.key).label('keys')
values_agg = func.array_agg(Tags.value).label('values')
categories_agg = func.array_agg(Categories.category).label('categories')
pois_query = db.session \
.query(bbox_query.c.osm_id,
bbox_query.c.osm_type,
bbox_query.c.geom.ST_Distance(type_coerce(geom, Geography)),
bbox_query.c.geom,
keys_agg,
values_agg,
categories_agg) \
.order_by(*sortby_group) \
.filter(*category_filters) \
.filter(*custom_filters) \
.outerjoin(Tags) \
.outerjoin(Categories) \
.group_by(bbox_query.c.osm_id) \
.group_by(bbox_query.c.osm_type) \
.group_by(bbox_query.c.geom) \
# response as geojson feature collection
return self.generate_geojson_features(pois_query, params['limit'])
@staticmethod
def generate_geom_filters(geometry, poi):
filters, geom = [], None
if 'bbox' in geometry and 'geom' not in geometry:
geom = geometry['bbox'].wkt
filters.append(
geo_func.ST_DWithin(geo_func.ST_Buffer(type_coerce(geom, Geography), geometry['buffer']), poi.geom, 0))
elif 'bbox' in geometry and 'geom' in geometry:
geom_bbox = geometry['bbox'].wkt
geom = geometry['geom'].wkt
filters.append( # in bbox
geo_func.ST_DWithin(
geo_func.ST_Intersection(geo_func.ST_Buffer(type_coerce(geom, Geography), geometry['buffer']),
type_coerce(geom_bbox, Geography)), poi.geom, 0))
elif 'bbox' not in geometry and 'geom' in geometry:
geom = geometry['geom'].wkt
filters.append( # buffer around geom
geo_func.ST_DWithin(geo_func.ST_Buffer(type_coerce(geom, Geography), geometry['buffer']), poi.geom, 0)
)
return filters, geom
@staticmethod
def generate_custom_filters(filters):
"""
Generates a list of custom filters used for query.
:param filters:
:return: returns sqlalchemy filters
:type: list
"""
custom_filters = []
for tag, settings in ops_settings['column_mappings'].items():
if tag in filters:
custom_filters.append(Tags.key == tag.lower())
custom_filters.append(Tags.value.in_(filters[tag]))
return custom_filters
@classmethod
def generate_category_stats(cls, query):
"""
Generates the json object from group by query.
:param query: response from the database
:type query: list of objects
:return: returns a dictionary with poi statistics
:type: dict
"""
places_dict = {
"places": {
"total_count": 0
}
}
for poi_group in query:
# get the group of this category
category_group = categories_tools.category_to_group_index[poi_group.category]
group_id = category_group["group_id"]
group_name = category_group["group_name"]
category_obj = {
categories_tools.category_ids_index[poi_group.category]['poi_name']: {
'count': poi_group.count,
'category_id': poi_group.category
}
}
if group_name not in places_dict["places"]:
places_dict["places"][group_name] = {
"group_id": group_id,
"categories": category_obj,
"total_count": poi_group.count
}
elif group_name in places_dict["places"]:
places_dict["places"][group_name]['categories'].update(category_obj)
places_dict["places"][group_name]['total_count'] += poi_group.count
places_dict["places"]["total_count"] += poi_group.count
logger.debug('Number of poi stats groups: {}'.format(len(places_dict)))
return places_dict
@classmethod
def generate_geojson_features(cls, query, limit):
"""
Generates a GeoJSON feature collection from the response.
:param limit:
:param query: the response from the database
:type query: list of objects
:return: GeoJSON feature collection containing properties
:type: list
"""
geojson_features = []
lat_lngs = []
for q_idx, q in enumerate(query):
geometry = wkb.loads(str(q[3]), hex=True)
x = float(format(geometry.x, ".6f"))
y = float(format(geometry.y, ".6f"))
trimmed_point = Point(x, y)
lat_lngs.append((trimmed_point.x, trimmed_point.y))
properties = dict(
osm_id=int(q[0]),
osm_type=int(q[1]),
distance=float(q[2])
)
category_ids_obj = {}
for c_id in set(q[6]):
category_name = categories_tools.category_ids_index[c_id]['poi_name']
category_group = categories_tools.category_ids_index[c_id]['poi_group']
category_ids_obj[c_id] = {
"category_name": category_name,
"category_group": category_group
}
properties["category_ids"] = category_ids_obj
if q[5][0] is not None:
key_values = {}
for idx, key in enumerate(q[4]):
key_values[key] = q[5][idx]
properties["osm_tags"] = key_values
geojson_feature = geojson.Feature(geometry=trimmed_point,
properties=properties)
geojson_features.append(geojson_feature)
# limit reached
if q_idx == limit - 2:
break
feature_collection = geojson.FeatureCollection(geojson_features, bbox=MultiPoint(lat_lngs).bounds)
logger.debug("Amount of features {}".format(len(geojson_features)))
return feature_collection