Skip to content

Climate data processing

Jump to bottom
dkakkar edited this page Sep 16, 2020 · 28 revisions

PostGIS

Partition: Shared
Memory:150 GB
Time:168:00:00
Number of cores:2
location of database storage area: /n/holyscratch01/cga/jblossom/

BIL files

  • Upload data to postgis for tmax for 1981-2019 using the below script:
screen
  • Connect to Postgis DB
ssh $postgisnode e.g ssh holy7c06609.rc.fas.harvard.edu

module load postgis

psql -h localhost -p $port -d postgres

Create extension postgis;

\q
  • Copy tmax.sh from /n/holyscratch01/cga/dkakkar/scripts/tmax.sh to your folder and run it from terminal using:
./tmax.sh

This will upload all tmax BIL as separate table for each year to PostGIS DB. It will take about 1 day to complete the upload. You can check the status using:

screen -r
  • Union all BIL tables (~2 hours)
psql -h localhost -p 7779 -d postgres -f union.sql

Address Files

  • Upload the address CSV using command
ssh $postgisnode e.g. ssh holy7c06609.rc.fas.harvard.edu

module load postgis
  • Copy the address CSV to table and edit the date fields
screen

psql -h localhost -p 7779 -d postgres -f address.sql

Querying (~1.5 hrs)

psql -h localhost -p 7779 -d postgres -f query.sql
  • Calculations for other variables:

Edit the corresponding scripts to replace old variable name by new name

:%s/$oldvariable/$newvariable/g
  • RH and AH equations
RH =100*(EXP((17.625*tdmean)/(243.04+tdmean))/EXP((17.625*tmean)/(243.04+tmean))) 

AH  = {6.112*EXP[(17.67*tmean)/(tmean +243.5)] * RH * 2.1674}/{273.15+ tmean}

To calculate RH and AH use query_rh_ah.sql. After you have the results file for tdmean and tmean just edit the script to provide their correct location and then run it on command line using (your port value). Also replace the location of result file:

psql -h localhost -p 7779 -d postgres -f query_rh_ah.sql

This will result in a CSV with address_id, day, RH, AH

Clone this wiki locally