{ "cells": [ { "cell_type": "markdown", "id": "af16a9f7-d064-43c8-bf62-40423eae9165", "metadata": { "execution": { "iopub.execute_input": "2023-10-11T13:50:56.460499Z", "iopub.status.busy": "2023-10-11T13:50:56.459183Z", "iopub.status.idle": "2023-10-11T13:50:56.527799Z", "shell.execute_reply": "2023-10-11T13:50:56.525738Z", "shell.execute_reply.started": "2023-10-11T13:50:56.460121Z" } }, "source": [ "[![DOI](https://zenodo.org/badge/DOI/10.5281/zenodo.10142770.svg)](https://doi.org/10.5281/zenodo.10142770)\n", "# [**SQLxMatch: In-Database Spatial Cross-Match of Astronomical Catalogs**](https://github.com/sciserver/SQLxMatch)\n", "\n", "##### Manuchehr Taghizadeh-Popp 1* and Laszlo Dobos1,2\n", "1 Department of Physics and Astronomy, Johns Hopkins University, Baltimore, MD, USA.
\n", "2 Department of Physics of Complex Systems, Eötvös Loránd University, Budapest, Hungary.
\n", "* Leading contributor email: mtaghiza [at] jhu.edu | Help Desk: sciserver-helpdesk [at] jhu.edu\n", "

\n", "\n", "`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.\n", "This procedure implements the `Zones Algorithm` ([[1]](https://arxiv.org/abs/cs/0701171), [[2]](https://arxiv.org/abs/cs/0408031)), which leverages relational database algebra and B-Trees to cross-match the database tables or views containing the catalogs. To run a cross-match, these tables must simply contain at least the Right Ascension (RA) or Longitude, Declination (Dec) or Latitude, and unique object identifier (ID) columns.\n", "\n", "We have integrated `SQLxMatch` with more than 50 astronomical catalogs, and made those publicly available as tables in remote SQL Server databases `in the cloud` through the [CasJobs](https://skyserver.sdss.org/CasJobs) website, as part of the [SciServer](https://www.sciserver.org) science platform ([[3]](https://www.sciencedirect.com/science/article/abs/pii/S2213133720300664)). 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](http://skyserver.sdss.org/public/CrossMatchTools/crossmatch) that uses the CasJobs REST API for running cross-match queries will be soon available in the [SkyServer](http://skyserver.sdss.org) astronomy portal.
\n", "To improve cross-match query execution speed, we install `SQLxMatch` in a SQL Server database supported by fast NVMe storage in a RAID 6 configuration. We also place the catalog tables across several databases in the same physical server, thus avoiding having to move data across servers with a potentially slower network connection.\n", "\n", "The advantage of this `in-database` remote cross-match, compared to other `in-memory` local cross-match software libraries, is that the users leverage the remote database server's own (and potentially bigger) computing/memory/storage resources to filter and cross-match the full catalogs right away, having only a relatively small-sized cross-match output table returned to them.\n", "This can be faster and more efficient than having users to download the full catalogs into their own computers (if they have enough storage), and then load them in python for filtering and running the cross-match, for instance.\n", "\n", "#### **Citation**\n", "\n", "Taghizadeh-Popp, M. and Dobos, L. (2023) “SQLxMatch: In-Database Spatial Cross-Match of Astronomical Catalogs”. Zenodo. doi: 10.5281/zenodo.10142771.\n", "\n", "[![DOI](https://zenodo.org/badge/DOI/10.5281/zenodo.10142770.svg)](https://doi.org/10.5281/zenodo.10142770)\n", "\n", "\n", "\n", "### The SQLxMatch stored procedure\n", "\n", "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`.\n", "\n", "The input tables or views are expected to have at least one unique identifier column, as well as an `RA` (Right Ascension) and `Dec` (Declination) columns.
\n", "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), as well as the `zoneid` column (calculated from a zone height of \n", "\n", "Since the Zones Algorithm internally works with the 3-D cartesian coordinates of an object located on the surface of a unit-radius sphere (named as `cx`, `cy`, and `cz`), the code tries to find those exact names as columns possibly present in the catalog tables. If they are not present, then the code will calculate those cordinates on the fly, based on the values of the `RA` and `Dec` columns.\n", "\n", "If the column defining the zone ID (must be named `zoneid`) is missing, then the code will calculate it on the fly, based on the value of the input search radius `@radius` around each object.\n", "\n", "The procedure returns 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.\n", "\n", "\n", "INPUT PARAMETERS:
\n", "\n", "\n", "RETURNS:
\n", " \n" ] }, { "cell_type": "markdown", "id": "e340dd23-7a68-489f-a312-72db7443c547", "metadata": {}, "source": [ "---\n", "# Demo\n", "\n", "In order to communicate and send queries to CasJobs, we need to import the CasJobs module from the [SciServer python](https://github.com/sciserver/SciScript-python) package. You will need to install this package if you are not running this notebook in [SciServer-Compute](https://apps.sciserver.org/compute)." ] }, { "cell_type": "code", "execution_count": 1, "id": "556ced78-8c2f-4e11-988f-d95c892ca965", "metadata": { "execution": { "iopub.execute_input": "2024-08-01T12:53:20.327021Z", "iopub.status.busy": "2024-08-01T12:53:20.325473Z", "iopub.status.idle": "2024-08-01T12:53:22.337555Z", "shell.execute_reply": "2024-08-01T12:53:22.335116Z", "shell.execute_reply.started": "2024-08-01T12:53:20.326843Z" }, "tags": [] }, "outputs": [], "source": [ "from SciServer import CasJobs as cj\n", "import pandas as pd\n", "import numpy as np\n", "pd.set_option('display.max_rows', 20)" ] }, { "cell_type": "markdown", "id": "d1aeb758-dcf5-43cd-8012-12eac6fa5e6c", "metadata": {}, "source": [ "### Listing all catalogs\n", "\n", "\n", "Here we run a SQL query containing the `Catalogs` view that lists the names of all available catalogs.
" ] }, { "cell_type": "code", "execution_count": 2, "id": "ead71a6c-c121-479e-a4d3-6d7b41e9af89", "metadata": { "execution": { "iopub.execute_input": "2024-08-01T12:53:24.854987Z", "iopub.status.busy": "2024-08-01T12:53:24.854002Z", "iopub.status.idle": "2024-08-01T12:53:27.558325Z", "shell.execute_reply": "2024-08-01T12:53:27.556305Z", "shell.execute_reply.started": "2024-08-01T12:53:24.854886Z" }, "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
catalog_namesummaryremarksurl
0ACVSASAS Catalog of Variable Stars\\r\\n The ASAS-3 Catalog of Variable Stars...http://www.astrouw.edu.pl/asas/?page=main
1AGCArecibo Galaxy Catalog\\r\\n The AGC, or Arecibo General Catalog,...http://caborojo.astro.cornell.edu/alfalfalog/i...
2AKARIAKARI Point Source Catalogues\\r\\n AKARI (Previously known as ASTRO-F o...http://www.ir.isas.jaxa.jp/AKARI/
3CHANDRAThe Chandra Source Catalog, Release 1.1\\r\\n The first official release of the CS...http://cxc.cfa.harvard.edu/csc/
4CNOC2The Canadian Network for Observational Cosmol...\\r\\n The Canadian Network for Observation...http://www.astro.utoronto.ca/~cnoc/cnoc2.html
...............
50VVDSThe VIMOS VLT Deep Survey\\r\\n A total of 11 564 objects have been ...http://cesam.lam.fr/vvdsproject/index.html
51WiggleZWiggleZ Dark Energy Survey Data Release 1\\r\\n The WiggleZ Dark Energy Survey is a ...http://wigglez.swin.edu.au/site/index.html
52WISE\\tThe WISE All-Sky data Release\\n NASA's Wide-field Infrared Survey Expl...http://wise2.ipac.caltech.edu/docs/release/all...
53WMAP\\tNine-year WMAP point source catalogs\\r\\n Nine-year WMAP point source catalogshttps://heasarc.gsfc.nasa.gov/W3Browse/radio-c...
54zCOSMOSzCOSMOS-bright DR2 catalog\\r\\n zCOSMOS is a large-redshift survey t...http://vizier.cfa.harvard.edu/viz-bin/VizieR?-...
\n", "

55 rows × 4 columns

\n", "
" ], "text/plain": [ " catalog_name summary \\\n", "0 ACVS ASAS Catalog of Variable Stars \n", "1 AGC Arecibo Galaxy Catalog \n", "2 AKARI AKARI Point Source Catalogues \n", "3 CHANDRA The Chandra Source Catalog, Release 1.1 \n", "4 CNOC2 The Canadian Network for Observational Cosmol... \n", ".. ... ... \n", "50 VVDS The VIMOS VLT Deep Survey \n", "51 WiggleZ WiggleZ Dark Energy Survey Data Release 1 \n", "52 WISE \\tThe WISE All-Sky data Release \n", "53 WMAP \\tNine-year WMAP point source catalogs \n", "54 zCOSMOS zCOSMOS-bright DR2 catalog \n", "\n", " remarks \\\n", "0 \\r\\n The ASAS-3 Catalog of Variable Stars... \n", "1 \\r\\n The AGC, or Arecibo General Catalog,... \n", "2 \\r\\n AKARI (Previously known as ASTRO-F o... \n", "3 \\r\\n The first official release of the CS... \n", "4 \\r\\n The Canadian Network for Observation... \n", ".. ... \n", "50 \\r\\n A total of 11 564 objects have been ... \n", "51 \\r\\n The WiggleZ Dark Energy Survey is a ... \n", "52 \\n NASA's Wide-field Infrared Survey Expl... \n", "53 \\r\\n Nine-year WMAP point source catalogs \n", "54 \\r\\n zCOSMOS is a large-redshift survey t... \n", "\n", " url \n", "0 http://www.astrouw.edu.pl/asas/?page=main \n", "1 http://caborojo.astro.cornell.edu/alfalfalog/i... \n", "2 http://www.ir.isas.jaxa.jp/AKARI/ \n", "3 http://cxc.cfa.harvard.edu/csc/ \n", "4 http://www.astro.utoronto.ca/~cnoc/cnoc2.html \n", ".. ... \n", "50 http://cesam.lam.fr/vvdsproject/index.html \n", "51 http://wigglez.swin.edu.au/site/index.html \n", "52 http://wise2.ipac.caltech.edu/docs/release/all... \n", "53 https://heasarc.gsfc.nasa.gov/W3Browse/radio-c... \n", "54 http://vizier.cfa.harvard.edu/viz-bin/VizieR?-... \n", "\n", "[55 rows x 4 columns]" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "sql = \"SELECT * FROM Catalogs ORDER BY catalog_name\"\n", "cj.executeQuery(sql, context='xmatch')" ] }, { "cell_type": "markdown", "id": "19dab474-f4f3-4fca-90e7-83ee9e3949c5", "metadata": {}, "source": [ "### Listing tables in a catalog\n", "\n", "\n", "The `Tables` view contains the tables available in all catalogs. We can use the `WHERE` clause to filter rows on the `catalog_name` column." ] }, { "cell_type": "code", "execution_count": 3, "id": "5be240a8-716b-4b38-aefd-45a61ff3d9e0", "metadata": { "execution": { "iopub.execute_input": "2024-08-01T12:53:32.408844Z", "iopub.status.busy": "2024-08-01T12:53:32.408092Z", "iopub.status.idle": "2024-08-01T12:53:34.142257Z", "shell.execute_reply": "2024-08-01T12:53:34.140354Z", "shell.execute_reply.started": "2024-08-01T12:53:32.408781Z" }, "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
catalog_nametable_nametable_typedescriptionschema_name
0IRASIRAS_PhotoObjviewThe main PhotoObj table for the IRAS catalogdbo
\n", "
" ], "text/plain": [ " catalog_name table_name table_type \\\n", "0 IRAS IRAS_PhotoObj view \n", "\n", " description schema_name \n", "0 The main PhotoObj table for the IRAS catalog dbo " ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "sql = \"SELECT * FROM Tables WHERE catalog_name = 'IRAS'\"\n", "cj.executeQuery(sql, context='xmatch')" ] }, { "cell_type": "markdown", "id": "0ab1347a-459d-4945-9b00-69b72aadd14f", "metadata": { "execution": { "iopub.execute_input": "2023-10-25T00:03:14.772850Z", "iopub.status.busy": "2023-10-25T00:03:14.772193Z", "iopub.status.idle": "2023-10-25T00:03:14.784806Z", "shell.execute_reply": "2023-10-25T00:03:14.782326Z", "shell.execute_reply.started": "2023-10-25T00:03:14.772799Z" } }, "source": [ "### Listing columns in a table\n", "\n", "Given a table name, we can run this SQL query containing the `getTableColumns` function in order to get a description of all its columns.
\n", "This helps identifying the RA and Dec columns, as well as possible column on which we could impose filters." ] }, { "cell_type": "code", "execution_count": 4, "id": "bd484de1-c066-499e-bdb3-141d8b0b087b", "metadata": { "execution": { "iopub.execute_input": "2024-08-01T12:53:37.255959Z", "iopub.status.busy": "2024-08-01T12:53:37.255198Z", "iopub.status.idle": "2024-08-01T12:53:39.316390Z", "shell.execute_reply": "2024-08-01T12:53:39.314220Z", "shell.execute_reply.started": "2024-08-01T12:53:37.255864Z" }, "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
catalog_nametable_namecolumn_namedescriptionunitucdutypedatatypesizeprecisionscalecolumn_indexschema_name
0IRASIRAS_PhotoObjobjIDunique object identifiermeta.idbigint81901dbo
1IRASIRAS_PhotoObjnameIRAS Source Namemeta.idvarchar11002dbo
2IRASIRAS_PhotoObjraJ2000 right ascensiondegpos.eq.ra;pos.frame=j2000float85303dbo
3IRASIRAS_PhotoObjdecJ2000 declinationdegpos.eq.dec;pos.frame=j2000float85304dbo
4IRASIRAS_PhotoObjcxCartesian coordinate xpos.eq.x;pos.frame=j2000float85305dbo
..........................................
54IRASIRAS_PhotoObjmhconPossible number of HCONs, 99 for NULLmeta.numbersmallint25055dbo
55IRASIRAS_PhotoObjfcor_12Flux correction factor applied (times 1000),99...stat.param;phot.flux;em.IR.IRAS.12int410056dbo
56IRASIRAS_PhotoObjfcor_25Flux correction factor applied (times 1000),99...stat.param;phot.flux;em.IR.IRAS.25int410057dbo
57IRASIRAS_PhotoObjfcor_60Flux correction factor applied (times 1000),99...stat.param;phot.flux;em.IR.IRAS.60int410058dbo
58IRASIRAS_PhotoObjfcor_100Flux correction factor applied (times 1000),99...stat.param;phot.flux;em.IR.IRAS.100int410059dbo
\n", "

59 rows × 13 columns

\n", "
" ], "text/plain": [ " catalog_name table_name column_name \\\n", "0 IRAS IRAS_PhotoObj objID \n", "1 IRAS IRAS_PhotoObj name \n", "2 IRAS IRAS_PhotoObj ra \n", "3 IRAS IRAS_PhotoObj dec \n", "4 IRAS IRAS_PhotoObj cx \n", ".. ... ... ... \n", "54 IRAS IRAS_PhotoObj mhcon \n", "55 IRAS IRAS_PhotoObj fcor_12 \n", "56 IRAS IRAS_PhotoObj fcor_25 \n", "57 IRAS IRAS_PhotoObj fcor_60 \n", "58 IRAS IRAS_PhotoObj fcor_100 \n", "\n", " description unit \\\n", "0 unique object identifier \n", "1 IRAS Source Name \n", "2 J2000 right ascension deg \n", "3 J2000 declination deg \n", "4 Cartesian coordinate x \n", ".. ... ... \n", "54 Possible number of HCONs, 99 for NULL \n", "55 Flux correction factor applied (times 1000),99... \n", "56 Flux correction factor applied (times 1000),99... \n", "57 Flux correction factor applied (times 1000),99... \n", "58 Flux correction factor applied (times 1000),99... \n", "\n", " ucd utype datatype size precision \\\n", "0 meta.id bigint 8 19 \n", "1 meta.id varchar 11 0 \n", "2 pos.eq.ra;pos.frame=j2000 float 8 53 \n", "3 pos.eq.dec;pos.frame=j2000 float 8 53 \n", "4 pos.eq.x;pos.frame=j2000 float 8 53 \n", ".. ... ... ... ... ... \n", "54 meta.number smallint 2 5 \n", "55 stat.param;phot.flux;em.IR.IRAS.12 int 4 10 \n", "56 stat.param;phot.flux;em.IR.IRAS.25 int 4 10 \n", "57 stat.param;phot.flux;em.IR.IRAS.60 int 4 10 \n", "58 stat.param;phot.flux;em.IR.IRAS.100 int 4 10 \n", "\n", " scale column_index schema_name \n", "0 0 1 dbo \n", "1 0 2 dbo \n", "2 0 3 dbo \n", "3 0 4 dbo \n", "4 0 5 dbo \n", ".. ... ... ... \n", "54 0 55 dbo \n", "55 0 56 dbo \n", "56 0 57 dbo \n", "57 0 58 dbo \n", "58 0 59 dbo \n", "\n", "[59 rows x 13 columns]" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "sql = \"SELECT * FROM Columns WHERE table_name = 'IRAS_PhotoObj' ORDER BY column_index\"\n", "cj.executeQuery(sql, context='xmatch')" ] }, { "cell_type": "markdown", "id": "da8b09b0-4942-41e6-a274-6674f03f1b3e", "metadata": {}, "source": [ "---\n", "## Cross-Match Examples" ] }, { "cell_type": "code", "execution_count": 5, "id": "fa28b430-8353-4ddc-99be-07f1f4288a8e", "metadata": { "execution": { "iopub.execute_input": "2024-08-01T12:53:40.824039Z", "iopub.status.busy": "2024-08-01T12:53:40.823267Z", "iopub.status.idle": "2024-08-01T12:53:40.833302Z", "shell.execute_reply": "2024-08-01T12:53:40.831350Z", "shell.execute_reply.started": "2024-08-01T12:53:40.823975Z" }, "tags": [] }, "outputs": [], "source": [ "# Setting up seartch parameters. Search radius must be in arcseconds, the rest in degrees.\n", "\n", "radius = 30\n", "ra1 = 160\n", "ra2 = 160.5\n", "dec1 = 25\n", "dec2 = 25.5" ] }, { "cell_type": "markdown", "id": "b1b9bf0e-f97a-41a0-9a8a-2bae8e2e0802", "metadata": {}, "source": [ "### Cross-matching two small catalogs - Pandas DataFrame output\n", "\n", "Here we cross-match 2 local infrared catalog tables: `IRAS_PhotoObj` and `AKARI_FIS`.\n", "\n", "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`. " ] }, { "cell_type": "code", "execution_count": 6, "id": "7816e30a-0190-41bd-b38e-cde897a15faa", "metadata": { "execution": { "iopub.execute_input": "2024-08-01T12:53:44.968176Z", "iopub.status.busy": "2024-08-01T12:53:44.967408Z", "iopub.status.idle": "2024-08-01T12:53:55.650762Z", "shell.execute_reply": "2024-08-01T12:53:55.648783Z", "shell.execute_reply.started": "2024-08-01T12:53:44.968113Z" }, "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "There are 322 matches.\n", "CPU times: user 15 ms, sys: 6.97 ms, total: 21.9 ms\n", "Wall time: 10.7 s\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
id1id2sep
021105333823240.128767
19918231852420.423474
24726631578780.367530
320157033603070.924460
411732934061780.943397
............
3179465132260290.985342
3181906731299670.562924
3193720731582100.743264
32014232134061810.444341
32116348730403550.794326
\n", "

322 rows × 3 columns

\n", "
" ], "text/plain": [ " id1 id2 sep\n", "0 211053 3382324 0.128767\n", "1 99182 3185242 0.423474\n", "2 47266 3157878 0.367530\n", "3 201570 3360307 0.924460\n", "4 117329 3406178 0.943397\n", ".. ... ... ...\n", "317 94651 3226029 0.985342\n", "318 19067 3129967 0.562924\n", "319 37207 3158210 0.743264\n", "320 142321 3406181 0.444341\n", "321 163487 3040355 0.794326\n", "\n", "[322 rows x 3 columns]" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%time\n", "\n", "sql = f\"\"\"\n", "EXECUTE SQLxMatch @table1='IRAS_PhotoObj', @table2='AKARI_FIS', @radius=1\n", "\n", "-- Note that we could be more explicit by specifying the columns names, but that was not needed.\n", "--EXECUTE SQLxMatch @table1='IRAS_PhotoObj', @id_col1='objid', @ra_col1='ra', @dec_col1='dec', @table2='AKARI_FIS', @radius={radius}\n", "\"\"\"\n", "\n", "df = cj.executeQuery(sql, context=\"xmatch\")\n", "print(f\"There are {df.shape[0]} matches.\")\n", "df" ] }, { "cell_type": "markdown", "id": "5cc8fc58-1359-4fc7-98e2-ade0ccc3f568", "metadata": {}, "source": [ "### Cross-matching two filtered catalogs - Pandas DataFrame output\n", "\n", "Here we use 2 local tables (the `PhotoObjAll` tables in the `BestDR17` 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.\n", "\n", "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.\n", "\n", "Also, note that time we also give `@include_radec`=1, `@include_sep_rank`=1, `@sort_by_separation`=1 as input parameters. \n", "\n" ] }, { "cell_type": "code", "execution_count": 7, "id": "6db61e21-b49a-4ea1-83e7-18f372d7acb8", "metadata": { "execution": { "iopub.execute_input": "2024-08-01T12:54:08.272287Z", "iopub.status.busy": "2024-08-01T12:54:08.271496Z", "iopub.status.idle": "2024-08-01T12:54:12.391426Z", "shell.execute_reply": "2024-08-01T12:54:12.389257Z", "shell.execute_reply.started": "2024-08-01T12:54:08.272209Z" }, "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "There are 14482 matches.\n", "CPU times: user 136 ms, sys: 37.1 ms, total: 173 ms\n", "Wall time: 4.09 s\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
id1id2sepsep_rankra1dec1ra2dec2
0123766732271026195463878746582684797041.7989511160.00840725.004219160.00848725.003724
11237667322710261954638787465934222526912.0091392160.00840725.004219160.01186425.003073
2123766732271026195563878746582684797041.7923551160.00840225.004216160.00848725.003724
31237667322710261955638787465934222526912.0229252160.00840225.004216160.01186425.003073
41237667322710261983638787465826847974823.7390311160.02779325.022443160.03487325.020916
...........................
144771237667430638814217638787465827057886027.7912071160.04196725.474943160.03392025.477554
144781237667430638814217638787465826953341129.3797652160.04196725.474943160.04269725.466809
144791237667430638814224638787465826848182523.9943181160.03664925.495353160.02996925.498194
144801237667430638814224638787465827057884626.0407272160.03664925.495353160.03799525.488222
144811237667430638878978638787465826848180516.8130321160.17302625.499917160.17369125.495285
\n", "

14482 rows × 8 columns

\n", "
" ], "text/plain": [ " id1 id2 sep sep_rank \\\n", "0 1237667322710261954 6387874658268479704 1.798951 1 \n", "1 1237667322710261954 6387874659342225269 12.009139 2 \n", "2 1237667322710261955 6387874658268479704 1.792355 1 \n", "3 1237667322710261955 6387874659342225269 12.022925 2 \n", "4 1237667322710261983 6387874658268479748 23.739031 1 \n", "... ... ... ... ... \n", "14477 1237667430638814217 6387874658270578860 27.791207 1 \n", "14478 1237667430638814217 6387874658269533411 29.379765 2 \n", "14479 1237667430638814224 6387874658268481825 23.994318 1 \n", "14480 1237667430638814224 6387874658270578846 26.040727 2 \n", "14481 1237667430638878978 6387874658268481805 16.813032 1 \n", "\n", " ra1 dec1 ra2 dec2 \n", "0 160.008407 25.004219 160.008487 25.003724 \n", "1 160.008407 25.004219 160.011864 25.003073 \n", "2 160.008402 25.004216 160.008487 25.003724 \n", "3 160.008402 25.004216 160.011864 25.003073 \n", "4 160.027793 25.022443 160.034873 25.020916 \n", "... ... ... ... ... \n", "14477 160.041967 25.474943 160.033920 25.477554 \n", "14478 160.041967 25.474943 160.042697 25.466809 \n", "14479 160.036649 25.495353 160.029969 25.498194 \n", "14480 160.036649 25.495353 160.037995 25.488222 \n", "14481 160.173026 25.499917 160.173691 25.495285 \n", "\n", "[14482 rows x 8 columns]" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%time\n", "\n", "sql = f\"\"\"\n", "\n", "SELECT objid, ra, dec, cx, cy, cz INTO #temp1\n", "FROM SDSSDR17_PhotoObjAll WHERE ra BETWEEN {ra1} AND {ra2} AND dec BETWEEN {dec1} AND {dec2}\n", "\n", "SELECT objid, ra, dec, cx, cy, cz INTO #temp2 \n", "FROM GalexGR6_PhotoObjAll WHERE ra BETWEEN {ra1} AND {ra2} AND dec BETWEEN {dec1} AND {dec2}\n", "\n", "EXECUTE SQLxMatch @table1='#temp1', @table2='#temp2', @radius={radius}, @include_radec=1, @include_sep_rank=1, @sort_by_separation=1\n", "\"\"\"\n", "\n", "df = cj.executeQuery(sql, context=\"xmatch\")\n", "print(f\"There are {df.shape[0]} matches.\")\n", "df" ] }, { "cell_type": "markdown", "id": "2dbf978e-9af7-4475-99cc-5f1782dda9d0", "metadata": { "execution": { "iopub.execute_input": "2023-10-11T17:25:06.115662Z", "iopub.status.busy": "2023-10-11T17:25:06.115047Z", "iopub.status.idle": "2023-10-11T17:25:06.127777Z", "shell.execute_reply": "2023-10-11T17:25:06.125566Z", "shell.execute_reply.started": "2023-10-11T17:25:06.115614Z" } }, "source": [ "### Cross-Match beween 2 catalogs as an asynchronous job - Output to MyDB\n", "\n", "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 `MyDB`.\n", "\n", "First, one must create the output table in MyDB, or delete if this table was already created by this demo.\n" ] }, { "cell_type": "code", "execution_count": 8, "id": "85ee81f3-4a05-4db2-a191-26e3b16bf925", "metadata": { "execution": { "iopub.execute_input": "2024-08-01T12:54:24.237310Z", "iopub.status.busy": "2024-08-01T12:54:24.235044Z", "iopub.status.idle": "2024-08-01T12:54:26.500023Z", "shell.execute_reply": "2024-08-01T12:54:26.498371Z", "shell.execute_reply.started": "2024-08-01T12:54:24.237241Z" }, "tags": [] }, "outputs": [], "source": [ "mydb_output_table_name = \"xmatch_table\"\n", "\n", "sql = f\"\"\"\n", "IF EXISTS (select * from sys.objects WHERE object_id = OBJECT_ID(N'{mydb_output_table_name}') AND TYPE = 'U')\n", "DROP TABLE {mydb_output_table_name} \n", "CREATE TABLE {mydb_output_table_name}(id1 BIGINT, id2 BIGINT, sep FLOAT, sep_rank INTEGER, ra1 BIGINT, dec1 FLOAT, ra2 FLOAT, dec2 FLOAT)\n", "\"\"\"\n", "df = cj.executeQuery(sql, context=\"mydb\")" ] }, { "cell_type": "markdown", "id": "d4ad7b26-8bd1-4b46-b3cd-622e84da2157", "metadata": {}, "source": [ "
\n", "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.\n", "
" ] }, { "cell_type": "code", "execution_count": 9, "id": "7597e6c7-5293-49cd-be7c-1b4875f14ea7", "metadata": { "execution": { "iopub.execute_input": "2024-08-01T12:54:29.376384Z", "iopub.status.busy": "2024-08-01T12:54:29.375105Z", "iopub.status.idle": "2024-08-01T12:54:41.457210Z", "shell.execute_reply": "2024-08-01T12:54:41.455263Z", "shell.execute_reply.started": "2024-08-01T12:54:29.376319Z" }, "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Job Message: Query Complete\n", "CPU times: user 39.6 ms, sys: 10.7 ms, total: 50.3 ms\n", "Wall time: 12.1 s\n" ] } ], "source": [ "%%time\n", "\n", "sql = f\"\"\"\n", "SELECT objid, ra, dec INTO #temp1\n", "FROM SDSSDR17_PhotoObjAll WHERE ra BETWEEN {ra1} AND {ra2} AND dec BETWEEN {dec1} AND {dec2}\n", "\n", "SELECT objid, ra, dec into #temp2 \n", "FROM GALEXGR6_PhotoObjAll WHERE ra BETWEEN {ra1} AND {ra2} AND dec BETWEEN {dec1} AND {dec2}\n", "\n", "-- Creating temporary output table\n", "CREATE TABLE #out(id1 BIGINT, id2 BIGINT, sep FLOAT, sep_rank INTEGER, ra1 BIGINT, dec1 FLOAT, ra2 FLOAT, dec2 FLOAT)\n", "\n", "-- Executing cross-match that fills output table.\n", "EXECUTE SQLxMatch @table1='#temp1', @table2='#temp2', @radius={radius}, @output_table='#out', @include_radec=1, @include_sep_rank=1\n", "\n", "-- Filling up table in MyDB:\n", "INSERT INTO mydb.{mydb_output_table_name}\n", "SELECT * from #out\n", "\"\"\"\n", "\n", "job_id = cj.submitJob(sql, context=\"xmatch\")\n", "\n", "# this line will make the code wait until the job is done, if desired:\n", "job_description = cj.waitForJob(job_id)\n", "print(f\"Job Message: {job_description.get('Message')}\")" ] }, { "cell_type": "markdown", "id": "ec0e6d03-85f8-4e1e-8928-4568c7eb2542", "metadata": {}, "source": [ "
\n", "We can now inspect the contents of the MyDB output table:" ] }, { "cell_type": "code", "execution_count": 10, "id": "2843127a-f8fd-4596-b514-973a85b43e42", "metadata": { "execution": { "iopub.execute_input": "2024-08-01T12:54:45.879432Z", "iopub.status.busy": "2024-08-01T12:54:45.878665Z", "iopub.status.idle": "2024-08-01T12:54:48.309277Z", "shell.execute_reply": "2024-08-01T12:54:48.306911Z", "shell.execute_reply.started": "2024-08-01T12:54:45.879370Z" }, "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "There are 14482 matches.\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
id1id2sepsep_rankra1dec1ra2dec2
0123766732271026195463878746582684797041.798951116025.004219160.00848725.003724
11237667322710261954638787465934222526912.009139216025.004219160.01186425.003073
2123766732271026195563878746582684797041.792355116025.004216160.00848725.003724
31237667322710261955638787465934222526912.022925216025.004216160.01186425.003073
41237667322710261983638787465826847974823.739031116025.022443160.03487325.020916
...........................
144771237667430638814217638787465827057886027.791207116025.474943160.03392025.477554
144781237667430638814217638787465826953341129.379765216025.474943160.04269725.466809
144791237667430638814224638787465826848182523.994318116025.495353160.02996925.498194
144801237667430638814224638787465827057884626.040727216025.495353160.03799525.488222
144811237667430638878978638787465826848180516.813032116025.499917160.17369125.495285
\n", "

14482 rows × 8 columns

\n", "
" ], "text/plain": [ " id1 id2 sep sep_rank ra1 \\\n", "0 1237667322710261954 6387874658268479704 1.798951 1 160 \n", "1 1237667322710261954 6387874659342225269 12.009139 2 160 \n", "2 1237667322710261955 6387874658268479704 1.792355 1 160 \n", "3 1237667322710261955 6387874659342225269 12.022925 2 160 \n", "4 1237667322710261983 6387874658268479748 23.739031 1 160 \n", "... ... ... ... ... ... \n", "14477 1237667430638814217 6387874658270578860 27.791207 1 160 \n", "14478 1237667430638814217 6387874658269533411 29.379765 2 160 \n", "14479 1237667430638814224 6387874658268481825 23.994318 1 160 \n", "14480 1237667430638814224 6387874658270578846 26.040727 2 160 \n", "14481 1237667430638878978 6387874658268481805 16.813032 1 160 \n", "\n", " dec1 ra2 dec2 \n", "0 25.004219 160.008487 25.003724 \n", "1 25.004219 160.011864 25.003073 \n", "2 25.004216 160.008487 25.003724 \n", "3 25.004216 160.011864 25.003073 \n", "4 25.022443 160.034873 25.020916 \n", "... ... ... ... \n", "14477 25.474943 160.033920 25.477554 \n", "14478 25.474943 160.042697 25.466809 \n", "14479 25.495353 160.029969 25.498194 \n", "14480 25.495353 160.037995 25.488222 \n", "14481 25.499917 160.173691 25.495285 \n", "\n", "[14482 rows x 8 columns]" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "sql = f\"\"\"\n", "SELECT * FROM mydb.{mydb_output_table_name} ORDER BY id1, sep\n", "\"\"\"\n", "df = cj.executeQuery(sql, context=\"mydb\")\n", "print(f\"There are {df.shape[0]} matches.\")\n", "df" ] }, { "cell_type": "code", "execution_count": null, "id": "f2d9818b-5fbf-4cab-afe4-35b3ebbbdd11", "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3.9 (py39)", "language": "python", "name": "py39" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.9.13" } }, "nbformat": 4, "nbformat_minor": 5 }