def datasets_by_region(engine, index, product_name, region_code, time_range, limit):
product = index.products.get_by_name(product_name)
query = (
select(postgres_api._DATASET_SELECT_FIELDS)
.select_from(
DATASET_SPATIAL.join(DATASET, DATASET_SPATIAL.c.id == DATASET.c.id)
)
.where(DATASET_SPATIAL.c.region_code == bindparam("region_code", region_code))
.where(
DATASET_SPATIAL.c.dataset_type_ref
== bindparam("dataset_type_ref", product.id)
)
)
if time_range:
query = query.where(
DATASET_SPATIAL.c.center_time > bindparam("from_time", time_range.begin)
).where(DATASET_SPATIAL.c.center_time < bindparam("to_time", time_range.end))
query = query.order_by(DATASET_SPATIAL.c.center_time).limit(
bindparam("limit", limit)
)
return (
index.datasets._make(res, full_info=True)
for res in engine.execute(query).fetchall()
)
def search_items(
self,
*,
product_name: Optional[str] = None,
time: Optional[Tuple[datetime, datetime]] = None,
bbox: Tuple[float, float, float, float] = None,
limit: int = 500,
offset: int = 0,
full_dataset: bool = False,
dataset_ids: Sequence[UUID] = None,
require_geometry=True,
ordered=True,
) -> Generator[DatasetItem, None, None]:
"""
Search datasets using Cubedash's spatial table
Returned as DatasetItem records, with optional embedded full Datasets
(if full_dataset==True)
Returned results are always sorted by (center_time, id)
"""
geom = func.ST_Transform(DATASET_SPATIAL.c.footprint, 4326)
columns = [
geom.label("geometry"),
func.Box2D(geom).label("bbox"),
# TODO: dataset label?
DATASET_SPATIAL.c.region_code.label("region_code"),
DATASET_SPATIAL.c.creation_time,
DATASET_SPATIAL.c.center_time,
]
# If fetching the whole dataset, we need to join the ODC dataset table.
if full_dataset:
query: Select = select(
(*columns, *_utils.DATASET_SELECT_FIELDS)).select_from(
DATASET_SPATIAL.join(
ODC_DATASET,
onclause=ODC_DATASET.c.id == DATASET_SPATIAL.c.id))
# Otherwise query purely from the spatial table.
else:
query: Select = select((*columns, DATASET_SPATIAL.c.id,
DATASET_SPATIAL.c.dataset_type_ref
)).select_from(DATASET_SPATIAL)
if time:
query = query.where(
func.tstzrange(
_utils.default_utc(time[0]),
_utils.default_utc(time[1]),
"[]",
type_=TSTZRANGE,
).contains(DATASET_SPATIAL.c.center_time))
if bbox:
query = query.where(
func.ST_Transform(DATASET_SPATIAL.c.footprint,
4326).intersects(
func.ST_MakeEnvelope(*bbox)))
if product_name:
query = query.where(DATASET_SPATIAL.c.dataset_type_ref == select(
[ODC_DATASET_TYPE.c.id]).where(
ODC_DATASET_TYPE.c.name == product_name))
if dataset_ids:
query = query.where(DATASET_SPATIAL.c.id.in_(dataset_ids))
if require_geometry:
query = query.where(DATASET_SPATIAL.c.footprint != None)
if ordered:
query = query.order_by(DATASET_SPATIAL.c.center_time,
DATASET_SPATIAL.c.id)
query = query.limit(limit).offset(
# TODO: Offset/limit isn't particularly efficient for paging...
offset)
for r in self._engine.execute(query):
yield DatasetItem(
dataset_id=r.id,
bbox=_box2d_to_bbox(r.bbox) if r.bbox else None,
product_name=self.index.products.get(r.dataset_type_ref).name,
geometry=_get_shape(r.geometry),
region_code=r.region_code,
creation_time=r.creation_time,
center_time=r.center_time,
odc_dataset=(_utils.make_dataset_from_select_fields(
self.index, r) if full_dataset else None),
)