MAST has recently enabled TAP services to highly performant Postgres databases, which offer improved performance relative to the legacy databases. The PS1 DR2 catalog is the first catalog available through this new TAP service, under the schema ps1_dr2 (used as a prefix before the table names in TAP queries). Because both services are still available, the documentation for the PS1 catalogs (Source extraction and catalogs, Database object and detection tables) remains unchanged. This guide, together with the current catalog documentation, provides information on the PS1 DR2 tables and columns necessary to construct queries to the new Postgres TAP service.
Catalog Schema Browser and Catalogs Search
The Catalog Schema Browser can be used to explore the schemas for all TAP-accessible tables in the Postgres database. A subset of those tables are also available to be queried via MAST's Catalogs Search.
Overview of Key Migration Differences
The PS1 DR2 dataset on the new Postgres database contains a different set of tables and has differences in which columns are included in each table compared to the legacy database. This change arises because in some cases (as detailed below in Table 1 and Table 2), views in the legacy database were migrated to be tables in the Postgres database.
In addition to table-column content changes, note also that the new table names follow "snake case" (all lower case, with underscore separation), compared to "camel case" (capitalized words with no separation) in the legacy TAP service and CASJobs. The column names remain the same as in the legacy database.
Case Insensitive Column Names
As with the legacy database, formally the table and column names are case insensitive in the Postgres database. We recommend not "double quoting" column names, which renders the names case sensitive in ADQL.
There are a handful of key differences between the legacy database and the new Postgres database:
| Difference |
|---|
| All table names have been changed from "camel case" to "snake case", e.g., detection_flags is the equivalent of the legacy DetectionFlags. |
| MeanObjectView (join of MeanObject and ObjectThin) has been migrated as mean_object. |
| ForcedMeanObjectView (join of ForcedMeanObject and ObjectThin) has been migrated as forced_mean_object. |
| StackObjectView (join of ObjectThin, StackObjectThin, StackObjectAttributes) has been migrated as stack_object. |
| The legacy ObjectThin table was not migrated. Contents are available through one of mean_object, forced_mean_object, and stack_object. See PS1 DR2 Postgres caveats for more details. |
New Tap Endpoint
PS1 DR2 data now lives alongside other catalogs at the new MAST Cluster Catalogs TAP endpoint: https://mast.stsci.edu/vo-tap/api/v0.1/mast_catalogs/ Enter that TAP URL into software like TOPCAT, TAPsh, or pyvo, and query the ps1_dr2 database therein.
Migration Checklist
- Determine which Postgres table(s) contain the relevant column(s) for your query. See Table 1 and Table 2 below. In many cases, you may be able to simplify and avoid joins or using views, as columns from multiple Legacy DB tables are now available in one single Postgres table.
- Note in particular that there are changes to the mean_object, forced_mean_object, and stack_object tables – the new tables contain the legacy table columns as well as new columns from related tables which were often joined with the "main" tables, particularly the legacy ObjectThin table.
Queries that required joins between ObjectThin and the MeanObject, ForcedMeanObject, and StackObjectThin/StackObjectAttribute tables can now be rewritten without joins!
- Note in particular that there are changes to the mean_object, forced_mean_object, and stack_object tables – the new tables contain the legacy table columns as well as new columns from related tables which were often joined with the "main" tables, particularly the legacy ObjectThin table.
- Use the same column names as in the legacy table documentation. The column names for the Postgres TAP service remain the same as before, though tables columns may now be available in different tables; see point 1.
- For querying help, view "References" on MAST Cluster Catalogs Query Management. This section includes links to general references on ADQL (the language used for TAP queries) and TAP.
Tables Migrated with Changes between Legacy Service and Postgres
Migration Changes
The following PS1 DR2 tables have changes between the legacy CASJobs service and Postgres!
Table 1: PS1 DR2 Postgres Tables with Direct Equivalent to Legacy Service Views
| Table | Description | Combination of Legacy Tables | Direct Legacy Equivalent |
|---|---|---|---|
| mean_object | Contains the positional and mean photometric information for objects based on the single epoch data. | MeanObject and ObjectThin tables, joined on objID | MeanObjectView view |
| stack_object | Contains the positional and photometric information for point-source photometry of stack detections. | ObjectThin, StackObjectThin, StackObjectAttributes tables, joined on objID | StackObjectView view |
| forced_mean_object | Contains the mean of single-epoch photometric information for sources detected in the stacked data, extracted in fixed apertures. | ForcedMeanObject and ObjectThin tables, joined on objID | ForcedMeanObjectView view |
Table 2: Translation from Legacy PS1 DR2 Tables Migrated with Changes to Postgres Service
See Table 1 for information about migration changes. For details on missing rows due to joins of legacy tables, see PS1 DR2 Postgres caveats.
Legacy Table | Closest equivalent Postgres TAP table to use |
|---|---|
| ObjectThin | No direct equivalent. Use mean_object or stack_object. |
| MeanObject | mean_object |
| ForcedMeanObject | forced_mean_object |
| StackObjectThin | stack_object |
Tables Migrated Without Changes
Table 3: PS1 DR2 Postgres Tables with Direct Equivalent to Legacy Service
These tables migrated with a direct equivalent.
| Table | Description | Directly Equivalent Legacy Table |
|---|---|---|
| detection | Contains single epoch photometry of individual detections from a single exposure. | |
| detection_flags | Information flag values for detections. | |
| detection_flags_2 | Information flag values for detections. | |
| detection_flags_3 | Information flag values for detections. | |
| diff_det_eff_meta | Detection efficiency information for a given individual difference image. | |
| diff_meta | Metadata related to a difference image constructed by subtracting a stacked image from a single epoch image, or in the case of the MD Survey from a nightly stack (stack made from all exposures in a single filter in a single night). | |
| diff_to_image | The mapping of which input images were used to construct a particular difference image. | |
| diff_type | Descriptions of the types of difference images constructed | |
| filter | Description of the optical filters used in the survey (Tonry et al. 2012). | |
| fit_model | Descriptions of the models used in fitting detections in images, both PSF-like and extended galaxies. | |
| forced_galaxy_shape | Extended source galaxy shape parameters. | |
| forced_galaxy_shape_flags | Information flag values that define ForcedGalaxyShape chisq surface failures. | |
| forced_warp_extended | Single epoch forced photometry fluxes within the SDSS R5 (r = 3.00 arcsec), R6 (r = 4.63 arcsec), and R7 (r = 7.43 arcsec) apertures (Stoughton 2002) for objects detected in the stacked images. | |
| forced_warp_lensing | Kaiser et al. (1995) lensing parameters measured from the forced photometry of objects detected in stacked images on the individual single epoch data. | |
| forced_warp_masked | Entries for objects detected in the stacked images which were in the footprint of a single epoch exposure, but for which there are no unmasked pixels at that epoch. | |
| forced_warp_measurement | Contains single epoch forced photometry of individual measurements of objects detected in the stacked images. | |
| forced_warp_meta | Metadata related to a sky-aligned distortion corrected warp image, upon which forced photometry is performed. | |
| forced_warp_to_image | Mapping of which input image comprises a particular warp image used for forced photometry. | |
| frame_meta | Metadata related to an individual exposure. | |
| image_det_eff_meta | Detection efficiency information for a given individual OTA image. | |
| image_flags | Information flag values for images. | |
| image_meta | Metadata related to an individual OTA image that comprises a portion of the full exposure. | |
| object_filter_flags | Contains information flags for the photometric calibration of an object. | |
| object_info_flags | Contains information flag values for objects. | |
| object_quality_flags | Information flag values that denote if an object is real or a possible false positive. | |
| photo_cal | Contains photometric calibration information for each filter and detector image combinations. | |
| stack_ap_flx | Contains aperture fluxes in multiple radius apertures for both unconvolved and PSF-matched fluxes. | |
| stack_ap_flx_ex_gal_con_6 | Contains aperture fluxes in multiple radius apertures for extended objects, with the images convolved to a target of 6 sky pixels. | |
| stack_ap_flx_ex_gal_con_8 | Contains aperture fluxes in multiple radius apertures for extended objects, with the images convolved to a target of 8 sky pixels. | |
| stack_ap_flx_ex_gal_unc | Contains unconvolved aperture fluxes in multiple radius apertures for extended objects. | |
| stack_det_eff_meta | Contains the detection efficiency information for a given stacked image. | |
| stack_meta | Contains the metadata describing the stacked image produced from the combination of a set of single epoch exposures. | |
| stack_model_fit_de_v | Contains the de Vaucouleurs (1948) fit parameters to extended sources. | |
| stack_model_fit_exp | Contains the exponential fit parameters to extended sources. | |
| stack_model_fit_extra | Contains the galaxy shape and concentration parameters measured from the stack detections. | |
| stack_model_fit_ser | Contains the Sersic (1963) fit parameters to extended sources. | |
| stack_object_attributes | Contains a variety of flux measurements for all filters from stack detections, along with point source shape parameters, sky statistics, exposure parameters, and a basic s/g separation parameter. | |
stack_petrosian | Contains the Petrosian (1976) magnitudes and radii for extended sources. | |
| stack_to_frame | Contains the mapping of input frames used to construct a particular stack along with processing stats. | |
| stack_to_image | Contains the mapping of which input images were used to construct a particular stack. | |
| stack_type | Contains descriptions of the types of stacked images constructed. | |
| survey | Contains descriptions of the various PS1 Science Consortium Surveys. | |
| tessellation_type | Contains descriptions of the types of image tessellations for the sky. |
PS1 DR2 Postgres Caveats
Users of the Postgres PS1 DR2 data should be aware of a few issues and inconsistencies in the data.
Objects in mean, stack tables without entries in ObjectThin
In migrating the PS1 tables from the legacy to the Postgres database, three legacy views were migrated and stored directly as tables on Postgres: MeanObjectView was migrated to mean_object, StackObjectView was migrated to stack_object, and ForcedMeanObjectView was migrated to forced_mean_object.
However, the legacy ObjectThin table does not contain all objects. As discussed in the PS1 DR2 caveats, there are some "holes" in the ObjectThin table relative to the MeanObject table (and also the ForcedMeanObject table). Additionally, all stack sources with declination less than -30 degrees were removed from the PS1 DR2 ObjectThin table, but are present in the stack tables. Thus, some rows of the legacy MeanObject, ForcedMeanObject, StackObjectThin, and StackObjectAttributes tables without matches in ObjectThin are missing in the Postgres mean_object and stack_object tables.
The portions of the legacy MeanObject, ForcedMeanObject, StackObjectThin, and StackObjectAttributes tables which are not contained in ObjectThin were instead migrated as separate "orphan" tables, as noted below.
Other "orphaned" data
Due to the triple-table view migrated to stack_object, there are a few entries from legacy StackObjectThin table that were orphaned because objects are missing from the legacy StackObjectAttributes, which are contained within the orphan_stack_object_thin2 table.
Finally, a set of flag columns (ginfoFlag4, rinfoFlag4, iinfoFlag4, zinfoFlag4, yinfoFlag4) from the legacy StackObjectThin are missing from the extraction to the stack_object table. These columns (which are currently missing descriptions from the PS1 data product paper, and thus from the documentation) are made available in the orphan_stack_object_thin_flags4 table.
"Orphan Object" Tables
| Table | Description | Columns | Number of rows |
|---|---|---|---|
orphan_mean_object | Contains the mean photometric information for objects based on the single epoch data, for objects which are missing from ObjectThin. | Contains the same columns as the legacy MeanObject table | 1,392,610 |
orphan_forced_mean_object | Contains the mean of single-epoch photometric information for sources detected in the stacked data, extracted in fixed apertures, for objects which are missing from ObjectThin. | Contains the same columns as the legacy ForcedMeanObject table. | 41,560,323 |
orphan_stack_object_thin | Contains the positional and photometric information for point-source photometry of stack detections, for objects which are missing from ObjectThin. | Contains the same columns as the legacy StackObjectThin table. | 65,080,164 |
orphan_stack_object_attributes | Contains the PSF, Kron (1980), and aperture fluxes for all filters, along with point-source object shape parameters, for objects which are missing from ObjectThin. | Contains the same columns as the legacy StackObjectAttributes table. | 65,079,949 |
orphan_stack_object_thin_flags4 | Flag columns from StackObjectThin missing from extraction to stack_object. | Contains the columns objID, uniquePspsSTid, randomStackObjID, ginfoFlag4, rinfoFlag4, iinfoFlag4, zinfoFlag4, yinfoFlag4 (descriptions missing from PS1 data product paper). | 3,478,242,686 |
orphan_stack_object_thin2 | Contains the positional and photometric information for point-source photometry of stack detections, for objects which are missing from StackObjectAttributes. | Contains the same columns as the legacy StackObjectThin table. | 194 |
The entries in orphans_mean_object all lack positions. Furthermore, for the stack "orphan" tables, the positions lack Gaia astrometric corrections, which were applied only to the objects in the ObjectThin catalog.