def make_grid(self, resolution, geom=None, conditions=None): """ :param resolution: length of side of grid square in meters :type resolution: int :param geom: string representation of geojson fragment :type geom: str :param conditions: conditions on columns to filter on :type conditions: list of SQLAlchemy binary operations (e.g. col > value) :return: grid: result proxy with all result rows size_x, size_y: the horizontal and vertical size of the grid squares in degrees """ if conditions is None: conditions = [] # We need to convert resolution (given in meters) to degrees # - which is the unit of measure for EPSG 4326 - # - in order to generate our grid. center = self.get_bbox_center() # center[1] is longitude size_x, size_y = get_size_in_degrees(resolution, center[1]) # Generate a count for each resolution by resolution square t = self.point_table q = session.query(func.count(t.c.hash), func.ST_SnapToGrid(t.c.geom, size_x, size_y) .label('squares'))\ .filter(*conditions)\ .group_by('squares') if geom: q = q.filter(t.c.geom.ST_Within(func.ST_GeomFromGeoJSON(geom))) return session.execute(q), size_x, size_y
def make_grid(self, resolution, geom=None, conditions=None): """ :param resolution: length of side of grid square in meters :type resolution: int :param geom: string representation of geojson fragment :type geom: str :param conditions: conditions on columns to filter on :type conditions: list of SQLAlchemy binary operations (e.g. col > value) :return: grid: result proxy with all result rows size_x, size_y: the horizontal and vertical size of the grid squares in degrees """ if conditions is None: conditions = [] # We need to convert resolution (given in meters) to degrees # - which is the unit of measure for EPSG 4326 - # - in order to generate our grid. center = self.get_bbox_center() # center[1] is longitude size_x, size_y = get_size_in_degrees(resolution, center[1]) # Generate a count for each resolution by resolution square t = self.point_table q = session.query(func.count(t.c.hash), func.ST_SnapToGrid(t.c.geom, size_x, size_y) .label('squares'))\ .filter(*conditions)\ .group_by('squares') if geom: q = q.filter(t.c.geom.ST_Within(func.ST_GeomFromGeoJSON(geom))) return session.execute(q), size_x, size_y
def make_fragment_str(geojson_fragment, buffer=100): if geojson_fragment['type'] == 'LineString': shape = asShape(geojson_fragment) lat = shape.centroid.y x, y = get_size_in_degrees(buffer, lat) geojson_fragment = shape.buffer(y).__geo_interface__ geojson_fragment['crs'] = {"type": "name", "properties": {"name": "EPSG:4326"}} return json.dumps(geojson_fragment)
def make_fragment_str(geojson_fragment, buffer=100): if geojson_fragment['type'] == 'LineString': shape = asShape(geojson_fragment) lat = shape.centroid.y x, y = get_size_in_degrees(buffer, lat) geojson_fragment = shape.buffer(y).__geo_interface__ geojson_fragment['crs'] = {"type": "name", "properties": {"name": "EPSG:4326"}} return json.dumps(geojson_fragment)
def make_query(table, raw_query_params): table_keys = table.columns.keys() args_keys = raw_query_params.keys() resp = { 'meta': { 'status': 'error', 'message': '', }, 'objects': [], } status_code = 200 query_clauses = [] valid_query = True #print "make_query(): args_keys = ", args_keys if 'offset' in args_keys: args_keys.remove('offset') if 'limit' in args_keys: args_keys.remove('limit') if 'order_by' in args_keys: args_keys.remove('order_by') if 'weather' in args_keys: args_keys.remove('weather') for query_param in args_keys: try: field, operator = query_param.split('__') #print "make_query(): field, operator =", field, operator except ValueError: field = query_param operator = 'eq' query_value = raw_query_params.get(query_param) column = table.columns.get(field) if field not in table_keys: resp['meta']['message'] = '"%s" is not a valid fieldname' % field status_code = 400 valid_query = False elif operator == 'in': query = column.in_(query_value.split(',')) query_clauses.append(query) elif operator == 'within': geo = json.loads(query_value) #print "make_query(): geo is", geo.items() if 'features' in geo.keys(): val = geo['features'][0]['geometry'] elif 'geometry' in geo.keys(): val = geo['geometry'] else: val = geo if val['type'] == 'LineString': shape = shapely.geometry.asShape(val) lat = shape.centroid.y # 100 meters by default x, y = get_size_in_degrees(100, lat) val = shape.buffer(y).__geo_interface__ val['crs'] = {"type": "name", "properties": {"name": "EPSG:4326"}} query = column.ST_Within(func.ST_GeomFromGeoJSON(json.dumps(val))) #print "make_query: val=", val #print "make_query(): query = ", query query_clauses.append(query) elif operator.startswith('time_of_day'): if operator.endswith('ge'): query = func.date_part('hour', column).__ge__(query_value) elif operator.endswith('le'): query = func.date_part('hour', column).__le__(query_value) query_clauses.append(query) else: try: attr = filter(lambda e: hasattr(column, e % operator), ['%s', '%s_', '__%s__'])[0] % operator except IndexError: resp['meta'][ 'message'] = '"%s" is not a valid query operator' % operator status_code = 400 valid_query = False break if query_value == 'null': # pragma: no cover query_value = None query = getattr(column, attr)(query_value) query_clauses.append(query) #print "make_query(): query_clauses=", query_clauses return valid_query, query_clauses, resp, status_code
def make_query(table, raw_query_params): table_keys = table.columns.keys() args_keys = raw_query_params.keys() resp = { 'meta': { 'status': 'error', 'message': '', }, 'objects': [], } status_code = 200 query_clauses = [] valid_query = True #print "make_query(): args_keys = ", args_keys if 'offset' in args_keys: args_keys.remove('offset') if 'limit' in args_keys: args_keys.remove('limit') if 'order_by' in args_keys: args_keys.remove('order_by') if 'weather' in args_keys: args_keys.remove('weather') for query_param in args_keys: try: field, operator = query_param.split('__') #print "make_query(): field, operator =", field, operator except ValueError: field = query_param operator = 'eq' query_value = raw_query_params.get(query_param) column = table.columns.get(field) if field not in table_keys: resp['meta']['message'] = '"%s" is not a valid fieldname' % field status_code = 400 valid_query = False elif operator == 'in': query = column.in_(query_value.split(',')) query_clauses.append(query) elif operator == 'within': geo = json.loads(query_value) #print "make_query(): geo is", geo.items() if 'features' in geo.keys(): val = geo['features'][0]['geometry'] elif 'geometry' in geo.keys(): val = geo['geometry'] else: val = geo if val['type'] == 'LineString': shape = shapely.geometry.asShape(val) lat = shape.centroid.y # 100 meters by default x, y = get_size_in_degrees(100, lat) val = shape.buffer(y).__geo_interface__ val['crs'] = {"type":"name","properties":{"name":"EPSG:4326"}} query = column.ST_Within(func.ST_GeomFromGeoJSON(json.dumps(val))) #print "make_query: val=", val #print "make_query(): query = ", query query_clauses.append(query) elif operator.startswith('time_of_day'): if operator.endswith('ge'): query = func.date_part('hour', column).__ge__(query_value) elif operator.endswith('le'): query = func.date_part('hour', column).__le__(query_value) query_clauses.append(query) else: try: attr = filter( lambda e: hasattr(column, e % operator), ['%s', '%s_', '__%s__'] )[0] % operator except IndexError: resp['meta']['message'] = '"%s" is not a valid query operator' % operator status_code = 400 valid_query = False break if query_value == 'null': # pragma: no cover query_value = None query = getattr(column, attr)(query_value) query_clauses.append(query) #print "make_query(): query_clauses=", query_clauses return valid_query, query_clauses, resp, status_code