def add_spectrabin(freqs,dirs):
# determine whether bins exist in db
exists = False
session = DBman.startSession()
for rec in session.query(specbin):
dirshape = array(rec.spcdir).size==array(dirs).size
freqshape = array(rec.spcfreq).size==array(freqs).size
if (dirshape and freqshape):
dirval = all((array(rec.spcdir) - array(dirs)) < .01)
freqval = all((array(rec.spcfreq) - array(freqs)) < .01)
if (dirval and freqval):
exists = True
specid = rec.id
break
# if bins don't exist in db, add them
if (exists == False):
record = specbin(freqs,dirs)
session.add(record)
session.commit()
specid = record.id
session.close()
session.bind.dispose()
return specid
def makegrid(model_config):
west = model_config['west']
south = model_config['south']
east = model_config['east']
north = model_config['north']
nx = int(model_config['nx'])
ny = int(model_config['ny'])
projection = model_config['projection']
#build query
point_ll = 'ST_TRANSFORM(ST_SETSRID(ST_POINT('+west+','+south+'),4236),'+projection+')'
point_ur = 'ST_TRANSFORM(ST_SETSRID(ST_POINT('+east+','+north+'),4326),'+projection+')'
query = 'select ST_X('+point_ll+'),ST_Y('+point_ll+'),ST_X('+point_ur+'),ST_Y('+point_ur+')'
#execute query
session = DBman.startSession()
result = session.execute(query).fetchall()
west,south,east,north = result[0]
session.close()
session.bind.dispose()
#create meshgrid object
gridx = linspace(float(west),float(east),nx)
gridy = linspace(float(south),float(north),ny)
grid = meshgrid(gridx,gridy)
return grid
def makegrid(model_config):
west = model_config['west']
south = model_config['south']
east = model_config['east']
north = model_config['north']
nx = int(model_config['nx'])
ny = int(model_config['ny'])
projection = model_config['projection']
#build query
point_ll = 'ST_TRANSFORM(ST_SETSRID(ST_POINT(' + west + ',' + south + '),4236),' + projection + ')'
point_ur = 'ST_TRANSFORM(ST_SETSRID(ST_POINT(' + east + ',' + north + '),4326),' + projection + ')'
query = 'select ST_X(' + point_ll + '),ST_Y(' + point_ll + '),ST_X(' + point_ur + '),ST_Y(' + point_ur + ')'
#execute query
session = DBman.startSession()
result = session.execute(query).fetchall()
west, south, east, north = result[0]
session.close()
session.bind.dispose()
#create meshgrid object
gridx = linspace(float(west), float(east), nx)
gridy = linspace(float(south), float(north), ny)
grid = meshgrid(gridx, gridy)
return grid
def add_source(srctypeid,date):
# get the source type given an id
session = DBman.startSession()
srctypename = session.query(srctype)\
.filter( srctype.sourcetypeid == srctypeid )\
.first().sourcetypename
# choose srcname
for i in range(1000):
srcname = srctypename+'_'+date.strftime("%Y%m%d_%H")+'_'+str(i)
existing = session.query(src).filter( src.srcname == srcname )
if (existing.first() == None):
break
# prepare record for tblSource
record = src(
srcName=srcname,
srcConfig='',
srcBeginExecution=date.today(),
srcEndExecution=date.today(),
srcSourceTypeID=srctypeid)
# add record to tblSource
session.add(record)
session.commit()
srcid = record.id
session.close()
session.bind.dispose()
return srcid
def push_windata(windata,srcid):
session = DBman.startSession()
for date in windata.keys():
# parse dictionary
lats = windata[date]['lats']
lons = windata[date]['lons']
spd = windata[date]['speed']
dir = windata[date]['dir']
# add records to tblwind
for i in range(lats.shape[0]):
for j in range(lats.shape[1]):
loc = WKTSpatialElement('POINT('+str(lons[i][j])+' '+str(lats[i][j])+')')
record = wind(
winSourceID=srcid,
winLocation=loc,
winDateTime=date,
winSpeed=float(spd[i][j]),
winDirection=float(dir[i][j]))
session.add(record)
# commit and close session
session.commit()
session.close()
session.bind.dispose()
return
def push_wavdata(wavdata,srcid,specbinid):
session = DBman.startSession()
for loc in sort(wavdata.keys()):
for date in sort(wavdata[loc].keys()):
# parse dictionary
spectra = wavdata[loc][date]['spectra']
# add record to tblwave
record = wave(
wavSourceID=srcid,
wavSpectraBinID=specbinid,
wavLocation=loc,
wavDateTime=date,
wavSpectra=spectra,
wavHeight=None,
wavPeakDir=None,
wavPeakPeriod=None)
session.add(record)
# close session
session.commit()
session.close()
session.bind.dispose()
return
def getwinddata(box, steeringtimes, model_config):
#define constants
projection = model_config['projection']
starttime = steeringtimes[0]
stoptime = steeringtimes[len(steeringtimes) - 1]
starttime = starttime.strftime('%Y%m%d %H:00')
stoptime = stoptime.strftime('%Y%m%d %H:00')
starttime = '(TIMESTAMP \'' + starttime + '\')'
stoptime = '(TIMESTAMP \'' + stoptime + '\')'
#construct query
q1 = ' select winid from tblwind where'
q2 = ' windatetime>=' + starttime
q3 = ' and windatetime<=' + stoptime
subquery = q1 + q2 + q3
if (box != None):
q4 = ' and ST_WITHIN(ST_TRANSFORM( '
q5 = ' winlocation,' + projection + '),' + box + ')'
subquery = subquery + q4 + q5
q1 = ' select winspeed,windirection,windatetime,'
q2 = ' ST_X(ST_TRANSFORM(winlocation,' + projection + ')),'
q3 = ' ST_Y(ST_TRANSFORM(winlocation,' + projection + ')) '
q4 = ' from tblwind where winid in (' + subquery + ')'
query = q1 + q2 + q3 + q4
#execute query
session = DBman.startSession()
result = session.execute(query).fetchall()
if (len(result) == 0): return None
#parse results
wintime, winx, winy = [[], [], []]
winspeed, windir, winid = [[], [], []]
for k in range(len(result)):
winspeed.append(result[k][0])
windir.append(result[k][1])
wintime.append(result[k][2])
winx.append(result[k][3])
winy.append(result[k][4])
winspeed = array(winspeed)
windir = array(windir)
wintime = array(wintime)
winx = array(winx)
winy = array(winy)
#close session and return
session.close()
session.bind.dispose()
windata = {
'speed': winspeed,
'dir': windir,
'time': wintime,
'x': winx,
'y': winy
}
return windata
def getwinddata(box, steeringtimes, model_config):
#define constants
projection = model_config['projection']
starttime = steeringtimes[0]
stoptime = steeringtimes[len(steeringtimes)-1]
starttime = starttime.strftime('%Y%m%d %H:00' )
stoptime = stoptime.strftime('%Y%m%d %H:00' )
starttime = '(TIMESTAMP \''+starttime+'\')'
stoptime = '(TIMESTAMP \''+stoptime+'\')'
#construct query
q1 = ' select winid from tblwind where'
q2 = ' windatetime>='+starttime
q3 = ' and windatetime<='+stoptime
subquery = q1+q2+q3
if (box != None):
q4 = ' and ST_WITHIN(ST_TRANSFORM( '
q5 = ' winlocation,'+projection+'),'+box+')'
subquery = subquery+q4+q5
q1 = ' select winspeed,windirection,windatetime,'
q2 = ' ST_X(ST_TRANSFORM(winlocation,'+projection+')),'
q3 = ' ST_Y(ST_TRANSFORM(winlocation,'+projection+')) '
q4 = ' from tblwind where winid in ('+subquery+')'
query = q1+q2+q3+q4
#execute query
session = DBman.startSession()
result = session.execute(query).fetchall()
if (len(result)==0): return None
#parse results
wintime, winx, winy = [[],[],[]]
winspeed, windir, winid = [[],[],[]]
for k in range(len(result)):
winspeed.append(result[k][0])
windir.append(result[k][1])
wintime.append(result[k][2])
winx.append(result[k][3])
winy.append(result[k][4])
winspeed=array(winspeed)
windir=array(windir)
wintime=array(wintime)
winx=array(winx)
winy=array(winy)
#close session and return
session.close()
session.bind.dispose()
windata = {
'speed':winspeed,'dir':windir,
'time':wintime,'x':winx,'y':winy }
return windata
def add_sourcetype(srctypename):
#check if sourcetype exists
session = DBman.startSession()
existing = session.query(srctype)\
.filter( srctype.sourcetypename == srctypename )
if ( existing.first() == None ):
#if doesn't exist, add new sourcetype to db
record = srctype(srctypename)
session.add(record)
session.commit()
srctypeid = record.id
else:
#if exists, get id for existing sourcetype
srctypeid = session.query(srctype)\
.filter( srctype.sourcetypename == srctypename )\
.first().id
session.close()
session.bind.dispose()
return srctypeid
def getwavedata(box, steeringtimes, model_config):
#define constants
projection = model_config['projection']
starttime = steeringtimes[0]
stoptime = steeringtimes[len(steeringtimes)-1]
starttime = starttime.strftime('%Y%m%d %H:00' )
stoptime = stoptime.strftime('%Y%m%d %H:00' )
starttime = '(TIMESTAMP \''+starttime+'\')'
stoptime = '(TIMESTAMP \''+stoptime+'\')'
#construct query
q1 = ' select wavid from tblwave '
q2 = ' where ST_WITHIN(ST_TRANSFORM( '
q3 = ' wavlocation,'+projection+'),'+box+')'
q4 = ' and wavdatetime>='+starttime
q5 = ' and wavdatetime<='+stoptime
subquery = q1+q2+q3+q4+q5
q1 = ' select wavspectra,wavspectrabinid,'
q2 = ' wavdatetime,wavlocation,'
q3 = ' ST_X(ST_TRANSFORM(wavlocation,'+projection+')),'
q4 = ' ST_Y(ST_TRANSFORM(wavlocation,'+projection+')) '
q5 = ' from tblwave where wavid in ('+subquery+')'
query = q1+q2+q3+q4+q5
#execute query
session = DBman.startSession()
result = session.execute(query).fetchall()
if (len(result)==0): return None
#parse result of query
spec,specid,wavtime = [[],[],[]]
wavloc,wavx,wavy = [[],[],[]]
for k in range(len(result)):
spec.append(result[k][0])
specid.append(result[k][1])
wavtime.append(result[k][2])
wavloc.append(result[k][3])
wavx.append(result[k][4])
wavy.append(result[k][5])
spec=array(spec)
specid=array(specid)
wavtime=array(wavtime)
wavloc=array(wavloc)
wavx=array(wavx)
wavy=array(wavy)
#retreive freq/dir bins
freq,dir = [[],[]]
for myspectraid in specid:
q1 = "select spcfreq,spcdir from "
q2 = "tblspectrabin where spcid='"+myspectraid+"'"
query = q1+q2
result = session.execute(query).fetchall()
freq.append(result[0][0])
dir.append(result[0][1])
freq=array(freq)
dir=array(dir)
#close session and return
session.close()
session.bind.dispose()
wavdata = {
'spec':spec,'time':wavtime,'x':wavx,
'y':wavy,'freq':freq,'dir':dir,'loc':wavloc}
return wavdata
def getwavedata(box, steeringtimes, model_config):
#define constants
projection = model_config['projection']
starttime = steeringtimes[0]
stoptime = steeringtimes[len(steeringtimes) - 1]
starttime = starttime.strftime('%Y%m%d %H:00')
stoptime = stoptime.strftime('%Y%m%d %H:00')
starttime = '(TIMESTAMP \'' + starttime + '\')'
stoptime = '(TIMESTAMP \'' + stoptime + '\')'
#construct query
q1 = ' select wavid from tblwave '
q2 = ' where ST_WITHIN(ST_TRANSFORM( '
q3 = ' wavlocation,' + projection + '),' + box + ')'
q4 = ' and wavdatetime>=' + starttime
q5 = ' and wavdatetime<=' + stoptime
subquery = q1 + q2 + q3 + q4 + q5
q1 = ' select wavspectra,wavspectrabinid,'
q2 = ' wavdatetime,wavlocation,'
q3 = ' ST_X(ST_TRANSFORM(wavlocation,' + projection + ')),'
q4 = ' ST_Y(ST_TRANSFORM(wavlocation,' + projection + ')) '
q5 = ' from tblwave where wavid in (' + subquery + ')'
query = q1 + q2 + q3 + q4 + q5
#execute query
session = DBman.startSession()
result = session.execute(query).fetchall()
if (len(result) == 0): return None
#parse result of query
spec, specid, wavtime = [[], [], []]
wavloc, wavx, wavy = [[], [], []]
for k in range(len(result)):
spec.append(result[k][0])
specid.append(result[k][1])
wavtime.append(result[k][2])
wavloc.append(result[k][3])
wavx.append(result[k][4])
wavy.append(result[k][5])
spec = array(spec)
specid = array(specid)
wavtime = array(wavtime)
wavloc = array(wavloc)
wavx = array(wavx)
wavy = array(wavy)
#retreive freq/dir bins
freq, dir = [[], []]
for myspectraid in specid:
q1 = "select spcfreq,spcdir from "
q2 = "tblspectrabin where spcid='" + myspectraid + "'"
query = q1 + q2
result = session.execute(query).fetchall()
freq.append(result[0][0])
dir.append(result[0][1])
freq = array(freq)
dir = array(dir)
#close session and return
session.close()
session.bind.dispose()
wavdata = {
'spec': spec,
'time': wavtime,
'x': wavx,
'y': wavy,
'freq': freq,
'dir': dir,
'loc': wavloc
}
return wavdata
import os,sys,re #argument parsing
import glob #file wildcards
from numpy import * #math support
from math import * #pi constant
from netCDF4 import * #netcdf support
from geoalchemy import WKTSpatialElement
from config import CMSconfig
import DBman,CMSman
strptime = datetime.datetime.strptime
import tempfile
################################
# CREATE DATABASE OBJECTS
################################
srctype = DBman.accessTable( None, 'tblsourcetype' )
src = DBman.accessTable( None, 'tblsource')
wind = DBman.accessTable( None, 'tblwind' )
wave = DBman.accessTable( None, 'tblwave' )
specbin = DBman.accessTable( None, 'tblspectrabin' )
################################
# ADD RECORD TO TBLSOURCETYPE IF NECESSARY
################################
def add_sourcetype(srctypename):
#check if sourcetype exists
session = DBman.startSession()
existing = session.query(srctype)\
.filter( srctype.sourcetypename == srctypename )
if ( existing.first() == None ):
