Exemple #1
0
    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
Exemple #2
0
 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
Exemple #4
0
    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
Exemple #6
0
    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,
                )
            )