def test_intersects(self): r1 = session.query(Road).filter(Road.road_name=='Jeff Rd').one() r2 = session.query(Road).filter(Road.road_name=='Graeme Ave').one() r3 = session.query(Road).filter(Road.road_name=='Geordie Rd').one() intersecting_roads = session.query(Road).filter(Road.road_geom.intersects(r1.road_geom)).all() ok_(r2 in intersecting_roads) ok_(r3 not in intersecting_roads) eq_(session.scalar(functions.intersects('POINT(0 0)', 'LINESTRING ( 2 0, 0 2 )')), False)
def test_intersects(self): r1 = session.query(Road).filter(Road.road_name=='Jeff Rd').one() r2 = session.query(Road).filter(Road.road_name=='Graeme Ave').one() r3 = session.query(Road).filter(Road.road_name=='Geordie Rd').one() intersecting_roads = session.query(Road).filter(Road.road_geom.intersects(r1.road_geom)).all() ok_(r2 in intersecting_roads) ok_(r3 not in intersecting_roads) eq_(session.scalar(functions.intersects('POINT(0 0)', 'LINESTRING ( 2 0, 0 2 )')), False)
def _get_profile_filter(self, request): """ Return a spatial filter based on the profile boundary of the current profile which is queried from the database. Copied from the ActivityProtocol3 """ profile = self.Session.query(Profile).\ filter(Profile.code == get_current_profile(request)).\ first() if profile is None: return (Activity.id == 0) return functions.intersects(Activity.point, profile.geometry)
def evaluation_json(self): request = self.request ret = {'success': False} # temp tempSwitch = request.matchdict.get('temp', None) if tempSwitch == '1': groupby = request.params.get('groupby', 'Intention of Investment') input = { 'item': 'Activity', 'filter': { 'geometry': '', 'sql': '' }, 'attributes': { 'Activity': 'count', 'Intended area (ha)': 'sum' }, 'group_by': [groupby] } elif tempSwitch == '2': input = { 'item': 'Activity', 'filter': { 'geometry': '', 'sql': '' }, 'attributes': { 'Activity': 'count', 'Name of Investor': 'count distinct' }, 'group_by': ['Country'] } elif tempSwitch == '3': input = { 'item': 'Activity', 'filter': { 'geometry': '', 'sql': '' }, 'attributes': { 'Contract area (ha)': 'sum', 'Activity': 'count' }, 'group_by': ['Country', 'Country of Investor'] } elif tempSwitch == '4': input = { 'item': 'Activity', 'filter': { 'geometry': '', 'sql': '' }, 'attributes': { 'Activity': 'count', 'Contract area (ha)': 'sum' }, 'group_by': ['Year of agreement'] } elif tempSwitch == '5': input = { 'item': 'Activity', 'filter': { 'geometry': '', 'sql': '' }, 'attributes': { 'Activity': 'count', 'Contract area (ha)': 'sum' }, 'group_by': ['Year of agreement', 'Intention of Investment'] } elif tempSwitch == '6': input = { 'item': 'Stakeholder', 'filter': { 'geometry': '', 'sql': '' }, 'attributes': { 'Stakeholder': 'count' }, 'group_by': ['Country'] } else: ret['msg'] = 'Temporarily only /1, /2 and /3 available.' return ret if input['item'] == 'Stakeholder': Item = Stakeholder Tag_Group = SH_Tag_Group Tag = SH_Tag Key = SH_Key Value = SH_Value else: Item = Activity Tag_Group = A_Tag_Group Tag = A_Tag Key = A_Key Value = A_Value # Test input if 'group_by' not in input: ret['msg'] = "Missing parameter 'group by': At least one column needs to be specified." return ret if not isinstance(input['group_by'], list): ret['msg'] = "Parameter 'group by' needs to be an array." return ret if 'attributes' not in input: ret['msg'] = "Missing attributes: No attributes were specified." return ret for attr in input['attributes']: test = self._checkFunction(Item, Tag_Group, Tag, Key, Value, input['attributes'][attr], attr) if test is not True: ret['msg'] = test return ret # Get groups groups_subqueries, groups_columns = self._getGroupBy(Item, Tag_Group, Tag, Key, Value, input['group_by']) # Get functions functions_subqueries, functions_columns = self._getAttributeFunctions(Item, Tag_Group, Tag, Key, Value, input['attributes']) # Prepare basic query (already joins first group) q = Session.query(*groups_columns + functions_columns).\ join(Tag_Group).\ join(Item) # Join with further groups for g_sq in groups_subqueries[1:]: q = q.outerjoin(g_sq, g_sq.c.a_id == Item.id) # Join with functions for f_sq in functions_subqueries: q = q.outerjoin(f_sq, f_sq.c.a_id == Item.id) # Apply status filter (fix: active) fk_status = Session.query(Status.id).filter(Status.name == 'active') q = q.filter(Item.fk_status == fk_status) # Apply profile boundary filter if Item == Activity: profile = Session.query(Profile).\ filter(Profile.code == get_current_profile(request)).\ first() if profile is not None: q = q.filter(functions.intersects(Item.point, profile.geometry)) # Apply grouping and ordering q = q.group_by(*groups_columns).\ order_by(groups_columns[0]) data = [] for res in q.all(): entry = {} # first go through group_by for i, group in enumerate(input['group_by']): entry[group] = res[i] # then go through functions for i, attr in enumerate(input['attributes']): displayAttr = attr if attr == 'Activity': displayAttr = 'Deals' entry["%s (%s)" % (displayAttr, input['attributes'][attr])] = res[i + len(input['group_by'])] data.append(entry) ret['success'] = True ret['data'] = data return ret
def evaluation(self, data=None): ret = {'success': False} json_data = self.request.json_body if data is None else data if json_data is None: ret['msg'] = 'No data provided' return ret if validate_item_type(json_data.get('item', 'a')) == 'sh': self.db_item = Stakeholder self.db_taggroup = SH_Tag_Group self.db_tag = SH_Tag self.db_key = SH_Key self.db_value = SH_Value self.protocol = StakeholderProtocol3(Session) else: self.db_item = Activity self.db_taggroup = A_Tag_Group self.db_tag = A_Tag self.db_key = A_Key self.db_value = A_Value self.protocol = ActivityProtocol3(Session) # Make sure the json is valid if 'group_by' not in json_data: ret['msg'] = "Missing parameter 'group by': At least one column " "needs to be specified." return ret if not isinstance(json_data['group_by'], list): ret['msg'] = "Parameter 'group by' needs to be an array." return ret if 'attributes' not in json_data: ret['msg'] = "Missing attributes: No attributes were specified." return ret for attr in json_data['attributes']: test, msg = self._check_function( json_data['attributes'][attr], attr) if test is not True: ret['msg'] = msg return ret if 'locales' in json_data and not isinstance( json_data['locales'], list): ret['msg'] = "Parameter 'locales' needs to be an array." return ret translate_keys = json_data.get('translate', {}).get('keys', []) if translate_keys and not isinstance(translate_keys, list): ret['msg'] = "Parameter 'translate[\'keys\']' needs to be an " "array." return ret for k in translate_keys: if not isinstance(k, list): ret['msg'] = "Value of 'translate[\'keys\']' needs to be " "an array of arrays." return ret a_ids = json_data.get('a_ids', []) if not isinstance(a_ids, list): ret['msg'] = "Parameter 'a_ids' needs to be an array." return ret for i in a_ids: if not isinstance(i, str): ret['msg'] = "Entries of parameter 'a_ids' need to be " "strings (the UUIDs of Activities)" return ret sh_ids = json_data.get('sh_ids', []) if not isinstance(sh_ids, list): ret['msg'] = "Parameter 'sh_ids' needs to be an array." return ret for i in sh_ids: if not isinstance(i, str): ret['msg'] = "Entries of parameter 'sh_ids' need to be " "strings (the UUIDs of Stakeholders)" return ret if self.db_item == Activity: this_id_filter = a_ids other_id_filter = sh_ids else: this_id_filter = sh_ids other_id_filter = a_ids this_filter = [] other_filter = [] if 'filter' in json_data: params = [] for filters in json_data.get('filter', '').split('&'): try: f = filters.split('=') if len(f) == 2: params.append((f[0], f[1])) except: pass # Simulate a request to send the filters req = DummyRequest() req.params = MultiDict(params) a_tag_filter, __, sh_tag_filter, __ = self.protocol._filter(req) if self.db_item == Activity: this_filter = a_tag_filter other_filter = sh_tag_filter else: this_filter = sh_tag_filter other_filter = a_tag_filter isInvolvementRequired = ( self.db_item == Stakeholder or len(other_filter) + len(other_id_filter) > 0) # Collect all keys to be translated (values are translated in the # query) locales = ['default'] langs = [] locales.extend(json_data.get('locales', [])) translated_keys = {} exclude_from_translation = ['Activity', 'Stakeholder'] keys = [] for key, __ in json_data.get('attributes', {}).iteritems(): if key not in exclude_from_translation and key not in keys: keys.append(key) for key in json_data.get('group_by', []): if key not in exclude_from_translation and key not in keys: keys.append(key) for key in translate_keys: for k in key: if k not in keys: keys.append(k) for l in locales: locale = l if l == 'default': locale = get_current_locale(self.request) db_lang = Session.query(Language).filter( Language.locale == locale).first() langs.append((l, db_lang)) translated_keys[l] = get_translated_db_keys( self.db_key, keys, db_lang) # Get groups groups_subqueries, groups_columns = self._get_group_by( json_data['group_by'], langs) # Get functions functions_subqueries, functions_columns = \ self._get_attribute_functions(json_data['attributes']) # Prepare basic query q = Session.query(*groups_columns + functions_columns).\ join(self.db_taggroup).\ join(self.db_item) # Join with further groups for g_sq in groups_subqueries[1:]: q = q.outerjoin(g_sq, g_sq.c.item_id == self.db_item.id) # Join with functions for f_sq in functions_subqueries: q = q.outerjoin(f_sq, f_sq.c.item_id == self.db_item.id) # Apply status filter (fix: active) q = q.filter(self.db_item.fk_status == 2) if (this_id_filter): q = q.filter(self.db_item.identifier.in_(this_id_filter)) # Apply filters filter_subqueries = self.protocol.Session.query( self.db_item.id.label('a_filter_id') ) for x in this_filter: # Collect the IDs for each filter taggroups_sq = x.subquery() single_subquery = self.protocol.Session.query( self.db_item.id.label('a_filter_id') ).\ join(self.db_taggroup).\ join(taggroups_sq, taggroups_sq.c.a_filter_tg_id == self.db_taggroup.id).\ subquery() # Join each found ID with previously found IDs filter_subqueries = filter_subqueries.\ join(single_subquery, single_subquery.c.a_filter_id == self.db_item.id) filter_subqueries = filter_subqueries.subquery() q = q.join( filter_subqueries, filter_subqueries.c.a_filter_id == self.db_item.id) # Apply profile boundary filter if self.db_item == Activity: p = json_data.get('profile', get_current_profile(self.request)) profile = Session.query(Profile).\ filter(Profile.code == p).\ first() if profile is not None: q = q.filter(geofunctions.intersects( self.db_item.point, profile.geometry)) # Apply grouping and ordering q = q.group_by(*groups_columns).\ order_by(groups_columns[0]) if isInvolvementRequired: if self.db_item == Stakeholder: inv_subquery = Session.query( Involvement.fk_stakeholder.label('id') ).\ join(Activity).\ filter(Activity.fk_status == 2) p = json_data.get('profile', get_current_profile(self.request)) profile = Session.query(Profile).\ filter(Profile.code == p).\ first() if profile is not None: inv_subquery = inv_subquery.filter(geofunctions.intersects( Activity.point, profile.geometry)) other_db_item = Activity other_db_taggroup = A_Tag_Group else: inv_subquery = Session.query( Involvement.fk_activity.label('id') ).\ join(Stakeholder).\ filter(Stakeholder.fk_status == 2) other_db_item = Stakeholder other_db_taggroup = SH_Tag_Group if (other_id_filter): inv_subquery = inv_subquery.filter( other_db_item.identifier.in_(other_id_filter)) # Apply filters filter_subqueries = self.protocol.Session.query( other_db_item.id.label('a_filter_id') ) for x in other_filter: # Collect the IDs for each filter taggroups_sq = x.subquery() try: single_subquery = self.protocol.Session.query( other_db_item.id.label('a_filter_id') ).\ join(other_db_taggroup).\ join(taggroups_sq, taggroups_sq.c.a_filter_tg_id == other_db_taggroup.id).\ subquery() except AttributeError: single_subquery = self.protocol.Session.query( other_db_item.id.label('a_filter_id') ).\ join(other_db_taggroup).\ join(taggroups_sq, taggroups_sq.c.sh_filter_tg_id == other_db_taggroup.id).\ subquery() # Join each found ID with previously found IDs filter_subqueries = filter_subqueries.\ join(single_subquery, single_subquery.c.a_filter_id == other_db_item.id) filter_subqueries = filter_subqueries.subquery() inv_subquery = inv_subquery.join( filter_subqueries, filter_subqueries.c.a_filter_id == other_db_item.id) inv_subquery = inv_subquery.subquery() q = q.filter(self.db_item.id.in_( select([inv_subquery.c.id]) )) data = [] for res in q.all(): data = _handle_single_line( data, res, json_data.get('group_by'), json_data.get('attributes'), translated_keys) # Do a translation of groupable if available groupable_translated = [] for key in translate_keys: translations = [] for k in key: t = { 'key': k, 'default': k } for locale, key_translations in translated_keys.iteritems(): translation = ( None if k not in exclude_from_translation else k) for k_t in key_translations: if len(k_t) >= 2 and k_t[0] == k: translation = k_t[1] t[locale] = translation translations.append(t) groupable_translated.append(translations) if len(groupable_translated): ret.update({ 'translate': {'keys': groupable_translated} }) ret.update({ 'success': True, 'data': data }) return ret