|
|
SQL Frequently Asked Questions
How do I generate an abbreviated list of columns for specific survey
(or for all of them)?
How do I download a spectrum for an object whose name or coordinates
are known?
How do I upload a list of targets to see if GALEX has observed them?
How do I use the pull down menu on the MAST SQL form?
What are the detailed explanations for each of the SQL example
queries?
Q: How do I generate an abbreviated list of columns for specific
survey (or for all of them)?
A: Let's take the first question first. We will generate a results table
which you may want to generate as a Comma Separated Value table (an IDL routine
is provided in our contributing software area to convert CSV to blank separated
value tables). We will take the query below and paste it into the text box on
the SQL form . This query will provide you with a
list of tiles observed by the AIS mission and observed either in FUV or NUV
bandpasses. It will list the survey and tile names, the coordinate centers for
the tiles, the path name used to retrieve (say) the jpeg image, and (for cases
of multiple tiles at the same coordinates, it will rank multiple observations
by increasing exposure time. All statements encased within /* and */ symbols
are comments and are given here for clarification to the user.
To make the query inclusive to all surveys, delete the line "and
nuv_mpstype='AIS'.
Before clicking the Execute button, be sure to click on the "CSV" option for a
comma separated values ascii file; this will download to your computer, once
the execution is complete. To see the output conveniently first, click on the
html option.
START QUERY:
select nuv_mpstype as survey, tile as tilename, ra_cent,
dec_cent, nuv_exptime,fuv_exptime, FileNPath
/*
additional columns which are part of the original query for all AIS tiles
these can be added back into the select clause above to return these
columns as well.
f.img,f.subvis,
i.tilenum, nuv_nadded as nuv_Visits,
fuv_nadded as fuv_Visits, isnull(nSpectra,0) as nSpectra
*/
from img as i, fileDescriptions as fd, ftpList as f, imgrun as ir
where i.tilenum=f.tilenum and f.fileExtID = fd.fileExtID and
ir.imgrunid=i.imgrunid
and fd.fileExt = '-xd-int_2color.jpg'
and f.img = isnull(ir.visitNum,1)
and f.subvis = ir.subvis
and nuv_mpstype='AIS'
/* or substitute the previous line with the */
/* following to get a listing of all tiles */
/* for the MIS survey */
/* and nuv_mpstype = 'MIS' */
/* other options are DIS for the deep imaging survey, */
/* NGS for the nearby galaxy survey or the */
/* line can be completely removed to obtain a listing */
/* of all tiles */
order by ra_cent,dec_cent, nuv_exptime
END QUERY
Q: How do I download a spectrum for an object whose name or
coordinates are known?
A: Let's start with an example of an object at J2000 coordinates 00 37
53, -43 28 24 (or 9.471094282, -43.47359279) which goes by the name [ALF2001]
11 at NED.
Step 1: Get down to the Explore page of the object:
go to the Simple Query Form (under "Data Search" in the left gutter)
enter the object's RA and dec in hh mm ss, dd mm ss or decimal degrees (for
quickest results, turn the resolver from NED to "Don't Resolve.")
Provided your RA, Dec has been given precisely enough, the first object in the
Results page should be ObjId 2532819730078656292.
if you'd like, click on "Explore" and scroll down through the GALEX images and
grism spectrum. Is this what you're looking for?
Proceed to step 2 or step 3 depending on whether you want only the spectrum of
your object or all the spectra for the tile containing your object.
Step 2: For downloading a single spectrum in CSV or VOTable format:
Find the SpecObjID number just above the spectrum (in this example, it is
2532821929101911844).
Go to the SQL form and enter the following text in the box:
select * from specflux
where specobjid=2532821929101911844
order by wavelength
where the long number is the specObjID number you just discovered from the
spectrum in the Explore page.
Now decide if you want to look at your spectrum fluxes as a long table on the
web page (html), or -much more probably- if you want to download your data in
CSV (comma separated value, in ascii) or VOTable (xml) format. To do this,
check your preferred Output Format option and click on "Execute".
(It is up to the user to work with the format delivered, e.g. in IRAF or IDL.
Users who prefer to work with the CSV and have an IDL license may download the
script readcsvblkwriteall.pro code from our Contributed Software area. The
first row in this table file gives headers for the data (wavelength, flux,
errors, etc. ) in the rest of the table.)
Step 3: For downloading of the fits file of the tile containing the object:
note the ID number, marked "xid" in top right of the explore page (26404) for
this object.
Click on "Download spectra:" icon on the Explore page.
This will give a listing of all spectral products for this tile. The particular
file giving spectra for "both bands" (merged spectra) in this case is called
ELAISS1_00-xg-gsp.fits.gz. Click on download and it will download to your
computer. This file contains spectra (and other information) on all objects in
the tile.
Read and interpret the FITS file.
The fits table extension contains many columns. We are interested in the object
identification number, which was given as the "xid" number (e.g. 26404) on the
Explore page for the object (Step 2). The spectrum for our object is given in
one row of the "id" array, in this case row 833. Next, you will need to build
the wavelength array, using the zero and disp columns. This array will be 551
points in the GR2 delivery. Finally, the flux array should be extracted from
the heading "obj," which is a 925 X 551 table in our example. Since object id =
26404 is 833rd in the list of 925, the flux array of interest is the 833rd row
of the obj array, and can be expected as such. Similarly, the flux error can be
obtained from the column "objerr". You are now free work with the wavelength,
flux arrays.
Q: How do I upload a list of targets to see if GALEX has observed
them?
A:The best way to do this currently is to use the CASJobs tool. CASJobs
is a tool that was created by the JHU/SDSS developers to permit long running
queries (such as cross correlations on long lists of objects to run in batch
mode). Example 2 near the end of the GALEX Tutorial describes how to upload a
list and match it on GALEX-observed objects. Although this example is helpful
as a tutorial, it is also rather terse, so we describe it here in words. We
warn the reader in advance that CASJobs is somewhat browser dependent. For
example, CASJobs works best for Mac/Unix with Firefox (and for PC/window
systems with Internet Explorer or Firefox).
Step 1 is to register in CASJobs. This is done by clicking the "Create Account"
tab on the upper menu of the CASJobs home page. Registration is an
instantaneous process. We require this only to track legitimate, scientific use
of our database and server resources. Once a user account is created, this step
does not need to be repeated: just remember your account name and password!
(But if you have trouble, contact MAST)
If you are a new user to CASJobs, you may wish to read the CASJobs help page.
An important detail to understand is the selection of the "context" in which
you will be working. Since you will be "joining" your uploaded table with data
from the GALEX database, your context will be "MyDB" -- that is, you'll be
working in an area for which we have provided you some disk space for your
tables.
Step 2 is to upload an ascii table listing your objects (see also the
Tutorial), with one row assigned to each object. The first row will list the
names of columns you represent, e.g. "Name (or ID) RA Dec..." -- note that the
identifiers RA and Dec must be explicitly given in this row. Also, each row
should contain the same number of fields. It should include at least an
alphanumeric name for the objects (e.g. "A1, A2...") and its coordinates in
decimal format.
The example given in the Tutorial of a short uploaded list is:
name ra dec
A1 9.315 -42.966
A2 10.356 -42.949
A3 52.012 -28.211
An upload is initiated by clicking on the "Import" tab at the top of the
CASJobs login page. This will take you to a MyDB Table Import page. Browse to
or enter the path and file name of your list file in the Upload File text box.
Alternatively, if your table is short, you may decide to enter it in the Paste
Data box. Enter a name for the table which will contained the imported data in
the Table Name text box. Import the data by clicking on the Import button ,
there will be a message indicating success or failure of the import. Click on
the "MyDB" tab and the table name should be listed in the left gutter.
Step 3 is to consult your table to insure it was properly created. This is done
by first clicking on your newly created table (left gutter). When the new page
is loaded mouse over "View data" and click on Go under the black box. Your
imported table will appear. (For some browsers, this can fail.)
Step 4 is to cross-correlate (match) your object list with nearby objects
observed by GALEX. Click again on the MyDB tab and go to your new table by
clicking on it again in the left gutter. This time mouse over the "Neighbors"
tab above the Table Schema header, and enter a name for the cross-matched table
you wish to create, specify the search radius in arcminutes and choose from the
available GALEX GRn releases in "Search for neighbors in this context" drop
down list. Click on the Go button and wait for the cross-match to be performed.
Step 5 is to view your new cross-matched results table. This is done by
clicking on MyDB a last time and clicking on the name of your new table in the
left gutter. Mouse over the "View Table" tab a final time. If you wish to view
more than the first 100 entries (default limit), change the default limit to a
sufficiently large number. Click on "Go" again. Your cross-matched table will
be returned. You may also decide to download this table to your computer. You
may do this by setting the download file format from the list of available
formats, (CSV, XML, VOTABLE, etc) - then click on Go. An output page will be
displayed with your pending output, refreshing this page will eventually
indicate completion of the output file with a link to download the file.
Q: How do I use the pull down menu on the MAST SQL form?
A: Each of the queries in the pulldown menu of the SQL
form page is designed to return a list of object, tiles, or eclipses
pertaining to GALEX observations. These queries are meant to address generic
specific purposes and can be easily customized for the requester's purposes.
This process can be facilitated for the SQL novice with the list of keyword
parameters at the bottom of the SQL request form. Also, in putting together
these sample queries we have erred often on the side of overspecificity. For
example, requesters wanting lists of objects for all programs satisfying other
constraints, can easily delete in their SQL text the phrase denote GALEX
program selection to return entries for all programs. For the SQL-challenged we
recommend SDSS sites titled
SQL Introduction and
Sample SQL Queries.
Q: What are the detailed explanations for each of the SQL example
queries?
A: See the sample query and queries numbered 1-9 below with detailed
explanations.
SAMPLE QUERY: list 100 NUV-brightest objects in all GALEX surveys
select top 100 p.objid, p.ra, p.dec, p.glon, p.glat,
p.nuv_mag, p.nuv_magerr, p.fuv_mag, p.fuv_magerr,
p.e_bv, pe.mpstype as survey
from photoobjall as p, photoextract as pe
where p.photoextractid=pe.photoextractid
and p.nuv_mag>-99 and p.band=3
and p.nuv_fwhm_world<.01
order by p.nuv_mag
This query returns the 100 NUV-brightest objects observed by GALEX in both
wavelength bands among all programs (these happen to be stars), together with
several parameters of interest. The entries are sorted by nuv_mag.
SHORT QUERY: 1. Objects in sky areas with large T_exp
FULL: List all objects in program X within position (ra,dec,r) with exposure
times longer than 3000 seconds.
select nb.objid,nb.distance as dstArcMin,p.ra,p.dec,
p.band,p.glon,p.glat,p.nuv_mag,
p.nuv_magerr,p.fuv_mag, p.fuv_magerr, p.e_bv,
pe.nexptime,pe.fexptime,pe.mpstype as survey
from fgetnearbyobjeq(116.58073,42.66339, 5/*arcmins*/) as nb,
photoextract as pe,
photoobjall as p
where nb.objid=p.objid
and p.photoextractid=pe.photoextractid
and pe.nexptime > 3000 /*some threshold in seconds*/
and pe.mpstype='MIS' /* only MIS survey */
order by dstArcMin
For an area of radius 5' around the decimal (J2000 RA, Dec) coordinates
(116.58073,42.66339) center this syntax lists all objects with (NUV) exposures
longer than 3000 secs. The extraction of this list is done by the SQL function
"fgetnearbyobjeq" and by consulting tables photoextract (alias "pe") and
photoobjall ("p"). As for the brightest object query described above, one can
extract a list of brightest objects by substituting the nexptime ">3000"
condition with a limit on nuv_mag and/or fuv_mag.
SHORT QUERY: 2. Tiles with long MIS exposures
FULL: List all tiles that have cumulative exposure times of >2000 seconds in
any single program.
select mpstype as survey,
tilename,tilenum,object,nexptime,fexptime,
avaspra,avaspdec /* center of field of view */
from photoextract
where nexptime > 2000 /* some threshold in seconds*/
and mpstype = 'MIS' /* remove this line to show all tiles */
order by nexptime desc
SHORT QUERY: 3. Central objects in given tile
FULL: List all objects in tile xxxx
select nb.objID,p.nuv_mag,p.fuv_mag,
distance as dstArcMin, pe.mpstype as survey
from dbo.fGetNearbyObjEqFromTileCenter('SIRTFFL_00',5) as nb,
photoobjall as p, photoextract as pe
where nb.objid=p.objid
and p.photoextractid=pe.photoextractid
order by dstArcMin
/* Search based on tilename and radius in minutes */
To run this query one must first have found a tile of interest and input its
name, like "SIRTFFL_00". The SQL function fGetNearbyObjEqFromTileCenter has
been written for GALEX to determine all object names within a radius in
arcminutes.
SHORT QUERY: 4. Objects and their nearest neighbor
FULL: List all objects within area (ra, dec, r) and their nearest neighbor.
select nn.objid as COobjid,nn.neighborobjid as NBobjid,
nn.distance,onv.COra,onv.NBra,
onv.COdec,onv.NBdec,
onv.COband,onv.NBband,
onv.COglon,onv.NBglon,
onv.COglat,onv.NBglat,
onv.COnuv_mag,onv.NBnuv_mag,
onv.COnuv_magerr,onv.NBnuv_magerr,
onv.COfuv_mag,onv.NBfuv_mag,
onv.COfuv_magerr,onv.NBfuv_magerr,
onv.COe_bv,onv.NBe_bv
from fgetnearbyobjeq(349.903413,37.320482,5) as no,
objectNeighborV as onv,nearestNeighbor as nn
where onv.coobjid=no.objid
and onv.COobjid=nn.objid
and onv.nbobjid=nn.neighborobjid
/* CO = central object, NB = neighbor object */
/* distance is in arcminutes */
For an area of radius 5' centered at coordinates ~(349.903413,37.320482) this
query lists all objects and in each case its nearest neighbor only if there is
a neighbor within ~30".
SHORT QUERY: 5. FUV-faint objects detected in NUV
FULL: List all NUV objects that have no FUV counterparts
select nb.objid,pe.mpstype as survey,nb.distance as dstArcMin,
p.ra,p.dec,p.band,p.glon,p.glat,p.nuv_mag,
p.nuv_magerr,p.fuv_mag, p.fuv_magerr, p.e_bv
from fgetnearbyobjeq(116.58073,42.66339, 5/*arcmins*/) as nb,
photoobjall as p, photoextract as pe
where nb.objid=p.objid
and p.photoextractid=pe.photoextractid
and p.band=1 /* band = NUV */
order by dstArcMin
For an area of radius 5' centered at coordinates ~(116.58073,42.66339) this
query lists all objects that are detected in NUV only.
SHORT QUERY: 6. List all objects observed during eclipse x (e.g. 1727)
FULL: List all objects observed during eclipse x (e.g. 1727)
select p.plan_id,op.eclipse_num,
pe.mpstype,pe.tilename,
po.objid, po.ra, po.dec, po.fov_radius
from plans as p,
obspending as op,
visitmain as vm,
imgrunvisitmainjoin as j,
imgrun as ir,
img as i,
photoextract as pe,
photoobjall as po
where ir.imgrunid = j.imgrunid
and j.visitid=vm.visitid
and vm.nuv_eclipse = op.eclipse_num
and op.plan_id = p.plan_id
and pe.imgid = i.imgid
and ir.imgrunid = i.imgid
and pe.photoextractid = po.photoextractid
and op.eclipse_num = 1727
SHORT QUERY: 7. Tile is associated with eclipse x (e.g. 1727)?
FULL: What tile is associated with eclipse x (e.g. 1727)?
select p.plan_id,op.eclipse_num,
pe.mpstype,pe.tilename,pe.tilenum
from plans as p,
obspending as op,
visitmain as vm,
imgrunvisitmainjoin as j,
imgrun as ir,
img as i,
photoextract as pe
where ir.imgrunid = j.imgrunid
and j.visitid=vm.visitid
and vm.nuv_eclipse = op.eclipse_num
and op.plan_id = p.plan_id
and pe.imgid = i.imgid
and ir.imgrunid = i.imgid
and op.eclipse_num = 1727
SHORT QUERY: 8. List all objects from a tile that have spectra?
FULL: List all objects from tile CDFS_00 that also have spectra with a radius
less than 0.25 degrees from the center of the tile?
select objid,id as xid,fov_radius,
ra,dec,isThereSpectrum,
pe.tilenum,pe.tilename, pe.mpstype as survey
from photoobjall as po, photoextract as pe,
img as i, imgrun as ir
where po.photoextractid=pe.photoextractid
and pe.imgid=i.imgid
and i.imgrunid=ir.parentimgrunid
and pe.tilename='CDFS_00'
/* pick a specific tile, remove this line to see all tiles with spectra */
and ir.source='coaddS'
/* only choose tiles which also have spectra */
and fov_radius < 0.25
/* objects within 0.25 degrees of tile center */
and isThereSpectrum=1
/* flag to indicate this object also has a spectra */
order by pe.tilename,fov_radius
The results page lists objects with spectra as well as the running
tile number and the parent GALEX program name. Note the link to the
Explore page, so the spectra can be easily browsed.
SHORT QUERY: 9. List all tiles that cover these coordinates: RA=xxx,
RA=yyy?
FULL: List all tiles that cover these coordinates in the sky, RA=3.25491,
Dec=-10.593509?
select * from fFindInTile(3.25491,-10.593509)
/* This query includes GRn tiles only. To obtain tiles from */
/* GI programs and data if public, use this query */
/* select * from fFindInTileGI(49.36958,-37.8575) */
This query runs with the local SQL function fFindInTile and uses as a
working radius 39.6 arcmins. In comments following this query, we offer
the alternative query to list tilenames and data for proprietary and
public GI program data. Note that GI observations do not always
pass the same Quality Assurance tests as do GR data.
These data are maintained on a separate FTP site.
|
|