Notes for updating calsimetaw

[andybell]

Add Grid, DAU, spatial cimis eto to the PostGIS database

Makefile

The make file pulls the dau, calsimetaw grid from github repos and adds it to the postgresql database. The Makefile was modified so it pulls dwr-dau-2.1.0 version which includes a dau - county file (dauco.geojson).

The bounding box is added to the database from a local copy of the ssj-overview repo.

Modified the makefile to pull California counties boundaries from https://github.com/ucd-library/california-counties/releases

The file also pulls in the spatial cimis eto raster from a cloned copy of the ssj-weather repo. Also, update for wy 2016 so it adds the 2016.wy/ETo.tif. These files can be found in cimis.eto_wy2015 (replaces cimis.eto) & cimis.eto_wy2016

Set schema

Make sure that the schema "calsimetaw", "overview", "cimis", is already created in the postgres data base

CREATE SCHEMA schema_name

Run

The files can be added to the db using make. Note: changed the default port=5432 (from 5433)

Add model output

model_output.sql

This sql file creates a table called calsimetaw.model_output and copies the data from model_output.csv to the database. The sql code was run by cding to the model output folder and running psql service=ssj -f model_output.sql. Changed the table path for the model results are aded to the calsimetaw schema and renamed for the different water years. Files are now called calsimetaw.model_output_wy2015 & calsimetaw.model_output_wy2015. The years, months, days, doy fields need to be intergers. See ChangeCols2Int.R for really quick and dirty way to convert these columns to integer types. The last row of all the model_output in 2016 (ie September 30th of 2016) had a few NA values. These were replaced with zeros.

Weather?

The file calsimetaw.sql creates two tables weather.psychrometic_constant & weather.calsimetaw. It is not clear where the values for the two tables are comming from. The psychrometric_constant.csv file is not included in the repo.

Landuse

landuse.sql contains several queries that provide landuse information for CalSIMETAW by DAUco.

calsimetaw_landuse view

This query creates a view by summing the intersection of the landuse geometry and the dauco geom. The table reports the area for level_1 and level_2 crops that are within each DAUco area.

level_1_ct level_2_ct

These sections create views for the two crop levels that map the headers in the model_output table (ie "DAUCo18501GA") with the dauco integer id.

Crosstab for landuse area totals by DAUco

Need to enable extension

CREATE EXTENSION tablefunc;

Export to csv (run this in psql terminal). Otherwise, you'll hit an error that says you don't have permission to write to file.

\COPY (SELECT * FROM level_1_ct) TO '/home/ssj/ssj-calsimetaw/level_1_ct_2015.csv' DELIMITER ',' CSV HEADER;
\COPY (SELECT * FROM level_2_ct) TO '/home/ssj/ssj-calsimetaw/level_2_ct_2015.csv' DELIMITER ',' CSV HEADER;

fixup model_output for fallow?

Updates the calsimetaw.model_output table by selecting all fallow rows and set the min(okc) as okc, zero as ikc, and min(okc) as kc. Not sure why this is needed.

calsimetaw.crosswalk

Creates a table with a cross walk with all the calsimetaw crop types and the level_2 landiq levels. Added 'Summer Fallow' to the crosswalk to properly deal with naming issues with LandIQ.

landuse boundary (ssj-landuse rep)

The landuse table should have a columns called boundary that has valid geometry

-- Fix some non-valid geometries? 
alter table landuse add column boundary geometry(Geometry, 3310);
update landuse set boundary=st_makeValid(geom);

Raster

psql service=ssj -f raster.sql - this takes quite a while to run be patient