def fesomDataQuery(dbcon, fesominfo, tspan, interval=None):
"""Query a fesom run for datafiles"""
#retrieve/reflect the table
tbl=dbcon.getTable(fesominfo["run"]["runTable"],'fesom')
# qry=select([tbl]).where(or_(and_(tbl.c.tstart <= tspan[0],tspan[0] <= tbl.c.tend),
# and_(tbl.c.tstart <= tspan[1], tspan[1] <= tbl.c.tend)))
qry=select([tbl]).where(func.overlaps(tbl.c.tstart,tbl.c.tend,tspan[0],tspan[1]))
if interval:
qry=qry.where(tbl.c.interval == interval)
return dbcon.dbeng.execute(qry)
def awipiesQuery(dbcon, tspan=None, geoWKT=False):
tbl = dbcon.getTable('awipies', 'oceanobs')
#select time
subqry = select([tbl])
if tspan:
subqry = subqry.where(
func.overlaps(tbl.c.tstart, tbl.c.tend, tspan[0], tspan[1]))
subqry = subqry.alias("ar")
#expand the arrays and points int he subquery
qry = select([
subqry.c.id, subqry.c.name, subqry.c.uri, subqry.c.depth,
literal_column('geom::geometry').label('geom')
])
# ST_Dump(literal_column("ar.geom::geometry")).geom.label('geom')])
#additional spatial constraints
finalqry = qry
qry = qry.alias("arex")
return dbcon.dbeng.execute(finalqry)
def joinByPeriod(left,right):
"""Convenience function to make an inner table join based upon similar start times"""
dttol=timedelta(days=3)
return join(left,right,and_(func.overlaps(left.c.tstart-dttol,left.c.tstart+dttol,right.c.tstart-dttol,right.c.tstart+dttol)))
def argoQuery(dbcon, geoWKT=None, tspan=None, withinDmeter=None, tsort=None):
tbl = dbcon.getTable('argo2', 'oceanobs')
#first create a subquery to quickly discard argo profiles
subqry = select([tbl])
if tspan:
subqry = subqry.where(
func.overlaps(tbl.c.tstart, tbl.c.tend, tspan[0], tspan[1]))
# Apply initial geospatial constraints
if geoWKT:
if withinDmeter:
#only base initial constraints ont he bounding box
subqry = subqry.where(
func.ST_DWithin(
literal_column('ST_Envelope(geom::geometry)::geography'),
func.ST_GeogFromText(geoWKT), withinDmeter))
else:
subqry = subqry.where(
func.ST_Intersects(literal_column('geom::geometry'),
func.ST_GeomFromText(geoWKT, 4326)))
#we need to assign an alias to this subquery in order to work with it
subqry = subqry.alias("ar")
#expand the arrays and points int he subquery
qry = select([
subqry.c.wmoid, subqry.c.uri, subqry.c.datacenter,
func.unnest(subqry.c.mode).label('mode'),
func.unnest(subqry.c.ascend).label('ascend'),
func.unnest(subqry.c.tlocation).label('tlocation'),
func.unnest(subqry.c.cycle).label('cycle'),
func.unnest(subqry.c.iprof).label('iprof'),
ST_Dump(literal_column("ar.geom::geometry")).geom.label('geom')
])
#additional spatial constraints
finalqry = qry
qry = qry.alias("arex")
if tspan:
finalqry = select([qry]).where(
between(qry.c.tlocation, tspan[0], tspan[1]))
if geoWKT:
if withinDmeter:
#only base initial constraints ont he bounding box
finalqry = finalqry.where(
func.ST_DWithin(qry.c.geom, func.ST_GeogFromText(geoWKT),
withinDmeter))
else:
finalqry = finalqry.where(
func.ST_Within(literal_column("arex.geom"),
func.ST_GeomFromText(geoWKT, 4326)))
if tsort:
finalqry = finalqry.order_by(qry.c.tlocation)
return dbcon.dbeng.execute(finalqry)