wiki:PSPSShared
PSPS Help Sections Software Components PSPS User Tools Project Wikis Related sites * -- restricted access

Shared User Experiences and Information Exchange

This space is provided for the PSPS beta testers to share their experiences with the system, especially in terms of common experiences and solutions they may develop for common queries. Please sign your name at the end of your entry and add a horizontal bar from the editing menu.



Jim Geach (Durham)

Notes - impressed with the user interface, especially the way it can lead you through the build-up of queries. This is good way to get a feel for content.

So, trying to extract some basic photometry for the MD04 field (COSMOS). I have a band-merged catalogue of photometry for the 1 sq deg COSMOS pointing for comparison, so this seemed like a good place to start. First attempt: grab (top) 10'000 with i-band 16<=i<=24. I want ra, dec, filterID, calibrated magnitude and error, and I also want the zeropoint. I'm grabbing this from the DetectionFull? table. Note that when I started the query build, I selected 'i' as the filter.

Query (from query builder):

SELECT TOP 10000 d.objID AS d_objID, d.ra AS d_ra, d.dec AS d_dec, d.filterID AS d_filterID, d.calMag AS d_calMag, d.calMagErr AS d_calMagErr, pc.zeropoint AS pc_zeropoint
 FROM Object AS o 
JOIN DetectionFull AS d ON d.objID = o.objID
JOIN PhotoCal AS pc ON pc.objID = o.objID
 WHERE d.calMag >= 16
 AND d.calMag <= 24
 AND pc.filterID IN (3)

Error (main line that looks to highlight the problem):

faultstring=System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Exception: Invalid column name 'objID'. 
---> System.Data.SqlClient.SqlException: Invalid column name 'objID'.

I suspect this is some issue with trying to do joining. To make it simpler, I ditch the zeropoint request:

SELECT TOP 10000 d.objID AS d_objID, d.ra AS d_ra, d.dec AS d_dec, d.filterID AS d_filterID, d.calMag AS d_calMag, d.calMagErr AS d_calMagErr
 FROM Object AS o 
JOIN DetectionFull AS d ON d.objID = o.objID
 WHERE d.calMag >= 16
 AND d.calMag <= 24

After some delay I get the message:

Error Fetching http headers 

Perhaps my request was too large? So, I reduce the number of objects in my request to 100, not 10'000 and manage to recover rows. I was expecting a single number in the d_filterID column (i=3?), but I actually see a mixture of '4' and '5' (bytes according to the units in the header)? Perhaps this filterID refers to the band that the object was detected in? Anyway, I download the results as FITS-basic successfully.

Right, now to refine the search (and to overlap better with my comparison data), I amend the query to include some spatial constraints:

SELECT TOP 100 d.objID AS d_objID, d.ra AS d_ra, d.dec AS d_dec, d.filterID AS d_filterID, d.calMag AS d_calMag, d.calMagErr AS d_calMagErr 
 FROM Object AS o 
JOIN DetectionFull AS d ON d.objID = o.objID
 WHERE d.ra >= 149.4
 AND d.ra <= 150.8
 AND d.dec >= 1.5
 AND d.dec <= 2.9
 AND d.calMag >= 16
 AND d.calMag <= 24

But again, I get

Error Fetching http headers 

I've submitted the same query to the slow queue, but the "Queued Jobs" page doesn't seem to be updating with "Elapsed" information. Actually I spoke too soon - an e-mail informed me that the query had finished, so I managed to grab that table (which contained rows).

Finally, I think the most important feature I would like is to have band-merged outputs, with separate columns for g, r, i, z, y for any of the measured flux measurements (aperture, models, etc.)... at the moment it looks like I have to do a few operations in the query to get the calMag from the relevant filter and then merge these into the final table. Is this right, or have I overlooked something? I guess what I'm basically asking is whether instead of a calMag ad associated filterID, we could have a (e.g.) calMag_g, calMag_r, etc.?

At the moment it looks to me that, if (say) I wanted i and z calibrated mags, then I do something like:

SELECT TOP 1000 d.objID AS d_objID, d.ra AS d_ra, d.dec AS d_dec, d.filterID AS d_filterID, d.calMag AS d_calMag, d.calMagErr AS d_calMagErr
 FROM DetectionFull AS d 
 WHERE d.filterID IN (3, 4) 

And then the table will have duplicate rows (but with different filterIDs) for the calMag corresponding to i and z. If I wanted to look at colours (or do a query based on a colour cut) it looks like the SQL could get unnecessarily complicated. Please let me know if I have misinterpreted this aspect of the tables... (interval) actually it looks like I did misinterpret - if I go to the Object table, then I can get gr, ri, etc. colours and also the MagBest? stack magnitudes for each band, and so this is a potential route. However, I think in the long run, I would want to be using info in StackApFlx?, with different apertures, Petrosian fluxes, etc. rather than the Best mags (presumably following a similar philosophy to SExtractor's MAG_BEST in which case the method the magnitude was derived can vary - it looks from the schema that the Best mag is one of the model magnitudes?)

If this is the case, it might be very helpful to have the option to choose exactly which photometry measure to use in this table (e.g. I might just want r=4 aperture fluxes) -- of course, I guess that just means some behind the scenes SQL legwork? Hope that makes sense!


Last modified 7 years ago Last modified on 06/23/10 13:09:52