dlebauer

# on biocluster
metfile <- "/home/groups/ebimodeling/narr/threehourly_32km/in/out-rename/1990.nc"
library(ncdf4)
met.nc <- nc_open(metfile)
lat <- ncvar_get(met.nc, "latitude")
lon <- ncvar_get(met.nc, "longitude")
latlon <- data.table(lat = as.vector(lat), lon = as.vector(lon))
latlon[lat<43 & lat > 37 & lon < -87 & lon > -92]
write.csv(latlon, "latlon.csv")

dlebauer

[dlebauer@biocluster vars2]$ ncdump -h all.nc
netcdf all {
dimensions:
        lat = 360 ;
        lon = 720 ;
        time = 160708 ;
        nv = 2 ;
        points_terre = 62482 ;
variables:
        int crs ;

dlebauer

"# David LeBauer, Dan Wang, and Michael Dietze, 2010.  Biofuel Ecophysiological Traits and Yields Database Version 1.0.  Energy Biosciences Institute, Urbana, IL"
#
# Contact:,dlebauer@illinois.edu
#
# SQL query:,"SELECT ""traits_and_yields_view"".* FROM ""traits_and_yields_view"" WHERE (access_level >= 4) AND ((LOWER(traits_and_yields_view.scientificname) LIKE '%switchgrass%' OR LOWER(traits_and_yields_view.commonname) LIKE '%switchgrass%' OR LOWER(traits_and_yields_view.trait) LIKE '%switchgrass%' OR LOWER(traits_and_yields_view.trait_description) LIKE '%switchgrass%' OR LOWER(traits_and_yields_view.city) LIKE '%switchgrass%' OR LOWER(traits_and_yields_view.sitename) LIKE '%switchgrass%'))"
#
# Date of query:,2014-04-11 00:42:35 -0500
#
sitename,city,lat,lon,scientificname,commonname,genus,author,citation_year,treatment,date,month,year,dateloc,trait,mean,units,n,statname,stat,notes
Grassland Soil & Water Research Laboratory,Temple,31.05,-97.35,Panicum virgatum,switchgrass,Panicum,Kiniry,1999,Switch1,1995-06

dlebauer

ALTER TABLE "traits" ADD CONSTRAINT "fk_traits_species_1" FOREIGN KEY ("specie_id") REFERENCES "species" ("id");
ALTER TABLE "yields" ADD CONSTRAINT "fk_yields_species_1" FOREIGN KEY ("specie_id") REFERENCES "species" ("id");
ALTER TABLE "traits" ADD CONSTRAINT "fk_traits_sites_1" FOREIGN KEY ("site_id") REFERENCES "sites" ("id");
ALTER TABLE "yields" ADD CONSTRAINT "fk_yields_sites_1" FOREIGN KEY ("site_id") REFERENCES "sites" ("id");
ALTER TABLE "traits" ADD CONSTRAINT "fk_traits_citations_1" FOREIGN KEY ("citation_id") REFERENCES "citations" ("id");
ALTER TABLE "yields" ADD CONSTRAINT "fk_yields_citations_1" FOREIGN KEY ("citation_id") REFERENCES "citations" ("id");
ALTER TABLE "traits" ADD CONSTRAINT "fk_traits_cultivars_1" FOREIGN KEY ("cultivar_id") REFERENCES "cultivars" ("id");
ALTER TABLE "yields" ADD CONSTRAINT "fk_yields_cultivars_1" FOREIGN KEY ("cultivar_id") REFERENCES "cultivars" ("id");
ALTER TABLE "traits" ADD CONSTRAINT "fk_traits_treatments_1" FOREIGN KEY ("treatment_id") REFERENCES "treatm

dlebauer

CREATE TABLE "citations" (
"id" int4 NOT NULL DEFAULT nextval('citations_id_seq'::regclass),
"author" varchar(255) COLLATE "default" NOT NULL,
"year" int4 NOT NULL,
"title" varchar(255) COLLATE "default" NOT NULL,
"journal" varchar(255) COLLATE "default",
"vol" int4,
"pg" varchar(255) COLLATE "default",
"url" varchar(512) COLLATE "default",
"pdf" varchar(255) COLLATE "default",

dlebauer

update sites set geometry=ST_SetSRID(ST_MakePoint(44.28,  -96.68,  495), 4326), notes = 'elevation queried from lat lon using R geonames::GNstrm3', updated_at = now() where id=  12;
update sites set geometry=ST_SetSRID(ST_MakePoint(29.72,  -95.4,  22), 4326), notes = 'elevation queried from lat lon using R geonames::GNstrm3', updated_at = now() where id=  100;
update sites set geometry=ST_SetSRID(ST_MakePoint(47.17,  -122.17,  167), 4326), notes = 'elevation queried from lat lon using R geonames::GNstrm3', updated_at = now() where id=  156;
update sites set geometry=ST_SetSRID(ST_MakePoint(50.63,  -3.45,  3), 4326), notes = 'elevation queried from lat lon using R geonames::GNstrm3', updated_at = now() where id=  186;
update sites set geometry=ST_SetSRID(ST_MakePoint(52.42,  0.11,  -1), 4326), notes = 'experimental site; elevation queried from lat lon using R geonames::GNstrm3', updated_at = now() where id=  199;
update sites set geometry=ST_SetSRID(ST_MakePoint(52.79,  7.09,  13), 4326), notes = 'elevation qu

dlebauer

<?xml version="1.0"?>
<pecan>
  <outdir>/tmp/biocrotest/</outdir>
  <pfts>
    <pft>
      <name>salix</name>
    </pft>
  </pfts>
  <database>
    <bety>

dlebauer

Importing from production database
----------------------------------

```{bash}
mysql -u dlebauer -p"bety" ebi_production < ebi_productiondump.sql
```

```{sql}
delete from traits where user_id not = 15;
```

dlebauer

// Use Gists to store code you would like to remember later on
console.log(window); // log the "window" object to the console

dlebauer

/*
Navicat PGSQL Data Transfer

Source Server         : pecandev
Source Server Version : 90305
Source Host           : pecandev.igb.illinois.edu:5432
Source Database       : bety_constraints
Source Schema         : public

Target Server Type    : PGSQL