SQLxMatch: In-Database Spatial Cross-Match of Astronomical Catalogs

SQLxMatch (or sequel cross match) is a SQL stored procedure that allows to perform 2-dimensional spatial cross-matches and cone searches across multiple astronomical catalogs stored in relational databases.

We have integrated SQLxMatch with more than 50 astronomical catalogs, and made those publicly available as tables in remote SQL Server databases through the CasJobs website, as part of the SciServer science platform ([1]). In CasJobs, users can directly execute form-free SQL queries for cross-matching, either synchronously or as asynchronous jobs. For general audiences, a simpler form-based interactive web interface that uses the CasJobs REST API for running cross-match queries will be soon available in the SkyServer astronomy portal. A stand-alone version of this web interface can accesed at https://skyserver.sdss.org/xmatch.

Documentation

Detailed documentation can be found HERE.

---

Demo

Here we will show how to run a cross-match between objects in the avaiable catalogs, including those in the Sloan Digital Sky Survey (SDSS).

In order to communicate and send queries to CasJobs, we need to import the CasJobs module from the SciServer python package. You will need to install this package if you are not running this notebook in SciServer-Compute.

from SciServer import CasJobs as cj
import pandas as pd
import numpy as np
pd.set_option('display.max_rows', 20)

Listing all catalogs

Here we run a SQL query to the Catalogs view that lists the names of all available catalogs. This query is run in the xmatch database context.

sql = "SELECT * FROM Catalogs ORDER BY catalog_name"
cj.executeQuery(sql, context='xmatch')

	catalog_name	summary	remarks	url
0	ACVS	ASAS Catalog of Variable Stars	\r\n The ASAS-3 Catalog of Variable Stars...	http://www.astrouw.edu.pl/asas/?page=main
1	AGC	Arecibo Galaxy Catalog	\r\n The AGC, or Arecibo General Catalog,...	http://caborojo.astro.cornell.edu/alfalfalog/i...
2	AKARI	AKARI Point Source Catalogues	\r\n AKARI (Previously known as ASTRO-F o...	http://www.ir.isas.jaxa.jp/AKARI/
3	CHANDRA	The Chandra Source Catalog, Release 1.1	\r\n The first official release of the CS...	http://cxc.cfa.harvard.edu/csc/
4	CNOC2	The Canadian Network for Observational Cosmol...	\r\n The Canadian Network for Observation...	http://www.astro.utoronto.ca/~cnoc/cnoc2.html
...	...	...	...	...
50	VVDS	The VIMOS VLT Deep Survey	\r\n A total of 11 564 objects have been ...	http://cesam.lam.fr/vvdsproject/index.html
51	WiggleZ	WiggleZ Dark Energy Survey Data Release 1	\r\n The WiggleZ Dark Energy Survey is a ...	http://wigglez.swin.edu.au/site/index.html
52	WISE	\tThe WISE All-Sky data Release	\n NASA's Wide-field Infrared Survey Expl...	http://wise2.ipac.caltech.edu/docs/release/all...
53	WMAP	\tNine-year WMAP point source catalogs	\r\n Nine-year WMAP point source catalogs	https://heasarc.gsfc.nasa.gov/W3Browse/radio-c...
54	zCOSMOS	zCOSMOS-bright DR2 catalog	\r\n zCOSMOS is a large-redshift survey t...	http://vizier.cfa.harvard.edu/viz-bin/VizieR?-...

55 rows × 4 columns

Listing tables in a catalog

The Tables view contains the tables available in all catalogs. We can use the WHERE clause to filter rows on the catalog_name column. Note that the table_name column is unique and written in the following format: <CatalogName>_<TableName>, and will be passed as parameter in the SQLxMatch stored procedure, as shown later.

sql = "SELECT * FROM Tables WHERE catalog_name = 'SDSSDR18'"
cj.executeQuery(sql, context='xmatch')

	catalog_name	table_name	table_type	description	schema_name
0	SDSSDR18	SDSSDR18_AncillaryTarget1	view	Contains the AncillaryTarget1 flag values fro...	dbo
1	SDSSDR18	SDSSDR18_AncillaryTarget2	view	Contains the AncillaryTarget2 flag values fro...	dbo
2	SDSSDR18	SDSSDR18_apogee_starhorse	view	StarHorse distance, extinction and other astr...	dbo
3	SDSSDR18	SDSSDR18_ApogeeAspcapFlag	view	Contains the ApogeeAspcapFlag flag values fro...	dbo
4	SDSSDR18	SDSSDR18_apogeeDesign	view	Contains the plate design information for APO...	dbo
...	...	...	...	...	...
269	SDSSDR18	SDSSDR18_zooMirrorBias	view	Results from the bias study using mirrored im...	dbo
270	SDSSDR18	SDSSDR18_zooMonochromeBias	view	Results from the bias study that introduced m...	dbo
271	SDSSDR18	SDSSDR18_zooNoSpec	view	Morphology classifications of galaxies withou...	dbo
272	SDSSDR18	SDSSDR18_zooSpec	view	Morphological classifications of spectroscopi...	dbo
273	SDSSDR18	SDSSDR18_zooVotes	view	Vote breakdown in Galaxy Zoo results.	dbo

274 rows × 5 columns

Listing columns in a table

Given a table name, we can run this SQL query containing the getTableColumns function in order to get more information about its columns.
This helps identifying the RA and Dec columns, as well as possible column on which we could impose filters.

sql = "SELECT * FROM Columns WHERE table_name = 'SDSSDR18_PhotoObj' ORDER BY column_index"
cj.executeQuery(sql, context='xmatch')

	catalog_name	table_name	column_name	description	unit	ucd	utype	datatype	size	precision	scale	column_index	schema_name
0	SDSSDR18	SDSSDR18_PhotoObj	objID					bigint	None	19	0.0	1	dbo
1	SDSSDR18	SDSSDR18_PhotoObj	skyVersion					tinyint	None	3	0.0	2	dbo
2	SDSSDR18	SDSSDR18_PhotoObj	run					smallint	None	5	0.0	3	dbo
3	SDSSDR18	SDSSDR18_PhotoObj	rerun					smallint	None	5	0.0	4	dbo
4	SDSSDR18	SDSSDR18_PhotoObj	camcol					tinyint	None	3	0.0	5	dbo
...	...	...	...	...	...	...	...	...	...	...	...	...	...
504	SDSSDR18	SDSSDR18_PhotoObj	TAI_u					float	None	53	NaN	505	dbo
505	SDSSDR18	SDSSDR18_PhotoObj	TAI_g					float	None	53	NaN	506	dbo
506	SDSSDR18	SDSSDR18_PhotoObj	TAI_r					float	None	53	NaN	507	dbo
507	SDSSDR18	SDSSDR18_PhotoObj	TAI_i					float	None	53	NaN	508	dbo
508	SDSSDR18	SDSSDR18_PhotoObj	TAI_z					float	None	53	NaN	509	dbo

509 rows × 13 columns

---

Cross-Match Examples

# Setting up search parameters. Search radius must be in arcseconds, the rest in degrees.

radius = 5

ra1 = 160
ra2 = 160.5
dec1 = 25
dec2 = 25.5

Quick cross-match of small catalogs - Pandas DataFrame output

As a quick example, here we cross-match two small infrared catalog tables: IRAS_PhotoObj and AKARI_FIS.

We can retrieve the cross-match output table as a Pandas dataframe by using the syncronous executeQuery function in CasJobs, as this cross-match takes less time than its 1 minute timeout.

The first input parameters are the names of 2 catalog tables, views, or temporary tables located in the CasJobs xmatch database context. This context already contains several table views from specific astronomical catalogs, and are named with the following format: <CatalogName>_<TableName>.

The input tables or views are expected to have at least one unique identifier column (e.g. objid, specobjid, etc), as well as an RA (Right Ascension) and Dec (Declination) columns.
However, the code will run faster if the input tables or views already contain the cx, cy, and cz columns (3-D cartesian coordinates of an object located on the surface of a unit-radius sphere).

The procedure returns by default a table as TABLE (id1, id2, sep), where id1 and id2 are the names of the columns that uniquely identify objects in the first and second catalogs, respectively, and sep is the angular separation between objetcs in arseconds.

%%time

sql = f"""
EXECUTE SQLxMatch @table1='IRAS_PhotoObj', @table2='AKARI_FIS', @radius=1

-- Note that we could be more explicit by specifying the columns names, but that was not needed.
--EXECUTE SQLxMatch @table1='IRAS_PhotoObj', @id_col1='objid', @ra_col1='ra', @dec_col1='dec',  
-- @table2='AKARI_FIS', @radius={radius}
"""

df = cj.executeQuery(sql, context="xmatch")
print(f"There are {df.shape[0]} matches.")
df

There are 322 matches.
CPU times: user 27.6 ms, sys: 9.34 ms, total: 36.9 ms
Wall time: 10 s

	id1	id2	sep
0	67986	3192456	0.986740
1	17257	3113227	0.798928
2	11682	3111531	0.591832
3	245973	3407403	0.117180
4	112694	3230998	0.812598
...	...	...	...
317	51335	3145058	0.242160
318	95801	3196760	0.977087
319	33112	3137825	0.621349
320	93117	3219425	0.991353
321	158301	3047423	0.971029

322 rows × 3 columns

Cross-matching two filtered catalogs - Pandas DataFrame output

Here we use two bigger tables (the PhotoObjAll tables in the BestDR18 and GalexDR6 catalogs), which we filter by ra and dec in rectangular regions and store in local temporary tables. These temporary tables are then passed as input to the SQLxMatch procedure.

Note that we can also store the (cx, cy, cz) columns in the temporary tables in order to avaoid having the code to compute them later internally on the fly.

Also, note that this time we also give @include_radec=1, @include_sep_rank=1, @sort_by_separation=1 as input parameters.

%%time

sql = f"""

SELECT objid, ra, dec, cx, cy, cz INTO #temp1
FROM SDSSDR18_PhotoObjAll WHERE ra BETWEEN {ra1} AND {ra2} AND dec BETWEEN {dec1} AND {dec2}

SELECT objid, ra, dec, cx, cy, cz INTO #temp2 
FROM GalexGR6_PhotoObjAll WHERE ra BETWEEN {ra1} AND {ra2} AND dec BETWEEN {dec1} AND {dec2}

EXECUTE SQLxMatch @table1='#temp1', @table2='#temp2', @radius={radius}, @include_radec=1, 
                  @include_sep_rank=1, @sort_by_separation=1
"""

df = cj.executeQuery(sql, context="xmatch")
print(f"There are {df.shape[0]} matches.")
df

There are 2198 matches.
CPU times: user 65.6 ms, sys: 12.1 ms, total: 77.7 ms
Wall time: 10.4 s

	id1	id2	sep	sep_rank	ra1	dec1	ra2	dec2
0	1237667322710261954	6387874658268479704	1.798951	1	160.008407	25.004219	160.008487	25.003724
1	1237667322710261955	6387874658268479704	1.792355	1	160.008402	25.004216	160.008487	25.003724
2	1237667322710262036	6387874659342225136	0.859082	1	160.028276	25.008489	160.028044	25.008602
3	1237667322710262036	6387874658268479663	3.983702	2	160.028276	25.008489	160.028181	25.007386
4	1237667322710262038	6387874658268479748	0.618532	1	160.034692	25.020967	160.034873	25.020916
...	...	...	...	...	...	...	...	...
2193	1237667430638813617	6387874658268481765	1.223999	1	160.046122	25.488607	160.046326	25.488322
2194	1237667430638813618	6387874658268481765	1.223999	1	160.046122	25.488607	160.046326	25.488322
2195	1237667430638813619	6387874658268481771	3.875215	1	160.048198	25.489805	160.049371	25.489999
2196	1237667430638813647	6387874658268481823	1.686612	1	160.057424	25.498517	160.057077	25.498168
2197	1237667430638813741	6387874658268481756	2.243794	1	160.096954	25.486217	160.097633	25.486106

2198 rows × 8 columns

Cross-Match beween 2 catalogs as an asynchronous job - Output to MyDB

When the cross-match is expected to take longer than the 1 minute timeout, it is recommendable to run it as an asynchronous job store the cross-match results asynchronous job into a table in your own MyDB database.

First, one must create the output table in MyDB, or delete if this table was already created by previous runs of this demo.

mydb_output_table_name = "xmatch_table"

sql = f"""
IF EXISTS (
SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'{mydb_output_table_name}') AND TYPE = 'U'
) DROP TABLE {mydb_output_table_name}  
CREATE TABLE {mydb_output_table_name}(id1 BIGINT, id2 BIGINT, sep FLOAT, sep_rank INTEGER, 
                                      ra1 FLOAT, dec1 FLOAT, ra2 FLOAT, dec2 FLOAT)
"""
df = cj.executeQuery(sql, context="mydb")

The result of the sp_xmatch procedure can be stored in a local temporary table specified by the @output_table input parameter. This output table can be then used to fill the table in MyDB.

%%time

sql = f"""
SELECT objid, ra, dec INTO #temp1
FROM SDSSDR18_PhotoObjAll WHERE ra BETWEEN {ra1} AND {ra2} AND dec BETWEEN {dec1} AND {dec2}

SELECT objid, ra, dec into #temp2 
FROM GALEXGR6_PhotoObjAll WHERE ra BETWEEN {ra1} AND {ra2} AND dec BETWEEN {dec1} AND {dec2}

-- Creating temporary output table
CREATE TABLE #out(id1 BIGINT, id2 BIGINT, sep FLOAT, sep_rank INTEGER, 
                  ra1 FLOAT, dec1 FLOAT, ra2 FLOAT, dec2 FLOAT)

-- Executing cross-match that fills output table.
EXECUTE SQLxMatch @table1='#temp1', @table2='#temp2', @radius={radius}, @output_table='#out', 
                  @include_radec=1, @include_sep_rank=1

-- Filling up table in MyDB:
INSERT INTO mydb.{mydb_output_table_name}
SELECT * from #out
"""

job_id = cj.submitJob(sql, context="xmatch")

# this line will make the code wait until the job is done, if desired:
job_description = cj.waitForJob(job_id)
print(f"Job Message: {job_description.get('Message')}")

Job Message: Query Complete
CPU times: user 124 ms, sys: 17.4 ms, total: 141 ms
Wall time: 17.2 s

We can now inspect the contents of the MyDB output table:

sql = f"""
SELECT * FROM mydb.{mydb_output_table_name} ORDER BY id1, sep
"""
df = cj.executeQuery(sql, context="mydb")
print(f"There are {df.shape[0]} matches.")
df

There are 2198 matches.

	id1	id2	sep	sep_rank	ra1	dec1	ra2	dec2
0	1237667322710261954	6387874658268479704	1.798951	1	160.008407	25.004219	160.008487	25.003724
1	1237667322710261955	6387874658268479704	1.792355	1	160.008402	25.004216	160.008487	25.003724
2	1237667322710262036	6387874659342225136	0.859082	1	160.028276	25.008489	160.028044	25.008602
3	1237667322710262036	6387874658268479663	3.983702	2	160.028276	25.008489	160.028181	25.007386
4	1237667322710262038	6387874658268479748	0.618532	1	160.034692	25.020967	160.034873	25.020916
...	...	...	...	...	...	...	...	...
2193	1237667430638813617	6387874658268481765	1.223999	1	160.046122	25.488607	160.046326	25.488322
2194	1237667430638813618	6387874658268481765	1.223999	1	160.046122	25.488607	160.046326	25.488322
2195	1237667430638813619	6387874658268481771	3.875215	1	160.048198	25.489805	160.049371	25.489999
2196	1237667430638813647	6387874658268481823	1.686612	1	160.057424	25.498517	160.057077	25.498168
2197	1237667430638813741	6387874658268481756	2.243794	1	160.096954	25.486217	160.097633	25.486106

2198 rows × 8 columns