def _saveRasters(filepath, name, varname, startdate, enddate, dbname):
""""Save geophysical variable from *dbname* database, between *startdate*
and *enddate* dates into Geotif files inside *filepath* directory."""
logging.basicConfig(level=logging.INFO, format='%(message)s')
log = logging.getLogger(__name__)
if dbio.tableExists(dbname, name, varname):
db = dbio.connect(dbname)
cur = db.cursor()
sql = "select fdate,st_astiff(st_union(rast)) as tif from {0}.{1}".format(
name, varname)
try:
sdt = datetime.strptime(startdate, "%Y-%m-%d")
edt = datetime.strptime(enddate, "%Y-%m-%d")
sql += " where fdate>=date'{0}' and fdate<=date'{1} group by fdate".format(
sdt.strftime("%Y-%m-%d"), edt.strftime("%Y-%m-%d"))
except ValueError:
sql += " group by fdate"
log.warning("Start and/or end dates were invalid. Ignoring...")
cur.execute(sql)
results = cur.fetchall()
for res in results:
with open(
"{0}/{1}_{2}.tif".format(filepath, varname,
res[0].strftime("%Y%m%d")),
'wb') as fout:
fout.write(res[1])
else:
log.error("Variable {0} does not exist in schema {1}.".format(
varname, name))
def calcSeverity(model, cid, varname="soil_moist"):
"""Calculate drought severity from *climatology* table stored in database."""
log = logging.getLogger(__name__)
outvars = model.getOutputStruct(model.model_path + "/global.txt")
col = outvars[varname][1]
if varname in ["soil_moist"]:
p = np.loadtxt("{0}/{1}_{2:.{4}f}_{3:.{4}f}".format(model.model_path, outvars['runoff'][0], model.gid[cid][0], model.gid[cid][1], model.grid_decimal))[:, col:col+model.nlayers]
p = pandas.Series(np.sum(p, axis=1), [datetime(model.startyear, model.startmonth, model.startday) + timedelta(t) for t in range(len(p))])
else:
p = np.loadtxt("{0}/{1}_{2:.{4}f}_{3:.{4}f}".format(model.model_path, outvars['runoff'][0], model.gid[cid][0], model.gid[cid][1], model.grid_decimal))[:, col]
p = pandas.Series(p, [datetime(model.startyear, model.startmonth, model.startday) + timedelta(t) for t in range(len(p))])
db = dbio.connect(model.dbname)
cur = db.cursor()
if dbio.tableExists(model.dbname, model.name, varname):
if varname in ["soil_moist"]:
lvar = ",layer"
else:
lvar = ""
if dbio.columnExists(model.dbname, model.name, varname, "ensemble"):
fsql = "with f as (select fdate{3},avg(st_value(rast,st_geomfromtext('POINT({0} {1})',4326))) as vals from {2}.{4} where st_intersects(rast,st_geomfromtext('POINT({0} {1})',4326)) group by fdate{3})".format(model.gid[cid][1], model.gid[cid][0], model.name, lvar, varname)
else:
fsql = "with f as (select fdate{3},st_value(rast,st_geomfromtext('POINT({0} {1})',4326)) as vals from {2}.{4} where st_intersects(rast,st_geomfromtext('POINT({0} {1})',4326)))".format(model.gid[cid][1], model.gid[cid][0], model.name, lvar, varname)
sql = "{0} select fdate,sum(vals) from f group by fdate".format(fsql)
cur.execute(sql)
if bool(cur.rowcount):
results = cur.fetchall()
clim = pandas.Series([r[1] for r in results], [r[0] for r in results])
else:
clim = p
else:
log.warning("Climatology table does not exist. Severity calculation will be inaccurate!")
clim = p
s = 100.0 - np.array(map(lambda v: stats.percentileofscore(clim, v), p))
return s
def ingestSoils(dbname="rheas"):
"""Ingest soil information from downloaded files."""
filenames = glob.glob("SoilGrids-for-DSSAT-10km v1.0 (by country)/*.SOL")
db = dbio.connect(dbname)
cur = db.cursor()
if dbio.tableExists(dbname, "dssat", "soils"):
print("Overwriting existing DSSAT soils table in database!")
cur.execute("drop table dssat.soils")
db.commit()
cur.execute(
"create table dssat.soils (rid serial primary key, geom geometry(Point, 4326), props text)"
)
db.commit()
for filename in filenames:
try:
profiles = parseSolFile(filename)
for latlon in profiles:
lat, lon = latlon
sql = "insert into dssat.soils (geom, props) values (st_geomfromtext('POINT({0} {1})', 4326), '{2}')".format(
lon, lat, profiles[latlon])
cur.execute(sql)
except:
print("Cannot process file {0}".format(filename))
db.commit()
cur.close()
db.close()
def calcSMDI(model, cid):
"""Calculate Soil Moisture Deficit Index (Narasimhan & Srinivasan, 2005)."""
log = logging.getLogger(__name__)
outvars = model.getOutputStruct(model.model_path + "/global.txt")
col = outvars['soil_moist'][1]
p = np.loadtxt("{0}/{1}_{2:.{4}f}_{3:.{4}f}".format(
model.model_path, outvars['soil_moist'][0], model.gid[cid][0],
model.gid[cid][1], model.grid_decimal))[:, col:col + model.nlayers]
p = pandas.Series(np.sum(p, axis=1), [
datetime(model.startyear, model.startmonth, model.startday) +
timedelta(t) for t in range(len(p))
])
db = dbio.connect(model.dbname)
cur = db.cursor()
if dbio.tableExists(model.dbname, model.name, "soil_moist"):
if dbio.columnExists(model.dbname, model.name, "soil_moist",
"ensemble"):
fsql = "with f as (select fdate,layer,avg(st_value(rast,st_geomfromtext('POINT({0} {1})',4326))) as sm from {2}.soil_moist where st_intersects(rast,st_geomfromtext('POINT({0} {1})',4326)) group by fdate,layer)".format(
model.gid[cid][1], model.gid[cid][0], model.name)
else:
fsql = "with f as (select fdate,layer,st_value(rast,st_geomfromtext('POINT({0} {1})',4326)) as sm from {2}.soil_moist where st_intersects(rast,st_geomfromtext('POINT({0} {1})',4326)))".format(
model.gid[cid][1], model.gid[cid][0], model.name)
sql = "{0} select fdate,sum(sm) from f group by fdate".format(fsql)
cur.execute(sql)
if bool(cur.rowcount):
results = cur.fetchall()
clim = pandas.Series([r[1] for r in results],
[r[0] for r in results])
else:
clim = p
else:
log.warning(
"Climatology table does not exist. SMDI calculation will be inaccurate!"
)
clim = p
smdi = np.zeros(len(p))
MSW = clim.median()
maxSW = clim.max()
minSW = clim.min()
for i in range(7, len(smdi)):
SW = np.median(p[i - 7:i + 1])
if SW == MSW:
SD = (SW - MSW) / (MSW - minSW) * 100.0
else:
SD = (SW - MSW) / (maxSW - MSW) * 100.0
if i > 7:
smdi[i] = 0.5 * smdi[i - 1] + SD / 50.0
else:
smdi[i] = SD / 50.0
cur.close()
db.close()
return smdi
def calcSMDI(model, cid):
"""Calculate Soil Moisture Deficit Index (Narasimhan & Srinivasan, 2005)."""
log = logging.getLogger(__name__)
outvars = model.getOutputStruct(model.model_path + "/global.txt")
col = outvars['soil_moist'][1]
p = np.loadtxt("{0}/{1}_{2:.{4}f}_{3:.{4}f}".format(model.model_path, outvars['soil_moist'][0], model.gid[cid][0], model.gid[cid][1], model.grid_decimal))[:, col:col+model.nlayers]
p = pandas.Series(np.sum(p, axis=1), [datetime(model.startyear, model.startmonth, model.startday) + timedelta(t) for t in range(len(p))])
db = dbio.connect(model.dbname)
cur = db.cursor()
if dbio.tableExists(model.dbname, model.name, "soil_moist"):
if dbio.columnExists(model.dbname, model.name, "soil_moist", "ensemble"):
fsql = "with f as (select fdate,layer,avg(st_value(rast,st_geomfromtext('POINT({0} {1})',4326))) as sm from {2}.soil_moist where st_intersects(rast,st_geomfromtext('POINT({0} {1})',4326)) group by fdate,layer)".format(model.gid[cid][1], model.gid[cid][0], model.name)
else:
fsql = "with f as (select fdate,layer,st_value(rast,st_geomfromtext('POINT({0} {1})',4326)) as sm from {2}.soil_moist where st_intersects(rast,st_geomfromtext('POINT({0} {1})',4326)))".format(model.gid[cid][1], model.gid[cid][0], model.name)
sql = "{0} select fdate,sum(sm) from f group by fdate".format(fsql)
cur.execute(sql)
if bool(cur.rowcount):
results = cur.fetchall()
clim = pandas.Series([r[1] for r in results], [r[0] for r in results])
else:
clim = p
else:
log.warning("Climatology table does not exist. SMDI calculation will be inaccurate!")
clim = p
smdi = np.zeros(len(p))
MSW = clim.median()
maxSW = clim.max()
minSW = clim.min()
for i in range(7, len(smdi)):
SW = np.median(p[i-7:i+1])
if SW == MSW:
SD = (SW - MSW) / (MSW - minSW) * 100.0
else:
SD = (SW - MSW) / (maxSW - MSW) * 100.0
if i > 7:
smdi[i] = 0.5 * smdi[i-1] + SD / 50.0
else:
smdi[i] = SD / 50.0
cur.close()
db.close()
return smdi
def _saveRasters(filepath, name, varname, startdate, enddate, dbname):
""""Save geophysical variable from *dbname* database, between *startdate*
and *enddate* dates into Geotif files inside *filepath* directory."""
logging.basicConfig(level=logging.INFO, format='%(message)s')
log = logging.getLogger(__name__)
if dbio.tableExists(dbname, name, varname):
db = dbio.connect(dbname)
cur = db.cursor()
sql = "select fdate,st_astiff(st_union(rast)) as tif from {0}.{1}".format(name, varname)
try:
sdt = datetime.strptime(startdate, "%Y-%m-%d")
edt = datetime.strptime(enddate, "%Y-%m-%d")
sql += " where fdate>=date'{0}' and fdate<=date'{1} group by fdate".format(sdt.strftime("%Y-%m-%d"), edt.strftime("%Y-%m-%d"))
except ValueError:
sql += " group by fdate"
log.warning("Start and/or end dates were invalid. Ignoring...")
cur.execute(sql)
results = cur.fetchall()
for res in results:
with open("{0}/{1}_{2}.tif".format(filepath, varname, res[0].strftime("%Y%m%d")), 'wb') as fout:
fout.write(res[1])
else:
log.error("Variable {0} does not exist in schema {1}.".format(varname, name))
def ingestSoils(dbname="rheas"):
"""Ingest soil information from downloaded files."""
filenames = glob.glob("SoilGrids-for-DSSAT-10km v1.0 (by country)/*.SOL")
db = dbio.connect(dbname)
cur = db.cursor()
if dbio.tableExists(dbname, "dssat", "soils"):
print("Overwriting existing DSSAT soils table in database!")
cur.execute("drop table dssat.soils")
db.commit()
cur.execute("create table dssat.soils (rid serial primary key, geom geometry(Point, 4326), props text)")
db.commit()
for filename in filenames:
try:
profiles = parseSolFile(filename)
for latlon in profiles:
lat, lon = latlon
sql = "insert into dssat.soils (geom, props) values (st_geomfromtext('POINT({0} {1})', 4326), '{2}')".format(lon, lat, profiles[latlon])
cur.execute(sql)
except:
print("Cannot process file {0}".format(filename))
db.commit()
cur.close()
db.close()
def writeToDB(self, data, dates, tablename, initialize, ensemble=False, skipsave=0):
"""Writes output data into database."""
db = dbio.connect(self.dbname)
cur = db.cursor()
if dbio.tableExists(self.dbname, self.name, tablename) and ensemble and not dbio.columnExists(self.dbname, self.name, tablename, "ensemble"):
print("WARNING! Table {0} exists but does not contain ensemble information. Overwriting entire table!")
cur.execute("drop table {0}.{1}".format(self.name, tablename))
db.commit()
if dbio.tableExists(self.dbname, self.name, tablename):
if initialize:
for dt in [self.startdate + timedelta(t) for t in range((self.enddate - self.startdate).days+1)]:
dbio.deleteRasters(self.dbname, "{0}.{1}".format(self.name, tablename), dt)
else:
sql = "create table {0}.{1} (id serial not null primary key, rid int not null, fdate date not null, rast raster)".format(
self.name, tablename)
cur.execute(sql)
if data.shape[1] > 1:
cur.execute("alter table {0}.{1} add column layer int".format(self.name, tablename))
if ensemble:
cur.execute("alter table {0}.{1} add column ensemble int".format(self.name, tablename))
db.commit()
startyear, startmonth, startday = self.startyear, self.startmonth, self.startday
if skipsave > 0:
ts = date(self.startyear, self.startmonth,
self.startday) + timedelta(skipsave)
data = data[skipsave:]
startyear, startmonth, startday = ts.year, ts.month, ts.day
tiffiles = []
for t in range(data.shape[0]):
dt = date(startyear, startmonth, startday) + timedelta(t)
for lyr in range(data.shape[1]):
filename = "{0}/{1}_{2}{3:02d}{4:02d}_{5:02d}.tif".format(
self.model_path, tablename, dt.year, dt.month, dt.day, lyr + 1)
self._writeRaster(data[t, lyr, :, :], filename)
tiffiles.append(filename)
ps1 = subprocess.Popen(["{0}/raster2pgsql".format(rpath.bins), "-s", "4326", "-F", "-d", "-t", "auto"] + tiffiles + ["temp"], stdout=subprocess.PIPE)
ps2 = subprocess.Popen(["{0}/psql".format(rpath.bins), "-d", self.dbname], stdin=ps1.stdout, stdout=subprocess.PIPE)
ps1.stdout.close()
ps2.communicate()[0]
ps1.wait()
cur.execute("alter table temp add column fdate date")
cur.execute("update temp set fdate = date (concat_ws('-',substring(filename from {0} for 4),substring(filename from {1} for 2),substring(filename from {2} for 2)))".format(
len(tablename) + 2, len(tablename) + 6, len(tablename) + 8))
if data.shape[1] > 1:
cur.execute("alter table temp add column layer int")
cur.execute("update temp set layer=(substring(filename from {0} for 2))::int".format(
len(tablename) + 11))
cur.execute("select count(*) from temp")
n = int(cur.fetchone()[0])
ntiles = n / data.shape[0]
if data.shape[1] > 1:
cur.execute(
"insert into {0}.{1} (rid,fdate,layer,rast) select ((rid+{2}) % {2})+1,fdate,layer,rast from temp".format(self.name, tablename, ntiles))
else:
cur.execute(
"insert into {0}.{1} (rid,fdate,rast) select ((rid+{2}) % {2})+1,fdate,rast from temp".format(self.name, tablename, ntiles))
if bool(ensemble):
sql = "update {0}.{1} set ensemble = {2} where ensemble is null".format(
self.name, tablename, int(ensemble))
cur.execute(sql)
cur.execute("drop index if exists {0}.{1}_dtidx".format(
self.name, tablename))
cur.execute("create index {1}_dtidx on {0}.{1}(fdate)".format(
self.name, tablename))
cur.execute("drop index if exists {0}.{1}_spidx".format(
self.name, tablename))
cur.execute("create index {1}_spidx on {0}.{1} using gist(st_convexhull(rast))".format(
self.name, tablename))
db.commit()
cur.close()
db.close()
def writeToDB(self,
data,
dates,
tablename,
initialize,
ensemble=False,
skipsave=0):
"""Writes output data into database."""
db = dbio.connect(self.dbname)
cur = db.cursor()
if dbio.tableExists(self.dbname, self.name,
tablename) and ensemble and not dbio.columnExists(
self.dbname, self.name, tablename, "ensemble"):
print(
"WARNING! Table {0} exists but does not contain ensemble information. Overwriting entire table!"
)
cur.execute("drop table {0}.{1}".format(self.name, tablename))
db.commit()
if dbio.tableExists(self.dbname, self.name, tablename):
if initialize:
for dt in [
self.startdate + timedelta(t)
for t in range((self.enddate - self.startdate).days +
1)
]:
dbio.deleteRasters(self.dbname,
"{0}.{1}".format(self.name,
tablename), dt)
else:
sql = "create table {0}.{1} (id serial not null primary key, rid int not null, fdate date not null, rast raster)".format(
self.name, tablename)
cur.execute(sql)
if data.shape[1] > 1:
cur.execute("alter table {0}.{1} add column layer int".format(
self.name, tablename))
if ensemble:
cur.execute(
"alter table {0}.{1} add column ensemble int".format(
self.name, tablename))
db.commit()
startyear, startmonth, startday = self.startyear, self.startmonth, self.startday
if skipsave > 0:
ts = date(self.startyear, self.startmonth,
self.startday) + timedelta(skipsave)
data = data[skipsave:]
startyear, startmonth, startday = ts.year, ts.month, ts.day
tiffiles = []
for t in range(data.shape[0]):
dt = date(startyear, startmonth, startday) + timedelta(t)
for lyr in range(data.shape[1]):
filename = "{0}/{1}_{2}{3:02d}{4:02d}_{5:02d}.tif".format(
self.model_path, tablename, dt.year, dt.month, dt.day,
lyr + 1)
self._writeRaster(data[t, lyr, :, :], filename)
tiffiles.append(filename)
ps1 = subprocess.Popen([
"{0}/raster2pgsql".format(rpath.bins), "-s", "4326", "-F", "-d",
"-t", "auto"
] + tiffiles + ["temp"],
stdout=subprocess.PIPE)
ps2 = subprocess.Popen(
["{0}/psql".format(rpath.bins), "-d", self.dbname],
stdin=ps1.stdout,
stdout=subprocess.PIPE)
ps1.stdout.close()
ps2.communicate()[0]
ps1.wait()
cur.execute("alter table temp add column fdate date")
cur.execute(
"update temp set fdate = date (concat_ws('-',substring(filename from {0} for 4),substring(filename from {1} for 2),substring(filename from {2} for 2)))"
.format(
len(tablename) + 2,
len(tablename) + 6,
len(tablename) + 8))
if data.shape[1] > 1:
cur.execute("alter table temp add column layer int")
cur.execute(
"update temp set layer=(substring(filename from {0} for 2))::int"
.format(len(tablename) + 11))
cur.execute("select count(*) from temp")
n = int(cur.fetchone()[0])
ntiles = n / data.shape[0]
if data.shape[1] > 1:
cur.execute(
"insert into {0}.{1} (rid,fdate,layer,rast) select ((rid+{2}) % {2})+1,fdate,layer,rast from temp"
.format(self.name, tablename, ntiles))
else:
cur.execute(
"insert into {0}.{1} (rid,fdate,rast) select ((rid+{2}) % {2})+1,fdate,rast from temp"
.format(self.name, tablename, ntiles))
if bool(ensemble):
sql = "update {0}.{1} set ensemble = {2} where ensemble is null".format(
self.name, tablename, int(ensemble))
cur.execute(sql)
cur.execute("drop index if exists {0}.{1}_dtidx".format(
self.name, tablename))
cur.execute("create index {1}_dtidx on {0}.{1}(fdate)".format(
self.name, tablename))
cur.execute("drop index if exists {0}.{1}_spidx".format(
self.name, tablename))
cur.execute(
"create index {1}_spidx on {0}.{1} using gist(st_convexhull(rast))"
.format(self.name, tablename))
db.commit()
cur.close()
db.close()
