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