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-12-12
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).

This tutorial demonstrates the workflow for ingesting user-provided tables into the TAP service to enable queries against the LSST/DP1 data. The system's ingestion capability automatically handles columns containing any variations of NaN and Inf values in uploaded tables; therefore, no manual pre-processing is needed.

Warning: When uploading plain text formats (such as CSV), explicitly convert all Boolean True/False strings to integers 1/0 to avoid ingestion errors. For metadata-rich formats like VOTable, ensure the column metadata is correctly defined as boolean.

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 [7]:

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 [8]:

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

Display the results of the query.

In [9]:

results

Out[9]:

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	--
...	...	...	...	...	...	...	...	...	...	...	...
1237667229835657731	38.0971024587478	7.2409522319144	648374928793414462	38.097083281734946	7.240922018720353	--	23.2127	21.5775	20.5752	20.2025	--
1237667229835657867	38.0935998181291	7.24329037912213	648374928793414463	38.09349762428832	7.243392786555413	--	23.8932	22.2135	20.9404	20.5039	--
1237667229835657253	38.1384702979749	7.23241321428244	648374928793414339	38.13848084199607	7.2324069230100045	--	15.4366	--	--	14.6934	--
1237667229835657482	38.1071505842876	7.23155265499485	648374928793414288	38.107102640448986	7.2316162026370465	--	22.0819	21.3545	20.9608	20.6706	--
1237667229835657317	38.1416853824342	7.2406076065846	648374928793414645	38.14176720812618	7.240644828967476	--	17.271	15.9404	15.3239	15.0183	--
1237667229835657318	38.1402679950435	7.24345978918223	648374928793414646	38.14024485213889	7.243715094894315	--	20.6254	19.1529	17.8856	17.333	--
1237667229835657489	38.1152028420074	7.24127411300906	648374928793414672	38.11518789712987	7.241423748566566	--	23.5192	22.1011	20.8045	20.2689	--
1237667229835657490	38.1163978432377	7.24283821598468	648374928793414673	38.11643211288625	7.242795319053565	--	21.9682	21.3781	20.9746	20.8572	--
1237667229835657310	38.1088365366613	7.23721185590736	648374928793414494	38.10885522610851	7.2372217991560275	--	18.9702	17.9939	17.5916	17.4302	--

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 [10]:

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 [11]:

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 [12]:

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 [13]:

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

8558 4059

Option to display results_2.

In [14]:

# 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 [15]:

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 [16]:

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 [17]:

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 [18]:

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 [19]:

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 [20]:

gaia_results

Out[20]:

	denomination	inclination	eccentricity	semi_major_axis
0	2000_vc34	0.080547	0.141541	3.136670
1	kalm	0.253986	0.059510	3.073346
2	1999_gc56	0.291257	0.130964	3.135344
3	1992_pt2	0.068843	0.143489	2.297147
4	2004_uq2	0.381016	0.036916	3.144319
...	...	...	...	...
995	2002_ep79	0.079210	0.076642	2.229149
996	balasridhar	0.118829	0.135759	2.330797
997	1998_yb31	0.239116	0.124303	2.669046
998	2000_xp29	0.230711	0.272915	2.991023
999	1993_rk6	0.250761	0.076172	2.662570

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 [21]:

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

Option to display the table.

In [22]:

# gaia_results

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

In [23]:

ut2 = Table.from_pandas(gaia_results)
ut2.dtype

Out[23]:

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 [24]:

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 [25]:

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 [26]:

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 [27]:

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

Display the results of the query.

In [28]:

results

Out[28]:

Table length=787

ut2_mpcDesignation	mpcDesignation	ssObjectId
str15	str13	int64
2000 VC34	2000 VC34	21163607416058947
1999 GC56	1999 GC56	20892066447177283
1992 PT2	1992 PT2	20892036533072468
2004 UQ2	2004 UQ2	21163624578953809
2002 CK24	2002 CK24	21163615687160907
1999 NR59	1999 NR59	20892066564618578
1991 UQ	1991 UQ	20892032321990737
2000 AF110	2000 AF110	21163607064719430
2000 WQ115	2000 WQ115	21163607433819473
...	...	...
2005 WM89	2005 WM89	21163628908001613
1990 XF	1990 XF	20892028077355078
1998 TX12	1998 TX12	20892062370050648
1988 RT4	1988 RT4	20890962824803412
1998 QM4	1998 QM4	20892062319653965
1999 NP64	1999 NP64	20892066564682832
2002 EP79	2002 EP79	21163615721044304
1998 YB31	1998 YB31	20892062454067522
2000 XP29	2000 XP29	21163607449549136
1993 RK6	1993 RK6	20892040861595211

In [29]:

del results, query, ut2