Technical Notes

SQL Queries

Model run query

Query for getting noon predictions based on model run

-- we want everything in UTC
set timezone = 'UTC';

select prediction_models.abbreviation, weather_station_model_predictions.station_code, prediction_model_run_timestamps.prediction_run_timestamp as model_run_timestamp, 
	  weather_station_model_predictions.prediction_timestamp, weather_station_model_predictions.tmp_tgl_2, weather_station_model_predictions.rh_tgl_2, weather_station_model_predictions.bias_adjusted_temperature, weather_station_model_predictions.bias_adjusted_rh

    from weather_station_model_predictions

    inner join prediction_model_run_timestamps on prediction_model_run_timestamps.id = weather_station_model_predictions.prediction_model_run_timestamp_id  inner join prediction_models on prediction_models.id = prediction_model_run_timestamps.prediction_model_id

    where
		-- get model run on day X
        prediction_model_run_timestamps.prediction_run_timestamp >= '2021-06-25'
        and prediction_model_run_timestamps.prediction_run_timestamp < '2021-06-26'
		-- get predictions from X to X+4
		and weather_station_model_predictions.prediction_timestamp >= '2021-06-25'
		and weather_station_model_predictions.prediction_timestamp < '2021-06-30'
		-- we are only interested in noon
		and extract(hour from weather_station_model_predictions.prediction_timestamp) = '20'
		-- we are only interested in these stations
		and weather_station_model_predictions.station_code in (390, 1375, 1075, 165, 956)
		-- we are only interested in these models
		and prediction_models.abbreviation in ('GDPS', 'HRDPS')
		-- we are particular about order
	order by prediction_models.abbreviation, weather_station_model_predictions.station_code, prediction_model_run_timestamps.prediction_run_timestamp, weather_station_model_predictions.prediction_timestamp		

Dumping some raw weather data into csv format

\copy (select prediction_models.abbreviation, prediction_models.name as model_name, prediction_model_run_timestamps.prediction_run_timestamp as model_run_timestamp, weather_station_model_predictions.* from  weather_station_model_predictions  inner join prediction_model_run_timestamps on prediction_model_run_timestamps.id = weather_station_model_predictions.prediction_model_run_timestamp_id  inner join prediction_models on prediction_models.id = prediction_model_run_timestamps.prediction_model_id  where weather_station_model_predictions.prediction_timestamp >= '2021-07-25'  and weather_station_model_predictions.prediction_timestamp <= '2021-07-26') to 'query_for_jesse.csv' csv header;