def get_ranges(dc, product, path=None, is_dc_product=False):
cfg = product.global_cfg
conn = get_sqlconn(dc)
if not is_dc_product and product.multi_product:
if path is not None:
raise Exception(
"Combining subproducts and multiproducts is not yet supported")
results = conn.execute(
"""
SELECT *
FROM wms.multiproduct_ranges
WHERE wms_product_name=%s""", product.name)
else:
if is_dc_product:
prod_id = product.id
else:
prod_id = product.product.id
if path is not None:
results = conn.execute(
"""
SELECT *
FROM wms.sub_product_ranges
WHERE product_id=%s and sub_product_id=%s""", prod_id, path)
else:
results = conn.execute(
"""
SELECT *
FROM wms.product_ranges
WHERE id=%s""", prod_id)
for result in results:
conn.close()
times = [
datetime.strptime(d, "%Y-%m-%d").date() for d in result["dates"]
if d is not None
]
if not times:
return None
return {
"lat": {
"min": float(result["lat_min"]),
"max": float(result["lat_max"]),
},
"lon": {
"min": float(result["lon_min"]),
"max": float(result["lon_max"]),
},
"times": times,
"start_time": times[0],
"end_time": times[-1],
"time_set": set(times),
"bboxes": cfg.alias_bboxes(result["bboxes"])
}
return None
def datasets_exist(dc, product_name):
conn = get_sqlconn(dc)
results = conn.execute("""
SELECT COUNT(*)
FROM agdc.dataset ds, agdc.dataset_type p
WHERE ds.archived IS NULL
AND ds.dataset_type_ref = p.id
AND p.name = %s""",
product_name)
conn.close()
return list(results)[0][0] > 0
def create_multiprod_range_entry(dc, product, crses):
print("Merging multiproduct ranges for %s (ODC products: %s)" % (
product.name,
repr(product.product_names)
))
conn = get_sqlconn(dc)
prodids = [ p.id for p in product.products ]
wms_name = product.name
if all(
not datasets_exist(dc, p_name)
for p_name in product.product_names
):
print("Could not find datasets for any product in multiproduct: ", product.name)
conn.close()
print("Done")
return
txn = conn.begin()
# Attempt to insert row
conn.execute("""
INSERT INTO wms.multiproduct_ranges
(wms_product_name,lat_min,lat_max,lon_min,lon_max,dates,bboxes)
VALUES
(%(p_id)s, 0, 0, 0, 0, %(empty)s, %(empty)s)
ON CONFLICT (wms_product_name) DO NOTHING
""",
{"p_id": wms_name, "empty": Json("")})
# Update extents
conn.execute("""
UPDATE wms.multiproduct_ranges
SET lat_min = subq.lat_min,
lat_max = subq.lat_max,
lon_min = subq.lon_min,
lon_max = subq.lon_max
FROM (
select min(lat_min) as lat_min,
max(lat_max) as lat_max,
min(lon_min) as lon_min,
max(lon_max) as lon_max
from wms.product_ranges
where id = ANY (%(p_prodids)s)
) as subq
WHERE wms_product_name = %(p_id)s
""",
{"p_id": wms_name, "p_prodids": prodids})
# Create sorted list of dates
results = conn.execute(
"""
SELECT dates
FROM wms.product_ranges
WHERE id = ANY (%(p_prodids)s)
""", {"p_prodids": prodids}
)
dates = set()
for r in results:
for d in r[0]:
dates.add(d)
dates = sorted(dates)
conn.execute("""
UPDATE wms.multiproduct_ranges
SET dates = %(dates)s
WHERE wms_product_name= %(p_id)s
""",
{
"dates": Json(dates),
"p_id": wms_name
}
)
# calculate bounding boxes
results = list(conn.execute("""
SELECT lat_min,lat_max,lon_min,lon_max
FROM wms.multiproduct_ranges
WHERE wms_product_name=%(p_id)s
""",
{"p_id": wms_name} ))
r = results[0]
epsg4326 = datacube.utils.geometry.CRS("EPSG:4326")
box = datacube.utils.geometry.box(
float(r[2]),
float(r[0]),
float(r[3]),
float(r[1]),
epsg4326)
cfg = get_config()
conn.execute("""
UPDATE wms.multiproduct_ranges
SET bboxes = %s::jsonb
WHERE wms_product_name=%s
""",
Json({ crsid: jsonise_bbox(box.to_crs(crs).boundingbox) for crsid, crs in get_crses(cfg).items() }),
wms_name
)
txn.commit()
conn.close()
return
def create_range_entry(dc, product, crses, summary_product=False):
print("Updating range for ODC product %s..."% product.name)
# NB. product is an ODC product
conn = get_sqlconn(dc)
txn = conn.begin()
prodid = product.id
# insert empty row if one does not already exist
conn.execute("""
INSERT INTO wms.product_ranges
(id,lat_min,lat_max,lon_min,lon_max,dates,bboxes)
VALUES
(%(p_id)s, 0, 0, 0, 0, %(empty)s, %(empty)s)
ON CONFLICT (id) DO NOTHING
""",
{"p_id": prodid, "empty": Json("")})
# Update min/max lat/longs
conn.execute(
"""
UPDATE wms.product_ranges pr
SET lat_min = st_ymin(subq.bbox),
lat_max = st_ymax(subq.bbox),
lon_min = st_xmin(subq.bbox),
lon_max = st_xmax(subq.bbox)
FROM (
SELECT st_extent(stv.spatial_extent) as bbox
FROM public.space_time_view stv
WHERE stv.dataset_type_ref = %(p_id)s
) as subq
WHERE pr.id = %(p_id)s
""",
{"p_id": prodid})
# Set default timezone
conn.execute("""
set timezone to 'Etc/UTC'
""")
if summary_product:
# Loop over dates
dates = set()
results = conn.execute(
"""
select
array_agg(temporal_extent)
from public.space_time_view
WHERE dataset_type_ref = %(p_id)s
""",
{"p_id": prodid}
)
for result in results:
for dat_ran in result[0]:
dates.add(dat_ran.lower)
dates = sorted(dates)
conn.execute("""
UPDATE wms.product_ranges
SET dates = %(dates)s
WHERE id= %(p_id)s
""",
{
"dates": Json([t.strftime("%Y-%m-%d") for t in dates]),
"p_id": prodid
}
)
else:
# Loop over dates
dates = set()
results = conn.execute(
"""
select
lower(temporal_extent), upper(temporal_extent),
ST_X(ST_Centroid(spatial_extent)),
ST_Y(ST_Centroid(spatial_extent))
from public.space_time_view
WHERE dataset_type_ref = %(p_id)s
""" %
{"p_id": prodid}
)
for result in results:
dt1, dt2, lon, lat = result
try:
tz = tz_for_coord(lon, lat)
except NoTimezoneException:
offset = round(lon / 15.0)
tz = timezone(timedelta(hours=offset))
dates.add(dt1.astimezone(tz).date())
dates = sorted(dates)
conn.execute("""
UPDATE wms.product_ranges
SET dates = %(dates)s
WHERE id= %(p_id)s
""",
{
"dates": Json([t.strftime("%Y-%m-%d") for t in dates]),
"p_id": prodid
}
)
# calculate bounding boxes
results = list(conn.execute("""
SELECT lat_min,lat_max,lon_min,lon_max
FROM wms.product_ranges
WHERE id=%(p_id)s
""",
{"p_id": prodid}))
r = results[0]
epsg4326 = datacube.utils.geometry.CRS("EPSG:4326")
box = datacube.utils.geometry.box(
float(r[2]),
float(r[0]),
float(r[3]),
float(r[1]),
epsg4326)
conn.execute("""
UPDATE wms.product_ranges
SET bboxes = %(bbox)s::jsonb
WHERE id=%(p_id)s
""", {
"bbox": Json(
{crsid: {"top": box.to_crs(crs).boundingbox.top,
"bottom": box.to_crs(crs).boundingbox.bottom,
"left": box.to_crs(crs).boundingbox.left,
"right": box.to_crs(crs).boundingbox.right}
for crsid, crs in crses.items()
}
),
"p_id": product.id})
txn.commit()
conn.close()
