PSPS to IPP

back up to ippToPspsSAS39BillsNotes

Notes on tracking data in the PSPS database back to the DVO and IPP.

Detection to single frame data

exp_id = frameID = Detection.imageID / 100

or using joins back to FrameMeta one can find frameID (exp_id) and frameName (exp_name)

    exp_id = frameID [ select ImageMeta.frameID from Detection join ImageMeta on Detection.imageID = ImageMeta.imageID ]

    exp_name = frameName [ select FrameMeta.frameName 
                from Detection 
                join ImageMeta on Detection.imageID = ImageMeta.imageID 
                join FrameMeta on ImageMeta.frameID = FrameMeta.frameName ]

chip, camera, and warp IDS cannot be found directly in PSPS. Need to go to gpc1 database using the frameID obtained above.

Using the gpc1 database

    select chip_id, cam_id, warp_id from relExp join ippRelease using(rel_id) 
        where exp_id = frameID and release_name = '3PI.PV3'

From a script these data can also be obtained with the command

releasetool -listrelexp -exp_id $frameID -release_name 3PI.PV3

SUGGESTION: I think we should add chip_id, warp_id, and cam_id to FrameMeta. The first two are useful for postage stamps and cam_id would be useful for tracking/debugging.)

Detection to corresponding row in CMF

Find cam_id as above, then find the cmf from camProcessedExp. Then in the cmf the detection is the one with

    [cmf].IPP_IDET = Detection.ippDetectID 

Detection to corresponding row in DVO

---

mostly dvo queries to particular detections are by obsTime.

Here is how the detection column values are set.

objID           = PSPS_OBJ_ID = [dvo ave file].EXT_ID (as defined by Jim Heasley's formula)
detectID        = meas.EXT_ID (as defined by Jim Heasley's formula)
imageID         = exp_id * 100 + ccdnum
dvoRegionID     = meas.CAT_ID
ippDetectID     = meas.DET_ID = [cmf].IPP_IDET (My old wiki page said that this was measurement.IMAGE_ID << 32 | meas.DET_ID) 
ippObjID        = meas.CAT_ID << 32 + meas.OBJ_ID

So to find the catalog file

    cat_id = Detection.dvoRegionID

The OBJ_ID is the lower 32 bits of ippObjID
    OBJ_ID = ippObjID & 0xFFffFFff

The "directory" for the dvo catalog is SkyTable.fits. The file is then the entry with

[Images.dat].INDEX = cat_id

Example if cat_id = 93255 then

    fileName = [SkyTable.fits[93255]].NAME = "s0000/5228.06"

And the various dvo files may be found from there.

file	description	EXTNAME
cpt	objects	DVO_AVERAGE_PS1_V5
cpm	measures	DVO_MEASURE_PS1_V5
cps	secfilt	DVO_SECFILT_PS1_V5
cpn	missing	DVO_MISSING
cpx	lensing	DVO_LENSING_PS1_V5
cpy	lensobj	DVO_LENSOBJ_PS1_V5
cpz		DVO_STARPAR_PS1_V5
cpq		DVO_GALPHOT_PS1_V5

How can we get at just the detections for the specific image?

This would require going to the ipp database with two commands

    releasetool -listrelexp -exp_id $frameID -release_name SAS.39
    chiptool -processedimfile -chip_id $chip_id -class_id "XY$ccdnum'

(need to use sprintf for class_id value to make sure leading zero is
there for ccdnum < 10)

Then taking chip_imfile_id from the chiptool cmd results look in Images table find the row with

    [Images.dat].EXTERN_ID == chip_imfile_id

and take the value of the column IMAGE_ID and use it to select rows in the measurements from this chip have with that IMAGE_ID

FullForce Measurement to exposure

This was discussed on the psps working group mailing list. There is a table which is the list of imageIDs that went into a ForcedWarpMeta (fullForceRun).

One can find the exposure for a given measurement using the forcedWarpID

    select DISTINCT ForcedWarpToImage.imageID/100 as frameID 
        FROM ForcedWarpMeasurement 
        JOIN ForcedWarpToImage 
        ON ForcedWarpMeasurement.forcedWarpID = ForcedWarpToImage.forcedWarpID

SUGGESTION: Better to add frameID to ForcedWarpMeta (and for postage stamps warp_id would be useful too, but if we add warp_id to FrameMeta that wouldn't be required.)

Then given the exp_id, and skyCellID one can go to the IPP's gpc1 database and find the fullForceResult. (Well, one would need to know the label or the skycal_id).

    fftool -result -label SAS.20141118.ff8 -exp_id $frameID -tess_id $tessID -skycell_id $skyCellID

Stack Tables

The following values in the stack tables are useful.

StackObjectThin

objID, ippObjID, dvoRegionID, ippObjID are the same as for a single frame detection
xstackDetectID  = meas.EXT_ID (corresponds to detectID computed with Jim Heasley's formula)
xstackMetaID    = stack_id

StackObjectAttributes
xiippDetectID   = meas.DET_ID = [cmf].IPP_IDET

Note: x == one of (g r i z y w)

Stack Detection to cmf

stackMetaID is the stack_id. To find the skycal cmf we can use releasetool

releasetool -listrelstack -release_name SAS.39 -stack_id 3942203 | grep skycal_path_base | awk '{print $3 ".cmf"}'

SUGGESTION: It might be nice to add skycalID to StackMeta but since we need to look up the path_base that doesn't really avoid any work.

Stack Detection to DVO

The steps to be followed are similar to the steps for single frame detections.

1. Go to Images.dat to find the IMAGE_ID in the row where EXTERN_ID == stackMetaID
2. Go to SkyTable.fits to find the NAME of the catalog file with CAT_ID == dvoRegionID
3. Look for measurement(s) with IMAGE_ID and DET_ID = xippDetectID