search

HARPgroup / model_meteorology

0 stars 0 forks source link

Workflow `met` #64

Open rburghol opened 4 months ago

rburghol commented 4 months ago

Tasks

Overview

rburghol commented 2 months ago
rburghol commented 2 months ago

Manually insert a draft hourly fraction raster set. To be replaced for daily imports later.

Clear old data

delete from dh_timeseries_weather where varid in (select hydroid from dh_variabledefinition where varkey = 'nldas2_precip_daily_frac');

Take our Hourly Data and sum to create a single precipitation value

--This sums all of our raster points, so 24 rasters, into one, this creates a single raster of the full days rain
create temp table tmp_nldas2_daily_precip as
select min(a.tstime), max(a.tsendtime) as tsendtime, a.featureid, a.entity_type, ST_Union(a.rast, 'SUM') as rast
from dh_timeseries_weather as a
left outer join dh_variabledefinition as b
on (a.varid = b.hydroid and b.varkey='nldas2_obs_hourly')
WHERE b.varkey = 'nldas2_obs_hourly'
GROUP BY a.featureid, a.entity_type, date(to_timestamp(a.tsendtime));

24-Hour Fractions of Daily Total tmp_fraction_raster

--We take our hourly raster and our daily raster and figure out the fraction of rain for each hour, for each raster
create temp table tmp_fraction_raster as
SELECT hourly_table.featureid, hourly_table.entity_type, hourly_table.tstime, hourly_table.tsendtime,
   st_mapalgebra(hourly_table.rast, 1, ST_SetBandNoDataValue(daily_table.rast,0), 1, '[rast1] / [rast2]') as rast
from dh_timeseries_weather as hourly_table
left outer join tmp_nldas2_daily_precip as daily_table
on (
   DATE(to_timestamp(daily_table.tsendtime)) = DATE(to_timestamp(hourly_table.tsendtime)) 
)
left outer join dh_variabledefinition as b
on (hourly_table.varid = b.hydroid and b.varkey='nldas2_obs_hourly')
WHERE b.varkey = 'nldas2_obs_hourly';

Add these to the dh_timeseries_weather table

insert into dh_timeseries_weather(featureid, entity_type, varid, tstime, tsendtime, rast)
select a.featureid, a.entity_type, b.hydroid as varid, a.tstime, a.tsendtime, a.rast 
from dh_variabledefinition as b, tmp_fraction_raster as a
where b.varkey = 'nldas2_precip_daily_frac'
rburghol commented 6 days ago

@mwdunlap2004 Hint: how to turn a text date (YYYY-MM-DD) into a Unix timestamp? Something like this:

select extract(epoch from ('2021-05-01'::timestamp));