def _within_distance(compiler, geom1, geom2, distance, *args): """MySQL does not support the function distance, so we are doing a kind of "mbr_within_distance". The MBR of 'geom2' is expanded with the amount of 'distance' by manually changing the coordinates. Then we test if 'geom1' intersects this expanded MBR. """ mbr = func.ExteriorRing(func.Envelope(geom2)) lower_left = func.StartPoint(mbr) upper_right = func.PointN(mbr, 3) xmin = func.X(lower_left) ymin = func.Y(lower_left) xmax = func.X(upper_right) ymax = func.Y(upper_right) return func.Intersects( geom1, func.GeomFromText( func.Concat('Polygon((', xmin - distance, ' ', ymin - distance, ',', xmax + distance, ' ', ymin - distance, ',', xmax + distance, ' ', ymax + distance, ',', xmin - distance, ' ', ymax + distance, ',', xmin - distance, ' ', ymin - distance, '))'), func.srid(geom2)))
def get_product_availability(self, product_id, date_start, date_stop, bbox, pattern): """ """ """ sqlQuery = "SELECT COUNT(*) AS nbDatasets, DATE_FORMAT(begin_datetime, '%Y/%m/%d') AS begin_datetime2, " \ "DATE_FORMAT(end_datetime, '%Y/%m/%d') AS end_datetime2 FROM `product_" + productId.replace(' ', '_') + "` " \ "GROUP BY begin_datetime2, end_datetime2" """ insp = reflection.Inspector.from_engine(self.__engine) existing_tables = insp.get_table_names() table_name = Storage.get_product_table_name(product_id) if table_name not in existing_tables: return [] dataset_cls = self.get_product_table(product_id) nb_datasets = func.count(dataset_cls.dataset_name).label('nb_datasets') dataset_begin = sqlalchemy.func.date(dataset_cls.begin_datetime) dataset_end = sqlalchemy.func.date(dataset_cls.end_datetime) q = self.__session.query(nb_datasets, dataset_begin, dataset_end) if bbox is not None: q = q.filter( sqlalchemy.or_( dataset_cls.shape_text == 'POINT(0 0)', func.Intersects(dataset_cls.shape_geometry, func.GeomFromText(bbox)))) if pattern is not None: q = q.filter(dataset_cls.dataset_name.like('%{}%'.format(pattern))) q = q.group_by(dataset_begin, dataset_end) return q.all()
def get_datasets(self, data_url, data_path, start, stop, bbox_text, requested_products, patterns, results_limit): """ """ insp = reflection.Inspector.from_engine(self.__engine) existing_tables = insp.get_table_names() products = [ p for p in requested_products if Storage.get_product_table_name(p) in existing_tables ] # Spatial filter bbox_polygon = None if bbox_text is not None: west, south, east, north = bbox_text.split(',') bbox_polygon = 'POLYGON(({} {}, {} {}, {} {}, {} {}, {} {}))' bbox_polygon = bbox_polygon.format(west, north, east, north, east, south, west, south, west, north) result = [] bucket_size = results_limit for product_id in products: if 0 >= bucket_size: break product_type = self.__session.query(Product.type).filter( Product.product_id == product_id).first()[0] pattern = None if patterns is not None: pattern = patterns.get(product_id, None) dataset_cls = self.get_product_table(product_id) q = self.__session.query(dataset_cls) q = q.filter(dataset_cls.begin_datetime < stop) q = q.filter(dataset_cls.end_datetime > start) if bbox_polygon is not None: q = q.filter( sqlalchemy.or_( dataset_cls.shape_text == 'POINT(0 0)', func.Intersects(dataset_cls.shape_geometry, func.GeomFromText(bbox_polygon)))) if pattern is not None: q = q.filter( dataset_cls.dataset_name.like('%{}%'.format(pattern))) q = q.order_by(dataset_cls.begin_datetime) q = q.limit(bucket_size) product_datasets = map( lambda x: Storage.format_dataset(product_id, product_type, data_url, data_path, x), q.all()) bucket_size = bucket_size - len(product_datasets) result.extend(product_datasets) return result
def search_datasets(self, start, stop, bbox_text, requested_products, patterns): """ """ insp = reflection.Inspector.from_engine(self.__engine) existing_tables = insp.get_table_names() products = [ p for p in requested_products if Storage.get_product_table_name(p) in existing_tables ] # Spatial filter bbox_polygon = None if bbox_text is not None: west, south, east, north = bbox_text.split(',') bbox_polygon = 'POLYGON(({} {}, {} {}, {} {}, {} {}, {} {}))' bbox_polygon = bbox_polygon.format(west, north, east, north, east, south, west, south, west, north) results = {} for product_id in products: pattern = None if patterns is not None: pattern = patterns.get(product_id, None) dataset_cls = self.get_product_table(product_id) q = self.__session.query(dataset_cls.dataset_name) q = q.filter(dataset_cls.begin_datetime < stop) q = q.filter(dataset_cls.end_datetime > start) if bbox_polygon is not None: q = q.filter( sqlalchemy.or_( dataset_cls.shape_text == 'POINT(0 0)', func.Intersects(dataset_cls.shape_geometry, func.GeomFromText(bbox_polygon)))) if pattern is not None: q = q.filter( dataset_cls.dataset_name.like('%{}%'.format(pattern))) q = q.order_by(dataset_cls.begin_datetime) results[product_id] = map(lambda x: x[0], q.all()) return results
def bind_expression(self, bindvalue): value = func.GeomFromText(bindvalue, type_=self) return value
def select(self, action): model = __import__(self.model, fromlist=['*']) cls = getattr(model, self.cls) geom_cls = None if self.geom_cls: geom_cls = getattr(model, self.geom_cls) if action.id is not None: result = [self.session.query(cls).get(action.id)] else: if self.geom_rel and self.geom_cls: main_table = cls.__tablename__ geom_table = geom_cls.__tablename__ join_condition = self.join_condition or "%s.%s_id=%s.id" % ( main_table, geom_table, geom_table) query = self.session.query(cls, geom_cls).filter(join_condition) else: query = self.session.query(cls) if action.attributes: query = query.filter( and_(*[ self.feature_predicate(v['column'], v['type'], v['value']) for k, v in action.attributes.iteritems() ])) if action.bbox: if self.geom_rel and self.geom_cls: geom_element = getattr(geom_cls, self.geom_col) else: geom_element = getattr(cls, self.geom_col) query = query.filter( geom_element.intersects( self.session.scalar( func.GeomFromText(self.bbox2wkt(action.bbox), self.srid)))) if self.order: query = query.order_by(getattr(cls, self.order)) if action.maxfeatures: query.limit(action.maxfeatures) else: query.limit(1000) if action.startfeature: query.offset(action.startfeature) result = query.all() features = [] for row_tuple in result: props = {} id = None geom = None if not isinstance(row_tuple, (tuple, list, dict, set)): row_tuple = (row_tuple, ) for row in row_tuple: if isinstance(row, cls): cols = cls.__table__.c.keys() for col in cols: if col == self.fid_col: id = getattr(row, col) elif col == self.geom_col: geom = WKT.from_wkt( self.session.scalar(getattr(row, col).wkt)) else: if self.attribute_cols == '*' or col in self.attribute_cols: props[col] = getattr(row, col) elif isinstance(row, geom_cls) and geom_cls: cols = geom_cls.__table__.c.keys() for col in cols: if col == self.fid_col: pass elif col == self.geom_col: geom = WKT.from_wkt( self.session.scalar(getattr(row, col).wkt)) else: if self.attribute_cols == '*' or col in self.attribute_cols: props[col] = getattr(row, col) else: continue for key, value in props.items(): if isinstance(value, str): props[key] = unicode(value, self.encoding) elif isinstance(value, datetime.datetime) or isinstance( value, datetime.date): # stringify datetimes props[key] = str(value) try: if isinstance(value, decimal.Decimal): props[key] = unicode(str(value), self.encoding) except: pass if (geom): features.append( Feature(id=id, geometry=geom, geometry_attr=self.geom_col, srs=self.srid_out, props=props)) return features
def bind_expression(self, bindvalue): return func.GeomFromText(bindvalue)
def find_nearest(self, direction, current, bbox_text, requested_products, patterns, search_step=60): """ """ insp = reflection.Inspector.from_engine(self.__engine) existing_tables = insp.get_table_names() products = [ p for p in requested_products if Storage.get_product_table_name(p) in existing_tables ] # Spatial filter bbox_polygon = None if bbox_text is not None: west, south, east, north = bbox_text.split(',') bbox_polygon = 'POLYGON(({} {}, {} {}, {} {}, {} {}, {} {}))' bbox_polygon = bbox_polygon.format(west, north, east, north, east, south, west, south, west, north) after_delta = float('inf') before_delta = float('inf') for product_id in products: pattern = None if patterns is not None: pattern = patterns.get(product_id, None) dataset_cls = self.get_product_table(product_id) mid_ts = ((func.unix_timestamp(dataset_cls.begin_datetime) + func.unix_timestamp(dataset_cls.end_datetime)) / 2).label('mid_ts') current_ts = calendar.timegm(current.timetuple()) # Look for datasets in the future if direction in ['nearest', 'after', None]: q = self.__session.query(mid_ts) q = q.filter(mid_ts > current_ts + search_step) if bbox_polygon is not None: q = q.filter( sqlalchemy.or_( dataset_cls.shape_text == 'POINT(0 0)', func.Intersects(dataset_cls.shape_geometry, func.GeomFromText(bbox_polygon)))) if pattern is not None: q = q.filter( dataset_cls.dataset_name.like('%{}%'.format(pattern))) q = q.order_by(mid_ts) q = q.limit(1) result = q.first() if result is not None: delta = int(result[0]) - current_ts if delta < after_delta: after_delta = delta # Look for datasets in the past if direction in ['nearest', 'before', None]: q = self.__session.query(mid_ts) q = q.filter(mid_ts < current_ts - search_step) if bbox_polygon is not None: q = q.filter( sqlalchemy.or_( dataset_cls.shape_text == 'POINT(0 0)', func.Intersects(dataset_cls.shape_geometry, func.GeomFromText(bbox_polygon)))) if pattern is not None: q = q.filter( dataset_cls.dataset_name.like('%{}%'.format(pattern))) q = q.order_by(desc(mid_ts)) q = q.limit(1) result = q.first() if result is not None: delta = current_ts - int(result[0]) if delta < before_delta: before_delta = delta delta = min(after_delta, before_delta) if delta == float('inf'): new_ts = current_ts elif delta == after_delta: new_ts = current_ts + delta else: new_ts = current_ts - delta if before_delta == float('inf'): before_delta = -1 if after_delta == float('inf'): after_delta = -1 return new_ts, before_delta, after_delta