In [1]:
# Python 2/3 compatibility
from __future__ import print_function # to use print() as a function in Python 2

try:
    input = raw_input # use 'input' function in both Python 2 and 3
except NameError:
    pass

# std lib
from astropy.table import Table
from io import BytesIO

# 3rd party
import numpy as np
import pandas as pd
import matplotlib
import matplotlib.pyplot as plt
from matplotlib.ticker import NullFormatter
from astropy.utils.data import download_file  #import file from URL
from scipy.stats import binned_statistic_2d
from astropy.io import fits
%matplotlib inline

# Data Lab
from dl import authClient as ac, queryClient as qc, storeClient as sc, helpers
from dl.helpers.utils import convert
from getpass import getpass

print('Done importing')

Done importing
In [2]:
des = fits.getdata('J_MNRAS_489_5301_desy3-lt.fits')
In [3]:
token = ac.login('ameisner',getpass("Enter password: (+ENTER) "))
if not ac.isValidToken(token):
    raise Exception('Token is not valid. Please check your usename/password and execute this cell again.')

Enter password: (+ENTER) ········
In [4]:
try:
    print(qc.schema('nsc_dr1.object',format='json',profile='db01'))
except Exception as e:
    print(e.message)

Schema: nsc_dr1
 Table: object

     Column Name   Description
     -----------   -----------
              ra   Right ascension (J2000.0) of object, in degrees
             dec   Declination (J2000.0) of object, in degrees
            glon   Galactic Longitude
            glat   Galactic Latitude
            elon   Ecliptic Longitude
            elat   Ecliptic Latitude
           raerr   Uncertainty of RA
          decerr   Uncertainty in DEC
            pmra   Proper motion in RA*cos(DEC)
           pmdec   Proper motion in DEC
         pmraerr   Uncertainty in PMRA
        pmdecerr   Uncertainty in PMDEC
             mjd   Mean Modified Julian Date
        deltamjd   Range of Modified Julian Date
            umag   Weighted-average u-band magnitude
            urms   RMS scatter in u from multiple measurements of this object
            uerr   Uncertainty in mean u-band magnitude
          uasemi   u-band profile RMS along major axis, in arcseconds
          ubsemi   u-band profile RMS along minor axis, in arcseconds
          utheta   u-band position-angle measured CCW East of North, in degrees
            gmag   Weighted-average g-band magnitude
            grms   RMS scatter in g from multiple measurements of this object
            gerr   Uncertainty in mean g-band magnitude
          gasemi   g-band profile RMS along major axis, in arcseconds
          gbsemi   g-band profile RMS along minor axis, in arcseconds
          gtheta   g-band position-angle measured CCW East of North, in degrees
            rmag   Weighted-average r-band magnitude
            rrms   RMS scatter in r from multiple measurements of this object
            rerr   Uncertainty in mean r-band magnitude
          rasemi   r-band profile RMS along major axis, in arcseconds
          rbsemi   r-band profile RMS along minor axis, in arcseconds
          rtheta   r-band position-angle measured CCW East of North, in degrees
            imag   Weighted-average i-band magnitude
            irms   RMS scatter in i from multiple measurements of this object
            ierr   Uncertainty in mean i-band magnitude
          iasemi   i-band profile RMS along major axis, in arcseconds
          ibsemi   i-band profile RMS along minor axis, in arcseconds
          itheta   i-band position-angle measured CCW East of North, in degrees
            zmag   Weighted-average z-band magnitude
            zrms   RMS scatter in z from multiple measurements of this object
            zerr   Uncertainty in mean z-band magnitude
          zasemi   z-band profile RMS along major axis, in arcseconds
          zbsemi   z-band profile RMS along minor axis, in arcseconds
          ztheta   z-band position-angle measured CCW East of North, in degrees
            ymag   Weighted-average Y-band magnitude
            yrms   RMS scatter in Y from multiple measurements of this object
            yerr   Uncertainty in mean Y-band magnitude
          yasemi   Y-band profile RMS along major axis, in arcseconds
          ybsemi   Y-band profile RMS along minor axis, in arcseconds
          ytheta   Y-band position-angle measured CCW East of North, in degrees
           vrmag   Weighted-average VR-band magnitude
           vrrms   RMS scatter in VR from multiple measurements of this object
           vrerr   Uncertainty in mean VR-band magnitude
         vrasemi   VR-band profile RMS along major axis, in arcseconds
         vrbsemi   VR-band profile RMS along minor axis, in arcseconds
         vrtheta   VR-band position-angle measured CCW East of North, in degrees
              x2   Mean variance (second moment) along RA
           x2err   Uncertainty in mean variance along RA
              y2   Mean variance (second moment) along DEC
           y2err   Uncertainty in mean variance along DEC
              xy   Mean covariance between RA and DEC
           xyerr   Uncertainty in mean covariance between RA and DEC
           asemi   Mean profile RMS along major axis, in arcseconds
        asemierr   Uncertainty in mean RMS along major axis
           bsemi   Mean profile RMS along minor axis, in arcseconds
        bsemierr   Uncertainty in mean RMS along minor axis
           theta   Position angle measured CCW East of North, in degrees
        thetaerr   Uncertainty in THETA
     ellipticity   Object ellipticity, 1 - BSEMI/ASEMI
            fwhm   FWHM assuming a gaussian core, in arcseconds
      class_star   Star/Galaxy classifier
             ebv   SFD E(B-V) reddening
             u_g   Computed (u-g) color
             g_r   Computed (g-r) color
             r_i   Computed (r-i) color
             i_z   Computed (i-z) color
             z_y   Computed (z-Y) color
       random_id   Random ID in the range 0.0 => 100.0
             pix   Healpix nside=128 pixel number
            ndet   Number of detections in all bands
           nphot   Number of photometric detections in all bands
            htm9   HTM index (order 9 => ~10 arcmin size)
         ring256   HEALPIX index (Nsides 256, Ring scheme => ~14 arcmin size)
        nest4096   HEALPIX index (Nsides 4096, Nest scheme => ~52 arcsec size
           ndetu   Number of detections in u
          nphotu   Number of photometric detections in u
           ndetg   Number of detections in g
          nphotg   Number of photometric detections in g
           ndetr   Number of detections in r
          nphotr   Number of photometric detections in r
           ndeti   Number of detections in i
          nphoti   Number of photometric detections in i
           ndetz   Number of detections in z
          nphotz   Number of photometric detections in z
           ndety   Number of detections in Y
          nphoty   Number of photometric detections in Y
          ndetvr   Number of detections in VR
         nphotvr   Number of photometric detections in VR
           flags   OR-combined SExtractor FLAG value
              id   Unique object ID
In [5]:
des = Table(des)
In [6]:
_des = des[0:]
print(len(_des))

11745
In [7]:
df = _des.to_pandas()
In [8]:
%%time

# Preemptively drop the table in case it already exists (mydb does not overwrite well right now)
qc.mydb_drop('des_bds')

# Import the dataframe as mybd table
qc.mydb_import('des_bds',df)

CPU times: user 399 ms, sys: 22 ms, total: 421 ms
Wall time: 6.65 s
Out[8]:
'OK'
In [9]:
query0 = 'SELECT * FROM mydb://des_bds'
r0 = qc.query(sql=query0,fmt='table')
In [10]:
len(r0)
Out[10]:
11745
In [11]:
query_xmatch="""
SELECT N.*
FROM mydb://des_bds as d, nsc_dr1.object as N
WHERE q3c_join(d.radeg, d.dedeg, N.ra, N.dec, (1/3600.0))
"""
In [12]:
import time
t0 = time.time()
xmatch = qc.query(sql=query_xmatch,fmt='table')
dt = time.time()-t0
print(dt, ' seconds')

4.918838024139404  seconds
In [13]:
len(xmatch)
Out[13]:
12257
In [14]:
query_xmatch_dr2="""
SELECT N.*
FROM mydb://des_bds as d, nsc_dr2.object as N
WHERE q3c_join(d.radeg, d.dedeg, N.ra, N.dec, (1/3600.0))
"""
In [15]:
xmatch = qc.query(sql=query_xmatch_dr2,fmt='table',profile='db01')

---------------------------------------------------------------------------
queryClientError                          Traceback (most recent call last)
<ipython-input-15-a71e7536ec2f> in <module>
----> 1 xmatch = qc.query(sql=query_xmatch_dr2,fmt='table',profile='db01')

/data0/sw/anaconda3/lib/python3.7/site-packages/noaodatalab-2.18.10-py3.7.egg/dl/Util.py in __call__(self, *args, **kw)
     80             return function(self.obj, *args, **kw)
     81         else:
---> 82             return function(*args, **kw)
     83 
     84     def __repr__(self):

/data0/sw/anaconda3/lib/python3.7/site-packages/noaodatalab-2.18.10-py3.7.egg/dl/queryClient.py in query(token, adql, sql, fmt, out, async_, drop, profile, **kw)
    536     return qc_client._query (token=def_token(token), adql=adql, sql=sql, 
    537                              fmt=fmt, out=out, async_=async_, drop=drop, profile=profile,
--> 538                              **kw)
    539 
    540 

/data0/sw/anaconda3/lib/python3.7/site-packages/noaodatalab-2.18.10-py3.7.egg/dl/queryClient.py in _query(self, token, adql, sql, fmt, out, async_, drop, profile, **kw)
   1919         r = requests.get (dburl, headers=headers, timeout=timeout)
   1920         if r.status_code != 200:
-> 1921             raise queryClientError (r.text)
   1922         resp = qcToString(r.content)
   1923 

queryClientError: Error: QM: MyDB table des_bds does not exist
In [16]:
xmatch = qc.query(sql=query_xmatch_dr2,fmt='table',profile='default')

---------------------------------------------------------------------------
queryClientError                          Traceback (most recent call last)
<ipython-input-16-6c0cbcce4c29> in <module>
----> 1 xmatch = qc.query(sql=query_xmatch_dr2,fmt='table',profile='default')

/data0/sw/anaconda3/lib/python3.7/site-packages/noaodatalab-2.18.10-py3.7.egg/dl/Util.py in __call__(self, *args, **kw)
     80             return function(self.obj, *args, **kw)
     81         else:
---> 82             return function(*args, **kw)
     83 
     84     def __repr__(self):

/data0/sw/anaconda3/lib/python3.7/site-packages/noaodatalab-2.18.10-py3.7.egg/dl/queryClient.py in query(token, adql, sql, fmt, out, async_, drop, profile, **kw)
    536     return qc_client._query (token=def_token(token), adql=adql, sql=sql, 
    537                              fmt=fmt, out=out, async_=async_, drop=drop, profile=profile,
--> 538                              **kw)
    539 
    540 

/data0/sw/anaconda3/lib/python3.7/site-packages/noaodatalab-2.18.10-py3.7.egg/dl/queryClient.py in _query(self, token, adql, sql, fmt, out, async_, drop, profile, **kw)
   1919         r = requests.get (dburl, headers=headers, timeout=timeout)
   1920         if r.status_code != 200:
-> 1921             raise queryClientError (r.text)
   1922         resp = qcToString(r.content)
   1923 

queryClientError: Error: relation "nsc_dr2.object" does not exist
LINE 3: FROM mydb."des_bds" as d, nsc_dr2.object as N
                                       ^
In [17]:
xmatch = qc.query(sql=query_xmatch_dr2,fmt='table')

---------------------------------------------------------------------------
queryClientError                          Traceback (most recent call last)
<ipython-input-17-4971d671fbb1> in <module>
----> 1 xmatch = qc.query(sql=query_xmatch_dr2,fmt='table')

/data0/sw/anaconda3/lib/python3.7/site-packages/noaodatalab-2.18.10-py3.7.egg/dl/Util.py in __call__(self, *args, **kw)
     80             return function(self.obj, *args, **kw)
     81         else:
---> 82             return function(*args, **kw)
     83 
     84     def __repr__(self):

/data0/sw/anaconda3/lib/python3.7/site-packages/noaodatalab-2.18.10-py3.7.egg/dl/queryClient.py in query(token, adql, sql, fmt, out, async_, drop, profile, **kw)
    536     return qc_client._query (token=def_token(token), adql=adql, sql=sql, 
    537                              fmt=fmt, out=out, async_=async_, drop=drop, profile=profile,
--> 538                              **kw)
    539 
    540 

/data0/sw/anaconda3/lib/python3.7/site-packages/noaodatalab-2.18.10-py3.7.egg/dl/queryClient.py in _query(self, token, adql, sql, fmt, out, async_, drop, profile, **kw)
   1919         r = requests.get (dburl, headers=headers, timeout=timeout)
   1920         if r.status_code != 200:
-> 1921             raise queryClientError (r.text)
   1922         resp = qcToString(r.content)
   1923 

queryClientError: Error: relation "nsc_dr2.object" does not exist
LINE 3: FROM mydb."des_bds" as d, nsc_dr2.object as N
                                       ^
In [ ]: