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

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


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