The Pan-STARRS1 Database and Data Products

H. A. Flewelling; E. A. Magnier; K. C. Chambers; J. N. Heasley; C. Holmberg; M. E. Huber; W. Sweeney; C. Z. Waters; A. Calamida; S. Casertano; X. Chen; D. Farrow; G. Hasinger; R. Henderson; K. S. Long; N. Metcalfe; G. Narayan; M. A. Nieto-Santisteban; P. Norberg; A. Rest; R. P. Saglia; A. Szalay; A. R. Thakar; J. L. Tonry; J. Valenti; S. Werner; R. White; L. Denneau; P. W. Draper; K. W. Hodapp; R. Jedicke; N. Kaiser; R. P. Kudritzki; P. A. Price; R. J. Wainscoat; P. S. Builders; S. Chastel; B. McLean; M. Postman; B. Shiao

Source-linked AI summary

The Pan-STARRS1 Database and Data Products

H. A. Flewelling, E. A. Magnier, K. C. Chambers, J. N. Heasley, C. Holmberg, M. E. Huber, W. Sweeney, C. Z. Waters, A. Calamida, S. Casertano, X. Chen, D. Farrow, G. Hasinger, R. Henderson, K. S. Long, N. Metcalfe, G. Narayan, M. A. Nieto-Santisteban, P. Norberg, A. Rest, R. P. Saglia, A. Szalay, A. R. Thakar, J. L. Tonry, J. Valenti, S. Werner, R. White, L. Denneau, P. W. Draper, K. W. Hodapp, R. Jedicke, N. Kaiser, R. P. Kudritzki, P. A. Price, R. J. Wainscoat, P. S. Builders, S. Chastel, B. McLean, M. Postman, B. Shiao

arXiv:1612.05243v3Dec 15, 2016astro-ph.IM

TL;DR

Pan-STARRS1’s enormous, complex survey requires an accessible way to navigate its catalog data. This paper describes the database’s construction, provenance, schema, relationships, and example queries, making 10,723,304,629 objects accessible through MAST.

Problem
Pan-STARRS1’s large, complex catalog requires clear documentation of its organization and access for astronomical users.
Method
The paper documents the database’s construction, data provenance, schema, table relationships, processing stages, and example queries for varied science goals.
Results
The database contains 10,723,304,629 objects and provides Pan-STARRS1 catalog products to the astronomical community through MAST.
Takeaways & Limitations
The documented database and query examples provide an accessible form of the Pan-STARRS1 catalog for astronomical research.
Takeaways & Limitations
A small number of processing duplicates remained because removing them would have required recalculating mean properties.

Abstract

from arXiv · show

This paper describes the organization of the database and the catalog data products from the Pan-STARRS1 $3π$ Steradian Survey. The catalog data products are available in the form of an SQL-based relational database from MAST, the Mikulski Archive for Space Telescopes at STScI. The database is described in detail, including the construction of the database, the provenance of the data, the schema, and how the database tables are related. Examples of queries for a range of science goals are included. The catalog data products are available in the form of an SQL-based relational database from MAST, the Mikulski Archive for Space Telescopes at STScI.

2. BACKGROUND

The Pan-STARRS1 Project partnered with Alex Szalay’s Johns Hopkins database group to build a publicly accessible hierarchical database, reusing SDSS software where possible. The design partitions data across servers while preserving a unified-table view and retains SQL Server tools, including Hierarchical Triangular Mesh.

Database development: Pan-STARRS1 partnered with Alex Szalay’s Johns Hopkins database group to develop a publicly accessible hierarchical database.The JHU team had been a major developer of the SDSS database.
Database development: The project aimed to reuse as much SDSS database software as possible.
Database architecture: Partitioning data across multiple server databases preserves a unified-table view for users.
Database architecture: Staying with SQL Server retains SDSS software tools, including Hierarchical Triangular Mesh.

3. DATA RELEASES

Pan-STARRS1 data releases begin with the 3π survey, covering sky north of −30 deg, and are planned to expand from stacked images and static catalogs to single-epoch products and additional survey data. DR1 provides stack images and the static sky catalog, while DR2 adds single-epoch imaging, detections, forced photometry, and improvements superseding DR1.

Data Release 1: The initial Pan-STARRS1 releases focus on the 3π survey, covering sky north of −30 deg, with Medium Deep Survey data planned thereafter.The paper describes multiple releases, beginning with the 3π survey and subsequently extending to the Medium Deep Survey.
Data Release 1: DR1, released in December 2016, includes 3π stack images and the static sky catalog.Its available pixel products include the PS1 stack images.
Data Release 2: DR2, scheduled for January 2019, adds single-epoch warp images with signal, mask, and variance maps, plus Detection and Forced* tables.The Detection tables contain single-epoch source detections, while the Forced* tables contain forced photometry.
Data Release 2: DR2 also improves the DR1 data products and supersedes them.Future releases are planned to provide 3π difference-image products.

4. OVERVIEW OF THE DATA PRODUCTS

The Pan-STARRS1 catalog data products are publicly accessed through MAST and organized into fundamental products, derived products, observational metadata, and system metadata. The database also provides user-oriented views and documentation for the 3π data releases, with most details applying to the Medium Deep fields.

Public Data Access: MAST provides access to Pan-STARRS data products, including pixel data, metadata, and source catalogs.Available products include FITS images, image cutouts, scriptable image access, and color JPEG images.
Database Organization: The catalog database schema is organized into four sections: Fundamental Data Products, Derived Data Products, Observational Metadata, and System Metadata.These sections cover calibrated catalog measurements, higher-order science products, exposure and image-combination information, and database or flag descriptions.
Fundamental Data Products: Catalog values are preferred to new measurements from released pixels because instrumental fluxes, magnitudes, and sky coordinates are recalibrated in the database.The recommendation applies to the recalibrated catalog products relative to direct measurements from available released pixel data.
Derived Data Products: Derived products such as proper motions and photometric redshifts were not yet available and were planned for later data releases.These are higher-order science products calculated from Fundamental Data Products.
Database Views: Database views simplify standard queries by joining commonly used tables or combining sky-area slices into full-sky views.The “Detection” view joins 32 Detection tables while hiding the individual tables from users.
Scope and Documentation: The paper documents data products and schema for the 3π data releases, while most details also apply to the Medium Deep fields.Additional documentation is provided with the data products through MAST.

5. FLOW OF DATA FROM PIPELINE TO THE PAN-STARRS CATALOG DATABASE

Pan-STARRS1 data flow begins with summit exposures processed by the Image Processing Pipeline into calibrated images and catalog files, then proceeds through DVO ingestion and IppToPsps transformation into the PSPS database. The pipeline includes chip, camera, warp, stack, difference-image, and forced-photometry stages that generate the products underlying the public catalog data.

IPP processing: Summit exposures are transferred to the IPP cluster, registered in metadata, and processed through chip and camera stages that detrend images, detect sources, perform photometry, and combine chip outputs.The metadata database tracks handling of more than 1.3 million images, while the IPP database tracks processing stages.
IPP processing: The warp stage projects chip outputs onto a common tangential RA/Dec layout using 0.25′′ pixels and 6242 × 6254 pixel skycells.For the 3π survey, skycells use the Rings.V3 tessellation.
Stack products: Stack, staticsky, and skycal stages combine warp exposures by skycell and filter, generate matched-source catalogs, and determine photometry and astrometry.Staticsky performs photometric analysis across all five filters and generates one catalog per filter for sources matched within a 5 pixel radius.
Difference and forced photometry: Difference processing creates WARP STACK diffs by subtracting appropriate deep stacks from good-quality warps, while forced photometry measures fluxes at stack-derived source positions and fits galaxy models.Forced photometry includes individual-warp measurements and galaxy-model fits on stacks; ForcedWarp* products are available starting with DR2.
Database ingestion: DVO stores calibrated mean source properties, and IppToPsps combines DVO information with camera, stack, forced-warp, and difference catalogs before transforming and loading batches into the PSPS schema.IppToPsps uses MySQL for processing tracking and temporary scratch databases and produces data stores that users interact with through PSPS.

6. PSPS

The Published Science Products Subsystem (PSPS) combines data transformation, loading, workflow management, partitioned storage, and user-facing retrieval components. Its design supports query access through the DRL while organizing ingestion and database operations across specialized systems.

Architecture: PSPS comprises the DXLayer, Object Database Manager, Workflow Manager Database, and data retrieval layer, which together transform, load, manage, and serve database data.Users access the data through the DRL via scripts, STScI CasJobs, or the consortium-only Published Science Interface.
Database organization: Distributed Partitioned Views divide tables into declination-based slices across linked servers, with partition layouts customized for the 3π and MD databases.Slices are designed to contain similar amounts of data.
Data ingestion: The DXLayer polls IppToPsps datastores for new batches, prepares them for loading, and supports batch verification before submission to the ODM.Batches contain products from different processing stages or different DVO files.
Operations and performance: The WMD records ODM workflows, logs, processes, and requests, while metadata and selected object tables are merged into a head database to provide faster queries.The database system uses Load, Cold, Warm, and Hot roles; the hot database serves the fast-response queue and the warm database serves the slow queue.
User access: The DRL manages user queries through an API, tracks their progress securely, and terminates queries that exceed resource or time limits.The DRL is based on CasJobs and is accessed through SOAP, enabling multiple database-access methods.

7. OVERVIEW OF THE PSPS DATABASE SCHEMA

The PSPS database schema comprises over 50 tables and uses a unique ObjID to organize detections of each astronomical object across Pan-STARRS1 data.

The PSPS database contains over 50 tables.
Each detected object is assigned a unique ObjID within the Pan-STARRS1 database.An object is defined as a source with measurable flux at a given R.A. and Dec.
Multiple detections of the same object generally share its ObjID, including detections within 1′′ that may not be associated with it.

schema.

The PS1 database schema comprises four table types, with object tables organizing source-level information and detection tables arranged by IPP processing stage. System metadata tables define survey-specific flags, filters, surveys, calibration, and processing-model information, while object tables use identifiers to connect sources with detections and related products.

Schema organization: Four main table types organize the PS1 database: Fundamental Data Product, Observational Metadata, Derived Data Product, and System Metadata tables.Fundamental Data Product tables divide into Object Tables and Detection Tables.
Schema organization: Detection tables are organized by IPP processing stage and combine Fundamental Data Product and Observational Metadata tables.This arrangement was chosen as the more natural organization for the detection portion of the schema.
System metadata: System metadata tables contain static Pan-STARRS1-specific information about flags, filters, surveys, and other system properties.Additional tables describe filters, surveys, fitting models, photometric calibration, stack types, difference types, and tessellation types.
System metadata: The schema contains 45 flag columns and 8 flag types, with dedicated information tables documenting the bits used by object, image, forced-shape, and detection flags.Table 8 organizes the available flags as a reference for selecting appropriate flag-information tables.
Object tables: Object tables provide source-level mean photometric and astrometric information, with ObjID serving as a unique identifier for objects across related data.The object tables originate from DVO and also include detection counts, statistics, and other object information.

GaiaFrameCoordinate

GaiaFrameCoordinate provides Gaia-calibrated positions for PSPS objects and is the preferred source of R.A. and Dec. for most non-moving-object uses in DR1. The table is DR1-only; in DR2, the Gaia-calibrated objects are in ObjectThin tables.

GaiaFrameCoordinate: GaiaFrameCoordinate contains PSPS objects calibrated against Gaia astrometry, providing the best R.A. and Dec. to use for an object.For joins to most tables, use ObjID.
GaiaFrameCoordinate: The table is present only in DR1; DR2 places PSPS objects calibrated against Gaia astrometry in ObjectThin tables.Thus, the appropriate Gaia-calibrated coordinate table depends on the database release.
GaiaFrameCoordinate: For single-epoch photometry, use R.A. and Dec. from the Detection table instead of GaiaFrameCoordinate.This recommendation distinguishes object-level Gaia-calibrated coordinates from coordinates associated with individual detections.

8. THE 3π DATABASE

The 3π database was built through staged, quality-controlled processing and ingestion of survey exposures into calibrated catalog products. It partitions the database into 32 declination-defined slices, while retaining small amounts of missing and duplicate data for later correction.

Processing and quality: 388,177 raw 3π exposures were identified, 381,279 (98.2%) queued for chip processing, and 375,573 (98.5%) completed PV3 processing with good quality.Exposures were excluded or lost because of observer flags, failed processing, bad seeing, camera issues, or other observing conditions.
Processing and quality: 379,551 (99.9%) of 379,973 fake/warp-stage exposures had good quality, producing 206177 distinct skycells, while 200,725 of 200,730 stack skycells were good.The lower stack count reflects a declination cut excluding stacks with insufficient coverage.
Database products: 19,266,450 forced-warp cmf files covered 373,743 exposures and 199151 distinct skycells, with 994,890 forced warps and no reported numerical inconsistencies.The slightly smaller skycell count than in the stack stages reflects the ragged survey edge near declination −30.
Database products: 998,101 expected skycal cmfs were all ingested, while 374,446 of 374,521 camera-stage smfs were ingested and 75 repeatedly failed and were excluded from PSPS.The camera stage produces one smf per exposure with extensions for each of the 60 chips.

9. CONCLUSION

The Pan-STARRS database provides the astronomical community with MAST access to 10,723,304,629 objects distilled from 1.6 petabytes of images and tables, alongside descriptions of its primary tables, quantities, and example queries. Combining this release with other large catalogs is expected to enable new scientific studies and support future high-dimensional survey science.

9. CONCLUSION: 10,723,304,629 objects are contained in the Pan-STARRS database, which distills 1.6 petabytes of images and tables into data accessible through MAST.The release is described as the largest data release from the largest digital sky survey to date.
9. CONCLUSION: The work describes the database’s primary tables and quantities, provides example queries, and addresses the challenge of sifting through its large volume.The catalog data products are intended to make the large database more usable for the astronomical community.
9. CONCLUSION: Combining Pan-STARRS with GALEX, 2MASS, and Gaia will provide a rich, high-dimensional dataset enabling new scientific studies.The combined catalogs may also yield astronomical discoveries not yet imagined.
9. CONCLUSION: The data release is expected to spur new interfaces and ways of working with high-dimensional datasets, supporting science with future surveys such as LSST.The authors identify these developments as critical to future survey science.

APPENDIX QUERY EXAMPLES

This section presents example queries for the Pan-STARRS1 database, progressing from simple to more complex cases with emphasis on DR1. It also specifies capitalization conventions for SQL and PSPS schema identifiers.

The examples progress from simple to more complicated Pan-STARRS1 database queries, focusing on DR1.Queries for DR2 are planned for later.
SQL reserved words and functions use capital letters, while PSPS database tables and columns use CamelCase.

1. Counting the number of rows in a large table

The section explains how to count rows in large Pan-STARRS1 tables, emphasizing that COUNT BIG() is required when row counts exceed the INT limit.

1. Counting the number of rows in a large table: COUNT BIG() returns a BIGINT, whereas COUNT() returns an INT capped at 2.14 billion, making COUNT BIG() necessary for sufficiently large PSPS tables.The query must be run in the slow queue.
1. Counting the number of rows in a large table: COUNT() is recommended unless the query involves large tables and large areas of sky; users should select the counting method appropriate to their data range.

SELECT COUNT BIG(objID) FROM ObjectThin

A query joins ObjectThin and MeanObject to return PSF magnitudes and errors for all grizy filters in a defined sky patch. It returns 3869 objects, most detected only once.

Query scope and fields: The query joins ObjectThin and MeanObject on objID for objects within 100.0 < raMean < 100.1 and 0.0 < decMean < 0.1.It selects objID, nDetections, mean coordinates, and PSF magnitudes with errors.
Query scope and fields: The query returns PSF magnitudes and errors for the g, r, i, z, and y filters.The selected fields are gMeanPSFMag, rMeanPSFMag, iMeanPSFMag, zMeanPSFMag, and yMeanPSFMag, with corresponding errors.
Query result: 3869 objects are returned, and the majority have been detected only once.This result describes the output of the rectangular sky-patch query.

3. Make a simple text histogram of ObjectThin.nDetections for a rectangular patch of sky

A saved MyDB query creates a text histogram of ObjectThin.nDetections by counting objects at each detection count. The ordered results show that most objects have 0–2 detections.

3. Make a simple text histogram of ObjectThin.nDetections for a rectangular patch of sky: The query groups MyDBtest objects by nDetections, counts each group, and orders the results by nDetections.The query is: SELECT nDetections, COUNT(nDetections) FROM MyDBtest GROUP BY nDetections ORDER BY nDetections.
3. Make a simple text histogram of ObjectThin.nDetections for a rectangular patch of sky: Most objects have 0–2 detections in the resulting histogram.The nDetections column represents how many times an object was detected.

5. Select stack PSF magnitudes for all filters for a rectangular patch of sky … IPPTOPSPS TRANSLATION TABLES

The paper demonstrates SQL queries for retrieving stack photometry, handling NULL values, constraining sky and magnitude ranges, and selecting specialized object samples. It also documents time-resolved photometry, database table contents, flags, and IPPTOPSPS data provenance.

5. Select stack PSF magnitudes for all filters for a rectangular patch of sky: A rectangular-patch query joins ObjectThin with StackObjectThin to retrieve stack PSF magnitudes and errors in all filters, returning 1808 objects.No nDetections restriction is required because stack sources are expected to be more likely astrophysical.
6. An example of finding rows with NULL values, using TOP to limit results: PSPS represents NULL values as -999, and TOP 10 limits a query for objects detected in single exposures but absent from stacks.The query selects rows where raStack = -999.
7. Basic search using BETWEEN to limit ranges; 8. Using built in functions to do a box search: SQL BETWEEN clauses constrain RA, declination, and i-band PSF magnitude, while ObjectThin’s HTM information supports rectangular searches through dbo.fGetObjFromRectEq.The example ranges are RA 100.0–100.1, declination 0.0–0.1, and iPSFMag 18.0–21.0.
9. Using built in functions to do a cone search: ObjectThin’s HTM, cx, cy, and cz fields also enable cone searches with dbo.fGetNearbyObjEq around specified coordinates and an angular radius.The example retrieves object positions and g-band PSF photometry within 0.2 degrees of RA 56.85 and Dec 24.12.
11. Galaxy Candidates for K2 SN Search: The examples identify galaxy candidates for K2 Campaign 14 using z ≤ 0.12, r ≤ 19.5, Kron–PSF separation ≥ 0.05, and majority-coverage requirements.The query returns stacked and mean coordinates, multi-band PSF and Kron photometry, detection counts, and quality measures.
12. Find the objID of a single object; 13. Obtain lightcurve for a given object (Detections): A Gaia-linked RR Lyrae example uses coordinates and identifiers to locate the object, yielding ObjID 109850463414820867 for subsequent light-curve queries.The target has period 0.55547 days and coordinates RA 46.341468915923, DEC 1.54199810825252.
14. Obtain lightcurve for a given object (Forced photometry); ABBREVIATIONS AND ACRONYMS; Single Exposure Detection Tables; Forced Mean Object Tables; Forced warp Exposure Tables; Diff Object Tables; Diff Detection Tables; IPPTOPSPS TRANSLATION TABLES: Detection and forced-photometry tables provide time-resolved PSF and aperture measurements, while documented schema tables, flags, survey abbreviations, and IPPTOPSPS translations define database contents and provenance.The figure notes agreement between IPP aperture and PSF fluxes and recommends checking photometric flags.