102.2. Catalog queries with TAP#

102_2_Catalog_queries_with_TAP.r29_2_0

102.2. Catalog queries with TAP¶

For the Rubin Science Platform at data.lsst.cloud.
Data Release: Data Preview 1
Container Size: large
LSST Science Pipelines version: r29.2.0
Last verified to run: 2025-09-02
Repository: github.com/lsst/tutorial-notebooks

Learning objective: How to execute complex ADQL TAP queries on catalogs.

LSST data products: Object table

Packages: lsst.rsp.get_tap_service

Credit: Originally developed by the Rubin Community Science team. Please consider acknowledging them if this notebook is used for the preparation of journal articles, software releases, or other notebooks.

Get Support: Everyone is encouraged to ask questions or raise issues in the Support Category of the Rubin Community Forum. Rubin staff will respond to all questions posted there.

1. Introduction¶

TAP provides standardized access to catalog data for discovery, search, and retrieval. Full documentation for TAP is provided by the International Virtual Observatory Alliance (IVOA).

ADQL (Astronomy Data Query Language) is similar to SQL (Structured Query Langage). The documentation for ADQL includes more information about syntax and keywords. Not all ADQL functionality is supported yet in the preview-era RSP.

Related tutorials: The other 100-level tutorials in this series demonstrate the TAP basics. The 200-level tutorials describe the contents of the catalog data and have example queries for each.

1.1. Import packages¶

Import general python packages numpy and the RSP TAP service.

In [1]:

import numpy as np
from lsst.rsp import get_tap_service

1.2. Define parameters¶

Instantiate the TAP service.

In [2]:

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

2. Manipulate columns¶

Start with a basic query that would execute a cone search on the Object table for objects within 0.01 degrees of coordinates near the center of the ECDFS field, RA, Dec = 53, -28 degrees.

In [3]:

query = """SELECT coord_ra, coord_dec
        FROM dp1.Object
        WHERE CONTAINS(POINT('ICRS', coord_ra, coord_dec),
        CIRCLE('ICRS', 53, -28, 0.01)) = 1"""
print(query)

SELECT coord_ra, coord_dec
        FROM dp1.Object
        WHERE CONTAINS(POINT('ICRS', coord_ra, coord_dec),
        CIRCLE('ICRS', 53, -28, 0.01)) = 1

2.1. Rename columns¶

Columns can be renamed using AS in the query.

Rename coord_ra as ra, and coord_dec as dec.

In [4]:

query = """SELECT coord_ra AS ra, coord_dec AS dec
        FROM dp1.Object
        WHERE CONTAINS(POINT('ICRS', coord_ra, coord_dec),
        CIRCLE('ICRS', 53, -28, 0.01)) = 1"""
print(query)

SELECT coord_ra AS ra, coord_dec AS dec
        FROM dp1.Object
        WHERE CONTAINS(POINT('ICRS', coord_ra, coord_dec),
        CIRCLE('ICRS', 53, -28, 0.01)) = 1

Execute the query.

In [5]:

job = service.submit_job(query)
job.run()
job.wait(phases=['COMPLETED', 'ERROR'])
print('Job phase is', job.phase)
if job.phase == 'ERROR':
    job.raise_if_error()
assert job.phase == 'COMPLETED'
results = job.fetch_result().to_table()

Job phase is COMPLETED

Show that the columns were renamed.

In [6]:

results.colnames

Out[6]:

['ra', 'dec']

In [7]:

job.delete()
del query, results

2.2. Apply ADQL functions¶

A variety of mathematical, trigonometrical, and geometrical functions can be applied to columns, with the results stored as columns in the returned array. See the ADQL documentation for the full list.

Use the same query as above but convert the coordinates from degrees to radians, and rename the columns ra_radians and dec_radians. Also retrieve the 2D sky distance between each object and the search coordinates as distance.

In [8]:

query = """SELECT RADIANS(coord_ra) AS ra_rad,
        RADIANS(coord_dec) AS dec_rad,
        DISTANCE(POINT('ICRS', coord_ra, coord_dec),
        POINT('ICRS', 53, -28)) AS distance
        FROM dp1.Object
        WHERE CONTAINS(POINT('ICRS', coord_ra, coord_dec),
        CIRCLE('ICRS', 53, -28, 0.01)) = 1"""
print(query)

SELECT RADIANS(coord_ra) AS ra_rad,
        RADIANS(coord_dec) AS dec_rad,
        DISTANCE(POINT('ICRS', coord_ra, coord_dec),
        POINT('ICRS', 53, -28)) AS distance
        FROM dp1.Object
        WHERE CONTAINS(POINT('ICRS', coord_ra, coord_dec),
        CIRCLE('ICRS', 53, -28, 0.01)) = 1

In [9]:

job = service.submit_job(query)
job.run()
job.wait(phases=['COMPLETED', 'ERROR'])
print('Job phase is', job.phase)
if job.phase == 'ERROR':
    job.raise_if_error()
assert job.phase == 'COMPLETED'
results = job.fetch_result().to_table()

Job phase is COMPLETED

Display only the first row of the table.

In [10]:

results[0]

Out[10]:

Row index=0

ra_rad	dec_rad	distance
float64	float64	float64
0.9250236455912983	-0.488863082199624	0.009791465987706714

In [11]:

job.delete()
del query, results

2.3. Convert fluxes to magnitudes¶

Photometric measurements for LSST objects and sources are stored in the catalogs as fluxes with units of nanoJanskys (nJy).

The conversion from nJy to AB magnitude is $m_{\rm AB} = -2.5 \log(f_{\rm nJy}) + 31.4$.

Warning: Difference-image fluxes and forced photometry fluxes can be negative; take care with magnitude conversions.

Functions exist to return fluxes (and errors) as AB magnitudes:

scisql_nanojanskyToAbMag
scisql_nanojanskyToAbMagSigma

The Object catalog has magnitude columns corresponding to the PSF and cModel fluxes, but other columns (and other tables) have only fluxes, not magnitudes.

The query below returns the coordinates, the g- and r-band cModel magnitudes, and converts the Sersic fluxes to AB magnitudes and retrieves them.

In [12]:

query = """SELECT coord_ra, coord_dec,
        g_cModelMag, g_cModelMagErr,
        r_cModelMag, r_cModelMagErr,
        scisql_nanojanskyToAbMag(g_sersicFlux) AS g_sersicMag,
        scisql_nanojanskyToAbMagSigma(g_sersicFlux, g_sersicFluxErr) AS g_sersicMagErr,
        scisql_nanojanskyToAbMag(r_sersicFlux) AS r_sersicMag,
        scisql_nanojanskyToAbMagSigma(r_sersicFlux, r_sersicFluxErr) AS r_sersicMagErr
        FROM dp1.Object
        WHERE CONTAINS(POINT('ICRS', coord_ra, coord_dec),
        CIRCLE('ICRS', 53, -28, 0.01)) = 1"""
print(query)

SELECT coord_ra, coord_dec,
        g_cModelMag, g_cModelMagErr,
        r_cModelMag, r_cModelMagErr,
        scisql_nanojanskyToAbMag(g_sersicFlux) AS g_sersicMag,
        scisql_nanojanskyToAbMagSigma(g_sersicFlux, g_sersicFluxErr) AS g_sersicMagErr,
        scisql_nanojanskyToAbMag(r_sersicFlux) AS r_sersicMag,
        scisql_nanojanskyToAbMagSigma(r_sersicFlux, r_sersicFluxErr) AS r_sersicMagErr
        FROM dp1.Object
        WHERE CONTAINS(POINT('ICRS', coord_ra, coord_dec),
        CIRCLE('ICRS', 53, -28, 0.01)) = 1

In [13]:

job = service.submit_job(query)
job.run()
job.wait(phases=['COMPLETED', 'ERROR'])
print('Job phase is', job.phase)
if job.phase == 'ERROR':
    job.raise_if_error()
assert job.phase == 'COMPLETED'
results = job.fetch_result().to_table()

Job phase is COMPLETED

In [14]:

results

Out[14]:

Table length=141

coord_ra	coord_dec	g_cModelMag	g_cModelMagErr	r_cModelMag	r_cModelMagErr	g_sersicMag	g_sersicMagErr	r_sersicMag	r_sersicMagErr
deg	deg	mag	mag	mag	mag
float64	float64	float32	float32	float32	float32	float64	float64	float64	float64
52.999950842186635	-28.009791369795494	25.3058	0.0408128	24.8951	0.037891	25.289549452182214	0.040237818711488176	24.892949378203916	0.03766112794964982
53.000647668093336	-28.00041790185584	25.0412	0.0298784	24.4576	0.0226485	24.866171833290277	0.0292662574085082	24.292082739141648	0.022401841938212753
52.99865025242912	-28.007343580438622	24.9923	0.0309844	24.7067	0.0317504	24.90763455079954	0.030462383351975278	24.630331766822504	0.03144153073250518
53.00440835952543	-28.00706527664946	25.615	0.0891581	24.0388	0.021853	25.824036556658854	0.08788197304022516	23.975996228411077	0.021711488954269238
52.99728049236805	-28.004763783217346	24.1276	0.0187086	23.7754	0.020053	23.905462678258765	0.01842487813911017	23.66474104374684	0.019896250251999065
52.998611316150786	-28.007966170045258	24.6015	0.0201114	24.1979	0.0186312	24.604628401562163	0.019900164058944833	24.217741365529697	0.01851201132941137
52.99610446568829	-28.00392847688764	25.7216	0.0553019	24.699	0.028772	25.51758445383836	0.05332594940368145	24.509594032041257	0.028202493409365273
52.994574524381925	-27.997372314676916	24.6329	0.0226044	23.8767	0.0155296	24.588047465995317	0.022427202238376947	23.902507096802537	0.015430592488508811
52.998335112826375	-28.003816346850325	24.2705	0.0164768	24.1018	0.0186653	24.28554955290755	0.016401610324314392	24.121507128684378	0.018583906770695945
52.99727214731918	-27.995802683902657	24.1665	0.0148334	23.9779	0.0159933	24.05499024455791	0.01472144188486561	23.86914537758115	0.015866300626627625
52.99448158796685	-28.004930968057806	25.4552	0.0485347	24.5962	0.0287135	25.449133222023576	0.04758318208775367	24.59559267560771	0.028457169418620278
53.00329626602324	-28.00003899309067	26.4099	0.12389	25.1792	0.0553923	26.214156107023598	0.12011723028055703	25.066084862462944	0.05502026080091147
52.995155520103665	-28.001850964307206	25.2018	0.032764	24.9675	0.0340522	24.98366181547818	0.03153300679336374	24.747833555881364	0.03296765049953945
52.998350813029674	-27.998949176106738	24.9673	0.036206	24.4623	0.0292279	24.689558070181704	0.03559698277072439	24.190417616723618	0.028889488895029118
52.99775326969135	-28.002351573160492	23.9493	0.0181754	23.7738	0.0204255	23.966454620658244	0.01784050507271788	23.81095705407506	0.020293583897009082
53.00136383200885	-28.00378700494794	24.1718	0.0148051	22.5728	0.00462062	24.12388626731189	0.014574249214755933	22.532669787765567	0.004566907128197098
52.998141835898004	-27.99448571126122	23.474	0.00780369	22.8152	0.00553536	23.471800778385468	0.007746273093980313	22.826360671596085	0.00550083209919062
53.00348062615347	-27.998557634775082	24.3642	0.021632	22.8599	0.00721825	24.18665519491221	0.020860959923297534	22.70293818046334	0.007074329470682065
...	...	...	...	...	...	...	...	...	...
53.00721376470392	-28.001180170512843	25.8221	0.0705159	25.138	0.0547828	25.643071406300145	0.0695114984063437	25.062861961887055	0.054008888233512506
53.00731182899547	-28.007278522078224	26.5402	0.116733	25.7899	0.0781803	26.51143392456686	0.11444293325696969	25.749709662903598	0.07567945095039222
52.996154273849754	-28.00745531725093	26.5604	0.139	26.2221	0.136156	25.797519548521954	0.09593058005399466	25.621443205734508	0.10922534251617994
53.00652419077008	-27.996847460472292	26.4912	0.104924	25.8685	0.0768782	26.216381835835843	0.10326724647430342	25.57709124155874	0.07522092856933878
53.00959690484238	-27.995582825205606	26.1223	0.103543	26.0045	0.122761	25.84619747536304	0.09927947435859197	25.77521978879163	0.12274444058105709
53.00468263321903	-27.993287377947603	23.0769	0.00549674	22.2739	0.00348814	23.048143772478458	0.0054415511451337386	22.25616738068976	0.003464440490055301
52.99651645707511	-27.993612805089697	25.2278	0.0368774	24.5424	0.0250163	25.129143995230777	0.03646948837143515	24.44799918035816	0.024823940859456724
53.0024143070743	-27.994569940599067	29.1129	1.16653	28.1966	0.643018	28.11391580977372	0.8523796476347621	27.435392873223932	0.5926338475351709
53.00683694050946	-27.995182293985668	26.9208	0.206666	27.1901	0.355787	26.726780897459953	0.20325626559192458	27.00931080069696	0.347245559971689
53.00624909377419	-27.992036216815464	25.0768	0.0288796	23.8358	0.0119606	25.070734802074092	0.028472772676904644	23.831714466054347	0.011824806749270257
53.00766969633569	-27.993399513118437	20.4858	0.000620706	19.3215	0.000333081	20.488225674135386	0.0005964093735726239	19.327588879321173	0.000313522792252015
53.00642044578477	-27.994667920805927	24.3085	0.0337899	24.1698	0.0395691	23.392352928047327	0.0332268011886009	23.19645761026413	0.03703635296876196
52.996802053288405	-27.9912332351428	28.5065	0.677682	32.7069	42.3778	28.228616805619353	0.7439242460744452	34.46578974634167	299.92762698447825
53.0031279692871	-27.99123590474861	29.5039	1.75463	29.4607	3.12418	31.701939724362447	25.793940395325283	31.092177473081613	19.884466632324333
53.003139507715005	-27.992142682076214	29.3874	1.50775	28.666	1.00012	27.92504600267281	0.6554646047782284	27.728928845537737	0.7193995074034013
53.00136705577273	-27.990401541457896	26.7056	0.150559	26.6686	0.180899	26.253211444495825	0.15207798087462815	26.144998117227367	0.18148483107217858
53.001939897066684	-27.990603538058334	25.7765	0.07207	25.7781	0.0942976	25.506527466972184	0.06893346762410889	25.482099994870694	0.08844322213745942
53.00036920322047	-27.991722047885638	27.4609	0.267638	26.5169	0.143943	26.846490281567256	0.23935535511200468	26.0771889088126	0.15246200938368937
53.00070612505027	-27.99198924376921	26.9852	0.227693	26.5283	0.182682	26.740069361434774	0.244807886464271	26.109630148347108	0.17823406636097816

In [15]:

job.delete()
del query, results

2.4. Derive new columns¶

Retrieve "derived columns" that are the difference of two columns, in this case the cModel magnitudes of adjacent filters to obtain colors.

In [16]:

query = """SELECT 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
        FROM dp1.Object
        WHERE CONTAINS(POINT('ICRS', coord_ra, coord_dec),
        CIRCLE('ICRS', 53, -28, 0.01)) = 1"""
print(query)

SELECT 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
        FROM dp1.Object
        WHERE CONTAINS(POINT('ICRS', coord_ra, coord_dec),
        CIRCLE('ICRS', 53, -28, 0.01)) = 1

In [17]:

job = service.submit_job(query)
job.run()
job.wait(phases=['COMPLETED', 'ERROR'])
print('Job phase is', job.phase)
if job.phase == 'ERROR':
    job.raise_if_error()
assert job.phase == 'COMPLETED'
results = job.fetch_result().to_table()
len(results)

Job phase is COMPLETED

Out[17]:

In [18]:

print(results.colnames)

['coord_ra', 'coord_dec', 'ug_clr', 'gr_clr', 'ri_clr', 'iz_clr', 'zy_clr']

In [19]:

job.delete()
del query, results

3. Column constraints¶

3.1. Apply relational operators¶

In the queries above, only the = operator is used, but others are available, e.g., !=, <, >, <=, and >=.

Include query constraints on the magnitudes to return only objects that are brighter than 25th mag in all filters.

In [20]:

query = """SELECT 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
        FROM dp1.Object
        WHERE CONTAINS(POINT('ICRS', coord_ra, coord_dec),
        CIRCLE('ICRS', 53, -28, 0.01)) = 1
        AND u_cModelMag < 25 AND g_cModelMag < 25
        AND r_cModelMag < 25 AND i_cModelMag < 25
        AND z_cModelMag < 25 AND y_cModelMag < 25"""
print(query)

SELECT 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
        FROM dp1.Object
        WHERE CONTAINS(POINT('ICRS', coord_ra, coord_dec),
        CIRCLE('ICRS', 53, -28, 0.01)) = 1
        AND u_cModelMag < 25 AND g_cModelMag < 25
        AND r_cModelMag < 25 AND i_cModelMag < 25
        AND z_cModelMag < 25 AND y_cModelMag < 25

In [21]:

job = service.submit_job(query)
job.run()
job.wait(phases=['COMPLETED', 'ERROR'])
print('Job phase is', job.phase)
if job.phase == 'ERROR':
    job.raise_if_error()
assert job.phase == 'COMPLETED'
results = job.fetch_result().to_table()

Job phase is COMPLETED

As an example, print the minimum and maximum values of the derived columns.

In [22]:

print(np.min(results['ug_clr']), np.max(results['ug_clr']))
print(np.min(results['gr_clr']), np.max(results['gr_clr']))
print(np.min(results['ri_clr']), np.max(results['ri_clr']))
print(np.min(results['iz_clr']), np.max(results['iz_clr']))
print(np.min(results['zy_clr']), np.max(results['zy_clr']))

-0.22574234008789062 2.437074661254883
0.03248786926269531 1.5774612426757812
-0.13392066955566406 0.775299072265625
-0.0517425537109375 0.5262928009033203
-0.6113967895507812 0.5244102478027344

In [23]:

job.delete()
del query, results

3.2. Apply operators to functions¶

Only the original column names can be used in a constraint.

For example, this attempt to create a column named g_sersicMag and then also apply a constraint to it will return an error.

SELECT coord_ra, coord_dec,
scisql_nanojanskyToAbMag(g_sersicFlux) AS g_sersicMag
FROM dp1.Object
WHERE CONTAINS(POINT('ICRS', coord_ra, coord_dec),
CIRCLE('ICRS', 53, -28, 0.01)) = 1
AND g_sersicMag < 25

The correct way to make this query is to use the original column name in the WHERE statement.

In [24]:

query = """SELECT coord_ra, coord_dec,
        scisql_nanojanskyToAbMag(g_sersicFlux) AS g_sersicMag
        FROM dp1.Object
        WHERE CONTAINS(POINT('ICRS', coord_ra, coord_dec),
        CIRCLE('ICRS', 53, -28, 0.01)) = 1
        AND scisql_nanojanskyToAbMag(g_sersicFlux) < 25"""
print(query)

SELECT coord_ra, coord_dec,
        scisql_nanojanskyToAbMag(g_sersicFlux) AS g_sersicMag
        FROM dp1.Object
        WHERE CONTAINS(POINT('ICRS', coord_ra, coord_dec),
        CIRCLE('ICRS', 53, -28, 0.01)) = 1
        AND scisql_nanojanskyToAbMag(g_sersicFlux) < 25

In [25]:

job = service.submit_job(query)
job.run()
job.wait(phases=['COMPLETED', 'ERROR'])
print('Job phase is', job.phase)
if job.phase == 'ERROR':
    job.raise_if_error()
assert job.phase == 'COMPLETED'
results = job.fetch_result().to_table()
print(len(results))

Job phase is COMPLETED
44

In [26]:

job.delete()
del query, results

3.3. Use WHERE ... BETWEEN ... AND¶

A constraint that one column's value is between two other columns' values can be applied with a statement like WHERE a BETWEEN b AND c.

Order matters!

WHERE a BETWEEN b AND c will return all rows for which $b < a < c$.
WHERE a BETWEEN c AND b will return all rows for which $c < a < b$.

For example, return all objects within the cone search region for which the PSF magnitudes are $g < r < i$. In other words, objects that are brighter in g-band than r-band, and brighter in r-band than i-band (i.e., blue objects).

In [27]:

query = """SELECT objectId, g_psfMag, r_psfMag, i_psfMag
        FROM dp1.Object
        WHERE CONTAINS(POINT('ICRS', coord_ra, coord_dec),
        CIRCLE('ICRS', 53, -28, 0.01)) = 1
        AND r_psfMag BETWEEN g_psfMag AND i_psfMag"""
print(query)

SELECT objectId, g_psfMag, r_psfMag, i_psfMag
        FROM dp1.Object
        WHERE CONTAINS(POINT('ICRS', coord_ra, coord_dec),
        CIRCLE('ICRS', 53, -28, 0.01)) = 1
        AND r_psfMag BETWEEN g_psfMag AND i_psfMag

In [28]:

job = service.submit_job(query)
job.run()
job.wait(phases=['COMPLETED', 'ERROR'])
print('Job phase is', job.phase)
if job.phase == 'ERROR':
    job.raise_if_error()
assert job.phase == 'COMPLETED'
results = job.fetch_result().to_table()
print(len(results))

Job phase is COMPLETED
3

In [29]:

for i in range(len(results)):
    print(results['objectId'][i],
          results['g_psfMag'][i],
          results['r_psfMag'][i],
          results['i_psfMag'][i])

611255141361789816 26.2232 26.2289 26.2984
611255141361790690 26.64 26.7169 27.3359
611255141361791996 28.4808 31.817 33.0504

In [30]:

job.delete()
del query, results

3.4. Use WHERE ... IN ()¶

The constraint option of WHERE <col1> IN (value1, value2, value3) is a simpler way of doing, e.g., WHERE <col1> = value1 OR <col1> = value2 OR <col1 = value3.

A scenario for using WHERE ... IN () is when the list of objectId are known. A list of up to ~50,000 identifiers can be passed.

Define id_list as a string, formatted as a tuple, of the objectId of the three blue objects identified in the previous section.

In [31]:

id_array = np.asarray((611255141361789816, 611255141361790690, 611255141361791996),
                      dtype='long')
id_list = "(" + ", ".join(str(value) for value in id_array) + ")"
print(id_list)

(611255141361789816, 611255141361790690, 611255141361791996)

Create a query to return the coordinates of the three objects.

In [32]:

query = """SELECT objectId, coord_ra, coord_dec
        FROM dp1.Object
        WHERE objectId IN {}""".format(id_list)
print(query)

SELECT objectId, coord_ra, coord_dec
        FROM dp1.Object
        WHERE objectId IN (611255141361789816, 611255141361790690, 611255141361791996)

In [33]:

job = service.submit_job(query)
job.run()
job.wait(phases=['COMPLETED', 'ERROR'])
print('Job phase is', job.phase)
if job.phase == 'ERROR':
    job.raise_if_error()
assert job.phase == 'COMPLETED'
results = job.fetch_result().to_table()

Job phase is COMPLETED

Display the results.

In [34]:

results

Out[34]:

Table length=3

objectId	coord_ra	coord_dec
	deg	deg
int64	float64	float64
611255141361789816	53.005323357996005	-28.00468291953882
611255141361790690	52.99438633549297	-27.99473439509563
611255141361791996	52.996802053288405	-27.9912332351428

In [35]:

job.delete()
del query, results

In [ ]: