= PSPS to IPP =
[wiki:ippToPspsSAS39BillsNotes 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.

ISSUE: How can we get at just the detections from 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 last week. 
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 
------------

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