def return_gridmeta(ids): if ids: ids_str = '(' + ','.join(map(str, ids)) + ')' query = "select uid, filename, filesize, filetype, meta_data, date_created, date_inserted from grid_meta where uid in %s" % ids_str else: query = "select uid, filename, filesize, filetype, meta_data, date_created, date_inserted from grid_meta" rows = db_session.execute(query) # The response JSON out = {} out['request'] = {} out['request']['datetime'] = time.strftime('%Y-%m-%d %H:%M:%S') out['response'] = {} out['response']['datetime'] = time.strftime('%Y-%m-%d %H:%M:%S') out['response']['status'] = 'OK' out['response']['status_code'] = 200 out['response']['data'] = [] for row in rows: new_doc = {} new_doc['uid'] = row[0] new_doc['filename'] = row[1] new_doc['filesize'] = row[2] new_doc['filetype'] = row[3] new_doc['meta_data'] = row[4] new_doc['date_created'] = datetime.strftime(row[5], "%Y-%m-%d %H:%M:%S") new_doc['date_inserted'] = datetime.strftime(row[6], "%Y-%m-%d %H:%M:%S") out['response']['data'].append(new_doc) return out
def return_polydata(ids): if ids: ids_str = '(' + ','.join(map(str, ids)) + ')' query = "select uid, ST_AsGeoJSON(geom) from regions where uid in %s" % ids_str else: query = "select uid, ST_AsGeoJSON(geom) from regions" rows = db_session.execute(query) # The response JSON out = {} out['request'] = {} out['request']['datetime'] = time.strftime('%Y-%m-%d %H:%M:%S') out['response'] = {} out['response']['datetime'] = time.strftime('%Y-%m-%d %H:%M:%S') out['response']['status'] = 'OK' out['response']['status_code'] = 200 out['response']['data'] = [] for row in rows: new_doc = {} new_doc['uid'] = row[0] new_doc['geo'] = row[1] out['response']['data'].append(new_doc) return out
def return_griddata_aggregate_temporal_by_id(meta_id, var_id, poly, dates): # poly + dates specified if poly and dates: date_str = '(' + ','.join(map(str, dates)) + ')' tmp = "with foo as (select array(select ROW(ST_Union(ST_Clip(gd.rast, r.geom)), 1)::rastbandarg as rast " \ "from grid_data as gd, regions as r where " \ "gd.meta_id=%s and gd.var_id=%s and r.uid=%s and gd.date in %s group by date))" %\ (meta_id, var_id, poly, date_str) query = tmp + '\n' + "SELECT ST_X(geom), ST_Y(geom), val FROM " \ "(select (ST_PixelAsCentroids(ST_MapAlgebra((select * from foo)::rastbandarg[], " \ "'st_stddev4ma(double precision[], int[], text[])'::regprocedure))).*) foo;" # only poly specified elif poly: tmp = "with foo as (select array(select ROW(ST_Union(ST_Clip(gd.rast, r.geom)), 1)::rastbandarg as rast " \ "from grid_data as gd, regions as r where " \ "gd.meta_id=%s and gd.var_id=%s and r.uid=%s group by date))" %\ (meta_id, var_id, poly) query = tmp + '\n' + "SELECT ST_X(geom), ST_Y(geom), val FROM " \ "(select (ST_PixelAsCentroids(ST_MapAlgebra((select * from foo)::rastbandarg[], " \ "'st_stddev4ma(double precision[], int[], text[])'::regprocedure))).*) foo;" # only dates specified elif dates: poly = [[-180, -90], [180, -90], [180, 90], [-180, 90], [-180, -90]] poly_str = "ST_Polygon(ST_GeomFromText('LINESTRING(%s %s, %s %s, %s %s, %s %s, %s %s)'), 4326)" %\ (poly[0][0], poly[0][1], poly[1][0], poly[1][1], poly[2][0], poly[2][1], poly[3][0], poly[3][1], poly[4][0], poly[4][1]) date_str = '(' + ','.join(map(str, dates)) + ')' tmp = "with foo as (select array(select ROW(ST_Union(ST_Clip(rast, %s)), 1)::rastbandarg as rast from grid_data " \ "where meta_id=%s and var_id=%s and date in %s group by date))" %\ (poly_str, meta_id, var_id, date_str) query = tmp + '\n' + "SELECT ST_X(geom), ST_Y(geom), val FROM " \ "(select (ST_PixelAsCentroids(ST_MapAlgebra((select * from foo)::rastbandarg[], " \ "'st_stddev4ma(double precision[], int[], text[])'::regprocedure))).*) foo;" # neither poly nor dates specified else: poly = [[-180, -90], [180, -90], [180, 90], [-180, 90], [-180, -90]] poly_str = "ST_Polygon(ST_GeomFromText('LINESTRING(%s %s, %s %s, %s %s, %s %s, %s %s)'), 4326)" %\ (poly[0][0], poly[0][1], poly[1][0], poly[1][1], poly[2][0], poly[2][1], poly[3][0], poly[3][1], poly[4][0], poly[4][1]) tmp = "with foo as (select array(select ROW(ST_Union(ST_Clip(rast, %s)), 1)::rastbandarg as rast from grid_data " \ "where meta_id=%s and var_id=%s group by date))" %\ (poly_str, meta_id, var_id) query = tmp + '\n' + "SELECT ST_X(geom), ST_Y(geom), val FROM " \ "(select (ST_PixelAsCentroids(ST_MapAlgebra((select * from foo)::rastbandarg[], " \ "'st_stddev4ma(double precision[], int[], text[])'::regprocedure))).*) foo;" rows = db_session.execute(query) # the response JSON out = {} out['request'] = {} out['request']['datetime'] = time.strftime('%Y-%m-%d %H:%M:%S') out['response'] = {} out['response']['datetime'] = time.strftime('%Y-%m-%d %H:%M:%S') out['response']['status'] = 'OK' out['response']['status_code'] = 200 out['response']['data'] = [] for row in rows: lon = row[0] lat = row[1] val = row[2] new_data_item = {} new_data_item['type'] = 'Feature' new_data_item['geometry'] = {'type': 'Point', 'coordinates': [lon, lat]} new_data_item['properties'] = {'values': [val]} out['response']['data'].append(new_data_item) if dates: query = "select to_char(date, \'YYYY-MM-DD HH24:MI:SS\') from grid_dates where uid in %s" % date_str else: query = "select to_char(date, \'YYYY-MM-DD HH24:MI:SS\') from grid_dates" rows = db_session.execute(query) out['response']['metadata'] = {} out['response']['metadata']['dates'] = [] for row in rows: date_str = str(row[0]) out['response']['metadata']['dates'].append(date_str) out['response']['metadata']['region'] = poly query = "select vname from grid_vars where uid=%s" % var_id rows = db_session.execute(query) for row in rows: vname = row[0] query = "select meta_data from grid_meta where uid=%s" % meta_id rows = db_session.execute(query) for row in rows: meta_data = row[0] for var in meta_data['variables']: if var['name'] == vname: for attr in var['attributes']: if attr['name'] == 'units': units = attr['value'] out['response']['metadata']['units'] = units out['response']['metadata']['format'] = 'grid' return out
def return_griddata(meta_id, var_id, poly, date): # poly + date specified if poly and date: poly_str = "ST_Polygon(ST_GeomFromText('LINESTRING(%s %s, %s %s, %s %s, %s %s, %s %s)'), 4326)" %\ (poly[0][0], poly[0][1], poly[1][0], poly[1][1], poly[2][0], poly[2][1], poly[3][0], poly[3][1], poly[4][0], poly[4][1]) query = "SELECT ST_X(geom), ST_Y(geom), val " \ "from (select (ST_PixelAsCentroids(ST_Clip(rast, %s, TRUE))).* from " \ "grid_data where meta_id=%s and var_id=%s and date=%s) foo;" %\ (poly_str, meta_id, var_id, date) # only poly specified elif poly: poly_str = "ST_Polygon(ST_GeomFromText('LINESTRING(%s %s, %s %s, %s %s, %s %s, %s %s)'), 4326)" %\ (poly[0][0], poly[0][1], poly[1][0], poly[1][1], poly[2][0], poly[2][1], poly[3][0], poly[3][1], poly[4][0], poly[4][1]) query = "SELECT ST_X(geom), ST_Y(geom), val " \ "from (select (ST_PixelAsCentroids(ST_Clip(rast, %s, TRUE))).* from " \ "grid_data where meta_id=%s and var_id=%s) foo;" %\ (poly_str, meta_id, var_id) # only date specified elif date: poly = [[-180, -90], [180, -90], [180, 90], [-180, 90], [-180, -90]] poly_str = "ST_Polygon(ST_GeomFromText('LINESTRING(%s %s, %s %s, %s %s, %s %s, %s %s)'), 4326)" %\ (poly[0][0], poly[0][1], poly[1][0], poly[1][1], poly[2][0], poly[2][1], poly[3][0], poly[3][1], poly[4][0], poly[4][1]) query = "SELECT ST_X(geom), ST_Y(geom), val " \ "from (select (ST_PixelAsCentroids(ST_Clip(rast, %s, TRUE))).* from " \ "grid_data where meta_id=%s and var_id=%s and date=%s) foo;" %\ (poly_str, meta_id, var_id, date) # neither poly nor date specified else: poly = [[-180, -90], [180, -90], [180, 90], [-180, 90], [-180, -90]] poly_str = "ST_Polygon(ST_GeomFromText('LINESTRING(%s %s, %s %s, %s %s, %s %s, %s %s)'), 4326)" %\ (poly[0][0], poly[0][1], poly[1][0], poly[1][1], poly[2][0], poly[2][1], poly[3][0], poly[3][1], poly[4][0], poly[4][1]) query = "SELECT ST_X(geom), ST_Y(geom), val " \ "from (select (ST_PixelAsCentroids(ST_Clip(rast, %s, TRUE))).* from " \ "grid_data where meta_id=%s and var_id=%s) foo;" %\ (poly_str, meta_id, var_id) print query rows = db_session.execute(query) # the response JSON out = {} out['request'] = {} out['request']['datetime'] = time.strftime('%Y-%m-%d %H:%M:%S') out['response'] = {} out['response']['datetime'] = time.strftime('%Y-%m-%d %H:%M:%S') out['response']['status'] = 'OK' out['response']['status_code'] = 200 out['response']['data'] = [] for row in rows: lon = row[0] # longitude lat = row[1] # latitude vals = row[2] # array of values, including NULL new_data_item = {} new_data_item['type'] = 'Feature' new_data_item['geometry'] = { 'type': 'Point', 'coordinates': [lon, lat] } new_data_item['properties'] = { 'values': vals } out['response']['data'].append(new_data_item) if date: query = "select to_char(date, \'YYYY-MM-DD HH24:MI:SS\') from grid_dates where uid=%s" % (date) else: query = "select to_char(date, \'YYYY-MM-DD HH24:MI:SS\') from grid_dates" rows = db_session.execute(query) out['response']['metadata'] = {} out['response']['metadata']['dates'] = [] for row in rows: date_str = str(row[0]) out['response']['metadata']['dates'].append(date_str) out['response']['metadata']['region'] = poly query = "select vname from grid_vars where uid=%s" % var_id rows = db_session.execute(query) for row in rows: vname = row[0] query = "select meta_data from grid_meta where uid=%s" % meta_id rows = db_session.execute(query) for row in rows: meta_data = row[0] for var in meta_data['variables']: if var['name'] == vname: for attr in var['attributes']: if attr['name'] == 'units': units = attr['value'] out['response']['metadata']['units'] = units out['response']['metadata']['format'] = 'grid' return out
def return_griddata_aggregate_spatial_by_id(meta_id, var_id, poly, date): # poly + date specified if poly and date: query = "select ST_SummaryStats(ST_Union(ST_Clip(rast, r.geom, true))) from grid_data as gd, regions as r " \ "where gd.meta_id=%s and gd.var_id=%s and r.uid=%s and gd.date=%s;" %\ (meta_id, var_id, poly, date) # only poly specified elif poly: query = "select ST_SummaryStats(ST_Union(ST_Clip(rast, r.geom, true))) from grid_data as gd, regions as r " \ "where gd.meta_id=%s and gd.var_id=%s and r.uid=%s;" %\ (meta_id, var_id, poly) # only date specified elif date: poly = [[-180, -90], [180, -90], [180, 90], [-180, 90], [-180, -90]] poly_str = "ST_Polygon(ST_GeomFromText('LINESTRING(%s %s, %s %s, %s %s, %s %s, %s %s)'), 4326)" %\ (poly[0][0], poly[0][1], poly[1][0], poly[1][1], poly[2][0], poly[2][1], poly[3][0], poly[3][1], poly[4][0], poly[4][1]) query = "select ST_SummaryStats(ST_Union(ST_Clip(rast, %s, true))) from grid_data " \ "where meta_id=%s and var_id=%s and date=%s;" %\ (poly_str, meta_id, var_id, date) # neither poly nor date specified else: poly = [[-180, -90], [180, -90], [180, 90], [-180, 90], [-180, -90]] poly_str = "ST_Polygon(ST_GeomFromText('LINESTRING(%s %s, %s %s, %s %s, %s %s, %s %s)'), 4326)" %\ (poly[0][0], poly[0][1], poly[1][0], poly[1][1], poly[2][0], poly[2][1], poly[3][0], poly[3][1], poly[4][0], poly[4][1]) query = "select ST_SummaryStats(ST_Union(ST_Clip(rast, %s, true))) from grid_data " \ "where meta_id=%s and var_id=%s;" %\ (poly_str, meta_id, var_id) #print query rows = db_session.execute(query) for row in rows: res = row[0].lstrip('(').rstrip(')').split(',') count = int(res[0]) sum = float(res[1]) mean = float(res[2]) stddev = float(res[3]) min = float(res[4]) max = float(res[5]) # the response JSON out = {} out['request'] = {} out['request']['datetime'] = time.strftime('%Y-%m-%d %H:%M:%S') out['response'] = {} out['response']['datetime'] = time.strftime('%Y-%m-%d %H:%M:%S') out['response']['status'] = 'OK' out['response']['status_code'] = 200 out['response']['data'] = [] new_data_item = {} new_data_item['type'] = 'Feature' new_data_item['geometry'] = {'type': 'Polygon', 'coordinates': poly} new_data_item['properties'] = {'count': count, 'sum': sum, 'mean': mean, 'stddev': stddev, 'min':min, 'max': max} out['response']['data'].append(new_data_item) if date: query = "select to_char(date, \'YYYY-MM-DD HH24:MI:SS\') from grid_dates where uid=%s" % (date) else: query = "select to_char(date, \'YYYY-MM-DD HH24:MI:SS\') from grid_dates" rows = db_session.execute(query) out['response']['metadata'] = {} out['response']['metadata']['dates'] = [] for row in rows: date_str = str(row[0]) out['response']['metadata']['dates'].append(date_str) out['response']['metadata']['region'] = poly query = "select vname from grid_vars where uid=%s" % var_id rows = db_session.execute(query) for row in rows: vname = row[0] query = "select meta_data from grid_meta where uid=%s" % meta_id rows = db_session.execute(query) for row in rows: meta_data = row[0] for var in meta_data['variables']: if var['name'] == vname: for attr in var['attributes']: if attr['name'] == 'units': units = attr['value'] out['response']['metadata']['units'] = units out['response']['metadata']['format'] = 'polygon' return out
def return_griddata_aggregate_temporal_by_id(meta_id, var_id, poly, dates): # poly + dates specified if poly and dates: date_str = '(' + ','.join(map(str, dates)) + ')' tmp = "with foo as (select array(select ROW(ST_Union(ST_Clip(gd.rast, r.geom)), 1)::rastbandarg as rast " \ "from grid_data as gd, regions as r where " \ "gd.meta_id=%s and gd.var_id=%s and r.uid=%s and gd.date in %s group by date))" %\ (meta_id, var_id, poly, date_str) query = tmp + '\n' + "SELECT ST_X(geom), ST_Y(geom), val FROM " \ "(select (ST_PixelAsCentroids(ST_MapAlgebra((select * from foo)::rastbandarg[], " \ "'st_stddev4ma(double precision[], int[], text[])'::regprocedure))).*) foo;" # only poly specified elif poly: tmp = "with foo as (select array(select ROW(ST_Union(ST_Clip(gd.rast, r.geom)), 1)::rastbandarg as rast " \ "from grid_data as gd, regions as r where " \ "gd.meta_id=%s and gd.var_id=%s and r.uid=%s group by date))" %\ (meta_id, var_id, poly) query = tmp + '\n' + "SELECT ST_X(geom), ST_Y(geom), val FROM " \ "(select (ST_PixelAsCentroids(ST_MapAlgebra((select * from foo)::rastbandarg[], " \ "'st_stddev4ma(double precision[], int[], text[])'::regprocedure))).*) foo;" # only dates specified elif dates: poly = [[-180, -90], [180, -90], [180, 90], [-180, 90], [-180, -90]] poly_str = "ST_Polygon(ST_GeomFromText('LINESTRING(%s %s, %s %s, %s %s, %s %s, %s %s)'), 4326)" %\ (poly[0][0], poly[0][1], poly[1][0], poly[1][1], poly[2][0], poly[2][1], poly[3][0], poly[3][1], poly[4][0], poly[4][1]) date_str = '(' + ','.join(map(str, dates)) + ')' tmp = "with foo as (select array(select ROW(ST_Union(ST_Clip(rast, %s)), 1)::rastbandarg as rast from grid_data " \ "where meta_id=%s and var_id=%s and date in %s group by date))" %\ (poly_str, meta_id, var_id, date_str) query = tmp + '\n' + "SELECT ST_X(geom), ST_Y(geom), val FROM " \ "(select (ST_PixelAsCentroids(ST_MapAlgebra((select * from foo)::rastbandarg[], " \ "'st_stddev4ma(double precision[], int[], text[])'::regprocedure))).*) foo;" # neither poly nor dates specified else: poly = [[-180, -90], [180, -90], [180, 90], [-180, 90], [-180, -90]] poly_str = "ST_Polygon(ST_GeomFromText('LINESTRING(%s %s, %s %s, %s %s, %s %s, %s %s)'), 4326)" %\ (poly[0][0], poly[0][1], poly[1][0], poly[1][1], poly[2][0], poly[2][1], poly[3][0], poly[3][1], poly[4][0], poly[4][1]) tmp = "with foo as (select array(select ROW(ST_Union(ST_Clip(rast, %s)), 1)::rastbandarg as rast from grid_data " \ "where meta_id=%s and var_id=%s group by date))" %\ (poly_str, meta_id, var_id) query = tmp + '\n' + "SELECT ST_X(geom), ST_Y(geom), val FROM " \ "(select (ST_PixelAsCentroids(ST_MapAlgebra((select * from foo)::rastbandarg[], " \ "'st_stddev4ma(double precision[], int[], text[])'::regprocedure))).*) foo;" rows = db_session.execute(query) # the response JSON out = {} out['request'] = {} out['request']['datetime'] = time.strftime('%Y-%m-%d %H:%M:%S') out['response'] = {} out['response']['datetime'] = time.strftime('%Y-%m-%d %H:%M:%S') out['response']['status'] = 'OK' out['response']['status_code'] = 200 out['response']['data'] = [] for row in rows: lon = row[0] lat = row[1] val = row[2] new_data_item = {} new_data_item['type'] = 'Feature' new_data_item['geometry'] = { 'type': 'Point', 'coordinates': [lon, lat] } new_data_item['properties'] = {'values': [val]} out['response']['data'].append(new_data_item) if dates: query = "select to_char(date, \'YYYY-MM-DD HH24:MI:SS\') from grid_dates where uid in %s" % date_str else: query = "select to_char(date, \'YYYY-MM-DD HH24:MI:SS\') from grid_dates" rows = db_session.execute(query) out['response']['metadata'] = {} out['response']['metadata']['dates'] = [] for row in rows: date_str = str(row[0]) out['response']['metadata']['dates'].append(date_str) out['response']['metadata']['region'] = poly query = "select vname from grid_vars where uid=%s" % var_id rows = db_session.execute(query) for row in rows: vname = row[0] query = "select meta_data from grid_meta where uid=%s" % meta_id rows = db_session.execute(query) for row in rows: meta_data = row[0] for var in meta_data['variables']: if var['name'] == vname: for attr in var['attributes']: if attr['name'] == 'units': units = attr['value'] out['response']['metadata']['units'] = units out['response']['metadata']['format'] = 'grid' return out
def return_griddata(meta_id, var_id, poly, date): # poly + date specified if poly and date: poly_str = "ST_Polygon(ST_GeomFromText('LINESTRING(%s %s, %s %s, %s %s, %s %s, %s %s)'), 4326)" %\ (poly[0][0], poly[0][1], poly[1][0], poly[1][1], poly[2][0], poly[2][1], poly[3][0], poly[3][1], poly[4][0], poly[4][1]) query = "SELECT ST_X(geom), ST_Y(geom), val " \ "from (select (ST_PixelAsCentroids(ST_Clip(rast, %s, TRUE))).* from " \ "grid_data where meta_id=%s and var_id=%s and date=%s) foo;" %\ (poly_str, meta_id, var_id, date) # only poly specified elif poly: poly_str = "ST_Polygon(ST_GeomFromText('LINESTRING(%s %s, %s %s, %s %s, %s %s, %s %s)'), 4326)" %\ (poly[0][0], poly[0][1], poly[1][0], poly[1][1], poly[2][0], poly[2][1], poly[3][0], poly[3][1], poly[4][0], poly[4][1]) query = "SELECT ST_X(geom), ST_Y(geom), val " \ "from (select (ST_PixelAsCentroids(ST_Clip(rast, %s, TRUE))).* from " \ "grid_data where meta_id=%s and var_id=%s) foo;" %\ (poly_str, meta_id, var_id) # only date specified elif date: poly = [[-180, -90], [180, -90], [180, 90], [-180, 90], [-180, -90]] poly_str = "ST_Polygon(ST_GeomFromText('LINESTRING(%s %s, %s %s, %s %s, %s %s, %s %s)'), 4326)" %\ (poly[0][0], poly[0][1], poly[1][0], poly[1][1], poly[2][0], poly[2][1], poly[3][0], poly[3][1], poly[4][0], poly[4][1]) query = "SELECT ST_X(geom), ST_Y(geom), val " \ "from (select (ST_PixelAsCentroids(ST_Clip(rast, %s, TRUE))).* from " \ "grid_data where meta_id=%s and var_id=%s and date=%s) foo;" %\ (poly_str, meta_id, var_id, date) # neither poly nor date specified else: poly = [[-180, -90], [180, -90], [180, 90], [-180, 90], [-180, -90]] poly_str = "ST_Polygon(ST_GeomFromText('LINESTRING(%s %s, %s %s, %s %s, %s %s, %s %s)'), 4326)" %\ (poly[0][0], poly[0][1], poly[1][0], poly[1][1], poly[2][0], poly[2][1], poly[3][0], poly[3][1], poly[4][0], poly[4][1]) query = "SELECT ST_X(geom), ST_Y(geom), val " \ "from (select (ST_PixelAsCentroids(ST_Clip(rast, %s, TRUE))).* from " \ "grid_data where meta_id=%s and var_id=%s) foo;" %\ (poly_str, meta_id, var_id) print query rows = db_session.execute(query) # the response JSON out = {} out['request'] = {} out['request']['datetime'] = time.strftime('%Y-%m-%d %H:%M:%S') out['response'] = {} out['response']['datetime'] = time.strftime('%Y-%m-%d %H:%M:%S') out['response']['status'] = 'OK' out['response']['status_code'] = 200 out['response']['data'] = [] for row in rows: lon = row[0] # longitude lat = row[1] # latitude vals = row[2] # array of values, including NULL new_data_item = {} new_data_item['type'] = 'Feature' new_data_item['geometry'] = { 'type': 'Point', 'coordinates': [lon, lat] } new_data_item['properties'] = {'values': vals} out['response']['data'].append(new_data_item) if date: query = "select to_char(date, \'YYYY-MM-DD HH24:MI:SS\') from grid_dates where uid=%s" % ( date) else: query = "select to_char(date, \'YYYY-MM-DD HH24:MI:SS\') from grid_dates" rows = db_session.execute(query) out['response']['metadata'] = {} out['response']['metadata']['dates'] = [] for row in rows: date_str = str(row[0]) out['response']['metadata']['dates'].append(date_str) out['response']['metadata']['region'] = poly query = "select vname from grid_vars where uid=%s" % var_id rows = db_session.execute(query) for row in rows: vname = row[0] query = "select meta_data from grid_meta where uid=%s" % meta_id rows = db_session.execute(query) for row in rows: meta_data = row[0] for var in meta_data['variables']: if var['name'] == vname: for attr in var['attributes']: if attr['name'] == 'units': units = attr['value'] out['response']['metadata']['units'] = units out['response']['metadata']['format'] = 'grid' return out
def return_griddata_aggregate_spatial_by_id(meta_id, var_id, poly, date): # poly + date specified if poly and date: query = "select ST_SummaryStats(ST_Union(ST_Clip(rast, r.geom, true))) from grid_data as gd, regions as r " \ "where gd.meta_id=%s and gd.var_id=%s and r.uid=%s and gd.date=%s;" %\ (meta_id, var_id, poly, date) # only poly specified elif poly: query = "select ST_SummaryStats(ST_Union(ST_Clip(rast, r.geom, true))) from grid_data as gd, regions as r " \ "where gd.meta_id=%s and gd.var_id=%s and r.uid=%s;" %\ (meta_id, var_id, poly) # only date specified elif date: poly = [[-180, -90], [180, -90], [180, 90], [-180, 90], [-180, -90]] poly_str = "ST_Polygon(ST_GeomFromText('LINESTRING(%s %s, %s %s, %s %s, %s %s, %s %s)'), 4326)" %\ (poly[0][0], poly[0][1], poly[1][0], poly[1][1], poly[2][0], poly[2][1], poly[3][0], poly[3][1], poly[4][0], poly[4][1]) query = "select ST_SummaryStats(ST_Union(ST_Clip(rast, %s, true))) from grid_data " \ "where meta_id=%s and var_id=%s and date=%s;" %\ (poly_str, meta_id, var_id, date) # neither poly nor date specified else: poly = [[-180, -90], [180, -90], [180, 90], [-180, 90], [-180, -90]] poly_str = "ST_Polygon(ST_GeomFromText('LINESTRING(%s %s, %s %s, %s %s, %s %s, %s %s)'), 4326)" %\ (poly[0][0], poly[0][1], poly[1][0], poly[1][1], poly[2][0], poly[2][1], poly[3][0], poly[3][1], poly[4][0], poly[4][1]) query = "select ST_SummaryStats(ST_Union(ST_Clip(rast, %s, true))) from grid_data " \ "where meta_id=%s and var_id=%s;" %\ (poly_str, meta_id, var_id) #print query rows = db_session.execute(query) for row in rows: res = row[0].lstrip('(').rstrip(')').split(',') count = int(res[0]) sum = float(res[1]) mean = float(res[2]) stddev = float(res[3]) min = float(res[4]) max = float(res[5]) # the response JSON out = {} out['request'] = {} out['request']['datetime'] = time.strftime('%Y-%m-%d %H:%M:%S') out['response'] = {} out['response']['datetime'] = time.strftime('%Y-%m-%d %H:%M:%S') out['response']['status'] = 'OK' out['response']['status_code'] = 200 out['response']['data'] = [] new_data_item = {} new_data_item['type'] = 'Feature' new_data_item['geometry'] = {'type': 'Polygon', 'coordinates': poly} new_data_item['properties'] = { 'count': count, 'sum': sum, 'mean': mean, 'stddev': stddev, 'min': min, 'max': max } out['response']['data'].append(new_data_item) if date: query = "select to_char(date, \'YYYY-MM-DD HH24:MI:SS\') from grid_dates where uid=%s" % ( date) else: query = "select to_char(date, \'YYYY-MM-DD HH24:MI:SS\') from grid_dates" rows = db_session.execute(query) out['response']['metadata'] = {} out['response']['metadata']['dates'] = [] for row in rows: date_str = str(row[0]) out['response']['metadata']['dates'].append(date_str) out['response']['metadata']['region'] = poly query = "select vname from grid_vars where uid=%s" % var_id rows = db_session.execute(query) for row in rows: vname = row[0] query = "select meta_data from grid_meta where uid=%s" % meta_id rows = db_session.execute(query) for row in rows: meta_data = row[0] for var in meta_data['variables']: if var['name'] == vname: for attr in var['attributes']: if attr['name'] == 'units': units = attr['value'] out['response']['metadata']['units'] = units out['response']['metadata']['format'] = 'polygon' return out