SQL

From shell (Postgres):

# log in using the server name, database name, and username
# psql -h [myhost] -d [mydb] -U [myuser]
psql -h dbase1 drupal.dh03

psql --help
\q or quit # to quit

Useful commands:

-- this is a comment

SELECT current_database();
SELECT pg_size_pretty(pg_database_size('drupal.dh03'));
SELECT pg_size_pretty(pg_total_relation_size('dh_feature'));

SELECT table_name
FROM information_schema.tables
WHERE table_type='BASE TABLE'
AND table_schema='public';

-- List all columns for a specified table
select *
from dh_properties
where false;

-- describe tables schema/details
drupal.dh03=> \d dh_feature
drupal.dh03=> \d+ dh_feature

-- list table constraints 
SELECT constraint_name, table_name, column_name, ordinal_position
FROM information_schema.key_column_usage
WHERE table_name = 'dh_feature';

Some example queries:

SELECT *
FROM dh_properties 
WHERE propname = 'hsp2_2022'
ORDER BY modified DESC
LIMIT 5;

SELECT *
FROM dh_properties as model
LEFT OUTER JOIN dh_properties as scenario 
  ON (model.pid = scenario.featureid)
WHERE model.propcode = 'cbp-6.0' AND scenario.propname = 'hsp2_2022'  
ORDER BY model.modified DESC
LIMIT 5;

SELECT rseg.hydrocode AS "rseg.hydrocode",
	   model.pid AS "model.pid", 
	   model.propname AS "model.propname", 
	   model.propcode AS "model.propcode", 
	   scenario.pid AS "scenario.pid", 
	   scenario.propname AS "scenario.propname", 
	   scenario.propcode AS "scenario.propcode",
	   Qout.propvalue AS "Qout"
FROM dh_properties as model
LEFT OUTER JOIN dh_properties as scenario 
  ON (model.pid = scenario.featureid)
LEFT OUTER JOIN dh_properties as Qout 
  ON (scenario.pid = Qout.featureid)
LEFT OUTER JOIN dh_feature as rseg 
  ON (model.featureid = rseg.hydroid)   
WHERE model.propcode = 'cbp-6.0' AND scenario.propname = 'hsp2_2022' AND Qout.propname = 'Qout'
ORDER BY model.modified DESC
LIMIT 10;

SELECT COUNT(*)
FROM dh_properties as model
LEFT OUTER JOIN dh_properties as scenario 
  ON (model.pid = scenario.featureid)
LEFT OUTER JOIN dh_properties as Qout 
  ON (scenario.pid = Qout.featureid)
LEFT OUTER JOIN dh_feature as rseg 
  ON (model.featureid = rseg.hydroid)   
WHERE model.propcode = 'cbp-6.0' AND scenario.propname = 'hsp2_2022' AND Qout.propname = 'Qout' AND rseg.hydrocode LIKE '%P%';

qa_data <- sqldf(
"SELECT pid, hydrocode, Qout_hsp2
 FROM wshed_data
 WHERE hydrocode LIKE '%PM%'
")

Window Functions:

# compute 7-day rolling average
gage <-sqldf(paste('SELECT *, 
                      AVG(Flow) OVER (ORDER BY Date ASC ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS sql_rolling_avg
                    FROM gage',sep="")) 

CTE:

# format table using CTE temporary table
sw_status_df_all = sqldf("""WITH cte AS(
                                SELECT  CASE
                                        WHEN `drought_status_override` < `[nonex_pct]_propcode` THEN `drought_status_override`
                                            ELSE `[nonex_pct]_propcode`
                                        END AS final_status,
                                        COUNT(`containing_drought_region`) AS gage_count
                                FROM sw_df
                                WHERE `[nonex_pct]_propcode` > 0
                                GROUP BY final_status
                            )
   
                            SELECT  CASE
                                        WHEN `final_status` = 1 THEN 'watch'
                                        WHEN `final_status` = 2 THEN 'warning'
                                        WHEN `final_status` = 3 THEN 'emergency'
                                    END AS gage_status,
                                    gage_count
                            FROM cte
                            """)

Complex Examples:

• Some PostgreSQL functions
  • TO_TIMESTAMP(timestamp, format)
  • EXTRACT(field FROM source)

-- https://github.com/HARPgroup/vahydro/blob/master/sql/ann_ne_monthly.sql
select * from (
  select a.hydroid, a.name, 
    extract(year from to_timestamp(b.tstime)),
    b.tsvalue as annual,
    sum(c.tsvalue) as monthly
  from dh_feature as a
  left outer join dh_timeseries as b 
  on (
    a.hydroid = b.featureid
    and b.entity_type = 'dh_feature'
    and b.varid = 305
  )
  left outer join dh_timeseries as c
  on (
    a.hydroid = c.featureid
    and c.entity_type = 'dh_feature'
    and c.varid = 1021
  )
  where a.bundle in ('well', 'intake')
  and extract(year from to_timestamp(b.tstime)) = 2021
  and extract(year from to_timestamp(c.tstime)) = 2021
  group by a.hydroid, a.name, b.tsvalue, extract(year from to_timestamp(b.tstime))
) as foo
where (
  (annual > 1.01 * monthly )
  or (annual < 0.99 * monthly )
)
;

Identifying watershed models that need to be run

WITH model AS (
	SELECT *
	FROM dh_feature AS watershed
	LEFT OUTER JOIN dh_properties AS model
	  ON (watershed.hydroid = model.featureid)
	WHERE watershed.bundle = 'watershed' AND
		  watershed.ftype = 'cbp60'
	),
	scenario AS (
	SELECT *
	FROM dh_properties as scenario
	WHERE scenario.propname = 'hsp2_2022'
	),
	metric AS (
	SELECT *
	FROM dh_properties as metric
	WHERE metric.propname = 'Qout'
	)
	
SELECT model.hydrocode, 
       model.ftype,
	   model.pid,
       model.propname, 
	   scenario.propname,
	   metric.propname,
       metric.propvalue
FROM model
	LEFT OUTER JOIN scenario
	  ON (model.pid = scenario.featureid)
	LEFT OUTER JOIN metric
	  ON (scenario.pid = metric.featureid)
	WHERE hydrocode LIKE 'P%' AND hydrocode NOT LIKE '%_0000'
;