Database (PDR3)

Updates since PDR2

There are a number of database changes since PDR2:

  • Uncertainties were referred to as  sigma in PDR2, but it is now called err.  Your SQL script will have to be updated when you recycle queries from PDR2.
  • There was a smallcat table, which contains some of the most useful columns from the forced and meas table.  It has been updated to a summary table.  We encourage the user to start with this table.
  • coord is normalized to unity (it was not in PDR2).
  • There is a new column in the forced table, field.   This can be used to query objects in a specific field, e.g., WHERE field = 'cosmos' yields objects in the D/UD-COSMOS field.  The available field names are cosmos, deep2-3, elais-n1, xmm-lss for D/UD fields.  For Wide, hectomap is available for the Hectomap field.  The equatorial stripes are split into R.A., and equator00 returns objects in tracts whose centers are at 0-1h, equator01 includes tracts with centers between 1-2h, and so on.  Note that the search is done on a tract by tract basis and equator00 includes objects outside of R.A.=0-1h because a tract located at, e.g., R.A.=0.9h extends to R.A.>1h.  You can use coneSearch or boxSearch to query objects at specific R.A., Dec ranges.

Summary table (note added in Nov 2023)

There is a summary table in the database, which is a collection of primary objects with selected columns such as coordinates and fluxes. There is no need to join with other tables (unless you need other information that is not in the summary table) and your query will run fast on this table. We suggest the first-time user to start with it. However, in addition to the isprimary cut, we also applied the following flag cut:

  • g_is_clean_centerpixels = True OR
  • r_is_clean_centerpixels = True OR
  • i_is_clean_centerpixels = True OR
  • z_is_clean_centerpixels = True OR
  • y_is_clean_centerpixels = True

If this cut is going to be problematic for you, then you should make your own query (i.e., join forced tables).

Database Tables

The Deep+UltraDeep and Wide layers are stored in separate database schemas named pdr3_dud and pdr3_wide, respectively. Note that ‘dud’ here means Deep+UltraDeep, not a bad table!  In each schema, there are meta tables and object catalog tables. Be sure to use the right schema/table for your search, e.g., if you search for objects in the Deep-ELAIS-N1 field using a table in the Wide schema, you will find no objects.  Also, be sure to choose the right release; if you use the online SQL editor, choose PDR3 from the release pull-down menu.  Due to a limitation of a database engine, we split the forced and unforced measurements into multiple tables (forced[2345] and meas[234]).  You can join these tables with object ID.  There is a summary table that condensed the most useful columns from the forced and meas table into a single table.  We believe 80% of the user can do their science with just table only and we suggest you start with it.

All the tables in database can be queried by using the CAS (Catalog Archive Server) search with online SQL editor. There are some useful sample SQLs in the CAS search page. See the document of Stored Functions, as well.

Schema Table Notes
Meta Information pdr3_[dud/wide] mosaic, frame, wcs etc. Meta information of image data, such as time, filter, wcs, etc, for each image. This table is generated from the header information of the pipeline products. Most users do not need to directly use this table.
Object Catalogs (Summary Table) pdr3_[dud/wide] forced, forced[2/3/4/5]
Measurements of forced photometry. We use the extinction map of Schlegel, Finkbeiner, and Davis (1998) to compute the reddening (Galactic extinction) in the HSC filters.
pdr3_[dud/wide] meas, meas[2/3/4]
Measurements of unforced photometry. Due to the limitation of the postgreSQL database engine, we split the unforced photometry table into multiple separate tables. If you query the meas tables, you want to join the two tables using object_id.
Observational Log obslog obslog_pdr Observing logs. This includes seeing, transparency, etc, from the on-site reduction system.
Quality Assuarance pdr3_[dud/wide] patch_qa Table with color offsets in the stellar sequence on color-color diagrams as well as average depth and seeing estimates for each patch.
Value-Added pdr3_[dud/wide] specz Collection of public spectroscopic redshifts. See this page for details.
pdr3_[dud/wide] random Set of randomly generated points within the survey footprints for clustering analysis. See this page for details.
pdr3_[dud/wide] mask
Bright star masks.  Masks for various features are available.  See this page for details.
Calibration pdr3_[dud/wide] magcorr The magnitude offsets in this table corrects the r/i photometry into r2/i2 for each object.  We encourage the user to applythe offsets.  The offses have to be subtracted from the catalog magnitudes.  Also, not all objects have the correction (in rare cases, effective response was not generated) and make sure to use LEFT JOIN.  The magnitudes in this table has to be subtracted from the catalogued magnitudes.
pdr3_[dud/wide] stellar_sequence_offsets
Photometric zero-point offsets for each patch estimated from the location of the stellar sequence.  This table fixes a photometry issue introduced by FGCM and we encourage the user to apply the offsets.  The magnitudes in this table has to be subtracted from the catalogued magnitudes.

Database Records

Summary of Public Release Catalog Database. The last column shows the number of primary objects in the forced table.
Survey/Field Name Filters Tract No. of objects
UDEEP+DEEP g,r,i,z,y,NB0387,NB0816,NB0921,NB1010 19,051,243
COSMOS g,r,i,z,y,NB0387,NB0816,NB0921,NB1010 9569-9572, 9812-9814, 10054-10056 5,495,012
DEEP2-3 g,r,i,z,y,NB0387,NB0816,NB0921 9219-9221, 9462-9465, 9706-9708 4,114,697
ELAIS-N1 g,r,i,z,y,NB0816,NB0921 16984-16985, 17129-17131, 17270-17272, 17406-17407 4,826,118
SXDS+XMM-LSS g,r,i,z,y,NB0387,NB0816,NB0921,NB1010 8282-8284, 8523-8525, 8765-8767 4,615,416
WIDE g,r,i,z,y 507,215,729
Spring field g,r,i,z,y 9069-9136, 9312-9379, 9555-9622, 9797-9864,10039-10106, 10282-10348 281,969,880
Autumn field g,r,i,z,y 8278-8286, 8519-8527, 8761-8769, 8984-9011, 9206-9253, 9448-9496, 9691-9739, 9933-9981, 10175-10223, 10418-10437, 10659-10677, 10899-10904, 10912-10917 184,911,815
Hectomap g,r,i,z,y 15808-15834, 15987-16012, 16162-16186 39,422,651
(AEGIS) g,r,i,z,y 16821-16822, 16972-16973 911,383

The Number of Records in Public Data Release Database Tables
Table Table Name Table Description UDEEP+DEEP WIDE
Frame frame CCD Image Meta 433,746 2,167,558
Warped warped Warped Image Meta 364,919 1,584,918
Mosaic mosaic Mosaic Image Meta (Coadd) 11,794 247,282
Mosaic Meas meas* Objects detected in Coadd in multiBand photometry 31,004,134 830,139,306
Mosaic Forced forced* Objects detected in Coadd in forced photometry 31,004,134 830,139,306