{ "cells": [ { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "import datetime as dt\n", "import numpy as np\n", "import netCDF4 as nc\n", "import pandas as pd\n", "import glob\n", "from salishsea_tools import geo_tools\n", "import gsw\n", "import os\n", "import pytz\n", "import matplotlib.pyplot as plt\n", "import cmocean as cmo\n", "import warnings\n", "from sqlalchemy import create_engine, case, MetaData\n", "from sqlalchemy.orm import create_session, aliased\n", "from sqlalchemy.ext.automap import automap_base\n", "from sqlalchemy.sql import and_, or_, not_, func\n", "from salishsea_tools import viz_tools\n", "\n", "pd.set_option('display.max_colwidth', -1)\n", "\n", "%matplotlib inline" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "basedir='/ocean/shared/SalishSeaCastData/DFO/CTD/'\n", "dbname='DFO_CTD.sqlite'\n", "datelims=()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "engine = create_engine('sqlite:///' + basedir + dbname, echo = False)" ] }, { "cell_type": "raw", "metadata": { "collapsed": true }, "source": [ "connection = engine.connect()" ] }, { "cell_type": "raw", "metadata": { "collapsed": true }, "source": [ "def Sal_PSU_to_SA(S,press,lon,lat):\n", " SA=gsw.SA_from_SP(S,press,lon,lat)\n", " if SA>=40:\n", " print('Err: SA=',SA,', None entered')\n", " SA=None\n", " elif SA<0:\n", " SA=None\n", " print('Err: SA=',SA,', None entered')\n", " return(SA)\n", "\n", "def T_to_CT(SA,T,press):\n", " CT=gsw.CT_from_t(SA,T,press)\n", " if CT>=40:\n", " print('Err: CT=',CT,', None entered')\n", " CT=None\n", " elif CT<-5:\n", " CT=None\n", " print('Err: CT=',CT,', None entered')\n", " return(CT)\n", "\n", "def p_to_Z(press,lat):\n", " Z=-1.0*gsw.z_from_p(press,lat)\n", " return(Z)" ] }, { "cell_type": "raw", "metadata": { "collapsed": true }, "source": [ " connection.connection.create_function(\"Sal_PSU_to_SA_DB\",4,Sal_PSU_to_SA)\n", " connection.connection.create_function(\"T_to_CT_DB\",3,T_to_CT)\n", " connection.connection.create_function(\"p_to_Z_DB\",2,p_to_Z)" ] }, { "cell_type": "raw", "metadata": {}, "source": [ "connection.execute(\"\"\"UPDATE CalcsTBL SET \n", " Temperature_CT = T_to_CT_DB(CalcsTBL.Salinity_SA,ObsTBL.Temperature,ObsTBL.Pressure), \n", " Temperature_Primary_CT = T_to_CT_DB(CalcsTBL.Salinity_T0_C0_SA,ObsTBL.Temperature,ObsTBL.Pressure), \n", " Temperature_Secondary_CT = T_to_CT_DB(CalcsTBL.Salinity_T1_C1_SA,ObsTBL.Temperature,ObsTBL.Pressure),\n", " FROM ((CalcsTBL INNER JOIN ObsTBL ON CalcsTBL.ObsID = ObsTBL.ID) INNER JOIN StationTBL ON CalcsTBL.StationID = StationTBL.ID)\"\"\")" ] }, { "cell_type": "raw", "metadata": { "collapsed": true }, "source": [ "test=connection.execute(\"\"\"SELECT T_to_CT_DB( CASE WHEN CalcsTBL.Salinity_T1_C1_SA IS NOT NULL THEN CalcsTBL.Salinity_T1_C1_SA\n", " WHEN CalcsTBL.Salinity_T0_C0_SA IS NOT NULL THEN CalcsTBL.Salinity_T0_C0_SA\n", " WHEN CalcsTBL.Salinity_SA IS NOT NULL THEN CalcsTBL.Salinity_SA\n", " ELSE NULL END,ObsTBL.Temperature,ObsTBL.Pressure) as T1, \n", " T_to_CT_DB( CASE WHEN CalcsTBL.Salinity_T1_C1_SA IS NOT NULL THEN CalcsTBL.Salinity_T1_C1_SA\n", " WHEN CalcsTBL.Salinity_T0_C0_SA IS NOT NULL THEN CalcsTBL.Salinity_T0_C0_SA\n", " WHEN CalcsTBL.Salinity_SA IS NOT NULL THEN CalcsTBL.Salinity_SA\n", " ELSE NULL END,ObsTBL.Temperature,ObsTBL.Pressure) as T2, \n", " T_to_CT_DB( CASE WHEN CalcsTBL.Salinity_T1_C1_SA IS NOT NULL THEN CalcsTBL.Salinity_T1_C1_SA\n", " WHEN CalcsTBL.Salinity_T0_C0_SA IS NOT NULL THEN CalcsTBL.Salinity_T0_C0_SA\n", " WHEN CalcsTBL.Salinity_SA IS NOT NULL THEN CalcsTBL.Salinity_SA\n", " ELSE NULL END,ObsTBL.Temperature,ObsTBL.Pressure) as T3\n", " FROM CalcsTBL INNER JOIN ObsTBL ON CalcsTBL.ObsID = ObsTBL.ID INNER JOIN StationTBL ON CalcsTBL.StationID = StationTBL.ID;\"\"\")\n" ] }, { "cell_type": "raw", "metadata": {}, "source": [ "ii=0\n", "for row in test:\n", " ii=ii+1\n", " if (ii>100)&(ii<120):\n", " print(row)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "grid = nc.Dataset('/data/eolson/MEOPAR/NEMO-forcing-new/grid/bathymetry_201702.nc')" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "md=MetaData()\n", "md.reflect(engine)\n", "Base = automap_base(metadata=md)\n", "# reflect the tables in salish.sqlite:\n", "Base.prepare()\n", "# mapped classes have been created\n", "# existing tables:\n", "StationTBL=Base.classes.StationTBL\n", "ObsTBL=Base.classes.ObsTBL\n", "CalcsTBL=Base.classes.CalcsTBL\n", "AncTBL=Base.classes.AncillaryTBL\n", "#JDFLocsTBL=Base.classes.JDFLocsTBL\n", "session = create_session(bind = engine, autocommit = False, autoflush = True)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "qry=session.query(ObsTBL.Depth,ObsTBL.Pressure,CalcsTBL.Z,ObsTBL.Salinity,ObsTBL.Salinity_T0_C0,ObsTBL.Salinity_T1_C1,CalcsTBL.Salinity_SA,CalcsTBL.Salinity_T0_C0_SA,CalcsTBL.Salinity_T1_C1_SA,\n", " CalcsTBL.Temperature_CT,CalcsTBL.Temperature_Primary_CT,CalcsTBL.Temperature_Secondary_CT).\\\n", " select_from(CalcsTBL).join(ObsTBL,ObsTBL.ID==CalcsTBL.ObsTBLID).filter(ObsTBL.Depth==ObsTBL.Depth).all()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df=pd.DataFrame(qry)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df.describe()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Look at cases where Depth and Z are different:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "np.min((df['Depth']-df['Z'])/(df['Z']+df['Depth'])*2),np.max((df['Depth']-df['Z'])/(df['Z']+df['Depth'])*2)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df.loc[(np.abs((df['Depth']-df['Z'])/(df['Z']+df['Depth'])*2)>.1)&(np.abs((df['Depth']-df['Z']))>.3)]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### List column names for tables:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "sorted([x.name for x in md.tables['StationTBL'].columns])" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "sorted([x.name for x in md.tables['ObsTBL'].columns])" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "sorted([x.name for x in md.tables['CalcsTBL'].columns])" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "sorted([x.name for x in md.tables['AncillaryTBL'].columns])" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### salinity variables: 'Salinity','Salinity_T0_C0', 'Salinity_T1_C1'\n", "#### temperature variables:'Temperature','Temperature_Primary','Temperature_Secondary'" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### How many Depths with no Pressure and vice versa?" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "print('Z without P:',session.query(ObsTBL.Depth).filter(ObsTBL.Pressure==None).count())\n", "print('P without Z:',session.query(ObsTBL.Pressure).filter(ObsTBL.Depth==None).count())\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Other depth info:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "print('Z min, max:',session.query(func.min(ObsTBL.Depth)).one(),session.query(func.max(ObsTBL.Depth)).one())\n", "print('P min, max:',session.query(func.min(ObsTBL.Pressure)).one(),session.query(func.max(ObsTBL.Pressure)).one())" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Other Variables:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "for var in (ObsTBL.Salinity,ObsTBL.Salinity_T0_C0,ObsTBL.Salinity_T1_C1,ObsTBL.Temperature,ObsTBL.Temperature_Primary,ObsTBL.Temperature_Secondary):\n", " print(var,'min max count:',session.query(func.min(var)).one(),session.query(func.max(var)).one(),session.query(var).filter(var!=None).count())" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "vlist=(ObsTBL.Salinity,ObsTBL.Salinity_T0_C0,ObsTBL.Salinity_T1_C1,ObsTBL.Temperature,ObsTBL.Temperature_Primary,ObsTBL.Temperature_Secondary)\n", "ulist=(ObsTBL.Salinity_units,ObsTBL.Salinity_T0_C0_units,ObsTBL.Salinity_T1_C1_units,ObsTBL.Temperature_units,\n", " ObsTBL.Temperature_Primary_units,ObsTBL.Temperature_Secondary_units)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "for vvar,uvar in zip(vlist,ulist):\n", " print(uvar,'unique:')\n", " print('\\t',[i for i in session.query(uvar).group_by(uvar).all()])\n", " print('\\t','# missing units:',session.query(vvar,uvar).filter(and_(vvar!=None,uvar==None)).count())" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Check which T&S variable combinations are present" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df.loc[(~np.isnan(df['Salinity_T1_C1']))&(~np.isnan(df['Temperature_Primary']))]" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df.loc[(~np.isnan(df['Salinity_T1_C1']))&(~np.isnan(df['Temperature']))]" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df.loc[(~np.isnan(df['Salinity_T0_C0']))&(~np.isnan(df['Temperature']))]" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df.loc[(~np.isnan(df['Salinity_T0_C0']))&(~np.isnan(df['Temperature_Secondary']))]" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df.loc[(~np.isnan(df['Salinity']))&(~np.isnan(df['Temperature_Secondary']))]" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df.loc[(~np.isnan(df['Salinity']))&(~np.isnan(df['Temperature_Primary']))]" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "len(df.loc[(~np.isnan(df['Salinity']))&(~np.isnan(df['Temperature']))])" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "len(df.loc[(~np.isnan(df['Salinity_T0_C0']))&(~np.isnan(df['Temperature_Primary']))])" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "len(df.loc[(~np.isnan(df['Salinity_T1_C1']))&(~np.isnan(df['Temperature_Secondary']))])" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Plot All T S Data" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df=pd.DataFrame(session.query(ObsTBL.Depth,ObsTBL.Pressure,ObsTBL.Salinity,ObsTBL.Salinity_T0_C0,ObsTBL.Salinity_T1_C1,\n", " ObsTBL.Temperature,ObsTBL.Temperature_Primary,ObsTBL.Temperature_Secondary,\n", " StationTBL.Lat,StationTBL.Lon).select_from(ObsTBL).join(StationTBL,StationTBL.ID==ObsTBL.StationTBLID).all())\n", "\n", "fig,ax=plt.subplots(1,3,figsize=(18,6))\n", "ax[0].plot(df['Salinity'],df['Temperature'],'r.')\n", "ax[1].plot(df['Salinity_T0_C0'],df['Temperature_Primary'],'c.')\n", "ax[2].plot(df['Salinity_T1_C1'],df['Temperature_Secondary'],'m.')" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "fig,ax=plt.subplots(1,3,figsize=(18,6))\n", "for iax in ax:\n", " viz_tools.set_aspect(iax, coords = 'map')\n", " viz_tools.plot_coastline(iax, grid, coords = 'map')\n", " iax.set_ylim(47, 52)\n", " iax.set_xlim(-130, -122);\n", "ax[0].plot(df.loc[(df['Salinity']>0)&(df['Temperature']>0),['Lon']],\n", " df.loc[(df['Salinity']>0)&(df['Temperature']>0),['Lat']],'ro')\n", "ax[1].plot(df.loc[(df['Salinity_T0_C0']>0)&(df['Temperature_Primary']>0),['Lon']],\n", " df.loc[(df['Salinity_T0_C0']>0)&(df['Temperature_Primary']>0),['Lat']],'co')\n", "ax[2].plot(df.loc[(df['Salinity_T1_C1']>0)&(df['Temperature_Secondary']>0),['Lon']],\n", " df.loc[(df['Salinity_T1_C1']>0)&(df['Temperature_Secondary']>0),['Lat']],'mo')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Plot all included T&S data" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df=pd.DataFrame(session.query(ObsTBL.Depth,ObsTBL.Pressure,ObsTBL.Salinity,ObsTBL.Salinity_T0_C0,\n", " ObsTBL.Salinity_T1_C1,ObsTBL.Temperature,ObsTBL.Temperature_Primary,\n", " ObsTBL.Temperature_Secondary,StationTBL.Lat,StationTBL.Lon).\\\n", " select_from(ObsTBL).join(StationTBL,StationTBL.ID==ObsTBL.StationTBLID).\\\n", " filter(ObsTBL.Include==True).all())\n", "\n", "fig,ax=plt.subplots(1,3,figsize=(18,6))\n", "ax[0].plot(df['Salinity'],df['Temperature'],'r.')\n", "ax[1].plot(df['Salinity_T0_C0'],df['Temperature_Primary'],'c.')\n", "ax[2].plot(df['Salinity_T1_C1'],df['Temperature_Secondary'],'m.')" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "fig,ax=plt.subplots(1,3,figsize=(18,6))\n", "for iax in ax:\n", " viz_tools.set_aspect(iax, coords = 'map')\n", " viz_tools.plot_coastline(iax, grid, coords = 'map')\n", " iax.set_ylim(47, 52)\n", " iax.set_xlim(-130, -122);\n", "ax[0].plot(df.loc[(df['Salinity']>0)&(df['Temperature']>0),['Lon']],\n", " df.loc[(df['Salinity']>0)&(df['Temperature']>0),['Lat']],'ro')\n", "ax[1].plot(df.loc[(df['Salinity_T0_C0']>0)&(df['Temperature_Primary']>0),['Lon']],\n", " df.loc[(df['Salinity_T0_C0']>0)&(df['Temperature_Primary']>0),['Lat']],'co')\n", "ax[2].plot(df.loc[(df['Salinity_T1_C1']>0)&(df['Temperature_Secondary']>0),['Lon']],\n", " df.loc[(df['Salinity_T1_C1']>0)&(df['Temperature_Secondary']>0),['Lat']],'mo')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Restrict to Salish Sea" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "qry=session.query(StationTBL.StartYear.label('Year'),StationTBL.StartMonth.label('Month'),\n", " StationTBL.StartDay.label('Day'),StationTBL.StartHour.label('Hour'),\n", " StationTBL.Lat,StationTBL.Lon,\n", " ObsTBL.Depth,ObsTBL.Pressure,ObsTBL.Salinity,\n", " ObsTBL.Salinity_T0_C0,ObsTBL.Salinity_T1_C1,\n", " ObsTBL.Temperature,ObsTBL.Temperature_Primary,ObsTBL.Temperature_Secondary,ObsTBL.sourceFile).\\\n", " select_from(StationTBL).join(ObsTBL,ObsTBL.StationTBLID==StationTBL.ID).\\\n", " filter(and_(StationTBL.Lat>47-3/2.5*(StationTBL.Lon+123.5),\n", " StationTBL.Lat<47-3/2.5*(StationTBL.Lon+121)))\n", "df=pd.DataFrame(qry.all())" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "fig,ax=plt.subplots(1,3,figsize=(18,6))\n", "ax[0].plot(df['Salinity'],df['Temperature'],'r.')\n", "ax[1].plot(df['Salinity_T0_C0'],df['Temperature_Primary'],'c.')\n", "ax[2].plot(df['Salinity_T1_C1'],df['Temperature_Secondary'],'m.')" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "fig,ax=plt.subplots(1,3,figsize=(18,6))\n", "for iax in ax:\n", " viz_tools.set_aspect(iax, coords = 'map')\n", " viz_tools.plot_coastline(iax, grid, coords = 'map')\n", " #iax.set_ylim(48, 50.5)\n", " #iax.set_xlim(-125.7, -122.5);\n", " iax.set_ylim(47, 52)\n", " iax.set_xlim(-130, -122);\n", "ax[0].plot(df.loc[(df['Salinity']>0)&(df['Temperature']>0),['Lon']],\n", " df.loc[(df['Salinity']>0)&(df['Temperature']>0),['Lat']],'ro')\n", "ax[1].plot(df.loc[(df['Salinity_T0_C0']>0)&(df['Temperature_Primary']>0),['Lon']],\n", " df.loc[(df['Salinity_T0_C0']>0)&(df['Temperature_Primary']>0),['Lat']],'co')\n", "ax[2].plot(df.loc[(df['Salinity_T1_C1']>0)&(df['Temperature_Secondary']>0),['Lon']],\n", " df.loc[(df['Salinity_T1_C1']>0)&(df['Temperature_Secondary']>0),['Lat']],'mo')" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "models=session.query(AncTBL.MODEL).distinct().all()\n", "models" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Display CastAway stations" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "## Where are CastAway stations?\n", "qry=session.query(StationTBL.StartYear.label('Year'),StationTBL.StartMonth.label('Month'),\n", " StationTBL.StartDay.label('Day'),StationTBL.StartHour.label('Hour'),\n", " StationTBL.Lat,StationTBL.Lon,\n", " ObsTBL.Depth,ObsTBL.Pressure,ObsTBL.Salinity,\n", " ObsTBL.Salinity_T0_C0,ObsTBL.Salinity_T1_C1,\n", " ObsTBL.Temperature,ObsTBL.Temperature_Primary,ObsTBL.Temperature_Secondary,ObsTBL.sourceFile).\\\n", " select_from(StationTBL).join(ObsTBL,ObsTBL.StationTBLID==StationTBL.ID).\\\n", " join(AncTBL,AncTBL.StationTBLID==StationTBL.ID).\\\n", " filter(AncTBL.MODEL=='CastAway')\n", "df=pd.DataFrame(qry.all())\n", "fig,ax=plt.subplots(1,3,figsize=(18,6))\n", "for iax in ax:\n", " viz_tools.set_aspect(iax, coords = 'map')\n", " viz_tools.plot_coastline(iax, grid, coords = 'map')\n", " #iax.set_ylim(48, 50.5)\n", " #iax.set_xlim(-125.7, -122.5);\n", " iax.set_ylim(47, 52)\n", " iax.set_xlim(-130, -122);\n", "ax[0].plot(df.loc[(df['Salinity']>0)&(df['Temperature']>0),['Lon']],\n", " df.loc[(df['Salinity']>0)&(df['Temperature']>0),['Lat']],'ro')\n", "ax[1].plot(df.loc[(df['Salinity_T0_C0']>0)&(df['Temperature_Primary']>0),['Lon']],\n", " df.loc[(df['Salinity_T0_C0']>0)&(df['Temperature_Primary']>0),['Lat']],'co')\n", "ax[2].plot(df.loc[(df['Salinity_T1_C1']>0)&(df['Temperature_Secondary']>0),['Lon']],\n", " df.loc[(df['Salinity_T1_C1']>0)&(df['Temperature_Secondary']>0),['Lat']],'mo')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Check CastAway profiles are excluded:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "qry=session.query(StationTBL.Include).select_from(StationTBL).join(AncTBL,AncTBL.StationTBLID==StationTBL.ID).\\\n", " filter(AncTBL.MODEL=='CastAway').distinct().all()\n", "print('Station Include:',qry)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "qry=session.query(ObsTBL.Include).select_from(ObsTBL).join(AncTBL,AncTBL.StationTBLID==ObsTBL.StationTBLID).\\\n", " filter(AncTBL.MODEL=='CastAway').distinct().all()\n", "print('Obs Include:',qry)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### check that this plot is linear or you are likely missing stations from obs table:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "test=session.query(ObsTBL.StationTBLID).distinct().order_by(ObsTBL.StationTBLID).all()\n", "plt.plot(test)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Salish Sea with Include=True" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "qry=session.query(StationTBL.StartYear.label('Year'),StationTBL.StartMonth.label('Month'),\n", " StationTBL.StartDay.label('Day'),StationTBL.StartHour.label('Hour'),\n", " StationTBL.Lat,StationTBL.Lon,\n", " ObsTBL.Depth,ObsTBL.Pressure,ObsTBL.Salinity,\n", " ObsTBL.Salinity_T0_C0,ObsTBL.Salinity_T1_C1,\n", " ObsTBL.Temperature,ObsTBL.Temperature_Primary,ObsTBL.Temperature_Secondary,ObsTBL.sourceFile).\\\n", " select_from(StationTBL).join(ObsTBL,ObsTBL.StationTBLID==StationTBL.ID).\\\n", " join(AncTBL,AncTBL.StationTBLID==StationTBL.ID).\\\n", " filter(and_(StationTBL.Lat>47-3/2.5*(StationTBL.Lon+123.5),\n", " StationTBL.Lat<47-3/2.5*(StationTBL.Lon+121),ObsTBL.Include==True))\n", "df=pd.DataFrame(qry.all())\n", "fig,ax=plt.subplots(1,3,figsize=(18,6))\n", "ax[0].plot(df['Salinity'],df['Temperature'],'r.')\n", "ax[1].plot(df['Salinity_T0_C0'],df['Temperature_Primary'],'c.')\n", "ax[2].plot(df['Salinity_T1_C1'],df['Temperature_Secondary'],'m.')" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "fig,ax=plt.subplots(1,3,figsize=(18,6))\n", "for iax in ax:\n", " viz_tools.set_aspect(iax, coords = 'map')\n", " viz_tools.plot_coastline(iax, grid, coords = 'map')\n", " #iax.set_ylim(48, 50.5)\n", " #iax.set_xlim(-125.7, -122.5);\n", " iax.set_ylim(47, 52)\n", " iax.set_xlim(-130, -122);\n", "ax[0].plot(df.loc[(df['Salinity']>0)&(df['Temperature']>0),['Lon']],\n", " df.loc[(df['Salinity']>0)&(df['Temperature']>0),['Lat']],'ro')\n", "ax[1].plot(df.loc[(df['Salinity_T0_C0']>0)&(df['Temperature_Primary']>0),['Lon']],\n", " df.loc[(df['Salinity_T0_C0']>0)&(df['Temperature_Primary']>0),['Lat']],'co')\n", "ax[2].plot(df.loc[(df['Salinity_T1_C1']>0)&(df['Temperature_Secondary']>0),['Lon']],\n", " df.loc[(df['Salinity_T1_C1']>0)&(df['Temperature_Secondary']>0),['Lat']],'mo')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Check for Duplicates" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# search for duplicate stations and investigate:\n", "a1=aliased(StationTBL)\n", "a2=aliased(StationTBL)\n", "dupsQRY=session.query(a1.ID.label('ID1'),a1.Include,a2.ID.label('ID2'),a2.Include,a1.sourceFile.label('source1'),a2.sourceFile.label('source2'),\n", " a1.EVENT_NUMBER.label('EVENT_NUMBER1'),a2.EVENT_NUMBER.label('EVENT_NUMBER2'),\n", " a1.PLATFORM.label('PLATFORM1'),a2.PLATFORM.label('PLATFORM2'),\n", " a1.STATION.label('STATION1'),a2.STATION.label('STATION2'),\n", " a1.WATER_DEPTH.label('WATER_DEPTH1'),a2.WATER_DEPTH.label('WATER_DEPTH2')).select_from(a1).join(a2,and_(\n", " a1.StartYear==a2.StartYear,\n", " a1.StartMonth==a2.StartMonth,\n", " a1.StartDay==a2.StartDay,\n", " a1.StartHour-a2.StartHour<0.001,\n", " a1.StartHour-a2.StartHour>-0.001,\n", " a1.Lat-a2.Lat<0.001,\n", " a1.Lat-a2.Lat>-0.001,\n", " a1.Lon-a2.Lon<0.001,\n", " a1.Lon-a2.Lon>-0.001,\n", " a1.ID!=a2.ID)).filter(a1.Include==True,a2.Include==True,a1.ID-0.001,\n", " a1.Lat-a2.Lat<0.001,\n", " a1.Lat-a2.Lat>-0.001,\n", " a1.Lon-a2.Lon<0.001,\n", " a1.Lon-a2.Lon>-0.001,\n", " a1.ID!=a2.ID)).filter(a1.Include==True,a2.Include==False,a1.ID