Skip to end of metadata
Go to start of metadata

Queries on Output Catalogs

Preliminaries

Once images are processed with the Stack, analysis of the resulting catalog(s) is a logical next step. The following catalog query examples can be customized to your science needs. First, start MySQL on your DB server (we assume here it is the server on the LSST development cluster): 

mysql -h lsst10.ncsa.uiuc.edu -u <db_username> -p    # Enter you password at the prompt

Show the available databases in the system (if needed), select your database, and review the table names (since there are many): 

show databases like <userName>%;
use <your_db_name>;
show tables;

Queries like those in the next section can be performed, assuming the appropriate tables have been populated. 

Fetching Calibrated Magnitudes

Getting ten example calibrated magnitudes out of a database with a Source and Science_Ccd_Exposure table: 

SELECT scisql_dnToAbMag(s.psfFlux, sce.fluxMag0) as psfMag_r, 
       scisql_dnToAbMagSigma(s.psfFlux, s.psfFluxSigma, sce.fluxMag0, sce.fluxMag0Sigma) psfMagErr_r 
FROM Source s 
JOIN Science_Ccd_Exposure sce 
ON (s.scienceCcdExposureId = sce.scienceCcdExposureId) 
WHERE s.filterId = 2 
LIMIT 10;


If you are using a Co-Add database (GoodSeeing co-add in this case) the names of the tables and columns change, but the syntax is the same. 

SELECT scisql_dnToAbMag(s.psfFlux, sce.fluxMag0) AS psfMag_r, scisql_dnToAbMagSigma(s.psfFlux, s.psfFluxSigma, sce.fluxMag0, sce.fluxMag0Sigma) psfMagErr_r 
FROM GoodSeeingSource s 
JOIN GoodSeeingCoadd sce 
ON (s.goodSeeingCoaddId = sce.goodSeeingCoaddId) 
WHERE s.filterId = 2 LIMIT 10;

Here is the example query sent to the list. It is much the same as the one above but with an extra join to the reference catalog. Note there is not limit so will return all matches for the r-band: 

SELECT ref.rMag, scisql_dnToAbMag(s.psfFlux, sce.fluxMag0) psfMag, scisql_dnToAbMagSigma(s.psfFlux, s.psfFluxSigma, sce.fluxMag0, sce.fluxMag0Sigma) psfMagSigma 
FROM Source s 
JOIN RefSrcMatch rsm 
ON (r.sourceId = rsm.sourceId) 
JOIN RefObject ro 
ON (rsm.refObjectId = ro.refObjectId) 
JOIN Science_Ccd_Exposure sce 
ON (s.scienceCcdExposureId = sce.scienceCcdExposureId) 
WHERE s.filterId = 2 and rsm.refObjectId is not NULL;
  • No labels