A Query to Export PanSTARRS-DR1 Data for Use with Sherlock

A Query to Export PanSTARRS-DR1 Data for Use with Sherlock

Inspection of the PanSTARRS-DR1 database tables to determine which data we need for Sherlock crossmatching
 • 
in     10.54 minutes read

PanSTARRS DR1 lives in Space-Telescope archives (images and catalogues) and a list of the DR1 database tables and their corresponding schema can be found here. We want to extract the minimum information from these tables required to integrate the catalogue into the Sherlock crossmatch-catalogues database. The big three checkboxes we need to hit are:

  1. Positions of sources
  2. Magnitudes of sources
  3. Star-galaxy separation metric(s) for each source

Detections vs Objects

Here are the formal definitions for detections and objects in PanSTARRS:

Detection

: source found in a single exposure

Object

: either a collection of detections matched across exposures, or : sources found in stacked images that combine multiple epochs to produce deeper detection limits

Pan-Starrs Catalogue DB Tables

In DR1 there are only 2 tables we need to focus on to extract what we need:

  1. StackObjectThin: Contains the positional and photometric information for all photometry of detections on the stacks. One detection of each object will be flagged as the best. This table can be used on its own as a primary source of stack photometry.
  2. StackObjectAttributes: For all stack detections it contains the PSFFlux, KronFlux, and APFlux fluxes (as opposed to magnitudes) for all filters in a single row, along with point-source object shape parameters (e.g. FWHM, moments). Also sky statistics, exposure times, basic s/g separation parameter. Needs to be JOINed to PS1 StackObjectThin table fields to get positions.

Come DR2 we will also target the Detection table, but for now a lot of the columns we require are not yet populated in this table.

Star-Galaxy Separation Recipes

This page mentions a ‘basic s/g separation parameter’ to be found in the StackObjectAttributes table:

https://outerspace.stsci.edu/display/PANSTARRS/PS1+Source+extraction+and+catalogs

The columns this comment relates to are the XExtNSigma columns (X = grizy). From the definition:

An extendedness measure for the g filter stack detection based on the deviation between PSF and Kron (1980) magnitudes, normalized by the PSF magnitude uncertainty.

it seems this metric is to be used with the first recipe found here. Not sure how normalisation with the PSF mag error works, but I think we can assume anything < 0 is a star and > 0 is a galaxy.

Alongside the basic s/g separation parameter in the StackObjectAttributes table, the PanSTARRS data archive pages have some other recipes for separating stars and galaxies. These recipes include the following parameters:

Extracting what we need

It seems from this information that everything we need from PanSTARRS DR1 is contained within the StackObjectThin and StackObjectAttributes tables. I’ve decided to include the detection flag columns in the export as it’s unclear how clean the data is in the stack tables and we may have to do some data scrubbing once we have the data locally.

Given the data required for the star-galaxy separation recipes alongside magnitude and portions, here are the definitions for the columns I think we need from the 2 tables:

StackObjectThin

Name Unit Data Type Size Default Value Description
objID dimensionless BIGINT 8 NA Unique object identifier.
uniquePspsSTid dimensionless BIGINT 8 NA Unique internal PSPS stack identifier.
primaryDetection dimensionless TINYINT 1 255 Identifies if this row is the primary stack detection. Note that in the DR1 database, about 0.5% of the objects have more than one entry with primaryDetection=1. This is expected to be fixed in the DR2 database.
gra degrees FLOAT 8 -999 Right ascension from g filter stack detection.
gdec degrees FLOAT 8 -999 Declination from g filter stack detection.
gPSFMag AB magnitudes REAL 4 -999 PSF magnitude from g filter stack detection.
gPSFMagErr AB magnitudes REAL 4 -999 Error in PSF magnitude from g filter stack detection.
gApMag AB magnitudes REAL 4 -999 Aperture magnitude from g filter stack detection.
gApMagErr AB magnitudes REAL 4 -999 Error in aperture magnitude from g filter stack detection.
gKronMag AB magnitudes REAL 4 -999 Kron (1980) magnitude from g filter stack detection.
gKronMagErr AB magnitudes REAL 4 -999 Error in Kron (1980) magnitude from g filter stack detection.
ginfoFlag dimensionless BIGINT 8 0 Information flag bitmask indicating details of the g filter stack photometry. Values listed in DetectionFlags.
ginfoFlag2 dimensionless INT 4 0 Information flag bitmask indicating details of the g filter stack photometry. Values listed in DetectionFlags2.
ginfoFlag3 dimensionless INT 4 0 Information flag bitmask indicating details of the g filter stack photometry. Values listed in DetectionFlags3.
rra degrees FLOAT 8 -999 Right ascension from r filter stack detection.
rdec degrees FLOAT 8 -999 Declination from r filter stack detection.
rPSFMag AB magnitudes REAL 4 -999 PSF magnitude from r filter stack detection.
rPSFMagErr AB magnitudes REAL 4 -999 Error in PSF magnitude from r filter stack detection.
rApMag AB magnitudes REAL 4 -999 Aperture magnitude from r filter stack detection.
rApMagErr AB magnitudes REAL 4 -999 Error in aperture magnitude from r filter stack detection.
rKronMag AB magnitudes REAL 4 -999 Kron (1980) magnitude from r filter stack detection.
rKronMagErr AB magnitudes REAL 4 -999 Error in Kron (1980) magnitude from r filter stack detection.
rinfoFlag dimensionless BIGINT 8 0 Information flag bitmask indicating details of the r filter stack photometry. Values listed in DetectionFlags.
rinfoFlag2 dimensionless INT 4 0 Information flag bitmask indicating details of the r filter stack photometry. Values listed in DetectionFlags2.
rinfoFlag3 dimensionless INT 4 0 Information flag bitmask indicating details of the r filter stack photometry. Values listed in DetectionFlags3.
ira degrees FLOAT 8 -999 Right ascension from i filter stack detection.
idec degrees FLOAT 8 -999 Declination from i filter stack detection.
iPSFMag AB magnitudes REAL 4 -999 PSF magnitude from i filter stack detection.
iPSFMagErr AB magnitudes REAL 4 -999 Error in PSF magnitude from i filter stack detection.
iApMag AB magnitudes REAL 4 -999 Aperture magnitude from i filter stack detection.
iApMagErr AB magnitudes REAL 4 -999 Error in aperture magnitude from i filter stack detection.
iKronMag AB magnitudes REAL 4 -999 Kron (1980) magnitude from i filter stack detection.
iKronMagErr AB magnitudes REAL 4 -999 Error in Kron (1980) magnitude from i filter stack detection.
iinfoFlag dimensionless BIGINT 8 0 Information flag bitmask indicating details of the i filter stack photometry. Values listed in DetectionFlags.
iinfoFlag2 dimensionless INT 4 0 Information flag bitmask indicating details of the i filter stack photometry. Values listed in DetectionFlags2.
iinfoFlag3 dimensionless INT 4 0 Information flag bitmask indicating details of the i filter stack photometry. Values listed in DetectionFlags3.
zra degrees FLOAT 8 -999 Right ascension from z filter stack detection.
zdec degrees FLOAT 8 -999 Declination from z filter stack detection.
zPSFMag AB magnitudes REAL 4 -999 PSF magnitude from z filter stack detection.
zPSFMagErr AB magnitudes REAL 4 -999 Error in PSF magnitude from z filter stack detection.
zApMag AB magnitudes REAL 4 -999 Aperture magnitude from z filter stack detection.
zApMagErr AB magnitudes REAL 4 -999 Error in aperture magnitude from z filter stack detection.
zKronMag AB magnitudes REAL 4 -999 Kron (1980) magnitude from z filter stack detection.
zKronMagErr AB magnitudes REAL 4 -999 Error in Kron (1980) magnitude from z filter stack detection.
zinfoFlag dimensionless BIGINT 8 0 Information flag bitmask indicating details of the z filter stack photometry. Values listed in DetectionFlags.
zinfoFlag2 dimensionless INT 4 0 Information flag bitmask indicating details of the z filter stack photometry. Values listed in DetectionFlags2.
zinfoFlag3 dimensionless INT 4 0 Information flag bitmask indicating details of the z filter stack photometry. Values listed in DetectionFlags3.
yra degrees FLOAT 8 -999 Right ascension from y filter stack detection.
ydec degrees FLOAT 8 -999 Declination from y filter stack detection.
yPSFMag AB magnitudes REAL 4 -999 PSF magnitude from y filter stack detection.
yPSFMagErr AB magnitudes REAL 4 -999 Error in PSF magnitude from y filter stack detection.
yApMag AB magnitudes REAL 4 -999 Aperture magnitude from y filter stack detection.
yApMagErr AB magnitudes REAL 4 -999 Error in aperture magnitude from y filter stack detection.
yKronMag AB magnitudes REAL 4 -999 Kron (1980) magnitude from y filter stack detection.
yKronMagErr AB magnitudes REAL 4 -999 Error in Kron (1980) magnitude from y filter stack detection.
yinfoFlag dimensionless BIGINT 8 0 Information flag bitmask indicating details of the y filter stack photometry. Values listed in DetectionFlags.
yinfoFlag2 dimensionless INT 4 0 Information flag bitmask indicating details of the y filter stack photometry. Values listed in DetectionFlags2.
yinfoFlag3 dimensionless INT 4 0 Information flag bitmask indicating details of the y filter stack photometry. Values listed in DetectionFlags3.

StackObjectAttributes

Name Unit Data Type Size Default Value Description
objID dimensionless BIGINT 8 NA Unique object identifier.
uniquePspsSTid dimensionless BIGINT 8 NA Unique internal PSPS stack identifier.
primaryDetection dimensionless TINYINT 1 255 Identifies if this row is the primary stack detection.
bestDetection dimensionless TINYINT 1 255 Identifies if this row is the best detection.
gpsfLikelihood dimensionless REAL 4 -999 Likelihood that this g filter stack detection is best fit by a PSF.
gmomentR1 arcsec REAL 4 -999 First radial moment for g filter stack detection.
gmomentRH arcsec0.5 REAL 4 -999 Half radial moment (r0.5 weighting) for g filter stack detection.
gKronRad arcsec REAL 4 -999 Kron (1980) radius from g filter stack detection.
gExtNSigma dimensionless REAL 4 -999 An extendedness measure for the g filter stack detection based on the deviation between PSF and Kron (1980) magnitudes, normalized by the PSF magnitude uncertainty.
rpsfLikelihood dimensionless REAL 4 -999 Likelihood that this r filter stack detection is best fit by a PSF.
rmomentR1 arcsec REAL 4 -999 First radial moment for r filter stack detection.
rmomentRH arcsec0.5 REAL 4 -999 Half radial moment (r0.5 weighting) for r filter stack detection.
rKronRad arcsec REAL 4 -999 Kron (1980) radius from r filter stack detection.
rExtNSigma dimensionless REAL 4 -999 An extendedness measure for the r filter stack detection based on the deviation between PSF and Kron (1980) magnitudes, normalized by the PSF magnitude uncertainty.
ipsfLikelihood dimensionless REAL 4 -999 Likelihood that this i filter stack detection is best fit by a PSF.
imomentR1 arcsec REAL 4 -999 First radial moment for i filter stack detection.
imomentRH arcsec0.5 REAL 4 -999 Half radial moment (r0.5 weighting) for i filter stack detection.
iKronRad arcsec REAL 4 -999 Kron (1980) radius from i filter stack detection.
iExtNSigma dimensionless REAL 4 -999 An extendedness measure for the i filter stack detection based on the deviation between PSF and Kron (1980) magnitudes, normalized by the PSF magnitude uncertainty.
zpsfLikelihood dimensionless REAL 4 -999 Likelihood that this z filter stack detection is best fit by a PSF.
zmomentR1 arcsec REAL 4 -999 First radial moment for z filter stack detection.
zmomentRH arcsec0.5 REAL 4 -999 Half radial moment (r0.5 weighting) for z filter stack detection.
zKronRad arcsec REAL 4 -999 Kron (1980) radius from z filter stack detection.
zExtNSigma dimensionless REAL 4 -999 An extendedness measure for the z filter stack detection based on the deviation between PSF and Kron (1980) magnitudes, normalized by the PSF magnitude uncertainty.
ypsfLikelihood dimensionless REAL 4 -999 Likelihood that this y filter stack detection is best fit by a PSF.
ymomentR1 arcsec REAL 4 -999 First radial moment for y filter stack detection.
ymomentRH arcsec0.5 REAL 4 -999 Half radial moment (r0.5 weighting) for y filter stack detection.
yKronRad arcsec REAL 4 -999 Kron (1980) radius from y filter stack detection.
yExtNSigma dimensionless REAL 4 -999 An extendedness measure for the y filter stack detection based on the deviation between PSF and Kron (1980) magnitudes, normalized by the PSF magnitude uncertainty.

Final MAST Query

According to the PanSTARRS database documentation, the bestDetection should not be trusted for DR1, but to instead request primaryDetection=1 to receive unique object rows. But also note that in the DR1 database, about 0.5% of the objects have more than one entry with primaryDetection=1!

Here then is the final query I’ve developed that should get us all the data we need:

SELECT 
    a.objID,
    o.objID,
    a.uniquePspsSTid,
    o.uniquePspsSTid,
    a.primaryDetection,
    a.bestDetection,
    a.gra,
    a.gdec,
    a.gPSFMag,
    a.gPSFMagErr,
    a.gApMag,
    a.gApMagErr,
    a.gKronMag,
    a.gKronMagErr,
    a.ginfoFlag,
    a.ginfoFlag2,
    a.ginfoFlag3,
    a.rra,
    a.rdec,
    a.rPSFMag,
    a.rPSFMagErr,
    a.rApMag,
    a.rApMagErr,
    a.rKronMag,
    a.rKronMagErr,
    a.rinfoFlag,
    a.rinfoFlag2,
    a.rinfoFlag3,
    a.ira,
    a.idec,
    a.iPSFMag,
    a.iPSFMagErr,
    a.iApMag,
    a.iApMagErr,
    a.iKronMag,
    a.iKronMagErr,
    a.iinfoFlag,
    a.iinfoFlag2,
    a.iinfoFlag3,
    a.zra,
    a.zdec,
    a.zPSFMag,
    a.zPSFMagErr,
    a.zApMag,
    a.zApMagErr,
    a.zKronMag,
    a.zKronMagErr,
    a.zinfoFlag,
    a.zinfoFlag2,
    a.zinfoFlag3,
    a.yra,
    a.ydec,
    a.yPSFMag,
    a.yPSFMagErr,
    a.yApMag,
    a.yApMagErr,
    a.yKronMag,
    a.yKronMagErr,
    a.yinfoFlag,
    a.yinfoFlag2,
    a.yinfoFlag3,
    o.gpsfLikelihood,
    o.gmomentR1,
    o.gmomentRH,
    o.gKronRad,
    o.gExtNSigma,
    o.rpsfLikelihood,
    o.rmomentR1,
    o.rmomentRH,
    o.rKronRad,
    o.rExtNSigma,
    o.ipsfLikelihood,
    o.imomentR1,
    o.imomentRH,
    o.iKronRad,
    o.iExtNSigma,
    o.zpsfLikelihood,
    o.zmomentR1,
    o.zmomentRH,
    o.zKronRad,
    o.zExtNSigma,
    o.ypsfLikelihood,
    o.ymomentR1,
    o.ymomentRH,
    o.yKronRad,
    o.yExtNSigma
FROM
    StackObjectThin a
        JOIN
    StackObjectAttributes AS o ON a.objid = o.objid
WHERE
    a.primaryDetection = 1;

You can download the FITS binary sample of the data here, which is easily opened and viewed in topcat.


Tags:  •