Notebook

Combined DFO_OPDB and PRISM databases¶

add N, Si, and ancillary data from DFO and from PRISM
check for duplicate entries

In [1]:

# imports
import sqlalchemy
from sqlalchemy import create_engine, Column, String, Integer, Numeric, MetaData, Table, type_coerce, ForeignKey, case
from sqlalchemy.orm import mapper, create_session, relationship, aliased, Session
from sqlalchemy.ext.declarative import declarative_base
import csv
from sqlalchemy import case
import numpy as np
from sqlalchemy.ext.automap import automap_base
import matplotlib.pyplot as plt
import sqlalchemy.types as types
import numbers
from sqlalchemy.sql import and_, or_, not_, func
from sqlalchemy.sql import select
import os
import glob
import re
from os.path import isfile
from mpl_toolkits.basemap import Basemap, shiftgrid, cm
import gsw
import matplotlib.cm as cmm
import matplotlib.colors as col
import matplotlib.colors as col
import createDBfromDFO_OPDB
from netCDF4 import Dataset
import datetime as dt
import dateutil as dutil
%matplotlib inline

In [2]:

# definitions
basepath='/ocean/eolson/MEOPAR/obs/'
dbnameOPDB='DFOOPDB/DFO_OcProfDB'
dbnamePRISM='NANOOS_PRISMCRUISES/PRISM'
dbnameComb='combinedForICs'

First, create database to hold combined data:¶

In [3]:

engine = create_engine('sqlite:///' + basepath + dbnameComb + '.sqlite')
Base=declarative_base()

class forceNumeric(types.TypeDecorator):
    impl = types.Numeric
    def process_bind_param(self, value, dialect):
        try:
            int(float(value))
        except:
            value = None
        return value

class forceInt(types.TypeDecorator):
    impl = types.Integer
    def process_bind_param(self, value, dialect):
        try:
            int(value)
        except:
            value = None
        return value

In [4]:

# define Table Classes:
class StationTBL(Base):
    __table__=Table('StationTBL', Base.metadata,
                Column('ID', Integer, primary_key=True),
                Column('Lat', forceNumeric),
                Column('Lon', forceNumeric),
                Column('Year', forceInt),
                Column('Month', forceInt),
                Column('Day', forceInt),
                Column('sourceStaID', forceNumeric),
                Column('sourceDB', String),
                Column('sourceStaName', String))

class ObsTBL(Base):
    __table__=Table('ObsTBL', Base.metadata,
                    Column('ID', Integer, primary_key=True),
                    Column('StationTBLID', forceInt, ForeignKey('StationTBL.ID')),
                    Column('sourceObsID', Integer),
                    Column('sourceStaID', Integer),
                    Column('sourceDB', String),
                    Column('Z', forceNumeric),
                    Column('Press', forceNumeric),
                    Column('T', forceNumeric),
                    Column('gswTC', forceNumeric),
                    Column('S', forceNumeric),
                    Column('gswSA', forceNumeric),
                    Column('gswRho', forceNumeric),
                    Column('NO3', forceNumeric),
                    Column('NH4', forceNumeric),
                    Column('Si', forceNumeric),
                    Column('PON', forceNumeric),
                    Column('DON', forceNumeric),
                    Column('bSi', forceNumeric))
    station=relationship(StationTBL, primaryjoin=__table__.c.StationTBLID == StationTBL.ID)

In [5]:

Base.metadata.create_all(engine)
session = create_session(bind = engine, autocommit = False, autoflush = True)

Next, open DFO_OPDB database and extract data:¶

In [6]:

Base1 = automap_base()
engine1 = create_engine('sqlite:///' + basepath + dbnameOPDB + '.sqlite', echo = False)
# reflect the tables:
Base1.prepare(engine1, reflect=True)
Sta1=Base1.classes.StationTBL
Obs1=Base1.classes.ObsTBL
session1 = create_session(bind = engine1, autocommit = False, autoflush = True)

definitions:¶

In [7]:

Press1=case([(Obs1.Pressure!=None, Obs1.Pressure)], else_=Obs1.Pressure_Reversing)

In [8]:

Tem1=case([(Obs1.Temperature!=None, Obs1.Temperature)], else_=
         case([(Obs1.Temperature_Primary!=None, Obs1.Temperature_Primary)], else_=
         case([(Obs1.Temperature_Secondary!=None, Obs1.Temperature_Secondary)], else_=Obs1.Temperature_Reversing)))

In [9]:

Sal1=case([(Obs1.Salinity_Bottle!=None, Obs1.Salinity_Bottle)], else_=
         case([(Obs1.Salinity_T0_C0!=None, Obs1.Salinity_T0_C0)], else_=
         case([(Obs1.Salinity_T1_C1!=None, Obs1.Salinity_T1_C1)], else_=
         case([(Obs1.Salinity!=None, Obs1.Salinity)], else_=
         case([(Obs1.Salinity__Unknown!=None, Obs1.Salinity__Unknown)], else_=Obs1.Salinity__Pre1978)
        ))))
Sal1Flag=case([(Obs1.Salinity_Bottle!=None, Obs1.Flag_Salinity_Bottle)], else_=
         case([(Obs1.Salinity_T0_C0!=None, Obs1.Flag_Salinity)], else_=
         case([(Obs1.Salinity_T1_C1!=None, Obs1.Flag_Salinity)], else_=
         case([(Obs1.Salinity!=None, Obs1.Flag_Salinity)], else_=
         case([(Obs1.Salinity__Unknown!=None, Obs1.Flag_Salinity)], else_=Obs1.Quality_Flag_Sali)
        ))))
Sal1_noflag=case([(Sal1Flag>2, None)], else_=Sal1)

In [10]:

NO1=case([(Obs1.Nitrate_plus_Nitrite!=None, Obs1.Nitrate_plus_Nitrite)], else_=Obs1.Nitrate)
NO1Flag=case([(Obs1.Nitrate_plus_Nitrite!=None, Obs1.Flag_Nitrate_plus_Nitrite)], else_=Obs1.Flag_Nitrate)
# Obs.Quality_Flag_Nitr does not match any nitrate obs
# ISUS not included in this NO
# units are micromolar (muM)
NO1_noflag=case([(NO1Flag>2, None)], else_=NO1)

In [11]:

NH1_noflag=case([(Obs1.Flag_Ammonium>2, None)], else_=Obs1.Ammonium)

In [12]:

Si1_noflag=case([(Obs1.Flag_Silicate>2, None)], else_=Obs1.Silicate)

In [13]:

DON_noflag=case([(Obs1.Flag_Nitrogen_Dissolved_Organic>2, None)], else_=Obs1.Nitrogen_Dissolved_Organic)

In [14]:

PON_noflag=Obs1.Nitrogen_Particulate_Organic

do not include data with quality flag 3 or greater. from 2011-27-0020.che: Flag channels initialized with zeros. Non-zero values have the following significance: -------------------------------------------------------------------------------------- 1 = Sample for this measurement was drawn from water bottle but not analyzed (not normally used). 2 = Acceptable measurement (not normally used). 3 = Questionable measurement (no problem observed in sampling or analysis, but value is not trusted, nonetheless; includes outlyers). 4 = Bad measurement (known problem with sampling or analysis, but not serious enough to completely discard the value). 5 = Not reported (lost sample; unredeemably bad measurement). 6 = Mean of replicate measurements. 7 = Manual chromatographic peak measurement. 8 = Irregular digital chromatographic peak integration. 9 = Sample not drawn for this measurement from this bottle (not normally used).

start with OBS table:¶

In [15]:

qry1_Obs=session1.query(Obs1.ID, Obs1.StationTBLID, Obs1.Depth, Press1, Tem1, Sal1_noflag, NO1_noflag, NH1_noflag,\
                       Si1_noflag, DON_noflag, PON_noflag, Sta1.Lon, Sta1.Lat).select_from(Obs1).\
                join(Sta1,Sta1.ID==Obs1.StationTBLID).filter(or_(
                    NO1_noflag!=None,
                    NH1_noflag!=None,
                    Si1_noflag!=None,
                    DON_noflag!=None,
                    PON_noflag!=None))

In [16]:

jj=0
for iSourceID, iSourceStaID, iZ, iP, iT, iS, iNO, iNH, iSi, iDON, iPON, iLon, iLat in qry1_Obs.all():
    jj+=1
    idict={}
    idict['sourceObsID']=iSourceID
    idict['sourceStaID']=iSourceStaID
    idict['sourceDB']='DFO_OPDB'
    idict['Z']=iZ
    idict['Press']=iP
    idict['T']=iT
    idict['S']=iS
    idict['NO3']=iNO
    idict['NH4']=iNH
    idict['Si']=iSi
    idict['PON']=iPON
    idict['DON']=iDON
    if iS!=None and iP!=None and iLon!=None and iLat!=None:
        iSA=gsw.SA_from_SP(iS,iP,iLon,iLat)
        idict['gswSA']=iSA
        if iT!=None:
            idict['gswRho']=gsw.rho(iSA,iT,iP)
            idict['gswTC']=gsw.CT_from_t(iSA,iT,iP)
    # enter in new db Obs table:
    session.execute(ObsTBL.__table__.insert().values(**idict))
    if jj%5000==1:
        print(idict)

{'sourceStaID': 1, 'sourceObsID': 1, 'DON': None, 'sourceDB': 'DFO_OPDB', 'NH4': None, 'T': 14.3, 'NO3': 6.6, 'Si': 13.6, 'PON': None, 'Z': 12.0, 'S': 25.8, 'Press': None}
{'sourceStaID': 597, 'sourceObsID': 6213, 'gswTC': 9.9774455341009798, 'DON': None, 'sourceDB': 'DFO_OPDB', 'NH4': None, 'T': 9.886, 'NO3': None, 'gswRho': 1022.5666440387499, 'gswSA': 29.289740448372932, 'Si': 41.4, 'PON': None, 'Z': None, 'S': 29.1496, 'Press': 31.1}
{'sourceStaID': 1153, 'sourceObsID': 12689, 'gswTC': 7.1981975413649293, 'DON': None, 'sourceDB': 'DFO_OPDB', 'NH4': None, 'T': 7.1133, 'NO3': None, 'gswRho': 1022.1896305004838, 'gswSA': 28.46321542073542, 'Si': 56.9, 'PON': None, 'Z': None, 'S': 28.3262, 'Press': 5.0}
{'sourceStaID': 1769, 'sourceObsID': 19106, 'gswTC': 9.8709211683004732, 'DON': None, 'sourceDB': 'DFO_OPDB', 'NH4': None, 'T': 9.7996, 'NO3': None, 'gswRho': 1023.4505149892334, 'gswSA': 30.536476111886568, 'Si': 44.29, 'PON': None, 'Z': None, 'S': 30.3899, 'Press': 10.4}
{'sourceStaID': 2277, 'sourceObsID': 25023, 'gswTC': 6.7618013474984435, 'DON': None, 'sourceDB': 'DFO_OPDB', 'NH4': None, 'T': 6.7621, 'NO3': 23.1, 'gswRho': 1026.9513812707041, 'gswSA': 33.637181076694901, 'Si': 29.8, 'PON': None, 'Z': None, 'S': 33.4743, 'Press': 151.7}
{'sourceStaID': 2686, 'sourceObsID': 30194, 'DON': None, 'sourceDB': 'DFO_OPDB', 'NH4': None, 'T': 5.4678, 'NO3': None, 'Si': 72.1, 'PON': None, 'Z': None, 'S': None, 'Press': 210.3}
{'sourceStaID': 3136, 'sourceObsID': 35650, 'gswTC': 8.7800011399187969, 'DON': None, 'sourceDB': 'DFO_OPDB', 'NH4': None, 'T': 8.7346, 'NO3': None, 'gswRho': 1024.5671596935504, 'gswSA': 31.707012106529575, 'Si': 8.8, 'PON': None, 'Z': None, 'S': 31.5554, 'Press': 20.5}
{'sourceStaID': 3632, 'sourceObsID': 41276, 'DON': None, 'sourceDB': 'DFO_OPDB', 'NH4': None, 'T': 7.8282, 'NO3': None, 'Si': 41.8, 'PON': None, 'Z': None, 'S': None, 'Press': 76.0}
{'sourceStaID': 4202, 'sourceObsID': 46940, 'DON': None, 'sourceDB': 'DFO_OPDB', 'NH4': None, 'T': 6.7, 'NO3': None, 'Si': 563.0, 'PON': None, 'Z': 100.0, 'S': 33.84, 'Press': None}
{'sourceStaID': 5066, 'sourceObsID': 53282, 'DON': None, 'sourceDB': 'DFO_OPDB', 'NH4': None, 'T': 13.36, 'NO3': 0.0, 'Si': 5.9, 'PON': None, 'Z': 9.8, 'S': 31.55, 'Press': None}
{'sourceStaID': 5632, 'sourceObsID': 58457, 'gswTC': 9.2553406792164381, 'DON': None, 'sourceDB': 'DFO_OPDB', 'NH4': None, 'T': 9.2229, 'NO3': 28.5, 'gswRho': 1025.0951124643843, 'gswSA': 30.932667688775656, 'Si': 67.9, 'PON': None, 'Z': None, 'S': 30.7798, 'Press': 285.4}
{'sourceStaID': 6981, 'sourceObsID': 72307, 'DON': None, 'sourceDB': 'DFO_OPDB', 'NH4': None, 'T': 11.05, 'NO3': None, 'Si': 30.0, 'PON': None, 'Z': 50.0, 'S': None, 'Press': None}
{'sourceStaID': 8138, 'sourceObsID': 84121, 'DON': None, 'sourceDB': 'DFO_OPDB', 'NH4': None, 'T': 9.55, 'NO3': 17.1, 'Si': 36.5, 'PON': None, 'Z': 20.0, 'S': 29.253, 'Press': None}

add data to StationTBL¶

In [17]:

qry1_Sta=session1.query(Sta1.ID, Sta1.Lat, Sta1.Lon, Sta1.StartYear, Sta1.StartMonth, Sta1.StartDay,\
                        Sta1.STATION).select_from(Obs1).\
                join(Sta1,Sta1.ID==Obs1.StationTBLID).filter(or_(
                    NO1_noflag!=None,
                    NH1_noflag!=None,
                    Si1_noflag!=None,
                    DON_noflag!=None,
                    PON_noflag!=None)).group_by(Sta1.ID)

In [18]:

jj=0
for iID, iLat, iLon, iYr, iMo, iDy, iSN in qry1_Sta.all():
    jj+=1
    idict={}
    idict['Lat']=iLat
    idict['Lon']=iLon
    idict['Year']=iYr
    idict['Month']=iMo
    idict['Day']=iDy
    idict['sourceStaID']=iID
    idict['sourceDB']='DFO_OPDB'
    idict['sourceStaName']=iSN
    session.execute(StationTBL.__table__.insert().values(**idict))
    if jj%1000==1:
        print(idict)

{'sourceStaName': '26', 'sourceStaID': 1, 'Year': 1982.0, 'Day': 16.0, 'sourceDB': 'DFO_OPDB', 'Lon': -123.943, 'Lat': 49.25, 'Month': 7.0}
{'sourceStaName': '65', 'sourceStaID': 1047, 'Year': 2012.0, 'Day': 7.0, 'sourceDB': 'DFO_OPDB', 'Lon': -123.16483333333333, 'Lat': 48.26433333333333, 'Month': 4.0}
{'sourceStaName': 'P04', 'sourceStaID': 2077, 'Year': 1998.0, 'Day': 14.0, 'sourceDB': 'DFO_OPDB', 'Lon': -126.6695, 'Lat': 48.64516666666667, 'Month': 5.0}
{'sourceStaName': 'LB14', 'sourceStaID': 3079, 'Year': 2004.0, 'Day': 10.0, 'sourceDB': 'DFO_OPDB', 'Lon': -125.9995, 'Lat': 48.141333333333336, 'Month': 9.0}
{'sourceStaName': 'G', 'sourceStaID': 4099, 'Year': 1932.0, 'Day': 22.0, 'sourceDB': 'DFO_OPDB', 'Lon': -122.7, 'Lat': 48.666666666666664, 'Month': 7.0}
{'sourceStaName': '043', 'sourceStaID': 5132, 'Year': 1986.0, 'Day': 22.0, 'sourceDB': 'DFO_OPDB', 'Lon': -127.387, 'Lat': 49.272, 'Month': 8.0}
{'sourceStaName': None, 'sourceStaID': 7368, 'Year': 1953.0, 'Day': 20.0, 'sourceDB': 'DFO_OPDB', 'Lon': -122.86666666666666, 'Lat': 47.65, 'Month': 7.0}

update ObsTBL to have correct foreign keys based on DFO_OPDB relationships¶

In [19]:

jj=0
for iObs in session.query(ObsTBL).all():
    jj+=1
    if jj%5000==1:
        print(iObs.ID,iObs.StationTBLID, iObs.sourceStaID)
    iObs.StationTBLID=session.query(StationTBL.ID).filter(StationTBL.sourceStaID==iObs.sourceStaID).one()[0]
    if jj%5000==1:
        print(iObs.ID,iObs.StationTBLID, iObs.sourceStaID)

1 None 1
1 1 1
5001 None 597
5001 578 597
10001 None 1153
10001 1107 1153
15001 None 1769
15001 1714 1769
20001 None 2277
20001 2201 2277
25001 None 2686
25001 2609 2686
30001 None 3136
30001 3058 3136
35001 None 3632
35001 3549 3632
40001 None 4202
40001 4103 4202
45001 None 5066
45001 4935 5066
50001 None 5632
50001 5501 5632
55001 None 6981
55001 5964 6981
60001 None 8138
60001 6397 8138

/home/eolson/anaconda3/envs/teos10/lib/python3.4/site-packages/sqlalchemy/sql/sqltypes.py:562: SAWarning: Dialect sqlite+pysqlite does *not* support Decimal objects natively, and SQLAlchemy must convert from floating point - rounding errors and other issues may occur. Please consider storing Decimal numbers as strings or integers on this platform for lossless storage.
  'storage.' % (dialect.name, dialect.driver))

In [20]:

session.commit()
session1.close()
engine1.dispose()

Next, load PRISM data:¶

In [21]:

Base2 = automap_base()
engine2 = create_engine('sqlite:///' + basepath + dbnamePRISM + '.sqlite', echo = False)
# reflect the tables:
Base2.prepare(engine2, reflect=True)
Obs2=Base2.classes.ObsTBL
Cast2=Base2.classes.CastTBL
session2 = create_session(bind = engine2, autocommit = False, autoflush = True)

In [22]:

qry2_0=session2.query(Obs2.id, Obs2.cast_dbid, Obs2.depth,Obs2.cast_lat.label('lat'),Obs2.cast_lon.label('lon')).filter(or_(
                            Obs2.variable=='ammonium_concentration',
                            Obs2.variable=='nitrate_concentration',
                            Obs2.variable=='silicate_concentration')).subquery()
qry2_ObsBase=session2.query(qry2_0.c.id, qry2_0.c.cast_dbid, qry2_0.c.depth, qry2_0.c.lat, qry2_0.c.lon).group_by(qry2_0.c.cast_dbid, qry2_0.c.depth).subquery()

In [23]:

test=session2.query(qry2_ObsBase.c.id,qry2_ObsBase.c.cast_dbid,qry2_ObsBase.c.depth, qry2_ObsBase.c.lat, qry2_ObsBase.c.lon).limit(5).all()
for row in test:
    print(row)

(920742, 1160, Decimal('2.8000000000'), Decimal('48.0163333333'), Decimal('-122.3041666670'))
(920757, 1160, Decimal('7.9000000000'), Decimal('48.0163333333'), Decimal('-122.3041666670'))
(920772, 1160, Decimal('13.0000000000'), Decimal('48.0163333333'), Decimal('-122.3041666670'))
(920787, 1160, Decimal('23.0000000000'), Decimal('48.0163333333'), Decimal('-122.3041666670'))
(920802, 1160, Decimal('33.0000000000'), Decimal('48.0163333333'), Decimal('-122.3041666670'))

/home/eolson/anaconda3/envs/teos10/lib/python3.4/site-packages/sqlalchemy/sql/sqltypes.py:562: SAWarning: Dialect sqlite+pysqlite does *not* support Decimal objects natively, and SQLAlchemy must convert from floating point - rounding errors and other issues may occur. Please consider storing Decimal numbers as strings or integers on this platform for lossless storage.
  'storage.' % (dialect.name, dialect.driver))

some variables have multiple entires at a depth and station; take the average:¶

In [24]:

qNO=session2.query(Obs2.cast_dbid,Obs2.depth,func.avg(Obs2.value).label('value')).filter(Obs2.variable=='nitrate_concentration').group_by(Obs2.cast_dbid,Obs2.depth).subquery()
qNH=session2.query(Obs2.cast_dbid,Obs2.depth,func.avg(Obs2.value).label('value')).filter(Obs2.variable=='ammonium_concentration').group_by(Obs2.cast_dbid,Obs2.depth).subquery()
qSi=session2.query(Obs2.cast_dbid,Obs2.depth,func.avg(Obs2.value).label('value')).filter(Obs2.variable=='silicate_concentration').group_by(Obs2.cast_dbid,Obs2.depth).subquery()
qP=session2.query(Obs2.cast_dbid,Obs2.depth,func.avg(Obs2.value).label('value')).filter(Obs2.variable=='water_pressure').group_by(Obs2.cast_dbid,Obs2.depth).subquery()
qT=session2.query(Obs2.cast_dbid,Obs2.depth,func.avg(Obs2.value).label('value')).filter(Obs2.variable=='water_temperature').group_by(Obs2.cast_dbid,Obs2.depth).subquery()
qS=session2.query(Obs2.cast_dbid,Obs2.depth,func.avg(Obs2.value).label('value')).filter(Obs2.variable=='water_salinity').group_by(Obs2.cast_dbid,Obs2.depth).subquery()

In [25]:

qry2_all=session2.query(qry2_ObsBase.c.id, qry2_ObsBase.c.lat, qry2_ObsBase.c.lon, qry2_ObsBase.c.cast_dbid, qry2_ObsBase.c.depth,
                       qNO.c.value, qNH.c.value, qSi.c.value, qP.c.value, qT.c.value, qS.c.value).\
    outerjoin(qNO, and_(qNO.c.cast_dbid==qry2_ObsBase.c.cast_dbid, qNO.c.depth==qry2_ObsBase.c.depth)).\
    outerjoin(qNH, and_(qNH.c.cast_dbid==qry2_ObsBase.c.cast_dbid, qNH.c.depth==qry2_ObsBase.c.depth)).\
    outerjoin(qSi, and_(qSi.c.cast_dbid==qry2_ObsBase.c.cast_dbid, qSi.c.depth==qry2_ObsBase.c.depth)).\
    outerjoin(qP, and_(qP.c.cast_dbid==qry2_ObsBase.c.cast_dbid, qP.c.depth==qry2_ObsBase.c.depth)).\
    outerjoin(qT, and_(qT.c.cast_dbid==qry2_ObsBase.c.cast_dbid, qT.c.depth==qry2_ObsBase.c.depth)).\
    outerjoin(qS, and_(qS.c.cast_dbid==qry2_ObsBase.c.cast_dbid, qS.c.depth==qry2_ObsBase.c.depth))

In [26]:

test=qry2_all.count()
print(test)

In [27]:

test2=session2.query(qry2_ObsBase).count()
print(test2)

In [28]:

jj=0
for iID, iLat, iLon, iCastID, iZ, iNO, iNH, iSi, iP, iT, iS in qry2_all.all():
    jj+=1
    idict={}
    idict['sourceObsID']=iID
    idict['sourceStaID']=iCastID
    idict['sourceDB']='PRISM'
    idict['Z']=iZ
    idict['T']=iT
    idict['S']=iS
    idict['NO3']=iNO
    idict['NH4']=iNH
    idict['Si']=iSi
    idict['Press']=iP
    if iS!=None and iP!=None and iLon!=None and iLat!=None:
        iSA=gsw.SA_from_SP(iS,iP,iLon,iLat)
        idict['gswSA']=iSA
        if iT!=None:
            idict['gswRho']=gsw.rho(iSA,iT,iP)
            idict['gswTC']=gsw.CT_from_t(iSA,iT,iP)
    # enter in new db Obs table:
    session.execute(ObsTBL.__table__.insert().values(**idict))
    if jj%5000==1:
        print(idict)

{'sourceStaID': 1160, 'sourceObsID': 920742, 'gswTC': 14.659235764855625, 'sourceDB': 'PRISM', 'NH4': 0.159997469161, 'T': 14.416, 'gswRho': 1017.8875549349336, 'gswSA': 24.374606843954123, 'Si': 23.499628283, 'NO3': 1.49997627338, 'Z': Decimal('2.8000000000'), 'S': 24.257600000000004, 'Press': 2.8}
{'sourceStaID': 1742, 'sourceObsID': 2271510, 'gswTC': 8.992970261288594, 'sourceDB': 'PRISM', 'NH4': 0.127033485043, 'T': 8.9436, 'gswRho': 1024.3648794085707, 'gswSA': 30.990698588268391, 'Si': 53.6765927411, 'NO3': 29.1039631806, 'Z': Decimal('104.1490000000'), 'S': 30.8415, 'Press': 105.056}

In [29]:

qry2_Cast=session2.query(Cast2.cast_dbid, Cast2.cast_datetime, Cast2.cast_lat, Cast2.cast_lon,
                        Cast2.station_name).join(qry2_ObsBase, qry2_ObsBase.c.cast_dbid==Cast2.cast_dbid).\
                group_by(Cast2.cast_dbid)

In [30]:

print(qry2_Cast.count())

In [31]:

qtest=session2.query(Cast2).count()
print(qtest)

In [32]:

jj=0
for iID, iDT, iLat, iLon, iSN in qry2_Cast.all():
    jj+=1
    idict={}
    idate=dutil.parser.parse(iDT)
    idict['Lat']=iLat
    idict['Lon']=iLon
    idict['Year']=idate.year
    idict['Month']=idate.month
    idict['Day']=idate.day
    idict['sourceStaID']=iID
    idict['sourceDB']='PRISM'
    idict['sourceStaName']=iSN
    session.execute(StationTBL.__table__.insert().values(**idict))
    if jj%100==1:
        print(idict)

{'sourceStaName': 'P1, Gedney Island', 'sourceStaID': 1160, 'Year': 1998, 'Day': 15, 'sourceDB': 'PRISM', 'Lon': Decimal('-122.3041666670'), 'Lat': Decimal('48.0163333333'), 'Month': 6}
{'sourceStaName': 'P30, Three Tree Point', 'sourceStaID': 1260, 'Year': 1999, 'Day': 15, 'sourceDB': 'PRISM', 'Lon': Decimal('-122.4091666670'), 'Lat': Decimal('47.4563333333'), 'Month': 6}
{'sourceStaName': 'P16, Hazel Point', 'sourceStaID': 1363, 'Year': 2000, 'Day': 14, 'sourceDB': 'PRISM', 'Lon': Decimal('-122.7608333330'), 'Lat': Decimal('47.6913333333'), 'Month': 6}
{'sourceStaName': 'P12, Hoodsport', 'sourceStaID': 1464, 'Year': 2001, 'Day': 11, 'sourceDB': 'PRISM', 'Lon': Decimal('-123.1080000000'), 'Lat': Decimal('47.4250000000'), 'Month': 6}
{'sourceStaName': 'P9, South Point', 'sourceStaID': 1564, 'Year': 2003, 'Day': 14, 'sourceDB': 'PRISM', 'Lon': Decimal('-122.6665000000'), 'Lat': Decimal('47.8321666667'), 'Month': 6}
{'sourceStaName': 'P35, South of McNeil Island', 'sourceStaID': 1675, 'Year': 2004, 'Day': 5, 'sourceDB': 'PRISM', 'Lon': Decimal('-122.6316666670'), 'Lat': Decimal('47.1833333333'), 'Month': 12}
{'sourceStaName': 'P18, Bush Point', 'sourceStaID': 1775, 'Year': 2006, 'Day': 28, 'sourceDB': 'PRISM', 'Lon': Decimal('-122.6156666670'), 'Lat': Decimal('48.0330000000'), 'Month': 6}
{'sourceStaName': 'P19, Lagoon Point', 'sourceStaID': 1878, 'Year': 2008, 'Day': 5, 'sourceDB': 'PRISM', 'Lon': Decimal('-122.6298333330'), 'Lat': Decimal('48.0935000000'), 'Month': 2}
{'sourceStaName': 'P13, Eldon', 'sourceStaID': 9185, 'Year': 2009, 'Day': 1, 'sourceDB': 'PRISM', 'Lon': Decimal('-123.0073333330'), 'Lat': Decimal('47.5473333333'), 'Month': 10}

/home/eolson/anaconda3/envs/teos10/lib/python3.4/site-packages/sqlalchemy/sql/sqltypes.py:562: SAWarning: Dialect sqlite+pysqlite does *not* support Decimal objects natively, and SQLAlchemy must convert from floating point - rounding errors and other issues may occur. Please consider storing Decimal numbers as strings or integers on this platform for lossless storage.
  'storage.' % (dialect.name, dialect.driver))

In [33]:

jj=0
for iObs in session.query(ObsTBL).filter(ObsTBL.sourceDB=='PRISM').all():
    jj+=1
    if jj%1000==1:
        print(iObs.ID,iObs.StationTBLID, iObs.sourceStaID)
    iObs.StationTBLID=session.query(StationTBL.ID).filter(StationTBL.sourceStaID==iObs.sourceStaID,
                                                         StationTBL.sourceDB=='PRISM').one()[0]
    if jj%1000==1:
        print(iObs.ID,iObs.StationTBLID, iObs.sourceStaID)

61294 None 1160
61294 6474 1160
62294 None 1273
62294 6586 1273
63294 None 1388
63294 6698 1388
64294 None 1504
64294 6814 1504
65294 None 1624
65294 6925 1624
66294 None 1742
66294 7041 1742
67294 None 1858
67294 7155 1858
68294 None 9177
68294 7266 9177

In [34]:

session.commit()
session2.close()
engine2.dispose()

Check for duplicates (inspect entries with same lat, lon, date, depth)¶

first, check that longitudes have same sign for both databases:

In [35]:

qtest=session.query(func.max(StationTBL.Lon)).group_by(StationTBL.sourceDB).all()
for row in qtest:
    print(row)

(Decimal('-122.2666666667'),)
(Decimal('-122.2998333330'),)

/home/eolson/anaconda3/envs/teos10/lib/python3.4/site-packages/sqlalchemy/sql/sqltypes.py:562: SAWarning: Dialect sqlite+pysqlite does *not* support Decimal objects natively, and SQLAlchemy must convert from floating point - rounding errors and other issues may occur. Please consider storing Decimal numbers as strings or integers on this platform for lossless storage.
  'storage.' % (dialect.name, dialect.driver))

next, check that no stations are duplicated between the different datasets that were combined by comparing Year, Month, Day, Lat, Lon

In [36]:

a1=aliased(StationTBL)
a2=aliased(StationTBL)

In [44]:

qDupSta=session.query(a1.ID.label('ID1'),a2.ID.label('ID2'),a1.Lat,a1.Lon,a1.Year,a1.Month,
                     a1.Day,a1.sourceDB.label('DB1'),a2.sourceDB.label('DB2')).select_from(a1).join(a2,and_(
    a1.Year==a2.Year,
    a1.Month==a2.Month,
    a1.Day==a2.Day,
    a1.Lat-a2.Lat<0.001,
    a1.Lat-a2.Lat>-0.001,
    a1.Lon-a2.Lon<0.001,
    a1.Lon-a2.Lon>-0.001,
    a1.ID!=a2.ID,
    a1.sourceDB!=a2.sourceDB)) # assume there were no duplicates in the original databases

In [45]:

for row in qDupSta.all():
    print(row)

In [46]:

session.close()
engine.dispose()

In [48]:

indthw = np.loadtxt('/ocean/eolson/MEOPAR/tools/bathymetry/thalweg_working.txt', delimiter=" ", unpack=False)
indthw = indthw.astype(int)

In [ ]: