Table Access Protocol (TAP) queries

import numpy as np
from lsst.rsp import get_tap_service
import pandas as pd

service = get_tap_service ('tap')
assert service is not None

fields = pd.DataFrame(columns=['ra_deg', 'dec_deg'])
fields.loc['47Tuc']          = [  6.02, -72.08] # 47 Tuc Globular 
fields.loc['LELF_SV_38_7']   = [ 37.86,   6.98] # 
fields.loc['Fornax']         = [ 40.00, -34.45] # 
fields.loc['ECDFS']          = [ 53.13, -28.10] # 
fields.loc['EDFS']           = [ 59.10, -48.73] # 
fields.loc['LGLF_SV_95_-25'] = [ 95.00, -25.00] # 
fields.loc['Seagull_Nebula'] = [106.23, -10.51] # 

ddeg = .01
def grabcone(field):
    return "CONTAINS(POINT('ICRS', coord_ra, coord_dec), " +\
           f"CIRCLE('ICRS', {fields.loc[field, 'ra_deg']}, " +\
                          f"{fields.loc[field, 'dec_deg']}, {ddeg})) = 1"
def grabpoint(field):
    return f"POINT('ICRS', {fields.loc[field, 'ra_deg']}, " +\
                         f"{fields.loc[field, 'dec_deg']})"

field = 'ECDFS'
#                               COLUMNS  CATALOG CONSTRAINTS
queries = pd.DataFrame(columns=['select', 'from', 'where', 'order by'])
# queries.loc['tap_schemas'] = ["*", "tap_schema", "", ""]          Table [ tap_schema ] is not found in TapSchema. Possible reasons: table does not exist or permission is denied.
queries.loc['schemas'] = ["*", "tap_schema.schemas", "", ""]          # list all schemas
queries.loc['tables'] =  ["*",                                        # list all table names from DP1 TAP schema
                          "tap_schema.tables", 
                          "tap_schema.tables.schema_name = 'dp1'", 
                          "table_index ASC"]
queries.loc['columns'] = ["column_name, datatype, description, unit", # list all columns from DP1 Object table
                          "tap_schema.columns",
                          "table_name = 'dp1.Object'", ""]
queries.loc['cone']    = ["coord_ra, coord_dec", 
                          "dp1.Object", grabcone(field), ""]
queries.loc['sort']    = ["coord_ra, coord_dec", 
                          "dp1.Object", grabcone(field), "coord_ra ASC"]
queries.loc['top']     = ["TOP 10 coord_ra, coord_dec", 
                          "dp1.Object", grabcone(field), "coord_ra ASC"]
queries.loc['rename']  = ["coord_ra AS ra_deg, coord_dec AS dec_deg", 
                          "dp1.Object", grabcone(field), ""]
queries.loc['deg2rad'] = ["RADIANS(coord_ra) AS ra_radians, RADIANS(coord_dec) AS dec_radians", 
                          "dp1.Object", grabcone(field), ""]
queries.loc['dstance'] = ["coord_ra, coord_dec, " +
                           "DISTANCE(POINT('ICRS', coord_ra, coord_dec), " + grabpoint(field) + ")",
                          "dp1.Object", grabcone(field), ""]                           
queries.loc['mag']     = ["coord_ra, coord_dec, " +
                          "u_cModelMag, g_cModelMag, r_cModelMag, i_cModelMag, z_cModelMag, y_cModelMag",
                          "dp1.Object", grabcone(field), ""]
queries.loc['relat']   = ["coord_ra, coord_dec, " +
                          "u_cModelMag, g_cModelMag, r_cModelMag, i_cModelMag, z_cModelMag, y_cModelMag",
                          "dp1.Object", 
                          grabcone(field) + " AND u_cModelMag < 25 AND g_cModelMag < 25"
                                         + " AND r_cModelMag < 25 AND i_cModelMag < 25"
                                         + " AND z_cModelMag < 25 AND y_cModelMag < 25", ""]
queries.loc['btween']  = ["objectId, u_psfMAG, g_psfMag, r_psfMag, i_psfMag, z_psfMag, y_psfMag", 
                          "dp1.Object", 
                          grabcone(field) + " AND r_psfMag BETWEEN g_psfMag AND i_psfMag", ""]
queries.loc['in']      = ["objectId, coord_ra, coord_dec",
                          "dp1.Object", 
                          "objectID IN ({})".format(", ".join(str(dd) for dd in [611255141361789816, 611255141361790690, 611255141361791996])), ""]
queries.loc['jy2ab']   = ["coord_ra, coord_dec, " +
                          "scisql_nanojanskyToAbMag(u_sersicFlux) AS u_sersicMag, " +
                          "scisql_nanojanskyToAbMag(g_sersicFlux) AS g_sersicMag, " +
                          "scisql_nanojanskyToAbMag(r_sersicFlux) AS r_sersicMag, " +
                          "scisql_nanojanskyToAbMag(i_sersicFlux) AS i_sersicMag, " +
                          "scisql_nanojanskyToAbMag(z_sersicFlux) AS z_sersicMag, " +
                          "scisql_nanojanskyToAbMag(y_sersicFlux) AS y_sersicMag",
                          "dp1.Object", grabcone(field), ""]
queries.loc['sigma']   = ["coord_ra, coord_dec, " +
                          "scisql_nanojanskyToAbMag(g_sersicFlux) AS g_sersicMag, " +
                          "scisql_nanojanskyToAbMagSigma(g_sersicFlux, g_sersicFluxErr) AS g_sersicMagErr",
                          "dp1.Object", grabcone(field), ""]
queries.loc['dif2clr'] = ["coord_ra, coord_dec, " +
                          "u_cModelMag - g_cModelMag AS ug_clr, " +
                          "g_cModelMag - r_cModelMag AS gr_clr, " +
                          "r_cModelMag - i_cModelMag AS ri_clr, " +
                          "i_cModelMag - z_cModelMag AS iz_clr, " +
                          "z_cModelMag - y_cModelMag AS zy_clr",
                          "dp1.Object", grabcone(field), ""]
queries

cols = queries.columns
def grabout(tisq):
    for idx, dat in tisq.iterrows():
        query = ''
        for col in cols:
            if dat[col] != '':
                query += f' {col.upper()} {dat[col]}'
        print(idx); print(query)
        if 'schema' not in dat['from']:
            job = service.submit_job(query)
            job.run()
            job.wait(phases=['COMPLETED', 'ERROR'])
            if job.phase == 'ERROR':
                job.raise_if_error()
            elif job.phase == 'COMPLETED':
                out = job.fetch_result().to_table()
        else:
            out = service.search(query).to_table()
        tisq.loc[idx, 'out'] = out
        print(type(out), len(out))
#       display(out)
        print()
grabout(queries)

queries

# list all schemas
queries.loc['schemas', 'out']
# 2 out of 4 are relevant to me: dp1, tap_schema

# list all table names from DP1 TAP schema
queries.loc['tables', 'out']
# column 'table_name' matches the list of 12 tables under Schema Browser > Data Preview 1 
# https://sdm-schemas.lsst.io/dp1.html

# list all columns from DP1 Object table
queries.loc['columns', 'out'].to_pandas()
# same as the 1296 rows in ./DP1_schema/Object.csv