102.6. Cross-match to uploaded table with TAP

102.6. Cross-match to uploaded table 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-22
Repository: github.com/lsst/tutorial-notebooks
DOI: 10.11578/rubin/dc.20250909.20

Learning objective: How to use the TAP service to cross-match to a user-uploaded table.

LSST data products: Object table, MPCORB 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).

Related tutorials: The other 100-level tutorials in this series demonstrate how to use the TAP service.

1.1. Import packages¶

Import general python packages matplotlib, numpy, and astropy. Import the pyvo module for accessing remote data. From the lsst package, import the module for accessing the Table Access Protocol (TAP) service.

In [1]:

import matplotlib.pyplot as plt
import numpy as np
from astropy.table import Table
import pyvo
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. Cross-match with a table of coordinates¶

Load a user-defined table to upload to the TAP service and spatially cross-match to the Object table, which includes detections in the coadded images.

This example uses a table of objects from the SDSS SkyServer within 0.5 degrees of the center of the northernmost DP1 field, the Low Ecliptic Latitude Field (RA = 37.86, Dec = 6.89).

2.1. Load a table with coordinates¶

Read the table as an astropy table.

In [3]:

path = '/rubin/cst_repos/tutorial-notebooks-data/data/'
ut1 = Table.read(path + 'dp1_102_6_user_table_SDSS.csv', format='ascii.csv')

Option to display the table.

In [4]:

# ut1

2.2. Cross-match to the Object table¶

Query the Object table for objects matching the coordinates from the user-defined table, ut1. Use a spatial cross-match radius of 1 arcsec and retrieve the objectId, coordinates, and cModel magnitudes.

In [5]:

query = "SELECT ut1.objid AS ut1_objid, "\
        "ut1.ra AS ut1_ra, ut1.dec AS ut1_dec, "\
        "objectId, coord_ra, coord_dec, "\
        "u_cModelMag, g_cModelMag, r_cModelMag, "\
        "i_cModelMag, z_cModelMag, y_cModelMag "\
        "FROM dp1.Object "\
        "JOIN TAP_UPLOAD.ut1 AS ut1 "\
        "WHERE CONTAINS(POINT('ICRS', coord_ra, coord_dec), "\
        "CIRCLE('ICRS', ut1.ra, ut1.dec, 0.00027))=1 "

Run the query and upload the user-defined table.

In [6]:

job = service.submit_job(query, uploads={"ut1": ut1})
job.run()
job.wait(phases=['COMPLETED', 'ERROR'])
print('Job phase is', job.phase)
if job.phase == 'ERROR':
    job.raise_if_error()

Job phase is COMPLETED

Retrieve the results.

In [7]:

assert job.phase == 'COMPLETED'
results = job.fetch_result().to_table()

Display the results of the query.

In [8]:

results

Out[8]:

Table length=8558

ut1_objid	ut1_ra	ut1_dec	objectId	coord_ra	coord_dec	u_cModelMag	g_cModelMag	r_cModelMag	i_cModelMag	z_cModelMag	y_cModelMag
				deg	deg	mag	mag	mag	mag	mag	mag
int64	float64	float64	int64	float64	float64	float32	float32	float32	float32	float32	float32
1237670017269367163	38.0578934775496	7.4277059696483	648368125565216675	38.0578955158831	7.427678283659483	--	22.3473	20.8504	19.1487	18.4172	--
1237670017269367135	38.0380097296434	7.4208625344998	648368125565216779	38.03799154665733	7.420815523624187	--	20.4955	19.9307	19.5524	19.5198	--
1237670017269367138	38.0405014279851	7.42558199894606	648368125565216865	38.04049812733134	7.425580160378057	--	20.8585	20.2295	19.7944	19.9894	--
1237670017269367194	38.0858491374902	7.42454384847825	648368125565216816	38.085855234143565	7.424561461984984	--	21.0139	20.002	19.3324	19.034	--
1237670017269367161	38.0561088297459	7.42484433760601	648368125565216820	38.05609481494671	7.4248269172911785	--	22.4634	22.0107	21.7796	21.7385	--
1237670017269366997	38.0345148104381	7.4302562915662	648368125565216616	38.03446533924158	7.430339999182441	--	21.4401	19.9154	18.5918	18.0448	--
1237670017269367024	38.0358699730184	7.42752170257929	648368125565216617	38.03588820804813	7.427517859611573	--	20.8136	20.288	20.0384	19.9923	--
1237670017269367361	38.0465434395526	7.41538105849588	648368125565215782	38.04654266667325	7.415426976515737	--	23.1355	22.2494	21.8968	21.6267	--
1237670017269367109	38.0264696213815	7.42906608949243	648368125565216618	38.02646930429519	7.429094194827333	--	22.3579	21.7662	21.4726	21.4218	--
...	...	...	...	...	...	...	...	...	...	...	...
1237680003065774789	38.2605421721046	6.7126589402092	648372798489635074	38.26052213261512	6.71269535424419	--	20.7357	19.5352	19.0385	18.777	--
1237680003065774815	38.2778318667821	6.71484705182021	648372798489635052	38.27785651825313	6.714768978994745	--	22.6205	21.862	21.3553	21.525	--
1237680003065774308	38.2822367883301	6.71349131691253	648372798489635128	38.28228840862636	6.713495137258602	--	19.2671	17.9494	16.9499	16.5073	--
1237680003065774733	38.2202222347535	6.72175054206644	648372798489635317	38.220227889044	6.721716384618791	--	22.1347	21.4248	21.0575	20.7602	--
1237680003065774691	38.1982135852548	6.72249714692861	648372798489635342	38.19821159150923	6.7225412408182175	--	21.545	20.7752	20.4199	20.2384	--
1237680003065774776	38.2424839276331	6.72272407702287	648372798489635366	38.24246884996766	6.722750396985676	--	22.5934	21.7246	21.0439	20.8355	--
1237680003065774724	38.215926516263	6.72260225163418	648372798489635322	38.21591213802974	6.7226326470092586	--	21.1112	20.4534	20.1532	20.0256	--
1237680003065774303	38.2567832724226	6.72286318889888	648372798489635285	38.25676650279843	6.72288514836295	--	19.8377	19.0242	18.6041	18.4021	--
1237680003065774948	38.198431224028	6.71973362889175	648372798489635267	38.198448369105975	6.719743410461669	--	23.5211	22.2351	21.5494	21.0067	--

2.3. Cross-match with column constraints¶

In addition to spatial constraints on the cross-match, constraints can also be placed on the values of other columns in the uploaded tables.

The following query is similar to the one in Section 2.2, except that it:

• returns the column r from ut1 as sdss_r (the $r$-band SDSS magnitude)
• returns the difference between DP1 and SDSS $r$-band magnitudes as r_diffs
• constrains the results to rows of ut1 with r > 17 mag
• constrains the results to cross-matches for which the $r$-band magnitudes agree within 0.1 mag

In [9]:

query_2 = "SELECT ut1.objid AS ut1_objid, "\
          "ut1.ra AS ut1_ra, ut1.dec AS ut1_dec, ut1.r AS sdss_r, "\
          "objectId, coord_ra, coord_dec, "\
          "u_cModelMag, g_cModelMag, r_cModelMag, "\
          "i_cModelMag, z_cModelMag, y_cModelMag, "\
          "ABS(ut1.r - r_cModelMag) AS r_diffs "\
          "FROM dp1.Object "\
          "JOIN TAP_UPLOAD.ut1 AS ut1 "\
          "WHERE CONTAINS(POINT('ICRS', coord_ra, coord_dec), "\
          "CIRCLE('ICRS', ut1.ra, ut1.dec, 0.00027))=1 "\
          "AND ut1.r > 17 AND ABS(ut1.r - r_cModelMag) < 0.1"

Submit the query to the TAP service.

In [10]:

job = service.submit_job(query_2, uploads={"ut1": ut1})
job.run()
job.wait(phases=['COMPLETED', 'ERROR'])
print('Job phase is', job.phase)
if job.phase == 'ERROR':
    job.raise_if_error()

Job phase is COMPLETED

Retrieve the results.

In [11]:

assert job.phase == 'COMPLETED'
results_2 = job.fetch_result().to_table()

Print the sizes of the original results table, and the results_2 table which imposed constraints on $r$-band magnitude.

In [12]:

print(len(results), len(results_2))

8558 4059

Option to display results_2.

In [13]:

# results_2

As an example visualization, plot the distributions of $r$-band Object table magnitudes for both the results table (spatial cross-match only) and the results_2 table (spatial cross-match with $r$-band magnitude constraints).

In [14]:

use_bins = np.arange(40, dtype='float')/2 + 7.5
fig = plt.figure(figsize=(6, 4))
plt.hist(ut1['r'], bins=use_bins, alpha=0.2, color='black', label='ut1, r')
plt.hist(results['r_cModelMag'], bins=use_bins, histtype='step',
         ls='solid', lw=1, label='results, r_cModelMag')
plt.hist(results_2['r_cModelMag'], bins=use_bins, histtype='step',
         ls='dashed', lw=2, label='results_2, r_cModelMag')
plt.xlim([15, 24])
plt.xlabel('r-band magnitude')
plt.ylabel('number')
plt.legend(loc='best')
plt.show()

Figure 1: Comparing the distribution of SDSS $r$-band magnitudes in the uploaded user table ut1 (grey); the distribution of DP1 r_cModelMag magnitudes resulting from a spatial cross-match to SDSS (blue solid); and the distribution of DP1 r_cModelMag magnitudes resulting from a spatial and magnitude-constrained cross-match to SDSS (orange dashed).

Clean up.

In [15]:

del results, results_2, query, query_2, ut1

3. Cross-match with table of identifiers¶

Query an external database for solar system objects and cross-match it to the MPCORB table using the Minor Planet Catalog (MPC) designation.

This example uses a table of moving objects retrieved from the Gaia database using PyVO.

3.1. Query the Gaia database using TAP¶

Instantiate the Gaia TAP service using PyVO.

In [16]:

gaia_tap_url = 'https://gea.esac.esa.int/tap-server/tap'
gaia_tap = pyvo.dal.TAPService(gaia_tap_url)
assert gaia_tap is not None
assert gaia_tap.baseurl == gaia_tap_url

Query the Gaia sso_orbits table for main-belt asteroids (MBAs). Constrain the query to 1000 objects, with semimajor axis a and q following the definition of MBA used by the JPL Horizons small body database query tool ( 2.0 < a < 3.25 au and q > 1.666 au).

In [17]:

gaia_query = "SELECT TOP 1000 denomination, inclination, "\
             "eccentricity, semi_major_axis "\
             "FROM gaiadr3.sso_orbits "\
             "WHERE num_observations > 200 "\
             "AND semi_major_axis > 2.0 "\
             "AND semi_major_axis < 3.25 "\
             "AND semi_major_axis*(1-eccentricity) > 1.666 "

Run the query and retrieve the results as a pandas DataFrame.

In [18]:

gaia_job = gaia_tap.submit_job(gaia_query)
gaia_job.run()
gaia_job.wait(phases=['COMPLETED', 'ERROR'])
print('Job phase is', gaia_job.phase)
assert gaia_job.phase == 'COMPLETED'
gaia_results = gaia_job.fetch_result().to_table().to_pandas()

Job phase is COMPLETED

Display the results of the query.

In [19]:

gaia_results

Out[19]:

	denomination	inclination	eccentricity	semi_major_axis
0	2000_so179	0.058380	0.120836	2.701355
1	1999_gu8	0.113790	0.208611	2.203320
2	2000_ge143	0.185379	0.074487	3.091090
3	5082_t-3	0.097229	0.149435	2.313023
4	2000_su299	0.227229	0.075321	3.169976
...	...	...	...	...
995	1999_jx102	0.107432	0.114787	3.137027
996	1993_fv82	0.049898	0.202413	2.189258
997	1999_jv24	0.267638	0.046316	2.570573
998	andrewchiang	0.147260	0.037843	3.062983
999	2001_qs256	0.079710	0.130844	2.671761

1000 rows × 4 columns

3.2. Reformat the Gaia results table¶

Format the denomination column in the Gaia results table to match the standard format for MPC designation used in the MPCORB table. Create a new column from the Gaia denomination column with underscores removed and letters capitalized.

In [20]:

gaia_results['mpc_designation'] = gaia_results['denomination'].str.replace('_', ' ').str.upper()

Option to display the table.

In [21]:

# gaia_results

Convert the pandas DataFrame to an astropy table and check the data type of the columns.

In [22]:

ut2 = Table.from_pandas(gaia_results)
ut2.dtype

Out[22]:

dtype([('denomination', '<U15'), ('inclination', '<f8'), ('eccentricity', '<f8'), ('semi_major_axis', '<f8'), ('mpc_designation', '<U15')])

Columns with data type Unicode are not supported for table uploads. Convert the data type from Unicode to bytestring.

In [23]:

ut2.convert_unicode_to_bytestring()

3.3. Cross-match to the MPCORB table¶

Query the MPCORB table for objects matching the MPC designation from the Gaia results table, ut2. Retrieve the SSObjectId for the matches.

In [24]:

query = "SELECT ut2.mpc_designation AS ut2_mpcDesignation, "\
        "mpc.mpcDesignation, mpc.ssObjectId "\
        "FROM dp1.MPCORB AS mpc "\
        "JOIN TAP_UPLOAD.ut2 as ut2 "\
        "ON ut2.mpc_designation = mpc.mpcDesignation "

Run the query and upload the user-defined table.

In [25]:

job = service.submit_job(query, uploads={"ut2": ut2})
job.run()
job.wait(phases=['COMPLETED', 'ERROR'])
print('Job phase is', job.phase)
if job.phase == 'ERROR':
    job.raise_if_error()

Job phase is COMPLETED

Retrieve the results.

In [26]:

assert job.phase == 'COMPLETED'
results = job.fetch_result().to_table()

Display the results of the query.

In [27]:

results

Out[27]:

Table length=774

ut2_mpcDesignation	mpcDesignation	ssObjectId
str15	str13	int64
2000 SO179	2000 SO179	21163607367104847
1999 GU8	1999 GU8	20892066446850133
2000 GE143	2000 GE143	21163607165580101
5082 T-3	5082 T-3	23700330511349810
2000 SU299	2000 SU299	21163607367891285
2000 ST343	2000 ST343	21163607368217428
1999 JC31	1999 JC31	20892066497376579
1999 RN133	1999 RN133	20892066632708942
1999 XO23	1999 XO23	20892066732192591
...	...	...
2001 DT2	2001 DT2	21163611408839252
2001 SK183	2001 SK183	21163611662136139
1999 XR215	1999 XR215	20892066733897042
1979 OH1	1979 OH1	20889867557810504
1999 GF35	1999 GF35	20892066447045958
1996 LZ	1996 LZ	20892053645832282
1999 JX102	1999 JX102	20892066498294360
1993 FV82	1993 FV82	20892040660791894
1999 JV24	1999 JV24	20892066497311830
2001 QS256	2001 QS256	21163611629041235

In [28]:

del results, query, ut2