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

== 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 == 


{{{
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
}}}