rburghol
commented
2 weeks ago TRy new method without raster calc which is unneccessary?
create temp table raster_best(tid serial, featureid integer, tstime bigint, tsendtime bigint, varid integer, rast raster);
insert into raster_best(featureid, tstime, tsendtime, varid, rast)
select f.hydroid, w.tstime, w.tsendtime, w.varid,
ST_Resample(st_clip(w.rast, fgeo.dh_geofield_geom), tpr.rast) as rast
from dh_feature as f
left outer join field_data_dh_geofield as fgeo
on (
fgeo.entity_id = f.hydroid
and fgeo.entity_type = 'dh_feature'
)
left outer join week_ratings as r
on (
yr = 1984
)
left outer join dh_variabledefinition as v
on (
v.varkey = r.best_varkey
)
left outer join dh_timeseries_weather as w
on (
extract( year from to_timestamp(w.tsendtime) ) = r.yr
and extract( month from to_timestamp(w.tsendtime) ) = r.mo
and extract( week from to_timestamp(w.tsendtime) ) = r.wk
and v.hydroid = w.varid
)
left outer join raster_templates as tpr
on (1=1)
where f.hydrocode = 'usgs_ws_01665500'
and w.tid is not null;
- Now export
PGHOST=dbase2 PGPORT=5432 PGUSER=rob gdal_translate -of GTiff "PG:dbname=drupal.dh03 table=raster_best_out where='tid = 1 ' " outfile.tiff
COBrogan
ilonah22
Overview
lm()of $Q{week} = f(P{week,month})$lm()created to compare weekly mean P with weekly mean QWork flow steps
Prep Variables
Quick Run
cd /backup/meteorology/opt/model/meta_model/run_model raster_met PRISM usgs_ws_02031000 auto geo downloadRun steps
/opt/model/meta_model/run_model raster_met PRISM usgs_ws_02031000 auto geo downloadcalc_raster_tscalc_raster_ts [hydrocode] [varkey] [outfile] [dbhost] [dbname] [remove sql file=y/n]/opt/model/model_meteorology/sh/calc_raster_ts usgs_ws_02031000 prism_mod_daily "/tmp/usgs_ws_02031000-prism-all.csv" dbase2 drupal.dh03$NLDAS_ROOT/[datasource]/out/[hydrocode]-[datasource]-all.csv$NLDAS_ROOT/[datasource]/out/[hydrocode]-[datasource]-all.csv$NLDAS_ROOT/[datasource]/out/[hydrocode]-[datasource]-all.csv$NLDAS_ROOT/[datasource]/out/[hydrocode]-[datasource]-weekly.csvrun_model raster_met PRISM "usgs_ws_02031000" auto geo analyze 01sftp dbase2:/tmp/usgs_ws_02031000-prism-all.csv /var/www/html/files/met/usgs_ws_02031000-prism-all.csvGenerate Weekly Ratings
lm()predictions versus USGS observedlm()predictions versus USGS observedcalc_raster_ts)Import Week Ratings
Create Temp Table
copy week_ratings from '/tmp/met_weekly_ratings_01665500.csv' with HEADER CSV;
alter table week_ratings add column best_varid integer; alter table week_ratings add column best_varkey varchar(64);
update week_ratings set best_varkey = CASE WHEN best_method = 'prism' THEN 'prism_mod_daily' WHEN best_method = 'daymet' THEN 'daymet_mod_daily' ELSE 'do_not_overwrite_nldas2_obs_hourly' END;
--use an existing raster as template for new raster -- adds a single band, with value 0.0 in each pixel using the band type as defined by the daymet raster dataset create table raster_templates(rid serial, rast raster); INSERT INTO raster_templates(rast) SELECT ST_AddBand(ST_MakeEmptyRaster(rast), 1, ST_BandPixelType(rast), 0.0) FROM dh_timeseries_weather WHERE featureid in ( select hydroid from dh_feature where hydrocode = 'cbp6_met_coverage' ) and varid in (select hydroid from dh_variabledefinition where varkey like 'daymet_mod_daily') and rast is not null limit 1;
select st_astext(st_envelope(rast)) from raster_templates; -- POLYGON((-83.66607521306385 44.095589132351506,-73.52855441054511 44.095589132351506,-73.52855441054511 33.95806832983277,-83.66607521306385 33.95806832983277,-83.66607521306385 44.095589132351506))
create temp table raster_best(tid serial, featureid integer, tstime bigint, tsendtime bigint, varid integer, rast raster); insert into raster_best(featureid, tstime, tsendtime, varid, rast) select f.hydroid, w.tstime, w.tsendtime, w.varid, st_mapalgebra(tpr.rast, 1, ST_Resample(st_clip(w.rast, fgeo.dh_geofield_geom), tpr.rast), 1, '[rast] + [rast2]') as rast from dh_feature as f left outer join field_data_dh_geofield as fgeo on ( fgeo.entity_id = f.hydroid and fgeo.entity_type = 'dh_feature' ) left outer join week_ratings as r on ( yr = 1984 ) left outer join dh_variabledefinition as v on ( v.varkey = r.best_varkey ) left outer join dh_timeseries_weather as w on ( extract( year from to_timestamp(w.tsendtime) ) = r.yr and extract( month from to_timestamp(w.tsendtime) ) = r.mo and extract( week from to_timestamp(w.tsendtime) ) = r.wk and v.hydroid = w.varid ) left outer join raster_templates as tpr on (1=1) where f.hydrocode = 'usgs_ws_01665500' and w.tid is not null;
PGHOST=dbase2 PGPORT=5432 PGUSER=rob gdal_translate -of GTiff "PG:dbname=drupal.dh03 table=raster_best_out where='tid = 1 ' " outfile.tiff
SET postgis.gdal_enabled_drivers = 'ENABLE_ALL'; select tid, lowrite(lo_open(tid, 131072), tiff) As num_bytes FROM ( VALUES (lo_create(0), ST_Astiff( (SELECT rast FROM raster_best where tid = 1)) ) ) As v(tid,tiff);
tid | num_bytes ---------+----------- 9155256 | 4002 (1 row)
\lo_export 9155256 '/tmp/myraster.tiff' SELECT lo_unlink(9155256 );