[lsst-dm-stack-users] How to calculate RMS, sigma-clipped RMS on DM Winter2013 Stripe82 forced photometry

[Michael Wood-Vasey]

On Jan 8, 2013, at 22:48 , Kian-Tat Lim <ktl at slac.stanford.edu> wrote:

> Michael,
> 
>> I'd like to calculate the RMS, the chisq, and a sigma-clipped RMS on
>> the DM Winter2013 Stripe 82 forced photometry
> 
> 	Do you mean across all the measurements for each object?  Or
> across the entire dataset?  In either case, a full table scan on
> DeepForcedSource will take some time, but it's not impossible.

Eventually across the entire dataset, but I'll now plan on working with Simon on that scale of thing.

I'll spend the immediate future choosing small subsections in RA, Dec to explore.

>> or should I talk to you about efficient ways to reprocess the raw
>> files that fed these tables?
> 
> 	I'd at least like to see that querying the database is too
> complex or too slow before going to the raw files (which still need to
> be joined).

The query below takes at least 15 minutes.  I had to move on to the next session at AAS (and thus suspend the connection) before it completed.

Perhaps I should manually construct my own table of a small RA, Dec region of DeepForcedSource to play around with for now.

 - Michael

SELECT afp.ra, afp.decl, afp.deepSourceID,
afp.q1Mag_g - afp.q3Mag_g as gdiff,
afp.q1Mag_r - afp.q3Mag_r as rdiff,
manualstd_r.fluxstd  as r_stdev,
ro.uMag, ro.gMag, ro.rMag, ro.iMag, ro.zMag,
ro.gMag-ro.rMag as g_r,
ro.rMag-ro.iMag as r_i
FROM 
(SELECT * FROM AvgForcedPhot
WHERE ra BETWEEN 0 AND 0.1 AND decl BETWEEN 0 AND 0.5) as afp
LEFT JOIN RefDeepSrcMatch
ON afp.deepSourceID=RefDeepSrcMatch.deepSourceID
LEFT JOIN RefObject AS ro
ON RefDeepSrcMatch.refObjectId=ro.refObjectId
LEFT JOIN
(SELECT STDDEV(psfFlux) as fluxstd FROM DeepForcedSource
WHERE ra BETWEEN 0 AND 0.1 AND decl BETWEEN 0 AND 0.5
AND filterId=3
GROUP BY deepForcedSourceID
) as manualstd_r
ON afp.refObjectId=manualstd_r.refObjectId