def generate_geom_filters(geometry, Pois):
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']), Pois.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)), Pois.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']), Pois.geom, 0))
return filters, geom
def test_ST_Distance_type_coerce(self):
from sqlalchemy.sql.expression import type_coerce
poi_id = self._create_one_poi()
poi = session.query(Poi) \
.filter(Poi.geog.ST_Distance(
type_coerce('POINT(5 45)', Geography)) < 1000).one()
eq_(poi.id, poi_id)
def test_ST_Distance_type_coerce(self):
poi_id = self._create_one_poi()
poi = session.query(Poi) \
.filter(Poi.geog.ST_Distance(
type_coerce('POINT(5 45)', Geography)) < 1000).one()
assert poi.id == poi_id
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:
if '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.uuid, 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)
# start = timer()
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.uuid) \
.group_by(bbox_query.c.osm_id) \
.group_by(bbox_query.c.osm_type) \
.group_by(bbox_query.c.geom) \
# .all()
# end = timer()
# print(end - start)
# print(str(pois_query))
# for dude in pois_query:
# print(dude)
# response as geojson feature collection
features = self.generate_geojson_features(pois_query,
params['limit'])
return features
def test_ST_Distance_type_coerce(self):
poi_id = self._create_one_poi()
poi = session.query(Poi) \
.filter(Poi.geog.ST_Distance(
type_coerce('POINT(5 45)', Geography)) < 1000).one()
assert poi.id == poi_id
def _get_paid_not_approved_query(cls, query_type, start_date, end_date):
"""
Gets the query for paid but not approved shares between and including
start and end date.
Args:
query_type: The type of the query to be build. 'data' for
retrieving rows and 'shares_count' for an aggregate count
query.
start_date: The first date of which paid and not approved shares
are considered.
end_date: The last date of which paid and not approved shares are
considered.
Returns:
A query according to the specified query_type.
For 'data' the query is build to retrieve rows with attributes 'id'
for member id, 'lastname' for the member's lastname, 'firstname'
for the member's firstname, 'shares_count' for the number of shares
and 'payment_received_date' for the date on which the payment was
received
For 'shares_count' an aggregate count query is returned to retrieve
the number of shares of all relevant shares packages.
"""
# Shares which of the day of the request have not been approved are not
# yet stored in Shares but only available on the C3sMember.
shares_count = expression.case(
# "== None" for SqlAlchemy instead of Python "is None"
# pylint: disable=singleton-comparison
[(Shares.id == None, C3sMember.num_shares)],
else_=Shares.number
)
payment_received_date = expression.case(
[(
# "== None" for SqlAlchemy instead of Python "is None"
# pylint: disable=singleton-comparison
Shares.id == None,
# C3sMember.payment_received_date has the data type DateTime
# but Date is required as it is used in
# Shares.payment_received_date. As CAST on DateTime '2017-01-02
# 12:23:34.456789' returns '2017' in SQLite and therefore
# cannot be used substring is used instead and then SQLAlchemy
# is forced by type_coerce to parse it as a Date column.
expression.type_coerce(
func.substr(C3sMember.payment_received_date, 1, 10), Date)
)],
else_=Shares.payment_received_date
)
# SqlAlchemy equality to None must be used as "== None" instead of
# Python "is not None".
date_of_acquisition = expression.case(
# "== None" for SqlAlchemy instead of Python "is None"
# pylint: disable=singleton-comparison
[(Shares.id == None, C3sMember.membership_date)],
else_=Shares.date_of_acquisition
)
if query_type == 'data':
# pylint: disable=no-member
query = DBSession.query(
C3sMember.id,
C3sMember.lastname,
C3sMember.firstname,
shares_count.label('shares_count'),
payment_received_date.label('payment_received_date'),
)
if query_type == 'shares_count':
# pylint: disable=no-member
query = DBSession.query(
func.sum(shares_count)
)
# Use outer joins as Shares do not have to exist yet.
return query.select_from(C3sMember) \
.outerjoin(members_shares) \
.outerjoin(Shares) \
.filter(
expression.and_(
# membership not approved in time period
expression.or_(
# membership or share approved later than end date
date_of_acquisition > end_date,
# or membership or share not approved yet (default
# date)
date_of_acquisition == date(1970, 1, 1),
),
# payment received in time period
payment_received_date >= start_date,
payment_received_date <= end_date,
)
)
def _get_paid_not_approved_query(cls, query_type, start_date, end_date):
"""
Gets the query for paid but not approved shares between and including
start and end date.
Args:
query_type: The type of the query to be build. 'data' for
retrieving rows and 'shares_count' for an aggregate count
query.
start_date: The first date of which paid and not approved shares
are considered.
end_date: The last date of which paid and not approved shares are
considered.
Returns:
A query according to the specified query_type.
For 'data' the query is build to retrieve rows with attributes 'id'
for member id, 'lastname' for the member's lastname, 'firstname'
for the member's firstname, 'shares_count' for the number of shares
and 'payment_received_date' for the date on which the payment was
received
For 'shares_count' an aggregate count query is returned to retrieve
the number of shares of all relevant shares packages.
"""
# Shares which of the day of the request have not been approved are not
# yet stored in Shares but only available on the C3sMember.
shares_count = expression.case(
# "== None" for SqlAlchemy instead of Python "is None"
# pylint: disable=singleton-comparison
[(Shares.id == None, C3sMember.num_shares)],
else_=Shares.number
)
payment_received_date = expression.case(
[(
# "== None" for SqlAlchemy instead of Python "is None"
# pylint: disable=singleton-comparison
Shares.id == None,
# C3sMember.payment_received_date has the data type DateTime
# but Date is required as it is used in
# Shares.payment_received_date. As CAST on DateTime '2017-01-02
# 12:23:34.456789' returns '2017' in SQLite and therefore
# cannot be used substring is used instead and then SQLAlchemy
# is forced by type_coerce to parse it as a Date column.
expression.type_coerce(
func.substr(C3sMember.payment_received_date, 1, 10), Date)
)],
else_=Shares.payment_received_date
)
# SqlAlchemy equality to None must be used as "== None" instead of
# Python "is not None".
date_of_acquisition = expression.case(
# "== None" for SqlAlchemy instead of Python "is None"
# pylint: disable=singleton-comparison
[(Shares.id == None, C3sMember.membership_date)],
else_=Shares.date_of_acquisition
)
if query_type == 'data':
# pylint: disable=no-member
query = DBSession.query(
C3sMember.id,
C3sMember.lastname,
C3sMember.firstname,
shares_count.label('shares_count'),
payment_received_date.label('payment_received_date'),
)
if query_type == 'shares_count':
# pylint: disable=no-member
query = DBSession.query(
func.sum(shares_count)
)
# Use outer joins as Shares do not have to exist yet.
return query.select_from(C3sMember) \
.outerjoin(members_shares) \
.outerjoin(Shares) \
.filter(
expression.and_(
# membership not approved in time period
expression.or_(
# membership or share approved later than end date
date_of_acquisition > end_date,
# or membership or share not approved yet (default
# date)
date_of_acquisition == date(1970, 1, 1),
),
# payment received in time period
payment_received_date >= start_date,
payment_received_date <= end_date,
)
)