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.)

UPDATE ippChipID, ippCamID, and ippWarpID will be added to FrameMeta beginning with SAS.40.

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.) I think it would also be useful to be able to trace back to the stack that was the source of the positions by having the stackMetaID in ForcedWarpMeta

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).

UPDATE: For SAS.40 frameID will be added to ForcedWarpMeta. Note also that ForcedWarpMeta.posImageID = warp_id

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

This result will also give the ff_id of the corresponding run.

ForcedWarpMeasurement to DVO

The entry in the dvo images table is the one where EXTERN_ID == the warp_skyfile_id of the corresponding warp. This can be found from

warptool -warped -exp_id 356845 -skycell_id skycell.1315.036 -label SAS.20141118

SUGGESTION: I have added warp_skyfile_id to the output of fftool -result so this step can be avoided.

The steps to find data in the dvo files is similar to that for regular detections.

1. Look for IMAGE_ID of entry in Images.dat with EXTERN_ID == warp_skyfile_id
2. Use dvoRegionID to find the NAME of the corresponding DVO files in SkyTable.fits
3. Find measurements with IMAGE_ID and DET_ID == ippDetectID

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