Accessing MAST TAP services: How to chunk large / long queries

Accessing MAST TAP services: How to chunk large / long queries#

Overview#

This notebook demonstrates how to split up a large query (in terms of total number of rows returned or total execution time) for submission to Table Access Protocol (TAP) services at MAST. The examples presented here access the PanSTARRS TAP service.

This includes how to scope out the query execution time and “row density” (number of rows for a given ID or spatial range), how to scale to appropriately-sized query chunks given the TAP service limitations, and how to structure the chunks with parallel and serial components (to avoid overusing database resources with too many queries in parallel).

Learning Goals#

By the end of this tutorial, you will:

  • Understand how to estimate the scaling of execution time and numbers of rows returned with ID range or sky coverage

  • Understand how to use the scaling estimation to design full query chunks that are compatible with TAP service limitations

  • Understand how to submit the chunked queries in a manner that does not overuse database resources.

Table of Contents#

Introduction#

Welcome! This notebook demonstrates how to perform large queries accessing the PanSTARRS catalogs at MAST through a Virual Observatory Table Access Protocol (TAP) service, where the full query has:

  1. an execution time longer than the time limit for queries to the TAP service, and/or

  2. more result rows than the maximum size of rows returned by the TAP service.

In this tutorial, the example queries are drawn from the PanSTARRS “20 queries” tutorials. Please see the tutorial notebooks for full information about these queries.

Note: ADQL/SQL comments follow "--". Comments are used throughout the queries to explain the purpose of specific clauses.

Imports#

This tutorial makes use of the following libraries:

  • numpy for numerical calculations

  • pyvo for querying the MAST catalogs via TAP

  • time, datetime to determine query duration

  • astropy.table vstack to concatenate chunk result tables

  • astropy.conf to modify number of lines displayed for tables, for brevity

import numpy as np
import pyvo as vo
import datetime
import time
from astropy.table import vstack

from astropy import conf
conf.max_lines = 10

Connect to TAP service#

We will be connecting to the PanSTARRS (PS1) Data release 2 (DR2) catalog. Specifically, we will use the ps1_dr2 catalogs available from the new postgres-backed TAP service, which offers improved performance relative to the legacy database (by factors of 100 or greater, in many cases).

See the PS1 documentation) for information about the tables available with this new TAP service.

We begin by connecting to the MAST PanSTARRS DR2 TAP service.

(Note: See https://mast.stsci.edu/vo-tap/ for a full list of MAST TAP services.)

TAP_service = vo.dal.TAPService(
    "https://mast.stsci.edu/vo-tap/api/v0.1/mast_catalogs"
)

TAP service limits#

We will use the pyvo describe() method to discover the methods, capabilities, and maximum result set size for this service.

TAP_service.describe()

Capability ivo://ivoa.net/std/TAP

Interface vs:ParamHTTP
    https://mast.stsci.edu/vo-tap/api/v0.1/mast_catalogs

Language ADQL
    ivo://ivoa.net/std/TAPRegExt#features-adqlgeo
        CONTAINS

        POINT

        CIRCLE


    ivo://edu.stsci.archive/std/exts#features-adql-bitwise
        BIT_AND
            The ampersand (&) operator performs a bitwise AND operation on two integer operands

        BIT_OR
            The vertical bar (|) operator performs a bitwise OR operation on two integer operands.

        BIT_XOR
            The circumflex (^) operator performs a bitwise XOR (exclusive or) operation on two integer operands.

        BIT_NOT
            The tilde (~) operator performs a bitwise NOT operation on an integer operand.


Output format application/json
    Also available as json

Output format text/csv;header=present
    Also available as csv

Output format application/xml
    Also available as xml

Output format application/x-votable+xml
    Also available as votable

Maximum size of resultsets
    Default 100000 row
    Maximum 100000 row


Capability ivo://ivoa.net/std/DALI#examples

Interface vr:WebBrowser
    https://mast.stsci.edu/vo-tap/api/v0.1/mast_catalogs/examples

We see the service supports queries with ADQL, with a few specific ADQL features for geometric searching and bigwise operations), and that the maximum result size is 100,000 rows.

For pyvo, the default job execution duration is 600 seconds.

It is possible to set this to longer durations, but most tap services enforace a maximum duration.

We do the following to find the maximum duration for the MAST TAP service:

  1. Create a job with an empty query.

  2. Set the execution duration to a very long time.

  3. Print the job execution duration: this will be the lesser of the requested or service-maximum duration.

  4. Delete the job

# Create the empty job
job = TAP_service.submit_job("")

# Set execution duration
job.execution_duration = int(1e7)   # seconds; Almost 1/3 of a year

# Print the duration allowed, the lesser of the requested or service-maximum value:
print(job.execution_duration)

# Clean up & delete the job
job.delete()

1200.0

As expected, we were not able to set the duration to 1/3 of a year. Instead, the service-imposed maximum duration is 1200 seconds (20 min).

Too many rows#

We will begin with the case of how to chunk a query that would return too many rows.

Here, we will work from the query Q2 from the PanSTARRS “20 queries”, which looks for galaxies with a specified blue surface brightness within a fairly large swath of the sky (RA between 170 and 190 deg, and Decl. less than 0).

The full query, with no subselections to implement chunking, is written as follows:

full_adql_query = """
SELECT fmo.objid, fmo.gfkronmag,
   fmo.gfkronmag + 2.5*log10(PI()*POWER(so.gkronrad,2)) AS bsurfmag, 
   fmo.ramean, fmo.decmean
FROM ps1_dr2.forced_mean_object as fmo
JOIN ps1_dr2.stack_object AS so ON 
    fmo.objid=so.objid
WHERE ((fmo.gfkronmag > 0) AND
       (fmo.gfpsfmag - fmo.gfkronmag > 0.05)) -- galaxies
AND fmo.ramean BETWEEN 170 AND 190            -- ra substitute for super-gal coords
AND fmo.decmean < 0
AND fmo.gfkronmag + 2.5*log10(PI()*POWER(so.gkronrad,2))
       BETWEEN 23 AND 25                      -- mag per sq arcsec
AND so.primarydetection = 1                   -- primary detection in stack_object"""

Determining the query row/time scaling#

First, we need submit a test query with a restricted range in order to estimate how the query execution time and number of rows scales with ID range (or area).

To do this, we will place limits on the objid.

We will leverage the fact that PanSTARRS object identifiers are determined from their position on the sky to determine an objid range. Since the first 5 digits of objid are determined from floor((decl+90)/0.00833333), we can easily determine objid ranges from a declination range.

For this first test query, we will limit objid based on a declination range of -5\(^{\circ}\)\(\leq\)decmean\(<\)0\(^{\circ}\).

We begin by writing a convenience function to obtain an objid from a given declination value.

def get_objid_from_decl(decl):
    return np.int64(np.floor((decl+90)/0.00833333)*1e13)

Next, we compute the objids that correspond to the declination range of [-5, 0].

decl_range = [-5, 0]
objidrange = [get_objid_from_decl(decl) for decl in decl_range]

We now construct our test query by appending constraints that the objid be between this range to the full, non-chunked query, and print the resulting query string for inspection.

test_adql_query = f"""{full_adql_query}
AND fmo.objid >= {objidrange[0]}           -- dec range subselection
AND fmo.objid < {objidrange[1]}"""
print(test_adql_query)

SELECT fmo.objid, fmo.gfkronmag,
   fmo.gfkronmag + 2.5*log10(PI()*POWER(so.gkronrad,2)) AS bsurfmag, 
   fmo.ramean, fmo.decmean
FROM ps1_dr2.forced_mean_object as fmo
JOIN ps1_dr2.stack_object AS so ON 
    fmo.objid=so.objid
WHERE ((fmo.gfkronmag > 0) AND
       (fmo.gfpsfmag - fmo.gfkronmag > 0.05)) -- galaxies
AND fmo.ramean BETWEEN 170 AND 190            -- ra substitute for super-gal coords
AND fmo.decmean < 0
AND fmo.gfkronmag + 2.5*log10(PI()*POWER(so.gkronrad,2))
       BETWEEN 23 AND 25                      -- mag per sq arcsec
AND so.primarydetection = 1                   -- primary detection in stack_object
AND fmo.objid >= 102000000000000000           -- dec range subselection
AND fmo.objid < 108000000000000000

Lastly, we submit this test query to the TAP service.

start = time.time()
job = TAP_service.run_async(test_adql_query)
end = time.time()
print(f"Elapsed time: {str(datetime.timedelta(seconds=end-start))}")

Elapsed time: 0:01:29.590129

/home/runner/micromamba/envs/ci-env/lib/python3.13/site-packages/pyvo/dal/query.py:403: DALOverflowWarning: Results truncated due to server limits. Consider setting a maxrec value.
  warn("Results truncated due to server limits. Consider "

This test query takes about 1 minute to run, well under the service maximum, so timing is not an immediate concern for our chunk partitioning.

To inspect the results, we convert the returned table to an astropy table, for easy viewing.

TAP_results = job.to_table()
TAP_results
Table length=100000
objidgfkronmagbsurfmagrameandecmean
magdegdeg
int64float32float32float64float64
10205170068320302621.75824.827238170.06829549481645-4.956144890282068
10235170006482215321.269624.92203170.00651369278813-4.706850072863837
...............
10519177428495832620.303323.997683177.42846750630974-2.335097590988124
10497178150929252519.717723.526714178.15089486111103-2.523315390990911

We see that the results table has 100,000 rows, equal to the service maximum limit — indicating that the results were almost certainly truncated.

Thus, we will submit a second test query with a smaller declination limit range of -1\(^{\circ}\)\(\leq\)decmean\(<\)0\(^{\circ}\).

We again compute the objid range corresponding to the declination range [-1, 0], and construct our test query by appending this objid range constraint to the full query. We once more print the test query for inspection.

decl_range = [-1, 0]
objidrange = [get_objid_from_decl(decl) for decl in decl_range]

test_adql_query = f"""{full_adql_query}
AND fmo.objid >= {objidrange[0]}           -- dec range subselection
AND fmo.objid < {objidrange[1]}"""
print(test_adql_query)

SELECT fmo.objid, fmo.gfkronmag,
   fmo.gfkronmag + 2.5*log10(PI()*POWER(so.gkronrad,2)) AS bsurfmag, 
   fmo.ramean, fmo.decmean
FROM ps1_dr2.forced_mean_object as fmo
JOIN ps1_dr2.stack_object AS so ON 
    fmo.objid=so.objid
WHERE ((fmo.gfkronmag > 0) AND
       (fmo.gfpsfmag - fmo.gfkronmag > 0.05)) -- galaxies
AND fmo.ramean BETWEEN 170 AND 190            -- ra substitute for super-gal coords
AND fmo.decmean < 0
AND fmo.gfkronmag + 2.5*log10(PI()*POWER(so.gkronrad,2))
       BETWEEN 23 AND 25                      -- mag per sq arcsec
AND so.primarydetection = 1                   -- primary detection in stack_object
AND fmo.objid >= 106800000000000000           -- dec range subselection
AND fmo.objid < 108000000000000000

We now submit this second, smaller test query to the TAP service.

start = time.time()
job = TAP_service.run_async(test_adql_query)
end = time.time()
print(f"Elapsed time: {str(datetime.timedelta(seconds=end-start))}")

Elapsed time: 0:00:50.152719

As expected, this test query takes less time to run.

We again convert the results to an astropy table, and inspect our sample.

TAP_results = job.to_table()
TAP_results
Table length=41150
objidgfkronmagbsurfmagrameandecmean
magdegdeg
int64float32float32float64float64
10680176430437518921.114124.604227176.4304496425183-0.996004230717282
10681176583137639721.887524.8599176.58312827-0.9867112
...............
10799177056019913821.752224.818466177.05600555448433-0.0011015989769555254
10799186943219671620.937324.95478186.9432443160625-0.0031884319996914002

This second test query has fewer results than the maximum (41,150), indicating we have received all matching results.

Since the area of a 1 degree range of declination, for the same RA range, decreases away from the equator, all other 1 degree declination chunks from -30 deg to 0 deg likely have fewer objects.

So in this case, we will proceed with declination range chunks of [-30,-29), [-29,-28), …, [-2, -1), [-1, 0.01). Note we slightly pad the upper limit (to account for the remaining digits in the objids), ensuring the objects very close to decl. ~ 0 deg are included.

(Note that in general, the number of results will also scale with source density — a concern for high density regions such as the galactic plane.)

Submitting the partitioned query#

With the query chunking scheme defined, we next proceed to submit the partitioned queries.

Submitting all chunks in parallel (simultaneously) will overuse database resources, and is likely to result in your IP address being blocked by MAST. It is acceptable to submit a few (\(\boldsymbol{\sim}\)5) simultaneous queries to MAST’s TAP services.

Thus, for our example query, we will structure our job submission as follows:

  1. Generate all 1 deg. declination range query ADQL strings.

  2. Loop over the full set of chunked queries: submit 5 queries simultaneously, wait for these to complete, and gather the results; then proceed to the next simultaneous set of queries.

As part of this process, we well also check that each query chunk returns FEWER than the maximum number of rows (to ensure no results are lost), and that each query chunk successfully executed.

We use a short helper function, defined below, to handle the process of submitting simultaneous queries and gathering the results, including checking for errors or potentially missing results:

# Helper function to run simultaneous queries and gather the results:
def run_simultaneous_queries(
    queries, tap_service,
    sleep_interval_sec=10, maximum_rows=100000,
    execution_duration=None
):
    # Start simultaneous jobs:
    jobs = []
    for query in queries:
        # Initialize job
        job = tap_service.submit_job(query)

        # Set execution time, if specified:
        if execution_duration is not None:
            job.execution_duration = execution_duration

        # Run job and add to list:
        job.run()
        jobs.append(job)

    # Wait for all jobs to be finished. 
    # Based on pyvo AsyncTAPJob.wait()
    # https://pyvo.readthedocs.io/en/latest/_modules/pyvo/dal/tap.html#AsyncTAPJob.wait
    # Active phases:
    active_phases = {"QUEUED", "EXECUTING", "RUN", "COMPLETED", "ERROR", "UNKNOWN"}
    completed_phases = {"COMPLETED"}
    failed_phases = {"ABORTED", "ERROR"}

    while True:
        # Check all job phases:
        statuses = []
        for i, job in enumerate(jobs):
            job_phase = job.phase
            if job_phase not in active_phases:
                raise ValueError(
                    "Job no longer active!\n"
                    f"URL: {job.url}\n"
                    "Query:\n"
                    f"{job.query}"
                )

            if job_phase in failed_phases:
                raise ValueError(
                    f"Job status: {job_phase}\n"
                    f"URL: {job.url}\n"
                    "Query:\n"
                    f"{job.query}"
                )

            statuses.append(True if job_phase in completed_phases else False)

        # Break if all jobs are completed:
        if np.all(statuses):
            break

        # Otherwise, pause until next check
        time.sleep(sleep_interval_sec)

    # Collect results, and check that no query hit the maximum number of results
    results = []
    for job in jobs:
        result = job.fetch_result().to_table()
        if len(result) == maximum_rows:
            raise ValueError(
                "Query hit maximum number of results!\n"
                f"URL: {job.url}\n"
                "Query:\n"
                f"{job.query}"
            )
        results.append(result)

        # Delete the job:
        job.delete()

    return results

We now define variables with the chunked declination ranges, and make a list constaining the ADQL query strings for each of the 1-degree declination chunks.

# Define the full set of query chunks, with 1 deg. declination ranges:

full_decl_range = [-30, 0]
decl_step = 1

# Generate an array with declination values on the boundaries:
decl_bounds = np.arange(full_decl_range[0], full_decl_range[1]+decl_step, decl_step)
# List comprehension to make lists for each decl. chunk range:
decl_ranges = [[decl_bounds[i], decl_bounds[i+1]] for i in range(len(decl_bounds)-1)]

# Pad the upper limit on the last range, to account for other digits in the objids 
# for objects at decl = 0 deg.
decl_ranges[-1][1] = 0.01

# Generate chunk queries
queries = []
for decl_range in decl_ranges:
    objidrange = [get_objid_from_decl(decl) for decl in decl_range]
    chunk_adql_query = f"""{full_adql_query}
        AND fmo.objid >= {objidrange[0]}           -- dec range subselection
        AND fmo.objid < {objidrange[1]}"""
    queries.append(chunk_adql_query)

Submit these queries in batches of 5 chunks, gathering the results for all before moving on to the next batch. At the end, we aggregate all the results into a single astropy table.

# Submit all query chunks and gather results:

n_per_chunk = 5  # Number of queries run simultaneously
n_queries = len(queries)
results_all = []

# Get wall-time to run full set of query chunks
start = time.time()
for i in range(int(np.ceil(n_queries/n_per_chunk))):
    imin = i*n_per_chunk
    imax = min([n_queries, (i+1)*n_per_chunk])
    results = run_simultaneous_queries(
        queries[imin:imax], TAP_service,
        sleep_interval_sec=10, maximum_rows=100000
    )
    results_all.extend(results)
end = time.time()
print(f"Elapsed time for full query: {str(datetime.timedelta(seconds=end-start))}")

# Aggregate all results into one table:
# Concatenate individual tables:
TAP_results = vstack(results_all)

# Clear memory:
results_all = None
results = None

Elapsed time for full query: 0:10:16.395380

By submitting 5 query chunks at a time, it takes about 9 minutes to run all partitions of the full query, obtaining the full result set of about 1.07 million objects, which we show an exerpt of below.

TAP_results
Table length=1067932
objidgfkronmagbsurfmagrameandecmean
magdegdeg
int64float32float32float64float64
7200179015843659620.743924.496178179.01582199045458-29.99482066466325
7201171623496547020.718424.515955171.62346826236762-29.987299477602395
...............
10799186672926416218.381923.64122186.67293150216565-0.005142773657562474
10800173924947040818.92623.615623173.9248817-2.855e-05
Tip: Ensuring robustness against individual query chunk failures

Depending on the complexity and total time required to run a query, it would be beneficial to modify the above approach to ensure that results from successfully executed chunks are not lost in the event that another chunk query fails (due to, e.g., isolated timeout issues).

This could be accomplished by saving the individual chunk results to disk, and then only rerunning those chunks where the query was previously unsuccessful.

Long query duration#

Next we will cover the case of how to chunk a query that would take too long to execute.

We will use Q4 from the PanSTARRS “20 queries”, which looks for galaxies with large galaxies with large red surface brightnes and high ellipticity.

The full query (again with no sub-selections to implement chunking) is written as follows:

full_adql_query = """
SELECT so.objid, so.ramean, so.decmean, so.rkronrad, so.rkronmag,
   so.rkronmag + 2.5*log10(PI()*POWER(so.rkronrad,2)) as rsurfmag,
   smf.rserab, smf.rserchisq,
   sqrt(1-power(smf.rserab,2)) as ellipticity
FROM ps1_dr2.stack_model_fit_ser AS smf
JOIN ps1_dr2.stack_object AS so ON 
    smf.objid=so.objid 
    AND smf.uniquepspsstid=so.uniquepspsstid
WHERE so.rkronrad BETWEEN 30 AND 60
  AND so.rkronmag > 0
  AND so.rkronmag + 2.5*log10(PI()*POWER(so.rkronrad,2)) < 24 -- mag per sq arcsec
  AND smf.rserchisq > 0
  AND smf.rserab < sqrt(0.75)
  AND so.rpsfqfperfect > 0.9
  AND so.ndetections > 3
  AND so.nr > 1"""

Determining the query row/time scaling#

As before, we will submit a test query with a restricted range in order to estimate how the query execution time and number of rows scales.

Again, we will limit the objid range in for our test query.

Since the first 5 digits of PanSTARRS objid are determined by floor((decl+90)/0.00833333) (see the PanSTARRS object identifiers documentation), PanSTARRS objids fall within the estimated range 72000000000000000 to 216010000000000000 (using declination -30 to 90.01 deg, with padding to ensuring all objects within -30 to 90 deg are included).

For this test query, we will restrict the objids to between 129500000000000000 and 133500000000000000 (roughly 1/36th of the estimated full range).

We construct this test query by appending the objid range constraints to the end of the full query, and print the ADQL query for inspection.

test_adql_query = f"""{full_adql_query}
  AND so.objid >= 129500000000000000     -- objid range subselection
  AND so.objid < 133500000000000000"""
print(test_adql_query)

SELECT so.objid, so.ramean, so.decmean, so.rkronrad, so.rkronmag,
   so.rkronmag + 2.5*log10(PI()*POWER(so.rkronrad,2)) as rsurfmag,
   smf.rserab, smf.rserchisq,
   sqrt(1-power(smf.rserab,2)) as ellipticity
FROM ps1_dr2.stack_model_fit_ser AS smf
JOIN ps1_dr2.stack_object AS so ON 
    smf.objid=so.objid 
    AND smf.uniquepspsstid=so.uniquepspsstid
WHERE so.rkronrad BETWEEN 30 AND 60
  AND so.rkronmag > 0
  AND so.rkronmag + 2.5*log10(PI()*POWER(so.rkronrad,2)) < 24 -- mag per sq arcsec
  AND smf.rserchisq > 0
  AND smf.rserab < sqrt(0.75)
  AND so.rpsfqfperfect > 0.9
  AND so.ndetections > 3
  AND so.nr > 1
  AND so.objid >= 129500000000000000     -- objid range subselection
  AND so.objid < 133500000000000000

As is now familiar, we now submit this query to the MAST TAP service.

start = time.time()
job = TAP_service.run_async(test_adql_query)
end = time.time()
print(f"Elapsed time: {str(datetime.timedelta(seconds=end-start))}")

Elapsed time: 0:01:01.728317

This test query takes about 45 seconds and returns 67 rows.

TAP_results = job.to_table()
TAP_results
Table length=67
objidrameandecmeanrkronradrkronmagrsurfmagrserabrserchisqellipticity
degdegmag
int64float64float64float32float32float32float32float32float32
129571656922317917165.692307195583517.98126732258913347.022513.226222.8306030.35750329.63620.933912
1312700457722322724.57722417066336119.3930790374405834.50114.282923.2149330.56164733.91790.82737696
...........................
13295028700594943628.70061567200127320.79906583627532531.12813.350822.0594310.64534413.03630.7638921
132902397965196100239.796496714236920.75463766081048431.36913.308522.0338780.51992786.82060.8542107

As this test query has fewer than the maximum number of rows, no results were omitted — and we can thus use this to determine the query scaling.

We thus estimate that executing the query over the full PanSTARRS catalog (~36 times larger) would take about 27 minutes and return about 2100 rows.

This full query would take longer than the service time limit, requiring us to submit query chunks in order to obtain our full sample.

Based on our test query execution time, and the service maximum time of 20 minutes, we will thus proceed by splitting our query in half, with objid chunks of [72000000000000000, 144005000000000000), [144005000000000000, 216010000000000000).

Submitting the partitioned query#

As before, we now proceed to generate and submit the partitioned queries.

Since we will only use 2 chunks in this case, we can submit both simultaneously (because up to 5 parallel queries is reasonable given database resources).

However, we need to increase the the execution duration to its maximum of 20 minutes, to cover the estimated ~13.5 minute execution time per chunk.

We will again use the previously-defined helper function to handle job submission, result gathering, and error checks.

We first define our chunk objid ranges, and construct a list with the chunked ADQL queries.

# Define the full set of query chunks, with the two objid ranges:
objid_ranges = [
    [72000000000000000, 144005000000000000],
    [144005000000000000, 216010000000000000]
    ]
# Generate chunk queries
queries = []
for objidrange in objid_ranges:
    chunk_adql_query = f"""{full_adql_query}
  AND so.objid >= {objidrange[0]}     -- objid range subselection
  AND so.objid < {objidrange[1]}"""
    queries.append(chunk_adql_query)

We now submit both queries at the same time, using our helper function as before. Here we also specify that the execution duration should be increased to its maximum (1200 seconds). The results of each chunk are combined into a single astropy table.

# Submit all query chunks and gather results:

# Get wall-time to run full set of query chunks
start = time.time()
results = run_simultaneous_queries(
    queries, TAP_service,
    sleep_interval_sec=10, maximum_rows=100000,
    execution_duration=1200
)
end = time.time()
print(f"Elapsed time for full query: {str(datetime.timedelta(seconds=end-start))}")

# Aggregate all results into one table:
# Concatenate individual tables:
TAP_results = vstack(results)

# Clear memory:
results = None

Elapsed time for full query: 0:06:56.606347

By submitting the 2 query chunks simultaneously, it takes about 8 minutes to run both partitions of the full query, obtaining the full result set of 1745 objects, as we show below.

TAP_results
Table length=1745
objidrameandecmeanrkronradrkronmagrsurfmagrserabrserchisqellipticity
degdegmag
int64float64float64float32float32float32float32float32float32
81392104361132383210.4360728073643-22.17325476580972732.450614.747723.5466880.13129686.10170.9913432
9610014172941269214.173036434421656-9.9147599103883335.021612.921921.8864540.52082535.94120.85366344
...........................
176072149413142528214.9413276856.7265277832.13928.093316.871350.054.0052e+070.9987492
151892142716600795214.27161455507836.5751866839189330.477913.468922.13170.455428100.8650.8902726

This does turn out to be faster than our initial estimate. However, this exercise does demonstrate what points to consider when benchmarking how a large query should be partitioned.

Note: Intrinsic source density variations

In this case, the full result set size is only about 80% of what we estimated, suggesting our test query selected objects in an ID range (equivalently, a portion of the sky) with a higher than average source density.

If the source density is known to be variable (with many selected objects in e.g., the galactic plane or nearby galaxies), submitting a test query covering a high density region/ID range will provide a conservative scaling estimate. This will ensure none of the chunks go over the TAP service time/row limits.

Additional Resources#

Another chunking option for PanSTARRS: randomid#

Another option available for the PanSTARRS catalogs (though not universally available for other surveys) is to chunk queries by ranges of randomid from the ObjectThin table (and joins thereof). randomid is a random number between 0 and 1 assigned to each object, regardless of position on the sky.

However, this tutorial presented focuses on using object ID ranges (extensible to position ranges) to provide broader applicability to queries with other datasets without similar random IDs.

Table Access Protocol#

  • IVOA standard for RESTful web service access to tabular data

  • http://www.ivoa.net/documents/TAP/

PanSTARRS 1 DR 2#

Astronomical Query Data Language (2.0)#

  • IVOA standard for querying astronomical data in tabular format, with geometric search support

  • http://www.ivoa.net/documents/latest/ADQL.html

PyVO#

  • an affiliated package for astropy

  • find and retrieve astronomical data available from archives that support standard IVOA virtual observatory service protocols.

  • https://pyvo.readthedocs.io/en/latest/index.html

Full list of MAST/TAP services#

  • A full list of available MAST TAP services can be found at:

  • https://mast.stsci.edu/vo-tap

Citations#

If you use astropy for published research, please cite the authors. Follow these links for more information about citing astropy:

If you use PanSTARRS data accessed through MAST for published research, please include the following acknowledgements, found at the following links:

About this Notebook#

Author(s): Sedona Price
Keyword(s): Tutorial, TAP, pyvo, ADQL, PanSTARRS
Last Updated: October 2026

Top of Page Space Telescope Logo