102.5. Cross-match to uploaded table with TAP

102.5. Cross-match to uploaded table with TAP¶

For the Rubin Science Platform at data.lsst.cloud.
Data Release: Data Preview 2
Container Size: Large
LSST Science Pipelines version: r30.0.9
Last verified to run: 2026-07-20
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, SSObject table

Packages: lsst.rsp.RSPDiscovery

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/DP2 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, time, and astropy. Import the pyvo module for accessing remote data. From the lsst package, import the the RSPDiscovery class for accessing the Table Access Protocol (TAP) service.

In [1]:

import matplotlib.pyplot as plt
import numpy as np
import time
from astropy.table import Table, vstack
from astropy.coordinates import SkyCoord
import astropy.units as u
from astropy_healpix import HEALPix
import pyvo

from lsst.rsp import RSPDiscovery

1.2. Define parameters¶

Instantiate RSPDiscovery with the DP2 release, create an instance of the TAP service, and assert that it exists.

In [2]:

discovery = RSPDiscovery("dp2")
service = discovery.get_tap_client()
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 1,000 objects from the SDSS SkyServer within 0.5 degrees of the center of RA = 345 degrees and Dec = -5 degrees.

2.1. Load a table with coordinates¶

Read the user table as an astropy table.

In [3]:

path = '/rubin/cst_repos/tutorial-notebooks-data/data/'
ut1 = Table.read(path + 'dp2_102_user_table.csv')

Option to display the table.

In [4]:

# ut1

2.2. Execute the cross-match¶

To cross-match a user-uploaded table against the LSST catalogs and retrieve the results, Qserv allows users to temporarily upload their catalogs to Qserv and use them in queries. It is recommended to apply a DISTANCE-based join in coordinate-based cross-matching as demonstrated in this Section or use unique identifier-based joins (like ObjectId) as demonstrated in Section 3, for which Qserv automatically partitions the user-uploaded table using the same algorithm used for the LSST catalogs. This approach makes a query highly efficient by targeting only the relevant chunks of data.

Query the Object table for objects matching the coordinates from the user-defined table, ut1. Use a spatial cross-match radius of 1" (or 0.00027 degrees).

In [5]:

query = """
    SELECT ut1.objid AS ut1_objid, ut1.ra AS ut1_ra, ut1.dec AS ut1_dec, 
    dp2.objectId, dp2.coord_ra, dp2.coord_dec
    FROM dp2.Object AS dp2
    JOIN TAP_UPLOAD.ut1 AS ut1
    ON DISTANCE(POINT('ICRS', dp2.coord_ra, dp2.coord_dec),
                POINT('ICRS', ut1.ra, ut1.dec)) < 0.00027
    """

Submit the query to the TAP service.

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

results = job.fetch_result().to_table()

In [8]:

unique_matches = np.unique(results['ut1_objid'])
print(f"Returned {len(results)} matches with {len(unique_matches)} unique entries.")
print("Always check for duplicates!")

Returned 856 matches with 849 unique entries.
Always check for duplicates!

In [9]:

del results, unique_matches

2.2.1. Add column constraints¶

In the query above for coordinate-based cross-matching, constraints can also be placed on other columns in the uploaded tables.

The following query is identical to the one above, except that it:

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

In [10]:

query = """
    SELECT ut1.objid AS ut1_objid, ut1.ra AS ut1_ra, ut1.dec AS ut1_dec,
    ut1.r AS sdss_r, ABS(ut1.r - r_cModelMag) AS r_diffs,
    dp2.objectId, dp2.coord_ra, dp2.coord_dec, dp2.r_cModelMag
    FROM dp2.Object AS dp2
    JOIN TAP_UPLOAD.ut1 AS ut1 
    ON DISTANCE(POINT('ICRS', dp2.coord_ra, dp2.coord_dec), 
                POINT('ICRS', ut1.ra, ut1.dec)) < 0.00027
    WHERE ABS(ut1.r - dp2.r_cModelMag) < 0.1"""

Submit the query to the TAP service.

In [11]:

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

results = job.fetch_result().to_table()

In [13]:

unique_matches = np.unique(results['ut1_objid'])
print(f"Returned {len(results)} matches with {len(unique_matches)} unique entries.")

Returned 384 matches with 384 unique entries.

In [14]:

del results, unique_matches

3. Cross-match with a table of identifiers¶

Query an external database for solar system objects and cross-match it to the SSObject 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 [15]:

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

Query the Gaia sso_orbits table for main-belt asteroids (MBAs). Constrain the query to 1,000 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 [16]:

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 an astropy table named ut2.

In [17]:

gaia_job = gaia_tap.submit_job(gaia_query)
gaia_job.run()
gaia_job.wait(phases=['COMPLETED', 'ERROR'])
print('Job phase is', gaia_job.phase)
if gaia_job.phase == 'ERROR':
    gaia_job.raise_if_error()

ut2 = gaia_job.fetch_result().to_table()

Job phase is COMPLETED

Display the results of the query. Confirm that the query returned 1,000 rows.

In [18]:

ut2

Out[18]:

Table length=1000

denomination	inclination	eccentricity	semi_major_axis
	rad		AU
object	float64	float64	float64
mairepercy	0.036872739657094256	0.05338550332562588	2.6979047496130035
1999_cj138	0.025365800073234634	0.17064383850623568	2.150420617612988
1999_jw19	0.08687176993130774	0.09537641647614271	2.2857468927378832
2000_jb23	0.2008077410471647	0.19637433500656612	2.696545547950699
1999_cv14	0.2773846230591117	0.1742077595699058	3.1492334657780474
1998_fa85	0.22916828296800218	0.1764982562246874	2.6279412520253747
2000_dv103	0.08421208923670576	0.0946230410981651	2.5873967955326984
2001_qa140	0.23172314870191268	0.1765546829639623	2.612878613604526
5082_t-3	0.09722857212427716	0.14943538161518813	2.313023490713334
1998_do8	0.09628083417194455	0.10146827683163133	2.4484418176588405
2000_bz26	0.15211491732671378	0.12359349817617386	3.2046964324960827
2002_vr101	0.5074305449328427	0.13262570086155473	3.106616923229002
1995_ub47	0.17206524636054898	0.09056619298403552	2.9882212392965513
2000_wm60	0.1870884168148752	0.19139679545185267	2.7468220037435596
1997_yw5	0.10645677804248788	0.09350980468604563	2.7999580482664546
kuratowski	0.27474627357111614	0.14888462222568005	2.5634344278493075
2004_bb122	0.42292116999586105	0.21512554537281242	3.107688106678456
banerjee	0.1260140948751931	0.09365682414345071	2.4206722372450917
2001_un21	0.17400332910202287	0.06645663215704938	2.995934840704686
1997_bm1	0.04195881396927992	0.01541814398736991	2.660354048967379
dostoevsky	0.07878854710611605	0.0868217386622158	2.3867732476774792
2001_hw22	0.13923594770170133	0.1286249165160701	2.254316238899614
2003_wq56	0.05116621806261512	0.2034714534394073	2.192913406338791
lappajarvi	0.17993265846266482	0.17938794758313747	3.083534822332628
...	...	...	...
mcmillan	0.03757583387483243	0.14279899539146507	2.6360970957700083
2000_we24	0.15355814429757664	0.22039906598077336	3.070179858883083
2000_wp175	0.16262645372771933	0.16410304268692882	2.8886384381565806
neujmina	0.1502649515363803	0.08020054292457321	3.0255628988741
syoyou	0.048050044743316755	0.23494367586541495	3.045731403369943
1999_xc177	0.14556200894799143	0.23007536283293642	2.5367789941781425
2000_dh30	0.18023896738078649	0.08538189287478622	3.0663787636972177
1988_bg	0.2161117507482786	0.1616013446713822	2.6225077743831045
2004_rm236	0.24637568061190684	0.12628486001838757	2.6978131589807224
beiser	0.18629287638678949	0.1556812026815461	2.9872223103876516
2001_sr325	0.16907690733801117	0.032275362271589836	3.0708909689730888
2000_ap235	0.11789727955131002	0.14626065900764254	3.053198939020833
2001_ql87	0.26111084430264	0.0863248798564256	3.1265527101468167
1999_tm1	0.14693459820210353	0.05631920945948141	2.226012013327621
2000_gw106	0.1550237425181844	0.13698736289309627	2.775448697785905
1995_aw2	0.09418917379794767	0.11483361734883687	2.2725714931750547
2000_vm10	0.153295018809384	0.1070124343560502	3.2180910430283642
2001_fc14	0.09644597933511201	0.09404850328374817	3.1424695642467615
rolandflorrie	0.22285462960748292	0.14139373811589226	2.6376665415388065
furmanov	0.14090496143591813	0.12287590363453968	2.601075337069033
1991_rt14	0.2669135091119601	0.10207098949650313	2.769935155138743
2001_tt92	0.1475548137136644	0.10658723610290885	2.72592875213448
2000_bp3	0.04660650842617731	0.10507706958556094	3.1483310547882533
1999_jo28	0.19381364060041406	0.15900522621846572	2.6834330863338667
1981_dt1	0.13760980302470438	0.20735088494093865	2.7598572951825826

3.2. Manipulate the Gaia results table¶

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

In [19]:

ut2['mpc_designation'] = np.char.upper(np.char.replace(ut2['denomination'].astype('U'), '_', ' '))

Display the updated table.

In [20]:

ut2

Out[20]:

Table length=1000

denomination	inclination	eccentricity	semi_major_axis	mpc_designation
	rad		AU
object	float64	float64	float64	str16
mairepercy	0.036872739657094256	0.05338550332562588	2.6979047496130035	MAIREPERCY
1999_cj138	0.025365800073234634	0.17064383850623568	2.150420617612988	1999 CJ138
1999_jw19	0.08687176993130774	0.09537641647614271	2.2857468927378832	1999 JW19
2000_jb23	0.2008077410471647	0.19637433500656612	2.696545547950699	2000 JB23
1999_cv14	0.2773846230591117	0.1742077595699058	3.1492334657780474	1999 CV14
1998_fa85	0.22916828296800218	0.1764982562246874	2.6279412520253747	1998 FA85
2000_dv103	0.08421208923670576	0.0946230410981651	2.5873967955326984	2000 DV103
2001_qa140	0.23172314870191268	0.1765546829639623	2.612878613604526	2001 QA140
5082_t-3	0.09722857212427716	0.14943538161518813	2.313023490713334	5082 T-3
1998_do8	0.09628083417194455	0.10146827683163133	2.4484418176588405	1998 DO8
2000_bz26	0.15211491732671378	0.12359349817617386	3.2046964324960827	2000 BZ26
2002_vr101	0.5074305449328427	0.13262570086155473	3.106616923229002	2002 VR101
1995_ub47	0.17206524636054898	0.09056619298403552	2.9882212392965513	1995 UB47
2000_wm60	0.1870884168148752	0.19139679545185267	2.7468220037435596	2000 WM60
1997_yw5	0.10645677804248788	0.09350980468604563	2.7999580482664546	1997 YW5
kuratowski	0.27474627357111614	0.14888462222568005	2.5634344278493075	KURATOWSKI
2004_bb122	0.42292116999586105	0.21512554537281242	3.107688106678456	2004 BB122
banerjee	0.1260140948751931	0.09365682414345071	2.4206722372450917	BANERJEE
2001_un21	0.17400332910202287	0.06645663215704938	2.995934840704686	2001 UN21
1997_bm1	0.04195881396927992	0.01541814398736991	2.660354048967379	1997 BM1
dostoevsky	0.07878854710611605	0.0868217386622158	2.3867732476774792	DOSTOEVSKY
2001_hw22	0.13923594770170133	0.1286249165160701	2.254316238899614	2001 HW22
2003_wq56	0.05116621806261512	0.2034714534394073	2.192913406338791	2003 WQ56
lappajarvi	0.17993265846266482	0.17938794758313747	3.083534822332628	LAPPAJARVI
...	...	...	...	...
mcmillan	0.03757583387483243	0.14279899539146507	2.6360970957700083	MCMILLAN
2000_we24	0.15355814429757664	0.22039906598077336	3.070179858883083	2000 WE24
2000_wp175	0.16262645372771933	0.16410304268692882	2.8886384381565806	2000 WP175
neujmina	0.1502649515363803	0.08020054292457321	3.0255628988741	NEUJMINA
syoyou	0.048050044743316755	0.23494367586541495	3.045731403369943	SYOYOU
1999_xc177	0.14556200894799143	0.23007536283293642	2.5367789941781425	1999 XC177
2000_dh30	0.18023896738078649	0.08538189287478622	3.0663787636972177	2000 DH30
1988_bg	0.2161117507482786	0.1616013446713822	2.6225077743831045	1988 BG
2004_rm236	0.24637568061190684	0.12628486001838757	2.6978131589807224	2004 RM236
beiser	0.18629287638678949	0.1556812026815461	2.9872223103876516	BEISER
2001_sr325	0.16907690733801117	0.032275362271589836	3.0708909689730888	2001 SR325
2000_ap235	0.11789727955131002	0.14626065900764254	3.053198939020833	2000 AP235
2001_ql87	0.26111084430264	0.0863248798564256	3.1265527101468167	2001 QL87
1999_tm1	0.14693459820210353	0.05631920945948141	2.226012013327621	1999 TM1
2000_gw106	0.1550237425181844	0.13698736289309627	2.775448697785905	2000 GW106
1995_aw2	0.09418917379794767	0.11483361734883687	2.2725714931750547	1995 AW2
2000_vm10	0.153295018809384	0.1070124343560502	3.2180910430283642	2000 VM10
2001_fc14	0.09644597933511201	0.09404850328374817	3.1424695642467615	2001 FC14
rolandflorrie	0.22285462960748292	0.14139373811589226	2.6376665415388065	ROLANDFLORRIE
furmanov	0.14090496143591813	0.12287590363453968	2.601075337069033	FURMANOV
1991_rt14	0.2669135091119601	0.10207098949650313	2.769935155138743	1991 RT14
2001_tt92	0.1475548137136644	0.10658723610290885	2.72592875213448	2001 TT92
2000_bp3	0.04660650842617731	0.10507706958556094	3.1483310547882533	2000 BP3
1999_jo28	0.19381364060041406	0.15900522621846572	2.6834330863338667	1999 JO28
1981_dt1	0.13760980302470438	0.20735088494093865	2.7598572951825826	1981 DT1

3.3. Cross-match to the SSObject table¶

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

In [21]:

query = """
    SELECT ut2.mpc_designation AS ut2_mpcDesignation, sso.designation, sso.ssObjectId
    FROM dp2.SSObject AS sso
    JOIN TAP_UPLOAD.ut2 as ut2
    ON ut2.mpc_designation = sso.designation"""

Run the query and upload the user-defined table.

In [22]:

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

results = job.fetch_result().to_table()

Display the results of the query.

In [24]:

results

Out[24]:

Table length=183

ut2_mpcDesignation	designation	ssObjectId
str16	str16	int64
1967 US	1967 US	20888759556911187
1979 OH1	1979 OH1	20889867557810504
1986 WP2	1986 WP2	20890954318754384
1988 RO11	1988 RO11	20890962824868175
1989 EJ1	1989 EJ1	20890966901666122
1990 RH2	1990 RH2	20892027976692296
1990 SB6	1990 SB6	20892027993470530
1990 XF	1990 XF	20892028077355078
1991 EO5	1991 EO5	20892032053556559
1991 PY14	1991 PY14	20892032238171225
1991 RC9	1991 RC9	20892032271661379
1991 VU2	1991 VU2	20892032338768469
1992 EV13	1992 EV13	20892036348588886
1992 EC18	1992 EC18	20892036348590147
1993 FU4	1993 FU4	20892040660268117
1993 TJ31	1993 TJ31	20892040895344970
1994 CG12	1994 CG12	20892044904968775
1994 GY10	1994 GY10	20892044972077145
1994 PW27	1994 PW27	20892045123139415
1994 WH1	1994 WH1	20892045240447304
1995 SH70	1995 SH70	20892049468764232
1996 HC16	1996 HC16	20892053578790467
1996 LZ	1996 LZ	20892053645832282
1996 OV2	1996 OV2	20892053696164438
1996 TG12	1996 TG12	20892053780116039
...	...	...
2001 SL289	2001 SL289	21163611662793036
2001 TS195	2001 TS195	21163611678979411
2001 VA49	2001 VA49	21163611711093057
2001 XT119	2001 XT119	21163611745565012
2001 YP92	2001 YP92	21163611761750608
2002 BH25	2002 BH25	21163615670383944
2002 CG5	2002 CG5	21163615687030087
2002 CZ49	2002 CZ49	21163615687293274
2002 CN116	2002 CN116	21163615688209998
2002 CL233	2002 CL233	21163615688995660
2002 EF87	2002 EF87	21163615721109318
2002 JU21	2002 JU21	21163615804600661
2002 PX118	2002 PX118	21163615906314328
2003 ML12	2003 ML12	21163620149834316
2003 QH67	2003 QH67	21163620217272136
2003 SK99	2003 SK99	21163620251023691
2003 SJ151	2003 SJ151	21163620251873610
2003 SY298	2003 SY298	21163620252792921
2003 UB187	2003 UB187	21163620285626178
2004 TD137	2004 TD137	21163624563488580
2005 EW227	2005 EW227	21163628607387479
2005 EB272	2005 EB272	21163628607713858
2005 XA64	2005 XA64	21163628924646465
2009 OG5	2009 OG5	21163645953127751
6520 P-L	6520 P-L	22601918412304944

In [25]:

del results, ut2

In [ ]: