In this document we provide a brief introduction to the use of the VISTA Science Archive (VSA) to do science in a step-by-step way with one particular survey, the VISTA Magellanic Cloud Survey. More general information about the VSA, SQL and other surveys is given in other pages on this site. The queries were developed for VMCDR7 and most will work on all releases.

2. Data Structure

2.1. Images and basic catalogues

Images are stored in the VSA as flat FITS files, with only the meta-data going into the SQL database. The VSA contains all the CASU generated images: reduced pawprint images (frameType='normal'), confidence images (frameType='conf'), stacked images made from a jitter combination of 'normal' frames (frameType='stack'), tiles made from six offset stacked pawprints (frameType='tilestack'), and additional deep images created by WFAU in Edinburgh, using the casutools software: (frameType='deepstack', 'deepstackconf', 'tiledeepstack' and 'tiledeepstackconf'). The frameType attribute can be found in the Multiframe table.

The images can be accessed through the Archive Listing, and cutouts can be created using Get Image and Multi-Get Image. The sky-coverage of the VMC and other surveys, including hosted surveys such as Glimpse, XMM3, WISE can be shown using in the Coverage Maps. It is necessary to download Aladin to view these maps, which use the Multi-Order Coverage (MOC) format.

Images and catalogues from CASU (and indeed WFAU generated deep images and catalogues) are created and stored as multi-extension FITS files, with each extension relating to a different VISTA detector in the case of pawprints, or with a single extension relating to the mosaiced image in the case of tiles. The image metadata and both the metadata and the table data from the catalogues are then ingested into the SQL data structure. The meta-data for each image is divided up amongst a set of tables for ease of use. Data from the primary header of each image FITS file, or parameters related to the whole multi-extension frame are stored in the table Multiframe and those relating to each extension are stored in MultiframeDetector except world-coordinate information and derived terms, which are in CurrentAstrometry. Each image is given a unique integer identifier (multiframeID), which can be used to track the frame through all the tables. The detector extensions are numbered using the extNum (values from 2 to 17).

Queries can be done on the these meta-data tables to find particular frames, e.g. Ks-band OB tile frames within 1 degree of the Galactic Centre observed between May 16th 2012 and June 20th 2012, with an airmass <1.2 and seeing <0.9".

Example 1

SELECT m.multiframeID,fileName,utDate,mjdObs
FROM Multiframe AS m,MultiframeDetector AS d, CurrentAstrometry AS c,ProgrammeFrame as p
WHERE p.programmeID=130 AND p.multiframeID=m.multiframeID AND m.multiframeID=d.multiframeID AND d.multiframeID=c.multiframeID AND d.extNum=c.extNum AND m.frameType='tilestack' AND dbo.fGreatCircleDist(c.centralRa,c.centralDec,80,-70.)<60. AND utDate BETWEEN '20130201' AND '20130401' AND m.filterID=5 AND (m.amStart+m.amEnd)/2.<1.5 AND d.seeing*c.xPixSize<0.9

The function fGreatCircleDist(ra1,dec1,ra2,dec2) returns an angular separation between two positions in arcminutes. This function can be used in many situations. Functions are given in the SchemaBrowser, but can be slow, so are best used on smaller tables, e.g. checking the separation between images rather than catalogues.
Another important meta-data table in Provenance, which links images to their components, e.g. a deep tile to the deep pawprint stacks, or a deep pawprint stack to observing block level (OB) pawprint stacks or an OB pawprint stack to the reduced images. Example 2 gets all of the OB pawprint component image names for a particular OB tile, and the third example gives the mean and stdev of the seeing for OB pawprint components of a deep tile and a comparison to the tile seeing.
Example 2

SELECT m.multiframeID,fileName,utDate,offSetID,mjdObs,mjdEnd
FROM Multiframe AS m,Provenance AS v
WHERE v.combiframeID=5732492 AND m.multiframeID=v.multiframeID

Example 3

SELECT v.combiframeID,AVG(do.seeing*co.xPixSize) as meanSeeing, STDEV(do.seeing*co.xPixSize) as sdSeeing, (d.seeing*c.xPixSize) as tileSeeing, COUNT(v1.multiframeID) as N_OBpawprints
FROM MultiframeDetector AS d,Provenance AS v,Provenance as v1, CurrentAstrometry as c, MultiframeDetector as do,CurrentAstrometry as co
WHERE v.combiframeID=1000000056154 AND v.multiframeID=v1.combiframeID AND do.multiframeID=v1.multiframeID AND co.multiframeID=do.multiframeID AND co.extNum=do.extNum AND d.multiframeID=v.combiframeID AND d.multiframeID=c.multiframeID AND d.extNum=c.extNum
GROUP BY v.combiframeID,(d.seeing*c.xPixSize)

In this case we go down two layers of provenance. The first (v) links deep tiles to the deep pawprint stacks that are produced to create the tiles and then the second (v1) links the deep pawprint stacks to OB pawprint stacks. The combiframeIDs do change from release to release and the above example was from VMCDR7.
Each science frame, (frameType='stack','tilestack','deepstack','tiledeepstack') has an extracted catalogue, stored in vmcDetection, and each measurement can be identified by a combination of multiframeID, extNum and seqNum (the sequential number of the extraction of the particular detector). vmcDetection contains ~2.7 billion rows in VMCDR7, from 24555 frames. Each row in vmcDetection includes measured outputs from the CASU imcore extractor, including positions, 13 aperture fluxes, star-galaxy separation parameters, calibrated astrometry and calibrated photometry. Much of the data in tables such as vmcSource and vmcSynopticSource originates in vmcDetection. The light-curves are created from vmcSynopticSource, using links stored in vmcSourceXSynopticSourceBestMatch.
Example 4 shows the connections between vmcSource and vmcDetection.

SELECT TOP 30 s.sourceID,s.frameSetID,l.ymfID,l.yeNum,s.yseqNum, s.yAperMag3,s.yAperMag3Err,d.filterID,d.aperMag3,d.aperMag3Err, s.ra AS meanRa,s.dec AS meanDec,d.ra AS yRA,d.dec as yDec
FROM vmcMergeLog as l,vmcSource as s,vmcDetection as d
WHERE l.frameSetID IN (SELECT MIN(frameSetID) FROM vmcMergeLog WHERE ymfID>0) AND l.frameSetID=s.frameSetID AND l.ymfID=d.multiframeID AND l.yeNum=d.extNum AND s.yseqNum=d.seqNum AND s.yAperMag3>13. AND s.yAperMag3<16.

The vmcMergeLog links up the deepest frames in each band, which go into a single pointing and vmcSource contains the catalogue for each frameSet. This contains measurements such as yAperMag3 which is a direct copy of aperMag3 in vmcDetection for ymfID,yeNum,yseqNum, and derived quantities, such as the mean position, and colours.
The tile and pawprint detections are also linked in the VSA. vmcTilePawprints matches up the tile detections and pawprint detections from the 6 offsets. Each tile-pawprint set has its own tileSetID, and the input frames are described in vmcTileSet. The layout of vmcTilePawprints is similar to vmcSource, but no magnitudes or positions are given, simply links to match up the tables, so it is necessary to do joins with vmcDetection. Below are some examples of comparing the tile and pawprint attributes in a tile. The first example (Example 5) gives comparison between the tile aperMag3 and the average pawprint aperMag3 for regions where there are overlaps between offset1 and offset2 only and the tile multiframeID. Example 6 shows the comparison for all regions where there are 6 overlaps for the same multiframeID. Later on, I will give examples of light curves using pawprint photometry.
Example 5.

SELECT o1ExtNum,o2ExtNum,o1SeqNum, o2SeqNum, dt.aperMag3 as tlAperMag3, dt.aperMag3Err as tlApermag4Err, do1.aperMag3 AS o1AperMag3, do1.aperMag3Err AS o1AperMag3Err, do2.aperMag3 AS o2AperMag3, do2.aperMag3Err AS o2AperMag3Err
FROM vmcTileSet AS t,vmcTilePawPrints AS p,vmcDetection AS do1, vmcDetection AS do2, vmcDetection AS dt
WHERE t.tlmfID=4300886 AND t.tileSetID=p.tileSetID AND (p.tlSeqNum>0 OR p.tileSetSeqNum<0) AND do1.multiframeID=t.o1mfID AND do1.extNum=p.o1ExtNum AND do1.seqNum=p.o1SeqNum AND do2.multiframeID=t.o2mfID AND do2.extNum=p.o2ExtNum AND do2.seqNum=p.o2SeqNum AND dt.multiframeID=t.tlmfID AND dt.extNum=p.tlExtNum AND dt.seqNum=p.tlSeqNum AND p.o1seqNum>0 AND p.o2seqNum>0

(p.tlSeqNum>0 OR p.tileSetSeqNum<0) returns a row for every tile detection, whether there are pawprint detections or not, and is very important if linking to tile based light-curves or similar situations. If you want to select only objects that are in pawprints offsetID=1 and offsetID=2, then add the additional constraint AND p.o3seqNum<0 AND p.o4seqNum<0 AND p.o5seqNum<0 AND p.o6seqNum<0
Example 6.

SELECT o1ExtNum,o2ExtNum,o3ExtNum, o4ExtNum, o5ExtNum, o6ExtNum, dt.aperMag3 as tlAperMag3,dt.aperMag3Err as tlApermag4Err, do1.aperMag3 AS o1AperMag3, do1.aperMag3Err AS o1AperMag3Err, do2.aperMag3 AS o2AperMag3, do2.aperMag3Err AS o2AperMag3Err, do3.aperMag3 AS o3AperMag3, do3.aperMag3Err AS o3AperMag3Err, do4.aperMag3 AS o4AperMag3, do4.aperMag3Err AS o4AperMag3Err, do5.aperMag3 AS o5AperMag3, do5.aperMag3Err AS o5AperMag3Err, do6.aperMag3 AS o6AperMag3, do6.aperMag3Err AS o6AperMag3Err
FROM vmcTileSet AS t,vmcTilePawPrints AS p, vmcDetection AS dt, vmcDetection AS do1, vmcDetection AS do2, vmcDetection AS do3, vmcDetection AS do4, vmcDetection AS do5, vmcDetection AS do6
WHERE t.tlmfID=4300886 AND t.tileSetID=p.tileSetID AND dt.multiframeID=t.tlmfID AND dt.extNum=p.tlExtNum AND dt.seqNum=p.tlSeqNum AND do1.multiframeID=t.o1mfID AND do1.extNum=p.o1ExtNum AND do1.seqNum=p.o1SeqNum AND do2.multiframeID=t.o2mfID AND do2.extNum=p.o2ExtNum AND do2.seqNum=p.o2SeqNum AND do3.multiframeID=t.o3mfID AND do3.extNum=p.o3ExtNum AND do3.seqNum=p.o3SeqNum AND do4.multiframeID=t.o4mfID AND do4.extNum=p.o4ExtNum AND do4.seqNum=p.o4SeqNum AND do5.multiframeID=t.o5mfID AND do5.extNum=p.o5ExtNum AND do5.seqNum=p.o5SeqNum AND do6.multiframeID=t.o6mfID AND do6.extNum=p.o6ExtNum AND do6.seqNum=p.o6SeqNum AND p.o1seqNum>0 AND p.o2seqNum>0 AND p.o3seqNum>0 AND p.o4seqNum>0 AND p.o5seqNum>0 AND p.o6seqNum>0

2.2. Stars

The vmcSource table is the central table in the WFAU data model, containing the master source list used for comparing to external surveys and for multi-epoch data. Thus it provides the best starting point to match the data to astrophysical sources. It provides a unique object list when the priOrSec flag is used to select primary sources. vmcSource are a set of band-merged catalogues from each pointing (frameSet) extracted from the deepest frames in each band. Where there is overlap, between sets of frames, the more complete source in terms of bands measured and the one with fewest qualit bit flags set will be selected as primary, and all the others as secondary. Selecting primary sources only gives us a master catalogue. vmcSource<\i> is used as the starting point for multi-epoch merging and for matching to external surveys. Below are some examples using vmcSource<\i> for selecting stars, matching to external catalogues. We will demonstrate light-curve selection and use of extinction maps in a later section.

Example 7, shows a selection on the vmcSource table, selecting positional, colour and magnitude data for a seamless sample selected in a Galactic longitude and latitude range, having detections in the Y-band and Ks-band and a Ks-band brightness <18 mag.

SELECT l, b, ra, dec, ymjPnt, ymjPntErr, jmksPnt, jmksPntErr, ksAperMag3, ksAperMag3Err, mergedClass
FROM vmcSource
WHERE ksAperMag3>0. AND ksAperMag3<18. AND yAperMag3>0. AND (priOrSec=0 OR priOrSec=frameSetID) AND l BETWEEN 275. AND 855. AND b BETWEEN -33. AND -30.

Example 8 Match between VMC and Spitzer SMC.

SELECT s.l, s.b, s.ra,s.dec, ymjPnt, ymjPntErr, ksAperMag3, ksAperMag3Err,ssm.E3_6umMag,ssm.E4_5umMag,mergedClass
FROM vmcSource AS s,SPITZER..spitzer_smcSource AS ssm,vmcSourceXspitzer_smcSource AS x
WHERE ksAperMag1>0. AND ksAperMag3<20. AND yAperMag3>0. AND (priOrSec=0 OR priOrSec=frameSetID) AND s.sourceID=x.masterObjID AND x.slaveObjID=ssm.seqNum AND distanceMins<0.08 AND distanceMins IN (select MIN(distanceMins) from vmcSourceXspitzer_smcSource where masterObjID=x.masterObjID) AND ssm.E3_6umMag<20. AND ssm.E4_5umMag<20.

The distanceMins<0.08 AND distanceMins IN (select MIN(distanceMins) from vmcSourceXspitzer_smcSource where masterObjID=x.masterObjID) constraint selects the nearest Spitzer match, as long as it is within 0.08 arcmin (~5 arcsec). However, only VMC sources which have a Spitzer match that match all the constraints will return rows. If you want all the VMC sources that match the VMC constraints, with Spitzer photometry for those with Spitzer detections above, and default values for those without Spitzer detections, then you can use the UNION command to join queries, see Example 9.
Example 9.

SELECT s.l, s.b, s.ra,s.dec, ymjPnt, ymjPntErr, ksAperMag3, ksAperMag3Err,
ssm.E3_6umMag,ssm.E4_5umMag,mergedClass, distanceMins
FROM vmcSource AS s,SPITZER..spitzer_smcSource AS ssm,vmcSourceXspitzer_smcSource AS x
WHERE ksAperMag3>0. AND ksAperMag3<20. AND yAperMag3>0. AND (priOrSec=0 OR priOrSec=frameSetID) AND s.l BETWEEN 297. AND 302. AND s.b BETWEEN -46. AND -44.AND s.sourceID=x.masterObjID AND x.slaveObjID=ssm.seqNum AND distanceMins<0.08 AND distanceMins IN (select MIN(distanceMins) from vmcSourceXspitzer_smcSource where masterObjID=x.masterObjID) AND ssm.E3_6umMag<20. AND ssm.E4_5umMag<20.
UNION
SELECT s.l, s.b, s.ra, s.dec, s.ymjPnt, s.ymjPntErr, s.ksAperMag3, s.ksAperMag3Err,
-9.999995e8,-9.999995e8, s.mergedClass,distanceMins
FROM vmcSource AS s,SPITZER..spitzer_smcSource AS ssm,vmcSourceXspitzer_smcSource AS x
WHERE s.ksAperMag3>0. AND s.ksAperMag3<18. AND s.yAperMag3>0. AND (s.priOrSec=0 OR s.priOrSec=frameSetID) AND s.l BETWEEN 297. AND 302. AND s.b BETWEEN -46. AND -44. AND s.sourceID=x.masterObjID AND x.slaveObjID=ssm.seqNum AND distanceMins<0.08 AND distanceMins IN (select MIN(distanceMins) from vmcSourceXspitzer_smcSource where masterObjID=x.masterObjID) AND (ssm.E3_6umMag>=20. OR ssm.E4_5umMag>=20.)
UNION
SELECT s.l, s.b, s.ra, s.dec, s.ymjPnt, s.ymjPntErr, s.ksAperMag3, s.ksAperMag3Err,
-9.999995e8,-9.999995e8, s.mergedClass,distanceMins
FROM vmcSource AS s, vmcSourceXspitzer_smcSource AS x
WHERE s.ksAperMag3>0. AND s.ksAperMag3<18. AND s.yAperMag3>0. AND (s.priOrSec=0 OR s.priOrSec=frameSetID) AND s.l BETWEEN 297. AND 302. AND s.b BETWEEN -46. AND -44. AND s.sourceID=x.masterObjID AND distanceMins>=0.08 AND distanceMins IN (select MIN(distanceMins) from vmcSourceXspitzer_smcSource where masterObjID=x.masterObjID)
UNION
SELECT s.l, s.b, s.ra, s.dec, s.ymjPnt, s.ymjPntErr, s.ksAperMag3, s.ksAperMag3Err,
-9.999995e8,-9.999995e8, s.mergedClass,-9.999995e8
FROM vmcSource AS s
WHERE s.ksAperMag3>0. AND s.ksAperMag3<18. AND s.yAperMag3>0. AND (s.priOrSec=0 OR s.priOrSec=frameSetID) AND s.l BETWEEN 297. AND 302. AND s.b BETWEEN -46. AND -44. AND s.sourceID NOT IN (select masterObjID from vmcSourceXspitzer_smcSource)

In this example there are 4 separate queries, which each return the same parameters, joined via a UNION. The first query is identical to example 8, except with the additional parameter distanceMins in the select string and a selection in Galactic coordinates. This returns 182207 rows when run on VMCv20250130. The second query returns VMC sources where there are VMC-Spitzer matches, but where one or both of the Spitzer 3.6/4.5 micron fluxes are greater than 20 mag. This returns 1776 rows when run on VMCv20250130. The 3rd query returns VMC photometry for VMC sources where there is a Spitzer source in the neighbour table, but the nearest one is further than 0.08 arcmin (returning 8905 rows in VMCv20250130), and the 4th query returns VMC photometry for VMC sources which meet the VMC criteria, and have no matches to any Spitzer sources (returning 164006 rows). Thus the whole query returns 356889 rows in VMCv20250130.
The vmcSource table is also the usual table for matching to your own catalogues through CrossID or doing cone searches using Region.
The CrossID service can be used to select the nearest object within a particular radius or all matches within that radius, much like the neighbour tables, but more flexible, although the queries will take longer and there is an upper limit of 50000 objects. Example 10 shows a crossID query using a simple input catalogue, which is a 3x6 grid around the Galactic Centre, see Galactic Centre Grid coordinates.
Example 10:

Set the table to 'source table (merged catalogue)'
Leave the constraints as '(priOrSec<=0 OR priOrSec=frameSetID)'. This selects primary sources only.
Leave the attributes to select as 'default'. As long as sourceID,ra,dec are in the attributes, it is useful for for future queries.
Set the pairing radius to '2.0' arcsec. For the VMC, keep this small.
Set the pairing option to 'Nearest object only'.
Browse for the file of coordinates e.g. 'vmcCrossIDCoords.txt'.
Set data format to preferred type. FITS or VoTable have the advantage of preserving the attribute names, which makes further queries more straightforward.

The input file in this case has 18 rows, and therefore the crossID match returns 18 rows, but only XX are matched to vmcSources.
2.3. Colours

In the VMC we have two tables of band-merged aperture photometry data: vmcSource and vmcSynopticSource. vmcSource contains colours from the deepest images in each pointing and vmcSynopticSource from contemporary colours. For non-variable sources, the colours from the deepest images will be best because of higher signal-to-noise, but for variables, the contemporary colours give a better indication. In each pointing there is typically one YJKs epoch, and one YJ and one JKs epoch for contemporary colours with bands taken in consecutive OBs and the other Y,J and Ks frames are taken in independent OBs.

One issue to be aware of is that to use the contemporary colours with other data, such as variability data or external data, it is necessary to join to the vmcSource table, since all the links in the data model are through this table. Some examples are shown below, which will demonstrate important aspects of the joins and selecting for different requirements.
The maximum time between band observations is given by the "bandMergingCriterion" in the Programme table, which is 240m (4 hours) for the VMC. This time is required because each OB for the VMC is quite long (around 1 hour) to get the required depth, and 3 have to be observed for contemporary colours to be observed.
To select data with contempory colours in the VMC, with an ID from the main vmcSource table (for joining to other tables (e.g. vmcVariability, or external surveys), we can use the vmcSourceXSynopticSource which gives all matches out to 10" and can be tailored for various purposes or the vmcSourceXSynopticSourceBestMatch which has the nearest match at each epoch and a default if there is an epoch without a detection. These options are shown in the next few examples:
Example 11.

SELECT s.sourceID,ss.synopticID,ss.ymjPnt, ss.ymjPntErr,ss.jmksPnt,ss.jmksPntErr
FROM vmcSource AS s,vmcSourceXSynopticSource AS x, vmcSynopticSource AS ss, (SELECT l.frameSetID,sl.synFrameSetID, sl.meanMjdObs FROM vmcSynopticMergeLog AS sl, vmcMergeLog AS l WHERE dbo.fGreatCircleDist(l.ra,l.dec,sl.ra,sl.dec)<1) AS ML
WHERE (s.priOrSec=0 OR s.priOrSec=s.frameSetID) AND x.masterObjID=s.sourceID AND x.distanceMins<0.033 AND ss.synopticID=x.slaveObjID AND ML.frameSetID=s.frameSetID AND ML.synFrameSetID=ss.synFrameSetID

The query within the FROM string creates a temporary match between the vmcMergeLog (list of frame sets used to create vmcSource) and vmcSynopticMergeLog (list of frame sets used to create vmcSynopticSource), within 1 arcminute, so that repeated matches in overlap regions aren't returned. If you prefer all the overlapping contemporary colours, exclude this and all parts of the query with the ML alias. This will return matches to all epochs within ~2" (0.033') of each source. The user can put additional constraints on the input sources: e.g. a single specified source, ones that match certain constraints, or a table from a CrossID (see Example 10). The user can put additional constraints on the epoch colour selection, e.g. that there is a Ks detection (ss.ksSeqNum>0), that there are at least 2 bands ((ss.yseqNum>0 and ss.jseqNum>0) or (ss.jseqNum>0 and ss.ksSeqNum>0) or (ss.yseqNum>0 and ss.ksSeqNum>0)) or that the colours are observed within a certain time (e.g. ML.meanMjdObs BETWEEN x AND y).

This next example uses the vmcSourceXSynopticSourceBestMatch
Example 12.

SELECT s.sourceID,ss.synopticID,sl.meanMjdObs,ss.ymjPnt,ss.ymjPntErr,ss.jmksPnt,ss.jmksPntErr
FROM vmcSource AS s,vmcSourceXSynopticSourceBestMatch AS b, vmcSynopticSource AS ss, vmcSynopticMergeLog AS sl
WHERE (s.priOrSec=0 OR s.priOrSec=s.frameSetID) AND b.sourceID=s.sourceID AND ss.synFramseSetID=b.synFrameSetID AND ss.synSeqNum=b.synSeqNum AND sl.synFrameSetID=ss.synFrameSetID

This gives the nearest match for each epoch or a default if there are no detections at a particular epoch. This usually a better choice for light-curves and colours over time.
2.4. Multi-epoch data
The individual OB measurements for each object are stored in vmcDetection and vmcSynopticSource. These vmcSourceXSynopticSourceBestMatch table links up OB tile detections for the same source, and statistics calculated from the light-curve of these tile detections for each source are stored in vmcVariability. Use of these tables allows you to select variables, and get the light-curve data. To select very red transient objects, for instance, combine a colour selection with a selection from vmcVariability which gives statistics on the tile light-curves. Example 13, selects sources that have a Ks band RMS > 2 magnitudes, at least 4 good observations and have contempory (J-Ks)>3, at at least one epoch.
Example 13

SELECT v.sourceID, v.ksnGoodObs, v.ksMeanMag, v.ksMagRms, sl.meanMjdObs, ss.jmksPnt
FROM vmcSynopticSource as ss, vmcSynopticMergeLog as sl, vmcSourceXSynopticSourceBestMatch as b, vmcVariability as v
WHERE b.sourceID=v.sourceID and b.synFrameSetID=ss.synFrameSetID and b.synSeqNum=ss.synSeqNum and sl.synFrameSetID=ss.synFrameSetID and sl.jmfID>0 and sl.ksmfID>0 and ss.jmksPnt>3. and v.ksMagRms>2. and v.ksnGoodObs>4

In the VMCv20250130 release, this returns just a single object, sourceID=558384922189, with 6 good observations, a mean Ks=18.56 mag, a Ks rms = 2.11 mag and (J-Ks)=5.43 mag.
We can take this object selection and get a light-curve.

SELECT objSel.sourceID,smlc.meanMjdObs,sslc.jmjd,sslc.jAperMag3,sslc.jAperMag3Err,sslc.jppErrBits,
sslc.ksmjd,sslc.ksAperMag3,sslc.ksAperMag3Err,sslc.ksppErrBits, smlc.jmfID,smlc.ksmfID,sslc.jmksPnt, blc.flag
FROM (select distinct v.sourceID
from vmcSynopticSource as ss, vmcSynopticMergeLog as sl, vmcSourceXSynopticSourceBestMatch as b,
vmcVariability as v where b.sourceID=v.sourceID and b.synFrameSetID=ss.synFrameSetID and
b.synSeqNum=ss.synSeqNum and sl.synFrameSetID=ss.synFrameSetID and sl.jmfID>0 and sl.ksmfID>0 and
ss.jmksPnt>3. and v.ksMagRms>2. and v.ksnGoodObs>4) as objSel,
vmcSourceXSynopticSourceBestMatch as blc, vmcSynopticSource as sslc, vmcSynopticMergeLog as smlc
WHERE objSel.sourceID=blc.sourceID and blc.synFrameSetID=sslc.synFrameSetID and blc.synSeqNum=sslc.synSeqNum
and smlc.synFrameSetID=sslc.synFrameSetID

The original selection is shown in red, except that we only select distinct sourceIDs. If there had been multiple epochs where the colour was >3, then there might have been multiple rows with the same sourceID in the original selection, and therefore duplicates of the light-curve in this selection. A second instance of vmcSourceXSynopticSourceBestMatch, vmcSynopticSource and vmcSynopticMergeLog are required to get the light-curves. We have given the second instances different aliases (blc rather than b, sslc rather than ss and smlc rather than sl) for clarification, although this isn't strictly necessary since one set of instances are in a subquery. The original selection would only give rows where there are both J and Ks measurements and J-Ks>3, rather than all the other epochs. This source turns out to have both J and Ks measurements at ~19 mag or default (too faint?), except one Ks=14.4, which is deblended. It seems likely that this is affected by a nearby source rather than being a truly interesting variable, but more investigation is necessary.
In this query, we have included several time measurements. We have included sslc.jmjd and sslc.ksmjd, the time in modified julian date for the j and ks measurements repsectively. These are the most accurate times for tile measurements, especially in the VMC, where the observations that go into the tile are taken over the course of over an hour, and depending on the sequence of dithers and pawprints, a measurement on a different part of the tile can have quite a different average time. However, if there isn't a measurement, e.g. if no observation took place at that epoch in that filter, or the object wasn't detected on the observation, then these values would be default. The meanMjdObs for the epoch frameset will always have a value, and can be used to identify when an object wasn't detected. Using the smlc.jmfID and smlc.ksmfID columns tell you if there is an observation at that epoch in the J and Ks bands respectively. If there is no observation, the value is default, -99999999. The sslc.jppErrBits and sslc.ksppErrBits columns give the bitwise post-processing error bit flag for the J and Ks epoch measurements respectively. See the page on ppErrBits for more details. The blc.flag will typically be 0 (and in this selection is always 0). However it can be 1, if the same epoch measurement is allocated to 2 or more sources. This can happen if there are two sources close to each other in the deep images, but on the epoch image there is only one measurement, so care may have to be taken as to which it is associated with. This could happen if one is too faint to be detected in epoch measurements, or if the seeing is worse and the objects become blended. The flag could also be 2 or greater if a source isn't detected, but is in the dither region close to the edge of the detector where the exposure time is a fraction of the full exposure. In the vmcSourceXSynopticSourceBestMatch where each row links to a multi-band frameset, the flag is 2**filterID. E.g. if there are non-detections in Y and Ks and both objects are expected to be in this dither region, then the flag would be 2**2+2**5 = 36. If only the Y was undetected, then the flag would be 4 (but the ksppErrBits would have a bit set). If on top of this the epoch measurements were linked to two sources, then the flag would have 1 added, so would be 5 or 37.

2.6 Non-pipeline team (Value Added Product, VAP) catalogues
We have imported catalogues generated by the VMC team, such as PSF catalogues. These are linked to other data via the neighbour tables mostly.
The list of VMC team catalogues are: vmcCepheidVariables, vmcRRLyraeVariables, vmcLmc75Variables, vmcAGBVariables, vmcLPVCandidates, vmcPsfSource, vmcProperMotionCatalogue, vmcMLClassificationCatalogue, vmcBackgroundSources, vmcQsos, vmcExtinction each containing data produced by groups within the VMC team. Some details about production and references can be found in the SchemaBrowser, in the top level information about each of these tables. Additionally each of these has an entry in ExternalProduct, and searching the reference, creator and institution gives useful information on the provenance. ProgrammeID=130 is the VMC.
SELECT productID,productType,tableName,reference,creator,institution
FROM ExternalProduct
WHERE programmeID=130

The tables ending in Variables are also combined into vmcVariablesType which gives contains uniqueID,ra,dec and variableType. The variableType in each case is just the productType from ExternalProduct, except in the case of vmcLmc75Variables, which contains a list of variables in one tile, in which case variableType points to varType. Each table of team generated data has a column uniqueID, a running number, to serve as an identifer. These can only be used for that particular table, and are not equivalent to uniqueIDs in any other team generated tables or any other VMC tables, such as vmcSource, except for the Variables tables, where uniqueID for a star in vmcCepheidVariables points to the same star in vmcVariablesType. As each variable table is ingested, the running number starts from the last entry in vmcVariablesType. Many of the tables, such as vmcVariablesType are generated from previous objects selected from vmcSource and other such tables, and are expected to have a one-to-one match with the current vmcSource. Others such as vmcPsfSource are generated by reprocessing the image data to find new sources. The tables with a one-to-one match can have a neighbour table to vmcSource which can be used to find the matching sourceID and then can use vmcSource neighbour tables to work with external surveys such as Gaia and WISE. The ones generated from reprocessing image data will need their own neighbour tables to external data. The VMC team members have generated the following data products that are available in the VMCDR7 release:

vmcCepheidVariables

vmcLPVCandidates

vmcAGBVariables

vmcLmc75Variables

vmcRRLyraeVariables

vmcEclipsingBinaryVariables

vmcQsos

vmcPsfSource

vmcProperMotionCatalogue

vmcMLClassificationCatalogue

vmcBackgroundSources

vmcExtinction

vmc30DorExtinction

PSF photometry.
The PSF photometry using DAOPHOT is stored in vmcPsfSource. The details of the processing can be found in Rubele et al. 2015, MNRAS, 449, 639. We have ingested data for each tile pointing (given by fieldName and frameSetID). In the overlap regions we assign a primary/secondary flag (priOrSec), in the same way as in vmcSource, so a set of unique sources can be selected if required. The catalogue includes separate Y, J, Ks positions for each source and an average position (ra, dec). In the archive we have also generated Galactic (l,b) and Cartesian coordinates (cx, cy, cz). The total brightness of each source in each band is given by yPsfMag, jPsfMag and ksPsfMag respectively, along with the associated uncertainty (yPsfMagErr etc). We have also added colour terms (ymjPsf, jmksPsf, ymksPsf), to aid users. In each band there is also a sharpness measurement (e.g. ySharp) that is useful in separating point and extended sources. Using all bands together, there is a starProb column that gives a probability that a the source is a star. The VMC team have used the PSF catalogues to generate local completeness (lCompY etc) and systematic errors (sysErrY etc) in each band and the number of local stars used to calculate the completeness (nY, etc). These columns are not calculated in the SMC gap field (fieldName 'SMC_1_1'). The following query might be used to generate a colour magnitude diagram (CMD) of good stars, selecting unique stars (priOrSec=0 or priOrSec=frameSetID) and starProb>0.9, stars with non-default magnitudes in each band, and ones where the local completeness is high (lCompY>0.95) and there are decent number of stars in each band to measured the completeness (nY>100). The useful columns for the colour (Y-J) magnitude (Ks) diagram are (ymjPsf and ksPsfMag), but other columns are included such as positions (ra, dec) and fieldName for splitting the data for additional tests.
SELECT fieldName,ra,dec,ymjPsf, ymjPsfErr, ksPsfMag,ksPsfMagErr,nY,nJ,nKs,lCompY,lCompJ,lCompKs
FROM vmcPsfSource
WHERE (priOrSec=0 OR priOrSec=frameSetID) AND ksPsfMag>0. AND jPsfMag>0. AND yPsfMag>0. AND starProb>0.9 AND lCompY>0.95 AND lCompJ>0.95 AND lCompKs>0.95 AND nY>100 and nJ>100 AND nKs>100

To select all of the data in a specific field.
SELECT ra,dec,ymjPsf, ymjPsfErr, ksPsfMag,ksPsfMagErr,nY,nJ,nKs,lCompY,lCompJ,lCompKs
FROM vmcPsfSource
WHERE fieldName='LMC_6_2'

Proper Motions.
The proper motions for sources detected by PSF fitting in each epoch, as detailed in Niederhofer et al. 2025, submitted. Like the vmcPsfSource catalogue, there are pointing columns including fieldName and frameSetID and positional columns, ra, dec and Galactic coordinates and Cartesian coordinates. The proper motion columns that are included are pmXi and pmEta and rmsX and rmsY for each source and the epoch for each field. For instance finding higher proper motion objects, in this case ones where the proper motion is >50mas/year. The pmXi>-10000 is to avoid default values.
SELECT ra, dec, pmXi, pmEta, rmsX, rmsY
FROM vmcProperMotionCatalogue
WHERE SQRT(pmXi*pmXi+pmEta*pmEta)>50. and pmXi>-10000 and pmEta>-10000.

While the proper motions were calculated from PSF photometric fitting, and will find objects that are not in the aperture catalogues, the proper motion tables will not be exactly the same set of sources that are in the PSF fitting catalogues from the deep fields but there should be a strong correlation. We set up a neighbour table between the two catalogues, vmcPsfSourceXProperMotionCatalogue. In the example below, we join the two tables on the nearest match with in 1 arcsec, and with 165mas and pmEta<-20mas (avoiding defaults).
SELECT psf.*, pm.pmXi,pm.pmEta,pm.rmsX,pm.rmsY
FROM vmcProperMotionCatalogue as pm, vmcPsfSource as psf, vmcPsfSourceXProperMotionCatalogue as x
WHERE psf.uniqueID=x.masterObjID and pm.uniqueID=x.slaveObjID and x.distanceMins<0.016 and
x.distanceMins in (
select min(distanceMins) from vmcPsfSourceXProperMotionCatalogue as xx
where xx.masterObjID=x.masterObjID)
AND psf.ksPsfMag>16. AND psf.ksPsfMag<20. AND pm.pmXi>5 AND pm.pmEta<-20. AND pm.pmEta>-10000.

In the following example, we take the PSF/PM join from above, but remove the proper motion contraints, and instead add constraints on the fieldName, selecting VMC Bridge (like 'BRI%') fields. We then join to WISE allwise_sc. In this catalogue, the primary key is cntr, which is used for the neighbour tables. In this case we match within 4 arcses, (0.064 arcmin), since the WISE seeing is much worse than VMC and haven't put a nearest neighbour constraint, so all WISE sources within 4" will be returned. Additional constraints on W1 or W2 mag could be added, but we haven't in this example.
SELECT psf.*, pm.pmXi,pm.pmEta,wise.w1mpro,wise.w2mpro
FROM vmcProperMotionCatalogue as pm, vmcPsfSource as psf, vmcPsfSourceXProperMotionCatalogue as x,
vmcPsfSourceXallwise_sc as x_wise, WISE..allwise_sc as wise
WHERE psf.uniqueID=x.masterObjID and pm.uniqueID=x.slaveObjID and x.distanceMins<0.02 and x.distanceMins in (
select min(distanceMins) from vmcPsfSourceXProperMotionCatalogue as xx where xx.masterObjID=x.masterObjID)
and psf.ksPsfMag>16. and psf.ksPsfMag<20. and psf.fieldName like 'BRI%' and x_wise.masterObjID=psf.uniqueID
and x_wise.slaveObjID=wise.cntr and x_wise.distanceMins<0.064

Variable stars
The vmcVariablesType records all the variable stars selected by different groups in the VMC team and has a variableType column. The variable stars in this table, all come from the following tables: vmcCepheidVariables, vmcEclipsingBinaryVariables, vmcAGBVariables, vmcLmc75Variables and vmcRRLyraeVariables. If the variable star comes from vmcCepheidVariables or vmcRRLyraeVariables this will be set to ceph or rrLyr, but in the case of vmcLmc75Variables, a table of multiple types, it is a copy of the varType column. Since the variables in LMC_7_5 have been found independently there may be overlaps and possibly disagreements with the work from other groups. One way to check is with the following query:
SELECT v1.variableType,v2.varType,COUNT(*) as nRows
FROM vmcVariablesType as v1, vmcLmc75Variables as v2
WHERE v1.uniqueID!=v2.uniqueID and dbo.fGreatCircleDist(v1.ra,v1.dec,v2.ra,v2.dec)<0.016
GROUP BY v1.variableType,v2.varType
ORDER BY COUNT(*) desc

The results are below. variableType varType nRows rrLyr RR Lyrae 363 eclBin EB 76 NULL NONE 60 ceph Cepheid 42 agb NONE 21 eclBin NONE 9 rrLyr NONE 5 ceph TypeII Cepheid 5 ceph aCepheid 2 eclBin EB- 1 There are 584 duplicates. As can be seen there are no horrible misclassifications, but some where no classification was found. There are 60, where no classification was found in either. Looking at them in more detail, it turned out that they are 30 pairs of nearby objects in LMC7_5. These can be removed from the previous query with the extra constraint v1.cuEventID!=546781, as the LMC7_5 data were ingested in cuEventID=546781. Adding this constraint gives 524 duplicates. If you want to look in more detail at the details, you need to go to the tables of each variable star. For different variables, different parameters are important, so the tables have all got different parameters, but some have some in common, beyond positional and UID. Several tables, have a period, but not the LMC7_5 variables. Many have mean mags and amplitudes, but some have magnitude at max light.
SELECT uniqueID,period,ksMeanMag,ksAmpl
FROM vmcCepheidVariables
WHERE period>20. and ksAmpl>0.5

This returns 29 objects, one of which gives the following values: 558345748618 +33.088917 +13.705261 +0.569022 To use the rest of the VMC data it is first necessary to join them to the vmcSource table and use the sourceID from there. The uniqueID is not the same as the sourceID unless by a chance coincidence. To do this use the vmcSourceXVariablesType neighbour table and find the nearest match within an arsecond or so. The masterObjID in this table is the vmcSource.sourceID and the slaveObjID is the uniqueID from vmcVariablesType and the constituent variables tables, one of which is vmcCepheidVariables.
SELECT uniqueID,period,ksMeanMag,ksAmpl,xvar.masterObjID as sourceID
FROM vmcCepheidVariables as ceph, vmcSourceXVariablesType as xvar
WHERE period>20. and ksAmpl>0.5 and ceph.uniqueID=xvar.slaveObjID and xvar.distanceMins<0.016
and xvar.distanceMins in (
select min(distanceMins) from vmcSourceXVariablesType as xxv where xxv.slaveObjID=xvar.slaveObjID)

To get light-curves, you can use the vmcSourceXSynopticSourceBestMatch that matches vmcSource sources to the multi-epoch photometry. Below we take the selection above, use this to generate a temporary table in the query and then get light curves for all 29 sources above:
SELECT cephSel.*,sl.ymfID,ss.ymjd,ss.yAperMag3,ss.yAperMag3Err,ss.yppErrBits,
sl.jmfID,ss.jmjd,ss.jAperMag3,ss.jAperMag3Err,ss.jppErrBits,
sl.ksmfID,ss.ksmjd,ss.ksAperMag3,ss.ksAperMag3Err,ss.ksppErrBits,b.flag
FROM (select uniqueID,period,ksMeanMag,ksAmpl,xvar.masterObjID as sourceID
from vmcCepheidVariables as ceph, vmcSourceXVariablesType as xvar
where period>20. and ksAmpl>0.5 and ceph.uniqueID=xvar.slaveObjID and
xvar.distanceMins<0.016 and xvar.distanceMins in (select min(distanceMins) from
vmcSourceXVariablesType as xxv where xxv.slaveObjID=xvar.slaveObjID)) as cephSel,
vmcSourceXSynopticSourceBestMatch as b, vmcSynopticSource as ss, vmcSynopticMergeLog as sl
WHERE cephSel.sourceID=b.sourceID and b.synFrameSetID=ss.synFrameSetID and b.synSeqNum=ss.synSeqNum
and sl.synFrameSetID=b.synFrameSetID

The selection of the Cepheids is highlighted in red, and this selection forms a temporary table called cephSel, which is joined to vmcSynopticSource via vmcSourceXSynopticSourceBestMatch (best match). The best match table links every primary source to all epochs, whether there is a detection or not. The vmcSynopticSource table is a multi-epoch merged band catalogue, and the details of the frames at each pointing/epoch are in vmcSynopticMergeLog. In the query above we output the following attributes for every filter: mfID, mjd, AperMag3, AperMag3Err, ppErrBits. The mfID is the UID of the image frame in that band for the epoch and pointing. If it is default (-99999999), then there was no observation in that band. The time (mjd), 1" aperture, corrected magnitude (aperMag3) and its error, are the fundamentals for a light-curve and the bitwise quality flag (ppErrBits) gives useful additional information. The BestMatch table flag is also useful to indicate if a detection is linked to two or more sources or if a non-detection occured in the dither region close to the detector edge. To select a light-curve for just one of these Cepheids in the Ks band only, such as the object mentioned above (uniqueID=558345748618). In this case I have removed any multi-epoch framesets which don't have a Ks frame (and sl.ksmfID>0).
SELECT cephSel.*,sl.ksmfID,ss.ksmjd,ss.ksAperMag3,ss.ksAperMag3Err,ss.ksppErrBits,b.flag
FROM (select uniqueID,period,ksMeanMag,ksAmpl,xvar.masterObjID as sourceID
from vmcCepheidVariables as ceph, vmcSourceXVariablesType as xvar
where period>20. and ksAmpl>0.5 and ceph.uniqueID=xvar.slaveObjID
and ceph.uniqueID=558345748618 and
xvar.distanceMins<0.016 and xvar.distanceMins in (select min(distanceMins) from
vmcSourceXVariablesType as xxv where xxv.slaveObjID=xvar.slaveObjID)) as cephSel,
vmcSourceXSynopticSourceBestMatch as b, vmcSynopticSource as ss, vmcSynopticMergeLog as sl
WHERE cephSel.sourceID=b.sourceID and b.synFrameSetID=ss.synFrameSetID and b.synSeqNum=ss.synSeqNum
and sl.synFrameSetID=b.synFrameSetID and sl.ksmfID>0

Classification Catalogue
We can look at the classifications in the ML classification catalogue.
SELECT prfClass, COUNT(*) as nRows
FROM vmcMLClassificationCatalogue
GROUP BY prfClass
ORDER BY prfClass

prfClass nRows
AGB 25514415
AGN 2273991
Galaxy 298037
HII/YSO 253156
OB 5071279
pAGB/RGB 33796
PM 2486132
PNe 1157024
RGB 1747880
RSG 7793
Unk 93843430
We can look at variability of different classes. First we add additional constraints - catFlag='H' and pPrfClass>0.95 to select the most reliably classified objects.
SELECT prfClass, COUNT(*) as nRows
FROM vmcMLClassificationCatalogue
WHERE catFlag='H' and pPrfClass>0.95
GROUP BY prfClass
ORDER BY prfClass

With these constraints we find 347 HII/YSOs. In a similar way as we did with the Cepheids we can get the light curves. But first, let's find which ones show variability. To do this we get the vmcSource.sourceID using the vmcSourceXMLClassificationCatalogue table and then link to the vmcVariability catalogue, where we use the constraint v.variableClass=1. Then we link up the light curve data, as in the Cepheid example above.
SELECT mlSel.*,sl.ksmfID,ss.ksmjd,ss.ksAperMag3,ss.ksAperMag3Err,ss.ksppErrBits,b.flag
FROM (SELECT uniqueID,mlCls.ra,mlCls.dec,prfClass,x.masterObjID as sourceID
FROM vmcMLClassificationCatalogue as mlCls, vmcSourceXMLClassificationCatalogue as x
WHERE prfClass like '%YSO%' and pPrfClass>0.95 and catFlag='H' and x.slaveObjID=mlCls.uniqueID
and x.distanceMins<0.05 and x.distanceMins in (select min(distanceMins)
from vmcSourceXMLClassificationCatalogue as xx where xx.slaveObjID=x.slaveObjID)) as mlSel, WHERE mlSel.sourceID=v.sourceID and v.variableClass=1 and v.sourceID=b.sourceID and
b.synFrameSetID=ss.synFrameSetID and b.synSeqNum=ss.synSeqNum and sl.synFrameSetID=b.synFrameSetID
and sl.ksmfID>0

Extragalactic sources
There are 3 team generated tables including extragalactic sources: vmcBackgroundSources, vmcQsos, and vmcMLClassificationCatalogue. You can select data from vmcBackgroundSources, vmcMLClassificationCatalogue and vmcVariability and link them together as below. For both the background sources and classification catalogue, the sourceID needs to be found first using the correct neighbour table. We have added these links in as subqueries and then join up.
SELECT backSrc.*,mlClsSrc.*,v.variableClass
FROM (select uniqueID,ra,dec,zBest,Template_GQ,xbk.masterObjID as sourceID
from vmcBackgroundSources as back, vmcSourceXBackgroundSources as xbk
where back.uniqueID=xml.slaveObjID and xbk.distanceMins<0.03 and xbk.distanceMins in (
select min(distanceMins) from vmcSourceXBackgroundSources as xxb where
xxb.slaveObjID=xbk.slaveObjID)) as backSrc,
(select uniqueID,ra,dec,prfClass,pPrfClass,catFlag,isXrayRadio,xml.masterObjID as sourceID
from vmcMLClassificationCatalogue as mlcls, vmcSourceXMLClassificationCatalogue as xml
where mlcls.uniqueID=xml.slaveObjID and xml.distanceMins<0.03 and xml.distanceMins in (
select min(distanceMins) from vmcSourceXMLClassificationCatalogue as xxm
where xxm.slaveObjID=xml.slaveObjID)) as mlClsSrc, vmcVariability as v
WHERE backSrc.sourceID=mlClsSrc.sourceID and v.sourceID=backSrc.sourceID

Additional constraints can be added to this query, such as
SELECT backSrc.*,mlClsSrc.*,v.variableClass
FROM (select uniqueID,ra,dec,zBest,Template_GQ,xbk.masterObjID as sourceID
from vmcBackgroundSources as back, vmcSourceXBackgroundSources as xbk
where back.uniqueID=xml.slaveObjID and xbk.distanceMins<0.03 and xbk.distanceMins in (
select min(distanceMins) from vmcSourceXBackgroundSources as xxb where
xxb.slaveObjID=xbk.slaveObjID)) as backSrc,
(select uniqueID,ra,dec,prfClass,pPrfClass,catFlag,isXrayRadio,xml.masterObjID as sourceID
from vmcMLClassificationCatalogue as mlcls, vmcSourceXMLClassificationCatalogue as xml
where mlcls.uniqueID=xml.slaveObjID and xml.distanceMins<0.03 and xml.distanceMins in (
select min(distanceMins) from vmcSourceXMLClassificationCatalogue as xxm
where xxm.slaveObjID=xml.slaveObjID)) as mlClsSrc, vmcVariability as v
WHERE backSrc.sourceID=mlClsSrc.sourceID and v.sourceID=backSrc.sourceID
AND v.ksMagRms>0.3 and zBest>0.3

Home | Overview | Browser | Access | Login | Cookbook
Listing | FreeSQL
Links | Credits

WFAU, Institute for Astronomy,
Royal Observatory, Blackford Hill
Edinburgh, EH9 3HJ, UK

vsa-support@roe.ac.uk
22/5/2025

Suggested SQL Queries for VMC Users

Contents

1. Introduction

2. Data Structure

2.1. Images and basic catalogues

2.2. Stars

2.3. Colours

2.4. Multi-epoch data

2.6 Non-pipeline team (Value Added Product, VAP) catalogues

PSF photometry.

Proper Motions.

Variable stars

Classification Catalogue

Extragalactic sources